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