# 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](https://orm.drizzle.team) 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 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. ```html
Browser / UI
Agent loop
Actions defineAction getDb() · Drizzle ORM
PostgreSQL database
PostgreSQL from DATABASE_URL
``` ```css .diagram-dbr { display: flex; align-items: center; gap: 14px; flex-wrap: wrap; } .diagram-dbr .diagram-col { display: flex; flex-direction: column; gap: 10px; } .diagram-dbr .diagram-panel { display: flex; flex-direction: column; gap: 4px; padding: 12px 16px; } .diagram-dbr .diagram-arrow { font-size: 22px; line-height: 1; } ``` ## Hosting Options {#hosting} 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 {#local-pglite} To develop locally without Docker or a hosted database, install the optional PGlite package and set `DATABASE_URL`: ```bash pnpm add @electric-sql/pglite@^0.5.8 ``` ```bash DATABASE_URL=pglite:./data/pglite ``` [PGlite](https://pglite.dev/) runs an in-process WASM PostgreSQL database. It is local-only storage and must not be used for production or shared environments. ### Production database {#production} Set `DATABASE_URL` in your `.env` file or deploy-provider environment to connect a hosted PostgreSQL database. Common managed options include [Neon](/docs/neon/), [Supabase](/docs/supabase/), Railway, Render, [Amazon RDS for PostgreSQL](/docs/aws-rds/), [Cloud SQL for PostgreSQL](/docs/cloud-sql/), and [Azure Database for PostgreSQL](/docs/azure-postgresql/). Use the [Plain Postgres](/docs/postgres/) guide for other managed services and self-hosted PostgreSQL servers: ```bash # 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: ```bash 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 {#builder-managed} _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 {#setup} 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 {#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. ```ts filename="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 Defined once with Drizzle's PostgreSQL schema. #### tasks Domain table. Add owner_email (or ...ownableColumns()) so SQL-level scoping can filter rows to the authenticated user. | Field | Type | Flags | Notes | | --- | --- | --- | --- | | id | text | pk | | | title | text | | | | priority | integer | | default 0 | | done | boolean | | default false | | owner_email | text | | enables data scoping | | created_at | text | | default now() | 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](#scoping) below. ### 2. Create the DB client {#db-client} Each app creates a lazy, singleton Drizzle client by calling `createGetDb(schema)`. The canonical location is `server/db/index.ts`: ```ts filename="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 {#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 ```ts import { runMigrations } from "@agent-native/core/db"; export default runMigrations( [ { version: 1, sql: `CREATE TABLE IF NOT EXISTS tasks ( id TEXT PRIMARY KEY, title TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT false, owner_email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP )`, }, { version: 2, sql: `ALTER TABLE tasks ADD COLUMN IF NOT EXISTS priority INTEGER NOT NULL DEFAULT 0`, }, { version: 3, sql: `ALTER TABLE tasks ADD COLUMN IF NOT EXISTS tsv tsvector`, }, ], { table: "my_app_migrations" }, ); ``` Annotations: - Lines 6: Per-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. - Lines 7: Initial table creation - `CREATE TABLE IF NOT EXISTS` is safe to re-run on every restart. Put the full table definition in version 1. - Lines 17: Additive column adds - `ADD COLUMN IF NOT EXISTS` is the safe way to add columns after initial creation. Never drop or rename columns. - Lines 22: PostgreSQL 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 {#drizzle-migrations} For new app-owned schema changes, use Drizzle Kit to generate reviewed SQL: ```bash pnpm db:generate ``` This creates `server/db/migrations/.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: ```ts filename="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: ```ts filename="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. ### Callout 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. ### Callout 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. ### Callout 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: ```ts filename="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 {#querying} Call `getDb()` from your actions to get the typed Drizzle client. Use Drizzle's query builder and operators from `drizzle-orm`: ```ts 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 {#scoping} 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: ```ts .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`: ```ts filename="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: ```ts 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](/docs/security/#data-scoping) and [Sharing](/docs/sharing/#building) for the full model. ## PostgreSQL-Backed Sync {#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 No watchers, no sticky state. A write bumps a version in PostgreSQL; every client polls the version and refetches. ```html
Action / helper
mutates data
PostgreSQL databasesync version increments
useDbSync()
polls /_agent-native/poll
invalidate caches → UI refreshes
``` ```css .diagram-db-sync { display: flex; align-items: center; gap: 14px; flex-wrap: wrap; } .diagram-db-sync .diagram-col { display: flex; flex-direction: column; gap: 8px; align-items: flex-start; } .diagram-db-sync .diagram-arrow { font-size: 22px; line-height: 1; } ``` ### The poll endpoint Return the current per-source database sync versions so the client can detect changes. #### GET /_agent-native/poll Return the current per-source database sync versions so the client can detect changes. `useDbSync()` calls this on an interval and falls back to it when SSE is unavailable. When a returned version is higher than the client's last-seen value, the matching React Query caches are invalidated and refetch. Auth: Session cookie (request-scoped identity) Responses: - 200: Current sync versions keyed by source. ## Raw SQL {#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`: ```ts 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 {#environment-variables} | Variable | Purpose | | -------------- | ----------------------------------------------------------------------- | | `DATABASE_URL` | Local `pglite:./data/pglite` URL or hosted PostgreSQL connection string | ## What's next {#whats-next} - [**Security — Data Scoping**](/docs/security/#data-scoping): how `owner_email` and access helpers scope reads and writes - [**Sharing**](/docs/sharing/#building): `ownableColumns()` and the visibility model for shared resources - [**Plugins**](/docs/server-plugins/): the startup plugin lifecycle where migrations run - [**Actions**](/docs/actions-overview/): the surface where actions call `getDb()` to read and write data - [**Deployment**](/docs/deployment/#persistent-database): connecting a persistent PostgreSQL database per deploy target - [**Real-Time Sync**](/docs/key-concepts/#polling-sync): `useDbSync()` and the full client-side sync model