Tables and Columns
A @forinda/kickjs-db schema is a plain TypeScript module that exports table() declarations. The same file is the source of truth for runtime SQL, TypeScript inference, and migration diffing — there is no second declaration to drift against.
// src/db/schema.ts
import { table, uuid, varchar, text, timestamp, boolean } from '@forinda/kickjs-db'
export const users = table('users', {
id: uuid().primaryKey().defaultRandom(),
email: varchar(255).notNull().unique(),
name: varchar(120),
bio: text(),
isActive: boolean().notNull().default('true'),
createdAt: timestamp().notNull().defaultNow(),
})table(name, columns, options?)
table() takes a literal table name, a record of column builders, and an optional constraints callback. The literal name is preserved at the type level so SchemaToTypes<S> can index by it:
export const posts = table(
'posts',
{
id: uuid().primaryKey().defaultRandom(),
title: varchar(200).notNull(),
body: text().notNull(),
},
(t) => ({
titleIdx: index('posts_title_idx').on(t.title),
}),
)The third argument receives a refs object — one ColumnRef per column — for declaring multi-column indexes and unique constraints. See Keys and Constraints → Indexes & unique constraints.
It can also be an object holding that callback and the table's comment:
export const posts = table('posts', columns, {
comment: 'Published and draft posts',
constraints: (t) => ({ titleIdx: index('posts_title_idx').on(t.title) }),
})Comments
.comment(text) on a column, and comment in a table's options, are stored in the database, where anyone reading the schema sees them (\d+ posts in psql, SHOW FULL COLUMNS in MySQL). Changing one generates a migration: COMMENT ON on Postgres, a restated column or ALTER TABLE … COMMENT on MySQL. kick db introspect reads them back. SQLite has no comments, so there they're ignored and never produce a migration.
email: varchar(200).notNull().comment('Sign-in address, lowercased'),Column builders
All cross-dialect builders are imported from the package root. Each carries a phantom TypeScript type that flows into the row shape.
| Builder | SQL type | TS type |
|---|---|---|
serial() | serial | number (generated, not-null) |
bigSerial() | bigserial | bigint (generated, not-null) |
smallSerial() | smallserial | number (generated, not-null) |
integer() | integer | number |
bigint({ mode? }) | bigint | bigint (or per mode) |
smallint() | smallint | number |
version() | integer | number (not-null, maintained) |
decimal(p?, s?, { mode? }) | decimal(p, s) | string (or per mode) |
numeric(p?, s?, { mode? }) | numeric(p, s) | string (or per mode) |
real() | real | number |
doublePrecision() | double precision | number |
varchar(length = 255) | varchar(n) | string |
char(length = 1) | char(n) | string |
text() | text | string |
boolean() | boolean | boolean |
timestamp() | timestamp | Date |
timestamptz() | timestamptz | Date |
date() | date | Date |
time() | time | string |
interval() | interval | string |
uuid() | uuid | string |
json<T>() | json | T |
jsonb<T>() | jsonb | T |
bytea() | bytea | Uint8Array |
mode for big and exact numbers. Drivers disagree on 64-bit integers: pg returns a string, better-sqlite3 a number. Pick what you get back, and the type follows:
bigint({ mode: 'bigint' }) // 9007199254740993n — exact
bigint({ mode: 'number' }) // a number — exact up to 2^53
bigint({ mode: 'string' }) // '9007199254740993'
numeric(12, 2, { mode: 'number' }) // 19.99 instead of '19.99'Without mode, bigint() returns the driver's value as it comes. Validators (insertSchema and friends) refuse input a 'number' mode can't hold exactly, rather than rounding it: a bigint past Number.MAX_SAFE_INTEGER, or a decimal with more than 15 significant digits. On MySQL, give the pool supportBigNumbers: true, bigNumberStrings: true, or mysql2 reads a BIGINT as a number and loses digits past 2^53 before mode sees it. mode applies to nested rows from db.query too.
A bigint in a response — a 'bigint' column, or the counts on an insert, update or delete result — is sent as its decimal string ("9007199254740993"), on every runtime. Return the row with returningAll() when the client needs the record rather than the counts.
decimal and numeric are strings so no digit is lost — decimal(12, 2) reads back as '0.10', not 0.1. Do arithmetic on them with a decimal library, or in SQL. SQLite has no exact decimal type: it stores a float, which kick/db reads back as the same string at the column's scale, exact up to 15 significant digits. An aggregate such as sum(amount) has no column to decode and comes back as a number on SQLite.
Modifiers
Every builder supports a chainable set of modifiers:
varchar(255).notNull() // drop `| null` from the TS type
uuid().primaryKey() // PRIMARY KEY (implies NOT NULL)
varchar(255).unique() // UNIQUE
integer().default(0) // DEFAULT 0 (marks column generated)
text().defaultSql('gen_random_uuid()::text') // a default the database evaluates
text().array() // text[] → TS type becomes T[]
uuid().references(() => users.id, { onDelete: 'cascade' }) // FOREIGN KEY.notNull()/.primaryKey()stamp the column NOT NULL and remove| nullfrom its inferred type..default(value)sets a default value and marks the column generated, so you can omit it on insert:.default(true),.default(0),.default('pending')— the bare value, quoted for you. On a text column it's always the text you wrote:.default('now()')stores the stringnow(). A number, boolean or time column also takes an expression that fits it (timestamp().default('now()'))..defaultSql(expression)sets a default the database evaluates for each new row, on any column, written in your dialect's SQL:text().defaultSql('gen_random_uuid()::text'),text().defaultSql("lower(hex(randomblob(8)))").kick db generatewarns about a.default()on a text column that looks like a call — on SQLite and MySQL earlier versions evaluated it..array()wraps the SQL type in[]and the TS type inT[]..references(() => target, { onDelete?, onUpdate?, name? })adds a foreign key. UseselfRef('id')for a column of the same table. See Foreign keys.timestamp({ precision: 3 }),timestamptz(…)andtime(…)keep that many fractional-second digits (0–6) —3for milliseconds. Without it each database uses its own default: microseconds on Postgres, whole seconds on MySQL. On MySQL adefaultNow()column getsCURRENT_TIMESTAMP(3)to match..comment(text)stores a comment with the column (Comments).
Defaults computed in JS
When a default can't be SQL (an id from your own generator, a value from config, who made the change), compute it in JS:
import { ulid } from 'ulid'
export const notes = table('notes', {
id: text()
.primaryKey()
.$defaultFn(() => ulid()), // each inserted row that doesn't set it
body: text().notNull(),
editedBy: text().$onUpdate(() => currentUser()), // each update that doesn't set it
})$defaultFn(fn)runs once per inserted row that leaves the column out, so a multi-row insert gets a fresh value per row. The column is optional on insert.$onUpdate(fn)runs on every update, and an upsert's update branch, that doesn't set the column. Inserts don't call it.- Neither is part of the schema: the database has no default, so raw
sqlinserts must set the column themselves.
Columns kick/db maintains
Three markers hand a column's upkeep to kick/db. They change the queries it sends, not the schema — no migration involved.
import { table, serial, text, timestamp, version } from '@forinda/kickjs-db'
export const docs = table('docs', {
id: serial().primaryKey(),
title: text().notNull(),
version: version(), // integer, not null, starts at 0, +1 on every update
createdAt: timestamp().notNull().defaultNow(),
updatedAt: timestamp().notNull().defaultNow().onUpdateNow(), // now() on every update
deletedAt: timestamp().softDelete(), // set = deleted; db.query skips the row
}).onUpdateNow()— everyupdateTable('docs')that doesn't set the column itself sets it to the current time; so does an upsert's update branch. Set it explicitly and your value wins.version()— every update adds 1. For optimistic locking, read the row, then update with.where('version', '=', read.version):numUpdatedRows === 0nmeans someone saved first.tsconst { numUpdatedRows } = await db .updateTable('docs') .set({ title }) .where('id', '=', doc.id) .where('version', '=', doc.version) .executeTakeFirst() if (numUpdatedRows === 0n) throw HttpException.conflict('Changed by someone else — reload and retry').softDelete()— on a nullable timestamp. Relational reads (db.query) skip rows where it's set, at the top level and in everywith; passwithDeleted: trueat a level to include them there. Soft-delete by setting it —updateTable('docs').set({ deletedAt: new Date() })— and restore by setting it back tonull. The query builder (selectFrom) is plain SQL and sees every row; add.where('deletedAt', 'is', null)there yourself.
These apply to queries kick/db builds: raw sql and a hand-written UPDATE don't maintain them.
Database-assigned values
serial() / bigSerial() / smallSerial() are numbered by the database and not-null. The date and uuid builders expose expression-default helpers:
uuid().defaultRandom() // DEFAULT gen_random_uuid()
timestamp().defaultNow() // DEFAULT CURRENT_TIMESTAMP (milliseconds on SQLite)
timestamptz().defaultNow()A column with a default is optional on insert but always present on select. Chaining works in either order:
uuid().primaryKey().defaultRandom()
uuid().defaultRandom().primaryKey() // also validGenerated columns
A generated column is computed by the database from the row's other columns, on every write. Declare it with the SQL expression:
export const orders = table('orders', {
id: integer().generatedAlwaysAsIdentity().primaryKey(),
quantity: integer().notNull(),
price: numeric(12, 2).notNull(),
total: numeric(12, 2).generatedAlwaysAs('price * quantity'),
summary: text().generatedAlwaysAs("quantity || ' x ' || price", { stored: false }),
})- Stored or virtual. Stored (the default) computes the value on write and keeps it.
{ stored: false }computes it on read: MySQL, SQLite and Postgres 18+. Both can be indexed on SQLite and on MySQL with InnoDB (the default engine); on Postgres only a stored one can. - Read-only. Its type makes
insertInto/updateTablereject it at compile time.insertSchemaandupdateSchemaleave it out, and the database refuses a write to it. - The SQL is passed through. Name columns as the database knows them, and write the expression for your dialect. Without
casinga key is the column's name, sofirstNameis a mixed-case column the SQL must quote:"firstName"(`firstName`on MySQL). Withcasing: 'snake_case'it'sfirst_name. - Changing it.
- On Postgres, a new expression is applied in place with
SET EXPRESSION(Postgres 17+). - Removing
generatedAlwaysAskeeps the values as a plain column (DROP EXPRESSION). - Making an existing column generated, or switching stored and virtual, re-creates the column. Its values are computed again, but an index on it is dropped with it.
- On MySQL the column is restated in place.
- On SQLite the table is rebuilt with its rows. SQLite also rebuilds to add a stored generated column, which
ALTER TABLEcan't.
- On Postgres, a new expression is applied in place with
A full name joined from two columns, the last one optional, in each dialect:
// Postgres and SQLite
fullName: text().generatedAlwaysAs(`"firstName" || coalesce(' ' || "lastName", '')`),
// MySQL: || is OR there; CONCAT_WS joins and skips NULLs
fullName: text().generatedAlwaysAs("CONCAT_WS(' ', `firstName`, `lastName`)"),Postgres only accepts immutable expressions in a generated column, so concat_ws() and concat() are refused there; use ||. For a value that's only displayed, a result extension computes it in TypeScript with no migration — see Derived fields for the trade-offs.
Identity columns (Postgres) are the SQL-standard replacement for serial:
| Builder | SQL | On insert |
|---|---|---|
.generatedAlwaysAsIdentity() | GENERATED ALWAYS AS IDENTITY | always numbered; giving a value is refused |
.generatedByDefaultAsIdentity() | GENERATED BY DEFAULT AS IDENTITY | numbered unless a value is given |
Adding, removing or switching identity on an existing column alters it in place. MySQL and SQLite have no identity columns, so they're refused there: use serial().
kick db introspect reads both back: identity and generated columns on Postgres, and generated columns on SQLite. Drift checks leave out the generated expression, which the database rewrites into its own form.
Typed JSON
json<T>() and jsonb<T>() carry the declared shape through inference instead of widening to unknown:
const tasks = table('tasks', {
id: uuid().primaryKey().defaultRandom(),
meta: jsonb<{ tags: string[]; pinned: boolean }>(),
})
// db.selectFrom('tasks').select('meta') → meta: { tags: string[]; pinned: boolean } | nullColumn names: casing
By default a key is the column's name: firstName is the firstName column. To keep camelCase keys in TypeScript over a snake_case database, set casing in both places that need to agree, the client and kick.config.ts:
// src/db/client.ts
export const db = createDbClient({ schema, dialect: pgDialect({ pool }), casing: 'snake_case' })
// kick.config.ts
export default defineConfig({
db: {
schemaPath: 'src/db/schema.ts',
migrationsDir: 'db/migrations',
dialect: 'postgres',
casing: 'snake_case',
},
})export const blogPosts = table('blogPosts', {
id: serial().primaryKey(),
authorId: integer()
.notNull()
.references(() => users.id), // column author_id
postTitle: text().notNull(), // column post_title
})
// → CREATE TABLE "blog_posts" ("id" serial, "author_id" integer, "post_title" text, …)- Migrations name tables, columns, keys and foreign keys in snake_case. Names kick/db derives (
blog_posts_author_id_fk) follow; names you write (index('posts_by_author')) are kept as written. - Queries convert both ways:
db.selectFrom('blogPosts').select('postTitle')runsselect "post_title" from "blog_posts", and every row (raw SQL results anddb.querynested rows included) comes back with camelCase keys. The conversion runs after your plugins on the way out and before them on the way back. - SQL you write stays SQL. CHECK expressions,
wherepredicates of partial indexes,generatedAlwaysAsandsqltemplates name columns as the database does:check('positive', 'post_count >= 0'). - Keys must be camelCase. A key that is already snake_case (
created_at) comes back from the database ascreatedAt. - Test helpers and introspection follow.
createTestDb({ schema, casing })takes the same option, andkick db introspectwithcasingset renders camelCase keys. - Migrating an existing project to
casingrenames every camelCase table and column. Generate that migration on its own and read it: it's a rename per column, which rename prompts let you confirm.
One column's name: .colName()
When a single column's database name isn't its key — a legacy EMAIL_ADDR you'd rather call email — name it on the column:
export const users = table('users', {
id: serial().primaryKey(),
email: varchar(254).notNull().unique().colName('EMAIL_ADDR'),
fullName: text().colName('FULL_NM'),
})
// → CREATE TABLE "users" ("id" serial, "EMAIL_ADDR" varchar(254), "FULL_NM" text, …)- Your code keeps the key.
db.selectFrom('users').select('email').where('email', '=', x)runs againstEMAIL_ADDR; a selected column comes back asemail(it's selected as"EMAIL_ADDR" as "email"), andselect *rows are renamed back too. Inserts, updates,upsert,findOrCreate, operators anddb.query(nested rows included) all go by key. - It's per table.
users.emailcan beEMAIL_ADDRwhileposts.emailstaysemail; each reference is resolved against the tables in its query. - Taken as written.
casingdoesn't touch a.colName()—EMAIL_ADDRstays upper-case — and works alongside it for the other columns. - Migrations use the name everywhere it appears: the column, its foreign keys and indexes, and the names kick/db derives from it (
users_EMAIL_ADDR_unique). - SQL you write names the column as the database does —
check('has_email', 'length("EMAIL_ADDR") > 0'),sql`lower("EMAIL_ADDR")`. - Introspection writes
.colName()wherecasingcouldn't give a name back:kick db introspectonEMAIL_ADDRrendersemailAddr: ….colName('EMAIL_ADDR').
Keys and constraints
Foreign keys, indexes, unique constraints, composite primary keys and CHECK constraints are on Keys and Constraints.
Postgres enums
pgEnum() is imported from the @forinda/kickjs-db/pg subpath. It returns a column factory whose phantom type narrows to the union of declared values:
import { table, uuid } from '@forinda/kickjs-db'
import { pgEnum } from '@forinda/kickjs-db/pg'
export const taskStatus = pgEnum('task_status', 'todo', 'in_progress', 'done')
export const tasks = table('tasks', {
id: uuid().primaryKey().defaultRandom(),
status: taskStatus().notNull().default('todo'),
})
// db.selectFrom('tasks').select('status') → status: 'todo' | 'in_progress' | 'done'Pass the default as the bare value — .default('todo'), not .default("'todo'"). The emitter quotes it for you; pre-quoting produces DEFAULT '''todo''', which is the four-character string 'todo' and not a member of the type.
The enum name and values are tracked so the migration pipeline can emit CREATE TYPE … AS ENUM (...) and handle value add / rename / removal. Enum value removal is gated behind a confirmation flag at apply time — see Migrations.
kick db introspect reads enum types back out, so adopting an existing database gives you the declarations too:
export const mood = pgEnum('mood', 'sad', 'ok', 'happy')
export const people = table('people', {
id: serial().primaryKey(),
mood: mood().notNull().default('ok'),
})Value order is preserved as declared, because for an enum it is part of the type — comparisons and ORDER BY follow it.
Postgres only
pgEnum (and the other @forinda/kickjs-db/pg types) are dialect-specific. Importing them while targeting SQLite or MySQL will not produce a valid migration for those dialects.
Other Postgres-only types
The @forinda/kickjs-db/pg subpath also exports:
import { tsvector, vector, halfvec, point, geometry, macaddr, citext } from '@forinda/kickjs-db/pg'
vector(384) // pgvector embedding → number[]
halfvec(384) // half-precision pgvector embedding (pgvector 0.7+) → number[]
point() // geometric point → { x, y }
geometry('Point', 4326) // PostGIS geometry → string (hex EWKB out, WKT/EWKT in)
macaddr() // MAC address → string (also macaddr8())
citext() // case-insensitive text → string
tsvector() // full-text search vector → stringvector, halfvec and point carry codecs, so you write and read the JS value. Before, a vector came back as the text '[1,2,3]' and an inserted array was sent as a Postgres array literal. vector and halfvec need the vector extension, and geometry needs postgis: create the extension in a migration before the table. For geometry, convert in SQL with ST_AsGeoJSON(...) / ST_GeomFromGeoJSON(...). Also exported: money, inet, cidr, xml.
These are subpath-imported (not from the package root) so you can't accidentally reach for a tsvector while targeting another dialect.
MySQL-only types
The @forinda/kickjs-db/mysql subpath exports MySQL's own column types:
import { mysqlEnum, unsigned, tinyint, mediumint, datetime } from '@forinda/kickjs-db/mysql'
export const events = table('events', {
id: serial().primaryKey(),
status: mysqlEnum('Draft', 'Live').notNull(), // ENUM, typed 'Draft' | 'Live'
hits: unsigned(integer()).notNull(), // INT UNSIGNED, 0…4294967295
level: unsigned(tinyint()), // TINYINT UNSIGNED, 0…255
bucket: mediumint(), // MEDIUMINT
at: datetime(3).notNull(), // DATETIME(3) → Date
})mysqlEnumkeeps its values' case through migrations, introspection and validation.unsigned()takes any integer column (integer,smallint,bigint,tinyint,mediumint), keeps its type and chain, and widens the validators' range.datetimeisn't converted to UTC asTIMESTAMPis, and covers years 1000–9999.
Views
A view is a stored SELECT you read like a table. Declare it with the columns it returns, which give the typed client its row type, and its SQL:
import { integer, text, view } from '@forinda/kickjs-db'
export const activeUsers = view(
'active_users',
{ id: integer().notNull(), email: text().notNull() },
{ as: 'SELECT id, email FROM users WHERE deleted_at IS NULL' },
)
await db.selectFrom('active_users').selectAll().execute() // { id: number; email: string }[]- Migrations create views after the tables, in declaration order (so a view can select from one declared before it), and drop them before the tables. A changed definition drops and re-creates the view.
- A change to a table a view reads re-creates the view around it. Postgres won't alter a column a view uses, and SQLite's table rebuild breaks a view over the table, so kick/db drops every view whose SQL names a table the migration alters, and the views built on those, then creates them again afterwards. Index and comment changes leave views alone.
- The columns aren't checked against the SQL. Keep them in step, as you would a hand-written type. With
casing: 'snake_case', alias the SQL's columns in snake_case. - Views are read-only in kick/db's eyes. The typed client accepts writes, but most views can't take them.
- Introspection reads views back with their columns (
kick db introspectrenders them). Drift checks which views exist, not their SQL, because every database rewrites it.
Materialized views (Postgres)
A materialized view stores the result, so reads are fast and data is as fresh as the last refresh:
import { date, numeric, unique } from '@forinda/kickjs-db'
import { materializedView } from '@forinda/kickjs-db/pg'
export const dailySales = materializedView(
'daily_sales',
{ day: date().notNull(), total: numeric(12, 2, { mode: 'number' }).notNull() },
{
as: 'SELECT created_at::date AS day, sum(amount) AS total FROM orders GROUP BY 1',
constraints: (t) => ({ byDay: unique('daily_sales_day').on(t.day) }),
},
)
await db.refreshMaterializedView('daily_sales', { concurrently: true })refreshMaterializedView re-runs the query. With concurrently: true, readers keep the old rows while it refreshes. That needs a unique index (declared in constraints, as above) and can't run inside a transaction. Refresh from a cron job or after the writes that matter.
Custom column types
customType<T>() lets a project introduce a typed column that isn't in the built-in DSL — encrypted strings, ULIDs, PostGIS geometry — without forking the package. It takes a dataType thunk plus optional toDriver / fromDriver codecs:
import { customType, table, timestamp } from '@forinda/kickjs-db'
const ulid = customType<string>({
dataType: () => 'char(26)',
toDriver: (s) => s,
fromDriver: (raw) => String(raw),
})
export const events = table('events', {
id: ulid().primaryKey(),
ts: timestamp().notNull().defaultNow(),
})The phantom T flows through SchemaToTypes<S> exactly like any built-in builder, so db.selectFrom('events').select('id') types id: string. The codecs run automatically on insert / update (toDriver) and on selected rows (fromDriver).
Relations (for db.query)
Relations are declared separately from table(), after both tables exist, with the relations() helper. They are query-time joining sugar for the relational query layer — they do not emit DDL:
import { relations } from '@forinda/kickjs-db'
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}))
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.authorId], references: [users.id] }),
}))one(target, { fields, references })— at most one row: many-to-one from the side with the key, or either side of a one-to-one.fieldsare columns on this table,referencesthe target columns they equal.many(target)— one-to-many; the join comes from the target's matchingone(or its foreign key).many(target, { through })— many-to-many through a junction table.morphMany/morphOne/morphTo— the same three over a type + id column pair instead of a foreign key.
Which one for which shape — many-to-one, one-to-many, one-to-one, many-to-many and the polymorphic three — with an example and its result for each: Relations.
When a source table has multiple foreign keys to the same target, pair the two sides with a matching relationName:
export const messagesRelations = relations(messages, ({ one }) => ({
sender: one(users, { fields: [messages.senderId], references: [users.id], relationName: 'sent' }),
recipient: one(users, {
fields: [messages.recipientId],
references: [users.id],
relationName: 'received',
}),
}))Export the relations alongside the tables (export * from './schema') so the client picks them up. They power db.query.users.findMany({ with: { posts: true } }) — see Queries.
Type inference
The schema feeds inference automatically through createDbClient({ schema }). To name the row shape yourself, use SchemaToTypes:
import { type SchemaToTypes } from '@forinda/kickjs-db'
import * as schema from './schema'
type DB = SchemaToTypes<typeof schema>
// DB['users'] → { id: Generated<string>; email: string; name: string | null; ... }The full inference story — Generated<T> wrapping, nullability, KickDbRegister augmentation — is covered in Schema Types.