This example reads the tenant ID from the URL. Before using it in production, authenticate the caller and verify their membership in that tenant. A tenant ID supplied by the caller does not authorize access.
Drizzle with Nile Database
Build a tenant-aware Express API with the Drizzle ORM and Drizzle Kit v1 release candidate. Nile provides the PostgreSQL database and routes queries to a tenantβs virtual database using the tenant context set by your application.
You can also explore Nileβs Drizzle example.
- You should have installed Drizzle ORM and Drizzle kit. You can do this by running the following command:
npm i drizzle-orm@rc
npm i -D drizzle-kit@rc
- You should have installed
dotenvpackage for managing environment variables. Read more about this package here
npm i dotenv
- You should have installed
pgpackage for connecting to the Postgres database. Read more about this package here
npm i pg
npm i -D @types/pg
- You should have installed
expresspackage for the web framework. This example uses Express 4 routing syntax. Read more about Express here
npm i express@4
npm i -D @types/express@4
npm i -D tsx
npm i -D typescript
npm i -D @types/node
- This guide uses AsyncLocalStorage to manage the tenant context. If your framework or runtime does not support
AsyncLocalStorage, you can refer to Drizzle<>Nile doc for alternative options.
Setup Nile and Drizzle ORM
Signup to Nile and create a database
If you havenβt already, sign up to Nile and follow the app instructions to create a new database.
Get database connection string
On the left side-bar menu, select the βSettingsβ option, click on the Postgres logo, and click βgenerate credentialsβ. Copy the connection string and add it to the .env file in your project:
NILEDB_URL=postgres://youruser:yourpassword@us-west-2.db.thenile.dev:5432/your_db_nameConnect Drizzle ORM to your database
Create a db.ts file in the src/db directory and set up your database configuration:
import { drizzle } from 'drizzle-orm/node-postgres';
import "dotenv/config";
import { sql } from "drizzle-orm";
import { AsyncLocalStorage } from "node: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`select set_config('nile.tenant_id', ${tenantId}, true)`);
}
return cb(tx);
}) as Promise<T>;
}Setup Drizzle config file
Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.
Create a drizzle.config.ts file in the root of your project and add the following content:
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
out: './drizzle',
schema: './src/db/schema.ts',
dialect: 'postgresql',
dbCredentials: {
url: process.env.NILEDB_URL!,
},
});Introspect Nile database
Your new Nile database already contains built-in tables, including tenants, which you will use to create and list tenants. Run pull --init to generate their Drizzle definitions and record the existing schema as the applied baseline. This writes migration bookkeeping to your tutorial database, so subsequent migrations do not try to create the built-in tables again.
npx drizzle-kit pull --initIn v1, an initial pull writes drizzle/schema.ts, drizzle/relations.ts, and a timestamped folder containing migration.sql and snapshot.json. Keep that migration folder: later generations use its snapshot as the baseline.
Copy drizzle/schema.ts to src/db/schema.ts. Keep every table and declaration returned by introspection when adding your application tables below. This tutorial uses SQL queries and does not need the generated relations.ts file.
Here is an excerpt showing the tenants table. The exact defaults and other built-in tables depend on your Nile database. Keep the definitions returned by your own pull:
// table schema generated by introspection
import { pgTable, uuid, text, timestamp } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"
export const tenants = pgTable("tenants", {
id: uuid().default(sql`uuid_generate_v7()`).primaryKey(),
name: text(),
created: timestamp().default(sql`LOCALTIMESTAMP`).notNull(),
updated: timestamp().default(sql`LOCALTIMESTAMP`).notNull(),
deleted: timestamp(),
});Create additional tables
Add the todos table to src/db/schema.ts, preserving every introspected definition. The following excerpt shows tenants and the new application table:
// table schema generated by introspection
import { pgTable, uuid, text, timestamp, varchar, vector, boolean } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"
export const tenants = pgTable("tenants", {
id: uuid().default(sql`uuid_generate_v7()`).primaryKey(),
name: text(),
created: timestamp().default(sql`LOCALTIMESTAMP`).notNull(),
updated: timestamp().default(sql`LOCALTIMESTAMP`).notNull(),
deleted: timestamp(),
});
export const todos = pgTable("todos", {
id: uuid().defaultRandom(),
tenantId: uuid("tenant_id"),
title: varchar({ length: 256 }),
estimate: varchar({ length: 256 }),
embedding: vector({ dimensions: 3 }),
complete: boolean(),
});Apply changes to the database
Generate a migration for the new application table:
npx drizzle-kit generateReview the new migration.sql. It should create todos without dropping or recreating Nileβs built-in tables. Then apply it:
npx drizzle-kit migrateKeep both migration folders and their snapshots in version control.
Initialize the webapp
Create src/app.ts to initialize the Express application. The following snippets add middleware and routes to this same file:
import express from "express";
import { tenantDB, tenantContext } from "./db/db";
import {
tenants as tenantSchema,
todos as todoSchema,
} from "./db/schema";
import { eq } from "drizzle-orm";
const PORT = process.env.PORT || 3001;
const app = express();
app.listen(PORT, () => console.log(`Server is running on port ${PORT}`));
app.use(express.json());Initialize the tenant-aware middleware
Next, weβll add middleware to the example. This middleware grabs the tenant ID from the path parameters and stores it in the AsyncLocalStorage.
The tenantDB wrapper that we created in src/db/db.ts uses this tenant ID to set nile.tenant_id when executing queries,
which then guarantees that the queries will execute against this tenantβs virtual database.
// set the tenant ID in the context based on the URL parameter
app.use('/api/tenants/:tenantId/*', (req, res, next) => {
const tenantId = req.params.tenantId;
console.log("setting context to tenant: " + tenantId);
tenantContext.run(tenantId, next);
});Add routes
Add routes for creating and listing tenants, and for creating, updating and listing their todos. Each query runs inside tenantDB. For the todo routes, the middleware supplies the tenant context, so the list query does not need a where tenant_id = ... filter. The update query still filters by todo ID to select the row to change.
// create new tenant
app.post("/api/tenants", async (req, res) => {
try {
const name = req.body.name;
const tenants = await tenantDB(async (tx) => {
return await tx.insert(tenantSchema).values({ name }).returning();
});
res.json(tenants);
} catch (error: any) {
console.log("error creating tenant: " + error.message);
res.status(500).json({message: "Internal Server Error",});
}
});
// return list of tenants
app.get("/api/tenants", async (req, res) => {
let tenants: any = [];
try {
tenants = await tenantDB(async (tx) => {
return await tx.select().from(tenantSchema);
});
res.json(tenants);
} catch (error: any) {
console.log("error listing tenants: " + error.message);
res.status(500).json({message: "Internal Server Error",});
}
});
// add new task for tenant
app.post("/api/tenants/:tenantId/todos", async (req, res) => {
try {
const { title, complete } = req.body;
if (!title) {
res.status(400).json({message: "No task title provided",});
return;
}
const tenantId = req.params.tenantId;
const newTodo = await tenantDB(async (tx) => {
return await tx
.insert(todoSchema)
.values({ tenantId, title, complete })
.returning();
});
res.json(newTodo);
} catch (error: any) {
console.log("error adding task: " + error.message);
res.status(500).json({message: "Internal Server Error",});
}
});
// update tasks for tenant
// The tenant context scopes the query, and the todo ID selects the row to update.
app.put("/api/tenants/:tenantId/todos", async (req, res) => {
try {
const { id, complete } = req.body;
await tenantDB(async (tx) => {
return await tx
.update(todoSchema)
.set({ complete })
.where(eq(todoSchema.id, id));
});
res.sendStatus(200);
} catch (error: any) {
console.log("error updating tasks: " + error.message);
res.status(500).json({message: "Internal Server Error",});
}
});
// get all tasks for tenant
app.get("/api/tenants/:tenantId/todos", async (req, res) => {
try {
// No need for a "where" clause here because we are setting the tenant ID in the context
const todos = await tenantDB(async (tx) => {
return await tx
.select({
id: todoSchema.id,
tenant_id: todoSchema.tenantId,
title: todoSchema.title,
estimate: todoSchema.estimate,
})
.from(todoSchema);
});
res.json(todos);
} catch (error: any) {
console.log("error listing tasks: " + error.message);
res.status(500).json({message: error.message,});
}
});Try it out!
You can now run your new web application:
npx tsx src/app.tsand use curl to try the routes you just created:
# create a tenant
curl --location --request POST 'localhost:3001/api/tenants' \
--header 'Content-Type: application/json' \
--data-raw '{"name":"my first customer"}'
# get tenants
curl -X GET 'http://localhost:3001/api/tenants'
# create a todo (don't forget to use a real tenant-id in the URL)
curl -X POST \
'http://localhost:3001/api/tenants/108124a5-2e34-418a-9735-b93082e9fbf2/todos' \
--header 'Content-Type: application/json' \
--data-raw '{"title": "feed the cat", "complete": false}'
# list todos for tenant (don't forget to use a real tenant-id in the URL)
curl -X GET \
'http://localhost:3001/api/tenants/108124a5-2e34-418a-9735-b93082e9fbf2/todos'Project file structure
This is the file structure of the project. In the src/db directory, we have database-related files including connection in db.ts and schema definitions
in schema.ts. The files generated by the migrations and introspections are in ./drizzle. The following timestamps and migration names are illustrative. Yours will differ.
π¦ <project root>
β π src
β β π db
β β β π db.ts
β β β π schema.ts
β β π app.ts
β π drizzle
β β π 20261117000000_baseline
β β β π migration.sql
β β β π snapshot.json
β β π 20261117000100_add_todos
β β β π migration.sql
β β β π snapshot.json
β β π relations.ts
β β π schema.ts
β π .env
β π drizzle.config.ts
β π package.json