Support for multiple schemas in postgre

[digoburigo]

I'm trying to introspect a postgre database with multiple schemas and it's not generating all the tables in all schemas.

Is there a support for multiple schemas?

[AndriiSherman]

Not yet, we were not adding it until someone asks
Added to backlog
Thanks!

[erickreutz]

Would love to see this also!

[Yuval-Peled]

Adding a +1 to this. We organize tables by logical schema (e.g "user" schema, "billing" schema) so the introspection does not work for us at all

[nicolasey]

Would be great indeed for modular architecture.

I was thinking about discovering all schemas using a rule in config > schema variable.
Instead of direct link to one folder, setup would be for example ./src/modules/**/*.schema.ts or something similar.

But I do not know if this is good idea for drizzle-kit

[AndriiSherman]

I’m currently working on this feature!
do it’s next to be released in drizzle-kit

[remorses]

this is also required to use drizzle with supabase because the auth users table is created on the auth schema

[anthonyalayo]

Could we get an update on this?

[capaj]

I can hack this with renaming my schema to public for now, but it's not ideal having to do this hack to introspect the DB

[nobleach]

I'm not allowed to use the public schema. I've tried specifying a different one via the ?search_path= connection string query param... Doesn't look like it's implemented just yet.

[anthonyalayo]

Is it possible to find out the progress and how much work is remaining? I'm also blocked on multiple schema support.

[anthonyalayo]

@AndriiSherman could we get an update?

[AndriiSherman]

Sorry for not providing updates on this issue for so long. This is the last significant task in the drizzle-kit that requires extensive restructuring due to a few poor decisions regarding the multi-schema architecture. I had been postponing this in favor of implementing a set of different features that required less effort but were important to add.

I'm currently in the process of adding unique constraints, checks, and all fields for indexes to the drizzle kit. After that, I will complete the implementation for multi-schema support in introspect and also address an issue where you can't name a table the same in different schemas

And after that I will be ready to open-source drizzle-kit as well, so you can contribute anything you'd like or just check the implementation

[capaj]

Sounds lovely. Is there any time estimate you can give for open sourcing? Is it likely in Q4 2023 ?

[AndriiSherman]

I want to do it at the end of August, but it's always hard to estimate accurately, and I believe that a more appropriate estimate would be in Q4 2023, yes

[peterferguson]

Hey didn't want to create a new issue since this is still open but on using the multiple schema introspection there is a small error in the generation. The result looks like this

export const nextAuth = pgSchema("next_auth");
,export const realtime = pgSchema("realtime");
,export const supabaseFunctions = pgSchema("supabase_functions");
,export const cron = pgSchema("cron");
,export const vault = pgSchema("vault");
,export const extensions = pgSchema("extensions");

my config looks like

export default {
	schema: './schema/*',
	out: './migrations',
	driver: 'pg',
	dbCredentials: { connectionString: DB_URL },
	schemaFilter: [
		'public',
		'next_auth',
		'vault',
		'supabase_functions',
		'realtime',
		'extensions',
		'cron',
	],
} satisfies Config

[anthonyalayo]

@AndriiSherman could we get an update on how this is going along? We're in Q4 now, and multiple schema support is super needed!

[albertilagan]

@AndriiSherman could we get an update on how this is going along? We're in Q4 now, and multiple schema support is super needed!

https://discord.com/channels/1043890932593987624/1143797814959292456/1143836511968706622

[albertilagan]

@AndriiSherman could we get an update on how this is going along? We're in Q4 now, and multiple schema support is super needed!

Last I try this is what's supported.

[anthonyalayo]

Thanks for the find @albertilagan. Looks like there's been no update since this:

ivanfeliciano — 08/28/2023 6:01 PM
Is there any timeline for this issue fix?

But what we have so far can unblock me at least.

[anthonyalayo]

And after that I will be ready to open-source drizzle-kit as well, so you can contribute anything you'd like or just check the implementation

If the support is out there, why is drizzle kit still not open source? I see a lot of comments in #kit-discussion about a big desire for it to be open source so that bug fixes can get in.

[Hebilicious]

I wanted to implement a drizzle/kit workflow where on each feature branch, I would create a new pg schema base on the branchname, and run the migrations against that schema with kit.
Then with drizzle, I wanted to be able to switch schema on the fly.

I found a hacky way to do this, which is to manually patch the migrations folder and define my tables with something like this :

export const schemaTable = pgSchema(process.env.PG_SCHEMA);

Unfortunately that currently doesn't work due to the table names being duplicated accross schemas.
(Note that it would be technically possible to patch the table names as well, but I didn't go there)

Ideally I'd want to not have to define tables with pgSchema in drizzle, and simple being able to pass a defaultPgSearchPath configuration string to both drizzle-kit and drizzle-orm, so that this hacky workaround becomes un-necessary.

My naive thought process of an implementation would be to automatically inject

sql`SET SEARCH_PATH = "${config.defaultPgSchema}"` 

right after any operation is sent by drizzle, and to restrict this to a single schema at first.

It's not perfect but it would enable a lot. However there might be some things I'm missing.
Wdyt @AndriiSherman ?

PS:
While I'm writing this, I realise that there's another solution that leverages postgres users and default search path, since we can specify a user in the connection string, this might work. However for a branching workflow it's a little cumbersome to create a new user every time, and that still wouldn't work due to the issues with same table names across multiple schemas.