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

Perhaps it would be beneficial to add the support for the second argument in the from function, like so:

from<TFrom, TName>(source: TFrom, schemaName?: TName)

This modification would also enable the utilization of the placeholder feature in the following manner:

from(users, sql.placeholder("dynamic_schema_name"))

It seems that this aligns with the SQL approach that Drizzle aims to adhere to, as per the documentation. What are your thoughts on this suggestion?

@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?

I am currently employing the following workaround:

import { Table } from 'drizzle-orm'

const useDynamicSchema = <T extends Table>(table: T, schema: string): T => {
  // @ts-expect-error Symbol is @internal in drizzle-orm, see https://github.com/drizzle-team/drizzle-orm/blob/0.30.4/drizzle-orm/src/table.ts#L64-L65
  table[Table.Symbol.Schema] = schema
  return table
}

I find this approach to be more DX-friendly and memory-efficient, especially in the case of a large number of identical schemas, compared to the use of pgSchema:

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
})

await db.select({ id: users.id }).from(useDynamicSchema(users, 'dynamic_schema_name'))

The only caveat is that useDynamicSchema must always be used for the same table once it has been applied.

Additionally, this method facilitates easy migration to an official solution, once supported, by simply replacing useDynamicSchema\(([^)]+)\) with $1. For example:

await db.select({ id: users.id }).from(users, 'dynamic_schema_name')

Got it. It’s kindof annoying with the code duplication. It would be nice to just be able to attach the user table directly to the schema factory that way we could just define the table once instead of defining the columns and a schemaless table, then attach the table in the factory. Something like this:

export const user = pgTable(
  'user',
   {
  id: text('id')
    .$defaultFn(() => createId())
    .primaryKey(),
 emailAddress: text('email_address').notNull(),
  ...
},
  // (user) => {
  //   return {
  //     emailIdx: uniqueIndex('email_idx').on(user.emailAddress),
  //   }
  // }
)

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

@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.