---
title: 'stackbone.database'
description: "Type-safe Postgres for your agent: declare the schema, generate a migration, query with Drizzle's full surface through the ambient handle."
position: 3
---

# 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](https://orm.drizzle.team/) 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](/docs/sdk/agents/overview) and a
[durable workflow step](/docs/sdk/workflows/overview) 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`:

```ts
// src/schema.ts
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:

```sh
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:

```sh
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](#agentyaml-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](/docs/sdk/agents/overview), where the model can
call it during a turn:

```ts
// deep-agents/sales/index.ts
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:

```ts
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](/docs/sdk/workflows/overview) step. A function marked
`'use step'` is executed by the
[Workflow SDK](https://workflow-sdk.dev/docs): it runs **once**,
persists its result, and auto-retries on failure
([step directive semantics](https://workflow-sdk.dev/docs/how-it-works/understanding-directives)).
Because a step can re-run after a crash, keep your writes **idempotent**:
upsert, or guard on a unique key.

```ts
// workflows/onboarding.workflow.ts
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()`](/docs/sdk/humans/approval) 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.

```yaml
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](/docs/sdk/workflows/overview)). 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](/docs/cli/reference/db)
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`](/docs/cli/reference/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`:

```ts
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`](/docs/sdk/data/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](/docs/sdk/reference/overview#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`](https://node.testcontainers.org/modules/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:

```sh
pnpm add -D @testcontainers/postgresql
```

Docker has to be running for the container to start.

```ts
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:

```ts
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:

```ts
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](/docs/sdk/reference/overview)**: the ambient
  `stackbone` handle and the rest of the modules: storage, AI, RAG,
  approval, secrets, config.
- **[Durable workflows](/docs/sdk/workflows/overview)**: persist data from
  a `'use step'` that survives crashes and retries.
- **[`stackbone.approval`](/docs/sdk/humans/approval)**: gate a database write
  behind a human decision with `requestApproval()`.
- **[Local development](/docs/cli/guides/local-development)**: how
  `stackbone dev` boots Postgres locally and injects the env vars
  `stackbone.database` reads.