stackbone.database

Type-safe Postgres for your agent. You declare a schema in TypeScript, the CLI emits the SQL migration, and the ambient stackbone.database handle gives you Drizzle bound to the agent's connection string. You never handle a connection string, and there is no second ORM or codegen step.

What you get

• A lazy Drizzle ORM handle on stackbone.database: the ambient handle, imported straight from @stackbone/sdk and bound to STACKBONE_POSTGRES_URL.
• The Drizzle column builders and helpers re-exported through @stackbone/sdk/db, so your project's package.json only depends on @stackbone/sdk.
• A migration engine driven by the stackbone db migrate ... commands: it applies files under an advisory lock and records them in a journal table, so it is safe to run on every boot.
• A .stackbone/migrations/ folder that lives in your project. You commit it alongside your code, and every environment replays it in declared order.

The same stackbone.database handle is what an agent tool and a durable workflow step both use to reach Postgres. The runtime injects STACKBONE_POSTGRES_URL per install in every environment, so the same code runs unchanged in local stackbone dev, self-host, and cloud.

Tutorial: from zero to a queryable table

This walkthrough assumes you have a workspace scaffolded with stackbone init. A fresh workspace ships with the schema and migrations wiring already in place; you can follow along on a new workspace or in your own.

Step 1: declare the schema

Open src/schema.ts and describe your tables with pgTable and the column helpers. Always import from @stackbone/sdk/db:

import { integer, pgTable, serial, text, timestamp } from '@stackbone/sdk/db';

export const leads = pgTable('leads', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
  status: text('status').notNull().default('new'),
  score: integer('score').notNull().default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Anything Drizzle exports (column types, helpers like eq, and, sql, relations, plus the inference types InferSelectModel / InferInsertModel) is reachable through @stackbone/sdk/db. Never import from drizzle-orm directly: the SDK barrel is the only contract the platform supports.

Step 2: generate the migration

Once the schema is the way you want it, ask the CLI to compare it against the current state of your migrations folder and emit the next SQL file:

stackbone db migrate create add_leads

That writes a new file under .stackbone/migrations/ with a numeric prefix (e.g. 0001_add_leads.sql) plus the matching journal entry under meta/. Commit both: they are part of your agent's source code.

Step 3: apply the migration

Apply pending migrations to the database your agent points at:

stackbone db migrate up

The command takes an advisory lock, so two boots cannot apply the same file twice. It records each successful migration in a journal table and exits cleanly when there is nothing to apply. Running it again is a no-op.

stackbone dev applies pending migrations against the agent's Postgres before bringing the runtime up. It runs on a workspace: a project that holds at least one deep-agents/<name>/ folder or one workflows/ module, or that declares a stackbone.config.ts at its root. A flat folder with only a root agent.yaml is not a workspace, and the command stops with "This directory is not a Stackbone workspace." rather than booting. The dev.autoMigrate flag only controls whether editing src/schema.ts mid-session auto-generates the next migration for you (see the agent.yaml configuration section).

Step 4: query with Drizzle

Inside your agent code you use stackbone.database with the familiar Drizzle surface. Here it is inside an agent tool, where the model can call it during a turn:

import { tool } from '@langchain/core/tools';
import { z } from 'zod';
import { stackbone } from '@stackbone/sdk';
import { eq } from '@stackbone/sdk/db';
import { leads } from '../../src/schema';

const qualifyLeads = tool(
  async ({ markContactedId }: { markContactedId?: number }) => {
    // Read.
    const qualified = await stackbone.database
      .select()
      .from(leads)
      .where(eq(leads.status, 'qualified'))
      .limit(20);

    // Write.
    await stackbone.database
      .insert(leads)
      .values({ email: 'jane@example.com', status: 'new', score: 0 });

    // Update.
    if (markContactedId !== undefined) {
      await stackbone.database
        .update(leads)
        .set({ status: 'contacted' })
        .where(eq(leads.id, markContactedId));
    }

    // Delete.
    await stackbone.database.delete(leads).where(eq(leads.status, 'spam'));

    return JSON.stringify({ qualified });
  },
  {
    name: 'qualify_leads',
    description: 'List qualified leads and mark one as contacted.',
    schema: z.object({ markContactedId: z.number().optional() }),
  },
);

The rows returned by select() are typed from your pgTable definition with no codegen step. stackbone.database is native Drizzle: awaiting a chain returns the rows directly and throws on error.

Step 5: wrap a unit of work in a transaction

Interactive transactions yield a tx with the same surface as stackbone.database, reached through the ambient stackbone handle from any tool or workflow step:

import { stackbone } from '@stackbone/sdk';
import { sql } from '@stackbone/sdk/db';
import { leads } from './schema';

await stackbone.database.transaction(async (tx) => {
  await tx.insert(leads).values({ email: 'a@example.com', status: 'new' });
  await tx.execute(sql`UPDATE leads SET score = score + 1 WHERE email = ${'a@example.com'}`);
});

If the callback throws, the transaction rolls back. If the host suspended the underlying connection between calls (cold-start on serverless Postgres), the SDK rebuilds the pool and replays the transaction once before surfacing the error.

Writing from a durable workflow

The common new-runtime pattern is persisting data from inside a durable workflow step. A function marked 'use step' is executed by the Workflow SDK: it runs once, persists its result, and auto-retries on failure (step directive semantics). Because a step can re-run after a crash, keep your writes idempotent: upsert, or guard on a unique key.

import { stackbone } from '@stackbone/sdk';
import { leads } from '../src/schema';

async function persistLead(input: { email: string; score: number }) {
  'use step'; // runs once, persisted, retried on failure. Keep it idempotent
  await stackbone.database
    .insert(leads)
    .values({ email: input.email, status: 'new', score: input.score })
    .onConflictDoUpdate({
      target: leads.email,
      set: { score: input.score },
    });
  return { email: input.email };
}

You can gate a write behind a human decision: open a requestApproval() gate first, then run the stackbone.database write only when the decision comes back approved. The gate is durable too: the workflow can wait minutes or months for the answer and resume where it paused.

agent.yaml configuration

The database block on agent.yaml controls where the CLI looks for your schema and migrations. All keys are optional and ship with sane defaults. agent.yaml itself is optional for this flow: a project without one gets the same defaults.

database:
  schema: ./src/schema.ts # default
  migrations: ./.stackbone/migrations # default

dev:
  autoMigrate: true # default; set false to generate migrations yourself

• database.schema is the TypeScript file the migration engine introspects when generating SQL. Override when your project layout differs (db/schema/index.ts, src/db/schema.ts, …).
• database.migrations is the folder where migration files land. Keep it inside the project and committed.
• dev.autoMigrate controls schema-to-migration automation while stackbone dev runs. With the default true, editing src/schema.ts generates a migration from the diff and applies it. Nothing restarts: your agents read the migrated schema live on their next query. With false you get a hint to run stackbone db migrate create instead. Either way stackbone dev always applies pending migrations before boot and applies any .sql you add to the migrations folder.

The CLI keeps both path values verbatim and resolves them against the project root at command execution time. They resolve the same way from any subdirectory you run stackbone in.

A multi-agent workspace keeps each agent under its own deep-agents/<name>/ folder, discovered by convention, so there is no central registry to maintain. An optional stackbone.config.ts exporting defineWorkspace can override that discovery when you need to (see workflows). The schema and migrations stay at the workspace root either way: every agent and workflow in the workspace shares the one install database, so they share one src/schema.ts and one migrations folder. The same database / dev keys apply.

CLI commands

Every database operation goes through the stackbone db subcommand tree. Each command exits non-zero on failure and supports --json for machine-readable output.

stackbone db migrate create <name>

Compares your schema against the current migrations folder and emits the next SQL file under database.migrations. Run it after every schema edit. The <name> becomes part of the filename (e.g. 0003_add_leads.sql).

stackbone db migrate up

Applies pending migrations to the database STACKBONE_POSTGRES_URL points at. When that variable is unset and stackbone dev is running in this project, the command targets the dev database that session recorded. Uses an advisory lock and a journal table, so concurrent boots cannot double-apply a migration. Idempotent: a second run with no pending files is a no-op.

stackbone db migrate status

Reports which migrations are applied, which are pending, and whether the database has drifted from the journal (e.g. an entry exists in the database but not in the folder). Informational only: never mutates state.

Reading the data: db schemas, db table, db query

Alongside migrations, stackbone db carries a read-only Explorer over a running installation: db schemas lists the schemas and tables, db table <schema> <name> pages through one table's rows, and db query "<sql>" runs a single read statement. The Explorer refuses writes without attempting them. See Agent-runtime CLI commands for the flags and the --agent targeting.

Migrations in production

You do not run a migration command against production. Your migrations travel with the agent and the container applies them when it starts.

stackbone build copies .stackbone/migrations/ into the workspace bundle at .well-known/migrations/v1, verbatim. The container reads them from there, applies the pending ones, and only then starts serving. The container downloads nothing at boot, so it comes up with your tables in place even without outbound network.

The boot repeats the guarantees stackbone db migrate up gives you locally: an advisory lock (two containers starting at once against one fresh database both come up), a journal row per applied file (a restart applies nothing), and a stop on the first failure. When a file fails, the log names the .sql that broke and what had already gone in. When the database role cannot create objects, the log names the GRANT to run instead of printing a Postgres stack trace.

The container also creates its own internal tables the same way, so you can point it at a completely empty Postgres.

Opting out: if your DBA owns every schema change, set STACKBONE_SKIP_MIGRATIONS=1 on the container and apply the .sql files by hand, in filename order, before the first boot. One flag covers both your tables and Stackbone's internal ones. The container logs each skip, so you can tell "we opted out" from "this container never migrated".

Skipping leaves the journal empty. If you later turn the flag off, the next boot tries to apply every file against objects that already exist, so either keep skipping or have your DBA backfill the journal.

pgvector

If your agent needs vector search, declare the column with the vector helper from @stackbone/sdk/db:

import { pgTable, text, vector } from '@stackbone/sdk/db';

export const documents = pgTable('documents', {
  id: text('id').primaryKey(),
  content: text('content').notNull(),
  embedding: vector('embedding', { dimensions: 1536 }).notNull(),
});

The first migration that introduces a vector(...) column emits the matching CREATE EXTENSION IF NOT EXISTS vector ahead of the CREATE TABLE. You never manage the extension by hand.

For higher-level retrieval (parsing, chunking, embedding, hybrid search), look at stackbone.rag: it builds on the same pgvector storage but adds the RAG ergonomics on top.

Connection lifecycle

stackbone.database is lazy: the SDK builds the underlying Postgres pool the first time you touch the handle, not when you import it. That is deliberate: the handle still sees env vars the platform rotated after import. There is one pool per agent process whether you reach Postgres through the ambient handle or through a tool/step context: they all resolve to the same singleton.

If your agent runs on a serverless host that suspends the container between requests (Fly Machines, SSR), the SDK detects the stale pool on the next call and rebuilds it, so from your code's point of view the query succeeds.

Contract gating

The terminal await of every Drizzle chain (select/insert/update/delete/execute/transaction) consults the contract handshake before dispatching. The required capability is database.postgres_direct; when the negotiated contract does not advertise it (or misses the version floor), the chain throws a tagged Error & { code, message, meta } with code set to capability_unavailable or contract_version_unsupported. The code matches the rest of the SDK so the same try/catch works for setup-bug errors like database_not_configured.

Two surfaces stay ungated by design, because they are synchronous accessors you opt into:

• stackbone.database.query: Drizzle's relational query builder.
• stackbone.database.raw(): the underlying Drizzle handle.

Use them when you need the escape hatch.

Testing: @stackbone/sdk/db/testing

For tests that need a real Postgres without colliding with other tests, the SDK ships a helper that boots an ephemeral Postgres container via @testcontainers/postgresql, applies your migrations against it, and hands back a Drizzle handle plus a dispose() for tear-down.

That container library is an optional peer dependency. The subpath loads it only when you call createTestDatabase, so importing @stackbone/sdk/db/testing works even where the peer is absent. Install it in the projects that start a container. Your deployed agent stays free of it:

pnpm add -D @testcontainers/postgresql

Docker has to be running for the container to start.

import { createTestDatabase } from '@stackbone/sdk/db/testing';
import { resolve } from 'node:path';
import { leads } from '../src/schema';

const testDb = await createTestDatabase({
  migrationsDir: resolve(__dirname, '../.stackbone/migrations'),
});

await testDb.drizzle.insert(leads).values({ email: 'a@example.com', status: 'new' });
const rows = await testDb.drizzle.select().from(leads);

await testDb.dispose();

Each call gets its own container, so parallel tests never see each other's rows and never share server-level state (locks, stats, configuration). dispose() closes the pool and stops the container; it is idempotent.

createTestDatabase returns { connectionString, drizzle, dispose }. The connectionString points at the running container: pass it to any sub-process the test spawns so it talks to the same database as the Drizzle handle.

image option

The container image defaults to pgvector/pgvector:pg17 so RAG and AI starters that need the vector extension work out of the box. Override it when you need a different footprint:

await createTestDatabase({
  migrationsDir: resolve(__dirname, '../.stackbone/migrations'),
  image: 'postgres:17', // vanilla, minimum footprint
});

Use stackbone/postgres:local when the test suite needs the bundled extensions.

Requirements

Docker must be running on the host (docker info must succeed). The helper does not consume STACKBONE_POSTGRES_URL, DATABASE_URL, or any other connection-string env: tests are self-contained. There is no docker compose up step and no shared dev database.

Escape hatch

Everything stackbone.database does is plain Drizzle. The connection string (STACKBONE_POSTGRES_URL) is injected into the container, and nothing stops you from instantiating Drizzle (or postgres-js) directly when you need a feature the SDK does not surface yet:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const sql = postgres(process.env['STACKBONE_POSTGRES_URL']!);
const db = drizzle(sql);

Where to go next

• @stackbone/sdk overview: the ambient stackbone handle and the rest of the modules: storage, AI, RAG, approval, secrets, config.
• Durable workflows: persist data from a 'use step' that survives crashes and retries.
• stackbone.approval: gate a database write behind a human decision with requestApproval().
• Local development: how stackbone dev boots Postgres locally and injects the env vars stackbone.database reads.