DEV Community

Cover image for Basics of Multi tenant Node.js and PostgreSQL
Agustín Rodríguez
Agustín Rodríguez

Posted on • Updated on


Basics of Multi tenant Node.js and PostgreSQL

Can February march? No, but April may. 😂

I know, it was a terrible joke but I also know if you following read this article you will learn the basics of how to create your own basic multi tenant Node.js and PostgreSQL API.

How does a multi tenant architecture work?

Well, basically you have a codebase running in a shared infrastructure but keeping isolated a database for each client.
Think in Jira, Jira is the most popular online tool for managing project tasks, tracking errors and issues, and for operational project management where each organization has its own dashboard accessed via custom subdomain where A and B have access to the same features, receives the same updates, but the issues, tickets, comments, users, etc. of A cannot be accessed by B and vice-versa.
Slack is another example of multi tenancy and works in the same way like Jira does… of course in this case we will talk about users, channels, PM, notifications, etc.

Now we’ve covered what is a multi tenant architecture and how it works, but if you still have some questions about it, here’s a blog that can help you review the differences between Single Tenant vs Multi Tenant.

When you must use multi tenancy?

Just imagine you have been working for a long time in an awesome application that can be offered as a SaaS, there are different ways to offer a SaaS application but if your software needs to keep a database isolated, but providing the same features to each customer, then needs it.

If you’re looking to create your own SaaS application on AWS, I highly recommend you this amazing article called Multi tenant Architecture SaaS Application on AWS!


One of the benefits of the multi tenant application is the maintainability of the code base because the code will always be the same for all clients, if a client reports a problem, the solution will be applied to their other 999 clients. Just note that if you enter an error, it will also apply to all clients. And what happens with the administration of the database, maybe it could be a little more complicated, but following the appropriate patterns and conventions, everything will be fine, there are different approaches to managing databases (segregation in distributed servers, databases of separate data sets, a database but separate schemas, row isolation) and of course each has pros and cons.

We’ve created many other Multi tenant and SaaS articles to help you continue learning. Take a look!

You wanna code?

I selected the separate databases as database approach because I think is easier for this example, also, due the sequelize requires a lot of configuration I used knex instead.

I going to focus on the specific files required to do the multi tenancy Node.js and PostgreSQL workflow.

Multi tenancy Node.js and PostgreSQL

Create the common database to manage the tenants

CREATE DATABASE tenants_app; 

CREATE TABLE tenants (  
  db_name VARCHAR(100) UNIQUE NOT NULL,  
  db_username VARCHAR(100),  
  db_password TEXT,   
  created_at TIMESTAMP DEFAULT NOW(),  
Enter fullscreen mode Exit fullscreen mode

database.js: Establishes the connection to the main database

const knex = require('knex') 
const config = {   
  client: process.env.DB_CLIENT,  
  connection: {   
    user: process.env.DB_USER,     
    host: process.env.DB_HOST,     
    port: process.env.DB_PORT,     
    database: process.env.DB_DATABASE,    
    password: process.env.DB_PASSWORD   
 const db = kenx(config) 
 module.exports = { db, config } 
Enter fullscreen mode Exit fullscreen mode

connection-service.js: Used to prepare the tenant database connection, in other words, the connection used to run queries in the proper database

const knex = require('knex')
const { getNamespace } = require('continuation-local-storage') 
const { db, config } = require('../config/database') let tenantMapping 

const getConfig = (tenant) => {   
  const { db_username: user, db_name: database, db_password: password } = tenant   
  return {     
    connection: {       

const getConnection = () => getNamespace('tenants').get('connection') || null 

const bootstrap = async () => { 
  try {     
    const tenants = await db       
      .select('uuid', 'db_name', 'db_username', 'db_password')     

    tenantMapping = => ({                       
      uuid: tenant.uuid,       
      connection: knex(getConfig(tenant))   
 } catch (e) {     

const getTenantConnection = (uuid) => {   
  const tenant = tenantMapping.find((tenant) => tenant.uuid === uuid)  

  if (!tenant) return null   

  return tenant.connection
Enter fullscreen mode Exit fullscreen mode

tenant-service.js: used to create a database for each new client, using the same database structure and used to delete it if is required.

const Queue = require('bull')
const { db } = require('../config/database') 
const migrate = require('../migrations') 
const seed = require('../seeders') 
const { bootstrap, getTennantConnection } = require('./connection') 

const up = async (params) => {  
  const job = new Queue(    
    `setting-up-database-${new Date().getTime()}`,             
job.add({ ...params })   
job.process(async (job, done) => {   
  try {      
    await db.raw(`CREATE ROLE ${params.tenantName} WITH LOGIN;`) // Postgres requires a role or user for each tenant       
    await db.raw(         
      `GRANT ${params.tenantName} TO ${process.env.POSTGRES_ROLE};`       
) // you need provide permissions to your admin role in order to allow the database administration       
    await db.raw(`CREATE DATABASE ${params.tenantName};`)       
    await db.raw(         
      `GRANT ALL PRIVILEGES ON DATABASE ${params.tenantName} TO ${params.tenantName};`
    await bootstrap() // refresh tenant connections to include the new one as available  
    const tenant = getTenantConnection(params.uuid)       
    await migrate(tenant) // create all tables in the current tenant database      
    await seed(tenant) // fill tables with dummy data     
  } catch (e) {      
Enter fullscreen mode Exit fullscreen mode

tenant.js: a controller used to handle the request to list, create or delete a tenant

const { db } = require('../config/database') 
const { v4: uuidv4 } = require('uuid') 
const generator = require('generate-password') 
const slugify = require('slugify') 
const { down, up } = require('../services/tenant-service') 

// index 

const store = async (req, res) => {   
  const {    
    body: { organization }   
  } = req   

  const tenantName = slugify(organization.toLowerCase(), '_')   
  const password = generator.generate({ length: 12, numbers: true })  
  const uuid = uuidv4()   
  const tenant = {     
    db_name: tenantName,     
    db_username: tenantName,     
    db_password: password   
  await db('tenants').insert(tenant)   
  await up({ tenantName, password, uuid })   

  return res.formatter.ok({ tenant: { ...tenant } }) 

const destroy = async (req, res) => {   
  const {     
    params: { uuid }   
  } = req   

  const tenant = await db    
    .select('db_name', 'db_username', 'uuid')     
    .where('uuid', uuid)    

   await down({     
     userName: tenant[0].db_username,    
     tenantName: tenant[0].db_name,    
     uuid: tenant[0].uuid  
   await db('tenants').where('uuid', uuid).del() 

   return res.formatter.ok({ message: 'tenant was deleted successfully' }) } 

module.exports = {  
  // index, 
Enter fullscreen mode Exit fullscreen mode

As you can see in the images below now the API is able to create multiple clients, sharing the services, endpoints and other stuff but keeping isolated the databases.





So cool!

Yup, multi tenant Node.js and PostgreSQL are not as complicated as it sounds, of course, there are many things to consider such as infrastructure, CI/CD, best practices, software patterns, but just handle each one at a time and everything will be fine. And as you can see, this architecture can help your business scale as high as you want because the cloud is the limit, and the cloud has not limits for now. Of course if you want to check complete code you can find it here.

At ClickIT we can provide the entire set of tools and knowledge necessary to prepare your application and run it as a multi tenant, so feel free to reach us anytime you need something regarding multi tenancy.


I created a branch to apply this concept using MySQL as database, also, I'll try to add support for Mongoose as soon as possible.

Top comments (10)

elgeokareem profile image
William Vegas

Bro good article! Do you have any info about a MongoDB (mongoose) implementation? Thanks !

agusrdz profile image
Agustín Rodríguez

I have not something ready at the moment, but I'll do an update to use MongoDB as soon as possible

jatinchandani28 profile image

hey,good article! Do you have any info about a mysql(phpmyadmin) implementation? Thanks !

agusrdz profile image
Agustín Rodríguez

Can you explain a little more what you are looking for?

jatinchandani28 profile image

how to implement multi tenant node js with mysql

Thread Thread
agusrdz profile image
Agustín Rodríguez • Edited

I added support for MySQL in a new branch, almost is the same, remember install the new MySQL package added in the package.json, also, the was updated too with some notes that could be helpful in case of issues.

visualcookie profile image
Dean (딘)

Great article. In terms of security, I would not expose the DB credentials of a tenant on the API. At least not unencrypted.

Would like to see, what ideas you got in terms of security. :)

agusrdz profile image
Agustín Rodríguez

Of course, for security terms another approach it will be implemented like create an APP_ID and APP_SECRET to get an access token or something like that, in the current example credentials are returned to show the result but I think in a real implementation using a token approach it will be enough to handle the database connections under the hood.

williambsb profile image
William Alencar

Great Article ... Helping me a lot ...


majedfaris profile image
majed L