Adonis 5 multi-tenant questions

[incraigulous]

I'm using the Adonis 5 preview because I need multi-tenant support with multiple databases.

The actual subdomain database connection handling is working really well, following the guide here: https://preview.adonisjs.com/guides/database/connections-management#finding-the-connection-details

I'm running into a couple problems however, how do I migrate the tenant databases? I've tried creating a custom command to create all the database connections before running my migration command, but that doesn't work. I also tried manually using @adonisjs/lucid/build/src/Migrator, but I haven't had much luck there either.

I'm also unsure of how to use the auth system so I can keep a users / api_tokens table in each of my tenant databases. Is there a way to dynamically create an auth provider after my tenant connection is set up?

Anyone else get this up and running yet?

[incraigulous]

I believe I've worked out multitenant migrations by extending the existing migration commands. Posting here in case it's helpful to anyone else. Now on to auth!

import Run from '@adonisjs/lucid/build/commands/Migration/Run'
import Tenant from 'App/Models/Tenant'
import {flags} from '@adonisjs/ace/build'
import {inject} from '@adonisjs/fold/build'

@inject([null, null, 'Adonis/Lucid/Database'])
export default class RunTenantMigrations extends Run {
  public static commandName = 'migration:tenants:run'
  public static description = 'Migrate the tenant databases.'

  /**
   * Custom connection for running migrations.
   */
  @flags.string({description: 'Define a custom database connection', alias: 'c'})
  public connection: string

  /**
   * Force run migrations in production
   */
  @flags.boolean({description: 'Explicitly force to run migrations in production'})
  public force: boolean

  /**
   * Perform dry run
   */
  @flags.boolean({description: 'Print SQL queries, instead of running the migrations'})
  public dryRun: boolean

  /**
   * Only run a specific tenant.
   */
  @flags.string({description: 'Supply a tenant handle to limit the migration to a specific tenant.'})
  public tenant: string

  /**
   * This command loads the application, since we need the runtime
   * to find the migration directories for a given connection
   */
  public static settings = {
    loadApp: true,
  }

  constructor (app, kernel, db) {
    super(app, kernel, db)
  }

  /**
   * Fetch the tenant(s) and migrate them.
   */
  public async handle () {
    //Handle a single tenant
    if (this.tenant) {
      const tenant = await Tenant.findByHandle(this.tenant)
      await this.handleTenant(tenant)
      return
    }

    //Handle all tenants
    const tenants = await Tenant.all()
    for (const tenant of tenants) {
      await this.handleTenant(tenant)
    }
  }

  /**
   * Make the tenant DB connection and then let the Adonis run command handle the migration.
   * @param tenant
   */
  public async handleTenant (tenant: Tenant) {
    console.log(`Migrating tenant ${tenant.handle} to connection ${tenant.connection} using database ${tenant.db_database}.`)

    this.connection = tenant.connection
    tenant.connect()
    await super.handle()
    tenant.closeConnection()
  }
}

import Tenant from 'App/Models/Tenant'
import {DatabaseContract} from '@ioc:Adonis/Lucid/Database'
import Rollback from '@adonisjs/lucid/build/commands/Migration/Rollback'
import {flags, Kernel} from '@adonisjs/ace/build'
import {inject} from '@adonisjs/fold/build'
import {ApplicationContract} from '@ioc:Adonis/Core/Application'

@ inject([null, null, 'Adonis/Lucid/Database'])
export default class RollbackTenantMigrations extends Rollback {
  public static commandName = 'migration:tenants:rollback'
  public static description = 'Rollback the tenant migrations.'

  /**
   * Custom connection for running migrations.
   */
  @flags.string({ description: 'Define a custom database connection', alias: 'c' })
  public connection: string

  /**
   * Force run migrations in production
   */
  @flags.boolean({ description: 'Explictly force to run migrations in production' })
  public force: boolean

  /**
   * Perform dry run
   */
  @flags.boolean({ description: 'Print SQL queries, instead of running the migrations' })
  public dryRun: boolean

  /**
   * Define custom batch, instead of rolling back to the latest batch
   */
  @flags.number({
    description: 'Define custom batch number for rollback. Use 0 to rollback to initial state',
  })
  public batch: number

  /**
   * Only rollback a specific tenant.
   */
  @flags.string({description: 'Supply a tenant handle to limit the rollback to a specific tenant.'})
  public tenant: string

  /**
   * This command loads the application, since we need the runtime
   * to find the migration directories for a given connection
   */
  public static settings = {
    loadApp: true,
  }

  constructor (app: ApplicationContract, kernel: Kernel, db: DatabaseContract) {
    super(app, kernel, db)
  }

  /**
   * Fetch the tenant(s) and migrate them.
   */
  public async handle () {
    //Handle a single tenant
    if (this.tenant) {
      const tenant = await Tenant.findByHandle(this.tenant)
      await this.handleTenant(tenant)
      return
    }

    //Handle all tenants
    const tenants = await Tenant.all()
    for (const tenant of tenants) {
      await this.handleTenant(tenant)
    }
  }

  public async handleTenant (tenant: Tenant) {
    console.log(`Rolling back tenant ${tenant.handle} migrations for connection ${tenant.connection} using database ${tenant.db_database}.`)

    this.connection = tenant.connection
    tenant.connect()
    await super.handle()
    tenant.closeConnection()
  }
}

import {DateTime} from 'luxon'
import {column, hasOne, scope} from '@ioc:Adonis/Lucid/Orm'
import Provider from 'App/Models/Provider'
import {HasOne} from '@ioc:Adonis/Lucid/Relations'
import ApiModel from 'App/Models/ApiModel'
import Config from '@ioc:Adonis/Core/Config'
import Application from '@ioc:Adonis/Core/Application'
import Database, {
  MysqlConfig,
  SqliteConfig,
} from '@ioc:Adonis/Lucid/Database'

export default class Tenant extends ApiModel {
  @column({isPrimary: true})
  public id: number

  @column()
  public handle: string

  @column()
  public connection: string

  @column()
  public db_host: string

  @column()
  public db_database: string

  @hasOne(() => Provider)
  public provider: HasOne<typeof Provider>

  @column.dateTime({autoCreate: true})
  public createdAt: DateTime

  @column.dateTime({autoCreate: true, autoUpdate: true})
  public updatedAt: DateTime

  public static whereHandle = scope((query, handle: string) => {

    query.where('handle', handle)

    return query
  })

  public static async findByHandle (handle: string): Promise<Tenant> {
    // @ts-ignore
    return super.query().where('handle', handle).first()
  }

  public createConnection (): MysqlConfig | SqliteConfig {
    const migrations = {
      disableTransactions: false,
      paths: [Application.appRoot + '/database/tenant/migrations'],
      tableName: 'adonis_schema',
      disableRollbacksInProduction: true,
    }

    if (Config.get('app.env') === 'testing') {
      return {
        client: 'sqlite',
        connection: {
          filename: Application.tmpPath(`${this.db_database}.sqlite3`),
        },
        useNullAsDefault: true,
        healthCheck: false,
        migrations,
      }
    }
    return {
      client: 'mysql' as const,
      connection: {
        host: this.db_host,
        port: 3306,
        user: Config.get('database.connections.mysql.connection.user'),
        password: Config.get('database.connections.mysql.connection.password'),
        database: this.db_database,
      },
      migrations,
    }
  }

  public connect () {
    if (!Database.manager.has(this.connection)) {
      Database.manager.add(this.connection, this.createConnection())
      Database.manager.connect(this.connection)
    }
  }

  public closeConnection () {
    if (Database.manager.has(this.connection)) {
      Database.manager.close(this.connection, true)
    }
  }
}

[sokhai-cambodia]

@incraigulous I am now working on multi-tenant too and you answer will save me later. big thank bro

[carlmathisen]

@incraigulous Thank you so much, these snippets are a great starting point for a multitenancy Adonis setup.

[w33zy]

This solution ought be expanded into a full entry in the official documentations.

[leognmotta]

@incraigulous

What is it that you do to migrate your tenants on the fly? do you use something like execa or node's child_process?

[thetutlage]

Lemme further explain how multi tenancy is supposed to be handled.

1st, you cannot override the existing config objects, as it will have race condition issues with other requests. Remember, Node.js runs on a single thread and has shared memory.

You have to use the Connection manager to add a new connection on the fly. Relevant docs here: https://docs.adonisjs.com/reference/database/connection-manager

Depending upon the number of tenants of you have, you will sooner or later run into the issue, where you have too many database connections opened from your AdonisJS application. One for each tenant. So you can set the min and max pool connections to 0. It means your AdonisJS app will disconnect with the database as soon as the database connection is idle. Read more about connection pooling here. https://docs.adonisjs.com/guides/database/introduction#connection-pooling.

To answer @leognmotta question. You can run migrations programmatically as well. Relevant docs: https://docs.adonisjs.com/guides/database/migrations#running-migrations-programmatically

[binsarjr]

I have set min and max connection pools to 0. but, getting error Cannot read property 'on' of undefined

TypeError: Cannot read property 'on' of undefined
    at Connection.monitorPoolResources (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Connection/index.js:87:19)
    at Connection.connect (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Connection/index.js:289:18)
    at ConnectionManager.connect (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Connection/Manager.js:120:31)
    at Database.connection (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Database/index.js:108:22)
    at Adapter.modelConstructorClient (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Orm/Adapter/index.js:34:24)
    at Adapter.query (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Orm/Adapter/index.js:40:29)
    at Function.query (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/lucid/build/src/Orm/BaseModel/index.js:160:30)
    at Function.fetchData (/home/binsar/Developments/service/giapps/app/Modules/Siplan/Models/KerusakanJalan.ts:13:22)
    at KerusakanJalanController.index (/home/binsar/Developments/service/giapps/app/Modules/Siplan/Controllers/Http/KerusakanJalanController.ts:11:37)
    at Injector.callAsync (/home/binsar/Developments/service/giapps/node_modules/@adonisjs/fold/build/src/Ioc/Injector.js:124:30) {
  status: 500
}

any solution?

[aayush123]

With the introduction of ALS (Async Local Storage) in Adonis. I believe there's an easier way now.
A middleware that creates a connection for the tenant when a request is received (or perhaps just opens a connection after it is registered once) and also populates the connection name in the HttpContext as a property.
Next, we abstract all our DB operations into a repository class, and each method in the repository extracts the tenant connection name from HttpContext and passes it to the operations we're doing.
That way, our services and controllers are unaware of how we're handling multi-tenancy and just focus on business logic. A middleware and a repository class handle the implementation of multi-tenancy

[thetutlage]

Exactly this. A repository or a service layer on top of models can end up in a really nice workflow for multi-tenancy.

I haven't thought through every aspect of it. So looking for feedback from others who are trying this workflow

[aayush123]

I already have a boilerplate app that has a repository layer baked in on top of models. And then a service layer for business logic, and controllers just handle happy paths and transform responses.
The only thing I think needs a little more thought is how can I retain unit tests for service layer methods (I really like having them, and I haven't found a way to mock HttpContext yet) and whether using a singleton pattern can cause any issues or not.

[fpolli]

Did this ever get further developed? The repository layer idea sounds like Doctrine, but I don't know how to write one.

[fpolli]

aayush123, any chance you could share that code?