Databases and migrations

@lenso/db makes a native Drizzle database a plugin resource. It does not replace Drizzle, choose a global database, or migrate on startup. Choose the driver for the host and keep the schema and queries in the application.

This page covers published @lenso/db@0.1.1 with @lenso/core@0.2.0, audited against source 549b987. The actual tarball retains the provider exports below. Match your installed artifacts and lockfile to the supported versions.

Choose a provider

The Bun factories accept an existing client instead of connection or filename. That client remains caller-owned. PostgreSQL accepts a URL or native PostgreSQL connection options; passing a MySQL client is rejected. SQLite accepts native constructor options when creating its connection. The source baseline pins Drizzle ORM 0.45.3 and Drizzle Kit 0.31.11.

D1 is a separate import with no Bun runtime code. Keep Bun SQL, Bun SQLite and filesystem adapters out of the Worker import graph. See deployment for host assembly.

Compose multiple databases

Distinct IDs describe instances; dependencies bind the exact objects. This small Bun example checks two independently owned connections without creating tables:

1import { definePlugin, startApp } from "@lenso/core";2import { createBunSqlitePlugin } from "@lenso/db/bun-sqlite";3import { sql } from "drizzle-orm";4
5const primaryDb = createBunSqlitePlugin({6  id: "notes.primary.db", filename: ":memory:", schema: {},7});8const archiveDb = createBunSqlitePlugin({9  id: "notes.archive.db", filename: ":memory:", schema: {},10});11const connections = definePlugin({12  id: "notes.connections",13  requires: [primaryDb, archiveDb],14  setup(context) {15    return { primary: context.get(primaryDb), archive: context.get(archiveDb) };16  },17});18const app = await startApp({ plugins: [connections, primaryDb, archiveDb] });19try {20  const databases = app.get(connections);21  databases.primary.run(sql`select 1`);22  databases.archive.run(sql`select 1`);23} finally {24  await app.stop();25}

A new plugin object with the same ID does not satisfy an existing dependency. For a custom driver, acquire inside connect(context), register context.onCleanup immediately, then return the native database. Register cleanup only for resources that factory owns. Setup rollback and normal shutdown use the same ownership rules; a dependent service releases before its database.

Reuse Notes across dialects

The existing Notes application supplies a useful boundary:

1. schema-pg.ts uses UUIDs and timestamps with time zones. schema-sqlite.ts uses text IDs and millisecond timestamps.
2. Dialect-specific query factories accept native Drizzle types. The shared NotesQueries interface contains only the business operations Notes needs.
3. createNotesPlugin({ id, database, authentication, queries }) binds those exact resources. The same ordinary async service serves CLI, Fetch and oRPC.

This does not promise that all drivers share transactions or connection APIs. The D1 Notes queries use single-statement mutations and RETURNING, without assuming PostgreSQL transactions or synchronous SQLite access.

Apply migrations explicitly

Run these commands in the reviewed source checkout, after building framework packages, against a database you have created and are authorized to change. They are example scripts, not commands installed by the DB package:

1# From examples/notes; DATABASE_URL already identifies an isolated PostgreSQL DB.2bun run generate:pg3# Review generated SQL before applying it.4bun run migrate:pg5
6# For a local SQLite file; SQLITE_PATH is set by the application owner.7bun run generate:sqlite8bun run migrate:sqlite

Generation is for a schema change; do not regenerate committed migrations merely to start the example. Installation, startApp and the provider factories apply none of them. Migration scripts use their documented working-directory semantics; the Notes runtime separately resolves configured paths against its app root.

For D1, the Worker configuration points migrations_dir at the reviewed SQLite migration directory and the owner invokes wrangler d1 migrations apply <database-name> --local. Wrangler records D1 history in d1_migrations; Bun SQLite uses Drizzle's journal. Use one runner for each store.

Notes' 0001_owner migration includes the Auth session baseline in its existing application history. Do not additionally run a separate Auth migrator on that database. Auth owns the session schema; Notes snapshots track Notes. Older rows receive the reserved __legacy_unowned__ owner, which cannot log in. Migration does not assign old private records to the first configured user. See the migration ownership record.

Protect data at the service boundary

A connection is privileged infrastructure access, not user authorization. Notes authenticates an audience-specific actor, revalidates its session, and checks the actual record owner. It also includes owner identity in update/delete SQL conditions. Lists select only that owner's rows. Owner, creation time and actor are not assignable through business JSON.

Do not expose a raw DB client through Web, CLI or MCP. Obtain connection secrets through the host's secret authority and keep them out of source, argv and logs. Files and Tasks require their own durable associations and policies; sharing a database does not confer permission.

Expose business operations without exposing the DB

The current Notes operation factory returns the exact sidecar plugin, shared operations and an optional Manage selection. Notes list/read/remove reuse the same owner-enforcing service. Each declares context: true; the entry binds verified evidence separately from business JSON. CLI uses named operationBinding; MCP uses its own launch binding and independent selection.

A Manage adapter borrows the running app and filters operation permission, then the service revalidates audience and record ownership. It adds no arbitrary SQL operation, database browser, schema migration, connection-secret read or administrator role. Database lifecycle remains with the resource owner. Static descriptors/catalog are not database health checks.

Diagnose the resource boundary

packages/db/test/resources.test.ts verifies exact-instance isolation, no implicit migration, owned shutdown and borrowed-client survival. Real Notes PostgreSQL and local workerd/D1 checks are described in testing and troubleshooting.