ORM
Semola ORM - typed tables, queries, and migrations for Bun SQL (SQLite or Postgres)
Define tables once, then use typed create, find, update, and delete helpers. Semola uses Bun's SQL client and supports SQLite and Postgres.
Quick start
Use a persistent database URL. Migration commands open their own connection, so :memory: does not survive from create to apply.
1. Define a table and client
// src/db.ts
import { createOrm, defineTable, string, uuid } from "semola/orm";
export const users = defineTable({
sqlName: "users",
columns: {
id: uuid("id").primaryKey().notNull(),
name: string("name").notNull(),
email: string("email").notNull().unique(),
},
});
export const db = createOrm({
adapter: "sqlite",
url: "file:./dev.db",
tables: { users },
});defineTable() describes row types and the database schema. createOrm() creates a typed client, but does not create the physical table.
2. Initialize the database
// semola.config.ts
import { defineConfig } from "semola";
export default defineConfig({
orm: {
schema: "./src/db.ts",
},
});bunx semola orm migrations create "initialize_database"
bunx semola orm migrations applyReview generated SQL before applying. For later schema changes, edit the table definitions and run both commands again.
3. Use the typed client
import { db } from "./db.js";
await db.users.create({
data: {
id: "u1",
name: "Ada",
email: "ada@example.com",
},
});
const user = await db.users.findFirst({
where: { email: "ada@example.com" },
});Tables and columns
Columns start nullable. Chain modifiers to tighten them.
const users = defineTable({
sqlName: "users",
columns: {
id: uuid("id").primaryKey().default(() => crypto.randomUUID()),
email: string("email").notNull().unique(),
role: string("role").notNull().dbDefault("member"),
createdAt: date("created_at")
.notNull()
.dbDefault("CURRENT_TIMESTAMP", { as: "sql" }),
},
});Types
| Builder | JS type |
|---|---|
string | string |
number | number |
boolean | boolean |
uuid | string |
date | Date |
json, jsonb | unknown (pass a generic to narrow) |
enumType | union of the listed strings |
Modifiers
| Method | Meaning |
|---|---|
.primaryKey() | Primary key (also not-null) |
.notNull() | Required |
.nullable() | Optional |
.unique() | Unique constraint |
.default(fn) | App fills the value on create() |
.dbDefault(value) | SQL literal default ("user", 0, true) |
.dbDefault(sql, { as: "sql" }) | Raw SQL default (now(), gen_random_uuid()) |
.references(() => col) | Foreign key |
.references() targets must be tables passed to createOrm({ tables }).
.default(fn) | .dbDefault(...) | |
|---|---|---|
| Who fills it | App, on create() | Database |
| Omitted insert | Runs fn | Uses the SQL default (not null) |
Use .dbDefault() when adding a NOT NULL column. { as: "sql" } is for SQL expressions such as functions and CURRENT_TIMESTAMP (a single expression only).
Relations
const posts = defineTable({
sqlName: "posts",
columns: {
id: uuid("id").primaryKey().notNull(),
title: string("title").notNull(),
authorId: uuid("authorId")
.notNull()
.references(() => users.columns.id),
},
});
const db = createOrm({
adapter: "sqlite",
url: "file:./dev.db",
tables: { users, posts },
relations: {
users: {
posts: many(() => posts),
},
posts: {
author: one("authorId", () => users),
},
},
});one(foreignKeyColumn, () => table) uses the source table's FK column name. many(() => table) is the reverse side.
Checks
Define custom CHECK constraints on the table config. Column-level rules (.primaryKey(), .unique(), .references(), enumType) stay on columns.
import {
check,
date,
defineTable,
number,
uuid,
} from "semola/orm";
const posts = defineTable({
sqlName: "posts",
columns: {
id: uuid("id").primaryKey().notNull(),
age: number("age"),
startedAt: date("started_at").notNull(),
endedAt: date("ended_at").nullable(),
},
checks: (columns) => [
check("posts_age_check").on(columns.age).where("age > 21"),
check("posts_dates_check")
.on(columns.startedAt, columns.endedAt)
.where("started_at < ended_at"),
],
});checks is optional. Check names are required and must be unique on each table (the same name on different tables is allowed). On Postgres, constraint names are unique per schema, so reusing a name across tables fails at migrate time. Use .on(columns...) to declare which columns the check depends on (for migrations when columns are dropped). .where("...") takes the raw SQL inside CHECK (...).
Indexes
Define secondary indexes on the table config. Column .unique() emits a UNIQUE constraint on the column. uniqueIndex() emits a CREATE UNIQUE INDEX (useful for composite uniqueness).
import {
date,
defineTable,
index,
string,
uniqueIndex,
uuid,
} from "semola/orm";
const posts = defineTable({
sqlName: "posts",
columns: {
id: uuid("id").primaryKey().notNull(),
authorId: uuid("author_id").notNull(),
slug: string("slug").notNull(),
createdAt: date("created_at").notNull(),
deletedAt: date("deleted_at").nullable(),
},
indexes: (columns) => [
index("posts_author_created_idx").on(columns.authorId, columns.createdAt),
uniqueIndex("posts_slug_idx").on(columns.slug),
index("posts_active_author_idx")
.on(columns.authorId)
.where("deleted_at IS NULL"),
],
});indexes is optional. Index names are required and must be unique across the schema. Partial indexes use .where("...") with a raw SQL expression.
Queries
await db.users.create({
data: {
id: "u1",
name: "Ada",
email: "ada@example.com",
},
});
const user = await db.users.findFirst({
where: { email: "ada@example.com" },
});
const page = await db.posts.findMany({
where: { authorId: "u1" },
orderBy: { title: "asc" },
take: 20,
skip: 0,
include: { author: true },
});
const nextPage = await db.posts.findMany({
take: 20,
skip: 1,
cursor: { id: "p20" },
orderBy: { id: "asc" },
});
const previousPage = await db.posts.findMany({
take: -20,
skip: 1,
cursor: { id: "p20" },
orderBy: { id: "asc" },
});
const titles = await db.posts.findMany({
distinct: ["title"],
select: { title: true },
});
await db.users.update({
where: { id: "u1" },
data: { name: "Augusta" },
});
await db.posts.deleteMany({
where: { authorId: "u1" },
});| Option | Meaning |
|---|---|
where | Column operators, plus $and / $or / $not. Relations: every / some / none |
select | Fields to return |
distinct | Deduplicate by these columns (Postgres: DISTINCT ON, SQLite: GROUP BY). On Postgres, orderBy must start with the same columns when set |
include | Related rows |
cursor | Unique/PK position marker for cursor pagination. Cursor row included unless skip: 1. Negative take pages backward |
take / skip | Limit and offset. Negative take reverses direction |
$skipHooks | Skip hooks for this call |
| Method | Meaning |
|---|---|
findMany / findFirst / findUnique | Read |
create / createMany | Insert |
update / updateMany | Patch |
delete / deleteMany | Remove |
Connect and disconnect
create and update accept connect / disconnect under a relation key in data. Records are matched by unique key.
await db.posts.create({
data: { id: "p1", title: "Hello", author: { connect: { id: "u1" } } },
});
await db.users.update({
where: { id: "u1" },
data: { posts: { connect: [{ id: "p2" }], disconnect: [{ id: "p3" }] } },
include: { posts: true },
});| Relation | connect | disconnect |
|---|---|---|
one() | One unique where | true (sets the FK to null) |
many() | Array of unique wheres | Array of unique wheres (sets their FK to null) |
The whole write runs in one transaction (a savepoint inside $transaction). A missing record throws and rolls everything back.
Connect and disconnect
create and update accept relation keys in data to link existing records by a unique key.
await db.posts.create({
data: { id: "p1", title: "Hello", author: { connect: { id: "u1" } } },
});
await db.users.update({
where: { id: "u1" },
data: { posts: { connect: [{ id: "p2" }], disconnect: [{ id: "p3" }] } },
include: { posts: true },
});one() relations take connect: { ... } or disconnect: true. many() relations take arrays. The whole write runs in a transaction (a savepoint inside $transaction), so a missing record throws and nothing is saved.
Transactions and raw SQL
await db.$transaction(async (tx) => {
await tx.users.create({ data: { /* ... */ } });
await tx.posts.create({ data: { /* ... */ } });
});
await db.$raw.unsafe(`SELECT 1`);$raw is the underlying Bun.SQL instance. Prefer migrations for schema changes.
Connection pooling
Postgres reuses connections through Bun's SQL pool. Queries wait in Bun's queue when every connection is busy. Pass pool to set size, idle cleanup, max lifetime, and connect timeout. pool is only available when adapter is "postgres".
const db = createOrm({
adapter: "postgres",
url: process.env.DATABASE_URL!,
tables: { users },
pool: {
max: 20,
idleTimeout: 30,
maxLifetime: 3600,
connectionTimeout: 10,
},
});
await db.$ping();$ping() is a public client method. It runs SELECT 1 and rejects if the database is unreachable.
Hooks
Before-hooks may return patched options. After-hooks and read hooks receive context only. Column-specific work belongs on hooks.tables.<name>.
const db = createOrm({
adapter: "sqlite",
url: "file:./dev.db",
tables: { users },
hooks: {
tables: {
users: {
beforeCreate(ctx) {
return {
data: {
...ctx.options.data,
name: ctx.options.data.name.trim(),
},
};
},
afterCreate(ctx) {
if (ctx.result) {
console.log("created", ctx.result.id);
}
},
},
},
},
});Migrations
createOrm() never changes the physical database schema. Generate SQL with the CLI, then apply with the CLI or the programmatic API. Both share the same history table, migration lock, and up.sql checksum checks.
Configuration
import { defineConfig } from "semola";
export default defineConfig({
orm: {
schema: "./src/db.ts",
migrationsDir: "migrations", // optional, default "migrations"
},
});schema must export a createOrm() client (default or named). Run commands from the directory that contains semola.config.ts.
Commands
bunx semola orm migrations create "add_user_roles"
bunx semola orm migrations apply
bunx semola orm migrations rollbackProgrammatic apply
import { applyMigrations, loadConfig } from "semola/orm";
const config = await loadConfig();
const applied = await applyMigrations(config);loadConfig(cwd?) reads semola.config.ts and the schema module (same as the CLI). Pass a LoadedConfig yourself when you already know the adapter, URL, and migrations directory:
import { applyMigrations } from "semola/orm";
await applyMigrations({
migrationsDir: "./migrations",
orm: {
adapter: "sqlite",
url: "file:./dev.db",
tables: {},
},
});Apply opens its own connection and closes it when finished. Prefer a file URL over :memory: so the schema is visible to later clients.
- Apply pending migrations before creating another one.
createfails when nothing changed.- Review
up.sql/down.sqlbeforeapply, especially drop,NOT NULL, and enumCHECKwarnings. - Ambiguous drop+add asks rename (keep data) vs create new. Leftover drops still need destructive confirmation. Non-TTY create fails on ambiguous renames.
- Destructive drops ask for confirmation in a TTY; non-TTY create fails.
- Rollback rejects an empty or comment-only
down.sql. - Do not hand-edit applied migration files; keep folders in order. Apply stores an
up.sqlchecksum and rejects drift.
Each migration folder looks like:
migrations/
20260817090000000_add_user_roles/
up.sql
down.sql
schema.jsonNot yet supported: foreign-key ON DELETE / ON UPDATE actions, CONCURRENTLY, custom Postgres USING expressions, or expression indexes.
Examples
Find with include
const post = await db.posts.findFirst({
where: { id: "p1" },
include: { author: true },
});
console.log(post?.author?.email);Compound where
const active = await db.users.findMany({
where: {
$and: [{ name: { startsWith: "A" } }, { email: { contains: "@" } }],
},
orderBy: { name: "asc" },
take: 10,
});Transaction rollback
Throwing inside $transaction() rolls back every write made through the transaction client.
await db.$transaction(async (tx) => {
await tx.users.create({
data: { id: "u2", name: "Grace", email: "grace@example.com" },
});
throw new Error("abort");
});On SQLite, overlapping $transaction calls (and nested relation writes that open a transaction) are queued per client so only one writer runs at a time. Nested $transaction calls from the root client inside an active transaction still fail.
Reference
createOrm options
| Option | Meaning |
|---|---|
adapter | "sqlite" or "postgres" |
url | Connection URL (e.g. "file:./dev.db") |
tables | Map of defineTable results |
relations | Optional one / many map |
hooks | Global and per-table lifecycle hooks |
pool | Optional Bun SQL pool settings for Postgres: max, idleTimeout, maxLifetime, connectionTimeout (seconds) |
Client
| Member | Meaning |
|---|---|
db.<table> | Typed table client |
db.$raw | Underlying Bun.SQL |
db.$transaction(cb) | Run work in a transaction |
db.$config | Adapter, redacted URL, tables, and pool |
db.$ping() | Health check (SELECT 1) |
Migrations API
| Export | Meaning |
|---|---|
loadConfig(cwd?) | Load semola.config.ts and schema into a LoadedConfig |
applyMigrations(config) | Apply pending migrations (history, lock, checksum) |
MigrationError | Thrown on migration failures |
LoadedConfig | { migrationsDir, orm: { adapter, url, tables } } |