Database

PostgreSQL database management commands and schema workflow

Overview

ChatJS uses PostgreSQL with Drizzle ORM for type-safe, schema-first database access. The schema is defined in apps/chat/lib/db/schema.ts. Edit it to change the database structure.

The CLI installs the same Drizzle and Postgres.js code for every Postgres host. You do not select a database host during installation. Create your database with your host, then put its connection strings in .env.local at your generated app’s root (or apps/chat when working in this repository).

Host	Where to get the connection	Settings
Neon	Project dashboard → Connect, then select your branch, role, and database	Use the pooled URL for runtime if needed and the direct URL for migrations
Supabase	Project Connect panel	Use the connection mode appropriate to your deployment. Transaction pooling requires `DATABASE_PREPARE=false`. Use a direct or suitable session connection for migrations
Local or another Postgres host	Your database administrator or host’s Postgres connection details	Set `DATABASE_URL`. Leave the migration URL and pooling overrides unset unless needed

Use Postgres credentials, not a host’s HTTP API URL or API key. Both connection URLs must target the same database and branch. The URLs can use different endpoints and roles, such as a limited runtime role and a migration role.

After filling in .env.local, run:

bun db:connect

This checks both configured connections with SELECT 1, using the runtime prepared-statement setting for the runtime check. Each endpoint has a 15-second deadline. The command closes connections, reports the failing configuration name without printing credentials, and exits unsuccessfully if either check fails. It does not apply migrations or change your schema. A successful check confirms connectivity, not migration permissions, schema readiness, or that both URLs point to the same database. Verify those details before applying migrations.

Connection settings

Set DATABASE_URL to the Postgres connection string from your host. The same schema and query implementation works with standard Postgres connections. No Neon-specific driver is required.

Variable	Purpose
`DATABASE_URL`	Runtime queries
`DATABASE_MIGRATION_URL`	Optional direct connection for migrations and Drizzle schema operations, falling back to `DATABASE_URL`
`DATABASE_PREPARE`	`true` by default. Set `false` for transaction poolers that do not support prepared statements
`DATABASE_MAX_CONNECTIONS`	Optional positive integer limiting runtime connections per process

DATABASE_URL=postgres://user:password@runtime-host/app?sslmode=verify-full
DATABASE_MIGRATION_URL=postgres://user:password@direct-host/app?sslmode=verify-full
DATABASE_PREPARE=false
DATABASE_MAX_CONNECTIONS=5

Use your host’s connection strings and TLS settings. Do not disable certificate verification to make a connection succeed. For remote connections, require verified TLS (for example, sslmode=verify-full) and configure your host’s CA trust when needed. Postgres.js does not enable TLS for a URL without TLS settings. db:connect uses those same settings and does not enforce a separate TLS policy. Local or private-network connections retain the settings you explicitly supply. Keep passwords in your environment.

For Supabase transaction pooling, disable prepared statements. Use a direct or suitable session connection for schema operations. Neon also recommends a direct connection for migration tools. A local Postgres instance or another host can use one URL for both purposes.

Database Management

Run these commands from the apps/chat directory:

# Push schema changes directly to the database (dev only, no migration file)
bun db:push

# Open Drizzle Studio at https://local.drizzle.studio
bun db:studio

# Generate a migration file from schema changes
bun db:generate

# Run pending migration files against the database
bun db:migrate

db:push: fastest for local iteration, skips migration files. Not safe for production.
db:generate + db:migrate: the production workflow, creates a versioned SQL file and applies it.
db:studio: inspect, query, and edit data via a browser UI.

Schema Changes

All schema changes start in apps/chat/lib/db/schema.ts. After editing:

Run bun db:generate to produce a migration file in lib/db/migrations/.
Review the generated SQL to confirm the diff is correct.
Run bun db:migrate to apply it.

Test schema changes against an isolated database before applying them to production. If you use Neon, Neon Branching is an optional workflow.

Deployment and migrations

bun db:migrate explicitly applies pending migrations on any host. It does not require VERCEL_ENV. The migration connection uses one connection and closes it on success or failure.

The app build runs migrations with --deployment, preserving the existing production-only Vercel behavior. Preview and local builds skip automatic migrations. On other hosts, run bun db:migrate as an explicit deployment step before starting the updated app.

Optional Neon development

Normal bun dev and bun db:migrate use your configured URLs. They do not read .neon-branch. To use the branch selected by the Neon helper scripts, run bun dev:neon or bun db:migrate:neon. The wrapper overrides both runtime and migration URLs so a main-database migration URL cannot leak into a branch session.