@coderbuzz/sql

Type-safe SQL query builder for TypeScript — schema-driven DDL, fluent SELECT/INSERT/DELETE, and dialect-specific engines for SQLite and ClickHouse.

Features

• Schema-driven tables — define once, generate DDL and run typed queries from the same schema
• kyo METADATA integration — columns carry [METADATA]: TypeMeta, enabling proto binary serialization of query results
• Fluent SELECT builder — CTEs, JOINs, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
• Fluent INSERT builder — batching with debounce, timeout, and max-rows flush strategies
• Fluent DELETE builder — WHERE conditions, raw execution
• SQLite engine — Bun-native bun:sqlite integration
• ClickHouse engine — streaming HTTP TSV parsing with automatic type coercion
• Dialect support — ANSI SQL, SQLite, ClickHouse
• ObjectId — MongoDB-compatible 24-hex ObjectId generator
• Debounce — runtime-agnostic debounce utility

Installation

bun add @coderbuzz/sql

Table of Contents

1. Schema Definition (SqlTable)
2. DDL Generation
3. SELECT Queries
4. INSERT Queries
5. DELETE Queries
6. SQLite Engine
7. ClickHouse Engine
8. Column Types Reference
9. kyo METADATA Integration
10. ObjectId
11. Debounce

1. Schema Definition (SqlTable)

Define your table schema once with typed column descriptors, then use it to generate DDL and build queries.

import { SqlTable } from "@coderbuzz/sql";
import * as t from "@coderbuzz/sql/sqlite";

const users = new SqlTable("users", {
  id: t.integer().PRIMARY(),
  name: t.text().NOT_NULL(),
  email: t.text().NOT_NULL().INDEX(),
  score: t.integer().DEFAULT(0),
  bio: t.text().ALLOW_NULL(),
  created_at: t.datetime(),
});

Column modifiers

| Modifier | Effect | | ----------------- | ------------------------------------------------- | | .PRIMARY() | Marks column as PRIMARY KEY | | .NOT_NULL() | Column is required (non-nullable) | | .ALLOW_NULL() | Column accepts NULL; parser handles null values | | .INDEX() | Generates a CREATE INDEX for this column | | .DEFAULT(value) | Sets a default value in DDL |

ClickHouse schema

import { SqlTable } from "@coderbuzz/sql";
import * as ch from "@coderbuzz/sql/clickhouse";

const events = new SqlTable(
  "analytics.events",
  {
    id: ch.uint32().PRIMARY(),
    session_id: ch.uuid(),
    name: ch.lowCardinality("String"),
    score: ch.float64(),
    active: ch.boolean(),
    tags: ch.string().ALLOW_NULL(),
    created_at: ch.datetime64(3),
  },
  {
    engine: "MergeTree()",
    orderBy: ["id"],
    partitionBy: "toYYYYMM(created_at)",
  },
);

2. DDL Generation

createTable(dialect, ifNotExists?)

console.log(users.createTable("sqlite"));
// CREATE TABLE IF NOT EXISTS users (
//   id INTEGER PRIMARY KEY,
//   name TEXT NOT NULL,
//   email TEXT NOT NULL,
//   score INTEGER DEFAULT 0,
//   bio TEXT,
//   created_at DATETIME NOT NULL
// );

console.log(events.createTable("clickhouse"));
// CREATE TABLE IF NOT EXISTS analytics.events (
//   id UInt32,
//   session_id UUID,
//   name LowCardinality(String),
//   score Float64,
//   active Bool,
//   tags Nullable(String),
//   created_at DateTime64(3)
// ) ENGINE = MergeTree()
// PARTITION BY toYYYYMM(created_at)
// ORDER BY (id);

createIndexes(dialect)

Returns an array of CREATE INDEX statements — one per .INDEX() column. Returns [] for ClickHouse (indexes are handled by the engine).

users.createIndexes("sqlite");
// ["CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);"]

events.createIndexes("clickhouse"); // []

dropTable(ifExists?)

users.dropTable(); // DROP TABLE IF EXISTS users;
users.dropTable(false); // DROP TABLE users;

Applying DDL with an engine

import { SQLiteEngine } from "@coderbuzz/sql";

const db = new SQLiteEngine();

await db.execute(users.createTable("sqlite"));
for (const idx of users.createIndexes("sqlite")) {
  await db.execute(idx);
}

3. SELECT Queries

Via SqlTable (recommended)

// SELECT * FROM users WHERE name = 'Alice'
const rows = await users.select(db).where({ name: "Alice" }).execute();

// SELECT id, name FROM users ORDER BY score DESC LIMIT 10
const top = await users.select(db, "id", "name")
  .order_by("score DESC")
  .limit(10)
  .execute();

Via Sql directly

import { Sql } from "@coderbuzz/sql";

const sql = new Sql("sqlite"); // dialect: 'ansi' | 'sqlite' | 'clickhouse'

const query = sql
  .select("id", "name", "email")
  .from("users")
  .where({ active: true })
  .order_by("name ASC")
  .limit(20, 0);

console.log(query.toSQL());
// SELECT id, name, email FROM users WHERE active = TRUE ORDER BY name ASC LIMIT 0, 20;

WHERE conditions

// Field equality
.where({ name: "Alice" })                     // name = 'Alice'
.where({ score: ">= 100" })                   // score >= 100
.where({ id: [1, 2, 3] })                     // id IN (1, 2, 3)
.where({ bio: "IS NULL" })                    // bio IS NULL
.where({ bio: "IS NOT NULL" })                // bio IS NOT NULL

// AND / OR / NOT
.where({ and: [{ name: "Alice" }, { score: ">= 10" }] })
.where({ or:  [{ name: "Alice" }, { name: "Bob" }] })
.where({ not: { name: "Alice" } })

// Raw string (for complex expressions)
.where("score > 0 AND created_at > '2024-01-01'")

JOINs

sql.select("u.name", "p.title")
  .from("users u")
  .left_join("posts p", "p.user_id = u.id")
  .inner_join("roles r", "r.id = u.role_id")
  .where("u.active = TRUE")
  .toSQL();

| Method | SQL | | ------------------------ | ----------------------- | | .left_join(table, on) | LEFT JOIN | | .inner_join(table, on) | INNER JOIN | | .right_join(table, on) | RIGHT JOIN | | .full_join(table, on) | FULL JOIN (ANSI only) |

GROUP BY, HAVING, ORDER BY, LIMIT

sql.select("name", "COUNT(*) AS cnt")
  .from("users")
  .group_by("name")
  .having("COUNT(*) > 1")
  .order_by("cnt DESC")
  .limit(10, 20) // LIMIT 10 OFFSET 20
  .toSQL();

CTEs (WITH)

sql.with(
  "top_users",
  (q) => q.select("id", "name").from("users").order_by("score DESC").limit(10),
)
  .select("*")
  .from("top_users")
  .toSQL();
// WITH top_users AS (SELECT id, name FROM users ORDER BY score DESC LIMIT 10) SELECT * FROM top_users;

Pretty print

query.toSQL({ pretty: true }); // multi-line formatted output
query.toString(); // same as toSQL({ pretty: true })

SELECT DISTINCT

sql.select_distinct("name").from("users").toSQL();
// SELECT DISTINCT name FROM users;

4. INSERT Queries

Via SqlTable

await users.insert(db).values([
  { id: 1, name: "Alice", email: "[email protected]", score: 100, bio: null },
  { id: 2, name: "Bob", email: "[email protected]", score: 50, bio: "Dev" },
]).flush();

Via Sql directly

sql.insert_into("users")
  .columns("id", "name", "email")
  .values([{ id: 1, name: "Alice", email: "[email protected]" }])
  .toSQL();
// INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]')

Batched INSERT (debounce + timeout + max-rows)

Batch mode accumulates rows in memory and flushes automatically based on three strategies:

| Option | Default | Trigger | | --------- | -------- | ----------------------------------------------------- | | wait | required | Flush after wait ms of inactivity (debounce) | | max | 1000 | Flush immediately when row count reaches max | | timeout | 1000 ms | Flush after timeout ms even if still receiving rows |

const insert = clickhouse
  .insert_into("events")
  .options({
    batch: { wait: 50, max: 5000, timeout: 2000 },
  });

// These accumulate and flush automatically
insert.values([{ id: 1, name: "click", ts: "2024-01-01 00:00:00.000" }]);
insert.values([{ id: 2, name: "view", ts: "2024-01-01 00:00:01.000" }]);

// Manual flush at any time
insert.flush();

ClickHouse SETTINGS

sql.insert_into("events")
  .options({ settings: { async_insert: "1", wait_for_async_insert: "0" } })
  .values([...])
  .toSQL();
// INSERT INTO events (...) SETTINGS async_insert=1, wait_for_async_insert=0 VALUES (...)

5. DELETE Queries

Via SqlTable

await users.delete(db).where({ id: 1 }).execute();
await users.delete(db).where({ score: "< 0" }).execute();

Via Sql directly

sql.delete_from("users").where({ name: "Alice" }).toSQL();
// DELETE FROM users WHERE name = 'Alice';

sql.delete_from("users").toSQL();
// DELETE FROM users;

6. SQLite Engine

Bun-native SQLite integration using bun:sqlite. Runs only in Bun.

import { SQLiteEngine } from "@coderbuzz/sql";

// In-memory database
const db = new SQLiteEngine();

// File-based database
const db = new SQLiteEngine({ path: "./data.db" });

// Read-only
const db = new SQLiteEngine({ path: "./data.db", readonly: true });

// SELECT returns rows as Record<string, any>[]
const rows = await db.execute("SELECT * FROM users;");

// DDL/DML returns []
await db.execute("CREATE TABLE ...");
await db.execute("INSERT INTO ...");
await db.execute("DELETE FROM ...");

// Access the underlying bun:sqlite Database directly
db.db.run("PRAGMA optimize;");

// Close when done
db.close();

Full SQLite example

import { SQLiteEngine, SqlTable } from "@coderbuzz/sql";
import * as t from "@coderbuzz/sql/sqlite";

const db = new SQLiteEngine();

const users = new SqlTable("users", {
  id: t.integer().PRIMARY(),
  name: t.text().NOT_NULL(),
  email: t.text().NOT_NULL().INDEX(),
  score: t.integer().DEFAULT(0),
});

// Apply DDL
await db.execute(users.createTable("sqlite"));
for (const idx of users.createIndexes("sqlite")) await db.execute(idx);

// Insert
await db.execute(
  users.insert(db).values([
    { id: 1, name: "Alice", email: "[email protected]", score: 95 },
    { id: 2, name: "Bob", email: "[email protected]", score: 80 },
  ]).toSQL(),
);

// Select
const rows = await users.select(db)
  .order_by("score DESC")
  .limit(10)
  .execute();
// [{ id: 1, name: "Alice", email: "[email protected]", score: 95 }, ...]

// Delete
await users.delete(db).where({ id: 2 }).execute();

db.close();

7. ClickHouse Engine

Streaming HTTP integration using ClickHouse's TabSeparatedRawWithNamesAndTypes format with automatic type coercion.

import { ClickHouse } from "@coderbuzz/sql";

const ch = new ClickHouse({
  url: "http://localhost:8123",
  user: "default",
  key: "",
});

Execute a query

const dataset = await ch.execute(
  "SELECT id, name, score FROM events LIMIT 100;",
);

// Stream rows
for await (const row of dataset.data) {
  console.log(row); // { id: 1, name: "alpha", score: 9.5 }
}

// Collect all rows at once
const rows = await dataset.toArray();

// Response metadata
console.log(dataset.meta); // [{ name: "id", type: "UInt32" }, ...]
console.log(dataset.summary); // { read_rows: 100, result_rows: 100, elapsed_ns: ... }

Type coercion

Column values are automatically parsed to their TypeScript equivalents:

| ClickHouse type | TypeScript type | | ----------------------------------------------------------------- | ------------------------------------ | | Int8…UInt64, Float32, Float64, Decimal | number | | Bool | boolean | | String, FixedString, UUID, IPv4, IPv6, LowCardinality | string | | Date, Date32, DateTime, DateTime64 | Date | | Nullable(T) | T \| null (\N sentinel → null) |

Full ClickHouse example

import { ClickHouse, SqlTable } from "@coderbuzz/sql";
import * as ch from "@coderbuzz/sql/clickhouse";

const client = new ClickHouse({
  url: "http://localhost:8123",
  user: "default",
  key: "",
});

const events = new SqlTable(
  "analytics.events",
  {
    id: ch.uint32().PRIMARY(),
    name: ch.string(),
    score: ch.float64(),
    active: ch.boolean(),
    created_at: ch.datetime64(3),
  },
  { engine: "MergeTree()", orderBy: ["id"] },
);

// DDL
await client.execute("CREATE DATABASE IF NOT EXISTS analytics;");
await client.execute(events.createTable("clickhouse"));

// Insert
await client.execute(
  events.insert(client).values([
    {
      id: 1,
      name: "alpha",
      score: 9.5,
      active: 1,
      created_at: "2024-01-01 00:00:00.000",
    },
    {
      id: 2,
      name: "beta",
      score: 7.2,
      active: 0,
      created_at: "2024-01-02 00:00:00.000",
    },
  ]).toSQL(),
);

// Select
const dataset = await events.select(client)
  .where("score > 8")
  .order_by("score DESC")
  .execute();

const rows = await dataset.toArray();
// rows[0].score is already a number (not a string)
// rows[0].created_at is already a Date instance

// Batched insert
const batcher = events.insert(client).options({
  batch: { wait: 100, max: 10000, timeout: 5000 },
});
batcher.values([{
  id: 3,
  name: "gamma",
  score: 5.0,
  active: 1,
  created_at: "2024-01-03 00:00:00.000",
}]);

8. Column Types Reference

ANSI (@coderbuzz/sql/ansi)

import * as ansi from "@coderbuzz/sql/ansi";

| Function | SQL Type | | ------------------- | ---------------------------------------- | | char(n?) | CHAR(n) | | varchar(n?) | VARCHAR(n) | | text() | TEXT | | clob() | CLOB | | integer() | INTEGER | | int() | INT | | smallint() | SMALLINT | | bigint() | BIGINT | | decimal(p?, s?) | DECIMAL(p, s) | | numeric(p?, s?) | NUMERIC(p, s) | | float(p?) | FLOAT / FLOAT(p) | | real() | REAL | | doublePrecision() | DOUBLE PRECISION | | boolean() | BOOLEAN | | date() | DATE | | time(tz?) | TIME / TIME WITH TIME ZONE | | timestamp(tz?) | TIMESTAMP / TIMESTAMP WITH TIME ZONE |

SQLite (@coderbuzz/sql/sqlite)

import * as t from "@coderbuzz/sql/sqlite";

| Function | SQL Type | | ----------------- | --------------- | | char(n?) | CHAR(n) | | varchar(n?) | VARCHAR(n) | | text() | TEXT | | clob() | CLOB | | integer() | INTEGER | | int() | INT | | smallint() | SMALLINT | | bigint() | BIGINT | | real() | REAL | | float() | FLOAT | | double() | DOUBLE | | numeric(p?, s?) | NUMERIC(p, s) | | decimal(p?, s?) | DECIMAL(p, s) | | boolean() | BOOLEAN | | date() | DATE | | time() | TIME | | datetime() | DATETIME |

ClickHouse (@coderbuzz/sql/clickhouse)

import * as ch from "@coderbuzz/sql/clickhouse";

| Function | SQL Type | | ---------------------- | ---------------------- | | string() | String | | fixedString(n) | FixedString(n) | | int8() … int64() | Int8 … Int64 | | uint8() … uint64() | UInt8 … UInt64 | | float32() | Float32 | | float64() | Float64 | | decimal(p?, s?) | Decimal(p, s) | | boolean() | Bool | | date() | Date | | date32() | Date32 | | datetime() | DateTime | | datetime64(p?) | DateTime64(p) | | uuid() | UUID | | ipv4() | IPv4 | | ipv6() | IPv6 | | lowCardinality(type) | LowCardinality(type) |

9. kyo METADATA Integration

Every SqlColumn<T> carries a [METADATA]: TypeMeta property from @coderbuzz/kyo. SqlTable assembles these into an object-shape TypeMeta for its rows.

This makes rows produced by a table schema directly compatible with @coderbuzz/proto for binary serialization — no extra configuration needed.

import { METADATA } from "@coderbuzz/sql";
import { proto } from "@coderbuzz/proto";

const users = new SqlTable("users", {
  id: t.integer().PRIMARY(),
  name: t.text().NOT_NULL(),
  score: t.integer().DEFAULT(0),
  bio: t.text().ALLOW_NULL(),
});

// The table carries a kyo TypeMeta describing the row shape
const meta = users[METADATA];
// {
//   type: "object",
//   shape: {
//     id:    { type: "number" },
//     name:  { type: "string" },
//     score: { type: "number" },
//     bio:   { type: "nullable", inner: { type: "string" } },
//   }
// }

// Compile a binary codec from the table schema
const codec = proto(users[METADATA]);
const bytes = codec.encode({ id: 1, name: "Alice", score: 95, bio: null });
const row = codec.decode(bytes);

TypeMeta per column modifier

| Modifier | [METADATA].type | | ---------------------------------------- | -------------------------- | | text(), varchar(), string() … | "string" | | integer(), float64(), decimal() … | "number" | | boolean() | "boolean" | | date(), datetime(), datetime64() … | "date" | | .ALLOW_NULL() | "nullable" (wraps inner) | | .NOT_NULL() on nullable | unwraps to inner type |

10. ObjectId

MongoDB-compatible 24-hex ObjectId generator. Encodes 4-byte timestamp | 3-byte machine id | 2-byte process id | 3-byte counter.

import { ObjectId } from "@coderbuzz/sql";

const id = ObjectId(); // "6698e99b5b1dcc203ed9c28a"

// Use as a primary key
const users = new SqlTable("users", {
  id: t.text().PRIMARY(),
  name: t.text().NOT_NULL(),
});

await db.execute(
  users.insert(db).values([{ id: ObjectId(), name: "Alice" }]).toSQL(),
);

11. Debounce

Runtime-agnostic debounce utility included with the package.

import { debounce } from "@coderbuzz/sql";

const flush = debounce((data: string[]) => {
  console.log("flushing", data.length, "items");
}, 100);

flush(["a", "b"]);
flush(["c"]); // resets the timer
flush.flush(); // execute immediately, cancel pending timer
flush.clear(); // cancel pending timer without executing
flush.pending; // true if a call is waiting

Interface

interface DebouncedFunction<T extends (...args: any[]) => any> {
  (...args: Parameters<T>): void;
  /** Execute immediately and cancel any pending call. */
  flush(): void;
  /** Cancel the pending call without executing. */
  clear(): void;
  /** Whether a call is currently pending. */
  readonly pending: boolean;
}

License

MIT — Copyright (c) 2026 Indra Gunawan