Drizzle ORM + Postgres, how to specify Postgres database Schema? defaults to public

I tried postgres://localhost:5432/mydatabase?searchpath=myschema

Drizzle uses node-postgres, which in turn depends on pg-connection-string that only accepts a limited list of connection-specific parameters using that ?key=value syntax, searchpath not being one of them.

To clarify, you're not connecting to any schema, only a database with schemas in it. Presence and order of their names in your searchpath dictates which ones your queries will interact with whenever they don't use schema-qualified references (select c1 from table1 as opposed to select c1 from schema1.table1).

1. By default, public is second to $user schema in searchpath. Create a schema named after your user and it'll automatically default to that instead of public. You can also rename myschema to myuser, or myuser to myschema, same effect. _{demo at dbfiddle}
2. You can change it for everyone

   alter database mydatabase set searchpath=myschema;
   

   Or just your service account:

   alter role youruser set searchpath=myschema;
   

   Note that it doesn't affect ongoing sessions (need to reconnect to apply new defaults) and they can still override that from inside their sessions with a regular set. Recent related thread.
   If you're looking to make this a parameter for any user of your library to override, you could just match the role/user/service account name to their schema name of choice or vice versa.
3. If you don't mind schema-qualifying all your references, drizzle offers pgSchema (as immediately pointed out by @authorizeduser):

   import { serial, text, pgSchema } from "drizzle-orm/pg-core";
   export const mySchema = pgSchema("myschema");
   export const colors = mySchema.enum('colors', ['red', 'green', 'blue']);
   export const mySchemaUsers = mySchema.table('users', {
     id: serial('id').primaryKey(),
     name: text('name'),
     color: colors('color').default('red'),
   });
   

4. I'm not sure if or how drizzle escapes and quotes identifiers, but if it just doesn't, it might be possible to try and inject it via pgTableCreator:

   import { pgTableCreator } from 'drizzle-orm/pg-core'; 
   const pgTable = pgTableCreator((name) => myschema.${name});
   

5. schemaFilter sounds like drizzle's search_path but the solution to this issue makes it sound like that only works in tandem with pgSchema.