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.

Recommended structure

Keep schema, app repositories, and feature ports in predictable places:

txt

infra/
  db/
    schema/
      index.ts
      posts.ts
      comments.ts
    repositories.ts
    test-database.ts
  posts/
    drizzle-post-repository.ts
features/
  posts/
    ports.ts
    seeds/
      demo-posts.ts
    tests/
      factories/
        post.ts
        index.ts
      persistence.test.ts
drizzle/
  *.sql
drizzle.config.ts

Feature code owns the repository interface. Infra owns the Drizzle implementation. Server wiring adapts the raw Drizzle port into app-facing repository ports.

Repository ports

Use cases should depend on repository ports, not a raw database client:

typescript

// features/posts/ports.ts
import type {
  CursorPage,
  CursorPageInfo,
  PageResult,
  SortOption,
} from "@beignet/core/pagination";

export interface PostRepository {
  findMany(input: {
    page: CursorPage;
    cursor?: { sortValue: string; id: string } | null;
    filters?: { status?: PostStatus };
    sort?: SortOption<"createdAt" | "title">;
  }): Promise<PageResult<Post, CursorPageInfo>>;
  findBySlug(slug: string): Promise<Post | null>;
  create(input: CreatePostInput): Promise<Post>;
}

Infrastructure adapts a concrete database to that port:

typescript

// infra/posts/drizzle-post-repository.ts
import { cursorPageResult } from "@beignet/core/pagination";
import { desc, eq } from "drizzle-orm";
import type { DrizzleSqliteDatabase } from "@beignet/provider-db-drizzle/sqlite";
import type { PostRepository } from "@/features/posts/ports";
import * as schema from "@/infra/db/schema";
import { encodePostCursor } from "./post-cursor";

export function createDrizzlePostRepository(
  db: DrizzleSqliteDatabase<typeof schema>,
): PostRepository {
  return {
    async findMany(input) {
      const rows = await db
        .select()
        .from(schema.posts)
        .orderBy(desc(schema.posts.createdAt))
        .limit(input.page.limit + 1);
      const pageRows = rows.slice(0, input.page.limit);
      const nextCursor =
        rows.length > input.page.limit && pageRows.length > 0
          ? encodePostCursor(pageRows[pageRows.length - 1])
          : null;

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

    async findBySlug(slug) {
      const [row] = await db
        .select()
        .from(schema.posts)
        .where(eq(schema.posts.slug, slug))
        .limit(1);

      return row ? toPost(row) : null;
    },
  };
}

The key detail is the DrizzleSqliteDatabase parameter. It accepts both the root Drizzle database and a transaction client, so the same repository factory works for normal reads and transaction-scoped writes.

Cursor encoding is app plumbing: generated resources include small app-owned base64url cursor encode/decode helpers next to the repository, and hand-written repositories should keep equivalent helpers.

Factories and seeds

Factories and seeds should stay feature-owned and persist through repository ports. This keeps test/demo data on the same app boundary as use cases:

typescript

// features/posts/tests/factories/post.ts
import { createFactory } from "@beignet/core/testing";
import type { AppContext } from "@/app-context";

export const postFactory = createFactory("posts.post", {
  defaults: ({ sequence }) => ({
    name: `Post ${sequence}`,
  }),
  persist: (ctx: AppContext, input) => ctx.ports.posts.create(input),
});

typescript

// features/posts/seeds/demo-posts.ts
import { defineSeed } from "@beignet/core/testing";
import type { AppContext } from "@/app-context";
import { postFactory } from "@/features/posts/tests/factories";

export const demoPostsSeed = defineSeed("posts.demo-posts", {
  run: async (ctx: AppContext) => {
    await postFactory.createList(ctx, 3);
  },
});

Generate the starter files with:

bash

beignet make factory posts/post
beignet make seed posts/demo-posts

The app-owned infra/db/seed.ts entrypoint decides which feature seeds run for local/demo environments. New apps do not scaffold seeds; add infra/db/seed.ts and a db:seed package script alongside your first generated seeds. Beignet never auto-runs seeds during migrations or application startup.

Repository factory

Collect app repositories in one infra factory:

typescript

// infra/db/repositories.ts
import type { DrizzleSqliteDatabase } from "@beignet/provider-db-drizzle/sqlite";
import { createDrizzlePostRepository } from "@/infra/posts/drizzle-post-repository";
import type { AppRepositoryPorts } from "@/ports";
import * as schema from "./schema";

export function createRepositories(
  db: DrizzleSqliteDatabase<typeof schema>,
): AppRepositoryPorts {
  return {
    posts: createDrizzlePostRepository(db),
  };
}

This keeps server/index.ts from importing every repository adapter directly and gives Unit of Work one place to create transaction-scoped ports.

Server wiring

The Drizzle provider installs the provider-owned db port. An app-owned database provider in infra/db/provider.ts turns it into repository ports, idempotency, and Unit of Work. Use the curried createProvider<Requires, Context, ServiceInput>() form so the required db port, the app context, and the provided ports stay typed without casts:

typescript

// infra/db/provider.ts
import { createProvider } from "@beignet/core/providers";
import {
  createDrizzleSqliteIdempotencyPort,
  createDrizzleSqliteUnitOfWork,
  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 repositories = createRepositories(ports.db.db);
    const idempotency = createDrizzleSqliteIdempotencyPort(ports.db.db);

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

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

Register it in server/providers.ts after createDrizzleSqliteProvider, which installs the db port it requires. The repository keys stay deferred in infra/app-ports.ts, and the server fails boot if a deferred port is still unbound after providers have started.

ctx.ports.db.db is an infrastructure escape hatch. Keep it out of use cases. Use cases should call ctx.ports.posts or ctx.ports.uow.transaction(...).

List queries

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

typescript

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.

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:

typescript

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, input.tenantId),
      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:

typescript

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.

Transactions

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

typescript

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:

typescript

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

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

Then record events inside the transaction:

typescript

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. 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.

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:

bash

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:

typescript

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

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

typescript

// 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.db,
  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:

typescript

// 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.

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

bash

beignet db generate
beignet db migrate
beignet db seed
beignet db reset

beignet db generate and beignet db migrate delegate to the app's Drizzle Kit scripts. beignet db seed and beignet db reset delegate to app-owned entrypoints such as infra/db/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.

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.

Testing

Repository tests should run against an isolated local database. The starter writes infra/db/test-database.ts with this shape: an in-memory database that applies the app's migrations, the same DDL path production uses. Keep the helper in infra and the behavior test with the feature:

typescript

// infra/db/test-database.ts
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();
    },
  };
}

typescript

// features/posts/tests/persistence.test.ts
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.

When to use what

Use ctx.ports.posts 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.