@beignet/provider-db-drizzle

Drizzle ORM database providers for Beignet applications, one subpath export per database backend:

Each subpath installs a typed database port backed by Drizzle ORM. Your application still owns the schema, repository interfaces, and migration workflow.

The backend factories return DrizzleSqliteProvider<TSchema, PortName>, DrizzlePostgresProvider<TSchema, PortName>, and DrizzleMysqlProvider<TSchema, PortName>. SqliteConfig, PostgresConfig, and MysqlConfig describe validated config; provider config schemas remain internal.

Features

• Factory-based provider creation with your schema at the call site.
• Full TypeScript inference from your Drizzle schema.
• One subpath per backend — SQLite (libSQL/Turso), Postgres (node-postgres), and MySQL (mysql2) — with the same provider, Unit of Work, audit log, outbox, and idempotency surface.
• Keeps runtime provider wiring separate from Drizzle CLI configuration.
• Provides a Unit of Work helper that can build transaction-scoped repositories, audit logs, outbox recorders, and other app-owned ports from one Drizzle transaction client.

Current scope

@beignet/provider-db-drizzle/sqlite is the reference SQL shape for Beignet apps: keep schema and migrations app-owned, accept both a root Drizzle database and transaction client in repository factories, and create transaction-scoped app ports inside Unit of Work.

The /postgres and /mysql subpaths ship the same surface with backend-specific naming, so switching databases means changing the driver install, the subpath import, and the connection env var. Contracts, use cases, policies, and routes keep depending on ports; only the infra adapter changes.

The walkthrough below uses SQLite. The Postgres and MySQL sections cover what changes per backend. The CLI scaffolds any of the three: bun create beignet my-app --db sqlite|postgres|mysql (default sqlite).

Install

Database drivers are optional peer dependencies, so you install only the driver for the backend you use:

# SQLite (libSQL)
bun add @beignet/provider-db-drizzle @beignet/core drizzle-orm @libsql/client

# Postgres (node-postgres)
bun add @beignet/provider-db-drizzle @beignet/core drizzle-orm pg

# MySQL (mysql2)
bun add @beignet/provider-db-drizzle @beignet/core drizzle-orm mysql2
Copy

beignet doctor --strict checks the provider variant an app registers:

• SQLite requires SQLITE_DB_URL unless the app passes a client directly. SQLITE_DB_AUTH_TOKEN is optional for hosted libSQL/Turso.
• Postgres requires POSTGRES_DB_URL.
• MySQL requires MYSQL_DB_URL.

Doctor also checks standard Drizzle config, schema exports, database scripts, seed/reset entrypoints, and provider registration drift in generated apps.

Agent skills

This package ships a TanStack Intent skill for coding agents: @beignet/provider-db-drizzle#database-provider. Load it when wiring Drizzle providers, schema placement, Drizzle config, DbPort typing, repository adapters, Unit of Work, audit, idempotency, outbox tables, migrations, seed/reset helpers, devtools query instrumentation, or database doctor drift.

Setup

1. Define your schema

Create your Drizzle schema files wherever makes sense for your app. Framework usage keeps them under infra/db/schema/ so larger apps can split schema by feature:

// infra/db/schema/todos.ts
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const todos = sqliteTable("todos", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  completed: integer("completed", { mode: "boolean" }).notNull().default(false),
  createdAt: text("created_at").notNull(),
});
Copy

// infra/db/schema/index.ts
export { todos } from "./todos";
Copy

If the app uses Beignet's Drizzle-backed operational ports for audit, idempotency, or outbox, generate the provider-owned table exports into the app schema:

bun beignet db schema sync
Copy

That writes infra/db/schema/beignet.ts and adds export * from "./beignet"; to infra/db/schema/index.ts. After that, run the normal app-owned migration flow:

bun beignet db generate
bun beignet db migrate
Copy

Use --tables audit,idempotency,outbox to select a subset when adopting one operational port at a time.

2. Configure Drizzle CLI (build-time)

Create drizzle.config.ts in your app root for the Drizzle CLI:

// drizzle.config.ts
export default {
  schema: "./infra/db/schema/index.ts",
  out: "./drizzle",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.SQLITE_DB_URL!,
  },
};
Copy

3. Create the provider (runtime)

Import your schema and create the provider:

// server/providers.ts
import { createDrizzleSqliteProvider } from "@beignet/provider-db-drizzle/sqlite";
import * as schema from "@/infra/db/schema";

const drizzleSqliteProvider = createDrizzleSqliteProvider({ schema });

export const providers = [drizzleSqliteProvider];
Copy

4. Type your ports

Define your app's ports type:

// ports/index.ts
import type { DbPort } from "@beignet/provider-db-drizzle/sqlite";
import * as schema from "@/infra/db/schema";

export type AppPorts = {
  db: DbPort<typeof schema>;
  // other ports...
};
Copy

5. Wire it up in your server

// server/index.ts
import { createNextServer, createNextServerLoader } from "@beignet/next";
import { initialPorts } from "@/infra/port-wiring";
import { routes } from "@/server/routes";

export const getServer = createNextServerLoader(async () => {
  const { providers } = await import("./providers");

  return createNextServer({
    ports: initialPorts,
    providers,
    context: ({ ports }) => ({ ports }),
    routes,
  });
});
Copy

6. Expose repository ports to use cases

Use cases should depend on app-owned repository ports. Keep raw Drizzle access in infrastructure, then wire the repository into ctx.ports.

// infra/todos/drizzle-todo-repository.ts
import { Buffer } from "node:buffer";
import { and, desc, eq, lt, or } from "drizzle-orm";
import { cursorPageResult } from "@beignet/core/pagination";
import type { DrizzleSqliteDatabase } from "@beignet/provider-db-drizzle/sqlite";
import type { TodoRepository } from "@/features/todos/ports";
import * as schema from "@/infra/db/schema";

type TodoCursor = {
  sortBy: "createdAt";
  sortDirection: "desc";
  sortValue: string;
  id: string;
};

function encodeTodoCursor(row: typeof schema.todos.$inferSelect): string {
  return Buffer.from(
    JSON.stringify({
      sortBy: "createdAt",
      sortDirection: "desc",
      sortValue: row.createdAt,
      id: row.id,
    }),
  ).toString("base64url");
}

function decodeTodoCursor(cursor: string): TodoCursor {
  return JSON.parse(Buffer.from(cursor, "base64url").toString("utf8"));
}

export function createTodoRepository(
  db: DrizzleSqliteDatabase<typeof schema>,
): TodoRepository {
  return {
    async list(input) {
      const cursor = input.page.cursor
        ? decodeTodoCursor(input.page.cursor)
        : null;
      const rows = await db
        .select()
        .from(schema.todos)
        .where(
          cursor
            ? or(
                lt(schema.todos.createdAt, cursor.sortValue),
                and(
                  eq(schema.todos.createdAt, cursor.sortValue),
                  lt(schema.todos.id, cursor.id),
                ),
              )
            : undefined,
        )
        .orderBy(desc(schema.todos.createdAt), desc(schema.todos.id))
        .limit(input.page.limit + 1);
      const pageRows = rows.slice(0, input.page.limit);
      const nextCursor =
        rows.length > input.page.limit && pageRows.length > 0
          ? encodeTodoCursor(pageRows[pageRows.length - 1])
          : null;

      return cursorPageResult(pageRows, input.page, nextCursor);
    },
  };
}
Copy

Collect repositories in one infra factory:

// infra/db/repositories.ts
import type { DrizzleSqliteDatabase } from "@beignet/provider-db-drizzle/sqlite";
import { createTodoRepository } from "@/infra/todos/drizzle-todo-repository";
import * as schema from "./schema";

export function createRepositories(db: DrizzleSqliteDatabase<typeof schema>) {
  return {
    todos: createTodoRepository(db),
  };
}
Copy

Wire repositories into app ports with a typed app-owned database provider. The curried createProvider<Requires, Context, ServiceInput>() form types the required db port, the app context, and createServiceContext without casts. Register it after createDrizzleSqliteProvider, which installs the db port it requires:

// infra/db/provider.ts
import { createProvider } from "@beignet/core/providers";
import type { DbPort } from "@beignet/provider-db-drizzle/sqlite";
import type { AppContext } from "@/app-context";
import type { AppPorts } from "@/ports";
import type { AppServiceContextInput } from "@/server";
import { createRepositories } from "./repositories";
import type * as schema from "./schema";

export const appDatabaseProvider = createProvider<
  { db: DbPort<typeof schema> },
  AppContext,
  AppServiceContextInput
>()({
  name: "app-database",
  async setup({ ports }) {
    const providedPorts: Pick<AppPorts, "todos"> = createRepositories(
      ports.db.drizzle,
    );

    return { ports: providedPorts };
  },
});
Copy

Type ctx.ports with InferProviderPorts so provider-contributed ports, including the provider-owned db port, stay typed in app code:

// app-context.ts
import type { InferProviderPorts } from "@beignet/core/providers";
import type { AppPorts } from "@/ports";
import type { providers } from "@/server/providers";

export type AppRuntimePorts = AppPorts & InferProviderPorts<typeof providers>;

export type AppContext = {
  requestId: string;
  ports: AppRuntimePorts;
};
Copy

// features/todos/use-cases/list-todos.ts
import { normalizeCursorPage } from "@beignet/core/pagination";

export const listTodos = useCase.query("todos.list").run(async ({ ctx }) => {
  const page = normalizeCursorPage({}, { defaultLimit: 20, maxLimit: 100 });

  return ctx.ports.todos.list({ page });
});
Copy

ctx.ports.db.drizzle remains available as a provider-specific escape hatch for infrastructure code and one-off advanced Drizzle features. Do not make it the normal dependency for application use cases.

Installed ports

Each backend provider contributes ctx.ports.db, the standard Beignet DbPort with a Drizzle escape hatch. Database-backed app repositories, UOW, outbox, and idempotency ports are app-owned wiring layered on top of that provider. The port also exposes checkHealth() for app-owned readiness endpoints:

const health = await ctx.ports.db.checkHealth();

if (!health.ok) {
  return Response.json({ ok: false, database: health }, { status: 503 });
}
Copy

checkHealth() runs a cheap select 1 through the underlying client or pool. It does not run migrations, create tables, or mutate schema.

Client ownership and scaling

Env-backed providers create one database client per installed server instance and close it when that server stops. Cache the server loader at module scope so warm requests reuse that client; do not create a server or pool in each route.

Apps that need to share a client with Better Auth or other infrastructure can inject it directly. Keep a shared client app/process-owned because retryable server initialization may clean up providers while Better Auth retains the module-scoped client:

// infra/db/client.ts
import pg from "pg";

export const databaseClient = new pg.Pool({
  connectionString: process.env.POSTGRES_DB_URL,
  max: Number(process.env.POSTGRES_DB_POOL_MAX ?? 5),
});

let closePromise: Promise<void> | undefined;

export function closeDatabaseClient(): Promise<void> {
  closePromise ??= Promise.resolve().then(() => databaseClient.end());
  return closePromise;
}
Copy

// server/providers.ts
const databaseProvider = createDrizzlePostgresProvider({
  schema,
  client: databaseClient,
});
Copy

Injected clients are caller-owned by default. Use closeOnStop: true only for a client owned exclusively by that server. Apps sharing a client should wrap the successfully created server's public stop() to stop providers first and then call the idempotent closeDatabaseClient(). This keeps failed-boot retries from reusing a closed client while allowing workers and one-off commands to exit cleanly. Serverless process termination reclaims clients when the host does not call stop().

Provider audit assumes the standard URL env variable. Apps whose injected client genuinely uses another configuration source can add that variable to providerAudit.ignoreRequiredEnv in beignet.config.ts; this changes static audit only, not runtime validation.

Size a pool from the total deployment budget, not one process in isolation:

per-process pool max <=
  (database connection limit - operational reserve)
  / maximum concurrent processes sharing the database
Copy

Count web instances, workers, previews, tasks, migrations, and other services. Serverless deployments should use a small per-instance pool and a provider-managed pooled endpoint when available. Worker concurrency and pool size are separate controls: a small pool limits concurrent database work even when the queue claims many jobs, while a large pool multiplied across replicas can exhaust the database.

Local file: SQLite is a single-host writable database. Use hosted libSQL or a server database when multiple hosts need concurrent access. Reset, migration, and test commands may create separate transient clients, but must close them before exiting.

Devtools

When @beignet/devtools is registered before this provider, Drizzle query logging is recorded automatically under the db watcher. Events include the SQL query text, parameter count, port name, and provider name. Parameter values are redacted by default.

import { createDevtoolsProvider } from "@beignet/devtools";
import { createDrizzleSqliteProvider } from "@beignet/provider-db-drizzle/sqlite";

export const providers = [
  createDevtoolsProvider(),
  createDrizzleSqliteProvider({ schema }),
];
Copy

Failure behavior

Env-backed providers throw during startup when their required database URL is missing or the driver cannot create the connection/client. Runtime query errors come from Drizzle and the selected driver. Unit of Work rolls back the database transaction when the callback throws; if post-commit event publishing fails, the database transaction has already committed even though the Unit of Work rejects. Callers must not interpret that rejection as rollback or blindly retry non-idempotent work. Use the outbox for durable follow-up work.

SQLite permits one writer at a time. The SQLite adapter therefore queues Beignet-owned Unit of Work, root outbox-claim, and root idempotency-reservation transactions per Drizzle client instead of leaking local SQLITE_BUSY errors under concurrent requests. This is process-local coordination for SQLite's single-writer model, not a multi-host worker strategy; use Postgres or MySQL for distributed worker fleets.

MySQL can choose a transaction as an InnoDB deadlock victim during heavy contention. The MySQL adapter retries only Beignet-owned outbox-claim and idempotency-reservation transactions, up to eight attempts with exponential backoff, proportional jitter, and a 250 ms per-delay cap. It does not retry app-owned Unit of Work callbacks. Idempotency and outbox ports created from the transaction client supplied to a MySQL Unit of Work execute directly in that transaction; they do not open a nested savepoint or run the internal deadlock retry loop inside an already failed outer transaction.

Local and tests

Generated SQLite apps include database reset helpers for isolated repository and route tests. Use repository fakes for pure use-case tests, and use the Drizzle provider with an isolated database when testing SQL mapping, transactions, outbox rows, idempotency rows, or migration assumptions.

Deployment notes

Run migrations before booting code that depends on new schema. Keep repository interfaces app-owned so switching SQLite, Postgres, or MySQL changes infra wiring instead of contracts, use cases, policies, or routes.

Unit of work

Use createDrizzleSqliteUnitOfWork(...) when a use case needs a real database transaction. Your app still owns the repository interfaces; the helper only starts a Drizzle transaction, gives you the transaction client, and optionally flushes recorded domain events after commit.

// ports/index.ts
import type {
  DomainEventRecorderPort,
  UnitOfWorkPort,
} from "@beignet/core/ports";
import type { DbPort } from "@beignet/provider-db-drizzle/sqlite";
import * as schema from "@/infra/db/schema";
import type { TodoRepository } from "./todo-repository";

export type AppTransactionPorts = {
  todos: TodoRepository;
  events: DomainEventRecorderPort;
};

export type AppPorts = {
  db: DbPort<typeof schema>;
  todos: TodoRepository;
  uow: UnitOfWorkPort<AppTransactionPorts>;
};
Copy

// infra/todos/drizzle-todo-repository.ts
import type { DrizzleSqliteDatabase } from "@beignet/provider-db-drizzle/sqlite";
import type { TodoRepository } from "@/features/todos/ports";
import * as schema from "@/infra/db/schema";

export function createDrizzleTodoRepository(
  db: DrizzleSqliteDatabase<typeof schema>,
): TodoRepository {
  return {
    async create(input) {
      const [row] = await db.insert(schema.todos).values(input).returning();
      return row;
    },
  };
}
Copy

// infra/db/provider.ts
import { createDrizzleSqliteUnitOfWork } from "@beignet/provider-db-drizzle/sqlite";
import { createRepositories } from "./repositories";

// Inside the app database provider's setup({ ports }):
const providedPorts: Pick<AppPorts, "todos" | "uow"> = {
  ...createRepositories(ports.db.drizzle),
  uow: createDrizzleSqliteUnitOfWork({
    db: ports.db.drizzle,
    eventBus: ports.eventBus,
    createTransactionPorts: (tx, events) => ({
      ...createRepositories(tx),
      events,
    }),
  }),
};

return { ports: providedPorts };
Copy

Inside a use case, call transaction-scoped repositories through tx and record declared events through the use-case events helper:

const todo = await ctx.ports.uow.transaction(async (tx) => {
  const created = await tx.todos.create(input);
  await events.record(tx.events, todoCreated, { todoId: created.id });
  return created;
});
Copy

Recorded events are published only after Drizzle commits. If the transaction throws, events are not flushed. If event publishing fails after commit, transaction(...) rejects but the database transaction is already committed; use an outbox when events or jobs need durable delivery guarantees.

Durable outbox

Use createDrizzleSqliteOutboxPort(...) when events or jobs must be recorded in the same database transaction as the business write, then drained after commit:

import {
  createOutboxEventRecorder,
  drainOutbox,
} from "@beignet/core/outbox";
import {
  createDrizzleSqliteAuditLogPort,
  createDrizzleSqliteAuditLogSetupStatements,
  createDrizzleSqliteIdempotencyPort,
  createDrizzleSqliteIdempotencySetupStatements,
  createDrizzleSqliteOutboxAdminPort,
  createDrizzleSqliteOutboxPort,
  createDrizzleSqliteOutboxSetupStatements,
  createDrizzleSqliteUnitOfWork,
} from "@beignet/provider-db-drizzle/sqlite";
Copy

Add Beignet's audit, outbox, and idempotency tables to your app-owned migration or bootstrap flow:

for (const statement of createDrizzleSqliteAuditLogSetupStatements()) {
  await client.execute(statement);
}

for (const statement of createDrizzleSqliteIdempotencySetupStatements()) {
  await client.execute(statement);
}

for (const statement of createDrizzleSqliteOutboxSetupStatements()) {
  await client.execute(statement);
}
Copy

Then expose transaction-scoped outbox-backed event recording:

uow: createDrizzleSqliteUnitOfWork({
  db: ports.db.drizzle,
  createTransactionPorts: (tx) => {
    const audit = createDrizzleSqliteAuditLogPort(tx);
    const idempotency = createDrizzleSqliteIdempotencyPort(tx);
    const outbox = createDrizzleSqliteOutboxPort(tx);

    return {
      ...createRepositories(tx),
      audit,
      events: createOutboxEventRecorder(outbox, {
        tracing: ports.tracing,
      }),
      idempotency,
      outbox,
    };
  },
});
Copy

Any port that must commit with the business write belongs in createTransactionPorts: repositories, audit logs, outbox-backed events/jobs, feature history repositories, and durable idempotency records. Root ports are still useful for reads and background work, but they do not participate in the current transaction unless you rebuild them from tx.

Use createDrizzleSqliteAuditLogPort(...) at the root app port for ordinary audit writes, and rebuild it from tx when audit rows must commit atomically with the business write.

Use createDrizzleSqliteIdempotencyPort(...) at the root app port for ordinary retry-safe commands, and rebuild it from tx when the idempotency reservation, business write, audit row, outbox records, and replay result must commit together.

Use createDrizzleSqliteOutboxAdminPort(...) as a root operational port for maintenance contexts. It powers beignet outbox list, show, requeue, purge, and prune; keep transaction-scoped writes on createDrizzleSqliteOutboxPort(...).

Durable audit log

Use createDrizzleSqliteAuditLogPort(...) when AuditLogPort should write through SQL instead of an in-memory test adapter:

import { createAmbientAuditLog } from "@beignet/core/server";
import { createInstrumentedAuditLog } from "@beignet/core/ports";
import {
  createDrizzleSqliteAuditLogPort,
  createDrizzleSqliteAuditLogSetupStatements,
} from "@beignet/provider-db-drizzle/sqlite";

for (const statement of createDrizzleSqliteAuditLogSetupStatements()) {
  await client.execute(statement);
}

const audit = createAmbientAuditLog(
  createInstrumentedAuditLog({
    audit: createDrizzleSqliteAuditLogPort(db),
    instrumentation: ports,
  }),
);
Copy

The default table is audit_log; pass tableName to both setup and port creation if your app uses a different table name. The adapter normalizes audit entries, applies Beignet's default metadata redaction, stores metadata as JSON text, and accepts either a root database or transaction client. Pass redact for a final app-owned redaction step, or createId in runtimes without globalThis.crypto.randomUUID().

Durable idempotency

Use createDrizzleSqliteIdempotencyPort(...) when runIdempotently(...) needs durable storage instead of the in-memory test adapter:

import { runIdempotently } from "@beignet/core/idempotency";
import {
  createDrizzleSqliteIdempotencyPort,
  createDrizzleSqliteIdempotencySetupStatements,
} from "@beignet/provider-db-drizzle/sqlite";

for (const statement of createDrizzleSqliteIdempotencySetupStatements()) {
  await client.execute(statement);
}

const idempotency = createDrizzleSqliteIdempotencyPort(db);

await runIdempotently(idempotency, {
  namespace: "posts.create",
  key: input.idempotencyKey,
  scope: { actorId: ctx.actor.id, tenantId: ctx.tenant.id },
  fingerprint,
  run: () => ctx.ports.posts.create(input),
});
Copy

The default table is idempotency_records; pass tableName to both setup and port creation if your app uses a different table name. Expired records are removed during reservation, failed in-progress reservations are released, and completed matching records replay their stored result.

Unfinished reservations use a 300-second TTL by default, independently from the completed replay ttlSec. complete(...) and fail(...) must match an in-progress reservation with both the provided fingerprint and the opaque reservation token returned by reserve(...). When the database reports that no row matched, the port throws DrizzleSqliteIdempotencyMutationError instead of silently doing nothing, so workflow bugs such as double completion, a missing reservation, or a mismatched fingerprint surface immediately. The error exposes action, namespace, key, and scopeKey for logging and debugging.

Drain from a worker, cron route, or schedule:

await drainOutbox({
  outbox: ports.outbox,
  registry: outboxRegistry,
  eventBus: ports.eventBus,
  jobs: ports.jobs,
});
Copy

The default table is outbox_messages; pass tableName to both setup and port creation if your app uses a different table name.

The adapter preserves Beignet's durable workflow semantics in SQL:

• attempts increments when a worker claims a message.
• max_attempts stores the maximum total attempts for the message.
• available_at stores retry timing and backoff decisions.
• trace_context_json stores Beignet's optional versioned trace carrier.
• locked_until and claim_token provide a lease so workers can compete.
• deadLettered is the terminal state for messages that should no longer be retried automatically.

drainOutbox(...) owns retry classification and backoff computation. The Drizzle SQLite adapter stores the resulting state and enforces claim ownership.

Existing outbox tables need one nullable column before deploying this version:

-- SQLite and Postgres
ALTER TABLE outbox_messages ADD COLUMN trace_context_json text;

-- MySQL
ALTER TABLE outbox_messages ADD COLUMN trace_context_json longtext;
Copy

Fresh tables created by the exported setup statements already include it. Rows written before the migration have no carrier and start a new trace when delivered.

Configuration

The provider reads configuration from environment variables with the SQLITE_ prefix:

SQLITE_DB_URL accepts every URL scheme @libsql/client accepts: libsql:// and file: plus http://, https://, ws://, and wss:// for local or self-hosted sqld instances.

Example .env

# For local development
SQLITE_DB_URL=file:local.db

# For Turso's hosted libSQL service
SQLITE_DB_URL=libsql://my-app-db.turso.io
SQLITE_DB_AUTH_TOKEN=eyJhbGciOiJFZERTQSIsInR5cCI...

# For a local sqld instance
SQLITE_DB_URL=http://127.0.0.1:8080
Copy

Postgres

@beignet/provider-db-drizzle/postgres mirrors the SQLite surface on top of Drizzle's node-postgres driver. Every DrizzleSqliteX export has a DrizzlePostgresX counterpart with the same behavior, so the walkthrough above applies; this section covers what changes per backend.

Install

bun add @beignet/provider-db-drizzle @beignet/core drizzle-orm pg
Copy

pg is an optional peer dependency (>=8.11.0). The other backends' drivers are not required.

Configuration

The provider reads configuration from environment variables with the POSTGRES_ prefix:

POSTGRES_DB_URL=postgres://postgres:postgres@localhost:5432/my_app
Copy

Drizzle CLI

Define schema with drizzle-orm/pg-core table builders such as pgTable, and point drizzle.config.ts at the postgresql dialect:

// drizzle.config.ts
export default {
  schema: "./infra/db/schema/index.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.POSTGRES_DB_URL!,
  },
};
Copy

Provider

// server/providers.ts
import { createDrizzlePostgresProvider } from "@beignet/provider-db-drizzle/postgres";
import * as schema from "@/infra/db/schema";

const drizzlePostgresProvider = createDrizzlePostgresProvider({ schema });

export const providers = [drizzlePostgresProvider];
Copy

Parameters:

• options.schema (required): Your Drizzle schema object
• options.portName (optional): Port name, defaults to "db"
• options.pool (optional): node-postgres pool options (Omit<PoolConfig, "connectionString">) such as max or ssl
• options.client (optional): app-supplied node-postgres Pool; when set, provider config and options.pool are not used
• options.closeOnStop (optional, injected clients only): close the supplied pool on provider stop; defaults to false

The provider is named "drizzle-postgres" and installs a DbPort with the typed Drizzle database under drizzle (a NodePgDatabase<TSchema>) and the node-postgres Pool under pool as the escape hatch.

Type repository factories with DrizzlePostgresDatabase<TSchema> so they accept both the root database and a transaction client (DrizzlePostgresTransaction). Both types accept any Drizzle Postgres driver, so the same repository code runs against node-postgres at runtime and PGlite in tests:

import type { DrizzlePostgresDatabase } from "@beignet/provider-db-drizzle/postgres";
import type { TodoRepository } from "@/features/todos/ports";
import * as schema from "@/infra/db/schema";

export function createTodoRepository(
  db: DrizzlePostgresDatabase<typeof schema>,
): TodoRepository {
  // same repository shape as the SQLite walkthrough
}
Copy

Unit of work

createDrizzlePostgresUnitOfWork(...) takes the same options as the SQLite helper: db, createTransactionPorts, optional eventBus, and optional transactionConfig for Drizzle's Postgres transaction configuration.

import { createDrizzlePostgresUnitOfWork } from "@beignet/provider-db-drizzle/postgres";

// Inside the app database provider's setup({ ports }):
uow: createDrizzlePostgresUnitOfWork({
  db: ports.db.drizzle,
  eventBus: ports.eventBus,
  createTransactionPorts: (tx, events) => ({
    ...createRepositories(tx),
    events,
  }),
}),
Copy

Outbox and idempotency

import {
  createDrizzlePostgresAuditLogPort,
  createDrizzlePostgresAuditLogSetupStatements,
  createDrizzlePostgresIdempotencyPort,
  createDrizzlePostgresIdempotencySetupStatements,
  createDrizzlePostgresOutboxAdminPort,
  createDrizzlePostgresOutboxPort,
  createDrizzlePostgresOutboxSetupStatements,
} from "@beignet/provider-db-drizzle/postgres";
Copy

Run the setup statements through your app-owned migration or bootstrap flow:

for (const statement of createDrizzlePostgresIdempotencySetupStatements()) {
  await pool.query(statement);
}

for (const statement of createDrizzlePostgresAuditLogSetupStatements()) {
  await pool.query(statement);
}

for (const statement of createDrizzlePostgresOutboxSetupStatements()) {
  await pool.query(statement);
}
Copy

Then build transaction-scoped ports exactly as in the SQLite walkthrough:

createTransactionPorts: (tx) => {
  const audit = createDrizzlePostgresAuditLogPort(tx);
  const idempotency = createDrizzlePostgresIdempotencyPort(tx);
  const outbox = createDrizzlePostgresOutboxPort(tx);

  return {
    ...createRepositories(tx),
    audit,
    events: createOutboxEventRecorder(outbox, {
      tracing: ports.tracing,
    }),
    idempotency,
    outbox,
  };
},
Copy

The audit, outbox, and idempotency factories accept a root database or transaction client plus the same options as the SQLite ports. The idempotency port throws DrizzlePostgresIdempotencyMutationError when complete(...) or fail(...) does not match an in-progress reservation.

Use createDrizzlePostgresOutboxAdminPort(...) from a root maintenance context when CLI/admin tooling needs to inspect, requeue, purge, or prune outbox rows.

MySQL

@beignet/provider-db-drizzle/mysql mirrors the same surface on top of Drizzle's mysql2 driver, with DrizzleMysqlX naming.

MySQL 8.0 or newer is required: the outbox uses FOR UPDATE SKIP LOCKED for claim leases and the idempotency table uses the utf8mb4_0900_bin collation for binary key comparison. MariaDB is not supported.

Install

bun add @beignet/provider-db-drizzle @beignet/core drizzle-orm mysql2
Copy

mysql2 is an optional peer dependency (>=3.6.0).

Configuration

The provider reads configuration from environment variables with the MYSQL_ prefix:

MYSQL_DB_URL=mysql://root:root@localhost:3306/my_app
Copy

Drizzle CLI

Define schema with drizzle-orm/mysql-core table builders such as mysqlTable, and point drizzle.config.ts at the mysql dialect:

// drizzle.config.ts
export default {
  schema: "./infra/db/schema/index.ts",
  out: "./drizzle",
  dialect: "mysql",
  dbCredentials: {
    url: process.env.MYSQL_DB_URL!,
  },
};
Copy

Provider

// server/providers.ts
import { createDrizzleMysqlProvider } from "@beignet/provider-db-drizzle/mysql";
import * as schema from "@/infra/db/schema";

const drizzleMysqlProvider = createDrizzleMysqlProvider({ schema });

export const providers = [drizzleMysqlProvider];
Copy

Parameters:

• options.schema (required): Your Drizzle schema object
• options.portName (optional): Port name, defaults to "db"
• options.pool (optional): mysql2 pool options (Omit<PoolOptions, "uri">) such as connectionLimit or TLS settings
• options.client (optional): app-supplied mysql2 pool; when set, provider config and options.pool are not used
• options.closeOnStop (optional, injected clients only): close the supplied pool on provider stop; defaults to false
• options.mode (optional): Drizzle relational-query mode, "default" or "planetscale"; use "planetscale" when connecting to PlanetScale

The provider is named "drizzle-mysql" and installs a DbPort with the typed Drizzle database under drizzle and the mysql2 pool under pool as the escape hatch.

Type repository factories with DrizzleMysqlDatabase<TSchema>; DrizzleMysqlTransaction is the transaction client type.

Unit of work, audit log, outbox, and idempotency

The factories mirror the Postgres section with Mysql naming:

import {
  createDrizzleMysqlAuditLogPort,
  createDrizzleMysqlAuditLogSetupStatements,
  createDrizzleMysqlIdempotencyPort,
  createDrizzleMysqlIdempotencySetupStatements,
  createDrizzleMysqlOutboxAdminPort,
  createDrizzleMysqlOutboxPort,
  createDrizzleMysqlOutboxSetupStatements,
  createDrizzleMysqlUnitOfWork,
} from "@beignet/provider-db-drizzle/mysql";
Copy

Run the setup statements through your app-owned migration or bootstrap flow:

for (const statement of createDrizzleMysqlAuditLogSetupStatements()) {
  await pool.query(statement);
}

for (const statement of createDrizzleMysqlIdempotencySetupStatements()) {
  await pool.query(statement);
}

for (const statement of createDrizzleMysqlOutboxSetupStatements()) {
  await pool.query(statement);
}
Copy

Unit of Work and transaction-port wiring are identical to the SQLite and Postgres examples. The idempotency port throws DrizzleMysqlIdempotencyMutationError on unmatched complete(...) or fail(...) mutations.

Use createDrizzleMysqlOutboxAdminPort(...) from a root maintenance context when CLI/admin tooling needs to inspect, requeue, purge, or prune outbox rows.

MySQL enforces length limits on idempotency keys: storage_key is varchar(512) and namespace, key, and scope are varchar(255). Over-length keys fail loudly instead of being truncated, so keep idempotency namespaces, keys, and scope values short.

Design notes

These choices are deliberate and shared across the three backends:

• ISO-8601 text timestamps. All three dialects store outbox and idempotency timestamps, plus audit occurred_at, as ISO-8601 UTC strings in text columns. This keeps retry timing, lease expiry, replay semantics, and audit ordering identical across backends, and lexicographic ordering matches chronological ordering.
• MySQL key length limits. SQLite and Postgres use unbounded text columns for idempotency keys; MySQL needs bounded varchar columns for its unique index, so over-length keys fail loudly rather than truncating silently.
• Shared conformance suite. One conformance suite runs the same Unit of Work, outbox, and idempotency behavior tests against SQLite (libSQL), Postgres, and MySQL. It races independent worker ports to prove that outbox messages are claimed once, stale lease owners cannot complete re-claimed work, and concurrent matching idempotency requests execute one protected operation, so durable workflow semantics do not drift between backends.

Advanced usage

Multiple databases

This provider reads one connection from the SQLITE_DB_URL and SQLITE_DB_AUTH_TOKEN environment variables. Registering it twice, even with different portName values, reads the same env vars, so both ports would connect to the same database. Use portName only to rename the single provider-owned port.

To connect a second database, wire it through an app-owned provider that loads its own connection config, following the same typed custom provider pattern shown above:

// infra/db/analytics-provider.ts
import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";
import { createProvider } from "@beignet/core/providers";
import type { DbPort } from "@beignet/provider-db-drizzle/sqlite";
import { z } from "zod";
import * as analyticsSchema from "./analytics-schema";

const AnalyticsDbConfigSchema = z.object({
  DB_URL: z.string().min(1),
  DB_AUTH_TOKEN: z.string().optional(),
});

export const analyticsDbProvider = createProvider({
  name: "analytics-db",

  config: {
    schema: AnalyticsDbConfigSchema,
    envPrefix: "ANALYTICS_",
  },

  async setup({ config }) {
    if (!config) {
      throw new Error(
        "[analyticsDbProvider] Missing config. Set ANALYTICS_DB_URL (and optional ANALYTICS_DB_AUTH_TOKEN).",
      );
    }

    const client = createClient({
      url: config.DB_URL,
      authToken: config.DB_AUTH_TOKEN,
    });

    const drizzleDb = drizzle(client, { schema: analyticsSchema });
    const analyticsDb: DbPort<typeof analyticsSchema> = {
      drizzle: drizzleDb,
      client,
      async checkHealth() {
        try {
          await client.execute("select 1");
          return { ok: true, metadata: { adapter: "sqlite" } };
        } catch (error) {
          return {
            ok: false,
            message: error instanceof Error ? error.message : String(error),
            metadata: { adapter: "sqlite" },
          };
        }
      },
    };

    return {
      ports: { analyticsDb },
      async stop() {
        await client.close();
      },
    };
  },
});
Copy

// ports/index.ts
export type AppPorts = {
  db: DbPort<typeof mainSchema>;
  analyticsDb: DbPort<typeof analyticsSchema>;
};
Copy

Accessing the Drizzle instance

The DbPort exposes the typed Drizzle database instance:

import { eq } from "drizzle-orm";

const db = ctx.ports.db.drizzle; // LibSQLDatabase<TSchema>

// All Drizzle operations are available:
await db.select().from(schema.todos);
await db.insert(schema.todos).values({ id: "1", title: "Hello" });
await db.update(schema.todos).set({ title: "Updated" }).where(eq(schema.todos.id, "1"));
await db.delete(schema.todos).where(eq(schema.todos.id, "1"));

// Prefer repository ports and createDrizzleSqliteUnitOfWork(...) for application
// workflows. Use raw db access in infra, scripts, and vendor-specific escape
// hatches.

// Access the underlying libSQL client for advanced operations:
const client = ctx.ports.db.client;
const result = await client.execute("SELECT * FROM todos WHERE id = ?", ["1"]);

// Use this from readiness routes:
const health = await ctx.ports.db.checkHealth();
Copy

Key design principles

Runtime vs. build-time separation

This provider follows a clean separation of concerns:

• Build-time (Drizzle CLI): Configured via drizzle.config.ts

  • Used for generating migrations
  • Used for introspecting the database
  • Lives in your app repository

• Runtime (Provider): Configured via factory function

  • Used for connecting to the database at runtime
  • Used for executing queries in your use cases
  • Gets the schema from your imports

Schema location independence

The provider does not care where your schema file lives. You:

1. Define your schema files wherever makes sense (infra/db/schema/, db/schema.ts, etc.)
2. Import them in your app: import * as schema from "@/infra/db/schema"
3. Pass it to the factory: createDrizzleSqliteProvider({ schema })

This keeps the provider flexible and your app in control of its structure.

API reference

All exports below come from @beignet/provider-db-drizzle/sqlite. The /postgres and /mysql subpaths export the same surface with DrizzlePostgres and DrizzleMysql naming; the Postgres and MySQL sections above cover the backend-specific differences: provider options (pool, and mode for MySQL), the DbPort escape hatch (pool instead of client), and the mutation error class names.

Schema subpaths

Each backend also exports canonical Beignet operational table definitions:

• @beignet/provider-db-drizzle/sqlite/schema
• @beignet/provider-db-drizzle/postgres/schema
• @beignet/provider-db-drizzle/mysql/schema

Each schema subpath exports auditLog, idempotencyRecords, and outboxMessages. Prefer beignet db schema sync in apps so the selected exports are re-exported from your app schema index before Drizzle Kit generates migrations.

DbPort<TSchema>

The port interface exposed on ctx.ports.db:

interface DbPort<TSchema extends Record<string, any> = any> {
  drizzle: LibSQLDatabase<TSchema>;
  client: Client;
  checkHealth(): Promise<DbHealth>;
}
Copy

• drizzle: The typed Drizzle database instance for ORM operations
• client: The underlying libSQL client for advanced operations not covered by Drizzle
• checkHealth: A cheap select 1 dependency check for app-owned readiness endpoints

createDrizzleSqliteProvider<TSchema>(options)

Factory function to create a Drizzle SQLite provider.

Parameters:

• options.schema (required): Your Drizzle schema object
• options.portName (optional): Port name, defaults to "db"
• options.client (optional): app-supplied libSQL client; when set, provider configuration is not required
• options.closeOnStop (optional, injected clients only): close the supplied client on provider stop; defaults to false

Returns: A provider that can be registered with createServer, createNextServer, or another Beignet server adapter.

Example:

const provider = createDrizzleSqliteProvider({
  schema: mySchema,
  portName: "db", // optional
});
Copy

createDrizzleSqliteUnitOfWork<TSchema, TxPorts>(options)

Factory function to create a transaction-backed UnitOfWorkPort.

Parameters:

• options.db (required): The root LibSQLDatabase<TSchema> instance
• options.createTransactionPorts (required): Factory that receives the Drizzle transaction client and event recorder
• options.eventBus (optional): Event bus used to flush recorded events after commit
• options.transactionConfig (optional): Drizzle transaction configuration

Returns: A UnitOfWorkPort<TxPorts> that runs work inside db.transaction(...).

createDrizzleSqliteOutboxPort<TSchema>(db, options?)

Factory function to create a SQL-backed OutboxPort from a root Drizzle database or transaction client.

Parameters:

• db (required): A DrizzleSqliteDatabase<TSchema> root database or transaction
• options.tableName (optional): Outbox table name, defaults to "outbox_messages"
• options.now (optional): Test clock

createDrizzleSqliteOutboxAdminPort<TSchema>(db, options?)

Factory function to create a SQL-backed OutboxAdminPort for operational outbox maintenance. Use it from root app or CLI contexts, not transaction ports.

Parameters:

• db (required): A DrizzleSqliteDatabase<TSchema> root database or transaction
• options.tableName (optional): Outbox table name, defaults to "outbox_messages"
• options.now (optional): Test clock

Supports: listing and counting outbox messages, showing one message, requeueing deadLettered messages, purging deadLettered rows, and pruning old delivered rows.

createDrizzleSqliteOutboxSetupStatements(options?)

Returns SQL setup statements for the app-owned outbox table and indexes. Run these through your migration/bootstrap flow or translate them into your normal Drizzle migrations.

createDrizzleSqliteAuditLogPort<TSchema>(db, options?)

Factory function to create a SQL-backed AuditLogPort from a root Drizzle database or transaction client.

Parameters:

• db (required): A DrizzleSqliteDatabase<TSchema> root database or transaction
• options.tableName (optional): Audit log table name, defaults to "audit_log"
• options.redact (optional): Final app-owned redaction hook
• options.createId (optional): Audit row ID factory

createDrizzleSqliteAuditLogSetupStatements(options?)

Returns SQL setup statements for the app-owned audit log table and indexes. Run these through your migration/bootstrap flow or translate them into your normal Drizzle migrations.

createDrizzleSqliteIdempotencyPort<TSchema>(db, options?)

Factory function to create a SQL-backed IdempotencyPort from a root Drizzle database or transaction client.

Parameters:

• db (required): A DrizzleSqliteDatabase<TSchema> root database or transaction
• options.tableName (optional): Idempotency table name, defaults to "idempotency_records"
• options.now (optional): Test clock

createDrizzleSqliteIdempotencySetupStatements(options?)

Returns SQL setup statements for the app-owned idempotency table and indexes. Run these through your migration/bootstrap flow or translate them into your normal Drizzle migrations.

License

MIT