DEV Community

Muhamed Sufail
Muhamed Sufail

Posted on • Edited on

How I Built a Multi-tenant SaaS Product in Node.js

#node #postgres #saas #express

A few months ago, I had to build an URL-based Multi-tenant SaaS product using Node.js. I had no idea what it is. So I googled "What is a multi-tenant architecture", "How to build a multi-tenant architecture in Node.js", and so on. Unfortunately, I couldn't find a lot of helpful content but finally, I found out about Knex (pronounced /kəˈnɛks/), which is a powerful SQL query builder. I want to take this as an opportunity to share something which I hope some people will find relevant and useful.

Multitenant Architecture is a type of software architecture where a single instance of the software can serve multiple user groups. These user groups are called tenants.

Single-And-Multitenancy

In a Single-tenant,

  • Separate Applications.
  • Separate Databases

In Multi-tenant,

  • Same Application
  • Separate Databases

Types of Multitenancy

There are two main multi-tenancy architectural models when it comes to separating data of tenants

  1. Single database for each tenant
  2. Single database with different tables for each tenant

Types-Of-Multitenancy

Knex.js

According to its official website, Knex.js is a "batteries included" SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use.

Now let's get our hands dirty by building a multi-tenant architecture in Node.js. In the following example, we'll be using different tables within a single database for each tenant's approach.

Pre-requisites

  • Basics of Node.js with express library

Setup

Create a new folder and initialize a node.js project by creating a package.json file by entering the following command in CLI.



$ npm init -y
Enter fullscreen mode Exit fullscreen mode

Install express, knex and pg packages. (pg is a PostgreSQL client for Node.js)



$ npm install express knex pg
Enter fullscreen mode Exit fullscreen mode

Create index.js file in the root folder. This will be the entry point to the application.

Database Schema

Image description

Code Explanation

Note: For ease of understanding, I'm not using best code practices

1. Knex Configuration

Create a db.js file. This is where we'll configure Knex to make a connection to the database.

db.js
Import knex module using require() function.



const knex = require("knex");
Enter fullscreen mode Exit fullscreen mode

Call the top-level function knex() exported by the Knex module which takes a configuration object, accepting a few parameters.



const db = knex({
  client: "postgresql",
  connection: {
    database: "mutitenancy-node",
    user: "postgres",
    password: "postgres",
  },
});
Enter fullscreen mode Exit fullscreen mode

Export the db variable so that it can be used elsewhere in the application.



module.exports = db;
Enter fullscreen mode Exit fullscreen mode

2. Setting Up a Basic Server

index.js

Import express in your application using require() function.



const express = require("express");
Enter fullscreen mode Exit fullscreen mode

Call the top-level function express() exported by the express module.



const app = express()
Enter fullscreen mode Exit fullscreen mode

Import the default export function in db.js file



const knex = require("./db.js");
Enter fullscreen mode Exit fullscreen mode

Mount the express.json() middleware function using the use() method of the app object to parse JSON in the request body.



app.use(express.json());
Enter fullscreen mode Exit fullscreen mode

Listen to the server by the listen() method of the app object.



app.listen(4000, () => {
  console.log("Server listening to Port 4000");
});
Enter fullscreen mode Exit fullscreen mode

3. Creating a Tenant

Create a tenants table through migration or manually with the following fields.

  • id - uuid
  • name - character varying
  • subdomain - character varying
  • admin_email - character varying

Whenever a new tenant register into our SaaS application, insert their details into the tenants table and also create a users table for the users of the tenant prefixed by the subdomain name (tenantname_users).

Create a POST request route /create-tenant using
the post() method of the app object.



app.post('/create-tenant', async (req, res) => {


})
Enter fullscreen mode Exit fullscreen mode

Inside the body of the call back function, obtain the value of name, subdomain, and adminEmail property from the body of the request.



const { name, subdomain, adminEmail } = req.body;
Enter fullscreen mode Exit fullscreen mode

Insert the details of the tenant into the tenants table



await knex("tenants").insert({
      name,
      subdomain,
      admin_email: adminEmail,
    });
Enter fullscreen mode Exit fullscreen mode

Now, create a table for the users of the tenant



await knex.schema.createTable(`${subdomain}_users`, (table) => {
    table.uuid("id").defaultTo(knex.raw("uuid_generate_v4()"));
    table.string("first_name");
    table.string("last_name");
    table.string("email").unique();
  });
Enter fullscreen mode Exit fullscreen mode

Send a response back to the client using the send() method.



  res.send("Tenant Created");
Enter fullscreen mode Exit fullscreen mode

4. Inserting into users table

Create a POST request route /create-user using
the post() method of the app object.



app.post('/create-user', async (req, res) => {


})
Enter fullscreen mode Exit fullscreen mode

Obtain the subdomain of the requesting client using subdomains array of req object.



const subdomain = req.subdomains[0];
Enter fullscreen mode Exit fullscreen mode

Note: To have a dynamic URL for each tenant, you might need to use Nginx with your frontend application.

Since we are using localhost, obtain the subdomain and user details from the request body.



const { firstName, lastName, email, subdomain } = req.body;
Enter fullscreen mode Exit fullscreen mode

Insert the details of the user into users table of that particular tenant



await knex(`${subdomain}_users`).insert({
    first_name: firstName,
    last_name: lastName,
    email,
  });
Enter fullscreen mode Exit fullscreen mode

Send a response back to the client using the send() method.




res.send("User Created !!");
Enter fullscreen mode Exit fullscreen mode




Wrap Up

Since we're using a single database, requests from multiple tenants can lead to a noisy-neighbor effect, which causes network performance issues.

Complete Code
Github

Credits

Top comments (3)

Collapse
 
dyvd4 profile image
Dyvd4 • • Edited

Why would you create tables for each tenant? What if your application has 15 Tables per Db and like 20 Tenants or so. Then you would have 300 Tables in your Db. Is that a common pattern? Isn't that unmanagable?

I would rather have an extra column for the tenant for each table so the amount of tables stays the same and you have all records for every tenant in one table.

I think the most performant but maybe also more expensive way is to just have multiple DBs for each tenant. But I'm new to this topic so answers / corrections are very appreciated!

Collapse
 
dinkopehar profile image
Dinko Pehar •

Postgres supports Schemas. You can for example see how this package django-tenant-schemas.readthedocs.... uses it.

Knex.js also supports with knexjs.org/guide/schema-builder.ht...

Collapse
 
mayank30 profile image
Mayank •

Cant we use the sqlite db for tenant db and mysql or postgres for comman db.

Meaning we can have multiple file based db for each tenant specific operations.

Read next

aaravjoshi profile image

Supercharge Your Node.js Apps: Zero-Copy I/O Secrets for Blazing Performance

Aarav Joshi -

dbvismarketing profile image

Simplify Data Migration Between Databases Using DbVisualizer

DbVisualizer -

hverlin profile image

Migrating from nvm to mise

Hugues Verlin -

ehtisamhaq profile image

Exploring Design Patterns for Express.js Projects: MVC, Modular, and More

Ehtisam Haq -