Drizzle Team•9mo ago

Raw sql in schema

Hello! I'm trying to reference the supabase auth table as mentioned in the supabase docs (https://supabase.com/docs/guides/auth/managing-user-data):

create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,

  primary key (id)
);

create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,

  primary key (id)
);

Here's what I have:

export const users = pgTable('users', {
  id: sql`uuid not null references auth.users on delete cascade`,
});

export const users = pgTable('users', {
  id: sql`uuid not null references auth.users on delete cascade`,
});

But I guess I'm not doing it correctly as Ts complains:

Type 'SQL<unknown>' is not assignable to type 'PgColumnBuilderBase<ColumnBuilderBaseConfig<ColumnDataType, string>, object>'.
  Types of property '_' are incompatible.
    Type '{ brand: "SQL"; type: unknown; }' is missing the following properties from type '{ brand: "ColumnBuilder"; name: string; dataType: ColumnDataType; columnType: string; data: unknown; driverParam: unknown; notNull: boolean; hasDefault: boolean; enumValues: string[] | undefined; dialect: "pg"; }': name, dataType, columnType, data, and 5 more.ts(2322)

Type 'SQL<unknown>' is not assignable to type 'PgColumnBuilderBase<ColumnBuilderBaseConfig<ColumnDataType, string>, object>'.
  Types of property '_' are incompatible.
    Type '{ brand: "SQL"; type: unknown; }' is missing the following properties from type '{ brand: "ColumnBuilder"; name: string; dataType: ColumnDataType; columnType: string; data: unknown; driverParam: unknown; notNull: boolean; hasDefault: boolean; enumValues: string[] | undefined; dialect: "pg"; }': name, dataType, columnType, data, and 5 more.ts(2322)

I'm aware there are other "solutions" to this problem of referencing the auth table that involve editing the generated SQL afterwards, but would prefer if I could somehow define the raw sql in the schema itself Thanks in advance

2 Replies

Icemourne•9mo ago

why not make table how it should be id: uuid('id').notNull().references(() => auth.users, { onDelete: 'cascade'}) if you pass raw sql string it kind of defeats purpose of having drizzle in the first place

puffy_tugboatOP•9mo ago

how do i get a reference to auth though? this looks like one solution https://twitter.com/rphlmr/status/1740692744992092438, but the idea of using raw sql was to avoid having to edit the generated SQL afterwards

Gaming

Programming

Raw sql in schema