Use supabase auth.uid() expression as default value in a uuid field

teneburulast year

12

  db: postgresAdapter({
    afterSchemaInit: [
      ({ schema, extendTable, adapter }) => {
        extendTable({
          table: schema.tables.clients,
          columns: {
            uid: uuid.default(sql`auth.uid()`),
          }
        })
        return schema
      },
    ],

    beforeSchemaInit: [
      ({ schema, adapter }) => {
        adapter.rawTables.clients = {
          ...adapter.rawTables.clients,
        }
        adapter.rawTables.clients.columns = {
          ...adapter.rawTables.clients.columns,
          uid: {
            name: 'uid',
            type: 'uuid',
            default: { sql: 'auth.uid()' },
          }
        }
        adapter.rawTables.clients.indexes = {
          ...adapter.rawTables.clients.indexes,
          uid_idx: {
            name: 'uid_idx',
            unique: true,
            on: ['uid'],
          }
        }

    afterSchemaInit: [
      ({ schema, adapter, extendTable }) => {
        extendTable({
          table: schema.tables.clients,
          columns: {
            uid: {
              name: 'uid',
              type: 'uuid',
              default: { sql: 'auth.uid()' },
            }
          },
          extraConfig: (table) => {
            uid_idx: index('uid_idx').on(table.uid)
          }
        })

 ⨯ TypeError: columnBuilder.build is not a function
    at extendTable (app/(payload)/payload.config.js:115:8)
  113 |     afterSchemaInit: [
  114 |       ({ schema, adapter, extendTable }) => {
> 115 |         extendTable({
      |        ^
  116 |           table: schema.tables.clients,
  117 |           columns: {
  118 |             uid: { {
  digest: '3986122377'

• 

  ritsu0455last year

  Hey, this should work for you:

  
  

      beforeSchemaInit: [
        ({ schema, adapter }) => {
           schema.rawTabes.clients.columns.uid = { name: 'uid', type: 'uuid', defaultRandom: true }
           return schema;
  }]
         

  
  
  

  then you can add an index with

  schema.rawTables.clients.indexes.uid_idx = {...}
• 

  teneburulast year

  first, thanks for coming to the rescue !

  
  

  🤔 i thought I had to use the expression made available by supabase in order for it to be tied to the auth schema and work with Supabase Auth

  
  
  

  the auth.uid thing

• 

  ritsu0455last year

  This just generates a random UUID via the db, I don't know about supabase but I don't think we have some integration with it

  
  
  auth.uid()

  is it specific to supabase?

• 

  teneburulast year

  yes, its specific to it. I don't know much about it but I know that its just a random uuid with some functionnality added to it

  
  
  

  with prisma i used to have

  uid String? @default(dbgenerated("auth.uid()")) @db.Uuid
• 

  ritsu0455last year

  then

  rawTables

  don't expose that API, you need to use

  extendTable

  with: (in

  afterSchemaInit
  

  uid:  uuid("uid").default(sql`auth.uid()`)

  
  
  

  so you can add that directly via drizzle

• 

  teneburulast year

  i did try that, thats the first thing i tried it gave an error because it didnt have uuid wait trying it again

• 

  ritsu0455last year

  import { uuid, index } from '@payloadcms/db-postgres/drizzle/pg-core'
    db: postgresAdapter({
      afterSchemaInit: [
        ({ schema, extendTable, adapter }) => {
          extendTable({
            table: schema.tables.clients
  ,
            columns: {
              uid:  uuid("uid").default(sql`auth.uid()`),
            },
            extraConfig: (table) => ({
              uid_idx: index('uid_idx').on(
                table.uid,
              ),
            }),
          })
  
          return schema
        },
      ],
    }),

• 

  teneburulast year

  ohhh the import !

  
  
  

  fuck i am bad

  
  
  

  why dont i think when i see Error: uuid is not defined

  
  
  

  thank you so so much

  
  
  

  i assume i need

  import { sql } from 'drizzle-orm'
  

  i'm getting this error

  ⨯ [error: schema "auth" does not exist]
• 

  ritsu0455last year

  don't need from

  drizzle-orm

  , just

  import { sql } @payloadcms/db-postgres/drizzle

  ,

• 

  teneburulast year

  okay

  
  
  

  auth is a schema created by supabase auth btw

• 

  ritsu0455last year

  but you're using the public schema?

  
  
  

  for clients

• 

  teneburulast year

  yes i am, and the whole point of that db generator is to not have to deal with the auth schema

  
  
  

  to be fair i'm trying to migrate something i implemented long ago but i feel like i can probably solve it now

  
  
  

  oh i forgot i'm still on a local postgres database for debugging it will probably be fixed as soon as my drizzle orm is connected to supabase

  
  
  

  i'll probably be fine now thank you very much for your help!! 🤍