Database Setup

All of your site's content - pages, CMS collections, users, settings - lives in your own database. ChaiBuilder ships its own set of tables alongside Payload's, and both are managed through one migration pipeline, so there is a single database and a single upgrade path.

Choosing a database

Option Best for Connection
SQLite (local file) Local development, quick starts file:./local.db
libSQL / Turso Small production sites, serverless-friendly SQLite libsql://… URL + auth token
Postgres Production, teams, multi-instance deployments postgresql://… connection string

The scaffolder (pnpm dlx chaibuilder-app create) asks which one you want and writes the connection into your environment, runs migrations, and seeds the database - so for a new project there is nothing manual to do. The notes below are for changing databases later or tuning production.

A local SQLite file is per-instance. On serverless platforms and multi-container deployments, each instance would get its own copy. Use a remote database (Turso or Postgres) for anything beyond a single long-running server.

Connection string

The database connection is a single environment variable:

# Postgres
DATABASE_URL=postgresql://user:password@host:5432/dbname

# libSQL / Turso
DATABASE_URL=libsql://your-db.turso.io

Point this at your production database in your hosting platform's environment settings. The same variable is read by Payload and by ChaiBuilder's own tables - they share one connection.

Postgres pooling

ChaiBuilder opens one shared connection pool for the whole app (Payload and the builder share it, so you never pay for two pools). Pool sizing adapts to your provider automatically:

  • Direct Postgres - up to 10 connections per instance.
  • PgBouncer / transaction poolers - detected via port 6543 or a ?pgbouncer=true query param; the pool shrinks to 5 connections and shortens idle timeouts.
  • Supabase - pooler detection plus tighter idle timeouts to play well with Supavisor.

On serverless platforms (many short-lived instances), always connect through your provider's pooler endpoint (Neon pooled connection string, Supabase transaction pooler on port 6543) rather than the direct database port, or you will exhaust connections under load.

Postgres schema isolation

To run ChaiBuilder inside a shared database, set an optional schema name and all tables are created there instead of public:

CHAIBUILDER_POSTGRESS_SCHEMA=chaibuilder

The variable name is spelled POSTGRESS, with a double S. The single S spelling is ignored. On Payload hosts the generated migrations follow the adapter's schemaName, so the schema needs no other handling.

Unlimited sites, one database

A single database can host any number of sites. Each site is a row in ChaiBuilder's apps table, and every page, user membership, and asset is scoped to its site - so adding a site is a data operation, not a new database or a new deployment. Which site a request belongs to is resolved from the hostname, or pinned with CHAIBUILDER_APP_KEY for single-site deployments (see Environment & License).

This is what makes agency and portfolio setups cheap to run: one deployment, one database, one set of migrations - unlimited sites.

Migrations

The starter uses Payload's migration workflow, and ChaiBuilder's tables are merged into the same schema snapshot - so one command manages everything:

# create a migration after changing collections or upgrading the SDK
pnpm payload migrate:create

# apply pending migrations
pnpm payload migrate

In production, the starter is configured with prodMigrations, which means pending migrations run automatically when the app boots - a deploy with new migrations applies them on startup. You can still run pnpm payload migrate explicitly as a release step if you prefer migrations to happen before traffic switches over.

When you upgrade the ChaiBuilder SDK, the flow is the same as any Payload schema change: bump the package, run pnpm payload migrate:create, review the generated migration, and deploy. See Releases and Upgrading.

Custom table and column names

ChaiBuilder's tables default to names such as apps, app_pages and clients, with relationship columns such as app, client and parent. If your database already has an apps table, or your platform calls sites "websites", rename them in code. Build the schema from one naming object with chaipro/db/schema-factory and hand it to the adapter, to drizzle-kit and to Payload:

// chai-naming.ts - a prefix for every table, explicit table names keyed by the export name,
// and relationship column names (global, or per table)
export const chaiNaming = {
  tablePrefix: 'chai_',
  tables: { apps: 'websites', appPages: { name: 'website_pages', columns: { parent: 'parent_page' } } },
  columns: { app: 'website_id', client: 'client_id' },
}

// chaibuilder.config.ts
db: createPostgresDb({ url, tables: createChaiPgSchema(chaiNaming).schema }),

// src/db/chai-schema.ts (read by drizzle-kit, which only sees top-level exports)
export const { apps, appPages /* , every other table */ } = createChaiPgSchema(chaiNaming).tables

// Payload
beforeSchemaInit: [createChaiBuilderSchemaHook({ naming: chaiNaming })],

Use createChaiSqliteSchema for SQLite and libSQL. Only SQL names change: query code, row shapes and the public API keep ChaiBuilder's own keys. Constraint, index and sequence names follow a table rename and never a column rename.

Decide on names before the first migration. drizzle-kit reads a renamed table as a drop and a create, so renaming on an existing database means writing the RENAME migration yourself.

Your own code names

If your schema exports the tables under your own names, such as websites and websitePages, pass your schema to the adapter with tableMapping, keyed by ChaiBuilder's names:

import * as hostSchema from './db/schema'

db: createPostgresDb({
  sql,
  tables: hostSchema,
  tableMapping: { apps: 'websites', appPages: 'websitePages' },
}),

// Payload migrations keep your code names
beforeSchemaInit: [createChaiBuilderSchemaHook({ tables: hostSchema })],

Your code uses hostSchema.websitePages; ChaiBuilder uses its own keys over the same table objects. Every adapter accepts tableMapping. An entry can also map column keys when your table names them differently, and null declares that your table has no such column, for columns only optional features read:

tableMapping: {
  apps: { table: 'websites', columns: { client: 'owner' } },
  clients: { table: 'companies', columns: { loginHtml: null, helpHtml: null } },
},

Every other column ChaiBuilder declares must exist on the mapped table, or the adapter throws before the first query. Code names need no migration.

Adding your own columns

You can add your own columns to ChaiBuilder's tables, in the database only or also in the tables you pass as tables. ChaiBuilder writes explicit column lists and never replaces a row, so your values are kept:

  • ChaiBuilder inserts rows into most of its tables (pages, live pages, revisions, trash, assets, redirects, library items, form submissions, AI logs). A column you add to one of those must be nullable or have a database default.
  • A column only the database knows about is never read, written or copied.
  • A column you declare in your tables comes back on every full-row read. Publishing copies it from app_pages to app_pages_online and from apps to apps_online when both tables declare it, and a column only the live table has is kept. Duplicate Page and the library copy carry it to the new page. Restoring a revision does not touch it.
  • drizzle-kit push and Payload's dev push drop columns the schema does not declare. Declare your columns in the tables you pass (and to createChaiBuilderSchemaHook({ tables })), or add them through migrations only.

Seeding

A fresh database gets its initial content (admin user, starter pages) from the seed script:

pnpm seed

The scaffolder runs this for you on project creation; you only need it when pointing the app at a brand-new empty database.

Moving from SQLite to a remote database

Starting on a local SQLite file does not lock you in:

  • To Turso - import the local file into a Turso database, then switch DATABASE_URL to the libsql:// URL and set the auth token.
  • To Postgres - migrate the schema onto Postgres (run migrations against the new database), then move content. Plan this like any database migration; do it before the site accumulates significant content if you can.

Backups

The database is yours, so backups are yours too. Use your provider's point-in-time recovery (Neon, Supabase, RDS all offer it) or scheduled dumps (pg_dump) for self-hosted Postgres. For SQLite, snapshot the file while the app is stopped, or use Turso's built-in backups.

© ChaiBuilder. All rights reserved.

ChaiBuilderMade withChaiBuilder