Databases: Drizzle on libSQL

The table is a Drizzle schema. $inferSelect gives the row type, so the type of a query and the type of the table cannot drift apart.

src/server/schema.tsts

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

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

export type Note = typeof notes.$inferSelect;

The connection module reads DATABASE_URL (and DATABASE_AUTH_TOKEN for a remote database). Without them it opens an in-memory database.

src/server/db.server.tsts

import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";
import * as schema from "./schema";

function open() {
  const authToken = process.env["DATABASE_AUTH_TOKEN"];
  const client = createClient({
    url: process.env["DATABASE_URL"] ?? ":memory:",
    ...(authToken === undefined ? {} : { authToken }),
  });
  return { client, db: drizzle(client, { schema }) };
}

// One connection per process, also across the reloads of `dusk dev`.
const shared = globalThis as typeof globalThis & { __notesDb?: ReturnType<typeof open> };
const opened = (shared.__notesDb ??= open());

export const db = opened.db;

/**
 * Creates the table. A real app runs `drizzle-kit migrate` when it deploys and has no such code; this page keeps it here so
 * that the example is complete.
 */
export const ready: Promise<unknown> = opened.client.execute(
  "create table if not exists notes (id integer primary key autoincrement, title text not null unique, owner text not null)",
);

Input checks use Standard Schema, as in the server functions page; src/schema.ts is the same small stand-in for Zod, Valibot or ArkType.

src/schema.tsts

import type { StandardSchemaV1, StandardSchemaV1Result } from "@shadow-js/dusk/server";

export function schema<T>(
  check: (value: unknown) => StandardSchemaV1Result<T>,
): StandardSchemaV1<T, T> {
  return { "~standard": { version: 1, vendor: "app", validate: check } };
}

function field(value: unknown, name: string): unknown {
  return typeof value === "object" && value !== null && name in value ? Reflect.get(value, name) : undefined;
}

export const newNote = schema<{ title: string }>((value) => {
  const title = field(value, "title");
  return typeof title === "string" && title.trim() !== "" && title.length <= 80
    ? { value: { title: title.trim() } }
    : { issues: [{ message: "title is required and at most 80 characters", path: ["title"] }] };
});

export const renameNote = schema<{ id: number; title: string }>((value) => {
  const id = field(value, "id");
  const title = field(value, "title");
  return typeof id === "number" && Number.isInteger(id) && typeof title === "string" && title.trim() !== ""
    ? { value: { id, title: title.trim() } }
    : { issues: [{ message: "id and title are required", path: [] }] };
});

Queries in server functions#

A server function is the place for a query: it is typed end to end, its input is validated before your code runs, and the build keeps it out of the browser. Two details matter for a database.

Map the database's errors to HTTP errors. A unique-key violation is the caller's fault and should answer 409, not 500. Drizzle wraps the driver's error in a DrizzleQueryError whose cause is the driver's error; the SQLite code for a unique violation is 2067. Any other database error stays an unexpected error: the handler hides its message from the client and reports it to onError.

Use a transaction for more than one write. db.transaction commits when the callback returns and rolls back when it throws.

src/server/notes.tsts

import { HttpError, serverFn } from "@shadow-js/dusk/server";
import { LibsqlError } from "@libsql/client";
import { DrizzleQueryError, eq } from "drizzle-orm";
import { newNote, renameNote } from "../schema";
import { db, ready } from "./db.server";
import { type Note, notes } from "./schema";

/** Whether an error is the database refusing a duplicate of a unique column. */
function isUniqueViolation(error: unknown): boolean {
  return (
    error instanceof DrizzleQueryError &&
    error.cause instanceof LibsqlError &&
    error.cause.rawCode === 2067
  );
}

export const listNotes = serverFn({ method: "GET" }, async (): Promise<Note[]> => {
  await ready;
  return db.select().from(notes).orderBy(notes.id);
});

export const addNote = serverFn({ input: newNote }, async ({ title }): Promise<Note> => {
  await ready;
  try {
    const [created] = await db.insert(notes).values({ title, owner: "anonymous" }).returning();
    if (created === undefined) throw new Error("the insert returned no row");
    return created;
  } catch (error) {
    if (isUniqueViolation(error)) throw new HttpError(409, "That title exists");
    throw error;
  }
});

/** Renames a note and writes an audit row in one transaction: if the second write fails, the first is undone. */
export const rename = serverFn({ input: renameNote }, async ({ id, title }): Promise<Note> => {
  await ready;
  return db.transaction(async (tx) => {
    const [updated] = await tx.update(notes).set({ title }).where(eq(notes.id, id)).returning();
    if (updated === undefined) throw new HttpError(404, "No such note");
    if (title === "forbidden") throw new Error("the audit write failed");
    return updated;
  });
});

Reading in a loader#

A loader on the server calls the function directly; a loader on a client navigation calls its stub. The page does not care which.

src/routes/notes.tsxtsx

import { useLoaderData } from "@shadow-js/router";
import type { JSX } from "@shadow-js/web";
import { For } from "@shadow-js/web";
import { listNotes } from "../server/notes";
import type { Note } from "../server/schema";

export const loader = (): Promise<Note[]> => listNotes();

export default function Notes(): JSX.Element {
  const notes = useLoaderData<Note[]>();
  return (
    <ul>
      <For each={notes() ?? []}>{(note) => <li>{note.title}</li>}</For>
    </ul>
  );
}

src/entry-server.tsts

import type { HandlerOptions } from "@shadow-js/dusk/server";
import { reported } from "./errors";

const options: Omit<HandlerOptions, "manifest"> = {
  security: { allowedHosts: ["localhost"] },
  onError(error, info) {
    reported.push({ phase: info.phase, message: error instanceof Error ? error.message : String(error) });
  },
};

export default options;