🔧 MDB - Database Management CLI Tool

A comprehensive TypeScript-based database management tool with both CLI and programmatic interfaces. Supports multiple database types with flexible configuration.

✨ Features

🖥️ CLI Tool

• Command-Line Interface: Easy-to-use CLI for database operations
• URL-based Connections: Simple connection string format
• SSH Tunnel Support: Secure database connections through SSH tunnels (Bastion/Jump hosts)
• Flexible Configuration: URL mode, config mode, or mixed configuration
• JSON Output: Structured output for integration with other tools
• Multiple Database Support: MariaDB, MySQL, PostgreSQL
• Extensible Commands: Easy to add new commands following OCP
• Intelligent Error Handling: Detailed error messages with troubleshooting guides

🔧 Programmatic API

• Flexible Configuration: Use provided config or fallback to config.json
• SSH Tunneling: Built-in SSH tunnel support for secure connections
• TypeScript Support: Full type safety and IntelliSense
• Connection Pooling: Efficient connection management
• Error Handling: Comprehensive error handling and validation
• Extensible: Easy to add new database adapters
• Logging System: Configurable Winston-based logging with multiple levels

🚀 Quick Start

CLI Tool Usage

The MDB CLI tool allows you to interact with databases using simple commands:

# Basic syntax
mdb {connection_url} {command} [args...]

# Connection URL format
{mariadb|mysql|postgresql}://{username}:{password}@{host}:{port}/{database}

Available Commands

# Initialize config.json in ~/.mdb/
mdb init
   
# Show database server information
mdb info

# List users and privileges
mdb users

# List all databases
mdb databases

# List tables (--detail for full info)
mdb tables [database] [--detail|-d]

# Show table schema and constraints
mdb {table} info

# Show table indexes
mdb {table} index

# Show help
mdb --help

CLI Examples

URL Mode (with connection string):

# Get MariaDB server information
mdb mariadb://root:password@localhost:3306/myapp info

# List all users in MySQL
mdb mysql://admin:[email protected]:3306/userdb users

# List databases in PostgreSQL
mdb postgresql://postgres:pass123@localhost:5432/postgres database

# List tables in a specific database
mdb mariadb://user:pass@localhost:3306/shop inventory tables

# Get detailed information about a table
mdb mysql://user:pass@localhost:3306/shop inventory products info

# Show indexes for a table
mdb postgresql://user:pass@localhost:5432/analytics logs user_actions index

Config Mode (using config.json):

# First, initialize configuration
mdb init

# Or create config.json manually in ~/.mdb/ directory
mkdir -p ~/.mdb
cat > ~/.mdb/config.json << 'EOF'
{
  "log": {
    "enable": true,
    "level": "info"
  },
  "ssh": {
    "enable": false
  },
  "database": {
    "type": "mariadb",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "your_password",
    "name": "your_database"
  }
}
EOF

# Then run commands without connection URL
mdb info                    # Get database server information
mdb users                   # List users and privileges
mdb databases               # List all databases
mdb mydb tables             # List tables in 'mydb' database
mdb mydb users info         # Get info about 'users' table
mdb mydb logs actions index # Show indexes for 'actions' table in 'logs'

All CLI commands return results in JSON format for easy integration with other tools.

🔐 SSH Tunnel Support

MDB supports secure database connections through SSH tunnels, perfect for accessing databases behind bastion/jump hosts or firewalls.

SSH Configuration

Add SSH configuration to your ~/.mdb/config.json:

{
  "log": {
    "enable": true,
    "level": "info"
  },
  "ssh": {
    "enable": true,
    "host": "bastion.example.com",
    "port": 22,
    "username": "ec2-user",
    "privateKeyPath": "/path/to/private-key.pem"
  },
  "database": {
    "type": "mariadb",
    "host": "internal-db.example.com",
    "port": 3306,
    "user": "dbuser",
    "password": "dbpassword",
    "name": "production_db"
  }
}

SSH Authentication Methods

1. Private Key Authentication (Recommended)

{
  "ssh": {
    "enable": true,
    "host": "bastion.example.com",
    "port": 22,
    "username": "ubuntu",
    "privateKeyPath": "/Users/you/.ssh/id_rsa"
  }
}

2. Password Authentication

{
  "ssh": {
    "enable": true,
    "host": "bastion.example.com",
    "port": 22,
    "username": "ubuntu",
    "password": "your-ssh-password"
  }
}

SSH Troubleshooting

Common Issues:

Connection Timeout

# Check SSH connectivity
ssh -i /path/to/key.pem [email protected]

# Verify network connectivity
ping bastion.example.com

Permission Denied (Private Key)

# Fix key permissions (required for security)
chmod 400 /path/to/key.pem

# Remove macOS quarantine attribute if needed
xattr -d com.apple.quarantine /path/to/key.pem

Host Key Verification Failed

# Add host to known_hosts
ssh-keyscan bastion.example.com >> ~/.ssh/known_hosts

Programmatic API Usage

For programmatic usage, you can also use the underlying Database class:

Configuration

Create a config.json file in ~/.mdb/ directory:

{
  "log": {
    "enable": true,
    "level": "info"
  },
  "ssh": {
    "enable": false
  },
  "database": {
    "type": "mariadb",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "your_password",
    "name": "your_database"
  }
}

With SSH Tunnel:

{
  "log": {
    "enable": true,
    "level": "info"
  },
  "ssh": {
    "enable": true,
    "host": "bastion.example.com",
    "port": 22,
    "username": "ubuntu",
    "privateKeyPath": "/Users/you/.ssh/id_rsa"
  },
  "database": {
    "type": "mariadb",
    "host": "internal-db.example.com",
    "port": 3306,
    "user": "dbuser",
    "password": "dbpassword",
    "name": "production_db"
  }
}

📋 Configuration Reference

Complete Configuration Structure

{
  "log": {
    "enable": true,
    "level": "info"  // "debug" | "info" | "warn" | "error"
  },
  "ssh": {
    "enable": false,
    "host": "bastion.example.com",
    "port": 22,
    "username": "ubuntu",
    "password": "",  // Optional: for password authentication
    "privateKeyPath": "/path/to/key.pem"  // Optional: for key authentication
  },
  "database": {
    "type": "mariadb",  // "mariadb" | "mysql" | "postgresql"
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "name": "mydb",
    // Database-specific options below
    "acquireTimeout": 60000,
    "timeout": 60000,
    "reconnect": true
  }
}

SSH Tunnel Configuration

Required Fields:

• enable: true to enable SSH tunneling
• host: SSH server hostname or IP
• port: SSH server port (default: 22)
• username: SSH username

Authentication:

• privateKeyPath: Path to private key file
• password: SSH password

📋 Supported Database Types

MariaDB

Basic Configuration:

{
  "database": {
    "type": "mariadb",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "name": "mydb"
  }
}

With Connection Options:

{
  "database": {
    "type": "mariadb",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "name": "mydb",
    "acquireTimeout": 60000,
    "timeout": 60000,
    "reconnect": true
  }
}

🔧 MariaDB Version Compatibility

The MDB tool includes intelligent compatibility handling for different MariaDB and MySQL versions:

• ✅ Dynamic Column Detection: Automatically detects available columns in mysql.user table across different versions
• ✅ Version-Safe Queries: Adapts queries based on detected schema to prevent compatibility errors
• ✅ Graceful Fallbacks: Provides sensible defaults when version-specific columns are unavailable

Support Versions:

• MariaDB 10.3+ (including latest versions with enhanced user management features)
• MariaDB 10.2 and earlier (with limited user table schema)
• MySQL 5.7+ (standard user table schema)
• MySQL 8.0+ (enhanced security features)

JSON Output Format

All CLI commands return structured JSON output:

Success Response:

{
  "success": true,
  "data": {
    "version": "10.5.8-MariaDB",
    "server_name": "localhost",
    "character_set": "utf8",
    "collation": "utf8_general_ci",
    "timezone": "SYSTEM",
    "max_connections": 151
  },
  "timestamp": "2025-09-28T22:18:48.219Z"
}

Error Response:

{
  "success": false,
  "error": "Access denied for user 'invalid'@'localhost'",
  "timestamp": "2025-09-28T22:18:48.219Z"
}

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

📋 CLI Commands Reference

| Command | Syntax | Description | Output | | ---------- | ------------------------------ | ----------------------------- | --------------------------------------- | | info | mdb {url} info | Database server information | Server version, charset, timezone, etc. | | users | mdb {url} users | Database users and privileges | User list with permissions | | database | mdb {url} database | List of databases | Database names with charset info | | tables | mdb {url} {db} tables | List tables in database | Table names array | | info | mdb {url} {db} {table} info | Table details | Table structure, size, engine info | | index | mdb {url} {db} {table} index | Table indexes | Index details with columns |

📄 License

MIT License - see LICENSE file for details.