tests: spin postgres docker automatically in jest (pnpm test:int:postgres)

CONTRIBUTING.md

@@ -77,9 +77,13 @@ If you wish to use your own MongoDB database for the `test` directory instead of
 
 ### Using Postgres
 
-If you have postgres installed on your system, you can also run the test suites using postgres. By default, mongodb is used.
+Our test suites supports automatic PostgreSQL + PostGIS setup using Docker. No local PostgreSQL installation required. By default, mongodb is used.
 
-To do that, simply set the `PAYLOAD_DATABASE` environment variable to `postgres`.
+To use postgres, simply set the `PAYLOAD_DATABASE` environment variable to `postgres`.
+
+```bash
+PAYLOAD_DATABASE=postgres pnpm dev {suite}
+```
 
 ### Running the e2e and int tests
 

package.json

@@ -76,6 +76,8 @@
     "dev:prod:memorydb": "cross-env NODE_OPTIONS=--no-deprecation tsx ./test/dev.ts --prod --start-memory-db",
     "dev:vercel-postgres": "cross-env PAYLOAD_DATABASE=vercel-postgres pnpm runts ./test/dev.ts",
     "devsafe": "node ./scripts/delete-recursively.js '**/.next' && pnpm dev",
+    "docker:postgres": "docker compose -f test/docker-compose.yml up -d postgres",
+    "docker:postgres:stop": "docker compose -f test/docker-compose.yml down postgres",
     "docker:restart": "pnpm docker:stop --remove-orphans && pnpm docker:start",
     "docker:start": "docker compose -f test/docker-compose.yml up -d",
     "docker:stop": "docker compose -f test/docker-compose.yml down",

test/dev.ts

@@ -11,6 +11,7 @@ import { loadEnv } from 'payload/node'
 import { parse } from 'url'
 
 import { getNextRootDir } from './helpers/getNextRootDir.js'
+import startDatabases from './helpers/startDatabases.js'
 import startMemoryDB from './helpers/startMemoryDB.js'
 import { runInit } from './runInit.js'
 import { child } from './safelyRunScript.js'
@@ -79,6 +80,13 @@ if (shouldStartMemoryDB) {
 // for example process.env.MONGODB_MEMORY_SERVER_URI otherwise app.prepare() will clear them
 nextEnvImport.updateInitialEnv(process.env)
 
+// Auto-start PostgreSQL + PostGIS container when using PostgreSQL (after updateInitialEnv)
+if (process.env.PAYLOAD_DATABASE === 'postgres') {
+  await startDatabases()
+  // Update env again with the new POSTGRES_URL
+  nextEnvImport.updateInitialEnv(process.env)
+}
+
 // Open the admin if the -o flag is passed
 if (args.o) {
   await open(`http://localhost:3000${adminRoute}`)

test/docker-compose.yml

@@ -1,5 +1,25 @@
 version: '3.2'
 services:
+  # PostgreSQL with PostGIS for automatic test database
+  postgres:
+    image: ghcr.io/payloadcms/postgis-vector:latest
+    container_name: payload_postgres_test
+    restart: unless-stopped
+    ports:
+      - '5433:5432'
+    environment:
+      POSTGRES_USER: devuser
+      POSTGRES_PASSWORD: devpassword
+      POSTGRES_DB: mydb
+      PAYLOAD_DATABASE: postgres
+    volumes:
+      - postgres_data:/var/lib/postgresql/data
+    healthcheck:
+      test: ['CMD-SHELL', 'pg_isready -U devuser -d mydb']
+      interval: 5s
+      timeout: 3s
+      retries: 5
+
   localstack:
     image: localstack/localstack:latest
     container_name: localstack_demo
@@ -48,5 +68,6 @@ services:
       - ./google-cloud-storage/payload-bucket:/data/payload-bucket
 
 volumes:
+  postgres_data:
   google-cloud-storage:
   azurestoragedata:

test/helpers/startDatabases.ts

@@ -0,0 +1,116 @@
+import { execSync } from 'child_process'
+import dotenv from 'dotenv'
+import { MongoMemoryReplSet } from 'mongodb-memory-server'
+
+dotenv.config()
+
+declare global {
+  // Add the custom property to the NodeJS global type
+  // eslint-disable-next-line no-var
+  var _mongoMemoryServer: MongoMemoryReplSet | undefined
+  // eslint-disable-next-line no-var
+  var _postgresDockerStarted: boolean | undefined
+}
+
+/**
+ * Unified database setup for Jest global setup and development
+ * Automatically starts the appropriate database based on PAYLOAD_DATABASE env var:
+ * - MongoDB Memory Server (default)
+ * - PostgreSQL + PostGIS Docker container
+ */
+// eslint-disable-next-line no-restricted-exports
+export default async () => {
+  // Set test defaults only if not already set (allows dev to override)
+  if (!process.env.NODE_ENV) {
+    process.env.NODE_ENV = process.env.NODE_ENV || 'test'
+  }
+  process.env.PAYLOAD_DROP_DATABASE = process.env.PAYLOAD_DROP_DATABASE || 'true'
+  process.env.NODE_OPTIONS = process.env.NODE_OPTIONS || '--no-deprecation'
+  process.env.DISABLE_PAYLOAD_HMR = process.env.DISABLE_PAYLOAD_HMR || 'true'
+
+  if (!process.env.PAYLOAD_DATABASE || process.env.PAYLOAD_DATABASE === 'mongodb') {
+    // Start MongoDB Memory Server
+    if (!global._mongoMemoryServer) {
+      console.log('Starting memory db...')
+      const db = await MongoMemoryReplSet.create({
+        replSet: {
+          count: 3,
+          dbName: 'payloadmemory',
+        },
+      })
+
+      await db.waitUntilRunning()
+
+      global._mongoMemoryServer = db
+
+      process.env.MONGODB_MEMORY_SERVER_URI = `${global._mongoMemoryServer.getUri()}&retryWrites=true`
+      console.log('Started memory db')
+    }
+  } else if (process.env.PAYLOAD_DATABASE === 'postgres') {
+    // Start PostgreSQL + PostGIS Docker container
+    if (!global._postgresDockerStarted) {
+      try {
+        console.log('Auto-starting PostgreSQL + PostGIS container...')
+
+        // Fast check: if container is running and healthy, exit immediately
+        let containerRunning = false
+        try {
+          const result = execSync(
+            'docker ps --filter name=payload_postgres_test --filter health=healthy --format "{{.Names}}"',
+            {
+              encoding: 'utf8',
+              stdio: 'pipe',
+            },
+          ).trim()
+
+          if (result === 'payload_postgres_test') {
+            // Container is running and healthy - set env var and exit fast (< 100ms)
+            process.env.POSTGRES_URL = 'postgres://devuser:devpassword@127.0.0.1:5433/mydb'
+            global._postgresDockerStarted = true
+            console.log('PostgreSQL container already running and healthy')
+            return
+          }
+        } catch {
+          // Container not running or not healthy, continue to start it
+        }
+
+        // Start the container using docker-compose
+        console.log('Starting PostgreSQL + PostGIS container...')
+        execSync('docker compose -f test/docker-compose.yml up -d postgres', { stdio: 'inherit' })
+
+        // Wait for PostgreSQL to be ready with optimized timing
+        console.log('Waiting for PostgreSQL to be ready...')
+        let retries = 30
+        while (retries > 0) {
+          try {
+            execSync('docker exec payload_postgres_test pg_isready -U devuser -d mydb', {
+              stdio: 'ignore',
+            })
+            containerRunning = true
+            break
+          } catch {
+            retries--
+            if (retries === 0) {
+              throw new Error('PostgreSQL container failed to start within timeout')
+            }
+            // Faster initial checks, then slower ones
+            const delay = retries > 20 ? 300 : 1000
+            await new Promise((resolve) => setTimeout(resolve, delay))
+          }
+        }
+
+        if (containerRunning) {
+          process.env.POSTGRES_URL = 'postgres://devuser:devpassword@127.0.0.1:5433/mydb'
+          global._postgresDockerStarted = true
+          console.log('PostgreSQL + PostGIS container started successfully!')
+        }
+      } catch (error) {
+        console.error(
+          'Failed to start PostgreSQL container:',
+          error instanceof Error ? error.message : error,
+        )
+        throw error
+      }
+    }
+  }
+}

test/helpers/stopDatabases.ts

@@ -0,0 +1,28 @@
+import { execSync } from 'child_process'
+
+/**
+ * Unified database teardown for Jest global teardown
+ * Stops the appropriate database based on what was started
+ */
+export default () => {
+  if (!process.env.PAYLOAD_DATABASE || process.env.PAYLOAD_DATABASE === 'mongodb') {
+    // Stop MongoDB Memory Server
+    if (global._mongoMemoryServer) {
+      global._mongoMemoryServer
+        .stop()
+        .then(() => {
+          console.log('Stopped memory db')
+        })
+        .catch((e) => {
+          console.error('Error stopping memory db:', e)
+        })
+    }
+  } else if (process.env.PAYLOAD_DATABASE === 'postgres') {
+    // For PostgreSQL, we keep the container running for reuse
+    // This avoids connection termination errors and improves performance
+    // Use manual scripts like `pnpm docker:postgres:stop` to stop when needed
+    console.log(
+      'PostgreSQL container kept running for reuse (use `pnpm docker:postgres:stop` to stop)',
+    )
+  }
+}

test/jest.config.js

@@ -11,8 +11,8 @@ const customJestConfig = {
   testMatch: ['<rootDir>/**/*int.spec.ts'],
   setupFilesAfterEnv: ['<rootDir>/jest.setup.js'],
 
-  globalSetup: path.resolve(dirname, './helpers/startMemoryDB.ts'),
-  globalTeardown: path.resolve(dirname, './helpers/stopMemoryDB.ts'),
+  globalSetup: path.resolve(dirname, './helpers/startDatabases.ts'),
+  globalTeardown: path.resolve(dirname, './helpers/stopDatabases.ts'),
 
   moduleNameMapper: {
     '\\.(css|scss)$': '<rootDir>/helpers/mocks/emptyModule.js',