[BUG]: Relations inferring incorrect table with non-default Postgres schema

[chrisjh]

What version of drizzle-orm are you using?

0.27.0

What version of drizzle-kit are you using?

0.19.2

Describe the Bug

I use the default Postgres Public schema with a Users table ("public.users") and an Auth schema with a Users table from Supabase ("auth.users").

Users can have many AuthUsers (aka multiple authentication methods to log in). This is represented via a join table Identities as I can't write to Supabase's auth.users table.

I've defined them as follows in the Drizzle schema:

// schema.ts

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  // rest omitted...
});

export const authSchema = pgSchema("auth");

export const auth_users = authSchema.table("users", {
  id: uuid("id").primaryKey(),
 // rest omitted...
});

export const identities = pgTable("identities", {
  user_id: text("user_id").references(() => users.id).notNull(),
  auth_uid: uuid("auth_uid").references(() => auth_users.id).notNull(),
  // rest omitted...
}, (t) => {
  return {
    pk: primaryKey(t.user_id, t.auth_uid),
  };
});

// relationships

export const identitiesRelations = relations(identities, ({ one }) => ({
  user: one(users, {
    fields: [identities.user_id],
    references: [users.id],
  }),
  auth_user: one(auth_users, {
    fields: [identities.auth_uid],
    references: [auth_users.id],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  identities: many(identities),
}));

What I've found is that the types aren't being inferred correctly for the identity <> auth_user relation as seen here:

Where auth_user: One<"users", true> is inferring "users" instead of the "auth_users" table from the auth schema.

Running a relational query like this:

 const data = await db.query.users.findMany({
    with: {
      identities: {
        with: {
          auth_user: true,
        },
      },
    },
  });

Will result is an error:

Unhandled Runtime Error
Error: operator does not exist: uuid = text

Which is a Postgres level error – it seems this is because instead of comparing identies.auth_uid (uuid) and auth_users.id (uuid) it's comparing it to users.id (text)

Thanks for taking a look!

Expected behavior

I would expect the correct postgres schema + table combination to be used when doing a relational query.

Environment & setup

All environments.

[productdevbook]

Do you have a solution for this?

[productdevbook]

PostgresError: relation "authorization.user" does not exist me bug

[kddige]

Similar issue when generating migrations with drizzle-kit -, the schema is never referenced in the SQL script's fk

[karthikjn01]

I'm running into the same issue 😞 it'd be nice to get some more info from the error too if possible ie. where is this happening and what's causing it (what tables/fields).

- error node_modules/postgres/src/connection.js (771:25) @ postgres
- error PostgresError: operator does not exist: text = uuid
    at Socket.emit (node:events:513:28)
digest: "1620123906"

is what I'm getting back

[bernatfortet]

+1 here