YATTA
Docs
Engines

Database

An embedded SQLite layer with types inferred from your schema. WAL mode is enabled automatically, so several processes can share one file safely.

Defining a schema

The schema is a plain object built with col. Row types are inferred from it — there is no generation step and no schema file to keep in sync.

yatta/func/db.tsts
import { col, createDatabase } from "yatta/db"; export const schema = {  users: {    id: col.uuid(),    email: col.text().unique(),    name: col.text(),    roles: col.json<string[]>().default(["user"]),    verified: col.boolean().default(false),    profile: col.json<Record<string, unknown>>().nullable(),    createdAt: col.createdAt(),    updatedAt: col.updatedAt(),  },  posts: {    id: col.id(),    title: col.text(),    body: col.text().default(""),    published: col.boolean().default(false),    authorId: col.text().references("users.id", { onDelete: "CASCADE" }),    createdAt: col.createdAt(),  },}; // Makes db.users and db.posts fully typed.declare module "yatta/db" {  interface Register {    schema: typeof schema;  }} export const db = createDatabase({  path: process.env.DATABASE_URL || "Database/app.db",  schema,});

Column builders

col.id()
Auto-incrementing integer primary key.
col.uuid()
Generated UUID primary key.
col.text()
Text column.
col.integer()
Integer column.
col.real()
Floating point column.
col.boolean()
Boolean, stored as integer.
col.json<T>()
JSON, serialized and parsed for you.
col.date()
Timestamp.
col.createdAt()
Timestamp, set on insert.
col.updatedAt()
Timestamp, refreshed on update.

Modifiers

tsts
col.text().unique()                       // UNIQUE constraintcol.text().nullable()                     // allows NULLcol.text().default("pending")             // DEFAULT valuecol.integer().default(1)col.text().references("users.id", { onDelete: "CASCADE" })col.text().hasMany("posts")               // relationcol.text().belongsTo("users")             // relation

Reading and writing

CRUDts
// Insert — returns the created rowconst user = db.users.insert({  email: "ada@example.com",  name: "Ada",}); db.users.insertMany([  { email: "a@example.com", name: "A" },  { email: "b@example.com", name: "B" },]); // Readconst byId   = db.users.findById(user.id);const first  = db.users.findFirst({ where: { verified: true } });const admins = db.users.findMany({  where: { roles: { contains: "admin" } },  orderBy: { createdAt: "desc" },  take: 20,}); // Updatedb.users.updateById(user.id, { name: "Ada L." }); // Deletedb.users.deleteById(user.id);db.users.delete({ where: { verified: false } }); // Upsertdb.apiKeys.upsert({  where: { id: "key_1" },  create: { id: "key_1", userId: user.id },  update: { lastUsedAt: new Date() },}); // Countconst total = db.users.count();

The query DSL

where accepts an object of comparisons. Combine them with AND and OR.

Filteringts
db.posts.findMany({  where: {    AND: [      { published: true },      { authorId: user.id },      { title: { contains: "bun" } },    ],  },}); // Operators{ gt: 100 }        // greater than{ lt: 10 }         // less than{ gte: 1 }         // greater or equal{ lte: 1 }         // less or equal{ ne: "x" }        // not equal{ contains: "bun" }// LIKE %bun%{ in: ["a", "b"] }{ like: "A%" }{ isNull: true }

Pagination

Offset and cursorts
// Page numbersconst page = db.posts.paginate({ page: 2, limit: 20 });// → { data, total, page, limit, totalPages } // Keyset — stays fast on large tablesconst feed = db.posts.cursorPaginate({ limit: 15, cursor: lastCursor });// → { data, nextCursor, hasMore }

Transactions

Nested calls use savepoints, so a helper that opens a transaction can be composed safely inside a larger one.

Transactionsts
const result = db.transaction(() => {  const user = db.users.insert({ email: "ada@example.com", name: "Ada" });   db.posts.insert({    title: "Hello",    authorId: user.id,  });   return user;}); // Nested — opens a SAVEPOINT insteaddb.transaction(() => {  db.users.insert({ email: "a@example.com", name: "A" });  db.transaction(() => {    db.posts.insert({ title: "Nested", authorId: "..." });  });});

Relations

Eager loadingts
const post = db.posts.findById("1", {  include: { author: true },}); const posts = db.posts.findMany({  include: { author: true, comments: true },  take: 50,});

Raw SQL

When the DSL is not the right tool, the underlying handle is available.

Raw accessts
const rows = db.sql.query(  `SELECT p.*, u.name AS author     FROM posts p     JOIN users u ON u.id = p.author_id     WHERE p.published = 1     ORDER BY p.created_at DESC     LIMIT 50`).all(); db.sql.exec("VACUUM;");

Backups

tsts
// Online backup — safe while the app is runningawait db.backup("Database/backups/app.db"); // Restore, with rollback on failureawait db.restore("Database/backups/app.db");

Multi-process safety

Every connection is opened with these pragmas:

Applied automaticallysql
PRAGMA journal_mode = WAL;    -- readers proceed during writesPRAGMA busy_timeout = 5000;    -- wait for a lock instead of throwingPRAGMA synchronous = NORMAL;   -- safe under WAL, fewer fsyncsPRAGMA foreign_keys = ON;
Warning
Schema creation runs under BEGIN IMMEDIATE and retries on contention, so mounting the same schema across many worker threads at boot is safe. Under heavy concurrency the L2 cache store also retries — see Reliability.