chill-sql

Type-safe MySQL query builder for TypeScript. Talks to your database via mysql2, keeps your table schemas honest at compile time, and never pollutes globals.

• Type-safe SQL building — schema-driven select / where / update / insert / delete / join with full inference for selected columns, conditions, ordering and aliases.
• IDE-friendly — works out of the box with VS Code, WebStorm and any other TypeScript-aware editor.
• Zero global side-effects — no Array.prototype patching, no implicit logging.
• Transactions — runTransaction with automatic rollback + release.
• Pluggable SQL logger — silent by default; opt in via mysqlTool.setSqlLogger(fn).

Installation

npm install chill-sql
# or
pnpm add chill-sql
# or
yarn add chill-sql

Requirements:

• Node.js ≥ 18
• TypeScript ≥ 4.7 (tested against 5.8)
• mysql2 is a runtime dependency

Quick start

import { createMysqlClient } from 'chill-sql'

// 1. Declare your tables
type User = {
    user_id: string
    user_name: string
    user_phone?: string
    user_company?: string
    user_height?: number
}

type UserGroup = {
    group_id: string
    group_name: string
    group_owner_id: string
    group_created_time: number
}

type MyTables = {
    user: User
    user_group: UserGroup
}

// 2. Create a client (mysql2 PoolOptions)
const mysqlClient = createMysqlClient<MyTables>({
    host: '127.0.0.1',
    user: 'root',
    port: 3306,
    password: 'whosyourdaddy',
    database: 'test',
    charset: 'utf8mb4_unicode_ci',
    connectTimeout: 8000,
})

Select

// users: User[]
const users = await mysqlClient.tables.user.select('*')
    .where(e => e.user_company.eq('doge'))
    .and(e => e.user_height.gte(166))
    .exec()

// names: Pick<User, 'user_name'>[]
const names = await mysqlClient.tables.user.select('user_name')
    .where(e =>
        e.user_company.eq('doge')
            .or(e.user_company.eq('cate')),
    )
    .and(e => e.user_height.gte(166))
    .exec()

// total: number
const total = await mysqlClient.tables.user.count().exec()

where(undefined) is a no-op so conditions can be wired in dynamically without fragile if chains.

match shorthand

// equivalent to: where(e => e.user_company.eq('doge').and(e.user_height.eq(180)))
await mysqlClient.tables.user.select('*')
    .match({ user_company: 'doge', user_height: 180 })
    .exec()

NULL semantics

eq(null) emits IS NULL and ne(null) emits IS NOT NULL. Passing null into insert / update writes a real SQL NULL; passing undefined skips the field altogether.

Join

// Array<UserGroup & { ownerName: string }>
const joined = await mysqlClient.tables.user_group.as('g')
    .join('user as u')
    .on(it => it.u.user_id.eq(it.g.group_owner_id))
    .select('g.*', 'u.user_name as ownerName')
    .where(it => it.u.user_company.eq('doge'))
    .orderBy('g.group_created_time asc')
    .exec()

// Array<{ ownerName: string, groupCount: number }>
const grouped = await mysqlClient.tables.user_group.as('g')
    .join('user')
    .on(it => it.user.user_id.eq(it.g.group_owner_id))
    .select(
        'user.user_name as ownerName',
        'count(g.group_id) as groupCount',
    )
    .orderBy('g.group_created_time asc')
    .having(it => it.groupCount.gte(3))
    .exec()

leftJoin(...) is available with the same shape.

Insert / Update / Delete

await mysqlClient.tables.user.insert({
    user_id: 'Enoch',
    user_name: 'EnochL',
    user_company: 'doge',
}).exec()

// insert ... on duplicate key update
await mysqlClient.tables.user.insert({
    user_id: 'Enoch',
    user_name: 'EnochL',
}).orUpdate({ user_name: 'EnochL2' }).exec()

await mysqlClient.tables.user
    .update({ user_company: 'capsule' })
    .where(e => e.user_id.eq('Enoch'))
    .exec()

await mysqlClient.tables.user
    .delete()
    .where(e => e.user_id.eq('xxx'))
    .exec()

update and delete refuse to execute without a where clause to prevent accidental full-table writes.

Transactions

await mysqlClient.runTransaction(async client => {
    await client.tables.user.insert({
        user_id: 'Enoch',
        user_name: 'EnochL',
        user_company: 'doge',
        user_height: 180,
    }).exec()
    await client.tables.user.insert({
        user_id: 'somebody',
        user_name: 'whoknows',
        user_company: 'doge',
        user_height: 166,
    }).exec()
})

If the callback throws (or any query inside it rejects), the transaction rolls back automatically. The pooled connection is always released, even if rollback itself fails.

Raw SQL & named-arg substitution

await mysqlClient.exec('select 1')

await mysqlClient.execNamedArgsSql(
    'select * from user where user_id = :id and user_height > :h',
    { id: 'Enoch', h: 100 },
)

:name placeholders are substituted with properly escaped literals. null values become NULL. Missing keys are left untouched.

SQL logging

chill-sql does not write anything to stdout. To see queries (e.g. during development) inject a logger:

import { mysqlTool } from 'chill-sql'

mysqlTool.setSqlLogger(sql => console.debug('[sql]', sql))

// disable again
mysqlTool.setSqlLogger(null)

Logger faults are swallowed and never break query execution.

Cleanly closing the pool

await mysqlClient.end()

Testing

Tests use the built-in Node.js test runner (node:test) and tsx:

pnpm test

The mock-pool pattern used inside src/__tests__/ is a useful blueprint if you want to write your own assertions against the generated SQL.

Migrating from v1.x

v2.0.0 is a breaking release:

• mysql was replaced with mysql2. createMysqlClient now takes mysql2's PoolOptions — most notably, the legacy timeout field was renamed to connectTimeout.
• chill-sql no longer monkey-patches Array.prototype. If you were relying on array helpers, import arrayUtil from chill-sql or switch to ES-native alternatives.
• Query execution is silent unless you call mysqlTool.setSqlLogger(...).
• MysqlClient#exec(sql, params) (v1) is now MysqlClient#execNamedArgsSql(sql, params). exec(sql) exists but no longer accepts a params object.

License

MIT.

Issues and support

Found a bug or have a question? Please open an issue.

If chill-sql is useful to you, consider starring the project on GitHub — it helps a lot. ⭐