Drizzle + SQLite

Use Drizzle directly; no Brevity database plugin is needed. This recipe uses Drizzle 0.45.4 and libSQL client 0.15.15 with a local SQLite file.

Open a repository

import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const notes = sqliteTable("notes", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  text: text("text").notNull(),
});

export async function openStore(url: string) {
  const client = createClient({ url });
  const db = drizzle(client);
  try {
    // A fixed schema for this tutorial; use versioned migrations in a real app.
    await client.execute(
      "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL)",
    );
  } catch (error) {
    client.close();
    throw error;
  }
  return {
    async list() {
      return db.select().from(notes).orderBy(notes.id);
    },
    async add(text: string) {
      return db.transaction(async (tx) => {
        const [note] = await tx.insert(notes).values({ text }).returning();
        if (!note) throw new Error("Insert returned no note");
        return note;
      });
    },
    close() {
      client.close();
    },
  };
}

export type NoteStore = Awaited<ReturnType<typeof openStore>>;

Open one client for the app and pass the repository to createNotebook. Drizzle parameterizes the SQL values.

SQLite/libSQL schema and transactions remain Drizzle's responsibility. The tutorial creates its first table on startup; for a real app, generate and review migrations using Drizzle's tooling and apply them as a deployment step. Never assume CREATE TABLE IF NOT EXISTS updates an existing table.

Transactions and shutdown

add() shows db.transaction: throw to roll back; let unexpected failures reach core's logged generic 500. Keep transactions short. Lock contention, timeouts, retries, and multi-process deployment policy are database decisions, not framework guarantees.

import { createNotebook } from "./app.js";
import { openStore } from "./store.js";

const store = await openStore(process.env.DATABASE_URL ?? "file:notebook.db");
const app = createNotebook(store);
const server = app.listen(Number(process.env.PORT ?? 3000));
server.on("listening", () => {
  const address = server.address();
  if (!address || typeof address === "string") throw new Error("Expected TCP listener");
  console.log(`Notebook: http://127.0.0.1:${address.port}`);
});
server.on("error", (error) => {
  console.error(error);
  store.close();
  process.exitCode = 1;
});
for (const signal of ["SIGINT", "SIGTERM"] as const) {
  process.once(signal, () => {
    server.close((error) => {
      store.close();
      if (error) {
        console.error(error);
        process.exitCode = 1;
      }
    });
    server.closeIdleConnections();
  });
}

Use file:notebook.db locally. For remote libSQL, configure its URL and token in your application rather than adding provider configuration to core. Never close the shared client at the end of each request.

Run npm run docs:check to check inserts and persistence after reopening SQLite.