// @ozaco/db

Core

Schema definition, a typed query builder, migrations, and the `DB` protocol drivers plug into.

import from@ozaco/db

@ozaco/db is a small, Drizzle-backed database layer. You describe tables with the col.* builders and defineTable/defineSchema, then run fully typed queries through a QueryBuilder — from/insert/update/delete, plus transaction and escape-hatch raw SQL. Row and insert shapes are inferred from the schema, so where clauses and returned rows stay in sync with your columns.

The layer is transport-agnostic. DB is an std:plugin protocol whose context is the QueryBuilder and whose only action is close. The concrete drivers — @ozaco/db/impl/sqlite and @ozaco/db/impl/postgres — implement it; install one into an effect scope and useDatabase() hands back its query builder anywhere downstream.

Installing a driver also runs applyMigrations, an additive schema sync: it creates any missing tables and adds any missing columns from your SchemaDef (never dropping or altering existing ones) and records a content hash in _ozaco_migrations. The dialect-aware DDL builders behind it (createTableStatement, addColumnStatement, columnType, …) are exported for tooling and tests. Driver failures surface as tagged Result failures using the DbErrorCode tags.

// additional entry points

@ozaco/db/impl/sqlite`SqliteDB` — SQLite driver over `better-sqlite3`/`bun:sqlite` (`file:` URLs).
@ozaco/db/impl/postgres`PostgresDB` — Postgres driver with a connection pool (`max` connections).

// on this page

Schema Query Protocol & Hooks Migrations Errors Types Constants

Schema

Declare columns, tables and schemas as plain, inferable data.

const

col

Column-builder factories, one per supported `ColumnType`.

signature

const col: {
  int(): ColumnBuilder<number>
  bigint(): ColumnBuilder<bigint>
  text(): ColumnBuilder<string>
  varchar(length: number): ColumnBuilder<string>
  boolean(): ColumnBuilder<boolean>
  timestamp(): ColumnBuilder<Date>
  json<T = unknown>(): ColumnBuilder<T>
  blob(): ColumnBuilder<Uint8Array>
}

Each factory returns a ColumnBuilder carrying the column’s JS type, which you refine with chainable modifiers (primary, unique, notNull, default, …). The JS type flows into InferRow/InferInsert, so query results and inputs are typed from the column definitions.

example

import { col } from '@ozaco/db'

const id = col.int().primary().autoIncrement()
const email = col.text().unique()
const meta = col.json<{ tags: string[] }>().optional()

iface

ColumnBuilder

A `ColumnDef` plus chainable modifiers; each call returns a new builder.

signature

interface ColumnBuilder<TJs> extends ColumnDef<TJs> {
  notNull(): ColumnBuilder<TJs>
  optional(): ColumnBuilder<TJs | null>
  primary(): ColumnBuilder<TJs>
  autoIncrement(): ColumnBuilder<TJs>
  unique(): ColumnBuilder<TJs>
  default(value: TJs): ColumnBuilder<TJs>
  defaultNow(): ColumnBuilder<TJs>
  references(ref: ColumnRef): ColumnBuilder<TJs>
}

Modifiers are immutable — each returns a fresh builder — so definitions can be composed freely. optional widens the JS type to TJs | null; primary implies non-null; autoIncrement and defaultNow set hasDefault. references records a foreign key used to order table creation during migration.

Because ColumnBuilder extends ColumnDef, a built column can be dropped straight into a defineTable column map.

defineTable

Create a `TableDef` from a name and a map of column definitions.

signature

function defineTable<TName extends string, TColumns extends ColumnMap>(
  name: TName,
  columns: TColumns,
): TableDef<TName, TColumns>

// returns A frozen-shape `TableDef` tagged with the `TABLE` symbol.

example

import { col, defineTable } from '@ozaco/db'

const users = defineTable('users', {
  id: col.int().primary().autoIncrement(),
  email: col.text().unique(),
  createdAt: col.timestamp().defaultNow(),
})

defineSchema

Group tables into a `SchemaDef` passed to a driver on install.

signature

function defineSchema<TTables extends TableMap>(tables: TTables): SchemaDef<TTables>

The resulting SchemaDef is what a driver migrates against and what the query builder resolves tables from. Keys are logical names; each value is a TableDef from defineTable.

example

import { defineSchema } from '@ozaco/db'

const schema = defineSchema({ users, posts })

Query

The typed query surface exposed by an installed driver.

iface

QueryBuilder

The database context: entry points for selects, writes, transactions and raw SQL.

signature

interface QueryBuilder {
  from<TTable extends TableDef>(table: TTable): SelectQuery<TTable>
  insert<TTable extends TableDef>(table: TTable): InsertQuery<TTable>
  update<TTable extends TableDef>(table: TTable): UpdateQuery<TTable>
  delete<TTable extends TableDef>(table: TTable): DeleteQuery<TTable>
  transaction<T>(fn: (tx: QueryBuilder) => Future<T>): Operation<T>
  raw<T = unknown>(sql: string, params?: unknown[]): Future<T[]>
}

QueryBuilder is the DB protocol’s context: whichever driver is installed provides one, and useDatabase() returns it. Each starter (from, insert, …) yields a chainable, table-typed query object.

transaction runs fn with a scoped builder and commits on success or rolls back on failure. raw is the escape hatch for driver-specific SQL, returning rows as T[].

example

import { useDatabase } from '@ozaco/db'

function* recent() {
  const db = yield* useDatabase()
  return yield* db.from(users).orderBy('createdAt', 'desc').limit(10).all()
}

iface

SelectQuery

A chainable read: filter, order, paginate, then materialize rows.

signature

interface SelectQuery<TTable extends TableDef> {
  where(clause: WhereClause<TTable>): SelectQuery<TTable>
  limit(count: number): SelectQuery<TTable>
  offset(count: number): SelectQuery<TTable>
  orderBy(column: keyof InferRow<TTable> & string, direction?: OrderDirection): SelectQuery<TTable>
  all(): Future<InferRow<TTable>[]>
  first(): Future<InferRow<TTable> | null>
  firstOrFail(): Future<InferRow<TTable>>
}

where matches by equality on the given columns. all returns every matching row, first returns the first or null, and firstOrFail fails with a not-found DbErrorCode when there is no match.

example

const admin = yield* db
  .from(users)
  .where({ role: 'admin' })
  .firstOrFail()

iface

InsertQuery

Stage one or many rows and either execute or return the inserted rows.

signature

interface InsertQuery<TTable extends TableDef> {
  values(row: InferInsert<TTable>): InsertQuery<TTable>
  valuesMany(rows: InferInsert<TTable>[]): InsertQuery<TTable>
  returning(): InsertReturning<TTable>
  execute(): Future<void>
}

InferInsert makes columns with defaults optional. Call execute() for a fire-and-forget write, or returning() to read back the inserted rows.

example

yield* db.insert(users).values({ email: 'a@b.co' }).execute()

const [created] = yield* db
  .insert(users)
  .values({ email: 'c@d.co' })
  .returning()
  .all()

iface

InsertReturning

Materialize the rows produced by an `INSERT ... RETURNING`.

signature

interface InsertReturning<TTable extends TableDef> {
  all(): Future<InferRow<TTable>[]>
  first(): Future<InferRow<TTable> | null>
  firstOrFail(): Future<InferRow<TTable>>
}

iface

UpdateQuery

Assign new column values, filter the target rows, then execute or return.

signature

interface UpdateQuery<TTable extends TableDef> {
  set(values: Partial<InferRow<TTable>>): UpdateQuery<TTable>
  where(clause: WhereClause<TTable>): UpdateQuery<TTable>
  returning(): UpdateReturning<TTable>
  execute(): Future<number>
}

execute() resolves to the number of affected rows. Use returning() when you need the updated rows themselves.

example

const changed = yield* db
  .update(users)
  .set({ active: false })
  .where({ id: 7 })
  .execute()

iface

UpdateReturning

Materialize the rows produced by an `UPDATE ... RETURNING`.

signature

interface UpdateReturning<TTable extends TableDef> {
  all(): Future<InferRow<TTable>[]>
  first(): Future<InferRow<TTable> | null>
}

iface

DeleteQuery

Filter rows and delete them, resolving to the affected count.

signature

interface DeleteQuery<TTable extends TableDef> {
  where(clause: WhereClause<TTable>): DeleteQuery<TTable>
  execute(): Operation<number>
}

example

const removed = yield* db.delete(users).where({ id: 7 }).execute()

type

WhereClause

A partial row used for equality filtering: `Partial<InferRow<TTable>>`.

signature

type WhereClause<TTable extends TableDef> = Partial<InferRow<TTable>>

Every supplied column is combined with AND equality. Omitting a column leaves it unconstrained.

type

OrderDirection

Sort direction for `orderBy`: `'asc' | 'desc'`.

signature

type OrderDirection = 'asc' | 'desc'

Protocol & Hooks

The `std:plugin` protocol drivers implement, and how to reach the builder.

const

DB

The database protocol — context is a `QueryBuilder`, with a single `close` action.

signature

const DB: Protocol<QueryBuilder, [unknown], DBActions>
// name 'db', version '0.0.1', subtype Symbol.for('@ozaco/db.protocol')

// The protocol's context and action shapes (from types/db):
type DBContext = QueryBuilder
interface DBActions {
  close(): Future<void>
}

// Against whichever driver is installed:
DB.actions.close(): Future<void>

DB is defined with defineProtocol from std:plugin. Drivers call DB.implement(...) to refine the setup argument (SqliteConfig, PostgresConfig) and produce a Plugin you install; installing one sets the QueryBuilder as the protocol context for the scope.

The only routed action is close, which releases the underlying connection or pool. Because DB is Hookable, you can layer before/after/around/error hooks onto its actions.

example

import { DB } from '@ozaco/db'
import { install } from '@ozaco/std/plugin'
import { SqliteDB } from '@ozaco/db/impl/sqlite'

const db = yield* install(SqliteDB, { url: 'file:app.db', schema })
// ... use db ...
yield* DB.actions.close()

useDatabase

Read the installed driver’s `QueryBuilder` from the current effect scope.

signature

function useDatabase(): Operation<QueryBuilder>

A thin wrapper over useContext(DB.context). Yields the QueryBuilder set by whichever driver was installed in the scope chain; fails if no driver is installed.

example

import { useDatabase } from '@ozaco/db'

function* countUsers() {
  const db = yield* useDatabase()
  return (yield* db.from(users).all()).length
}

Migrations

Additive schema sync and the dialect-aware DDL builders behind it. Drivers call these on install; they are exported for tooling.

applyMigrations

Create missing tables and add missing columns to match a `SchemaDef`.

signature

function applyMigrations(
  exec: RawExec,
  schema: SchemaDef,
  dialect: Dialect,
): Promise<void>

Runs the additive migration a driver performs at install time. It ensures the _ozaco_migrations bookkeeping table exists, orders tables so foreign-key targets are created first (falling back to definition order on cycles), then for each table either creates it or ALTER TABLE ... ADD COLUMNs any columns absent from the live schema.

It never drops or alters existing columns — only additive changes are applied. On completion it records the schema’s schemaTag in _ozaco_migrations (ignoring conflicts), so re-running against an unchanged schema is a no-op write.

schemaTag

Compute a stable content hash string identifying a schema’s shape.

signature

function schemaTag(schema: SchemaDef): string

Serializes every table, its sorted columns, and each column’s type/nullable/primary flags into a deterministic v1:... string. Two schemas with the same structure produce the same tag; used as the unique key recorded in _ozaco_migrations.

type

RawExec

The raw SQL executor `applyMigrations` runs against.

signature

type RawExec = (sql: string, params?: unknown[]) => Promise<unknown[]>

A driver-supplied function that runs a SQL string with optional bound params and resolves to result rows. Each driver adapts its client (better-sqlite3, bun:sqlite, postgres pool) to this shape.

type

Dialect

Target SQL dialect for DDL generation: `'sqlite' | 'postgres'`.

signature

type Dialect = 'sqlite' | 'postgres'

createTableStatement

Render a `CREATE TABLE IF NOT EXISTS` statement for a table.

signature

function createTableStatement(table: TableDef, dialect: Dialect): string

Emits each column with its dialect type and constraints (primary key, NOT NULL, UNIQUE, defaults, REFERENCES). Identifiers are quoted and escaped.

addColumnStatement

Render an `ALTER TABLE ... ADD COLUMN` statement for one column.

signature

function addColumnStatement(args: {
  tableName: string
  columnName: string
  column: ColumnDef
  dialect: Dialect
}): string

Used to backfill columns added to a table that already exists. PRIMARY KEY constraints are stripped, since primary keys cannot be added via ADD COLUMN.

existingColumnsQuery

Build the query that lists a table’s current column names.

signature

function existingColumnsQuery(tableName: string, dialect: Dialect): string

On SQLite this reads pragma_table_info; on Postgres it queries information_schema.columns. Rows expose a name field, letting the migrator diff live columns against the schema.

migrationsTableDdl

DDL for the `_ozaco_migrations` bookkeeping table.

signature

function migrationsTableDdl(dialect: Dialect): string

Creates a table with an auto-increment id, a unique tag, and an applied_at timestamp (integer epoch on SQLite, TIMESTAMP WITH TIME ZONE DEFAULT NOW() on Postgres).

recordMigrationSql

DDL that inserts a schema tag row, ignoring conflicts.

signature

function recordMigrationSql(dialect: Dialect): string

An INSERT ... ON CONFLICT DO NOTHING. SQLite binds (tag, applied_at) positionally; Postgres binds (tag) via $1 and lets applied_at default.

columnType

Map a `ColumnDef` to its concrete SQL type for a dialect.

signature

function columnType(column: ColumnDef, dialect: Dialect): string

For example int → INTEGER (or SERIAL for an auto-increment primary key on Postgres), timestamp → INTEGER on SQLite / TIMESTAMP WITH TIME ZONE on Postgres, varchar(n) → VARCHAR(n), blob → BLOB/BYTEA.

formatDefault

Render a JS default value as a SQL literal.

signature

function formatDefault(value: unknown): string

Numbers and bigints pass through, booleans become TRUE/FALSE, null becomes NULL, and strings (or JSON-encoded objects) are single-quoted with embedded quotes escaped.

Errors

const

DbErrorCode

The tag map failures are classified with by the drivers.

signature

const DbErrorCode: {
  ConnectionLost: 'connection-lost'
  UniqueViolation: 'unique-violation'
  ForeignKeyViolation: 'foreign-key-violation'
  CheckViolation: 'check-violation'
  NotFound: 'not-found'
  Validation: 'validation'
  TxConflict: 'tx-conflict'
  Driver: 'driver'
}

Built with createTags(null, ...), so each key maps to its bare kebab-case tag. Drivers translate raw client errors into Result failures carrying one of these tags (e.g. a duplicate key becomes unique-violation; firstOrFail on no rows becomes not-found), letting callers pattern-match instead of parsing driver messages.

Types

The schema data model and row inference helpers.

iface

ColumnDef

The immutable shape of a single column, carrying its JS type in `TJs`.

signature

interface ColumnDef<TJs = unknown> {
  readonly _t: typeof COLUMN
  readonly type: ColumnType
  readonly isNullable: boolean
  readonly isPrimary: boolean
  readonly isUnique: boolean
  readonly isAutoIncrement: boolean
  readonly hasDefault: boolean
  readonly isDefaultNow: boolean
  readonly defaultValue: unknown
  readonly foreignKey: ColumnRef | null
  readonly length: number | null
  readonly __js: TJs
}

Produced by col.* and mutated (immutably) by ColumnBuilder modifiers. __js is a phantom field carrying the TypeScript type used by InferRow/InferInsert; it holds no runtime value.

type

ColumnType

The set of supported logical column types.

signature

type ColumnType =
  | 'int'
  | 'bigint'
  | 'text'
  | 'varchar'
  | 'boolean'
  | 'timestamp'
  | 'json'
  | 'blob'

iface

ColumnRef

A foreign-key target: a table and column name.

signature

interface ColumnRef {
  readonly table: string
  readonly column: string
}

Set via ColumnBuilder.references. Used during migration to render REFERENCES clauses and to order table creation.

type

ColumnMap

A map of column name to `ColumnDef`.

signature

type ColumnMap = Record<string, ColumnDef>

iface

TableDef

A named table with its column map, tagged by the `TABLE` symbol.

signature

interface TableDef<TName extends string = string, TColumns extends ColumnMap = ColumnMap> {
  readonly _t: typeof TABLE
  readonly name: TName
  readonly columns: TColumns
}

type

TableMap

A map of logical name to `TableDef`.

signature

type TableMap = Record<string, TableDef>

iface

SchemaDef

A set of tables, tagged by the `SCHEMA` symbol.

signature

interface SchemaDef<TTables extends TableMap = TableMap> {
  readonly _t: typeof SCHEMA
  readonly tables: TTables
}

type

InferRow

The row type read back from a table: each column mapped to its JS type.

signature

type InferRow<TTable extends TableDef> =
  TTable extends TableDef<infer _TName, infer TCols>
    ? { [K in keyof TCols]: ColumnJs<TCols[K]> }
    : never

Drives the types returned by all/first/firstOrFail and accepted by where/set.

type

InferInsert

The insert type for a table: the row type made fully partial.

signature

type InferInsert<TTable extends TableDef> =
  TTable extends TableDef<infer _TName, infer TCols>
    ? Partial<{ [K in keyof TCols]: ColumnJs<TCols[K]> }>
    : never

All columns are optional at the type level, so columns with database defaults (auto-increment ids, defaultNow timestamps) may be omitted.

Constants

Registered symbols used as discriminant `_t` tags on schema data.

const

COLUMN

Tag for a `ColumnDef`.

signature

const COLUMN: unique symbol // Symbol.for('@ozaco/db.column')

const

TABLE

Tag for a `TableDef`.

signature

const TABLE: unique symbol // Symbol.for('@ozaco/db.table')

const

SCHEMA

Tag for a `SchemaDef`.

signature

const SCHEMA: unique symbol // Symbol.for('@ozaco/db.schema')