mysql-safe

Type-safe MySQL query builder with adaptive connection pooling, replica routing, and resilience mechanisms for Node.js and TypeScript.

Documentation | Interactive Playground | GitHub Repository

Overview

mysql-safe provides compile-time schema validation and adaptive connection pooling for MySQL applications in TypeScript.

Traditional database drivers and query construction methods frequently suffer from two fundamental issues:

1. Unchecked SQL strings: Schema typos or type mismatches are only discovered at runtime when queries fail in production.
2. Rigid connection pooling: Static connection pools fail to adjust to fluctuating concurrency, causing request queuing during traffic spikes and resource waste during idle periods.

mysql-safe resolves both by combining compile-time type validation with a Proportional-Integral (PI) feedback-controlled connection pooler.

Key Features

• Compile-Time Validation: Column names, table names, and operator comparisons are validated by TypeScript at compile time.
• Adaptive Connection Pool: A Proportional-Integral (PI) controller scales active connections dynamically based on queue depth and wait times.
• Read/Write Splitting: ReplicaPool automatically routes SELECT queries to reader replicas and write mutations to the primary writer.
• Circuit Breaker & Load Shedding: Prevents cascading database failures during outages or high load.
• SingleFlight Deduplication: Coalesces concurrent identical read queries to prevent thundering herd effects.
• Zero ORM Overhead: Generates clean, parameterized SQL with zero reflection or runtime metadata overhead.
• Prepared Statement Caching: LRU caching for query templates reduces parsing and allocation overhead.
• Schema Introspection CLI: Reverse-engineers live MySQL schemas into TypeScript schema definitions.

Installation

npm install mysql-safe mysql2

Requirements

• Node.js >= 18.0.0
• TypeScript >= 5.0.0 (if using TypeScript)
• MySQL >= 5.7 / MySQL 8.0 / MariaDB >= 10.3

Quick Start

1. Define Schema

import type { Schema } from 'mysql-safe';

export const AppSchema = {
  users: {
    id:         { type: 'INT'       as const, primaryKey: true, autoIncrement: true },
    name:       { type: 'VARCHAR'   as const },
    email:      { type: 'VARCHAR'   as const, unique: true },
    age:        { type: 'INT'       as const, nullable: true },
    active:     { type: 'BOOLEAN'   as const, default: true },
    created_at: { type: 'TIMESTAMP' as const },
  },
  orders: {
    id:         { type: 'INT'       as const, primaryKey: true, autoIncrement: true },
    user_id:    { type: 'INT'       as const },
    total:      { type: 'DECIMAL'   as const },
    status:     { type: 'VARCHAR'   as const },
    created_at: { type: 'TIMESTAMP' as const },
  },
} satisfies Schema;

2. Build Type-Safe Queries

import { QueryBuilder } from 'mysql-safe';
import { AppSchema } from './schema.js';

const db = new QueryBuilder(AppSchema);

// Type-safe SELECT query
const { sql, params } = db
  .from('users')
  .columns('id', 'name', 'email')
  .where('active', '=', true)
  .andWhere('age', '>=', 18)
  .orderBy('created_at', 'DESC')
  .limit(10)
  .build();

// Output:
// sql:    SELECT `id`, `name`, `email` FROM `users` WHERE `active` = ? AND `age` >= ? ORDER BY `created_at` DESC LIMIT 10
// params: [true, 18]

3. Initialize Adaptive Connection Pool

import { AdaptivePool } from 'mysql-safe';

const pool = new AdaptivePool(
  {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'my_app',
  },
  {
    minConnections: 10,     // Baseline connection count
    maxConnections: 28,     // Maximum scale ceiling
    targetWaitTimeMs: 15,   // Queue wait threshold
    targetLatencyMs: 30,    // Query latency threshold
    mode: 'pi',             // PI feedback control
    kp: 0.9,                // Proportional gain
    ki: 0.2,                // Integral gain
  }
);

await pool.init();

// Execute query
const rows = await pool.query(sql, params);

// Execute atomic transaction
await pool.transaction(async (tx) => {
  await tx.execute('UPDATE users SET active = ? WHERE id = ?', [false, 1]);
  await tx.execute('INSERT INTO audit_logs (action) VALUES (?)', ['user_deactivated']);
});

// Retrieve pool metrics
const metrics = pool.metrics();
console.log(metrics);

await pool.destroy();

Read/Write Replica Routing

import { ReplicaPool } from 'mysql-safe';

const cluster = new ReplicaPool({
  writer: { host: 'primary.mysql.local', user: 'root', password: '', database: 'app' },
  readers: [
    { host: 'reader-1.mysql.local', user: 'root', password: '', database: 'app' },
    { host: 'reader-2.mysql.local', user: 'root', password: '', database: 'app' },
  ],
  singleFlight: true, // In-flight query deduplication
});

// SELECT statements automatically balance across reader replicas
const users = await cluster.query('SELECT * FROM users WHERE active = ?', [true]);

// INSERT, UPDATE, DELETE queries automatically route to the primary writer
await cluster.query('INSERT INTO users (name, email) VALUES (?, ?)', ['Alice', '[email protected]']);

Schema Introspection CLI

Generate TypeScript schema definitions directly from an existing database:

npx mysql-safe introspect --host localhost --user root --database my_db --output src/schema.ts

Empirical Benchmark Evaluation

Simulated 50 concurrent virtual users executing mixed transaction workloads (50% SELECT, 25% INSERT, 15% UPDATE, 10% DELETE):

| Metric | Static Pool (Fixed 10) | Adaptive Pool (PI 10-28) | Performance Advantage | |:---|:---|:---|:---| | Mean Throughput | 635 txn/sec | 1,672 txn/sec | +163.3% throughput | | Avg Queue Wait Time | 61.5 ms | 7.0 ms | -88.6% wait time reduction | | SQL Syntax Errors | N/A | 0 (Caught at compile-time) | 100% error reduction | | Peak Connections | 10 (Fixed) | 28 (Dynamic) | Elastic auto-scaling |

License

MIT © Romauld Elochukwu