Database access — one connection and a unit of work

One plugin — DatabasePlugin from @orthacms/database — opens a single Drizzle client over pg. Every other plugin injects it.

import { Injectable } from '@nestjs/common';
import { InjectDatabase, type Database } from '@orthacms/database';

@Injectable()
export class ReviewsService {
    constructor(@InjectDatabase() private readonly db: Database) {}
}

Annotate with Database, not the dialect type — a dialect change stays a one-package edit.
Outside DI (scripts, seeds): getDatabase() and getPool().

A table and its migration

1. The schema — Drizzle tables in your package:

// packages/reviews/server/src/lib/schema/reviews.ts
import { index, pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core';

export const reviews = pgTable(
    'reviews',
    {
        id: uuid('id').primaryKey().defaultRandom(),
        workspaceId: uuid('workspace_id').notNull(), // plain uuid, no FK
        entryId: uuid('entry_id').notNull(),
        authorId: uuid('author_id'),
        body: text('body').notNull(),
        resolvedAt: timestamp('resolved_at', { withTimezone: true }),
        createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow()
    },
    (table) => [index('reviews_workspace_entry_idx').on(table.workspaceId, table.entryId)]
);

// packages/reviews/server/src/lib/schema/index.ts
export { reviews } from './reviews';

2. A drizzle config at the package root:

// packages/reviews/server/drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    dialect: 'postgresql',
    schema: './src/lib/schema/index.ts',
    out: './migrations'
});

3. Generate the SQL, and commit it:

Ortha monorepo Generated app

# Inferred on any project with a drizzle.config.ts. Needs no database.
npx nx run @acme/reviews-server:db:generate --name=create_reviews

# No Nx here, and `orthacms generate` covers only the app's content tables
npx drizzle-kit generate --config=packages/reviews/server/drizzle.config.ts --name=create_reviews

-- packages/reviews/server/migrations/0000_create_reviews.sql
CREATE TABLE "reviews" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid NOT NULL,
	"entry_id" uuid NOT NULL,
	"author_id" uuid,
	"body" text NOT NULL,
	"resolved_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE INDEX "reviews_workspace_entry_idx" ON "reviews" USING btree ("workspace_id","entry_id");

4. Point the plugin at it — the migrations descriptor is what the host reads:

migrations: {
    dir: () => join(__dirname, '../../migrations'),
    table: '__drizzle_migrations_reviews'
}

5. Apply — every plugin’s pending migrations, in plugin-list order:

Ortha monorepo Generated app

npx nx run server:db:migrate

npm run migrate   # orthacms migrate

…
Applying migrations: reviews → __drizzle_migrations_reviews

Each plugin tracks its history in its own table, so plugins version independently. See also migrations.

Transactions

import { Injectable } from '@nestjs/common';
import { UnitOfWork } from '@orthacms/database';
import { eq } from 'drizzle-orm';
import { reviews } from './schema';

@Injectable()
export class ReviewsService {
    constructor(private readonly uow: UnitOfWork) {}

    moveReviews(fromEntryId: string, toEntryId: string) {
        return this.uow.run(async () => {
            const db = this.uow.current(); // the ambient transaction
            return db
                .update(reviews)
                .set({ entryId: toEntryId })
                .where(eq(reviews.entryId, fromEntryId))
                .returning();
        });
    }
}

run(fn) executes fn in one transaction.
A nested run joins the outer one — a use case calling another still commits once.
current() returns the ambient transaction inside run, and the pool outside it. No tx parameter to thread through.
After the outermost commit, the outbox is drained — see domain events.

Pool settings

DatabasePlugin({
    connectionString: config.database.url,
    poolMax: 10,                    // default 10
    connectionTimeoutMillis: 10_000, // default 10 s wait for a connection, then fail
    statementTimeoutMillis: 30_000,  // optional; unset means no statement timeout
    outboxRetentionDays: 30          // OUTBOX_RETENTION_DAYS; 0 keeps delivered rows
});

The rules that matter

Lock a contended invariant — never count then write

Two requests both read “three of five used” and both insert. Take a row lock, or express the invariant as a constraint and handle the violation. The codebase’s review checklist names this one explicitly.

Cross-plugin foreign keys

Default: a plain uuid (workspace_id, user_id) with the relationship enforced in your service. Your plugin stays removable.
Cost: integrity for that column is your code’s job, not Postgres’s.
If you do take the FK (as memberships, saved views and copilot conversations do), declare a reference-only projection of the other table in an external-refs.ts your schema barrel does not re-export — and register your plugin after the owner, or a fresh migrate fails.

Not here

One database, one dialect, no cache

One connection string, one pool, PostgreSQL only. No read replicas, no sharding, no database-per-workspace. Nothing caches reads across requests — no query cache, no Redis. Caching belongs in front of the API or inside your plugin, and is yours to invalidate.