feat(db-postgres, db-sqlite): drizzle schema generation

[r1tsuu]

This PR allows to have full type safety on payload.drizzle with a single command

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:

Screen.Recording.2024-12-13.at.08.44.52.mov

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:

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 #4318

In the future we can also support types generation for mongoose / raw mongodb results.

[valerioleo]

Soooo happy for this! Great work team.

[willviles]

This is awesome @r1tsuu. I love that this PR is close. QQs:

1. Does this generate duplicate schemas for custom tables & relations passed in the beforeSchemaInit hook?
2. If the Payload schema is extended in afterSchemaInit, will the generated schema include these amends?

[willviles]

Also - one big thing I've noticed from using Drizzle introspection is that the output doesn't work well when entities have either self or circular relationships.

For example, tables with circular foreign key references such as the one below create an error on foreignKey definitions, resulting in an any type attributed to each table.

// Function implicitly has return type 'any' because it does not have a return type annotation and is referenced directly or indirectly in one of its return expressions.
export const mediaInPayload = payload.table("media", {
    ownerId: varchar("owner_id"),
}, (table) => {
    return {
        mediaOwnerIdUsersIdFk: foreignKey({
            columns: [table.ownerId],
            foreignColumns: [usersInPayload.id],
            name: "media_owner_id_users_id_fk"
        }).onDelete("set null"),
    }
});

// Function implicitly has return type 'any' because it does not have a return type annotation and is referenced directly or indirectly in one of its return expressions.
export const usersInPayload = payload.table("users", {
    avatarId: varchar("avatar_id"),
}, (table) => {
    return {
        usersAvatarIdMediaIdFk: foreignKey({
            columns: [table.avatarId],
            foreignColumns: [mediaInPayload.id],
            name: "users_avatar_id_media_id_fk"
        }).onDelete("set null"),
    }
});

Issue refs can be found in this Discord thread & these docs.

Is this an issue that may pose problems?

[magicspon]

Well this give us access to the eq function from drizzle?

[DanRibbens]

Looks good! You will need to add this feature to the docs. Otherwise, I'm ready to approve.

[r1tsuu]

@DanRibbens

• Wrote docs.
• Wrote an integration test that ensures the generated file is compilable and exports every table/relation/enum. Improved pattern for conditionals in tests.
• Few additional fixes.

@willviles
Sorry for the delay, I'm ready to answer your questions. We don't use Drizzle's introspection anymore here and this PR does all the schema codegen logic by itself.
Why? I noticed that what Drizzle generates through the introspection is very different from what we have in payload.db.drizzle. For example, export table names, names of properties for columns, different relations that don't actually exist in payload.db.drizzle (or have different names under payloa.db.drizzle).
The new method generates 100% the same code of the schema that Payload actually uses for its migrations / schema push, therefore, now you can even swap payload migrator to Drizzle directly, though it's not recommended.

Does this generate duplicate schemas for custom tables & relations passed in the beforeSchemaInit hook?

If you added them by changing schema - no. However, there's a different way now through mutating the "raw" Payload SQL schema in the beforeSchemaInit hook:
https://github.com/payloadcms/payload/pull/9953/files?plain=0#diff-583b55d282ab9145378592b5476dc40bfbbe46b3e0223b388886dee4fbfc518bR251-R290

You can mutate as well adapter.rawRelations to add your relations. Note that in beforeSchemaInit the Payload SQL schema exists, while Drizzle's doesn't (as before).

If the Payload schema is extended in afterSchemaInit, will the generated schema include these amends?
See above the note.

Also - one big thing I've noticed from using Drizzle introspection is that the output doesn't work well when entities have either self or circular relationships.
For example, tables with circular foreign key references such as the one below create an error on foreignKey definitions, resulting in an any type attributed to each table.

We don't use introspection anymore as I said, I included the workaround with : AnyPgColumn from https://orm.drizzle.team/docs/indexes-constraints#foreign-key with references to itself in to the generated output:
https://github.com/payloadcms/payload/pull/9953/files#diff-2583a68a595e56a58529bfea4c91c0c5bf168962e01b14fc51699927b5038dbdR78-R91
See example:
https://github.com/payloadcms/payload/pull/9953/files#diff-4d1376127d269fc05a0ece3ea2a7d5c56701311274ce5a0163ff45d25c38ef4bR204-R210
The generated table here is not any here.

@magicspon

Well this give us access to the eq function from drizzle?

Yes! You can import it from the adapter package directly:

import { eq, sql, and } from '@payloadcms/db-postgres/drizzle'

[github-actions]

🚀 This is included in version v3.10.0