sqlite-zod-orm: typed SQLite for Bun, described with Zod · Gor Berlian · diadem.fun
← Gor Berlian
ArticleNov 18, 20263 min read

sqlite-zod-orm: typed SQLite for Bun, described with Zod

Contents

Repository: https://github.com/7flash/sqlite-zod-orm · Package: bun add sqlite-zod-orm (version 4.2.3) · License: MIT

Describe your tables as Zod schemas. sqlite-zod-orm creates and migrates the tables, validates every write and gives typed reads back. It has no separate schema language and no code generation, and you don't write SQL for ordinary work.

import { Database, z } from "sqlite-zod-orm";

const db = new Database("app.db", {
  users: z.object({ name: z.string(), email: z.string(), score: z.number().default(0) }),
  posts: z.object({ title: z.string(), user_id: z.number() }),
}, {
  relations: { posts: { user_id: "users" } },
  timestamps: true,
  softDeletes: true,
});

const ada = db.users.insert({ name: "Ada", email: "ada@example.com" });
db.posts.insert({ title: "Hello", user_id: ada.id });
const posts = db.posts.select().where({ title: { $like: "H%" } }).with("user").all();
posts[0].user.name;     // "Ada", typed
ada.score = 42;         // assignment writes through to the database
db.users.on("update", (row) => console.log("changed", row.id)); // also across processes

What you get#

  • Typed queries:
    • where() with operators checked per column: $gte, $in, $like, $or, and null for IS NULL;
    • orderBy, limit, pagination, count, pluck;
    • relations loaded in batches with .with('posts.comments.author').
  • Writes that say what happened. Insert, insertMany, update, upsert, findOrCreate and delete return the stored row or the exact number of rows changed.
  • Lifecycle:
    • soft deletes, with a restore() that brings back what was deleted together;
    • cascades through every level;
    • timestamps;
    • hooks that run after the transaction commits, and never for rolled-back work.
  • Live changes. .on('insert' | 'update' | 'delete') sees writes from any connection or process on the same file.
  • Safe migrations. Columns are added and renamed from the schema. Changes that would lose data are refused at startup. bunx sqlite-zod-orm upgrade-check . lists code that a major upgrade affects.

Proof it works#

Every code example in the README and upgrade guide is run with the suite (51 of 51; scripts/docs-examples.ts runs each one and asserts the results the text states). The suite has 7,610 tests, most of them generated by test matrices that compare the library against hand-written SQL:

  • 3,072 queries: every where operator over 8 column types.
  • 2,048 random write sequences: inserts, deletes, restores, hooks and transactions, checked against a model after every step.
  • A relations matrix: 180 users, 650 posts and 2,100 comments.
  • A change-feed matrix: two connections and a raw SQLite connection on one file.
  • 71 write cases: every write function, across all combinations of options.
  • A type matrix: each type claim paired with a run-time check.

Real-run tools go further than unit tests:

  • Upgrade on a real file: an old published version (3.26.1, 3.34.0, 4.0.3) builds a database, and the new version opens the same file. No stored row changes and every field reads back the same (12/12 checks each).
  • Type-checking real apps: nine applications type-check against the new types with no new errors.
  • Speed against plain SQL: compared with hand-written bun:sqlite on the same machine:
Operation 4.2.0 4.2.2
get(id) 12% 91%
range select().where() 9% 77%
update(id) 3% 58%
.with() one level 10% 69%

These tools found real bugs that earlier tests had missed, some present since 3.x:

  • Bulk updates and deletes changed rows outside their range. They used only the first operator. Fixed in 4.0.8.
  • Bulk counts were doubled. Fixed in 4.0.11.
  • Live events were lost across processes. Fixed in 4.0.10.
  • A partial update reset other fields to their defaults. Fixed in 4.0.3.
  • Source, README and upgrade guide: https://github.com/7flash/sqlite-zod-orm
  • Changelog: https://github.com/7flash/sqlite-zod-orm/blob/master/CHANGELOG.md
  • Report on the real-run checks: https://diadem.fun/coin/berlian/webstack-real-run-report
Gor Berlian

One person, many companies, one coin. Every project I build sends a share of its revenue back to buy $BERLIAN on the open market, and every buy is published with its transaction.

See the page

More from Gor Berlian