@abinashpatri/mysql

Production-focused MySQL utility helpers for Node.js applications using mysql2/promise.

Why this package

This package gives you a small, practical MySQL toolkit with:

• pooled connections per database key
• built-in retry for transient query failures
• transaction helper with auto rollback
• health checks for one or many configured databases
• TypeScript-ready APIs and declaration files

Features

• Multi-database configuration
  Define multiple database connections (for example primary, analytics, readReplica) and query each by name.

• Connection pooling
  Uses mysql2 pool with sensible defaults (waitForConnections, connectionLimit, queueLimit, keep-alive, timeout).

• Retry support for queries
  query() supports retry options: retries, delay, and exponential factor.

• Slow query warning
  Logs a warning when query execution takes longer than 500ms.

• Safe transaction wrapper
  transaction() automatically:

  • begins transaction
  • commits on success
  • rolls back on error
  • releases connection in all cases

• Health checks
  Check one DB (checkHealth) or all configured DBs (checkAllHealth).

Install

npm install @abinashpatri/mysql mysql2

Quick start

import { mysql } from "@abinashpatri/mysql";

mysql.setDatabaseConfig({
  primary: {
    host: "127.0.0.1",
    port: 3306,
    user: "root",
    password: "secret",
    database: "app_db",
  },
});

const rows = await mysql.query("primary", "SELECT 1 AS ok");
console.log(rows);

Complete demo

import { mysql } from "@abinashpatri/mysql";
import type { RowDataPacket } from "mysql2";

// 1) Configure one or more databases
mysql.setDatabaseConfig({
  primary: {
    host: "127.0.0.1",
    port: 3306,
    user: "root",
    password: "secret",
    database: "app_db",
    connectionLimit: 20,
    queueLimit: 100,
    connectTimeout: 10000,
  },
  analytics: {
    host: "127.0.0.1",
    user: "analytics_user",
    password: "analytics_secret",
    database: "analytics_db",
  },
});

// 2) Typed SELECT query
type UserRow = RowDataPacket & {
  id: number;
  email: string;
  active: number;
};

const users = await mysql.query<UserRow[]>(
  "primary",
  "SELECT id, email, active FROM users WHERE active = ?",
  [1],
);

// 3) Insert with retry strategy
await mysql.query(
  "primary",
  "INSERT INTO audit_logs(action, created_at) VALUES(?, NOW())",
  ["login"],
  {
    retry: {
      retries: 3,
      delay: 200,
      factor: 2,
    },
  },
);

// 4) Transaction example
await mysql.transaction("primary", async (conn) => {
  await conn.execute(
    "UPDATE wallets SET balance = balance - ? WHERE user_id = ?",
    [100, 1],
  );
  await conn.execute(
    "UPDATE wallets SET balance = balance + ? WHERE user_id = ?",
    [100, 2],
  );
});

// 5) Health checks
const primaryHealth = await mysql.checkHealth("primary");
const allHealth = await mysql.checkAllHealth();
console.log({ primaryHealth, allHealth });

API reference

setDatabaseConfig(configs)

Registers one or more DB configurations.

mysql.setDatabaseConfig({
  primary: {
    host: "127.0.0.1",
    user: "root",
    password: "secret",
    database: "app_db",
  },
});

query(dbName, sql, params?, options?)

Executes a query using the named pool.

• dbName: string - configured database key
• sql: string - SQL statement
• params?: unknown[] - query parameters
• options?: { retry?: { retries?: number; delay?: number; factor?: number } }

Returns a promise of query rows (generic type supported).

transaction(dbName, callback)

Runs callback inside a transaction with auto commit/rollback.

• dbName: string
• callback: (conn) => Promise<T>

Returns the callback result T.

checkHealth(dbName)

Checks single DB by running SELECT 1.

Returns:

{
  db: string;
  status: "healthy" | "unhealthy";
  error?: string;
}

checkAllHealth()

Checks all configured DBs and returns an array of health statuses.

Scripts

• npm run build - Build CJS, ESM, and declaration files.
• npm run typecheck - Run TypeScript checks (tsc --noEmit).
• npm run dev - Watch mode build.

Production notes

• Call setDatabaseConfig() once during app startup before first query.
• Keep DB credentials in environment variables (do not hardcode secrets).
• Use separate DB users with least privilege for each environment.
• Keep retries conservative to avoid amplifying load during outages.

License

MIT