Semola

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

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 },
});

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
`include`	Related rows
`$skipHooks`	Skip hooks for this call

Method	Meaning
`findMany` / `findFirst` / `findUnique`	Read
`create` / `createMany`	Insert
`update` / `updateMany`	Patch
`delete` / `deleteMany`	Remove

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.

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. Use the CLI to generate and apply SQL from your table definitions.

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

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");
});

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

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, and tables

ORM