Drizzle <> Nile
- Database connection basics with Drizzle
- Nile Database - website
- Drizzle PostgreSQL drivers - docs
According to the official website, Nile is PostgreSQL re-engineered for multi-tenant apps.
Checkout official Nile + Drizzle Quickstart and Migration docs.
You can use Nile with any of Drizzle’s Postgres drivers, we’ll be showing the use of node-postgres below.
Step 1 - Install packages
npm i drizzle-orm postgres
npm i -D drizzle-kitStep 2 - Initialize the driver and make a query
// 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(...);If you need to provide your existing driver:
// 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(...);Connecting to a virtual tenant database
Nile provides virtual tenant databases, when you set the tenant context, Nile will direct your queries to the virtual database for this particular tenant and all queries will apply to that tenant (i.e. select * from table will result records only for this tenant).
In order to set the tenant context, we wrap each query in a transaction that sets the appropriate tenant context before running the transaction.
The tenant ID can simply be passed into the wrapper as an argument:
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);If you are using a web framwork that supports it, you can set up AsyncLocalStorage and use middleware to populate it with the tenant ID. In this case, your Drizzle client setup will be:
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>;
}And then, configure a middleware to populate the the AsyncLocalStorage and use tenantDB method when handling requests:
// 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);
});