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. No connection string juggling, no second ORM, no 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 — applied with an advisory lock and a journal table so it is safe to run on every boot.
• A .stackbone/migrations/ folder that lives in your project, gets committed alongside your code, and is replayed on every environment 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 exact 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, records each successful migration in a journal table, and exits cleanly if 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 — for both a single-folder project (a root agent.yaml with no agents/ folder) and a multi-agent workspace (each agent under its own deep-agents/<name>/ folder). 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 reach for stackbone.database and use 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() }),
  },
);

Row types flow through end to end. 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 underlying connection was suspended 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 headline 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 };
}

A write can be gated behind a human decision: open a requestApproval() gate first, then only run the stackbone.database write once the decision comes back approved. The gate is durable too — the workflow can wait minutes or months for the answer and resume exactly 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:

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

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

• database.schema — 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 — 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, applies it and restarts the agent; 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.

Both path values are kept verbatim — the CLI resolves them against the project root at command execution time, so they "just work" regardless of which subdirectory you ran stackbone from.

A multi-agent workspace keeps each agent under its own deep-agents/<name>/ folder, discovered by convention — no central registry is required. An optional stackbone.config.ts exporting defineWorkspace can override that discovery when you need to (see workflows). Either way the schema and migrations layout still lives per agent and 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 pointed at by STACKBONE_POSTGRES_URL. 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.

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. No manual extension management needed.

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 underlying Postgres pool is built the first time you touch it, not when the handle is imported. This is deliberate so that env vars rotated by the platform are still picked up. 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), a stale pool is detected on the next call and the SDK transparently rebuilds it. From your code's point of view, the query just 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 the version floor is missed), 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 are intentionally ungated because they are synchronous accessors you are opting into:

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

Use them when you genuinely 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:

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 fully self-contained. No docker compose up ceremony, no shared dev database, no env wiring.

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);

The SDK is a thin convenience layer, not a wall. Drop down to upstream when it pays off, then come back when it doesn't.

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.