Expose drizzle types to allow for custom, typesafe SQL queries [3.0]

[Niklas-Mezynski]

Idea for Payload 3.0

Sometimes I run in situations where I am required to run more complex SQL queries to satisfy my needs. I already figured out that I can use payloads drizzle objects to access the required tables and entities.

I figured out that code like this is already working:

const payload = await getPayloadHMR({ config: configPromise });

const rankingItems = payload.db.tables.ranking_items;
      //^? const rankingItems: any

const someItems = await payload.db.drizzle
      //^? const someItems: {
      //     id: any;
      //     title: any;
      // }[]
  .select({
    id: rankingItems.id,
    title: rankingItems.title,
  })
  .from(rankingItems)
  .execute();

"payload": "3.0.0-beta.71"

But there are no proper types for the drizzle tables and columns. If it is somehow possible to expose these from payload it would make writing such code much more robust while making payloads querying much more flexible.

Let me know about your thoughts!

(Somewhat realted to #6503)

[jancbeck]

drizzle kit can introspect types from db
https://orm.drizzle.team/kit-docs/overview#introspecting-with-db-pull

[siimsams]

I also need to have this for Postgis queries. Using the same database with other services is also a chore as the types are not published. This means I can't create a single Drizzle schema and use it in other projects.

EDIT: I can use the introspect but it probably has some downsides to it.

[siimsams]

Hey!

I was really struggling with this and then decided to look again if this is possible. Turns out this is solved and types can be generated using the following command: pnpm payload generate:db-schema

I found this closed discussion and this PR which implemented it.

I suggest we close this issue as it's solved.

[Niklas-Mezynski]

Thanks for linking that! I will close the discussion