[FEATURE]: Dynamic Schema

[d4mr]

Describe what you want

It doesn't seem possible to use pgSchema or related with dynamic runtime values. (https://orm.drizzle.team/docs/schemas) in #423, proposed solutions use a table factory to achieve this, and while this works with the query builder (with code duplication and bad DX), this is not viable for use with relational queries or drizzle-kit.

It is very common to use schemas for separating tables of different tenants in multitenant applications. The easiest way to support this would probably be with an addition to the drizzle() function, which could take an optional schema argument. The schema would apply to all the tables defined on the drizzle db instance.

Similarly, optional config with drizzle kit to specify a schema would solve the problem.

[d4mr]

I have already tried defining a drizzle schema factory instead of using a static drizzle schema, that takes a pgSchema as an input and returns the correct drizzle schema.

The problem here is with defining relations, and circular reference issues:

export const getAccreditationDocumentsTable = (schema: string) => {
  const { userDocuments } = getUserDocumentsTable(schema);
  const { accreditations } = getAccreditationsTable(schema);

  const accreditationDocuments = pgSchema(schema).table(
    'accreditation_documents',
    {
      accreditationId: varchar('accreditation_id', { length: 128 })
        .primaryKey()
        .references(() => accreditations.id),
      document_hash: text('document_hash'),
    }
  );

  const accreditationDocumentsRelations = relations(
    accreditationDocuments,
    ({ one }) => ({
      accreditation: one(accreditations, {
        fields: [accreditationDocuments.accreditationId],
        references: [accreditations.id],
      }),
    }),
  );

  return { accreditationDocuments, accreditationDocumentsRelations };
};

export const getAccreditationsTable = (schema: string) => {
  const { accreditationDocuments } = getAccreditationDocumentsTable(schema);

  const accreditations = pgSchema(schema).table('accreditations', {
    id: varchar('id', { length: 128 })
      .primaryKey()
      .$defaultFn(() => created()),
    // ....
  });

  const accreditationsRelations = relations(
    accreditations,
    ({ many }) => ({
      accreditationDocuments: many(accreditationDocuments),
    }),
  );

  return { accreditations, accreditationsRelations };
};

This causes TS Error: implicitly has return type 'any' because it does not have a return type annotation and is referenced directly or indirectly in one of its return expressions

[ninjrdevelop]

I've run into that TS error a few times with circular references, and it was resolved (in my PostgreSQL case) by adjusting this line:

.references(() => accreditations.id), -> .references((): AnyPgColumn => accreditations.id),

The Any[]Column object might be different if you aren't Postgres.

[givenloyiso]

I am developing a multi-tenant schema where dynamically switching the tenant schema is a critical aspect. Unfortunately, this feature is currently unavailable in Drizzle, which has led me to consider migrating to Kysely.

Kysely provides this feature with the following syntax: db.withSchema(tenant).selectFrom('user').selectAll();

Edit: Months later, there's still no progress on this feature in Drizzle. Although the team is aware, it seems other priorities have taken over. I've since migrated to Kysely. While the move required some effort, it now allows me to run complex queries that other ORMs can't handle. I wish drizzle had this from the start, because I liked the simplicity and lightweight.

[RRikor]

As a workaround this works for us for now

export const customerTable = {
  name: 'customers',
  columns: {
    id: serial('id').primaryKey(),
    name: varchar('name').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true })
      .defaultNow()
      .notNull(),
    active: boolean('active').default(true).notNull(),
  },
  extraConfig: (table: any) => ({
    customerNameIdx: index('customer_name_idx').on(table.name),
  }),
}

export class DBModel {
  dbSchema: string

  constructor(dbSchema: string, withLogs = true) {
    this.dbSchema = dbSchema
    if (withLogs) {
      console.log(`Initialized DB schema ${this.dbSchema}`)
    }
  }

  get customers() {
    return createTableWithSchema(
      this.dbSchema,
      customerTable.name,
      customerTable.columns,
      customerTable.extraConfig,
    )
  }
}

export function createTableWithSchema<
  TSchemaName extends string,
  TTableName extends string,
  TColumnsMap extends Record<string, PgColumnBuilderBase>,
>(
  schemaName: TSchemaName,
  tableName: TTableName,
  tableColumns: TColumnsMap,
  extraConfig: ((self: BuildColumns<TTableName, TColumnsMap, 'pg'>) => PgTableExtraConfig) | undefined
) {
  return pgSchema(schemaName).table(
    tableName,
    tableColumns,
    extraConfig
  )
}

[RRikor]

Also created this PR half a year ago to make the schema dynamically changeable at runtime within the library. I guess the subject lacks interest :-) https://github.com/drizzle-team/drizzle-orm/pull/1266

[ivosabev]

Currently the lack of this feature prevents me form using Drizzle in my projects.

[mkuchak]

Has this been neglected so far? I'm having trouble with Prisma handling this and I had Drizzle as my main alternative (actually to become primary). It seems that this important feature is not getting enough attention. Let's stick with Postgres.js for now.