Database

Every agent-native app stores its state in PostgreSQL. The UI and the agent both read and write the same PostgreSQL tables through the same actions, using the same Drizzle ORM client. Live sync uses SSE with polling fallback, so changes appear in the other surface without a manual refresh.

How data flows to the database

Browser / UI

Agent loop

Actions defineAction getDb() · Drizzle ORM

PostgreSQL database
PostgreSQL from DATABASE_URL

The UI and the agent both reach the database through the same Actions layer. Both callers use the same getDb() client and the same PostgreSQL tables. There is no separate backend for each.

Hosting Options

Agent-Native uses PostgreSQL. Use local PGlite for development without a separate server, and a persistent hosted PostgreSQL database for shared or deployed environments.

Local Postgres: PGlite

To develop locally without Docker or a hosted database, install the optional PGlite package and set DATABASE_URL:

pnpm add @electric-sql/pglite@^0.5.8

DATABASE_URL=pglite:./data/pglite

PGlite runs an in-process WASM PostgreSQL database. It is local-only storage and must not be used for production or shared environments.

Production database

Set DATABASE_URL in your .env file or deploy-provider environment to connect a hosted PostgreSQL database. Common managed options include Neon, Supabase, Railway, Render, Amazon RDS for PostgreSQL, Cloud SQL for PostgreSQL, and Azure Database for PostgreSQL. Use the Plain Postgres guide for other managed services and self-hosted PostgreSQL servers:

# Neon Postgres
DATABASE_URL=postgres://user:pass@ep-cool-name-123456.us-east-2.aws.neon.tech/mydb?sslmode=require

# Supabase Postgres
DATABASE_URL=postgres://postgres.xxxx:pass@aws-0-us-east-1.pooler.supabase.com:6543/postgres

# Self-hosted Postgres
DATABASE_URL=postgres://user:pass@localhost:5432/mydb

Use a PostgreSQL URL for every hosted environment. The framework configures Drizzle for PostgreSQL from DATABASE_URL.

The framework bounds connections per database pool. Defaults are 20 for long-lived Node.js processes, 2 for serverless foreground functions, 4 for serverless background functions, and 1 for low-connection scheduled workers. Set AGENT_NATIVE_DB_POOL_MAX to override the per-pool maximum for both Neon and standard PostgreSQL clients:

AGENT_NATIVE_DB_POOL_MAX=4

Estimate the aggregate connection budget across all warm function instances or Node.js processes, apps, and distinct database URLs. Each process or function instance owns its own pools. Keep the sum below the database or pooler's connection limit, leaving capacity for migrations, administration, and other clients. Use a provider's pooled endpoint when available. For self-hosted PostgreSQL with many app processes, consider transaction pooling with PgBouncer.

Builder.io managed database

Planned (not yet available): when connected to Builder.io, your app will be able to use a managed PostgreSQL database provisioned automatically, with no PostgreSQL connection string required.

Setting Up the Database

An app that uses the database needs three files:

server/db/schema.ts: table definitions
server/db/index.ts: the typed DB client singleton
server/plugins/db.ts: app-owned migrations for local startup

1. Define your schema

Define schemas with Drizzle's PostgreSQL exports directly. Use framework-owned sharing helpers from @agent-native/core/db/schema only when a table needs them.

server/db/schema.ts
import { sql } from "drizzle-orm";
import { boolean, integer, pgTable, text } from "drizzle-orm/pg-core";

export const tasks = pgTable("tasks", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  priority: integer("priority").notNull().default(0),
  done: boolean("done").notNull().default(false),
  ownerEmail: text("owner_email").notNull(),
  createdAt: text("created_at")
    .notNull()
    .default(sql`now()`),
});

Export	Purpose
`pgTable`	Define a PostgreSQL table
`text`	Text column, supports `{ enum: [...] }`
`integer`	Integer column
`boolean`	Boolean column
`sql`	SQL expression such as `now()`

The tasks table

Domain table. Add owner_email (or ...ownableColumns()) so SQL-level scoping can filter rows to the authenticated user.

id	text	PK
title	text
priority	integer	default 0
done	boolean	default false
owner_email	text	enables data scoping
created_at	text	default now()

Defined once with Drizzle's PostgreSQL schema.

Tables that store per-user data must include an owner_email column so the framework can filter rows to the authenticated user. Tables that also support sharing with other users or orgs should spread ...ownableColumns() instead, which adds owner_email, org_id, and visibility in one call. See Scoping Data to Users below.

2. Create the DB client

Each app creates a lazy, singleton Drizzle client by calling createGetDb(schema). The canonical location is server/db/index.ts:

server/db/index.ts
import { createGetDb } from "@agent-native/core/db";
import * as schema from "./schema.js";

export const getDb = createGetDb(schema);

createGetDb returns a getDb() function that opens the PostgreSQL connection on first call and returns the same typed Drizzle instance on subsequent calls.

Import getDb from this template-local path in actions and routes. Do not import from @agent-native/core directly. The core export is untyped; the local export carries your schema types.

3. Write migrations

For local development, schema changes can run through a Nitro plugin in server/plugins/db.ts. Use runMigrations from @agent-native/core/db:

A startup migration plugin

server/plugins/db.ts

1import { runMigrations } from "@agent-native/core/db";

3export default runMigrations(

4 [

5 {

6 version: 1,

7 sql: `CREATE TABLE IF NOT EXISTS tasks (

8 id TEXT PRIMARY KEY,

9 title TEXT NOT NULL,

10 done BOOLEAN NOT NULL DEFAULT false,

11 owner_email TEXT NOT NULL,

12 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

13 )`,

14 },

15 {

16 version: 2,

17 sql: `ALTER TABLE tasks ADD COLUMN IF NOT EXISTS priority INTEGER NOT NULL DEFAULT 0`,

18 },

19 {

20 version: 3,

21 sql: `ALTER TABLE tasks ADD COLUMN IF NOT EXISTS tsv tsvector`,

22 },

23 ],

24 { table: "my_app_migrations" },

25);

Line 6Per-app version table

Each app tracks its own applied versions in a separate table. Use a name unique to your app so it doesn't collide with framework migrations or other apps sharing the same database.

Line 7Initial table creation

CREATE TABLE IF NOT EXISTS is safe to re-run on every restart. Put the full table definition in version 1.

Line 17Additive column adds

ADD COLUMN IF NOT EXISTS is the safe way to add columns after initial creation. Never drop or rename columns.

Line 22PostgreSQL SQL

Migrations use PostgreSQL SQL directly and are applied in version order.

runMigrations runs each pending entry in order once, records it as applied, and skips it on later runs. Give new entries a stable name; named migrations are tracked independently of their version number.

Generate new app migrations with Drizzle Kit

For new app-owned schema changes, use Drizzle Kit to generate reviewed SQL:

pnpm db:generate

This creates server/db/migrations/<id_name>.sql. Commit the generated file and review its SQL before shipping it. runDrizzleMigrations loads those files into the shared Agent-Native runner on Node.js and Node-based serverless runtimes, so release authorization, database handling, and bookkeeping stay in one place:

server/db/migrations.ts
import { runDrizzleMigrations } from "@agent-native/core/db/drizzle-migrations";

export default runDrizzleMigrations(new URL("./migrations", import.meta.url), {
  table: "my_app_migrations",
});

Re-export that plugin from server/plugins/db.ts so the same ./migrations path works from source and from the emitted server bundle:

server/plugins/db.ts
export { default } from "../db/migrations.js";

The filesystem-backed loader is not available in filesystem-free edge runtimes. Keep migration files owned by the release step and use a hosted PostgreSQL connection.

If an app already has handwritten entries, keep that history and load generated entries into the same runMigrations() source after it. Do not give one schema two independent migration owners. Generate against PostgreSQL and keep using handwritten runMigrations() entries for framework tables, data backfills, and deferred JavaScript steps.

For production or serverless deployments, run migrations in a release or deploy step instead of on the first request. The Chat template provides pnpm migrate:production for this.

Give every new migration a stable name (e.g. { version: 4, name: "tasks-priority-index", sql: "..." }). The plain version number is only a position in a shared sequence: if two branches each add their own migrations at, say, version: 4, whichever branch deploys first "uses up" that version number in the bookkeeping table, and the other branch's DDL silently never runs when it merges — the table looks fully migrated even though the second branch's columns or tables don't exist. A name is tracked independently of version, so it applies exactly once regardless of what version number it shipped under or which branch merged first. Unnamed legacy migrations keep working as-is; add name to migrations you write from now on.

Never run drizzle-kit push against a production database. Template schemas only define app-specific tables; they do not include central framework tables (user, session, application_state, and others). Running drizzle-kit push against production will detect those tables as unknown and attempt to drop them, causing immediate data loss.

drizzle.config.ts at the root of each app configures drizzle-kit for local development schema inspection and migration generation:

drizzle.config.ts
import { createDrizzleConfig } from "@agent-native/core/db/drizzle-config";
export default createDrizzleConfig();

Use pnpm db:generate to create reviewed migration files and pnpm db:push against a local database only. Production schema changes go through the release migration command.

4. Query in actions

Call getDb() from your actions to get the typed Drizzle client. Use Drizzle's query builder and operators from drizzle-orm:

import { and, desc, eq } from "drizzle-orm";
import { getDb } from "../server/db/index.js";
import * as schema from "../server/db/schema.js";

const db = getDb();

const openTasks = await db
  .select()
  .from(schema.tasks)
  .where(
    and(eq(schema.tasks.ownerEmail, userEmail), eq(schema.tasks.done, false)),
  )
  .orderBy(desc(schema.tasks.createdAt));

await db
  .update(schema.tasks)
  .set({ done: true })
  .where(eq(schema.tasks.id, taskId));

Scoping Data to Users

All reads and writes against user-facing tables must be scoped to the authenticated user. The framework provides two patterns depending on whether the data is private or shareable.

Private data: tables that belong to one user. Add ownerEmail: text("owner_email").notNull() to the schema and include eq(table.ownerEmail, userEmail) in every query:

.where(eq(schema.tasks.ownerEmail, userEmail))

Shared resources: tables that can be shared with other users or organizations. Spread ...ownableColumns() in the schema instead of a bare owner_email. This adds owner_email, org_id, and visibility in one call, and creates a companion shares table with createSharesTable:

server/db/schema.ts
import {
  table,
  text,
  ownableColumns,
  createSharesTable,
} from "@agent-native/core/db/schema";

export const decks = table("decks", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  ...ownableColumns(),
});
export const deckShares = createSharesTable("deck_shares");

Then use accessFilter from @agent-native/core/sharing in list queries instead of a manual eq check:

import { accessFilter } from "@agent-native/core/sharing";

const rows = await db
  .select()
  .from(schema.decks)
  .where(accessFilter(schema.decks, schema.deckShares));

accessFilter builds a query that admits rows the caller owns, rows shared with their org, and rows explicitly shared with them. It does not expose rows from other users.

See Security — Data Scoping and Sharing for the full model.

PostgreSQL-Backed Sync

Agent-Native does not rely on filesystem watchers or sticky in-memory state. When an action writes to the database, a sync version increments. The client useDbSync() hook polls /_agent-native/poll and invalidates React Query caches when it sees a higher version.

This works across serverless and multi-instance deployments because the database is the coordination point. If you write custom mutations outside actions, use framework helpers or emit the appropriate sync invalidation so open UIs refresh.

PostgreSQL-backed sync loop

Action / helper
mutates data

PostgreSQL databasesync version increments

useDbSync()
polls /_agent-native/poll

invalidate caches → UI refreshes

No watchers, no sticky state. A write bumps a version in PostgreSQL; every client polls the version and refetches.

The poll endpoint

Raw SQL

For advanced queries, health checks, or one-off maintenance that the Drizzle query builder can't express, use getDbExec from @agent-native/core/db:

import { getDbExec } from "@agent-native/core/db";

const { rows } = await getDbExec().execute({
  sql: `SELECT id, title FROM tasks WHERE owner_email = ? LIMIT ?`,
  args: [userEmail, 50],
});

getDbExec auto-converts ? params to $1, $2, etc. for PostgreSQL. Prefer the Drizzle query builder for normal reads and writes. Raw SQL bypasses type safety and is harder to maintain.

Environment Variables

Variable	Purpose
`DATABASE_URL`	Local `pglite:./data/pglite` URL or hosted PostgreSQL connection string

What's next

Security — Data Scoping: how owner_email and access helpers scope reads and writes
Sharing: ownableColumns() and the visibility model for shared resources
Plugins: the startup plugin lifecycle where migrations run
Actions: the surface where actions call getDb() to read and write data
Deployment: connecting a persistent PostgreSQL database per deploy target
Real-Time Sync: useDbSync() and the full client-side sync model