Adonis v4 Multi Tenancy Example

example-model.js

'use strict'

/** @type {typeof import('@adonisjs/lucid/src/Lucid/Model')} */
const Model = use('Model')

class ExampleModel extends Model {
  /**
   * Add traits
   */
  static boot () {
    super.boot();
    this.addTrait('Tenant');
  }
}

module.exports = ExampleModel

index.js

const Customer = use ('App/Models/Customer')
const ExampleModel = use ('App/Models/ExampleModel')
const TenantManager = use('Service/TenantManager')

// Update a standard model
async function standardExample (customerId, exampleId, data) {
  const customer = await Customer.find(customerId);
  const tenant = await TenantManager.connect(customer);
  await tenant.perform(async (TenantExampleModel) => {
     const newExample = new TenantExampleModel();
     newExample.merge(data);
     await newExample.save();
  }, ExampleModel);
}

// Do something more complicated
async function rawExample (customerId, inserts) {
  const customer = await Customer.find(customerId);
  const tenant = await TenantManager.connect(customer);
  // Using the tenant perform the inserts
  await tenant.perform(async (connection) => {
    await connection.transaction(async (trx) => {
      // Create raw query to do bulk inserts for timescaledb
      await trx.raw(util.format(
        '%s ON CONFLICT ("uniqueId") DO NOTHING',
        trx
          .from(ExampleModel.table)
          .insert(inserts)
          .toString()
      ));
    });
  });
}

manager.js

'use strict'

const Tenant = require('./Tenant');

/** @typedef {import('app/Models/Customer')} Customer */
const Customer = use('App/Models/Customer');

class Manager {
  /**
   * Construct the manager class
   *
   * @param {Config} Config
   * @param {Database} Database
   */
  constructor (Config, Database) {
    this.config = Config;
    this.defaults = Config.get('database.tenant');
    this.databaseManager = Database;
    this.tenants = {};
  }

  /**
   * Check for expired connections and close them
   */
  expire = () => {
    const now = new Date();
    Object.values(this.tenants)
      .filter(tenant => tenant.expires > now.getTime())
      .forEach(tenant => this.disconnect(tenant.customerId));
  }

  /**
   * Get a fresh expiry date (future date, +60 seconds)
   */
  getExpiryDate () {
    return (new Date()).getTime() + (60 * 1000);
  }

  /**
   * Connect to the tenant database using the given customer
   *
   * @param {Customer} customer
   */
  async connect (customer) {
    return this.createConnection(customer);
  }

  /**
   * Create a connection to the tenant database for the customer record
   *
   * @param {Customer} customer
   * @param {Number} customer.id
   * @param {String} customer.database
   */
  createConnection ({ id: customerId, database }) {
    if (this.tenants[database]) {
      return this.tenants[database].tenant;
    }
    const { defaults } = this;
    // Locate the customer record and populate the config with the customer connection information
    const connectionConfig = { ...defaults };
    connectionConfig.connection.database = database;
    this.config.set(`database.${database}`, connectionConfig);

    // Use the database manager to establish a connection to the database
    const dbConnection = this.databaseManager.connection(database);

    const tenant = new Tenant(database, dbConnection, this);

    this.tenants[database] = {
      tenant,
      database,
      customerId,
      expires: this.getExpiryDate(),
    };

    return tenant;
  }

  /**
   * Ping the connection to update the expiry time
   *
   * @param {String} connection
   */
  ping (database) {
    if (!this.tenants[database]) {
      return;
    }
    // Update last used
    this.tenants[database].expires = this.getExpiryDate();
  }

  /**
   * Disconnect the tenant database for the given customer id
   *
   * @param {Number} customerId
   */
  async disconnect (customerId) {
    const { database } = await Customer.findOrFail(customerId);
    delete this.tenants[database];
    this.close(database);
  }

  /**
   * Closes the connection
   *
   * @param {String} connection
   */
  close (connection) {
    try {
      this.databaseManager.close([connection]);
    } catch (err) {
      // Ignore
    }
  }

  /**
   * Establishes a connection, this opens any that were closed before
   *
   * @param {String} connection
   */
  establish (connection) {
    return this.databaseManager.connection(connection);
  }
}

module.exports = Manager;

tenant.js

class Tenant {
  /**
   * Create the tenant class which is responsible for performing database actions with
   * tenant models
   *
   * @param {String} connection
   * @param {Manager} manager
   */
  constructor (dbName, dbConnection, manager) {
    this.dbName = dbName;
    this.dbConnection = dbConnection;
    this.manager = manager;
  }

  /**
   * Captures the models standard connection so it can be restored once the
   * tenant database actions have been performed
   *
   * @param {Array} models
   */
  capture (models) {
    const { dbName } = this;
    this.dbConnection = this.manager.establish(dbName);

    models.forEach(model => {
      model.previousConnection = model.connection;
      model.connection = dbName;
    });
  }

  /**
   * Restores the connection on the model
   *
   * @param {Array} models
   */
  release (models) {
    const { dbName } = this;
    // Put the previous connection back on the model
    models.forEach(model => {
      model.connection = model.previousConnection;
      model.previousConnection = null;
    });

    this.manager.ping(dbName);
  }

  /**
   * Captures the passed in models so they can be used on the tenant database, this will pass all of
   * the given models to the callback as arguments, these will all be configured so they will work
   * with the tenant database
   *
   * @param {Function} callback
   * @param  {...Model} models
   */
  async perform (callback, ...models) {
    this.capture(models);
    const result = await callback(...models, this.dbConnection);
    this.release(models);
    return result;
  }

  /**
   * Saves the model in the tenant database
   *
   * @param {Model} model
   */
  async save (model) {
    this.capture([model]);
    const result = await model.save();
    this.release([model]);
    return result;
  }

  /**
   * Finds the model from the tenant database, the model will not have the tenant connection when
   * returned and further actions must be used with the Tenant.perform or Tenant.save methods
   *
   * @param {Model} model
   * @param {any} value
   */
  async find(model, value) {
    this.capture([model]);
    const found = await model.find(value);
    this.release([model]);
    return found;
  }

  /**
   * Finds the model from the tenant database by key and value, the model will not have the tenant connection when
   * returned and further actions must be used with the Tenant.perform or Tenant.save methods
   *
   * @param {Model} model
   * @param {String} key
   * @param {any} value
   */
  async findBy(model, key, value) {
    this.capture([model]);
    const found = await model.findBy(key, value);
    this.release([model]);
    return found;
  }
}

module.exports = Tenant;

tenent-model-trait.js

'use strict'

class Tenant {
  register (Model) {
    Object.defineProperties(Model, {
      // Handles updating the connection used by the model
      connection: {
        get: () => this.connection,
        set: (connection) => this.connection = connection,
      },
      // Handles storing the previous connection
      previousConnection: {
        get: () => this.previousConnection,
        set: (prev) => this.previousConnection = prev,
      }
    })
  }
}

module.exports = Tenant

Hey,

1. Yes this was put into a production system and has been working successfully. I may have made some tweaks but I do not have access to that code base anymore.
2. Maybe, I'm not sure what your requirements are but it should satisfy selecting tenant database connections and performing queries / model updates.
3. Correct
4. No, but below could be useful to you:

Config
You should have a tenant database configuration section added to your databases.js file. A limitation of this system is you can only use it with one type of database at a time. There is no mixing PostgreSQL or MySQL.

Usage
You should have customer models / objects with at least id and database properties. These are what the manager users to connect to the right database and to store the connection. These databases must be accessible by the user setup on the tenant database configuration... this is not always ideal and would be better to store the username and password for each customer separately. Tweaking createConnection you could override those values easily.

You can get a specific tenant from the manager using a customer model, you can then use the tenant object to perform actions

const tenant = await TenantManager.connect(myCustomer);
await tenant.perform(async (TenantModel) => {
     const entity = await TenantModel.find(entityId);
}, EntityModel);

The last argument of the callback is always the DB connection so you can use that for raw queries:

const tenant = await TenantManager.connect(myCustomer);
await tenant.perform(async (TenantModel, dbConnection) => {
     // raw db query here
}, EntityModel);

The models passed to perform are optional

const tenant = await TenantManager.connect(myCustomer);
await tenant.perform(async (dbConnection) => {
     // raw db query here
});

There are also shortcuts which are listed on the tenant class:

const tenant = await TenantManager.connect(myCustomer);
const entity = await tenant.find(EntityModel, 'id', myId);

Long running processes or daemons should call the TenantManager.expire method periodically to remove any stale connections and to keep the connection pool low.

Hope this helps.

Thank you very much for your prompt response, I am grateful! Really appreciate it..!