DB Manager

A powerful, modern PostgreSQL database management and visualization tool built with React and NestJS. Manage multiple database connections, explore schemas, execute queries, visualize relationships, and export data—all from a beautiful, intuitive interface.

🖥️ NEW: Desktop App Available! You can now run DB Manager as a standalone desktop application for Windows, macOS, and Linux. See the Desktop App section below.

✨ Features

🔌 Connection Management

Multiple Database Connections: Manage unlimited PostgreSQL database connections
Secure Password Storage: Encrypted credentials using AES-256-CBC
Connection Testing: Test connections before saving
Connection Pooling: Efficient connection management with automatic pooling

📊 Schema & Data Exploration

Schema Browser: Navigate through databases, schemas, and tables
Table Viewer: Browse table data with advanced features:
- Pagination (configurable page size)
- Column filtering and sorting
- Full-text search across columns
- Column selection and visibility
Database Statistics: View database size, table counts, and more

🔍 Query Execution

SQL Query Editor: Execute custom SQL queries with syntax highlighting
Query History: Automatic tracking of executed queries
Saved Queries: Save frequently used queries with tags and descriptions
Query Snippets: Reusable SQL code snippets
Query Execution Plans: Analyze query performance with EXPLAIN
Query Timeout: Configurable timeout limits

🗺️ Visualization

ER Diagrams: Interactive entity-relationship diagrams
Foreign Key Navigation: Explore relationships between tables
Schema Visualization: Visual representation of database structure
Charts: Create visualizations from query results

📤 Data Export

Multiple Formats: Export to CSV or JSON
Filtered Exports: Export filtered or selected data
Schema Dumps: Export database schema definitions

🔧 Advanced Features

Index Recommendations: Get suggestions for database optimization
Full-Text Search: Search across database metadata
Dark Mode: Beautiful dark/light theme support
Responsive Design: Works seamlessly on desktop and tablet

🖥️ Desktop Application

DB Manager is also available as a standalone desktop application! No web server setup needed - just install and run.

Features

✅ Single executable - No npm run dev needed
✅ Cross-platform - Windows, macOS, Linux
✅ Offline-ready - Works without internet
✅ Native experience - Desktop file dialogs, notifications
✅ Portable - All data stored locally
✅ Same features - Identical to the web version

Quick Start (Desktop)

# 1. Navigate to desktop folder
cd desktop

# 2. Install dependencies
npm install

# 3. Build the desktop app
npm run build

# 4. Find installer in desktop/dist/
# - Windows: DB Manager Setup 1.0.0.exe
# - macOS: DB Manager-1.0.0.dmg
# - Linux: DB-Manager-1.0.0.AppImage

Full documentation: See desktop/README.md and desktop/QUICK_START.md

🛠️ Tech Stack

Backend

Framework: NestJS 10+
Language: TypeScript 5+
Database Driver: PostgreSQL (pg 8+)
Validation: class-validator, class-transformer
Encryption: AES-256-CBC for secure password storage

Frontend

Framework: React 18+ with TypeScript
Build Tool: Vite 5+
UI Components: Radix UI + shadcn/ui
Styling: Tailwind CSS
State Management: React Query (TanStack Query)
Routing: React Router v6
Visualization: ReactFlow, Recharts
SQL Parsing: node-sql-parser

📦 Prerequisites

Before you begin, ensure you have:

Node.js 18+ and npm (or yarn/pnpm)
PostgreSQL database (for testing/development)
Git (for cloning the repository)
OpenSSL (for generating encryption keys)

🚀 Quick Start

1. Clone the Repository

git clone <repository-url>
cd db-manager

2. Backend Setup

cd backend

# Install dependencies
npm install

# Create .env file
cp env.template .env

# Generate encryption key (required)
openssl rand -base64 32
# Add the generated key to .env:
# ENCRYPTION_KEY=your-generated-key-here

# Start development server
npm run start:dev

The backend will run on http://localhost:3000

Verify Backend:

curl http://localhost:3000/api/health
# Should return: {"status":"ok"}

3. Frontend Setup

# In a new terminal
cd frontend

# Install dependencies
npm install

# Start development server
npm run dev

The frontend will run on http://localhost:5173 or http://localhost:8080

Verify Frontend: Open http://localhost:5173 in your browser

📁 Project Structure

db-manager/
├── backend/                 # NestJS backend API
│   ├── src/
│   │   ├── connections/     # Connection management
│   │   ├── schemas/         # Schema metadata
│   │   ├── data/            # Table data operations
│   │   ├── queries/         # Query execution
│   │   ├── query-history/   # Query history & saved queries
│   │   ├── diagram/         # ER diagram generation
│   │   ├── export/          # Data export functionality
│   │   ├── foreign-keys/    # Foreign key navigation
│   │   ├── charts/          # Chart generation
│   │   ├── search/          # Database search
│   │   └── common/          # Shared utilities
│   ├── database/            # File storage (connections, history)
│   └── _docs/               # Backend documentation
│
├── frontend/                # React frontend
│   ├── src/
│   │   ├── components/      # React components
│   │   ├── pages/           # Page components
│   │   ├── lib/             # Utilities and API client
│   │   ├── contexts/        # React contexts
│   │   └── hooks/           # Custom hooks
│   └── public/              # Static assets
│
├── _docs/                   # Project documentation
│   ├── SETUP_GUIDE.md       # Detailed setup instructions
│   ├── API_SERVICE_DOCUMENTATION.md
│   └── FRONTEND_INTEGRATION_README.md
│
└── _scripts/                # Utility scripts
    ├── start-dev.sh         # Start both services
    └── start-prod.sh        # Production start script

⚙️ Configuration

Backend Configuration

File: backend/.env

# Database Encryption Key (REQUIRED)
# Generate with: openssl rand -base64 32
ENCRYPTION_KEY=your-secure-encryption-key-here

# Server Port (optional, default: 3000)
PORT=3000

# Node Environment
NODE_ENV=development

# Frontend URL (optional, for CORS)
FRONTEND_URL=http://localhost:5173

Important: The ENCRYPTION_KEY is required for encrypting database passwords. Generate a secure key:

openssl rand -base64 32

Frontend Configuration

File: frontend/.env (optional)

# API Base URL (optional, default: http://localhost:3000/api)
VITE_API_URL=http://localhost:3000/api

📚 Documentation

Comprehensive documentation is available in the _docs/ directory:

SETUP_GUIDE.md - Complete setup and installation guide
API_SERVICE_DOCUMENTATION.md - Frontend API service reference
FRONTEND_INTEGRATION_README.md - Frontend-backend integration guide
STATE_MANAGEMENT_DOCUMENTATION.md - State management patterns

Backend Documentation

backend/_docs/API_DOCUMENTATION.md - REST API reference
backend/_docs/ARCHITECTURE.md - System architecture and design
backend/_docs/DEPLOYMENT.md - Deployment guide

💻 Development

Backend Development

cd backend

# Start with hot reload
npm run start:dev

# Build for production
npm run build

# Start production server
npm run start:prod

# Run linting
npm run lint

# Run tests
npm test

Frontend Development

cd frontend

# Start development server
npm run dev

# Build for production
npm run build

# Preview production build
npm run preview

# Run linting
npm run lint

Start Both Services

Use the provided scripts:

# Development mode
./_scripts/start-dev.sh

# Production mode
./_scripts/start-prod.sh

🚢 Production Deployment

Backend Production

cd backend

# Build the application
npm run build

# Start production server
npm run start:prod

# Or use PM2 for process management
pm2 start dist/main.js --name db-manager-api

Frontend Production

cd frontend

# Build the application
npm run build

# Output will be in frontend/dist/
# Serve with any static file server:
# - nginx
# - Apache
# - Vercel
# - Netlify

Systemd Service (Optional)

See _scripts/SETUP_AUTOSTART.md for setting up the backend as a systemd service.

🔒 Security

Development

Use strong ENCRYPTION_KEY (32+ characters)
Don't commit .env files
Use test databases, not production
Keep dependencies updated

Production

Use environment variables for all secrets
Enable HTTPS
Configure CORS properly
Use strong encryption keys
Regular security audits
Keep dependencies updated

Security Features

Password Encryption: All database passwords are encrypted using AES-256-CBC
SQL Injection Prevention: All queries use parameterized statements
Input Validation: DTOs with class-validator ensure data integrity
CORS Protection: Configurable CORS settings for API access

🐛 Troubleshooting

Common Issues

Backend Port Already in Use

# Find process using port 3000
lsof -i :3000

# Kill the process
kill -9 <PID>

# Or change port in .env
PORT=3001

Encryption Key Error

# Generate a new key
openssl rand -base64 32

# Add to backend/.env
ENCRYPTION_KEY=<generated-key>

Database Connection Failed

Verify database credentials
Check PostgreSQL is running: pg_isready
Verify user permissions
Check pg_hba.conf for authentication settings

CORS Errors

Ensure backend CORS allows frontend origin
Check backend/src/main.ts CORS configuration
Verify frontend URL matches allowed origins

Frontend API Connection Failed

Verify backend is running: curl http://localhost:3000/api/health
Check VITE_API_URL in frontend/.env
Verify firewall/network settings

For more troubleshooting tips, see SETUP_GUIDE.md.

🧪 Testing

Backend Tests

cd backend

# Run unit tests
npm test

# Run E2E tests
npm run test:e2e

# Test coverage
npm run test:cov

Frontend Tests

cd frontend

# Run tests (if configured)
npm test

📝 Usage Example

Start the application (see Quick Start above)
Create a Database Connection:
- Open the app in your browser
- Click the Settings icon
- Click "Add New Connection"
- Enter your PostgreSQL connection details
- Test the connection
- Save the connection
Explore Your Database:
- Select a connection from the sidebar
- Browse schemas and tables
- Click on a table to view its data
- Use filters, sorting, and search to explore
Execute Queries:
- Open the Query Editor
- Write and execute SQL queries
- View query history and save frequently used queries
Visualize Relationships:
- Navigate to the Diagram view
- Explore entity-relationship diagrams
- Click on foreign keys to navigate relationships
Export Data:
- View table data or query results
- Click the Export button
- Choose format (CSV/JSON) and options

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Fork the repository
Create your feature branch (git checkout -b feature/AmazingFeature)
Commit your changes (git commit -m 'Add some AmazingFeature')
Push to the branch (git push origin feature/AmazingFeature)
Open a Pull Request

📄 License

This project is licensed under the MIT License.

🙏 Acknowledgments

Built with NestJS
UI components from shadcn/ui
Database visualization with ReactFlow
Charts powered by Recharts

📧 Support

For issues, questions, or contributions, please open an issue on the repository.

Happy Database Managing! 🚀

Name		Name	Last commit message	Last commit date
Latest commit History 25 Commits
_docs		_docs
backend		backend
desktop		desktop
frontend		frontend
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
CHANGELOG_DESKTOP.md		CHANGELOG_DESKTOP.md
README.md		README.md
RELEASE_NOTES.md		RELEASE_NOTES.md
START_DESKTOP.sh		START_DESKTOP.sh

rosettascript/db-manager

Folders and files

Latest commit

History

Repository files navigation