Payload CMS Database Integration (MongoDB/PostgreSQL)

PostgreSQL and MongoDB Setup in Payload CMS

Payload 2.x supports two database adapters: PostgreSQL via Drizzle ORM and MongoDB via Mongoose. The choice affects data storage schema, query capabilities, and performance.

PostgreSQL via Drizzle ORM

npm install @payloadcms/db-postgres

// payload.config.ts
import { postgresAdapter } from '@payloadcms/db-postgres'

export default buildConfig({
  db: postgresAdapter({
    pool: {
      connectionString: process.env.DATABASE_URL,
      // Or explicit parameters:
      host: 'localhost',
      port: 5432,
      database: 'payload_db',
      user: 'payload',
      password: process.env.DB_PASSWORD,
      max: 20,           // max connections in pool
      idleTimeoutMillis: 30000,
    },
    push: false,         // don't push schema in production (use migrations)
    migrationDir: './migrations',
  }),
})

Drizzle Migrations:

# Generate migration after collection changes
npx payload migrate:create

# Apply migrations
npx payload migrate

# Rollback
npx payload migrate:down

# Status
npx payload migrate:status

Migrations are saved in ./migrations/*.ts — can be manually edited for complex schema changes.

Storage Schema in PostgreSQL:

-- For posts collection, Payload creates tables:
SELECT table_name FROM information_schema.tables
WHERE table_name LIKE 'posts%';

-- posts                   — main data
-- posts_rels              — relationships (relationship fields)
-- posts_locales           — localized fields (if enabled)
-- _posts_v                — versions (if versioning enabled)
-- _posts_v_locales        — versioned localized fields

Direct Queries via Drizzle:

import { getPayload } from 'payload'
import config from '@payload-config'

const payload = await getPayload({ config })
const drizzle = payload.db.drizzle  // direct access to Drizzle instance

// Raw SQL query
const result = await drizzle.execute(
  sql`SELECT p.*, COUNT(c.id) as comment_count
      FROM posts p
      LEFT JOIN comments c ON c.post_id = p.id
      WHERE p.status = 'published'
      GROUP BY p.id
      ORDER BY comment_count DESC
      LIMIT 10`
)

MongoDB via Mongoose

npm install @payloadcms/db-mongodb

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

export default buildConfig({
  db: mongooseAdapter({
    url: process.env.MONGODB_URI!,
    // Connection options
    connectOptions: {
      maxPoolSize: 10,
      serverSelectionTimeoutMS: 5000,
    },
  }),
})

Direct Mongoose Access:

const payload = await getPayload({ config })
const mongoose = payload.db.mongoose  // mongoose instance

// Native MongoDB query
const result = await mongoose.connection.db
  .collection('posts')
  .aggregate([
    { $match: { status: 'published' } },
    { $group: { _id: '$category', count: { $sum: 1 } } },
    { $sort: { count: -1 } },
  ])
  .toArray()

Adapter Comparison

Switching Adapters

Switching adapters after production launch without data migration is not directly possible — export/import via Payload API is required. The choice is made at project start.

Production Setup

// PostgreSQL in production — via SSL and connection pooling
db: postgresAdapter({
  pool: {
    connectionString: process.env.DATABASE_URL,
    ssl: { rejectUnauthorized: true },
    max: 10,
  },
  push: false,  // MUST be false in production
})

// Or via PgBouncer/Supabase Pooler
// connectionString: 'postgresql://user:[email protected]:6543/postgres?pgbouncer=true'

Timeline

Initial database adapter setup with migrations takes 0.5 days. Query optimization and indexing for production takes 1 day.