SemolaSemola

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 apply

Review 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

BuilderJS type
stringstring
numbernumber
booleanboolean
uuidstring
dateDate
json, jsonbunknown (pass a generic to narrow)
enumTypeunion of the listed strings

Modifiers

MethodMeaning
.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 itApp, on create()Database
Omitted insertRuns fnUses 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" },
});
OptionMeaning
whereColumn operators, plus $and / $or / $not. Relations: every / some / none
selectFields to return
distinctDeduplicate by these columns (Postgres: DISTINCT ON, SQLite: GROUP BY). On Postgres, orderBy must start with the same columns when set
includeRelated rows
cursorUnique/PK position marker for cursor pagination. Cursor row included unless skip: 1. Negative take pages backward
take / skipLimit and offset. Negative take reverses direction
$skipHooksSkip hooks for this call
MethodMeaning
findMany / findFirst / findUniqueRead
create / createManyInsert
update / updateManyPatch
delete / deleteManyRemove

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 },
});
Relationconnectdisconnect
one()One unique wheretrue (sets the FK to null)
many()Array of unique wheresArray 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 rollback

Programmatic 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.
  • create fails when nothing changed.
  • Review up.sql / down.sql before apply, especially drop, NOT NULL, and enum CHECK warnings.
  • 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.sql checksum and rejects drift.

Each migration folder looks like:

migrations/
  20260817090000000_add_user_roles/
    up.sql
    down.sql
    schema.json

Not 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

OptionMeaning
adapter"sqlite" or "postgres"
urlConnection URL (e.g. "file:./dev.db")
tablesMap of defineTable results
relationsOptional one / many map
hooksGlobal and per-table lifecycle hooks
poolOptional Bun SQL pool settings for Postgres: max, idleTimeout, maxLifetime, connectionTimeout (seconds)

Client

MemberMeaning
db.<table>Typed table client
db.$rawUnderlying Bun.SQL
db.$transaction(cb)Run work in a transaction
db.$configAdapter, redacted URL, tables, and pool
db.$ping()Health check (SELECT 1)

Migrations API

ExportMeaning
loadConfig(cwd?)Load semola.config.ts and schema into a LoadedConfig
applyMigrations(config)Apply pending migrations (history, lock, checksum)
MigrationErrorThrown on migration failures
LoadedConfig{ migrationsDir, orm: { adapter, url, tables } }

On this page