0
# @payloadcms/db-postgres
1

2
The officially supported PostgreSQL database adapter for Payload CMS. This package provides a complete database abstraction layer using Drizzle ORM to handle PostgreSQL database operations including CRUD operations, migrations, schema management, transactions, and versioning.
3

4
## Package Information
5

6
- **Package Name**: @payloadcms/db-postgres
7
- **Package Type**: npm
8
- **Language**: TypeScript
9
- **Installation**: `npm install @payloadcms/db-postgres`
10

11
## Core Imports
12

13
```typescript
14
import { postgresAdapter, sql, geometryColumn } from "@payloadcms/db-postgres";
15
import type { PostgresAdapterArgs, PostgresAdapter, GeneratedDatabaseSchema, MigrateUpArgs, MigrateDownArgs } from "@payloadcms/db-postgres";
16
```
17

18
For migration utilities:
19

20
```typescript
21
import { migratePostgresV2toV3 } from "@payloadcms/db-postgres/migration-utils";
22
```
23

24
For Drizzle ORM integration:
25

26
```typescript
27
import { eq, and, or } from "@payloadcms/db-postgres/drizzle";
28
import { pgTable, text, integer } from "@payloadcms/db-postgres/drizzle/pg-core";
29
import { drizzle } from "drizzle-orm/node-postgres";
30
```
31

32
## Basic Usage
33

34
```typescript
35
import { buildConfig } from 'payload';
36
import { postgresAdapter } from '@payloadcms/db-postgres';
37

38
export default buildConfig({
39
  db: postgresAdapter({
40
    pool: {
41
      connectionString: process.env.DATABASE_URI,
42
    },
43
    // Optional configuration
44
    idType: 'serial', // or 'uuid'
45
    schemaName: 'my_schema', // experimental
46
    extensions: ['uuid-ossp', 'postgis'],
47
  }),
48
  // ...rest of config
49
});
50
```
51

52
## Architecture
53

54
The @payloadcms/db-postgres adapter is built around several key components:
55

56
- **PostgreSQL Adapter Factory**: The `postgresAdapter()` function creates a configured database adapter instance
57
- **Drizzle ORM Integration**: Complete Drizzle ORM functionality re-exported for direct database operations
58
- **Connection Management**: Built-in connection pooling, reconnection logic, and read replica support
59
- **Schema Management**: Dynamic schema generation, migrations, and custom schema support
60
- **Transaction Support**: Full transaction capabilities with configurable options
61
- **Migration System**: Version management and migration utilities for database schema evolution
62

63
## Capabilities
64

65
### Core Database Adapter
66

67
Main factory function and configuration for creating PostgreSQL database adapters with comprehensive features including connection pooling, schema management, and transaction support.
68

69
```typescript { .api }
70
function postgresAdapter(args: PostgresAdapterArgs): DatabaseAdapterObj<PostgresAdapter>;
71

72
interface PostgresAdapterArgs {
73
  /** PostgreSQL connection pool configuration (required) */
74
  pool: PoolConfig;
75
  /** Primary key type for generated IDs */
76
  idType?: 'serial' | 'uuid';
77
  /** Database schema name (experimental) */
78
  schemaName?: string;
79
  /** PostgreSQL extensions to enable */
80
  extensions?: string[];
81
  /** Directory containing migration files */
82
  migrationDir?: string;
83
  /** Allow custom ID values in create operations */
84
  allowIDOnCreate?: boolean;
85
  /** Store blocks as JSON instead of relational structure */
86
  blocksAsJSON?: boolean;
87
  /** Disable automatic database creation */
88
  disableCreateDatabase?: boolean;
89
  /** Suffix for locale tables */
90
  localesSuffix?: string;
91
  /** Suffix for relationship tables */
92
  relationshipsSuffix?: string;
93
  /** Suffix for version tables */
94
  versionsSuffix?: string;
95
  /** Filter specific tables */
96
  tablesFilter?: string[];
97
  /** Read replica connection strings */
98
  readReplicas?: string[];
99
  /** Transaction configuration or false to disable */
100
  transactionOptions?: false | PgTransactionConfig;
101
  /** Schema transformation hooks before initialization */
102
  beforeSchemaInit?: PostgresSchemaHook[];
103
  /** Schema transformation hooks after initialization */
104
  afterSchemaInit?: PostgresSchemaHook[];
105
  /** Drizzle query logger configuration */
106
  logger?: DrizzleConfig['logger'];
107
  /** Custom pg dependency injection */
108
  pg?: typeof import('pg');
109
  /** Enable schema pushing in development */
110
  push?: boolean;
111
  /** Production migration definitions */
112
  prodMigrations?: Array<{
113
    name: string;
114
    up: (args: MigrateUpArgs) => Promise<void>;
115
    down: (args: MigrateDownArgs) => Promise<void>;
116
  }>;
117
}
118
```
119

120
[Core Adapter](./core-adapter.md)
121

122
### SQL Template Literal
123

124
Raw SQL query builder for complex database operations that need to bypass the ORM.
125

126
```typescript { .api }
127
const sql: SQL;
128
```
129

130
**Usage Example:**
131

132
```typescript
133
import { sql } from "@payloadcms/db-postgres";
134

135
// Raw SQL queries in Payload hooks
136
const result = await payload.db.drizzle.execute(sql`
137
  SELECT * FROM users 
138
  WHERE created_at > ${new Date('2023-01-01')}
139
`);
140
```
141

142
### PostGIS Geometry Column
143

144
Helper function for creating PostGIS geometry columns for spatial data.
145

146
```typescript { .api }
147
function geometryColumn(
148
  name: string,
149
  type: 'POINT' | 'LINESTRING' | 'POLYGON' | 'MULTIPOINT' | 'MULTILINESTRING' | 'MULTIPOLYGON',
150
  srid?: number
151
): PgGeometry;
152
```
153

154
**Usage Example:**
155

156
```typescript
157
import { geometryColumn } from "@payloadcms/db-postgres";
158
import { pgTable, text, uuid } from "@payloadcms/db-postgres/drizzle/pg-core";
159

160
const locations = pgTable('locations', {
161
  id: uuid('id').primaryKey().defaultRandom(),
162
  name: text('name').notNull(),
163
  point: geometryColumn('point', 'POINT', 4326),
164
});
165
```
166

167
### Migration Utilities
168

169
Migration helper functions for upgrading PostgreSQL schemas from older versions of Payload.
170

171
```typescript { .api }
172
function migratePostgresV2toV3(args: MigrateUpArgs): Promise<void>;
173
```
174

175
[Migration Utils](./migration-utils.md)
176

177
### Drizzle ORM Integration
178

179
Complete Drizzle ORM functionality re-exported for direct database operations, schema definitions, and raw SQL queries.
180

181
```typescript { .api }
182
// Core Drizzle exports
183
const sql: SQL;
184
function eq<T>(left: T, right: T): SQLWrapper;
185
function and(...conditions: SQLWrapper[]): SQLWrapper;
186
function or(...conditions: SQLWrapper[]): SQLWrapper;
187

188
// PostgreSQL-specific exports
189
function pgTable(name: string, columns: Record<string, any>): PgTable;
190
function text(name?: string): PgText;
191
function integer(name?: string): PgInteger;
192
function pgEnum<T extends string>(name: string, values: readonly [T, ...T[]]): PgEnum<T>;
193
function pgSchema(name: string): PgSchema;
194

195
// Node.js PostgreSQL driver integration
196
function drizzle(options: DrizzleConfig): NodePgDatabase;
197
```
198

199
[Drizzle Integration](./drizzle-integration.md)
200

201
## Types
202

203
```typescript { .api }
204
interface PostgresAdapter extends BasePostgresAdapter {
205
  drizzle: Drizzle;
206
  pg: typeof import('pg');
207
  pool: Pool;
208
  poolOptions: PoolConfig;
209
}
210

211
interface GeneratedDatabaseSchema {
212
  schemaUntyped: Record<string, unknown>;
213
}
214

215
type Drizzle = 
216
  | NodePgDatabase<ResolveSchemaType<GeneratedDatabaseSchema>>
217
  | PgWithReplicas<PgDatabase<PgQueryResultHKT, Record<string, unknown>, ExtractTablesWithRelations<Record<string, unknown>>>>;
218

219
interface DatabaseAdapterObj<T> {
220
  name: string;
221
  allowIDOnCreate: boolean;
222
  defaultIDType: 'number' | 'text';
223
  init: (options: { payload: Payload }) => T;
224
}
225

226
interface PoolConfig {
227
  connectionString?: string;
228
  host?: string;
229
  port?: number;
230
  user?: string;
231
  password?: string;
232
  database?: string;
233
  ssl?: boolean | object;
234
  max?: number;
235
  min?: number;
236
  idle?: number;
237
  acquire?: number;
238
  evict?: number;
239
}
240

241
interface PgTransactionConfig {
242
  isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable';
243
  accessMode?: 'read only' | 'read write';
244
  deferrable?: boolean;
245
}
246

247
type PostgresSchemaHook = (args: { schema: Record<string, unknown> }) => Record<string, unknown>;
248

249
interface MigrateUpArgs {
250
  /** The Postgres Drizzle instance for executing SQL directly within the current transaction */
251
  db: PostgresDB;
252
  /** The Payload instance for executing Local API methods */
253
  payload: Payload;
254
  /** The PayloadRequest object containing the current transaction */
255
  req: PayloadRequest;
256
}
257

258
interface MigrateDownArgs {
259
  /** The Postgres Drizzle instance for executing SQL directly within the current transaction */
260
  db: PostgresDB;
261
  /** The Payload instance for executing Local API methods */
262
  payload: Payload;
263
  /** The PayloadRequest object containing the current transaction */
264
  req: PayloadRequest;
265
}
266

267
// External types from dependencies (for reference)
268
interface Payload {
269
  /** Payload CMS instance - see Payload CMS documentation */
270
}
271

272
interface PayloadRequest {
273
  /** Payload request object containing transaction context - see Payload CMS documentation */
274
}
275

276
interface PostgresDB {
277
  /** PostgreSQL database instance from Drizzle ORM - see Drizzle ORM documentation */
278
}
279

280
interface BasePostgresAdapter {
281
  /** Base PostgreSQL adapter interface from @payloadcms/drizzle package */
282
}
283

284
type Pool = import('pg').Pool;
285
type NodePgDatabase<T> = import('drizzle-orm/node-postgres').NodePgDatabase<T>;
286
type PgWithReplicas<T> = import('drizzle-orm/pg-core').PgWithReplicas<T>;
287
type PgDatabase<A, B, C> = import('drizzle-orm/pg-core').PgDatabase<A, B, C>;
288
type PgQueryResultHKT = import('drizzle-orm/pg-core').PgQueryResultHKT;
289
type ExtractTablesWithRelations<T> = import('drizzle-orm').ExtractTablesWithRelations<T>;
290
type ResolveSchemaType<T> = 'schema' extends keyof T ? T['schema'] : GeneratedDatabaseSchema['schemaUntyped'];
291
```