AI DBA

Universal database copilot — diagnostics, operations, and performance analysis via MCP and CLI.

Features

• MCP Server — expose database diagnostics as tools for AI agents (Hermes, Claude Code, etc.)
• CLI — one-off commands for scripting and automation
• Interactive REPL — explore your databases interactively with standard DBA commands
• Connection URLs — connect via mysql://, postgresql://, sqlserver://, oracle://, or mongodb:// URLs (no config file needed)
• Database-agnostic commands — databases, tables, describe, indexes, processes work across engines
• Multi-engine support — MySQL, PostgreSQL, SQL Server, Oracle, and MongoDB connectors
• MySQL blocking chains — detect and report row-level blocking with full query details
• Table sizes — list table/collection sizes with human-readable formatting (table-sizes)
• Explain plans — execution plan analysis with optional --analyze flag (PostgreSQL executes the query)
• Slow queries — surface slow query data from engine internals (performance_schema, pg_stat_statements, sys.dm_exec_query_stats, V$SQLAREA, currentOp)
• Health checks — orchestrate connectivity, blocking, processes, and slow queries into one status call
• SQL guard — shared validation module rejects destructive SQL before it reaches connectors
• GitHub Actions CI — build + test on Node 20/22, runs on every push/PR to main
• Documentation site — MkDocs Material with 8 pages, light/dark mode, search

Documentation

Full docs site live at: https://steveramos21.github.io/ai-dba/

To build docs locally:

python3 -m venv .venv
.venv/bin/pip install -r requirements-docs.txt
.venv/bin/mkdocs serve
# Open http://127.0.0.1:8000

Quick Start

1. Install dependencies

npm install

2. Build

npm run build

3. Connect and explore

Option A — Config file (for multiple engines):

cp config.yaml.example config.yaml
# Edit config.yaml with your database credentials
npm run repl

Option B — Connection URL (no config file needed):

node dist/index.js repl
# Then: connect mysql://root:[email protected]:3306/mydb

Option C — Connect via URL (drops into REPL):

# MySQL
npm run connect -- 'mysql://root:***@127.0.0.1:3306/mydb'

# PostgreSQL
npm run connect -- 'postgresql://postgres:***@127.0.0.1:5432/mydb'
# Connects and opens interactive REPL

Commands

Command	Description
serve	Start MCP server over stdio (for AI agents)
list-engines	List configured database engines
blocking-chains <engineId>	Show current blocking chains
table-sizes <engineId> [database]	List table sizes with human-readable formatting
explain <engineId> <query> [-a]	Show execution plan (add -a for ANALYZE)
slow-queries <engineId> [--limit N] [--min-duration-ms N]	List slow queries from engine internals
health-check <engineId>	Run health check (connectivity, blocking, processes, slow queries)
connect <url>	Connect to a database via URL
repl	Interactive REPL for database diagnostics

Global Options

Option	Description	Default
-c, --config <path>	Path to config.yaml	config.yaml
-V, --version	Show version	—
-h, --help	Show help	—

connect <url>

Connect to a database via URL and open an interactive REPL. No config file needed. Supports mysql://, postgresql://, and postgres:// URL schemes.

# MySQL
npm run connect -- 'mysql://root:***@127.0.0.1:3306/mydb'

# PostgreSQL
npm run connect -- 'postgresql://postgres:***@127.0.0.1:5432/mydb'

# With --type override
npm run connect -- 'postgresql://user:***@host:5432/db' --type postgres

This connects, verifies the connection, then drops you into the REPL where you can run databases, tables, describe, indexes, processes, etc.

blocking-chains

ai-dba blocking-chains <engineId>       # Table output
ai-dba blocking-chains <engineId> --json # JSON output

Detects blocking chains across MySQL and PostgreSQL. Returns a list of blocking chain entries:

Field	Description
engine_id	Engine identifier from config
blocking_pid	Process ID holding the lock
blocked_pid	Process ID waiting for the lock
wait_duration_ms	How long the blocked session has been waiting
wait_event	Wait event name (engine-specific)
blocking_query	SQL statement holding the lock
blocked_query	SQL statement waiting for the lock
database_name	Database context
wait_type	Wait type classification
status	Session status
host_name	Client host address
program_name	Client program name
login_time	Session login timestamp

MySQL uses INNODB_LOCK_WAITS + INNODB_TRX + performance_schema.threads (4-join query). wait_type and program_name are NULL for MySQL (not exposed in the query).

PostgreSQL uses pg_blocking_pids() + pg_stat_activity with query_start for wait duration. All 12 fields are populated natively.

Error cases:

• Unknown engine ID → Unknown engine "x". Available: mysql-primary
• Unsupported engine type → error from the connector's getBlockingChains() method

repl

ai-dba repl                # Uses config.yaml
ai-dba repl                # No config — start empty, use connect

Interactive commands:

Command	Alias	Description
help		Show available commands
connect <url>		Connect to a database via URL
databases	db	List databases on the server
tables	dt	List tables (with rows, size, engine)
describe <table>	desc	Show column details (type, nullable, key, default)
indexes <table>	idx	List indexes on a table
processes	ps	Show active connections/processes
engines	ls	List configured engines (current marked with *)
use <engineId>		Switch to a different engine
status	s	Show connection details for current engine
blocking-chains	bc	Show blocking chains on current engine
sql <statement>		Run a raw SQL query (escape hatch)
quit	q, exit	Exit the REPL

SQL keywords are auto-detected — just type SHOW DATABASES or SELECT * FROM users directly.

Example session:

AI-DBA REPL — type 'help' for commands
No engines configured. Use: connect <url>

ai-dba[no-engine]> connect mysql://root:[email protected]:13306/testdb
Connected to 127.0.0.1-testdb
  mysql://root:***@127.0.0.1:13306/testdb

ai-dba[127.0.0.1-testdb]> databases
┌────────────────────┐
│ Database           │
├────────────────────┤
│ information_schema │
│ testdb             │
└────────────────────┘
2 database(s)

ai-dba[127.0.0.1-testdb]> tables
┌────────────────┬──────┬─────────┬─────────┬─────────────────────┐
│ Table          │ Rows  │ Size    │ Engine  │ Collation           │
├────────────────┼──────┼─────────┼─────────┼─────────────────────┤
│ blocking_test  │ 3     │ 16.0 KB │ InnoDB  │ utf8mb4_0900_ai_ci  │
└────────────────┴──────┴─────────┴─────────┴─────────────────────┘
1 table(s)

ai-dba[127.0.0.1-testdb]> describe blocking_test
┌─────────┬──────────────┬──────┬─────┬─────────┬────────────────┐
│ Column  │ Type         │ Null │ Key │ Default  │ Extra          │
├─────────┼──────────────┼──────┼─────┼─────────┼────────────────┤
│ id      │ int          │ NO   │ PRI │ NULL     │ auto_increment │
│ name    │ varchar(100) │ YES  │     │ NULL     │                │
│ value   │ int          │ YES  │     │ NULL     │                │
└─────────┴──────────────┴──────┴─────┴─────────┴────────────────┘

ai-dba[127.0.0.1-testdb]> processes
┌──────┬──────┬──────────────────────┬─────────┬─────────┬──────┬─────────────┐
│ PID  │ User │ Host                 │ DB      │ Command │ Time │ State       │
├──────┼──────┼──────────────────────┼─────────┼─────────┼──────┼─────────────┤
│ 5    │ root │ 172.23.0.1:54312     │ testdb  │ Query   │ 0s   │ starting    │
└──────┴──────┴──────────────────────┴─────────┴─────────┴──────┴─────────────┘
1 process(es)

serve

Starts an MCP server over stdio. Used by AI agents to call database diagnostics tools.

MCP configuration (e.g., ~/.hermes/config.yaml):

{
  "mcpServers": {
    "ai-dba-diagnostics": {
      "command": "node",
      "args": ["/path/to/ai-dba/dist/index.js", "serve", "--config", "/path/to/ai-dba/config.yaml"]
    }
  }
}

The server exposes six tools:

Tool	Parameters	Description
blocking-chains	engineId (string, required)	Show current blocking chains
databases	engineId (string, required)	List databases/schemas on the server
tables	engineId (string, required), database (string, optional)	List tables in a database/schema
describe-table	engineId (string, required), table (string, required), database (string, optional)	Show column metadata for a table
indexes	engineId (string, required), table (string, required), database (string, optional)	List indexes on a table
processes	engineId (string, required)	List active database connections/processes
table-sizes	engineId (string, required), database (string, optional)	List table sizes with data/index/total breakdown
explain	engineId (string, required), query (string, required), analyze (boolean, optional)	Show execution plan for a query
slow-queries	engineId (string, required), limit (int, optional), minDurationMs (int, optional)	List slow queries from engine internals
health-check	engineId (string, required)	Run health check (connectivity, blocking, processes, slow queries)

All tools return JSON. The database parameter overrides the engine's configured database (MySQL) or schema (PostgreSQL).

Docker Test Environment

A Docker Compose file is included for local testing with MySQL 8.0 and PostgreSQL 16.

Start databases

docker compose up -d

Wait until healthy:

docker inspect --format='{{.State.Health.Status}}' ai-dba-mysql-test
docker inspect --format='{{.State.Health.Status}}' ai-dba-postgres-test
# Repeat until both show "healthy"

Seed MySQL test data

docker exec ai-dba-mysql-test mysql -uroot -ptestpassword testdb \
  -e "CREATE TABLE IF NOT EXISTS blocking_test (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), value INT); INSERT IGNORE INTO blocking_test (name, value) VALUES ('alpha', 1), ('beta', 2), ('gamma', 3);"

Create a test config

cp config.yaml.example config.yaml

The example config points to both Docker MySQL on port 13306 and PostgreSQL on port 15432.

Test blocking detection

MySQL blocking scenario:

node test/test-blocking.mjs

PostgreSQL blocking scenario:

# Seed test data first
docker exec ai-dba-postgres-test psql -U postgres -d testdb \
  -c "CREATE TABLE IF NOT EXISTS blocking_test (id SERIAL PRIMARY KEY, value INT); INSERT INTO blocking_test (value) VALUES (1) ON CONFLICT DO NOTHING;"

# Run the test
node test/test-blocking-postgres.mjs

Stop databases

docker compose down

Add -v to also delete the data volume.

Integration tests (require Docker)

# All connector methods against live MySQL + PostgreSQL (49 tests)
npm run test:integration

# Live blocking scenarios — creates real locks, validates detection (21 tests)
npm run test:blocking

These tests catch bugs that mocked unit tests cannot — they exercise real SQL against MySQL 8.0 and PostgreSQL 16.

Configuration

config.yaml format:

Connection URL (recommended)

engines:
  mysql-prod:
    type: mysql
    url: mysql://readonly:***@prod-db.internal:3306/app_db?ssl=true

  postgres-prod:
    type: postgres
    url: postgresql://readonly:***@prod-db.internal:5432/app_db?sslmode=require

The url field takes priority over individual fields. MySQL supports additional URL params:

• ssl=true — enable SSL with certificate verification
• ssl={"rejectUnauthorized":false} — custom SSL options (JSON)
• connectionLimit=10 — pool size (default: 5)

PostgreSQL URLs are passed directly to pg.Pool({ connectionString }), so any pg-supported parameter works (sslmode, connect_timeout, etc.).

Individual fields (legacy)

engines:
  <engine-id>:
    type: mysql          # or postgres, sqlserver, oracle, mongodb
    host: 127.0.0.1      # Hostname or IP (MySQL only)
    port: 3306            # Port (MySQL only)
    user: root            # Database user (MySQL only)
    password: secret      # Password (MySQL only)
    database: mydb        # Default database (MySQL only)

Note: Individual fields are only supported for MySQL. PostgreSQL requires a connection URL (url field).

Multiple engines are supported:

engines:
  mysql-prod:
    type: mysql
    url: mysql://readonly:***@prod-db.internal:3306/app_db?ssl=true
  mysql-staging:
    type: mysql
    host: staging-db.internal
    port: 3306
    user: readonly
    password: ${MYSQL_STAGING_PASSWORD}
    database: app_db
  postgres-analytics:
    type: postgres
    url: postgresql://readonly:***@analytics-db.internal:5432/warehouse?sslmode=require

Security: Add config.yaml to .gitignore (already included by default).

Architecture

src/
  index.ts              CLI entry point (commander, REPL)
  server.ts             MCP server setup + connector map
  config.ts             YAML config loader with URL parsing
  connector.ts          DatabaseConnector interface + shared types (BlockingChain, TableSizeInfo, ExplainResult, SlowQueryInfo, HealthCheckResult)
  sql-guard.ts          Shared SQL validation (validateReadOnlySql, validateExplainQuery, isJsonCommand)
  connectors/
    mysql.ts            MySQLConnector (implements DatabaseConnector)
    postgres.ts         PostgreSQLConnector (implements DatabaseConnector)
    sqlserver.ts        SqlServerConnector (implements DatabaseConnector)
    oracle.ts           OracleConnector (implements DatabaseConnector)
    mongodb.ts          MongoDbConnector (implements DatabaseConnector)
  tools/
    blocking-chains.ts  MCP tool — blocking chain diagnostics
    databases.ts        MCP tool — list databases/schemas
    tables.ts           MCP tool — list tables
    describe-table.ts   MCP tool — column metadata
    indexes.ts          MCP tool — list indexes
    processes.ts        MCP tool — active processes/connections
    table-sizes.ts      MCP tool — table size breakdown
    explain.ts          MCP tool — execution plans
    slow-queries.ts     MCP tool — slow query analysis
    health-check.ts     MCP tool — orchestrated health check

• DatabaseConnector interface — 10 methods: listDatabases, listTables, describeTable, listIndexes, listProcesses, query, getBlockingChains, listTableSizes, explainQuery, listSlowQueries. All 5 engines implement the interface.
• sql-guard.ts — shared validation module used by CLI, REPL, and MCP tool paths. Rejects destructive SQL (INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, CREATE, MERGE, GRANT, REVOKE) using \b word-boundary regex.
• Lazy imports — MCP SDK, mysql2, and pg are loaded dynamically only when needed. CLI commands like list-engines start instantly without loading database drivers.
• Lazy connection pools — Database connections are created on first use, not at startup.
• One tool per file — each src/tools/*.ts file is self-contained (schema + handler). Adding a new tool means adding a new file and registering it in server.ts.
• Graceful degradation — slow-queries and explain return empty results when engine features are unavailable (extension not installed, permission denied) rather than throwing errors.
• GitHub Actions CI — .github/workflows/ci.yml runs build + unit tests on Node 20/22 for every push/PR to main.

Requirements

• Node.js 18+
• MySQL 8.0+ (with performance_schema enabled, which is the default)
• PostgreSQL 12+ (for PostgreSQL connector)

License

MIT