DrizzleORM多租户支持方案

2026年8月22日

背景

drizzle是一个轻量级、全功能的typescript ORM，提供了类似SQL语法的使用方式。在开发SaaS应用时，通过Postgres Schema将不同的租户业务数据进行隔离是比较普遍的选择。

drizzle本身支持给表指定schema，但是只能在定义时指定，无法在运行时根据租户身份进行切换：

import { integer, pgSchema } from "drizzle-orm/pg-core";

export const tenantSchema = pgSchema('tenant_xxxx');
export const customerTable = tenantSchema.table('customer', {
  id: integer(),
  // ...
})
// 查询时固定死了只能从 tenant_xxxx schema 查询
db.select().from(customerTable)

从官方在 GitHub Issues 上回复的内容看，貌似并没有短期内支持动态schema的打算，所以需要一种在运行时动态指定shcema的方案

Schema 分类

一般认为需要三类 schema：

admin，用于管理后台，例如租户表、用户表等
public，用于租户之间共享数据，例如各类字典
tenant_xxx，只属于xxx租户，存储该租户业务数据

动态构建

用于drizzle-kit进行数据库迁移的 schema 和查询时使用的 schema 除了数据库 schema 外其他部分都是相同的，所以我们可以使用工具函数创建 schema

import {
  AnyPgColumnBuilder,
  PgBuildExtraConfigColumns,
  PgTableExtraConfigValue,
  snakeCase,
} from 'drizzle-orm/pg-core';

export type TableParams<
  TTableName extends string = string,
  TColumnsMap extends Record<string, AnyPgColumnBuilder> = Record<
    string,
    AnyPgColumnBuilder
  >,
> = {
  name: TTableName;
  columns: TColumnsMap;
  extra?: (
    self: PgBuildExtraConfigColumns<TColumnsMap>,
  ) => PgTableExtraConfigValue[];
};

export function createSchemaTable<
  TSchemaName extends string,
  TTableName extends string,
  TColumnsMap extends Record<string, AnyPgColumnBuilder>,
>(schema: TSchemaName, table: TableParams<TTableName, TColumnsMap>) {
  return snakeCase.schema(schema).table(table.name, table.columns, table.extra);
}

export function createTable<
  TTableName extends string,
  TColumnsMap extends Record<string, AnyPgColumnBuilder>,
>(table: TableParams<TTableName, TColumnsMap>) {
  return snakeCase.table(table.name, table.columns, table.extra);
}

使用

drizzle-kit

drizzle-kit根据配置文件指定的 schema 文件将表结构同步到数据库，schema 文件示例如下：

Admin Schema

import { AnyPgColumnBuilder, integer, varchar } from 'drizzle-orm/pg-core';
import { createSchemaTable, createTable, TableParams } from './db.utils';

export function withAdminSchema<TTableName extends string = string, TColumnsMap extends Record<string, AnyPgColumnBuilder> = Record<string, AnyPgColumnBuilder>>(params: TableParams<TTableName, TColumnsMap>) {
  return createSchemaTable('admin', params);
}

// 可以用 withAdminSchema 从该参数构建schema
export const tenantTableParams = {
  name: 'core_tenant',
  columns: {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: varchar({ length: 255 }).notNull(),
    code: varchar({ length: 255 }).notNull(),
    logo: varchar({ length: 255 }),
  },
} satisfies TableParams;
// drizzle-kit 会读取该导出对象进行数据库同步
export const tenantTable = createTable(tenantTableParams);

export const userTableParams = {
  name: 'core_user',
  columns: {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    username: varchar({ length: 255 }).notNull(),
    email: varchar({ length: 255 }),
  },
} satisfies TableParams;
export const userTable = createTable(userTableParams);

Public Schema

import { AnyPgColumnBuilder, integer, varchar } from "drizzle-orm/pg-core";
import { createSchemaTable, createTable, TableParams } from "./db.utils";

export function withPublicSchema<TTableName extends string = string, TColumnsMap extends Record<string, AnyPgColumnBuilder> = Record<string, AnyPgColumnBuilder>>(params: TableParams<TTableName, TColumnsMap>) {
  return createSchemaTable('public', params)
}

export const industryTableParams = {
  name: 'industry',
  columns: {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: varchar({ length: 255 }).notNull(),
    code: varchar({ length: 255 }).notNull(),
  }
} satisfies TableParams
export const industryTable = createTable(industryTableParams);

Tenant Schema

import { AnyPgColumnBuilder, integer, varchar } from "drizzle-orm/pg-core";
import { createSchemaTable, createTable, TableParams } from "./db.utils";

export function withTenantSchema<TTenantName extends string = string,TTableName extends string = string, TColumnsMap extends Record<string, AnyPgColumnBuilder> = Record<string, AnyPgColumnBuilder>>(tenant: TTenantName, params: TableParams<TTableName, TColumnsMap>) {
  return createSchemaTable(`tenant_${tenant}`, params)
}

export const customerTableParams = {
  name: 'customer',
  columns: {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
  companyName: varchar({ length: 255 }).notNull(),
  }
} satisfies TableParams
export const customerTable = createTable(customerTableParams)

export const orderTableParams = {
  name: 'order',
  columns: {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    serialNumber: varchar({ length: 255 }).notNull(),
  }
} satisfies TableParams
export const orderTable = createTable(orderTableParams)

动态查询

在从数据库插叙时可使用如下形式：

import { drizzle } from 'drizzle-orm/bun-sql';
import { tenantTableParams, withAdminSchema } from './db/admin.schema';
import { customerTableParams, withTenantSchema } from './db/tenant.schema';

const db = drizzle(import.meta.env.DATABASE_URL)
const tenants = await db.select().from(withAdminSchema(tenantTableParams));
// 这会只从指定的租户 schema 中查询客户信息
const customers = await db.select().from(withTenantSchema('xxxx', customerTableParams));

迁移

drizzle-kit支持业务表结构变更后同步到数据库，为了适配以上多租户方案，需要调整迁移脚本

drizzle.config.ts

原来的drizzle.config.ts文件不足以支撑多租户方案，所以分成三个配置文件，分别对应三类schema。可以在drizzle/config目录下创建以下三个配置文件：

admin.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle/migration/admin',
  schema: './src/db/admin.schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: import.meta.env.DATABASE_URL,
  },
});

public.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle/migration/public',
  schema: './src/db/public.schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: import.meta.env.DATABASE_URL,
  },
});

tenant.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle/migration/tenant',
  schema: './src/db/tenant.schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: import.meta.env.DATABASE_URL,
  },
});

drizzle-kit会根据这三个配置文件在drizzle/migration/<admin|public|tenant>目录下形成各自的迁移脚本

迁移逻辑

核心迁移逻辑放在了drizzle/migration.ts:

import { migrate } from 'drizzle-orm/bun-sql/migrator';
import { drizzle } from 'drizzle-orm/bun-sql/postgres';
import { tenantTableParams } from '../src/db/admin.schema';
import { createSchemaTable } from '../src/db/db.utils';

async function migration(schema: string) {
  const db = drizzle(import.meta.env.DATABASE_URL);
  if (schema === 'tenant') {
    const tenants = await db.select().from(createSchemaTable('admin', tenantTableParams));
    if (tenants.length > 0) {
      await Promise.all(tenants.map((t) => migration(`tenant_${t.code}`)));
    }
    console.log(`🚀 ${tenants.length} tenants migrated successfully!`);
    return;
  }
  await db.execute(`CREATE SCHEMA IF NOT EXISTS "${schema}";`);
  await db.execute(`SET search_path TO ${schema};`);
  await migrate(db, {
    migrationsFolder: `drizzle/migration/${schema.split('_')[0]}`,
    migrationsSchema: schema,
    migrationsTable: 'drizzle_migration',
  });
  console.log(`✅ ${schema} migrated successfully!`);
}

migration(process.argv[2]).catch((err) => {
  console.error('migration fail:', err);
  process.exit(1);
});

迁移脚本

{
  "name": "drizzle-multi-tenant-demo",
  "scripts": {
    "db:generate:admin": "drizzle-kit generate --config=drizzle/config/admin.config.ts",
    "db:generate:public": "drizzle-kit generate --config=drizzle/config/public.config.ts",
    "db:generate:tenant": "drizzle-kit generate --config=drizzle/config/tenant.config.ts",
    // 生成迁移所需的 SQL 脚本。分开是为了在开发阶段完成迁移 SQL 固定，避免 CI 环境中需要手动选择
    "db:generate": "bun db:generate:admin && bun db:generate:public && bun db:generate:tenant",
    "db:migrate:admin": "bun run drizzle/migration.ts admin",
    "db:migrate:public": "bun run drizzle/migration.ts public",
    "db:migrate:tenant": "bun run drizzle/migration.ts tenant",
    // 将 SQL 迁移脚本同步到各schema
    "db:migrate": "bun db:migrate:admin && bun db:migrate:public && bun db:migrate:tenant",
    // 一个脚本完成迁移
    "db:push": "bun db:generate && bun db:migrate"
  },
  "dependencies": {
    "drizzle-orm": "^1.0.0-rc.5"
  },
  "devDependencies": {
    "@biomejs/biome": "2.5.8",
    "@types/bun": "latest",
    "drizzle-kit": "^1.0.0-rc.5"
  }
}