obsidian/wiki/payloadcms/database-postgres.md

title	aliases	tags	sources	created	updated
Database — Postgres	payload-postgres payload-drizzle-postgres postgresAdapter	payloadcms postgres drizzle database migrations	raw/database__postgres.md	2026-05-15	2026-05-15

Overview

Payload supports Postgres via @payloadcms/db-postgres, which wraps Drizzle ORM + node-postgres. A Vercel-optimised variant (@payloadcms/db-vercel-postgres) is also available.

Setup

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

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

Vercel variant

import { vercelPostgresAdapter } from '@payloadcms/db-vercel-postgres'

export default buildConfig({
  // Auto-reads process.env.POSTGRES_URL when no options passed
  db: vercelPostgresAdapter(),
})

Gotcha: vercelPostgresAdapter with a localhost/127.0.0.1 connection string silently falls back to the pg module — @vercel/postgres doesn't work locally. Override with forceUseVercelPostgres: true only if you set up a Docker Neon DB per the Vercel guide.

Key Options

Option	Default	Notes
pool *	—	Required. Drizzle/node-postgres pool config
push	true (dev only)	Auto-push schema changes in dev — disable if using migrations manually
migrationDir	—	Custom path for migration files
idType	'serial'	'serial' or 'uuid'
schemaName	'public'	Postgres schema (experimental)
transactionOptions	—	PgTransactionConfig or false to disable transactions
disableCreateDatabase	false	Prevent auto DB creation
localesSuffix	'_locales'	Suffix for locale tables
relationshipsSuffix	'_rels'	Suffix for relationship tables
versionsSuffix	'_v'	Suffix for version tables
readReplicas	—	Array of replica connection strings
readReplicasAfterWriteInterval	2000	ms to route reads to primary after a write (prevents stale reads)
blocksAsJSON	false	Store blocks as JSON column — improves perf for large block counts
beforeSchemaInit	—	Hook before Drizzle schema is built
afterSchemaInit	—	Hook after Drizzle schema is built
generateSchemaOutputFile	src/payload-generated.schema.ts	Override generated schema output path
allowIDOnCreate	false	Allow passing id in create operations

Direct Drizzle Access

Generate schema first, then query via Drizzle directly:

npx payload generate:db-schema

import { posts } from './payload-generated-schema'
import { eq, sql, and } from '@payloadcms/db-postgres/drizzle'

// Drizzle relational query API
const allPosts = await payload.db.drizzle.query.posts.findMany()

// Drizzle select API
const result = await payload.db.drizzle
  .select()
  .from(posts)
  .where(and(eq(posts.id, 50), sql`lower(${posts.title}) = 'example'`))

Also exposed on payload.db:

• payload.db.tables — all Drizzle table objects
• payload.db.enums — all Drizzle enum configs
• payload.db.relations — all Drizzle relations

Development: Push vs Migrations

Mode	Mechanism	When to use
Dev (push: true)	Drizzle auto-pushes schema changes instantly	Rapid local iteration
Dev (push: false)	Only migrations apply	Match prod workflow locally
Production	Always use migrations	push disabled in prod

Do not mix push and manual migrate commands — they conflict.

Drizzle Schema Hooks

beforeSchemaInit

Runs before Payload builds the schema. Use to inject external tables (e.g. preserving an existing DB when migrating to Payload).

postgresAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => ({
      ...schema,
      tables: {
        ...schema.tables,
        users,     // imported from Drizzle introspection
        countries,
      },
    }),
  ],
})

Use Drizzle introspection (drizzle-kit introspect) to generate schema from an existing DB.

Collision check: if your existing DB has a users table and Payload also has a users collection, change the collection's dbName to avoid overlap.

afterSchemaInit

Runs after schema is built. Use extendTable utility to add columns or indexes not in the Payload config.

postgresAdapter({
  afterSchemaInit: [
    ({ schema, extendTable }) => {
      extendTable({
        table: schema.tables.places,
        columns: { extraIntegerColumn: integer('extra_integer_column') },
        extraConfig: (table) => ({
          country_city_idx: index('country_city_idx').on(table.country, table.city),
        }),
      })
      return schema
    },
  ],
})

Including hook-added tables in generated schema

Columns/tables added via hooks are not included in payload generate:db-schema output by default. To include them, mutate adapter.rawTables inside beforeSchemaInit:

beforeSchemaInit: [
  ({ schema, adapter }) => {
    adapter.rawTables.posts.columns.customColumn = {
      name: 'custom_column',
      type: 'integer',
      notNull: true,
    }
    adapter.rawTables.posts.indexes.customColumnIdx = {
      name: 'custom_column_idx',
      unique: true,
      on: ['custom_column'],
    }
    return schema
  },
]

Key Takeaways

• Install @payloadcms/db-postgres; use @payloadcms/db-vercel-postgres only for Vercel deployments
• push: true (default in dev) auto-syncs schema — disable it if you want migration-only workflow
• readReplicas + readReplicasAfterWriteInterval handles read-heavy workloads without stale read risk
• blocksAsJSON: true can significantly improve performance when blocks are heavily used
• beforeSchemaInit / afterSchemaInit hooks allow extending the schema with non-Payload tables or columns
• Always generate Drizzle schema (npx payload generate:db-schema) before using typed direct Drizzle queries
• Schema hook additions require adapter.rawTables mutation to appear in the generated schema file

Related

• wiki/payloadcms/database-overview
• wiki/payloadcms/database-migrations
• wiki/payloadcms/database-indexes
• wiki/payloadcms/database-mongodb
• wiki/payloadcms/configuration

Sources

• raw/database__postgres.md — official Payload CMS Postgres adapter docs