obsidian/wiki/payloadcms/database.md

tags	topic	sources	created
payloadcms tech-patterns	payloadcms	database__overview.md database__postgres.md database__mongodb.md database__sqlite.md database__migrations.md database__indexes.md database__transactions.md	2026-05-15

PayloadCMS — Database

Overview

• Payload is database-agnostic — integrates via swappable Database Adapters
• Adapter translates Payload's internal data structures to the native DB format
• db property in wiki/payloadcms/configuration accepts the adapter instance
• All major Payload features (localization, arrays, blocks, versions) work across all official adapters
• Exception: Point field not yet supported in SQLite

Supported Databases

Feature	MongoDB	Postgres	SQLite
Adapter pkg	@payloadcms/db-mongodb	@payloadcms/db-postgres	@payloadcms/db-sqlite
ORM	Mongoose	Drizzle + node-postgres	Drizzle + libSQL
Migrations required	Rarely	Always	Always
Schema enforced at DB level	No	Yes	Yes
Transactions	Requires replica set	Yes	Disabled by default
Point field	Yes	Yes	No
db push (dev mode)	N/A	Yes	Yes

Prefer MongoDB when:

• Lots of dynamic fields, arrays/blocks, or heavy localization
• Don't want to manage DDL migrations
• Comfortable letting Payload enforce data integrity

Prefer Postgres/SQLite when:

• Flat, stable schema with strong DB-level constraints
• Need enforced foreign key relationships
• Comfortable with migration workflow

Setup / Config

MongoDB

import { mongooseAdapter } from '@payloadcms/db-mongodb'

export default buildConfig({
  db: mongooseAdapter({
    url: process.env.DATABASE_URL,
  }),
})

Key options: transactionOptions, migrationDir, connectOptions, collation, allowAdditionalKeys

Compatibility shims for DocumentDB, Cosmos DB, Firestore:

import { mongooseAdapter, compatibilityOptions } from '@payloadcms/db-mongodb'
db: mongooseAdapter({ url: process.env.DATABASE_URL, ...compatibilityOptions.firestore })

Postgres

import { postgresAdapter } from '@payloadcms/db-postgres'

export default buildConfig({
  db: postgresAdapter({
    pool: { connectionString: process.env.DATABASE_URL },
  }),
})

Vercel variant: @payloadcms/db-vercel-postgres — auto-uses process.env.POSTGRES_URL, falls back to pg for localhost.

Key options: pool*, push, migrationDir, idType (serial|uuid), transactionOptions, readReplicas, readReplicasAfterWriteInterval (default 2000ms), blocksAsJSON, schemaName

SQLite

import { sqliteAdapter } from '@payloadcms/db-sqlite'

export default buildConfig({
  db: sqliteAdapter({
    client: {
      url: process.env.DATABASE_URL,
      authToken: process.env.DATABASE_AUTH_TOKEN,
    },
  }),
})

Key options: client*, push, migrationDir, idType (number|uuid), transactionOptions, wal, autoIncrement, blocksAsJSON, busyTimeout

Cloudflare D1 (beta): @payloadcms/db-d1-sqlite with binding: cloudflare.env.D1

Enable WAL mode for better concurrency:

db: sqliteAdapter({ wal: true, client: { url: process.env.DATABASE_URL } })

Migrations

Default storage path: ./src/migrations. Override via migrationDir in adapter options.

Each migration file exports up and down functions (TypeScript):

import { MigrateUpArgs, MigrateDownArgs } from '@payloadcms/your-db-adapter'

export async function up({ payload, req }: MigrateUpArgs): Promise<void> { /* ... */ }
export async function down({ payload, req }: MigrateDownArgs): Promise<void> { /* ... */ }

CLI Commands

npm run payload migrate              # run pending migrations
npm run payload migrate:create [name] # create new migration file
npm run payload migrate:status       # show which migrations ran / pending
npm run payload migrate:down         # roll back last batch
npm run payload migrate:refresh      # roll back all, re-run all
npm run payload migrate:reset        # roll back all
npm run payload migrate:fresh        # drop all entities, re-run from scratch

migrate:create flags: --skip-empty, --force-accept-warning

Requires npm script in package.json:

{ "scripts": { "payload": "cross-env PAYLOAD_CONFIG_PATH=src/payload.config.ts payload" } }

Recommended CI workflow (Postgres)

{ "scripts": { "ci": "payload migrate && pnpm build" } }

Runtime migrations (long-running servers)

import { migrations } from './migrations'
db: postgresAdapter({ prodMigrations: migrations })

Avoid on serverless — slows cold starts.

MongoDB vs Postgres migration strategy

• MongoDB: only needed when transforming existing data from shape A → B; run locally against prod DB or in CI
• Postgres: required for every schema change; use push mode locally (don't mix push + migrate on same DB)

Environment-specific config gotcha

If a plugin is enabled only in production, generate migrations with prod env vars active — otherwise the migration will be incomplete.

Indexes & Transactions

Indexes

Field-level index:

{ name: 'title', type: 'text', index: true }

Compound index (collection config):

indexes: [{ fields: ['title', 'createdAt'], unique: true }]

• id, createdAt, updatedAt are indexed by default
• unique: true creates a collection-wide unique DB index
• Localized fields with index: true on MongoDB → one index per locale path; can hit per-collection index limit with many locales

Transactions

• Enabled by default for all write operations where the DB supports it
• MongoDB: requires replica set connection
• SQLite: disabled by default — enable with transactionOptions: {}
• Pass req into Local API calls to participate in the same transaction:

const afterChange: CollectionAfterChangeHook = async ({ req }) => {
  await req.payload.create({ req, collection: 'my-slug', data: { some: 'data' } })
}

• Do not pass req for fire-and-forget (unawaited) calls — rolled-back transaction would still return OK

Direct transaction control:

const transactionID = await payload.db.beginTransaction()
try {
  await payload.update({ collection: 'posts', data: {}, where: {}, req: { transactionID } })
  await payload.db.commitTransaction(transactionID)
} catch {
  await payload.db.rollbackTransaction(transactionID)
}

Disable per-operation: disableTransaction: true in Local API args. Disable globally: transactionOptions: false in adapter config.

Gotchas

• Do not mix push and migrate on the same local dev database — Payload will warn and it causes drift
• Azure Cosmos DB: no cross-collection transactions; requires indexSortableFields: true in root config
• unique on array/blocks nested fields: creates collection-wide uniqueness, not per-document; on MongoDB with required: true → non-sparse index causes duplicate key errors on empty arrays
• Vercel Postgres adapter: uses pg (not @vercel/postgres) for localhost URLs; use forceUseVercelPostgres: true to override
• Schema hooks (beforeSchemaInit/afterSchemaInit): columns/tables added via hooks do NOT appear in payload generate:db-schema output — mutate adapter.rawTables inside beforeSchemaInit if you need them in the generated schema
• prodMigrations: runtime migration on serverless = slow cold starts; only use for long-running containers
• Environment-specific plugins: migrations generated without prod env vars will miss plugin-added fields

Related

• wiki/payloadcms/configuration
• wiki/payloadcms/getting-started
• wiki/payloadcms/fields
• wiki/payloadcms/hooks