Build a backend service with Nile and Drizzle

In this tutorial, you will learn about Nile's tenant virtualization features, while building a backend service for a todo list application. We'll use Drizzle as the ORM to interact with the database, Express as the web framework and NodeJS as the runtime.

1. Create a database

1. Sign up for an invite to Nile if you don't have one already
2. You should see a welcome message. Click on "Lets get started"
3. Give your workspace and database names, or you can accept the default auto-generated names. In order to complete this quickstart in a browser, make sure you select to “Use Token in Browser”.

2. Create a table

After you created a database, you will land in Nile's query editor. Since our application requires a table for storing all the "todos" this is a good time to create one:

  CREATE TABLE IF NOT EXISTS "todos" (
    "id" uuid DEFAULT gen_random_uuid(),
    "tenant_id" uuid,
    "title" varchar(256),
    "complete" boolean,
    CONSTRAINT todos_tenant_id_id PRIMARY KEY("tenant_id","id")
  );

You will see the new table in the panel on the left side of the screen, and you can expand it to view the columns.

See the tenant_id column? By specifying this column, You are making the table tenant aware. The rows in it will belong to specific tenants. If you leave it out, the table is considered shared, more on this later.

3. Get credentials

In the left-hand menu, click on "Settings" and then select "Connection".

Click on the Postgres button, then click "Generate Credentials" on the top right corner. Copy the connection string - it should now contain the credentials we just generated.

4. Set the environment

Enough GUI for now. Let's get to some code.

If you haven't cloned this repository yet, now will be an excellent time to do so.

git clone https://github.com/niledatabase/niledatabase
cd niledatabase/examples/quickstart/drizzle

Rename .env.example to .env, and update it with the connection string you just copied from Nile Console. Make sure you don't include the word "psql". It should look something like this:

DATABASE_URL=postgres://018b778a-30df-7cdd-b55c-2f9664db39f3:ff3fb983-683c-4616-bbbc-519d8ddbbce5@db.thenile.dev:5432/gwen_db

Install dependencies with yarn install or npm install.

npm install

5. Run the application

Start the web service with npm start or yarn start.

Now you can use curl to explore the APIs. Here are a few examples:

# create a tenant
curl --location --request POST 'localhost:3001/api/tenants' \
--header 'Content-Type: application/json' \
--data-raw '{"name":"my first customer", "id":"108124a5-2e34-418a-9735-b93082e9fbf2"}'

# get tenants
curl  -X GET 'http://localhost:3001/api/tenants'

# create a todo (don't forget to use a read 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 read tenant-id in the URL)
curl  -X GET \
  'http://localhost:3001/api/tenants/108124a5-2e34-418a-9735-b93082e9fbf2/todos'

# list todos for all tenants
curl  -X GET \
  'http://localhost:3001/insecure/all_todos'

6. Check the data in Nile

Go back to the Nile query editor and see the data you created from the app.

SELECT tenants.name, title, complete
FROM todos join tenants on tenants.id = todos.tenant_id;

You should see all the todos you created, and the tenants they belong to.

7. How does it work?

Let's take a look at the code. The web application starting point is in src/app.ts.

It creates an Express app, and sets up some of the usual middleware:

const app = express();
app.listen(PORT, () => console.log(`Server is running on port ${PORT}`));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

The interesting parts are the middleware that sets up the tenant context, and tenantDB which wraps Drizzle and passes the tenant context to Nile.

Lets look at them one by one.

7.1 Tenant Context Middleware

Immediately after the middleware that sets up the Express app, we add a middleware that introduces Nile's tenant context to the application:

app.use((req, res, next) => {
  const fn = match("/api/tenants/:tenantId/todos", {
    decode: decodeURIComponent,
  });
  const m = fn(req.path);

  //@ts-ignore
  const tenantId = m?.params?.tenantId;
  console.log("setting context to tenant: " + tenantId);
  tenantContext.run(tenantId, next);
});

This may look a bit magical, but let me explain. This middleware grabs the tenant id from the URL and sets it in the tenant context. This context is an instance of AsyncLocalStorage - which provides a way to store data that is global and unique per execution context. The data we are storing is the tenant ID, and by passing next as the second argument to tenantContext.run we are making sure that all the code that runs after this middleware will have access to the tenant ID.

7.2 TenantDB

Now lets take a look at db/db.ts - this is where we set up Drizzle and connect it to Nile. We use pg client as the driver for Drizzle, and we pass it the connection string from the environment.

const client = new Client({
  connectionString: process.env.DATABASE_URL,
});

// Drizzle expects the connection to be open when using a client. Alternatively, you can use a pool
await client.connect();

// check the connection
const res = await client.query("SELECT $1::text as message", [
  "Client connected to Nile",
]);
console.log(res.rows[0].message);

export const db = drizzle(client, { logger: true });

But the interesting part is the tenantDB function. This is where we wrap Drizzle with Nile's tenant virtualization features.

We need Drizzle to set Nile context on the connection before each query we execute. We do this by accepting each query as a callback and wrapping it in a transaction that starts with set nile.tenant_id. We get the tenant ID from the tenant context, which we set earlier in the middleware.

return db.transaction(async (tx) => {
  const tenantId = tenantContext.getStore();
  // clean old context
  await tx.execute(sql`reset nile.tenant_id`);
  // if there's a tenant ID, set it in the context
  if (tenantId) {
    await tx.execute(sql`set nile.tenant_id = '${sql.raw(tenantId)}'`);
  }
  return cb(tx);
});

7.3 Tying it all together - handling a request for all todos for a tenant

Now that we understand how the tenant context and tenantDB work, lets take a look at how we use them to handle a request for all todos for a tenant. We are using the tenantDB function to execute a query that returns all the todos for a tenant. The query doesn't need to include the tenant ID in the where clause, because tenantDB will set it in the context.

const todos = await tenantDB(async (tx) => {
  return await tx.select().from(todoSchema);
});
res.json(todos);

7.4 But wait, what's todoSchema and tenantSchema?

You may have noticed that we are not using the table name directly in the code. Instead, we are using todoSchema and tenantSchema. We defined these in src/db/schema.ts, and these are the objects that Drizzle uses to interact with the database. It is an object that maps to the tables in our database.

Drizzle can also generate migration files from these objects, and we can use them to create the tables in the database.

8. What's next?

This example is a good starting point for building your own application with Nile.

You have learned basic Nile concepts and how to use them with Drizzle.

You can learn more about Nile's tenant virtualization features in the following tutorials:

• Tenant management
• Tenant isolation

And you can explore Nile's JS SDK in the SDK reference.