db-connect

# db-connect Skill

## When to Use

Use this skill when implementing Node.js code that connects to PostgreSQL or MySQL databases to collect metadata (table sizes, row counts, schema info) without modifying data.

## PostgreSQL Connection with pg

```typescript
import { Pool } from 'pg';

export function createPostgresPool(connectionString: string): Pool {
  return new Pool({
    connectionString,
    max: 3,                  // small pool for read-only monitoring
    idleTimeoutMillis: 10_000,
    connectionTimeoutMillis: 5_000,
    ssl: connectionString.includes('ssl=true') ? { rejectUnauthorized: false } : undefined,
  });
}

export async function collectPostgres(connectionString: string): Promise<TableSizeRow[]> {
  const pool = createPostgresPool(connectionString);
  try {
    const result = await pool.query(`
      SELECT
        schemaname AS schema_name,
        relname AS table_name,
        n_live_tup AS row_count,
        pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname)) AS size_bytes,
        pg_indexes_size(quote_ident(schemaname) || '.' || quote_ident(relname)) AS index_size_bytes,
        pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname)) AS total_size_bytes
      FROM pg_stat_user_tables
      WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
      ORDER BY total_size_bytes DESC
    `);
    return result.rows.map((row) => ({
      schemaName: row.schema_name,
      tableName: row.table_name,
      rowCount: parseInt(row.row_count, 10),
      sizeBytes: parseInt(row.size_bytes, 10),
      indexSizeBytes: parseInt(row.index_size_bytes, 10),
      totalSizeBytes: parseInt(row.total_size_bytes, 10),
    }));
  } finally {
    await pool.end(); // always close -- this is a one-shot collector
  }
}
```

Note: `pg_relation_size` and related functions may return `-1` for tables the current user cannot access. Filter those rows before inserting.

## MySQL Connection with mysql2

```typescript
import mysql from 'mysql2/promise';

export async function collectMysql(connectionString: string): Promise<TableSizeRow[]> {
  const connection = await mysql.createConnection(connectionString);
  try {
    const [rows] = await connection.execute<mysql.RowDataPacket[]>(`
      SELECT
        TABLE_SCHEMA AS schema_name,
        TABLE_NAME AS table_name,
        IFNULL(TABLE_ROWS, 0) AS row_count,
        IFNULL(DATA_LENGTH, 0) AS size_bytes,
        IFNULL(INDEX_LENGTH, 0) AS index_size_bytes,
        IFNULL(DATA_LENGTH + INDEX_LENGTH, 0) AS total_size_bytes
      FROM information_schema.TABLES
      WHERE TABLE_TYPE = 'BASE TABLE'
        AND TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
      ORDER BY total_size_bytes DESC
    `);
    return rows.map((row) => ({
      schemaName: String(row.schema_name),
      tableName: String(row.table_name),
      rowCount: Number(row.row_count),
      sizeBytes: Number(row.size_bytes),
      indexSizeBytes: Number(row.index_size_bytes),
      totalSizeBytes: Number(row.total_size_bytes),
    }));
  } finally {
    await connection.end();
  }
}
```

## Connection String Formats

### PostgreSQL

```
postgresql://user:password@hostname:5432/database
postgresql://user:password@hostname:5432/database?sslmode=require
postgres://user:password@/var/run/postgresql/database  (Unix socket)
```

The `pg` library also accepts an object config:
```typescript
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'monitor',
  password: 'secret',
  database: 'mydb',
  ssl: { rejectUnauthorized: false },
});
```

### MySQL

mysql2 accepts a URL string or config object:
```
mysql://user:password@hostname:3306/database
mysql://user:password@hostname:3306/database?ssl=true
```

Config object:
```typescript
const connection = await mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'monitor',
  password: 'secret',
  database: 'mydb',
  ssl: { rejectUnauthorized: false },
});
```

## Creating a Read-Only Monitoring User

### PostgreSQL

```sql
CREATE USER db_monitor WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO db_monitor;
GRANT USAGE ON SCHEMA public TO db_monitor;
-- pg_stat_user_tables is accessible by default to any connected user
-- but pg_relation_size requires table visibility
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_monitor;
-- For cross-schema monitoring:
GRANT USAGE ON SCHEMA analytics TO db_monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO db_monitor;
```

### MySQL

```sql
CREATE USER 'db_monitor'@'%' IDENTIFIED BY 'secure_password';
-- information_schema is readable without grants on regular schemas
GRANT SELECT ON information_schema.TABLES TO 'db_monitor'@'%';
GRANT SELECT ON information_schema.SCHEMATA TO 'db_monitor'@'%';
FLUSH PRIVILEGES;
```

## Testing a Connection

```typescript
export async function testConnection(
  type: 'postgres' | 'mysql',
  connectionString: string
): Promise<{ ok: boolean; latencyMs: number; error?: string }> {
  const start = Date.now();
  try {
    if (type === 'postgres') {
      const pool = createPostgresPool(connectionString);
      await pool.query('SELECT 1');
      await pool.end();
    } else {
      const conn = await mysql.createConnection(connectionString);
      await conn.execute('SELECT 1');
      await conn.end();
    }
    return { ok: true, latencyMs: Date.now() - start };
  } catch (err) {
    return {
      ok: false,
      latencyMs: Date.now() - start,
      error: err instanceof Error ? err.message : String(err),
    };
  }
}
```

## SSL / TLS Notes

For managed cloud databases (RDS, CloudSQL, PlanetScale), TLS is required. Set the SSL mode in the connection string or config:

- PostgreSQL: append `?sslmode=require` or `?sslmode=verify-full`
- MySQL: set `ssl: true` in the config object or `?ssl=true` in the URL

If the database uses a self-signed certificate and you trust the network, use `rejectUnauthorized: false`. For production, provide the CA certificate:

```typescript
const pool = new Pool({
  connectionString,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/path/to/ca-cert.pem').toString(),
  },
});
```

## Error Handling

Common errors and their causes:

| Error | Cause |
|-------|-------|
| `ECONNREFUSED` | Wrong host or port, or database not running |
| `ETIMEDOUT` | Network unreachable or firewall blocking the port |
| `ENOTFOUND` | DNS resolution failed for the hostname |
| `password authentication failed` | Wrong username or password |
| `database "mydb" does not exist` | Database name typo or not created |
| `SSL SYSCALL error` | TLS mismatch -- try adding `sslmode=require` or removing SSL config |

Always catch and log the full error message, and store it in `databases.last_error` so the dashboard can surface connection problems.