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.
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
- Single database for each tenant
- Single database with different tables for each tenant
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
Install express
, knex
and pg
packages. (pg is a PostgreSQL client for Node.js)
$ npm install express knex pg
Create index.js
file in the root folder. This will be the entry point to the application.
Database Schema
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");
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",
},
});
Export the db
variable so that it can be used elsewhere in the application.
module.exports = db;
2. Setting Up a Basic Server
index.js
Import express in your application using require()
function.
const express = require("express");
Call the top-level function express()
exported by the express module.
const app = express()
Import the default export function in db.js
file
const knex = require("./db.js");
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());
Listen to the server by the listen()
method of the app object.
app.listen(4000, () => {
console.log("Server listening to Port 4000");
});
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) => {
})
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;
Insert the details of the tenant into the tenants
table
await knex("tenants").insert({
name,
subdomain,
admin_email: adminEmail,
});
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();
});
Send a response back to the client using the send()
method.
res.send("Tenant Created");
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) => {
})
Obtain the subdomain of the requesting client using subdomains
array of req
object.
const subdomain = req.subdomains[0];
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;
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,
});
Send a response back to the client using the send()
method.
res.send("User Created !!");
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)
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!
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...
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.