[BUG]: design: working with dynamic schemas is practically impossible

[mppub]

What version of drizzle-orm are you using?

0.23.2

Describe the Bug

This is not might be re-labeled as and design/architecture flaw and let me know if I get it wrong but: Imagine the typical multi-tenant app having a separate schema for each customer account - in this case drizzleorm is practically impossible to use since the whole API design revolves around directly including the schema TS code that is static by definition.

I would imagine something like this:

// schema definition
const accountSchema = pgDynamicSchema('account'); // instead of pgSchema, where the string param is just an optional prefix

export const users = accountSchema.table("users", {
//...
}

// some rest endpoint
const currentUserSchema = 'customer_schema_name'
db.select().from(users(currentUserSchema)) // this would then access "account_customer_schema_name" schema

But I understand this might mean to rewrite the whole thing..

[AndriiSherman]

@mppub https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/README.md#customizing-the-table-name

[AndriiSherman]

If link don’t work, try to search for “ Customizing the table name” in Postgres docs

[mppub]

@mppub https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/README.md#customizing-the-table-name

Thanks I looked at that earlier but that is not what I need + you even write there:

Note:: this feature should only be used to customize the table name. If you need to put the table into a different schema, refer to the Table schemas section.

That is intened for predefined values (eg. environements) that you will have in the code not for dynamically generated ones (eg. I have 1000 customers that would mean I would need 1000 user tables representations in the code).

Technically I could do a factory like this:

const getUsersTable = (userId: string) => {
  const pgTable = pgTableCreator((name) => `${userId}_${name}`);
  
  return pgTable('users', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
  });
}

db.select().from(getUsersTable(currentUserId))

// for migrations - I would just need to somehow replicate the generic into every other customer schema
export const genericUsersTable = getUsersTable('generic')

That could work, even with the pgSchema- but it is just seems so excesive - Imagine - everytime user creates an request that access some table you would need to call this factory and create the table representation instead of just specify the schema in a "query". Whats your opinion?

[dankochetov]

@mppub I'm a bit confused. Initially you said that you need to put the tables into different schemas for different customers, but the solution you came up with in the last message uses a dynamic prefix instead of a dynamic schema. If you need the dynamic schema, you can create a factory like this:

function getUsersTable<TSchema extends string>(schemaName: TSchema) {
	return pgSchema(schemaName).table('users', {
		id: integer('id').primaryKey(),
		name: text('name').notNull(),
	});
}

I've tested this locally, and it turned out current types are a bit misconfigured for this function to work, so I've pushed a fix to drizzle-orm@beta. You can update and try it.

Regarding the performance - I doubt there will be any noticeable issues using this method. You can test the performance by comparing the response times between using a table with a static schema and using a table factory.

[mppub]

Perfect! I will try the beta today, yes the example was just an illustration of the method for both schema and table prefix..

[maximilianmaihoefner]

@dankochetov We are using the function you suggested to dynamically create the schema and it works great for querying. We are however unsure how we would use drizzle-kit to generate sql migrations for the schema if it is defined within the function. Executing drizzle-kit generate:pg tells us it didn't find any Tables and that there is nothing to migrate. We need to have the dynamic schema at runtime because we have n-Tenants that we do not know at compile time, do you have a suggestion on how we could generate the Migrations?

[aflatoon2874]

@mppub I'm a bit confused. Initially you said that you need to put the tables into different schemas for different customers, but the solution you came up with in the last message uses a dynamic prefix instead of a dynamic schema. If you need the dynamic schema, you can create a factory like this:

function getUsersTable<TSchema extends string>(schemaName: TSchema) {
	return pgSchema(schemaName).table('users', {
		id: integer('id').primaryKey(),
		name: text('name').notNull(),
	});
}

I've tested this locally, and it turned out current types are a bit misconfigured for this function to work, so I've pushed a fix to drizzle-orm@beta. You can update and try it.

Regarding the performance - I doubt there will be any noticeable issues using this method. You can test the performance by comparing the response times between using a table with a static schema and using a table factory.

How to reference a table created via factory method in another table created via the same way in the refrences() function to define a foreign key.

[rodolfofranco]

How would this work If I need to define my tables as 1.Users,2.Users....N.Users??

Using export const genericUsersTable = getUsersTable('generic') as shown above would not work for me because my prefixes are not static such as 'generic'.

Is there any way to accomplish this?

[aflatoon2874]

How would this work If I need to define my tables as 1.Users,2.Users....N.Users??

Using export const genericUsersTable = getUsersTable('generic') as shown above would not work for me because my prefixes are not static such as 'generic'.

Is there any way to accomplish this?

Instead of passing 'generic' as parameter, you would pass the actual schema name at runtime based on some criteria as per your use case.

[rodolfofranco]

How would this work If I need to define my tables as 1.Users,2.Users....N.Users??
Using export const genericUsersTable = getUsersTable('generic') as shown above would not work for me because my prefixes are not static such as 'generic'.
Is there any way to accomplish this?

Instead of passing 'generic' as parameter, you would pass the actual schema name at runtime based on some criteria as per your use case.

If the only way is at runtime, how can I use drizzle-kit to generate my tables? Sorry if I seem confused.

[aflatoon2874]

How would this work If I need to define my tables as 1.Users,2.Users....N.Users??
Using export const genericUsersTable = getUsersTable('generic') as shown above would not work for me because my prefixes are not static such as 'generic'.
Is there any way to accomplish this?

Instead of passing 'generic' as parameter, you would pass the actual schema name at runtime based on some criteria as per your use case.

If the only way is at runtime, how can I use drizzle-kit to generate my tables? Sorry if I seem confused.

Good question. Although the docs do not talk of this kind of usage, but there is a way to handle this which I devised, may not be the best way but does the job.

1. Define your schema as given below:

const userColumns = {
  id: text('id')
    .$defaultFn(() => createId())
    .primaryKey(),
 emailAddress: text('email_address').notNull(),
  ...
}

export const user = pgTable(
  'user',
  userColumns,
  // (user) => {
  //   return {
  //     emailIdx: uniqueIndex('email_idx').on(user.emailAddress),
  //   }
  // }
)

export const getUserTable = <TSchema extends string>(
  schemaName: TSchema
) => {
  return pgSchema(schemaName).table(
    'user',
    userColumns
  )
}

2. In this definition, user will satisfy the drizzle-kit generate command as well as the type definitions.
3. In your application you will use getUserTable() with appropriate schema name to target the correct tenant at runtime.
4. This way you would not be able to use migrate() to apply any schema changes to the database after generating the sql script via generate command because the migrate() does not know the schema name so it will always target the public db schema. I have raised an issue/enhancement request [FEATURE]: Allow to specify a particular schema name in the migrate() config for postgres #908 on Github for migrate() to handle passing a db schema name so that desired db schema can be targetted. Until then, you need to run the generated script yourself against all the db schemas one by one using external sql client tool.

Hope this helps :)