Database and transactions

Beignet keeps database access behind app-owned ports. It gives you repository and Unit of Work conventions, but it does not hide Drizzle, Prisma, Kysely, or SQL behind a generic ORM abstraction.

The recommended framework path today is Drizzle through @beignet/provider-db-drizzle. The default starter uses the /sqlite subpath, a libSQL-backed provider that works with local SQLite files in development and Turso's hosted libSQL in production. Pass --db postgres or --db mysql to bun create beignet to scaffold the same structure against the other backends — see Other databases.

Read this page when a feature needs durable persistence, transactions, repository tests, seeds, or local database lifecycle commands.

Follow a todo write and read

Start with the SQLite Todos app from Quickstart. It already has repositories, provider wiring, and a checked-in initial migration. From the app root, prepare the database and start the app:

bun beignet db migrate
bun beignet db status
bun run dev

Sign in, create a todo, complete it, and reload the page. The todo should remain completed. Another account should not see it in its list.

Follow those two operations through the existing files:

The write passes the authenticated owner into the repository:

// features/todos/use-cases/create-todo.ts (excerpt)
return ctx.ports.uow.transaction(async (tx) =>
  tx.todos.create({ userId: user.id, title: input.title }),
);

The repository reads the same owner scope on a reload:

// infra/todos/drizzle-todo-repository.ts (excerpt)
const rows = await db
  .select()
  .from(schema.todos)
  .where(eq(schema.todos.userId, userId))
  .orderBy(desc(schema.todos.createdAt))
  .limit(page.limit)
  .offset(page.offset);

The port describes the operations the feature needs. The adapter supplies the SQL. Read the complete repository when changing its methods; you can use the existing wiring for the persistence change below.

Add a persistence field

Record when a todo was last updated. Add a nullable updated_at column to the todos table in infra/db/schema/index.ts:

--- a/infra/db/schema/index.ts
+++ b/infra/db/schema/index.ts
@@ -1,1 +1,2 @@
     createdAt: text("created_at").notNull(),
+    updatedAt: text("updated_at"),

Existing rows start with NULL, because their last update time is unknown. Set the column when the repository updates a todo:

--- a/infra/todos/drizzle-todo-repository.ts
+++ b/infra/todos/drizzle-todo-repository.ts
@@ -1,1 +1,4 @@
-        .set({ completed: input.completed })
+        .set({
+          completed: input.completed,
+          updatedAt: new Date().toISOString(),
+        })

This is persistence metadata. To return it to clients, also add it to TodoSchema in features/todos/schemas.ts and the repository's toTodo mapping.

Generate a migration, review its SQL under drizzle/, and apply it:

bun beignet db generate
bun beignet db migrate
bun beignet db status

Expect an ALTER TABLE adding the nullable column and a current database status after migration. Commit the schema and generated migration together. Apply migrations as a deployment step before serving requests with the new code. See Migrations and local setup for schema sync, shared packages, seeding, and reset commands.

Verify persistence

Add this complete test. The starter's createTestDatabase() applies checked-in migrations to an isolated SQLite file, and createRepositories() supplies the same adapter as the running app:

// infra/db/todo-persistence.test.ts
import { expect, test } from "@/lib/beignet-test";
import { eq } from "drizzle-orm";
import { todos, user } from "./schema";
import { createTestDatabase } from "./test-database";

test("updating a todo persists its completion and update time", async () => {
  const database = await createTestDatabase();
  try {
    await database.db.insert(user).values({
      id: "database-owner", name: "Owner", email: "database@example.com",
      createdAt: new Date(), updatedAt: new Date(),
    });
    const todo = await database.repositories.todos.create({
      userId: "database-owner", title: "Check persistence",
    });
    const [before] = await database.db.select().from(todos).where(eq(todos.id, todo.id));
    expect(before.updatedAt).toBeNull();
    const startedAt = Date.now();
    await database.repositories.todos.update(todo.id, { completed: true });
    const [stored] = await database.db.select().from(todos).where(eq(todos.id, todo.id));
    expect(stored.completed).toBe(true);
    expect(typeof stored.updatedAt).toBe("string");
    expect(Date.parse(stored.updatedAt ?? "") >= startedAt).toBe(true);
    expect(Date.parse(stored.updatedAt ?? "") <= Date.now()).toBe(true);
    expect((await database.repositories.todos.findById(todo.id))?.completed).toBe(true);
  } finally {
    await database.close();
  }
});

bun run typecheck
bun run test
bun beignet doctor --strict

Expect the persistence test and existing Todos tests to pass. In the app, complete a todo and reload again. The behavior should match the first check; the database row now also records its update time.

Recommended structure

The examples in this section use the default SQLite Todos app from Quickstart. These files already exist in the starter:

features/todos/
  ports.ts
  schemas.ts
  use-cases/
infra/
  todos/drizzle-todo-repository.ts
  db/
    schema/index.ts
    repositories.ts
    provider.ts
    test-database.ts
drizzle/
  *.sql
drizzle.config.ts

Feature code owns the repository interface. Infra implements it, and the server installs the repositories through the database provider. Create, list, complete, and delete a todo before changing persistence; the same operations should work after your changes.

Repository ports

Use cases depend on this complete feature port, rather than a raw database client. The authenticated user's ID scopes list reads; update and delete use cases load the todo and authorize its owner before writing.

// features/todos/ports.ts
import type {
  OffsetPage,
  OffsetPageInfo,
  PageResult,
} from "@beignet/core/pagination";
import type { Todo } from "@/features/todos/schemas";

export type ListTodosResult = PageResult<Todo, OffsetPageInfo>;

export type NewTodo = {
  userId: string;
  title: string;
};

export type UpdateTodoData = {
  completed: boolean;
};

export interface TodoRepository {
  list(userId: string, page: OffsetPage): Promise<ListTodosResult>;
  findById(id: string): Promise<Todo | null>;
  create(input: NewTodo): Promise<Todo>;
  update(id: string, input: UpdateTodoData): Promise<Todo>;
  delete(id: string): Promise<void>;
}

The SQLite implementation below is a complete file after adding the update-time column. It applies both limit and offset, scopes rows and totals to the same user, and implements every method the port declares:

// infra/todos/drizzle-todo-repository.ts
import "@beignet/core/server-only";
import { offsetPageResult } from "@beignet/core/pagination";
import type { DrizzleSqliteDatabase } from "@beignet/provider-db-drizzle/sqlite";
import { count, desc, eq } from "drizzle-orm";
import type {
  NewTodo,
  TodoRepository,
  UpdateTodoData,
} from "@/features/todos/ports";
import type { Todo } from "@/features/todos/schemas";
import * as schema from "@/infra/db/schema";

type TodoRow = typeof schema.todos.$inferSelect;

function toTodo(row: TodoRow): Todo {
  return {
    id: row.id,
    userId: row.userId,
    title: row.title,
    completed: row.completed,
    createdAt: row.createdAt,
  };
}

export function createDrizzleTodoRepository(
  db: DrizzleSqliteDatabase<typeof schema>,
): TodoRepository {
  return {
    async list(userId, page) {
      const rows = await db
        .select()
        .from(schema.todos)
        .where(eq(schema.todos.userId, userId))
        .orderBy(desc(schema.todos.createdAt))
        .limit(page.limit)
        .offset(page.offset);
      const [{ total }] = await db
        .select({ total: count() })
        .from(schema.todos)
        .where(eq(schema.todos.userId, userId));

      return offsetPageResult(rows.map(toTodo), page, total);
    },
    async findById(id: string) {
      const [row] = await db
        .select()
        .from(schema.todos)
        .where(eq(schema.todos.id, id))
        .limit(1);

      return row ? toTodo(row) : null;
    },
    async create(input: NewTodo) {
      const todo = {
        id: crypto.randomUUID(),
        userId: input.userId,
        title: input.title,
        completed: false,
        createdAt: new Date().toISOString(),
      };
      const [row] = await db.insert(schema.todos).values(todo).returning();

      if (!row) {
        throw new Error("Failed to create todo");
      }

      return toTodo(row);
    },
    async update(id: string, input: UpdateTodoData) {
      const [row] = await db
        .update(schema.todos)
        .set({ completed: input.completed, updatedAt: new Date().toISOString() })
        .where(eq(schema.todos.id, id))
        .returning();

      if (!row) {
        throw new Error(`Failed to update todo ${id}`);
      }

      return toTodo(row);
    },
    async delete(id: string) {
      await db.delete(schema.todos).where(eq(schema.todos.id, id));
    },
  };
}

DrizzleSqliteDatabase accepts both the root database and a transaction client, so the factory works for normal reads and transaction-scoped writes. The Todos starter uses offset pagination. For a complete cursor-paginated resource, follow Build your first feature; its generated Projects repository decodes the cursor and applies it to the database query.

Repository factory

This complete starter file collects transaction-compatible repositories:

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

export function createRepositories(
  db: DrizzleSqliteDatabase<typeof schema>,
): Omit<AppTransactionPorts, "idempotency"> {
  return {
    todos: createDrizzleTodoRepository(db),
  };
}

Unit of Work calls the same factory with its transaction client. When a new feature adds a repository, update its app port and this factory together; the resource generator does that wiring for you.

Server wiring

The Drizzle provider installs db. The app-owned provider below binds the Todos repository, idempotency, and Unit of Work, and checks migration readiness on startup. This is the complete default SQLite infra/db/provider.ts:

// infra/db/provider.ts
import "@beignet/core/server-only";
import { createProvider } from "@beignet/core/providers";
import {
  createDrizzleSqliteIdempotencyPort,
  createDrizzleSqliteUnitOfWork,
  type DbPort,
} from "@beignet/provider-db-drizzle/sqlite";
import type { AppPorts } from "@/ports";
import { ensureDatabaseReady } from "./database-ready";
import { createRepositories } from "./repositories";
import type * as schema from "./schema";

export const starterDatabaseProvider = createProvider<{
  db: DbPort<typeof schema>;
}>()({
  name: "starter-database",

  async setup({ ports }) {
    const dbPort = ports.db;
    if (!dbPort) {
      throw new Error(
        "starterDatabaseProvider requires a db port. Register createDrizzleSqliteProvider({ schema }) before it.",
      );
    }

    const repositories = createRepositories(dbPort.drizzle);
    const idempotency = createDrizzleSqliteIdempotencyPort(dbPort.drizzle);

    const providedPorts: Pick<AppPorts, "idempotency" | "todos" | "uow"> = {
      ...repositories,
      idempotency,
      uow: createDrizzleSqliteUnitOfWork({
        db: dbPort.drizzle,
        createTransactionPorts: (tx) => ({
          ...createRepositories(tx),
          idempotency: createDrizzleSqliteIdempotencyPort(tx),
        }),
      }),
    };

    return {
      ports: providedPorts,
      async start() {
        await ensureDatabaseReady(dbPort.client);
      },
    };
  },
});

The starter registers this provider after drizzleSqliteProvider in server/providers.ts. Keep both entries and the existing auth and logger providers. The server checks that every deferred port was contributed before serving requests.

Run these checks after editing the repository or wiring:

bun beignet db status
bun run typecheck
bun run test
bun beignet doctor --strict

Expect the database status to be current and all checks to pass. In the app, verify that a saved todo survives a reload and only appears for its owner. When changing pagination, test at least two pages and ensure their rows do not overlap. Testing explains the starter's isolated test database.

When to use what

Use ctx.ports.todos directly for simple reads and operations that do not need a transaction. Use ctx.ports.uow.transaction(...) for writes that coordinate multiple repositories, emit domain events, enqueue jobs, send notifications, or need a clear commit boundary.

Transactions

Use ctx.ports.uow.transaction(...) when a workflow needs multiple operations to commit or rollback together:

const createPostUseCase = useCase
  .command("posts.create")
  .input(CreatePostInputSchema)
  .output(PostSchema)
  .run(async ({ ctx, input }) =>
    ctx.ports.uow.transaction((tx) => tx.posts.create(input)),
  );

When a use case records domain events, expose the transaction-local recorder in your transaction ports and publish events after commit:

type AppTransactionPorts = AppRepositoryPorts & {
  events: DomainEventRecorderPort;
};

uow: createDrizzleSqliteUnitOfWork({
  db: ports.db.drizzle,
  eventBus: ports.eventBus,
  createTransactionPorts: (tx, events) => ({
    ...createRepositories(tx),
    events,
  }),
});

Then record events inside the transaction:

const post = await ctx.ports.uow.transaction(async (tx) => {
  const created = await tx.posts.create(input);
  await events.record(tx.events, postCreated, { postId: created.id });
  return created;
});

The mechanics: events recorded inside the transaction are discarded on rollback; on commit, the helper validates, parses, and flushes them to eventBus. If flushing fails after commit, transaction(...) rejects but the database transaction is already committed. A caller cannot infer rollback from that rejection and should not blindly retry non-idempotent work. See Workflows and state machines for the after-commit concept and Outbox when events or jobs need durable delivery guarantees.

Put every durable write that must commit with the business change behind a transaction-scoped port created from the Unit of Work transaction client: repository writes, history rows, audit entries, outbox records, and durable idempotency reservations. The Drizzle/libSQL convention rebuilds those ports from tx inside createTransactionPorts; root ports stay useful for reads and background work but do not join the current transaction.

SQLite allows one writer at a time. Beignet queues its SQLite Unit of Work, root outbox-claim, and root idempotency-reservation transactions per Drizzle client so concurrent requests wait instead of receiving a local SQLITE_BUSY error. That coordination is intentionally process-local. Use Postgres or MySQL when outbox workers run across multiple hosts; their adapters use transactional row locking and SKIP LOCKED for competing claims.

InnoDB may choose a transaction as a deadlock victim under heavy contention. The MySQL adapter retries Beignet-owned outbox-claim and idempotency-reservation transactions up to eight times with exponential backoff, proportional jitter, and a 250 ms per-delay cap. App-owned Unit of Work callbacks are never retried by this mechanism.

Factories and seeds

Keep factories under features/<feature>/tests/factories/ and seeds under features/<feature>/seeds/. Generate them for a feature that already exists:

bun beignet make factory todos.todo
bun beignet make seed todos.demo-todos

Fill in the generated definitions with your feature's required fields and repository calls. Todos require an existing owner; select that account explicitly in your seed setup. See Factories and seeds for the factory API.

beignet make seed creates the app-owned server/seed.ts entrypoint and the db:seed script when needed. That entrypoint decides which seeds run and provides their context. Beignet never runs seeds during migrations or startup.

List queries

The advanced examples below use an app-owned Posts feature with filtering, versioning, and tenancy. They are excerpts for that feature, not replacement files for the Todos starter. Use the Projects tutorial for a runnable generated resource before adapting these patterns.

Use @beignet/core/pagination for list boundaries. Contracts still own their query schema, while use cases normalize the validated input before calling a repository:

import { normalizeCursorPage } from "@beignet/core/pagination";

const page = normalizeCursorPage(input, {
  defaultLimit: 20,
  maxLimit: 100,
});

return ctx.ports.posts.findMany({
  page,
  cursor: input.cursor ? decodePostCursor(input.cursor) : null,
  filters: { status: input.status },
  sort: { field: "createdAt", direction: "desc" },
});

List responses should use items for the records and page for pagination metadata. Generated resources use cursor metadata with nextCursor and hasMore, filter names with case-insensitive contains matching, and sort only by allowlisted fields. Keep filters and sort values as app-owned plain objects so Beignet does not become a query builder.

Aggregates

When a feature needs summary data — counts, grouped counts — give the repository port a purpose-built aggregate method instead of paging findMany and counting rows in the use case. Paging a list method to compute a count is an anti-pattern: it transfers every row to count them and couples the summary to pagination limits.

Name grouped counts countBy<Dimension> and return an app-typed shape:

// features/issues/ports.ts
import type { TenantScope } from "@beignet/core/tenancy";

export interface IssueRepository {
  // ...
  countByStatus(scope: TenantScope): Promise<Record<IssueStatus, number>>;
}

The adapter implements the aggregate as one grouped query. Aggregates share the port's row-visibility semantics: the same soft-delete, archive, and tenant scoping that filters findMany applies to the counts, so a summary never reports records the list would hide.

Optimistic concurrency

Generated CRUD resources include this convention by default: schemas expose a numeric version, update bodies send it back, repositories compare and increment it in one statement, and stale updates map to the generated conflict catalog error.

Repository writes include the expected version in the WHERE clause and increment it in the same statement:

import { tenantScopeId } from "@beignet/core/tenancy";

const [row] = await db
  .update(schema.posts)
  .set({
    title: input.title,
    version: input.expectedVersion + 1,
    updatedAt: new Date().toISOString(),
  })
  .where(
    and(
      eq(schema.posts.slug, input.slug),
      eq(schema.posts.tenantId, tenantScopeId(scope)),
      eq(schema.posts.version, input.expectedVersion),
      isNull(schema.posts.deletedAt),
    ),
  )
  .returning();

If no row is updated, check whether the active row still exists. Return a not-found result when it does not, and a conflict result when the row exists with a different version. Use cases can map that conflict to an app error such as POST_VERSION_CONFLICT. Action routes that have no request body can carry the expected version in a header instead.

Soft delete and archive

For records that matter later, prefer lifecycle columns over hard deletes:

export const posts = sqliteTable("posts", {
  id: text("id").primaryKey(),
  tenantId: text("tenant_id").notNull(),
  version: integer("version").notNull().default(1),
  deletedAt: text("deleted_at"),
  archivedAt: text("archived_at"),
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at").notNull(),
});

Normal findMany and findBy... repository methods should filter out deletedAt and archivedAt records by default; expose explicit recovery or admin methods when an app needs the rest. Use soft delete to retain records for recovery, audit, or compliance; use archive to move a record out of the active workflow; reserve hard delete for records your app may physically erase.

Record history

Audit logs answer "who did what"; record history answers "what changed on this record." When a feature needs history, keep it behind a feature-owned repository port (for example PostHistoryRepository.record(...) with before and after snapshots, actor fields, and occurredAt), and write history rows inside the same Unit of Work transaction as the business change so history commits and rolls back with the data. For large or sensitive records, store redacted snapshots or field-level patches instead of full JSON. The important convention is that history is append-only and transaction-scoped.

Testing

Repository tests should run against an isolated local database. The starter writes infra/db/test-database.ts, which applies the app's migrations to an isolated SQLite file. The persistence check above uses that helper. For a custom fixture, keep the helper in infra and feature behavior tests with the feature. The following Posts example is a separate fixture:

// infra/db/test-database.ts (excerpt)
import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";
import { migrate } from "drizzle-orm/libsql/migrator";
import { createRepositories } from "./repositories";
import * as schema from "./schema";

export async function createTestDatabase() {
  const client = createClient({ url: "file::memory:" });
  const db = drizzle(client, { schema });
  await migrate(db, { migrationsFolder: "drizzle" });

  return {
    repositories: createRepositories(db),
    reset: async () => {
      await client.execute("DELETE FROM posts");
    },
    close: async () => {
      client.close();
    },
  };
}

// features/posts/tests/persistence.test.ts (excerpt)
import { createDatabaseTestHarness } from "@beignet/core/testing";
import { demoPostsSeed } from "@/features/posts/seeds";
import { postFactory } from "@/features/posts/tests/factories";

const databaseHarness = createDatabaseTestHarness({
  create: createTestDatabase,
  ctx: (database) => ({ repositories: database.repositories }),
  reset: (database) => database.reset(),
  close: (database) => database.close(),
  factories: [postFactory],
  seeds: [demoPostsSeed],
});

afterEach(async () => {
  await databaseHarness.cleanup();
});

const { ctx } = await databaseHarness.setup({ seed: true });
const post = await postFactory.create(ctx, {
  title: "Database conventions",
  content: "Use repository ports from use cases.",
});

expect(
  await ctx.repositories.posts.findBySlug({
    slug: post.slug,
    tenantId: post.tenantId,
  }),
).toMatchObject({ id: post.id });

Use createNoopUnitOfWork(...) for pure use-case tests that do not need a real database transaction. Use a real local database test when the behavior belongs to SQL, indexes, joins, constraints, or repository mapping.

Factories and seeds live with the feature because they describe app data, not database tables. Their persist functions should call repository ports so the same setup works against memory ports, isolated local databases, or transaction scoped test contexts.

Other databases

@beignet/provider-db-drizzle ships one subpath per backend — /sqlite (libSQL), /postgres (node-postgres), and /mysql (mysql2, MySQL 8.0+) — and all three expose the same provider, Unit of Work, outbox, and idempotency surface. Everything on this page carries over: contracts, use cases, policies, and routes keep depending on ports; only the infra adapter and provider wiring change.

Pick the backend when you create the app:

bun create beignet my-app --db postgres

--db accepts sqlite (the default), postgres, and mysql; in interactive mode a database prompt appears alongside the other setup prompts. The starter scaffolds the chosen backend end to end: provider wiring, an idiomatic Drizzle schema, the vendored initial migration (including the provider's idempotency setup statements), POSTGRES_DB_URL or MYSQL_DB_URL env examples, and a matching infra/db/test-database.ts. Later make resource and make feature runs detect the app's backend from infra/db/repositories.ts and generate dialect-correct schema and repository code.

Postgres apps need a running Postgres 14+ server for development and builds; MySQL apps need MySQL 8.0+. beignet db generate and beignet db migrate work unchanged for every dialect — each starter sets the matching drizzle-kit dialect — but Postgres and MySQL need the server running first. See Quickstart for docker one-liners.

Timestamps are ISO-8601 text in every dialect

Scaffolded app tables are idiomatic per dialect — native booleans, varchar ids on MySQL — with one deliberate exception: timestamp columns are ISO-8601 UTC strings in text columns in all three dialects. Cursors, optimistic concurrency checks, and contract responses compare and serialize timestamps as strings, so keeping the storage format identical keeps pagination and conflict semantics identical across backends. A later release may move the Postgres starter to native timestamptz.

Testing per backend

Each starter writes a dialect-matched infra/db/test-database.ts. SQLite tests use an in-memory libSQL database, and Postgres tests run against in-process PGlite — both are zero infrastructure. MySQL has no in-process engine, so tests that go through createTestDatabase() need a real server: the generated helper reads MYSQL_TEST_URL and throws with a docker one-liner when it is unset. The MySQL starter's own generated tests use in-memory fakes and pass without a server.

Switching an existing app

Apps created before --db existed, or apps changing backends after creation, switch manually. For Postgres, install the driver (bun add pg), set POSTGRES_DB_URL, change the drizzle.config.ts dialect to "postgresql", and swap the subpath imports:

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

export const providers = [createDrizzlePostgresProvider({ schema })];

// infra/db/provider.ts — inside the app database provider's setup({ ports })
import {
  createDrizzlePostgresIdempotencyPort,
  createDrizzlePostgresUnitOfWork,
} from "@beignet/provider-db-drizzle/postgres";

uow: createDrizzlePostgresUnitOfWork({
  db: ports.db.drizzle,
  createTransactionPorts: (tx) => ({
    ...createRepositories(tx),
    idempotency: createDrizzlePostgresIdempotencyPort(tx),
  }),
}),

Repository factories take DrizzlePostgresDatabase<typeof schema> instead of DrizzleSqliteDatabase, and the outbox and idempotency tables come from createDrizzlePostgresOutboxSetupStatements() and createDrizzlePostgresIdempotencySetupStatements() run through your migration flow. MySQL mirrors this with @beignet/provider-db-drizzle/mysql, MYSQL_DB_URL, and DrizzleMysql naming.

The @beignet/provider-db-drizzle README is the deep per-backend reference, including pool options, the PlanetScale mode for MySQL, and the design notes shared across backends.

Migrations and local setup

Keep Drizzle CLI config at the app root:

// drizzle.config.ts
export default {
  schema: "./infra/db/schema/index.ts",
  out: "./drizzle",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.SQLITE_DB_URL ?? "file:local.db",
    authToken: process.env.SQLITE_DB_AUTH_TOKEN,
  },
};

New apps ship with the initial migration vendored into the scaffold's drizzle/ folder, so beignet db migrate is the first database command you run. There is no bootstrap DDL at application boot: the schema comes entirely from migrations, the vendored one plus the ones you generate.

When you add Beignet's Drizzle-backed operational ports for audit, idempotency, or outbox, first bring your app schema's provider table re-exports in sync with the installed providers:

bun beignet db schema sync

That command idempotently writes infra/db/schema/beignet.ts, re-exports it from infra/db/schema/index.ts, and leaves SQL generation to your app's normal Drizzle Kit scripts. Pass --tables audit,idempotency,outbox to sync only the tables you are adopting in the next migration.

If your Drizzle schema lives in a shared package, list that source in beignet.config.ts so doctor and provider audits can verify operational tables there:

import { defineConfig } from "@beignet/cli/config";

export default defineConfig({
  database: {
    schemaSources: ["@acme/db/schema"],
  },
});

Use Beignet database lifecycle commands from the app root when the schema or local data changes:

bun beignet db generate
bun beignet db migrate
bun beignet db status
bun beignet db seed
bun beignet db reset

beignet db generate and beignet db migrate delegate to the app's Drizzle Kit scripts. beignet db status delegates to the read-only infra/db/migration-status.ts entrypoint, compares checked-in timestamps and SQL hashes with database history, and exits 2 when migrations are pending. beignet db seed and beignet db reset delegate to app-owned entrypoints such as server/seed.ts and infra/db/reset.ts. The CLI checks prerequisites before it runs the package script, and doctor reports drift in the same places, plus missing schema index exports and reset files that no longer mention BEIGNET_ALLOW_DATABASE_RESET.

MCP-aware coding agents can stay on the structured Beignet surface throughout this workflow. Call db_schema_sync to preview or apply provider-table schema re-exports, then call db with generate and migrate, followed by the read-only db_status tool. The db tool also supports seed and reset, bounds captured output, cancels the complete child process tree with the request, and applies a configurable timeout. All three tools return the same versioned reports as the CLI/library APIs. Lifecycle dryRun validates prerequisites and reports the app-owned script without executing it; it does not simulate the script's SQL or data changes.

For local SQLite development, keep SQLITE_DB_URL unset or set it to a file: URL. For hosted libSQL deployments such as Turso, set SQLITE_DB_URL and SQLITE_DB_AUTH_TOKEN in the deployment environment and run migrations as an explicit deployment step. Treat seeds as local/demo data unless the app owns a separate production seed entrypoint. The generated reset script refuses to run against non-local database URLs unless BEIGNET_ALLOW_DATABASE_RESET=true is set.

Connection ownership and pool sizing

Create one database client per running app process and share it with every integration that uses the same database. Generated apps keep that singleton in infra/db/client.ts; Better Auth and the Beignet Drizzle provider both import it. Keep that shared client app/process-owned: a failed server boot may clean up providers and retry, while Better Auth still references the same module-scoped client.

// infra/db/client.ts (Postgres)
import pg from "pg";
import { env } from "@/lib/env";

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

let closePromise: Promise<void> | undefined;

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

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

Injected clients are caller-owned by default. Set closeOnStop: true only when the client belongs exclusively to that Beignet server and can be discarded after failed initialization. When a client is shared with Better Auth, leave it app-owned. Generated server/index.ts wraps the successfully created server's public stop() so it stops providers first and then calls the idempotent closeDatabaseClient(). Failed boot cleanup does not close the shared client, so createNextServerLoader(...) can retry safely. Process termination reclaims it when a serverless host does not call stop(). When no client is injected, the provider creates its own env-backed client and always closes it. When the provider owns shutdown, a close failure rejects server.stop() so the process entrypoint can report the incomplete shutdown.

When devtools is registered before the Drizzle provider, database events expose the query length, parameter count, port name, and provider name. They do not record raw SQL or parameter values because SQL statements can contain inline application data.

Provider audit treats each database URL as required for the standard setup. If an injected platform client genuinely does not use that variable, declare the exception explicitly without weakening runtime validation:

import { defineConfig } from "@beignet/cli/config";

export default defineConfig({
  providerAudit: {
    ignoreRequiredEnv: ["POSTGRES_DB_URL"],
  },
});

Budget connections across the whole deployment:

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

Count web replicas, serverless instances, queue workers, previews, tasks, and migration jobs. Generated Postgres and MySQL apps default their per-process pool maximum to 5; this is a conservative starting point, not an automatic capacity decision. Serverless deployments should generally use a smaller pool and a provider-managed pooled database endpoint. Worker concurrency can exceed pool size, but database-heavy jobs will then wait for connections.

Local file: SQLite is suitable for one writable host. Use hosted libSQL, Postgres, or MySQL when multiple hosts need concurrent database access. Migration, reset, and test commands may create separate transient clients, but they must close those clients before exiting.