Multi-Engine SQL Client

A lightweight multi-engine SQL client for querying and test automation — one API across PostgreSQL, MySQL/MariaDB, SQLite, SQL Server, and Oracle, with a fluent query builder, typed results, and always-parametrised queries.

This package is the data layer: a programmatic SQL facade for humans + LLM agents to write against. It is the sibling of @civitas-cerebrum/wasapi (HTTP); together they back the Steps API in @civitas-cerebrum/element-interactions. If you are writing E2E test specs through that framework, use its steps.sql* wrappers — this package is what powers them, and what you reach for in fixtures, seed scripts, harness internals, and standalone tools.

🏗️ One API, Five Engines

Every database driver has its own connection ritual, placeholder style, and result shape. Multi-engine code normally means five code paths.

Before (raw drivers):

// pg: Pool, $1 placeholders
const pool = new Pool({ connectionString });
const res = await pool.query('SELECT * FROM books WHERE genre = $1', ['Fiction']);

// mysql2: createPool, ? placeholders, [rows, fields] tuples
const [rows] = await mysqlPool.query('SELECT * FROM books WHERE genre = ?', ['Fiction']);

// mssql: request objects with named .input() bindings
const r = await pool.request().input('p1', 'Fiction')
  .query('SELECT * FROM books WHERE genre = @p1');

// oracledb: outFormat option, UPPERCASE column keys, manual connection release
const conn = await pool.getConnection();
const result = await conn.execute('SELECT * FROM books WHERE genre = :1', ['Fiction'],
  { outFormat: oracledb.OUT_FORMAT_OBJECT });
await conn.close();

After (@civitas-cerebrum/sql-client):

// Same client, same call, any engine — detected from the connection string.
const db = new SqlClient({ connectionString: process.env.DB_URL! });
const res = await db.query('SELECT * FROM books WHERE genre = $1', ['Fiction']);

// Or fully portable: the builder renders engine-correct placeholders,
// quoting, and pagination from one chain.
const top = await QueryBuilder.select('books')
  .where('genre = ?', 'Fiction')
  .orderBy('price', 'desc')
  .limit(5)
  .run(db);

📦 Installation

Install the package plus the driver(s) for your engine(s) — drivers are optional peer dependencies, so you pull only the ones you use (nothing is auto-installed, including for packages that depend on sql-client):

# PostgreSQL
npm install @civitas-cerebrum/sql-client pg

# MySQL / MariaDB
npm install @civitas-cerebrum/sql-client mysql2

# SQLite (zero infrastructure)
npm install @civitas-cerebrum/sql-client better-sqlite3

# SQL Server
npm install @civitas-cerebrum/sql-client mssql

# Oracle (Thin mode — no Instant Client needed)
npm install @civitas-cerebrum/sql-client oracledb

Need several engines? List their drivers together (npm install @civitas-cerebrum/sql-client pg mysql2). Importing the package never loads a driver; it's required on first use, and a missing one throws a clear UnsupportedEngineException naming the driver to install.

Requirements: Node.js ≥ 20.

| Engine | Connection-string scheme(s) | Driver | Placeholders | returning() | |---------------------|----------------------------------------------------|-------------------|--------------|---------------| | PostgreSQL | postgres:// · postgresql:// | pg | $1 | ✅ RETURNING | | MySQL / MariaDB | mysql:// · mariadb:// | mysql2 | ? | ❌ | | SQLite | sqlite: · sqlite::memory: · file: · :memory: · *.db · *.sqlite | better-sqlite3 | ? | ✅ RETURNING | | SQL Server | mssql:// · sqlserver:// | mssql | @p1 | ✅ OUTPUT | | Oracle | oracle:// · oracledb:// | oracledb | :1 | ❌ |

🚀 Quick Start

Zero infrastructure — an in-memory SQLite database, no Docker, no server. Copy-paste and run:

import { SqlClient, QueryBuilder } from '@civitas-cerebrum/sql-client';

const db = new SqlClient({ engine: 'sqlite', connectionString: ':memory:' });

// Create a table (runScript splits a multi-statement script per engine)
await db.runScript(`
  CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL, in_stock INTEGER);
`);

// Insert (multi-row in one statement)
await QueryBuilder.insert('books')
  .values([
    { title: 'Dune', price: 12.99, in_stock: true },
    { title: '1984', price: 9.5,  in_stock: false },
  ])
  .run(db);

// Read straight into a typed, coercing ResultSet
const cheap = await QueryBuilder.select('books').where('price < ?', 10).fetch(db);
console.log(cheap.column('title'));            // ['1984']

const dune = await db.fetch('SELECT * FROM books WHERE title = ?', ['Dune']);
console.log(dune.one().number('price'));       // 12.99
console.log(dune.one().boolean('in_stock'));   // true  (1 → true)

await db.end();

✨ Features

• Engine auto-detection — The engine is inferred from the connection-string scheme; pass { engine } explicitly only when there's no scheme to read.
• Optional, lazily-loaded drivers — Drivers are optional peer deps: install only the engine(s) you use; nothing is forced on you (or on packages that depend on sql-client). Each is required on first client construction, and a missing one throws UnsupportedEngineException naming the driver to install.
• Always parametrised — Raw calls take a params array; the builder takes ? markers and renders the engine-correct placeholder style. No string interpolation anywhere.
• sql tagged template — sql`... ${value}` interpolates safely as bind parameters and compiles to the right placeholder style per engine; sql.id() for identifiers, fragments nest.
• Fluent query builder — select/insert/update/delete chains with engine-correct identifier quoting and pagination (LIMIT/OFFSET vs OFFSET..FETCH), plus whereIn/whereNull/whereNotNull and count()/exists() terminals.
• One-call reads — fetch/one/maybeOne/scalar land straight on a ResultSet/Row from either the client or a builder chain — no manual rows() wrap.
• Multi-row INSERT — values() accepts an array (or repeated calls) and compiles a single multi-row statement.
• Row-returning writes — returning(...cols) compiles RETURNING (postgres/sqlite) or OUTPUT INSERTED./DELETED. (mssql); engines without an equivalent reject at compile time with a clear error.
• Typed, normalized results — Every engine's result is reshaped into { rows, rowCount, fields }, generic over the row type; Row<T> gives column-name autocomplete while still allowing case-insensitive lookups.
• Case-insensitive, type-coercing accessors — rows(res).one().number('price') works whether the engine returned PRICE (Oracle) or "12.99" as a string (postgres/mysql NUMERIC). Matchers compare numerically too, so find({ price: 6.5 }) matches a "6.50" cell.
• In-memory row matchers — where/find accept literals, matchers (lt, oneOf, contains, …), or a full-row predicate; where() returns a chainable ResultSet.
• Transactions — transaction(fn) with automatic COMMIT on return and ROLLBACK on throw, on all five engines; the builder runs inside them (qb.run(tx)).
• Script runner — runScript() / splitSqlScript() split a multi-statement .sql file with engine-aware rules (Oracle /, SQL Server GO, comment/quote aware).
• Fail-fast connectivity — ping() for readiness checks and a connectTimeoutMs option so a wrong host dies fast instead of hanging.
• Typed failures — QueryFailedException shows the SQL + params in the message and carries .sql, .params, and the driver .cause; placeholder-style mistakes get a cross-engine hint.
• Pluggable engines — registerEngineFactory() swaps in a custom driver + dialect pair.
• Debug logging — DEBUG=sql:* prints every statement (including inside transactions) with params, namespaced per engine (sql:postgres, sql:mysql, …).

💻 Usage: SqlClient

import { SqlClient } from '@civitas-cerebrum/sql-client';

// --- PostgreSQL (engine auto-detected from the URL scheme) ---
const pg = new SqlClient({ connectionString: 'postgres://user:pass@localhost:5432/mydb' });

// --- MySQL ---
const mysql = new SqlClient({ connectionString: 'mysql://user:pass@localhost:3306/mydb' });

// --- SQLite (in-memory, zero infra) ---
const sqlite = new SqlClient({ engine: 'sqlite', connectionString: ':memory:' });

// --- SQL Server (engine-native connection object) ---
const mssql = new SqlClient({
  engine: 'mssql',
  connection: { server: 'localhost', port: 1433, user: 'sa', password: 'Secret!1',
                database: 'mydb', options: { trustServerCertificate: true } },
});

// --- Oracle ---
const oracle = new SqlClient({ connectionString: 'oracle://user:pass@localhost:1521/FREEPDB1' });

A connection can fail fast instead of hanging on a wrong host — pass connectTimeoutMs in the config, and use ping() (e.g. in a test globalSetup) to verify reachability up front:

const db = new SqlClient({ connectionString: process.env.DB_URL!, connectTimeoutMs: 5000 });
await db.ping();   // throws if the engine is unreachable

Raw queries

Placeholders in raw SQL are engine-native ($1 / ? / @p1 / :1 — see the table above):

const { rows } = await pg.query('SELECT * FROM books WHERE genre = $1', ['Fiction']);
await pg.execute('UPDATE books SET stock = 0 WHERE book_id = $1', ['b1']);

// fetch() wraps the result in a ResultSet in one step
const book = (await pg.fetch('SELECT * FROM books WHERE book_id = $1', ['b1'])).one();

sql tagged template

Prefer the sql tag for portable raw SQL: interpolations become bind parameters in the engine-correct placeholder style, so the same query runs everywhere. Use sql.id() for identifiers (quoted per dialect, never parameterised), and nest fragments to compose:

import { sql } from '@civitas-cerebrum/sql-client';

const genre = 'Fiction', max = 15;
await db.query(sql`SELECT * FROM books WHERE genre = ${genre} AND price < ${max}`);
// postgres → "... WHERE genre = $1 AND price < $2"; mysql → "... = ? AND < ?"; etc.

const onlyCheap = sql`AND price < ${10}`;
await db.query(sql`SELECT ${sql.id('title')} FROM books WHERE in_stock = ${true} ${onlyCheap}`);

Transactions

transaction(fn) commits on return and rolls back on throw. The handle carries the dialect, so QueryBuilder.run(tx) works inside it (the sql tag is client-only — inside a tx use the builder or raw SQL via tx.query):

await pg.transaction(async (tx) => {
  await QueryBuilder.update('books').set({ stock: 0 }).where('book_id = ?', 'b1').run(tx);
  await tx.execute('UPDATE books SET stock = stock - 1 WHERE book_id = $1', ['book-002']);
  // throw → automatic ROLLBACK; return → automatic COMMIT
});

Always close the client when done:

await pg.end();

🧱 Query Builder

Portable SQL from one chain — ? markers, quoting, and pagination all compile per engine:

import { QueryBuilder } from '@civitas-cerebrum/sql-client';

const top = await QueryBuilder.select('books')
  .columns('title', 'price')
  .where('price < ?', 15)
  .orderBy('price', 'desc')
  .limit(5)
  .run(pg);

// Multi-row INSERT — values() takes an array (or repeated calls)
await QueryBuilder.insert('books')
  .values([
    { book_id: 'b1', title: 'Dune' },
    { book_id: 'b2', title: '1984' },
  ])
  .run(pg);

// Writes can hand rows back: RETURNING (postgres/sqlite) or OUTPUT (mssql).
// mysql/oracle have no equivalent — returning() throws UnsupportedEngineException there.
const ins = await QueryBuilder.insert('books')
  .values({ title: 'Dune' })
  .returning('book_id', 'title')   // no args → all columns
  .run(pg);

// whereIn expands one placeholder per value (throws on an empty array, so you never compile
// `IN ()`); whereNull/whereNotNull avoid the `= NULL` footgun that silently matches nothing.
const some = await QueryBuilder.select('books')
  .whereIn('book_id', ['b1', 'b2', 'b3'])
  .whereNotNull('isbn')
  .fetch(pg);

// Inspect without executing — toSql() requires an explicit dialect
const { text, values } = QueryBuilder.select('books').where('price < ?', 15).toSql(pg.dialect);

Terminals dispatch the query through a client and shape the result in one call:

await QueryBuilder.select('books').where('book_id = ?', 'b1').one(pg);        // Row (else ResultError)
await QueryBuilder.select('books').where('genre = ?', 'Fiction').fetch(pg);   // ResultSet
await QueryBuilder.select('books').where('book_id = ?', 'b1').maybeOne(pg);   // Row | undefined
await QueryBuilder.select('books').scalar(pg, 'title');                       // first cell
await QueryBuilder.select('books').where('price < ?', 10).count(pg);          // number
await QueryBuilder.select('books').where('book_id = ?', 'b1').exists(pg);     // boolean

Chainable: columns(...cols) · join(table, on) · where(clause, ...params) (multiple calls are ANDed) · whereIn(col, values) · whereNull(col) · whereNotNull(col) · groupBy(...cols) · having(clause, ...params) · orderBy(col, dir) · limit(n) · offset(n) · values(row | row[]) · set(row) · returning(...cols). Terminals: run(client) · fetch(client) · one(client) · maybeOne(client) · scalar(client, col?) · count(client) · exists(client) · toSql(dialect).

Note: SQL Server and Oracle require orderBy() when paginating (OFFSET..FETCH needs it). Need OR-grouping? Put it in one fragment: where('(genre = ? OR genre = ?)', a, b).

📖 Reading Results

Pull values out of rows by name — case-insensitive (Oracle returns UPPERCASE keys) and type-coercing (postgres/mysql return NUMERIC/DECIMAL as strings):

import { rows, getNumber } from '@civitas-cerebrum/sql-client';

const res = await db.query('SELECT * FROM books WHERE book_id = $1', ['book-001']);

// Fluent wrapper
const book = rows(res).one();                 // exactly one row, else throws ResultError
book.string('title');                         // 'To Kill a Mockingbird'
book.number('price');                         // 12.99
book.boolean('in_stock');                     // normalizes 1/0, 't'/'f', 'true'/'false'

rows(await db.query('SELECT count(*) FROM books')).scalar();   // first cell of first row
rows(res).column('title');                    // every title
rows(res).find({ genre: 'Fiction' });         // first matching row, or undefined
rows(res).maybeOne();                         // 0..1 rows, else throws ResultError

// Or standalone functions on a raw row
getNumber(res.rows[0], 'price');              // 12.99

Semantics: absent column → undefined; SQL NULL → null; otherwise coerced.

Filtering rows

where/find accept a partial (column → literal or matcher) or a full-row predicate:

import { rows, lt, oneOf, contains } from '@civitas-cerebrum/sql-client';

const res = await db.query('SELECT * FROM books');
rows(res).where({ genre: 'Fiction', price: lt(10) });        // matchers
rows(res).find({ title: contains('1984') });
rows(res).where(row => row.number('price')! < 10);           // predicate

// where() returns a ResultSet, so filtering keeps chaining:
rows(res).where({ genre: 'Fiction' }).column('title');
rows(res).where({ book_id: 'b1' }).one();
if (rows(res).where({ status: 'SOLD' }).isEmpty()) { /* none sold */ }
rows(res).where({ genre: 'Fiction' }).all().forEach(r => { /* Row[] */ });

// standalone, on raw rows
import { filterRows } from '@civitas-cerebrum/sql-client';
filterRows(res.rows, { status: oneOf(['ACTIVE', 'SOLD']) });

Matchers: eq ne lt lte gt gte between oneOf like contains startsWith endsWith matches isNull notNull not. A null/undefined cell only matches isNull(); comparison/string matchers return false for it. Equality matchers (eq/ne/oneOf) and literal partials compare numerically when both sides are numeric, so a 6.5 query matches a "6.50" cell returned by postgres/mysql.

🛠️ API Reference

🔌 SqlClient

| Member | Description | |---|---| | new SqlClient(config) | config: { connectionString?, engine?, connection?, max?, connectTimeoutMs?, dialect? } | | query<T>(sql, params?) / query<T>(fragment) | Parametrised query (string or sql fragment) → Promise<SqlResult<T>> | | execute(sql, params?) / execute(fragment) | Write statement → Promise<SqlResult> (rowCount of affected rows) | | fetch<T>(sql, params?) | query() then rows() → Promise<ResultSet<T>> | | transaction(fn) | Atomic block; fn(tx) gets tx.query/tx.execute plus tx.dialect/tx.engine, so QueryBuilder.run(tx) works | | runScript(sqlText) | Split a multi-statement script (engine-aware) and execute each in order | | ping() | Cheap connectivity probe; throws if the engine is unreachable | | end() | Close the pool/connection | | engine / dialect | The resolved engine name and dialect instance |

📊 SqlResult<T>

{ rows: T[], rowCount: number, fields: { name, dataTypeID }[] } — the normalized shape every engine produces.

📖 ResultSet / Row

| Member | Description | |---|---| | rows<T>(result \| rawRows) | Wrap a result (or raw array) in a ResultSet<T> | | .one() / .maybeOne() | Exactly-one / at-most-one row, else ResultError | | .first() / .at(i) | Row or undefined | | .scalar(column?) | First cell of the first row | | .column(name) | One column across all rows | | .find(where) | First matching Row (partial of literals/matchers, or (row) => boolean) | | .where(where) | Filter → chainable ResultSet (same partial/predicate forms) | | .map(fn) / .all() / .raw() | Map over rows / wrap all as Row[] / unwrap to raw rows | | Row<T>.get/string/number/boolean(col) | Case-insensitive, type-coercing cell access; T gives column-name autocomplete | | Row.has(col) / Row.raw() | Presence check / underlying object |

🏷️ sql tag & scripts

| Export | Description | |---|---| | sql`...${v}` | Build a SqlFragment; interpolations → bind params, nested fragments compose | | sql.id(name) | Interpolate an identifier (dialect-quoted, not parameterised) | | splitSqlScript(sqlText, engine?) | Split a multi-statement script into individual statements (engine-aware) |

⚠️ Exceptions

| Class | Thrown when | Extras | |---|---|---| | SqlException | Base class for everything below | — | | QueryFailedException | A query/execute fails | message includes SQL + params (+ placeholder hint); .sql, .params, .cause | | UnsupportedEngineException | Unknown engine, missing driver, or unsupported returning() | — | | ResultError | one()/maybeOne() cardinality or a failed coercion | — |

🧩 Extending

| Function | Description | |---|---| | registerEngineFactory(engine, factory) | Plug in a custom { createDriver, createDialect } pair | | detectEngine(connectionString) | The scheme-detection used by SqlClient | | Dialect | Implement placeholder, quoteIdentifier, compilePagination, compileReturning | | createLogger(ns) / log | debug-based logger (SqlLogger) under the sql:* namespace |

🧪 Test Workflow

# Light (unit + zero-infra SQLite) — no Docker needed
npm test

# Single engine (bring up just that engine's container)
docker compose --profile postgres up -d --wait && npm run test:postgres
docker compose --profile mysql    up -d --wait && npm run test:mysql
docker compose --profile mssql    up -d --wait && npm run test:mssql
docker compose --profile oracle   up -d --wait && npm run test:oracle

# Full matrix — all five live engines
docker compose --profile all up -d --wait
npm run test:all-engines
docker compose --profile all down -v

The commands above are for local iteration. CI runs everything on every PR: the unit + SQLite suite across Node 20/22/24, and the full four-engine live matrix (Postgres, MySQL, SQL Server, Oracle) — each engine in its own isolated container — so engine-specific regressions are caught before merge.

Host ports collide with something already running? Override them: SQL_CLIENT_PG_PORT, SQL_CLIENT_MYSQL_PORT, SQL_CLIENT_MSSQL_PORT, SQL_CLIENT_ORACLE_PORT (and point the test at it, e.g. SQL_TEST_URL=postgres://bookhive:bookhive@localhost:15432/bookhive npm run test:postgres).

Debug logging: DEBUG=sql:* npm test (per-engine namespaces sql:postgres, sql:mysql, sql:sqlite, sql:mssql, sql:oracle; covers transaction statements and lifecycle).

⚠️ Engine Pitfalls

Cross-engine differences the package absorbs for you — and the few it can't:

• Placeholder style differs in raw SQL — $1 (postgres) · ? (mysql/sqlite) · @p1 (mssql) · :1 (oracle). Use the builder or the sql tag for portable code; both render the right style.
• Oracle returns UPPERCASE column keys — Row accessors are case-insensitive, so row.string('title') works regardless. Only raw res.rows[i].title indexing is affected.
• NUMERIC/DECIMAL arrive as strings on postgres/mysql — row.number() coerces, and matchers compare numerically, so you rarely notice. Raw cell access does not coerce.
• SQL Server UNIQUE treats NULLs as duplicates — a second row with NULL in a UNIQUE column violates the constraint on mssql (unlike the other engines). Use distinct sentinel values in test fixtures.
• SQL Server binds params by position — @p1..@pN map to the params array in order; the builder and sql tag handle this automatically.
• SQLite has no native Date/boolean — the driver normalises Date → ISO string, boolean → 1/0, undefined → null on the way in, so the same fixture code works as on the other engines.

🧭 Non-Goals

Deliberately out of scope to stay lean and predictable. For these, reach for a dedicated tool:

• ORM / relations / entity mapping — this is a query client, not an ORM.
• Migrations & schema management — use your migration tool; runScript() only executes SQL you give it.
• Streaming / cursors for huge result sets — results are materialised arrays.
• Named parameters — positional only (the builder's ? and the sql tag cover the ergonomic gap).
• OR-grouped builder trees — express OR inside a single where() fragment instead.
• Upsert helpers — write engine-specific ON CONFLICT / MERGE SQL directly for now.

🤖 Claude Code Skill

The package ships a Claude Code skill (skills/sql-client/SKILL.md) that teaches the agent the full API surface. It installs automatically into .claude/skills/ (project + user level) on npm install — whether the package is installed directly or arrives transitively through another @civitas-cerebrum package. In projects using the Steps-API test frameworks, the skill defers test-spec database work to their database-testing skill.

To skip the copy — e.g. in CI or containers — set SQL_CLIENT_SKIP_SKILLS=1 before installing.