Back to main page Traveling Coderman

Node.js Microservice Architecture Single schema multi-tenancy in Node.js with Postgres

Node.js Part 12 May 21, 2022

We want to allow multiple tenants to use the todos of this service independently in dedicated workspaces. The JWT provided from the token issuer contains a workspace ID to identify the workspace the authenticating user is operating in.

{
  "sub": "1234567890",
  "name": "John Doe",
  "workspaceId": "83fdfa88-737d-4550-87e4-c78bf954fcf2",
  "iat": 1516239022
}

In this post, we pass this workspace id from the endpoint controllers through the data access module to the database to achieve multi-tenancy.

Multi-tenancy options 🔗

When it comes to separating workspaces in the database, there are three different options.

  • Separate databases: Very expensive, very slow to create tenants, but great isolation of tenants.
  • Separate schemas: Expensive, slow to create tenants, still good isolation of tenants, but isolation needs to be handled in application.
  • Same schema, additional workspace ID column: Cheap, fast to create tenants, isolation of tenants fully in application, no isolated backups possible, admin queries over multiple workspaces possible.

All of these are valid options and depending on your requirements your chosen option will vary. The more sensible the information, the higher the isolation needs to be. You need to handle credit card information differently than a list of todos.

Since our service deals with non-sensible information, we favor speed over isolation and go for an implementation in the same schema.

Adding a workspace ID column 🔗

We add a second schema migration migrations/2-add-workspace-id.ts that adds a workspace ID column to thetodos table.

import { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("todos", (table) => {
    table.uuid("workspaceId").notNullable();
    table.dropPrimary();
    table.primary(["id", "workspaceId"]);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("todos", (table) => {
    table.dropPrimary();
    table.primary(["id"]);
  });
}

First, we create a UUID column for the workspace ID. Then, we drop the previous primary constraint of the id column. At last, we add a new primary constraint requiring the combination of id and workspaceId to be unique.

Note: If we were to create a table tasks referring to todos, then it would need to reference the combined foreign key of id and workspaceId. This makes JOIN queries more cumbersome and is a drawback of this approach. However, it reduces the risk of accidentally joining with data of different workspaces.

Workspace ID type 🔗

The workspace ID is referenced through the application in controllers and data access modules. To reduce the risk of confusing other IDs with workspace IDs, we create a type alias WorkspaceId in a file src/workspace-id.type.ts.

export type WorkspaceId = string;

export type WithWorkspaceId = {
  workspaceId: WorkspaceId;
};

Additionally, we create a type WithWorkspaceId. Every entity in the database has a workspace ID. But API users don't expect the workspace ID to be returned in the entity since for them all entities are part of the same workspace. The type WithWorkspaceId allows us to define a type type DbTodo = Todo & WithWorkspaceId for the usage within data access modules. Outside of the data access modules, we can refer to the type Todo without the workspace ID.

Database seed 🔗

Now that there is a workspace ID column in the todos table, the seeding of the database for the local environment needs to change. Instead of inserting three todos, we now insert five todos: Three todos in a first workspace and two todos in a second workspace.

import { Knex } from "knex";
import { DbTodo } from "../../controllers/todos/todo.type";
import { WorkspaceId } from "../../workspace-id.type";

const workspaceId1: WorkspaceId = "a9393008-eab2-48e8-b820-0e03447f881c";
const workspaceId2: WorkspaceId = "83fdfa88-737d-4550-87e4-c78bf954fcf2";

const todos: DbTodo[] = [
  {
    workspaceId: workspaceId1,
    id: "142e8446-f5ba-4813-bfb5-f3192a37f1bf",
    name: "Workspace 1 - Todo 1",
    assignee: "Naomi",
    dueDate: "2025-01-05",
  },
  {
    workspaceId: workspaceId1,
    id: "bf6091cc-69f8-4a9e-93ed-0294c3a8ac2f",
    name: "Workspace 1 - Todo 2",
    assignee: "Peter",
    dueDate: "2029-06-11",
  },
  {
    workspaceId: workspaceId1,
    id: "6e5bad4c-7f1c-4ab4-9361-c6bd697b4257",
    name: "Workspace 1 - Todo 3",
    assignee: "Sheila",
    dueDate: "2032-11-27",
  },
  {
    workspaceId: workspaceId2,
    id: "1d2be9f2-4fe9-4509-b8ec-d0d9425c3685",
    name: "Workspace 2 - Todo 1",
    assignee: "Ousmane",
    dueDate: "2025-07-14",
  },
  {
    workspaceId: workspaceId2,
    id: "e868f10d-d21a-4139-9c5d-b8c73c62735a",
    name: "Workspace 2 - Todo 2",
    assignee: "Carla",
    dueDate: "2034-09-07",
  },
];

export async function seed(knex: Knex): Promise<void> {
  await knex("todos").truncate();
  await knex("todos").insert(todos);
}

Local JWT tokens 🔗

Using jwt.io, we refer to these two workspace IDs in two tokens. We adjust the script local.sh to initialize these two tokens.

export TOKEN1=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwid29ya3NwYWNlSWQiOiJhOTM5MzAwOC1lYWIyLTQ4ZTgtYjgyMC0wZTAzNDQ3Zjg4MWMiLCJpYXQiOjE1MTYyMzkwMjJ9.TBClI8i5Urt4JDEnAo0g6LB59-S3hQwdt64MaAVulI4
export TOKEN2=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwid29ya3NwYWNlSWQiOiI4M2ZkZmE4OC03MzdkLTQ1NTAtODdlNC1jNzhiZjk1NGZjZjIiLCJpYXQiOjE1MTYyMzkwMjJ9.Pzcmnyvyvqm4PKbTm5Cu6yflYnC3oFAyFaib4Oo0cj4
export INVALID_TOKEN=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c

Additionally, we add an invalid token without a workspace ID. This way, we can manually check the behaviour of the application if the token issuer issues a faulty token.

Expecting a workspace ID in DAO functions 🔗

The data access module todo.dao.ts needs to accept the workspace ID as a parameter and utilize it in the Knex SQL queries. We adjust every function getTodo, getTodos, createTodo, updateTodo and deleteTodo.

getTodo 🔗

When retrieving an individual todo, we need to define an additional WHERE clause in the SQL query.

export function getTodo(
  workspaceId: WorkspaceId,
  id: TodoId
): Promise<Todo | undefined> {
  return db
    .table<DbTodo>("todos")
    .where("id", id)
    .andWhere("workspaceId", workspaceId)
    .first<Todo>(todoFields);
}

Additionally, we want to keep the fact that the workspaces are implemented via workspace ID column an implementation detail of the data access module and the database. Therefore, we only select the fields todoFields of a Todo. These are defined in the module todo.type.ts as export const todoFields: (keyof Todo)[] = ["id", "name", "assignee", "dueDate"];

getTodos 🔗

Similarly, when retrieving all todos, we pass the workspace ID in a WHERE clause and only select the fields todoFields.

export function getTodos(workspaceId: WorkspaceId): Promise<Todo[]> {
  return db
    .table<DbTodo>("todos")
    .where("workspaceId", workspaceId)
    .select(todoFields);
}

createTodo 🔗

When inserting a todo, we populate the workspace ID column with the passed parameter workspaceId.

export async function createTodo(
  workspaceId: WorkspaceId,
  todo: Omit<Todo, "id">
): Promise<Todo> {
  const todoWithId: Todo = {
    ...todo,
    id: uuid(),
  };
  await db.table<DbTodo>("todos").insert({
    ...todoWithId,
    workspaceId,
  });
  return todoWithId;
}

updateTodo 🔗

On update of a todo, we define an additional WHERE clause to make sure we don't update a todo of a different workspace with the same todo ID.

export async function updateTodo(
  workspaceId: WorkspaceId,
  id: TodoId,
  todo: Omit<Todo, "id">
): Promise<Todo | "NotFound"> {
  const todoWithId = {
    ...todo,
    id,
  };
  const changedRowCount = await db
    .table<DbTodo>("todos")
    .where("id", id)
    .andWhere("workspaceId", workspaceId)
    .update({
      ...todoWithId,
      workspaceId,
    });
  return changedRowCount === 0 ? "NotFound" : todoWithId;
}

deleteTodo 🔗

Also, on the deletion of a todo, we make sure to only delete the todo with the todo ID if the workspace ID matches.

export async function deleteTodo(
  workspaceId: WorkspaceId,
  id: TodoId
): Promise<"Ok" | "NotFound"> {
  const deletedRowCount = await db
    .table<DbTodo>("todos")
    .where("id", id)
    .andWhere("workspaceId", workspaceId)
    .delete();
  return deletedRowCount === 0 ? "NotFound" : "Ok";
}

Testing the data access module 🔗

With the signature of the functions of the data access module changed, we need to adjust the test cases. For each of the existing test cases, we generate a random workspace ID and pass it to the data access functions. Apart from that, nothing changes.

import { omit } from "lodash/fp";
import {
  migrateDatabase,
  truncateTables,
} from "../../database/db-test.functions";
import {
  createTodo,
  deleteTodo,
  getTodo,
  getTodos,
  updateTodo,
} from "./todo.dao";
import { createStubTodo } from "./todo.stub";
import { v4 as uuid } from "uuid";

describe("the todo dao", () => {
  beforeAll(migrateDatabase);
  afterEach(truncateTables);

  it("retrieves no todos if there are none in this workspace", async () => {
    const workspaceId = uuid();
    await expect(getTodos(workspaceId)).resolves.toEqual([]);
  });

  it("inserts a todo and retrieves it", async () => {
    const workspaceId = uuid();
    const todo = omit("id", createStubTodo());
    const createdTodo = await createTodo(workspaceId, todo);
    expect(createdTodo).toHaveProperty("id");
    await expect(getTodos(workspaceId)).resolves.toEqual([createdTodo]);
    await expect(getTodo(workspaceId, createdTodo.id)).resolves.toEqual(
      createdTodo
    );
  });

  it("updates an existing todo", async () => {
    const workspaceId = uuid();
    const toBeCreatedTodo = omit("id", createStubTodo());
    const createdTodo = await createTodo(workspaceId, toBeCreatedTodo);
    const toBeUpdatedTodo = omit("id", createStubTodo());
    const updatedTodo = await updateTodo(
      workspaceId,
      createdTodo.id,
      toBeUpdatedTodo
    );
    await expect(getTodos(workspaceId)).resolves.toEqual([updatedTodo]);
    await expect(getTodo(workspaceId, createdTodo.id)).resolves.toEqual(
      updatedTodo
    );
  });

  it("deletes an existing todo", async () => {
    const workspaceId = uuid();
    const toBeCreatedTodo = omit("id", createStubTodo());
    const createdTodo = await createTodo(workspaceId, toBeCreatedTodo);
    await expect(deleteTodo(workspaceId, createdTodo.id)).resolves.toEqual(
      "Ok"
    );
    await expect(getTodos(workspaceId)).resolves.toEqual([]);
    await expect(getTodo(workspaceId, createdTodo.id)).resolves.toEqual(
      undefined
    );
  });

  it("does not find a non-existing todo on update", async () => {
    const workspaceId = uuid();
    const todo = omit("id", createStubTodo());
    await expect(updateTodo(workspaceId, uuid(), todo)).resolves.toEqual(
      "NotFound"
    );
  });

  it("does not find a non-existing todo on delete", async () => {
    const workspaceId = uuid();
    await expect(deleteTodo(workspaceId, uuid())).resolves.toEqual("NotFound");
  });
});

Additionally, we add new test cases that explicitly check that the workspaces are properly separated. These tests are crucial, as they are the only safeguard to prevent a forgotten WHERE clause and exposing data of a different workspace.

We check for a specific scenario: There exists a todo with the todo ID in a different workspace. In this case, the following statements should be true.

  • The todo from the different workspace should not be retrieved.
  • The todo from the different workspace should not be updated.
  • The todo from the different workspace should not be deleted.
import { omit } from "lodash/fp";
import {
  migrateDatabase,
  truncateTables,
} from "../../database/db-test.functions";
import {
  createTodo,
  deleteTodo,
  getTodo,
  getTodos,
  updateTodo,
} from "./todo.dao";
import { createStubTodo } from "./todo.stub";
import { v4 as uuid } from "uuid";

describe("the todo dao", () => {
  beforeAll(migrateDatabase);
  afterEach(truncateTables);

  // ...

  it("does not retrieve a todo of a different workspace", async () => {
    const workspaceId1 = uuid();
    const workspaceId2 = uuid();
    const todo = omit("id", createStubTodo());
    const createdTodo = await createTodo(workspaceId1, todo);
    await expect(getTodos(workspaceId2)).resolves.toEqual([]);
    await expect(getTodo(workspaceId2, createdTodo.id)).resolves.toEqual(
      undefined
    );
  });

  // ...

  it("does not update a todo of a different workspace", async () => {
    const workspaceId1 = uuid();
    const workspaceId2 = uuid();
    const toBeCreatedTodo = omit("id", createStubTodo());
    const createdTodo = await createTodo(workspaceId1, toBeCreatedTodo);
    const toBeUpdatedTodo = omit("id", createStubTodo());
    const updatedTodo = await updateTodo(
      workspaceId2,
      createdTodo.id,
      toBeUpdatedTodo
    );
    expect(updatedTodo).toEqual("NotFound");
    await expect(getTodos(workspaceId1)).resolves.toEqual([
      { ...toBeCreatedTodo, id: createdTodo.id },
    ]);
    await expect(getTodos(workspaceId2)).resolves.toEqual([]);
    await expect(getTodo(workspaceId1, createdTodo.id)).resolves.toEqual({
      ...toBeCreatedTodo,
      id: createdTodo.id,
    });
    await expect(getTodo(workspaceId2, createdTodo.id)).resolves.toEqual(
      undefined
    );
  });

  // ...

  it("does not delete a todo of a different workspace", async () => {
    const workspaceId1 = uuid();
    const workspaceId2 = uuid();
    const toBeCreatedTodo = omit("id", createStubTodo());
    const createdTodo = await createTodo(workspaceId1, toBeCreatedTodo);
    await expect(deleteTodo(workspaceId2, createdTodo.id)).resolves.toEqual(
      "NotFound"
    );
    await expect(getTodos(workspaceId1)).resolves.toHaveLength(1);
    await expect(getTodos(workspaceId2)).resolves.toHaveLength(0);
    await expect(getTodo(workspaceId1, createdTodo.id)).resolves.not.toEqual(
      undefined
    );
    await expect(getTodo(workspaceId2, createdTodo.id)).resolves.toEqual(
      undefined
    );
  });

  // ...
});

Get workspace ID from JWT 🔗

Before we are able to pass the workspace ID from the endpoint controllers to the data access functions, we need to retrieve the workspace ID from the JWT.

We create a file src/jwt-token.ts that offers a function getToken. This function returns the token from the variable response.locals.token. This variable is initialized in the pre request handler pre-request-handlers/authenticate.ts introduced in the authentication post.

For more information on this, checkout 'JWT authentication with JWKS in Express.js'

import { Response } from "express";
import { JwtPayload } from "jsonwebtoken";
import { WithWorkspaceId } from "./workspace-id.type";

export type JwtTokenPayload = JwtPayload & WithWorkspaceId;

export function getToken(response: Response): JwtTokenPayload {
  return response.locals.token;
}

Defining the function getToken in a separate module has two advantages:

  • The response is properly typed as JwtTokenPayload.
  • We can mock the function getToken in tests of the controllers.

In this function getToken, we are relying on the issuer of the JWT that the workspace ID is present. To protect us from bugs in that service, we validate the token in the pre request handler authenticate.ts.

import { NextFunction, Request, Response } from "express";
import jwt from "jsonwebtoken";
// ...
import { JwtTokenPayload } from "../jwt-token";

// ...

export async function authenticate(
  request: Request,
  response: Response,
  next: NextFunction
): Promise<unknown> {
  try {
    // ...

    if (!isValidPayload(decodedToken.payload)) {
      throw new Error("Unexpected structure of JWT payload");
    }

    response.locals.token = decodedToken.payload;

    next();
  } catch (error) {
    next(error);
  }
}

function isValidPayload(
  payload: string | jwt.JwtPayload
): payload is JwtTokenPayload {
  return typeof payload !== "string" && "workspaceId" in payload;
}

If the token payload is not an object or does not contain the workspace ID, then we throw an error. This thrown error results in an internal server error response 500 due to a post request handler.

Passing the workspace ID from the controllers 🔗

Now that there is a function getToken to retrieve the workspace ID of the JWT, we can adjust the endpoint controllers.

The pattern is identical in all endpoint controllers. We call getToken and pass the workspace ID to the data access function.

import { NextFunction, Request, Response } from "express";
import { getToken } from "../../jwt-token";
import { deleteTodo } from "./todo.dao";

export async function deleteTodoController(
  request: Request,
  response: Response,
  next: NextFunction
): Promise<void> {
  try {
    const workspaceId = getToken(response).workspaceId;
    await deleteTodo(workspaceId, request.params.id);
    response.sendStatus(204);
  } catch (error) {
    next(error);
  }
}

In the tests, we mock the function getToken.

import request from "supertest";
import { v4 as uuid } from "uuid";
import { server } from "../../test.functions";
import { deleteTodoController } from "./delete-todo.controller";

const workspaceId = uuid();

jest.mock("./todo.dao");
jest.mock("../../jwt-token", () => ({
  getToken: () => ({
    workspaceId,
  }),
}));

describe("deleteTodoController", () => {
  const route = "/todos/:id";

  const app = server((app) => {
    app.delete(route, deleteTodoController);
  });

  it("deletes the todo and returns a 204", async () => {
    const deleteTodo = require("./todo.dao").deleteTodo;
    const todoId = uuid();
    deleteTodo.mockResolvedValue();
    await request(app).delete(route.replace(":id", todoId)).expect(204);
    expect(deleteTodo).toHaveBeenCalledWith(workspaceId, todoId);
  });

  // ...
});

Conclusion 🔗

We implemented multi-tenancy in the todo service. Each database table has a column workspaceId. The workspace ID is taken from JWT and passed from the controller through the data access module to the database.

Become a GitHub sponsor to access the code of this post as a GitHub repo.