npm package: @koosco/mdb
Description: A cross-database CLI tool for managing MariaDB, MySQL, and PostgreSQL (schemas, indexes, users, etc).
Author: koosco
Latest version: 1.0.0

# 🔧 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:

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

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

#### Available Commands

```bash
# 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):**

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

# List all users in MySQL
mdb mysql://admin:secret@192.168.1.100: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):**

```bash
# 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`:

```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)**

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

**2. Password Authentication**

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

### SSH Troubleshooting

**Common Issues:**

**Connection Timeout**
```bash
# Check SSH connectivity
ssh -i /path/to/key.pem user@bastion.example.com

# Verify network connectivity
ping bastion.example.com
```

**Permission Denied (Private Key)**
```bash
# 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**
```bash
# 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:

```json
{
  "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:**

```json
{
  "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

```json
{
  "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:**
```json
{
  "database": {
    "type": "mariadb",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "name": "mydb"
  }
}
```

**With Connection Options:**
```json
{
  "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:**

```json
{
  "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:**

```json
{
  "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.