db-connect

name	db-connect
description	Patterns for connecting to PostgreSQL and MySQL databases in Node.js, including connection pooling, read-only users, and schema introspection queries for size and row count collection
triggers	["postgres connection node","mysql connection node","pg pool","mysql2 promise","database introspection","pg_stat_user_tables","information_schema tables"]

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

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

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),
    }));
  }  {
     connection.();
  }
}

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:

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:

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

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

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

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.() - start,
      : err   ? err. : (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:

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.