MS mysql-safe v0.1.1
Docs / Getting Started / Introduction

Introduction

mysql-safe is a lightweight, zero-reflection, type-safe SQL query builder and load-aware adaptive connection pooler for MySQL in TypeScript.

Interactive Playground: You can test queries directly in your browser with the Visual Interactive Playground with live SQL compilation and dynamic pool simulator.

Traditional Node.js MySQL workflows suffer from two primary issues:

  1. Fragile string concatenation: Typos in table or column names slip through to production, causing runtime crashes.
  2. Static connection pools: Fixed pool sizes fail to adapt to fluctuating traffic, causing queue saturation during spikes and wasted resources during idle periods.

Why mysql-safe?

Feature Raw mysql2 Prisma / TypeORM mysql-safe
Compile-Time Type Safety No Yes (Heavy runtime) Yes (Zero overhead)
Adaptive Connection Pool Static fixed size Static fixed size PI Dynamic Scaling
Query Deduplication No No SingleFlight Built-in
Circuit Breaker No No 3-State Protection
Read/Write Replica Splitting Manual Complex plugins Automated CQRS

Installation

Terminal 
npm install mysql-safe mysql2

Quick Start

Define your schema and run your first type-safe query:

app.ts 
import { QueryBuilder, AdaptivePool, type Schema } from 'mysql-safe';

// 1. Define Database Schema
const AppSchema = {
  users: {
    id: { type: 'INT', primaryKey: true, autoIncrement: true },
    name: { type: 'VARCHAR' },
    email: { type: 'VARCHAR', unique: true },
    active: { type: 'BOOLEAN', default: true },
  },
  orders: {
    id: { type: 'INT', primaryKey: true },
    user_id: { type: 'INT' },
    total: { type: 'DECIMAL' },
  }
} satisfies Schema;

// 2. Initialize QueryBuilder and Adaptive Pool
const db = new QueryBuilder(AppSchema);
const pool = new AdaptivePool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'my_store',
  minConnections: 10,
  maxConnections: 28,
  targetWaitTimeMs: 15,
});

await pool.init();

// 3. Build and Execute Type-Checked Query
const { sql, params } = db.from('users')
  .columns('id', 'name', 'email')
  .where('active', '=', true)
  .orderBy('id', 'DESC')
  .limit(10)
  .build();

const rows = await pool.query(sql, params);
console.log(rows);

Schema Definition

The schema definition tells TypeScript the structure of your database:

schema.ts
export const StoreSchema = {
  products: {
    id: { type: 'INT', primaryKey: true },
    title: { type: 'VARCHAR' },
    price: { type: 'DECIMAL' },
    in_stock: { type: 'BOOLEAN' },
    created_at: { type: 'TIMESTAMP' },
  }
} satisfies Schema;

SELECT Queries

Use db.from(table) to initiate a SelectBuilder:

const { sql, params } = db.from('products')
  .distinct()
  .columns('id', 'title', 'price')
  .where('in_stock', '=', true)
  .andWhere('price', '<=', 100.00)
  .orderBy('price', 'ASC')
  .limit(20)
  .offset(40)
  .build();

UPDATE with Safety Guard

Safety Protection: Calling .build() on an UPDATE or DELETE without a .where() clause throws a safety error to prevent accidental whole-table overwrites. 
const { sql, params } = db.update('users')
  .set({ active: false })
  .where('id', '=', 42)
  .build();

Adaptive Connection Pool and PI Controller

AdaptivePool uses a Proportional-Integral (PI) feedback control loop to dynamically adjust connection capacity based on request wait times and backlog queue depth. 

const pool = new AdaptivePool({
  minConnections: 10,
  maxConnections: 28,
  targetWaitTimeMs: 15,
  scaleUpThreshold: 0.75,
  scaleIntervalMs: 1000,
});

Read/Write Splitting and Replica Balancing

Automated CQRS routing splits queries across writer primaries and reader replicas:

import { ReplicaPool } from 'mysql-safe';

const cluster = new ReplicaPool({
  writer: writerConfig,
  readers: [reader1Config, reader2Config, reader3Config],
  singleFlight: true,
});

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

// INSERT / UPDATE queries automatically route to the primary writer
await cluster.query('INSERT INTO orders (user_id, total) VALUES (?, ?)', [1, 99.99]);

Schema Introspection CLI

Extract TypeScript schemas directly from existing MySQL databases:

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

Empirical Benchmark Results

Evaluated using a 50-concurrent virtual user e-commerce transaction simulation:

Metric Static Pool (Fixed 10) Adaptive Pool (PI 10-28) Improvement
Throughput (TPS) 635 txn/sec 1,672 txn/sec +163.3%
Avg Queue Wait Time 61.5 ms 7.0 ms -88.6%
SQL Syntax Errors Vulnerable 0 (100% caught at compile-time) 100% Reduction
ESC