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.

This guide assumes familiarity with:
  • You should have installed Drizzle ORM and Drizzle kit. You can do this by running the following command:
npm
yarn
pnpm
bun
npm i drizzle-orm@rc
npm i -D drizzle-kit@rc
  • You should have installed dotenv package for managing environment variables. Read more about this package here
npm
yarn
pnpm
bun
npm i dotenv
  • You should have installed pg package for connecting to the Postgres database. Read more about this package here
npm
yarn
pnpm
bun
npm i pg
npm i -D @types/pg
  • You should have installed express package for the web framework. This example uses Express 4 routing syntax. Read more about Express here
npm
yarn
pnpm
bun
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_name

Connect Drizzle ORM to your database

Create a db.ts file in the src/db directory and set up your database configuration:

src/db/db.ts
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:

drizzle.config.ts
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 --init

In 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:

src/db/schema.ts
// 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:

src/db/schema.ts
// 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 generate

Review the new migration.sql. It should create todos without dropping or recreating Nile’s built-in tables. Then apply it:

npx drizzle-kit migrate

Keep 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:

src/app.ts
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.

src/app.ts
// 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);
});

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.

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.

src/app.ts
// 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.ts

and 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