obsidian/wiki/payloadcms/database-postgres.md

---
title: "Database — Postgres"
aliases: [payload-postgres, payload-drizzle-postgres, postgresAdapter]
tags: [payloadcms, postgres, drizzle, database, migrations]
sources: [raw/database__postgres.md]
created: 2026-05-15
updated: 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

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

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

### Vercel variant

```ts
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:

```sh
npx payload generate:db-schema
```

```ts
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).

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

> Use [Drizzle introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull) (`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.

```ts
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`:

```ts
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|Database — Overview]]
- [[wiki/payloadcms/database-migrations|Database Migrations]]
- [[wiki/payloadcms/database-indexes|Database Indexes]]
- [[wiki/payloadcms/database-mongodb|Database — MongoDB]]
- [[wiki/payloadcms/configuration|Payload Config — Overview]]

## Sources

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