[Lucid] [v6] Switching Connection with the Model

[leofmarciano]

I am conducting some tests with AdonisJS and would like to inquire about the multi-tenancy handling capabilities of Lucid/AdonisJS. We are considering using the framework for the largest group of universities in Latin America, and the framework has significantly captured my attention. However, there is not much content available regarding its operation with multi-tenancy.

Currently, I am attempting to create a Proof of Concept (PoC) that adheres to the following premises:

• The database needs to be completely isolated for each institution, thus requiring a change of the database connection for each origin.
• This feature will need to be utilized in a Kubernetes environment, hence without LocalStorage.
• There should be a "master" database, and when no connection is specified, it should use this master database to proceed with the routine.

In this context, I have successfully tested some use cases, for example, using the domain to determine the tenant. I implemented a middleware that identifies which tenant the connection refers to and then calls the addTenantConnection function. This middleware is used to populate the tenant information in the HttpContext, which I also use to successfully add a connection in the config/database.ts.

//config/database.ts
export async function addTenantConnection(database: string) {
  const tenant = await db.connection('master').from('tenants').where('tenant', database).first()
  const tenantConnectionName = `tenant_${database}`
  if (!tenant) {
    throw new Error('Tenant not found')
  }

  const tenantConnection: PostgreConfig = {
    client: 'pg',
    connection: {
      host: env.get('DB_HOST'),
      port: env.get('DB_PORT'),
      user: env.get('DB_USER'),
      password: env.get('DB_PASSWORD'),
      database: tenant.databaseName,
    },
    migrations: {
      naturalSort: true,
      paths: ['database/migrations/tenant'],
    },
  }

  dbConfig.connections[tenantConnectionName] = tenantConnection
  db.manager.add(tenantConnectionName, tenantConnection)

  console.log('') // line-break
  console.log('\x1b[34m%s\x1b[0m', `[APPLICATION]`, `Added connection for tenant ${database}`)
  dbConfig.connection = tenantConnectionName
  return dbConfig
}

I also succeeded in implementing a migration routine for tenants using the above-described feature, also utilizing the MigrationRunner available within the Lucid package.

//commands/create-tenant.ts
import { BaseCommand } from '@adonisjs/core/ace'
import type { CommandOptions } from '@adonisjs/core/types/ace'
import db from '@adonisjs/lucid/services/db'
import { addTenantConnection } from '#config/database'
import { MigrationRunner } from '@adonisjs/lucid/migration'
import config from '@adonisjs/core/services/config'
import app from '@adonisjs/core/services/app'
import logger from '@adonisjs/core/services/logger'

interface Tenant {
  tenant: string
  id: string
  database: string
  created_at: string
  updated_at: string
}

export default class MigrateTenant extends BaseCommand {
  static commandName = 'tenant:migrate'
  static description = 'Run migrations for all tenants'

  static options: CommandOptions = {
    startApp: true,
    allowUnknownFlags: false,
    staysAlive: true,
  }

  private async migrate(requestTenant: Tenant) {
    console.log(requestTenant)
    await db.manager.close(`tenant_${requestTenant.tenant}`)

    config.set(
      `database.connections.tenant_${requestTenant.tenant}.connection.database`,
      `tenant_${requestTenant.tenant}`
    )
    await db.manager.connect(`tenant_${requestTenant.tenant}`)

    const migrator = new MigrationRunner(db, app, {
      direction: 'up',
      dryRun: false,
      connectionName: `tenant_${JSON.stringify(requestTenant.tenant).replace(/"/g, '')}`,
    })
    await migrator.run()
    const migratedFiles = await migrator.getList()

    for (const migrated of migratedFiles) {
      const status = migrated.status
      console.log('\x1b[33m%s\x1b[0m', `[${status}]`, migrated.name)
    }
  }

  async runMigrationsForTenants() {
    const tenants = await db.connection('master').from('tenants').select('*')
    console.log(
      '\x1b[31m%s\x1b[0m',
      `[MIGRATION]`,
      `Running migrations for ${tenants.length} tenants`
    )

    for (const [index, tenant] of tenants.entries()) {
      if (index) console.log('')
      logger.info(`[TENANT]: ${tenant.tenant}`)
      await addTenantConnection(tenant.tenant)
      await this.migrate(tenant)
    }
  }

  async run() {
    this.runMigrationsForTenants()
      .then(() => {
        console.log('')
        console.log('\x1b[32m%s\x1b[0m', `[SUCCESS]`, 'All tenants migrated')
        process.exit(0)
      })
      .catch((error) => {
        console.error(error)
        process.exit(1)
      })
  }
}

Although the code may not be elegant, it is functional, and I plan to refine it later.

Now, moving on to my question: how can I use this capability to utilize the models available in App/models with the active record pattern? I searched the documentation, but it only references how to switch the connection for the queryClient, which is a distinct class from BaseModel, therefore not allowing me to reuse the documentation. I noticed that the connection property exists, but it only changes a property, and the connection still persists on 'master':

  db.connection('tenant_test1')
      User.connection = 'tenant_test1'

      console.log(await db.rawQuery('SELECT current_database();'))
      const user = await User.findBy('email', email)

      if (!user) throw an InvalidCredentialsException()

      if (!user.emailVerified) throw an EmailNotVerifiedException()

Whenever I execute this route, I notice that:

• the users are only considered from 'master', not changing the database connection, even though such a property exists.
• the database attempts to execute the query, as evidenced by the return message: "message": "select * from "users" where "email" = $1 limit $2 - relation "users" does not exist",

Another point I noticed is that the master database has the same email. I tested this, and even with such information, nothing returns.

Does this functionality exist? Or in my case, will I be forced to directly make the queries?

---

[brunojoao]

Up,

V5 uses Database.connection for change, in version v6 it removed everything and didn't document it

[Banashek]

There's a specific note on the connection property in the docs indicating not to use it for runtime use: ref.

This property is more for if you wanted a specific model (or set of models) to use their own database.

All of the base model methods take an additional optional options property (example).

What you can do is pass to this a connection retrieved from the db.connection function like so:

Here I have two databases:

The first database (connection1) has a user in it while the second (connection2) doesn't (just to show that setting the connection on a per-query basis works as expected).