2023-10-05 10:34:56 -04:00
---
title: Postgres
label: Postgres
2025-07-14 23:55:16 -04:00
order: 60
2024-07-11 14:55:13 -04:00
desc: Payload supports Postgres through an officially supported Drizzle Database Adapter.
2024-05-13 10:29:59 -04:00
keywords: Postgres, documentation, typescript, Content Management System, cms, headless, javascript, node, react, nextjs
2023-10-08 14:23:18 -04:00
---
To use Payload with Postgres, install the package `@payloadcms/db-postgres`. It leverages Drizzle ORM and `node-postgres` to interact with a Postgres database that you provide.
2024-08-23 15:17:26 -04:00
Alternatively, the `@payloadcms/db-vercel-postgres` package is also available and is optimized for use with Vercel.
2023-10-08 14:23:18 -04:00
It automatically manages changes to your database for you in development mode, and exposes a full suite of migration controls for you to leverage in order to keep other database environments in sync with your schema. DDL transformations are automatically generated.
2024-07-11 09:54:21 -04:00
To configure Payload to use Postgres, pass the `postgresAdapter` to your Payload Config as follows:
2023-10-08 14:23:18 -04:00
2024-08-23 15:17:26 -04:00
### Usage
`@payloadcms/db-postgres`:
2023-10-08 14:23:18 -04:00
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
export default buildConfig({
// Configure the Postgres adapter here
db: postgresAdapter({
// Postgres-specific arguments go here.
// `pool` is required.
pool: {
test: improve database test setup (#14982)
This PR brings over most of the test suite improvements I made
[here](https://github.com/payloadcms/enterprise-plugins/pull/249) to our
payload monorepo.
- Replaces mongodb-memory-server with an actual mongo db using the
mongodb-community-server docker image. This unblocks the vitest
migration PR (https://github.com/payloadcms/payload/pull/14337).
Currently, debugging does not work in that PR - this is due to the
global setup script that has to start the mongo memory db.
- Just like postgres, all mongodb databases now support vector search.
mongodb-atlas-local supports it natively, and for
mongodb-community-server, our docker compose script installs `mongot`,
which unlocks support for vector search. This means we could add [vector
storage/search tests similar to the ones we have for
postgres](https://github.com/payloadcms/payload/blob/main/test/database/postgres-vector.int.spec.ts)
- Int tests now run against both mongodb adapters: mongodb
(mongodb-community-server) and mongodb-atlas (mongodb-atlas-local)
- Adds docker scripts for mongodb, mongodb-atlas, and postgres,
documented in README.md. Updates default db adapter URLs to
automatically pick up databases started by those scripts. This makes it
easier for people cloning the repo to get started with consistent
databases matching CI - no complicated manual installation steps
- Simplified db setup handling locally in CI. In CI, everything is
scoped to `.github/actions/start-database/action.yml`. Locally,
everything is scoped to `test/helpers/db`. Each database adapter now
shares the same username, password and db name
- Use consistent db connection string env variables, all ending with
_URL
- Updates the CONTRIBUTING.md with up-to-date information and adds a new
database section. We now recommend everyone to use those docker scripts
2025-12-19 07:07:16 -08:00
connectionString: process.env.DATABASE_URL,
2023-10-08 14:23:18 -04:00
},
}),
})
```
2024-08-23 15:17:26 -04:00
`@payloadcms/db-vercel-postgres`:
```ts
import { vercelPostgresAdapter } from '@payloadcms/db-vercel-postgres'
export default buildConfig({
2025-05-26 01:58:26 +08:00
// Automatically uses process.env.POSTGRES_URL if no options are provided.
2024-08-23 15:17:26 -04:00
db: vercelPostgresAdapter(),
// Optionally, can accept the same options as the @vercel/postgres package.
db: vercelPostgresAdapter({
pool: {
2025-03-14 11:13:08 -06:00
connectionString: process.env.DATABASE_URL,
2024-08-23 15:17:26 -04:00
},
}),
})
```
2024-12-16 23:55:08 +02:00
<Banner type="info">
2025-10-24 23:53:45 +03:00
**Note:** If you're using `vercelPostgresAdapter` and your
2025-03-14 11:13:08 -06:00
`process.env.POSTGRES_URL` or `pool.connectionString` points to a local
database (e.g hostname has `localhost` or `127.0.0.1`) we use the `pg` module
for pooling instead of `@vercel/postgres`. This is because `@vercel/postgres`
doesn't work with local databases, if you want to disable that behavior, you
can pass `forceUseVercelPostgres: true` to the adapter's args and follow
[Vercel
guide](https://vercel.com/docs/storage/vercel-postgres/local-development#option-2:-local-postgres-instance-with-docker)
for a Docker Neon DB setup.
2024-12-16 23:55:08 +02:00
</Banner>
2024-06-05 17:08:15 -04:00
## Options
2023-10-08 14:23:18 -04:00
2026-03-30 18:45:24 +01:00
| Option | Description |
| -------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `pool` \* | [Pool connection options](https://orm.drizzle.team/docs/quick-postgresql/node-postgres) that will be passed to Drizzle and `node-postgres` or to `@vercel/postgres` |
| `push` | Disable Drizzle's [`db push`](https://orm.drizzle.team/kit-docs/overview#prototyping-with-db-push) in development mode. By default, `push` is enabled for development mode only. |
| `migrationDir` | Customize the directory that migrations are stored. |
| `schemaName` (experimental) | A string for the postgres schema to use, defaults to 'public'. |
| `idType` | A string of 'serial', or 'uuid' that is used for the data type given to id columns. |
| `transactionOptions` | A PgTransactionConfig object for transactions, or set to `false` to disable using transactions. [More details](https://orm.drizzle.team/docs/transactions) |
| `disableCreateDatabase` | Pass `true` to disable auto database creation if it doesn't exist. Defaults to `false`. |
| `localesSuffix` | A string appended to the end of table names for storing localized fields. Default is '\_locales'. |
| `relationshipsSuffix` | A string appended to the end of table names for storing relationships. Default is '\_rels'. |
| `versionsSuffix` | A string appended to the end of table names for storing versions. Defaults to '\_v'. |
| `beforeSchemaInit` | Drizzle schema hook. Runs before the schema is built. [More Details](#beforeschemainit) |
| `afterSchemaInit` | Drizzle schema hook. Runs after the schema is built. [More Details](#afterschemainit) |
| `generateSchemaOutputFile` | Override generated schema from `payload generate:db-schema` file path. Defaults to `{CWD}/src/payload-generated.schema.ts` |
| `allowIDOnCreate` | Set to `true` to use the `id` passed in data on the create API operations without using a custom ID field. |
| `readReplicas` | An array of DB read replicas connection strings, can be used to offload read-heavy traffic. |
| `readReplicasAfterWriteInterval` | How long (ms) after a write to keep routing reads to the primary instead of a replica. Prevents stale reads caused by replication lag. Only relevant when `readReplicas` is set. Default `2000`. Set to `0` to disable. |
| `blocksAsJSON` | Store blocks as a JSON column instead of using the relational structure which can improve performance with a large amount of blocks |
2023-10-08 14:23:18 -04:00
2024-06-05 17:08:15 -04:00
## Access to Drizzle
2023-10-08 14:23:18 -04:00
After Payload is initialized, this adapter will expose the full power of Drizzle to you for use if you need it.
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
To ensure type-safety, you need to generate Drizzle schema first with:
2025-03-14 11:13:08 -06:00
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
```sh
npx payload generate:db-schema
```
2023-10-08 14:23:18 -04:00
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
Then, you can access Drizzle as follows:
2025-03-14 11:13:08 -06:00
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
```ts
import { posts } from './payload-generated-schema'
// To avoid installing Drizzle, you can import everything that drizzle has from our re-export path.
import { eq, sql, and } from '@payloadcms/db-postgres/drizzle'
// Drizzle's Querying API: https://orm.drizzle.team/docs/rqb
const posts = await payload.db.drizzle.query.posts.findMany()
// Drizzle's Select API https://orm.drizzle.team/docs/select
2025-03-14 11:13:08 -06:00
const result = await payload.db.drizzle
.select()
.from(posts)
.where(
and(eq(posts.id, 50), sql`lower(${posts.title}) = 'example post title'`),
)
2023-10-08 14:23:18 -04:00
```
2024-06-05 17:08:15 -04:00
## Tables, relations, and enums
2023-10-08 14:23:18 -04:00
In addition to exposing Drizzle directly, all of the tables, Drizzle relations, and enum configs are exposed for you via the `payload.db` property as well.
- Tables - `payload.db.tables`
- Enums - `payload.db.enums`
- Relations - `payload.db.relations`
2024-06-05 17:08:15 -04:00
## Prototyping in development mode
2023-10-08 14:23:18 -04:00
Drizzle exposes two ways to work locally in development mode.
2024-07-11 09:54:21 -04:00
The first is [`db push`](https://orm.drizzle.team/kit-docs/overview#prototyping-with-db-push), which automatically pushes changes you make to your Payload Config (and therefore, Drizzle schema) to your database so you don't have to manually migrate every time you change your Payload Config. This only works in development mode, and should not be mixed with manually running [`migrate`](/docs/database/migrations) commands.
2023-10-08 14:23:18 -04:00
You will be warned if any changes that you make will entail data loss while in development mode. Push is enabled by default, but you can opt out if you'd like.
2024-07-11 09:54:21 -04:00
Alternatively, you can disable `push` and rely solely on migrations to keep your local database in sync with your Payload Config.
2023-10-08 14:23:18 -04:00
2024-06-05 17:08:15 -04:00
## Migration workflows
2023-10-08 14:23:18 -04:00
2024-06-24 13:17:50 -04:00
In Postgres, migrations are a fundamental aspect of working with Payload and you should become familiar with how they work.
2023-10-08 14:23:18 -04:00
2024-10-17 15:13:37 -04:00
For more information about migrations, [click here](./migrations#when-to-run-migrations).
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
## Drizzle schema hooks
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
2025-03-14 11:13:08 -06:00
import {
integer,
pgTable,
serial,
} from '@payloadcms/db-postgres/drizzle/pg-core'
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when migrating to Payload. By default, Payload drops the current database schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use [Drizzle Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
2025-03-14 11:13:08 -06:00
import {
pgTable,
uniqueIndex,
serial,
varchar,
text,
} from 'drizzle-orm/pg-core'
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the `beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
2025-03-14 11:13:08 -06:00
countries,
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
},
}
},
],
})
```
2024-12-16 23:55:08 +02:00
Make sure Payload doesn't overlap table names with its collections. For example, if you already have a collection with slug "users", you should either change the slug or `dbName` to change the table name for this collection.
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify the schema with features that aren't supported by Payload, or if you want to add a column that you don't want to be in the Payload config.
2025-05-26 01:58:26 +08:00
To extend a table, Payload exposes `extendTable` utility to the args. You can refer to the [Drizzle documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
The following example adds the `extra_integer_column` column and a composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
import { index, integer } from '@payloadcms/db-postgres/drizzle/pg-core'
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
2025-03-14 11:13:08 -06:00
country_city_composite_index: index(
'country_city_composite_index',
).on(table.country, table.city),
feat(drizzle): customize schema with before / after init hooks (#8196)
Adds abillity to customize the generated Drizzle schema with
`beforeSchemaInit` and `afterSchemaInit`. Could be useful if you want to
preserve the existing database schema / override the generated one with
features that aren't supported from the Payload config.
## Docs:
### beforeSchemaInit
Runs before the schema is built. You can use this hook to extend your
database structure with tables that won't be managed by Payload.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { integer, pgTable, serial } from 'drizzle-orm/pg-core'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
addedTable: pgTable('added_table', {
id: serial('id').notNull(),
}),
},
}
},
],
})
```
One use case is preserving your existing database structure when
migrating to Payload. By default, Payload drops the current database
schema, which may not be desirable in this scenario.
To quickly generate the Drizzle schema from your database you can use
[Drizzle
Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull)
You should get the `schema.ts` file which may look like this:
```ts
import { pgTable, uniqueIndex, serial, varchar, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
fullName: text('full_name'),
phone: varchar('phone', { length: 256 }),
})
export const countries = pgTable(
'countries',
{
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
},
(countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
},
)
```
You can import them into your config and append to the schema with the
`beforeSchemaInit` hook like this:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { users, countries } from '../drizzle/schema'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
return {
...schema,
tables: {
...schema.tables,
users,
countries
},
}
},
],
})
```
Make sure Payload doesn't overlap table names with its collections. For
example, if you already have a collection with slug "users", you should
either change the slug or `dbName` to change the table name for this
collection.
### afterSchemaInit
Runs after the Drizzle schema is built. You can use this hook to modify
the schema with features that aren't supported by Payload, or if you
want to add a column that you don't want to be in the Payload config.
To extend a table, Payload exposes `extendTable` utillity to the args.
You can refer to the [Drizzle
documentation](https://orm.drizzle.team/docs/sql-schema-declaration).
The following example adds the `extra_integer_column` column and a
composite index on `country` and `city` columns.
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
import { index, integer } from 'drizzle-orm/pg-core'
import { buildConfig } from 'payload'
export default buildConfig({
collections: [
{
slug: 'places',
fields: [
{
name: 'country',
type: 'text',
},
{
name: 'city',
type: 'text',
},
],
},
],
db: postgresAdapter({
afterSchemaInit: [
({ schema, extendTable, adapter }) => {
extendTable({
table: schema.tables.places,
columns: {
extraIntegerColumn: integer('extra_integer_column'),
},
extraConfig: (table) => ({
country_city_composite_index: index('country_city_composite_index').on(
table.country,
table.city,
),
}),
})
return schema
},
],
}),
})
```
<!--
For external contributors, please include:
- A summary of the pull request and any related issues it fixes.
- Reasoning for the changes made or any additional context that may be
useful.
Ensure you have read and understand the
[CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md)
document in this repository.
-->
2024-09-25 22:14:03 +03:00
}),
})
return schema
},
],
}),
})
```
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
### Note for generated schema:
2025-03-14 11:13:08 -06:00
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
Columns and tables, added in schema hooks won't be added to the generated via `payload generate:db-schema` Drizzle schema.
If you want them to be there, you either have to edit this file manually or mutate the internal Payload "raw" SQL schema in the `beforeSchemaInit`:
```ts
import { postgresAdapter } from '@payloadcms/db-postgres'
postgresAdapter({
beforeSchemaInit: [
({ schema, adapter }) => {
// Add a new table
2024-12-30 18:03:46 +02:00
adapter.rawTables.myTable = {
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
name: 'my_table',
2024-12-30 18:03:46 +02:00
columns: {
my_id: {
name: 'my_id',
type: 'serial',
2025-03-14 11:13:08 -06:00
primaryKey: true,
},
},
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
}
// Add a new column to generated by Payload table:
2024-12-30 18:03:46 +02:00
adapter.rawTables.posts.columns.customColumn = {
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
name: 'custom_column',
// Note that Payload SQL doesn't support everything that Drizzle does.
type: 'integer',
2025-03-14 11:13:08 -06:00
notNull: true,
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
}
// Add a new index to generated by Payload table:
2024-12-30 18:03:46 +02:00
adapter.rawTables.posts.indexes.customColumnIdx = {
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
name: 'custom_column_idx',
unique: true,
2025-03-14 11:13:08 -06:00
on: ['custom_column'],
feat(db-postgres, db-sqlite): drizzle schema generation (#9953)
This PR allows to have full type safety on `payload.drizzle` with a
single command
```sh
pnpm payload generate:db-schema
```
Which generates TypeScript code with Drizzle declarations based on the
current database schema.
Example of generated file with the website template:
https://gist.github.com/r1tsuu/b8687f211b51d9a3a7e78ba41e8fbf03
Video that shows the power:
https://github.com/user-attachments/assets/3ced958b-ec1d-49f5-9f51-d859d5fae236
We also now proxy drizzle package the same way we do for Lexical so you
don't have to install it (and you shouldn't because you may have version
mismatch).
Instead, you can import from Drizzle like this:
```ts
import {
pgTable,
index,
foreignKey,
integer,
text,
varchar,
jsonb,
boolean,
numeric,
serial,
timestamp,
uniqueIndex,
pgEnum,
} from '@payloadcms/db-postgres/drizzle/pg-core'
import { sql } from '@payloadcms/db-postgres/drizzle'
import { relations } from '@payloadcms/db-postgres/drizzle/relations'
```
Fixes https://github.com/payloadcms/payload/discussions/4318
In the future we can also support types generation for mongoose / raw
mongodb results.
2024-12-19 18:08:17 +02:00
}
return schema
},
],
})
```
