Drizzle <> Nile
This guide assumes familiarity with:
根据 官方网站，Nile 是针对多租户应用重新设计的 PostgreSQL。
查看官方 Nile + Drizzle 快速入门 和 迁移 文档。
你可以将 Nile 与 Drizzle 的任何 Postgres 驱动程序结合使用，我们将在下面展示 node-postgres 的用法。
步骤 1 - 安装软件包
npm
yarn
pnpm
bun
npm i drizzle-orm postgres -D drizzle-kit步骤 2 - 初始化驱动程序并进行查询
// Make sure to install the 'pg' package
import { drizzle } from 'drizzle-orm/node-postgres'
const db = drizzle(process.env.NILEDB_URL);
const response = await db.select().from(...);如果你需要提供现有的驱动程序：
// Make sure to install the 'pg' package
import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
const db = drizzle({ client: pool });
const response = await db.select().from(...);连接到虚拟租户数据库
Nile 提供虚拟租户数据库。当你设置租户上下文时，Nile 会将你的查询定向到此特定租户的虚拟数据库，并且所有查询都将应用于该租户（即 select * from table 将仅生成此租户的记录）。
为了设置租户上下文，我们将每个查询封装在一个事务中，该事务在运行事务之前设置适当的租户上下文。
租户 ID 可以简单地作为参数传递给封装器：
import { drizzle } from 'drizzle-orm/node-postgres';
import { todosTable, tenants } from "./db/schema";
import { sql } from 'drizzle-orm';
import 'dotenv/config';
const db = drizzle(process.env.NILEDB_URL);
function tenantDB<T>(tenantId: string, cb: (tx: any) => T | Promise<T>): Promise<T> {
return db.transaction(async (tx) => {
if (tenantId) {
await tx.execute(sql`set local nile.tenant_id = '${sql.raw(tenantId)}'`);
}
return cb(tx);
}) as Promise<T>;
}
// In a webapp, you'll likely get it from the request path parameters or headers
const tenantId = '01943e56-16df-754f-a7b6-6234c368b400'
const response = await tenantDB(tenantId, async (tx) => {
// No need for a "where" clause here
return await tx.select().from(todosTable);
});
console.log(response);如果你正在使用支持它的 Web 框架，则可以设置 AsyncLocalStorage 并使用中间件将租户 ID 填充到其中。在这种情况下，你的 Drizzle 客户端设置将是：
import { drizzle } from 'drizzle-orm/node-postgres';
import dotenv from "dotenv/config";
import { sql } from "drizzle-orm";
import { AsyncLocalStorage } from "async_hooks";
export const db = drizzle(process.env.NILEDB_URL);
export const tenantContext = new AsyncLocalStorage<string | undefined>();
export function tenantDB<T>(cb: (tx: any) => T | Promise<T>): Promise<T> {
return db.transaction(async (tx) => {
const tenantId = tenantContext.getStore();
console.log("executing query with tenant: " + tenantId);
// if there's a tenant ID, set it in the transaction context
if (tenantId) {
await tx.execute(sql`set local nile.tenant_id = '${sql.raw(tenantId)}'`);
}
return cb(tx);
}) as Promise<T>;
}然后，配置一个中间件来填充 AsyncLocalStorage 并在处理请求时使用 tenantDB 方法：
// Middleware to set tenant context
app.use("/api/tenants/:tenantId/*", async (c, next) => {
const tenantId = c.req.param("tenantId");
console.log("setting context to tenant: " + tenantId);
return tenantContext.run(tenantId, () => next());
});
// Route handler
app.get("/api/tenants/:tenantId/todos", async (c) => {
const todos = await tenantDB(c, async (tx) => {
return await tx
.select({
id: todoSchema.id,
tenant_id: todoSchema.tenantId,
title: todoSchema.title,
estimate: todoSchema.estimate,
})
.from(todoSchema);
});
return c.json(todos);
});