[BUG]: DrizzleQueries are failing because of missing schema name (Postgres)
Problem
Report hasn't been filed before. - [x] I have verified that the bug I'm about to report hasn't been filed before. What version of `drizzle-orm` are you using? 0.39.1 What version of `drizzle-kit` are you using? 0.30.4 Other packages _No response_ Describe the Bug We use pgSchema for our table definitions like: `export const user = pgSchema('app_one').table('user', { id: integer('id').generatedAlwaysAsIdentity().primaryKey(), name: text('name'), })` When making a query like `db.query.user.findMany()` the produced sql code looks like this `select "id", "name" from "user"` which fails because the schema is missing. It should be `select "id", "name" from "app_one"."user"` When using the query builder like `db.select().from(user)` the produced sql is correct `select "id", "name" from "app_one"."user"`
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix Missing Schema in DrizzleQueries for Postgres
The issue arises because the `db.query.user.findMany()` method does not include the schema name in the generated SQL query. This is likely due to the ORM's internal handling of schema definitions not being fully integrated with the query generation logic, leading to SQL statements that omit the schema context.
Awaiting Verification
Be the first to verify this fix
- 1
Update Query Method
Modify the query method to include the schema name when generating SQL for the `findMany` operation. This ensures that the correct schema is referenced in the SQL statement.
typescriptdb.query.user.findMany = function() { return this.from(pgSchema('app_one').table('user')).select(); } - 2
Test the Query
Run a test to ensure that the modified query method now generates the correct SQL statement with the schema name included. This can be done by logging the SQL output.
typescriptconsole.log(db.query.user.findMany()); - 3
Update Documentation
Ensure that any relevant documentation is updated to reflect this change in the query method, so future developers are aware of the schema requirement when using `findMany`.
- 4
Review Other Query Methods
Check other query methods in the ORM to ensure they also handle schema names correctly. This will help prevent similar issues in the future.
Validation
Confirm the fix by running the modified query and checking that the SQL output now includes the schema name, e.g., `select "id", "name" from "app_one"."user"`. Additionally, verify that other queries also function correctly with schema names.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep