Lucid: Being able to change connection (another database)

[JoaoCnh]

Inspiration: Objection JS multi-tenancy with database per tenant support

http://vincit.github.io/objection.js/#multi-tenancy

It would work similar to

MyModel.connection("tenant_database") or with an object or something.

I talked about it in the forums

https://forum.adonisjs.com/t/lucid-database-change-database-when-running-query/776/2

[molobala]

That will be realy greate!

I'm working on a multi tenant web application too, and for now I use a single database for all my tenants!

[thetutlage]

Also with Objection.js, they should an example, where they attach models to the request object.

I am not sure how great that is? Never used Objection.js myself, but interested to know everyone thoughts on same.

Also dynamic connection is just one part of the puzzle, the multi tenancy has lot more to manage. For example:

1. Having different database for each client, means too many open connections.
2. Do we want to close the connection after each request and re-open a new one?

[molobala]

Here is the problem @thetutlage .
Imagine we have millions tenants, that means millions of db connections opened and that could be problematic.

I think Objection.js caches db connections instead of opening a new one for each request

[thetutlage]

I think Objection.js caches db connections instead of opening a new one for each request

Then Objection.js will cache million opened connections right? ( assuming million tenants )

[molobala]

That's why I say it will be problematic, I'm not sure we could have so many opened files on most of OS

[thetutlage]

Yeah, that is the problem.

[molobala]

The only thing to do in my opinion is to virtualize those databases, and extend lucid model so we can have a control on which data to operate
By virtualize I mean have a single db connection (Like your philosophy for adonis-websocket ), but intstruct Lucid Model, to operate only on the data that belong to a specifique tenant

[RomainLanz]

The best way to handle correctly multi-tenancy application would be to deploy a complete separate application (via Docker) for each customer.

[thetutlage]

Yeah but again as I said, it's one part of the puzzle.

1. If I get it right, multi-tenant apps involves a lot from compliance point of view too. For example: At times clients want their data backup when they leave your product.
2. And data backup includes SQL dump + their uploaded files.

I believe deploying an app inside a Docker container for each client makes more sense. Since their content is sandboxed, and if one server crashes, it keeps others safe.

[molobala]

I believe deploying an app inside a Docker container for each client makes more sense. Since their content is sandboxed, and if one server crashes, it keeps others safe

So that is not multi tenancy anymore, because we have a multiple instance of the application , that is a single tenancy

[JoaoCnh]

We can also take a look at how the apartment gem for rails manages the connections

https://github.com/influitive/apartment

[JoaoCnh]

And regarding the docker containers that is not considered a "correct" way. The docker might be good for customizing stuff for each individual client.

You can also have a tenant_id field for each table but that doesn't fit every requirement.

The other solution is having multiple databases for multiple tenants that fits other requirements.

Point is, everyone has different requirements and we can aim to be flexible to fit all.

[thetutlage]

There is no thing called “one size fit all”

[JoaoCnh]

I know but it's not one size. Can be plug and play like the apartment gem. I'm just giving suggestions.

I've never done anything at this sort of level and I would actually like to know how one would handle it. The apartment gem handles it pretty well running migations automatically and everything. I will look at it to see how

[visualcookie]

I would highly like to see multi-tenancy as I'm currently developing an app, that needs multi-tenancy where each tenant has it's own database.

I'm currently checking out multiple webframeworks and for now only found hyn/multi-tenant for Laravel, which handles the registration of hosts to use a subdomain for the tenant and automatically runs the needed migrations for the tenant.

Maybe you can take the above named package as reference for Adonis.

[alexechdev]

You can do something like this:

database.js

...

anotherConnection: {
  client: 'mysql',
  ...
}

...

User.js

const config = use('Config')

...
static get connection() {
    return this.useConnection !== "undefined" ? this.useConnection : config.get('database.connection')
  }

  static setUseConnection(access) {
    return access
  }

...

UserController.js

...

async index() {
  User.useConnection = "anotherConnection"
  const user = await User.all()
}

...

This work for me.

[thetutlage]

I don't think, this is actionable right now. As I stated earlier, the process of creating multi-tenant apps is beyond database connection.

Also I have not seen any database libraries in Node.js doing it properly to handle scale.

I need my time to research and build proper structure around it. Added to my personal todo and closing it.

[carlsonorozco]

I solved this by creating a Database Trait

```'use strict'

class DbConnectionRoutify {
register (Model) {
Object.defineProperty(Model, 'connection', {
get: () => {
return this.connection
},
set: connection => {
this.connection = connection
}
})

Model.addHook('beforeCreate', () => {
  this.connection = 'db_writer'
})
Model.addHook('beforeUpdate', () => {
  this.connection = 'db_writer'
})
Model.addHook('beforeSave', () => {
  this.connection = 'db_writer'
})
Model.addHook('beforeDelete', () => {
  this.connection = 'db_writer'
})

Model.addHook('afterCreate', () => {
  this.connection = 'db_reader'
})
Model.addHook('afterUpdate', () => {
  this.connection = 'db_reader'
})
Model.addHook('afterSave', () => {
  this.connection = 'db_reader'
})
Model.addHook('afterDelete', () => {
  this.connection = 'db_reader'
})
Model.addHook('afterFind', () => {
  this.connection = 'db_reader'
})
Model.addHook('afterFetch', () => {
  this.connection = 'db_reader'
})
Model.addHook('afterPaginate', () => {
  this.connection = 'db_reader'
})

}
}

module.exports = DbConnectionRoutify

Then on model boot add this 

static boot () {
super.boot()
this.addTrait('DbConnectionRoutify')
}

Then add this into your config/database.js

db_writer: {
client: 'mysql',
connection: {
host: Env.get('DB_WRITER_HOST', 'localhost'),
port: Env.get('DB_WRITER_PORT', ''),
user: Env.get('DB_WRITER_USER', 'root'),
password: Env.get('DB_WRITER_PASSWORD', ''),
database: Env.get('DB_WRITER_DATABASE', 'adonis')
},
debug: Env.get('DB_DEBUG', false)
},

db_reader: {
client: 'mysql',
connection: {
host: Env.get('DB_READER_HOST', 'localhost'),
port: Env.get('DB_READER_PORT', ''),
user: Env.get('DB_READER_USER', 'root'),
password: Env.get('DB_READER_PASSWORD', ''),
database: Env.get('DB_READER_DATABASE', 'adonis')
},
debug: Env.get('DB_DEBUG', false)
},
```

[deye9]

I solve this by emitting the domain from my routes and in my DB file i load the connection string of the DB and voila am good. But ideally the best solution will be to have the ability to change the database dynamically per instance provided the databases are on the same server.

E.g Am on connection A which uses testDB and want to switch to tenantA on the same DB server, there should be a change database command that reuses the default connection and only changes the database i need to access.

[MiguelMelo]

@carlsonorozco Hello Carlson, how would I get the name of the connection in this trait? Example: inside my request I passed a "db" value, there inside the trait would I have to get this value and put it inside the hooks?

[elviskudo]

You can do something like this:

database.js

...

anotherConnection: {
  client: 'mysql',
  ...
}

...

User.js

const config = use('Config')

...
static get connection() {
    return this.useConnection !== "undefined" ? this.useConnection : config.get('database.connection')
  }

  static setUseConnection(access) {
    return access
  }

...

UserController.js

...

async index() {
  User.useConnection = "anotherConnection"
  const user = await User.all()
}

...

This work for me.

Hi alex,
I tried your code, but output still like this

invalid connection type

if i log the User model in index, result like this:

{ [Function: Order]
  '$booted': true,
  '$hooks':
   { before:
      Hooks {
        _events: [Array],
        _aliases: [Object],
        _aliasEvents: [Array],
        _handlers: {} },
     after:
      Hooks {
        _events: [Array],
        _aliases: [Object],
        _aliasEvents: [Array],
        _handlers: {} } },
  '$queryListeners': [],
  '$globalScopes': [],
  QueryBuilder: null }

there is no variable or function name useConnection,
please give me an advice, thanks

[carlsonorozco]

I solve this by emitting the domain from my routes and in my DB file i load the connection string of the DB and voila am good. But ideally the best solution will be to have the ability to change the database dynamically per instance provided the databases are on the same server.

E.g Am on connection A which uses testDB and want to switch to tenantA on the same DB server, there should be a change database command that reuses the default connection and only changes the database i need to access.

Hi @MiguelMelo,

See my updated code utilising AWS Aurora Database

'use strict'

class DbConnectionRoutify {
  register (Model) {
    Object.defineProperty(Model, 'connection', {
      get: () => {
        return this.connection
      },
      set: connection => {
        this.connection = connection
      }
    })

    const replicationLag = 50
    const sleep = milliseconds => {
      return new Promise(resolve => setTimeout(resolve, milliseconds))
    }
    const setWriterDbConnection = () => {
      this.connection = 'aurora_writer'
    }
    const setReaderDbConnection = async (delay = 0) => {
      await sleep(delay)
      this.connection = 'aurora_reader'
    }

    Model.addHook('beforeCreate', setWriterDbConnection)
    Model.addHook('beforeUpdate', setWriterDbConnection)
    Model.addHook('beforeSave', setWriterDbConnection)
    Model.addHook('beforeDelete', setWriterDbConnection)

    Model.addHook('afterCreate', setReaderDbConnection.bind(this, replicationLag))
    Model.addHook('afterUpdate', setReaderDbConnection.bind(this, replicationLag))
    Model.addHook('afterSave', setReaderDbConnection.bind(this, replicationLag))
    Model.addHook('afterDelete', setReaderDbConnection.bind(this, replicationLag))
    Model.addHook('afterFind', setReaderDbConnection)
    Model.addHook('afterFetch', setReaderDbConnection)
    Model.addHook('afterPaginate', setReaderDbConnection)
  }
}

module.exports = DbConnectionRoutify

[wixys]

Hi there,

Why don't use a middleware?, getting a param or segment of the URL to define a database name to set the connection.

[madrussa]

I thought I'd add my multi tenancy implementation here as it might prove useful for others. We currently have to separate our customer data into their own databases, but not for everything. Our microservices need to know where to store their data with our many IoT devices reporting in.

I choose to only perform model actions on a tenant database via callbacks and proxies. We get a Tenant object from the tenant manager which allows us to perform actions on the tenant in a manner similar to a transaction callback.

const User = use('App/Models/User');
const Device = use('App/Models/Device');
const TenantManager = use ('App/Services/TenantManager');

const tenant = TenantManager.tenant('adonis');

await tenant.perform(async (TenantUser) => {
     const newUser = new TenantUser();
     newUser.merge({ name: 'Joe' });
     await newUser.save();
}, User);

The models passed into the perform method are altered to use the tenants connection. This keeps things separate and ensures that us developers can only interact with a tenant database in a controlled way.

We can choose to use the connection to the tenant if needed. There are also shortcuts to the static calls.

await tenant.performWith(async (connection, TenantUser) => {
    await connection.raw(' ...sql...');
    await connection.select('*').from('table');
}, User);

await tenant.save(newUser); // Saves the user model into the tenant database
const joe = await tenant.find(User, 10); // Locates Joe from the tenant database
const bob = await tenant.findBy(User, 'email', '[email protected]'); // Locates Bob from the tenant database

The tenant manager allows us to use middleware to intercept different domains or routes so we can inject the tenant into the http context ready to use within the controller.

The tenant manager handles different connections by tampering with the config. We inject new connection configs when a customer is required and then call the new connection. Our manager keeps track of the open connections and will close stale connections and/or handle a max number of connections by prioritising those frequently used. Although this isn't an ideal way to handle things it's the easiest by far. It also allows us to inject new customers at runtime as our service listens for when new customers are created.

Example connection logic

createConnection ({ database }) {
    // Populate the database config with our required connection
    const connectionConfig = { ...this.getDefaultConfig() };
    connectionConfig.connection.database = database;
    this.config.set(`database.${database}`, connectionConfig);

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

    // Tenant class provides methods to perform actions on the tenant database,
    // the callback is called when ever an action is performed so we can keep track
    // of active connections
    const tenant = new Tenant(database, () => this.track(database));

    this.tenants[database] = {
        tenant,
        database,
        // keep track of calls and set the expiry time so we can remove old connections
        expires: this.getExpiryDate(),
        calls: 0,
    };

    return tenant;
}

We've also had a lot of fun implementing multi tenancy database migrations but I'm not too happy with the solution, although it does work :)

[FabioBachi]

@madrussa Any chance that you can share more details on your solution? Me and my team are really struggling with this. We also have multiple databases tenancy, and the contents from different databases are getting mixed up on requests.

I would really appreciate some help with this matter.

Thank you.

[madrussa]

The trick is to always use transactions with async/await. I too had some database leakage on my initial setup but this was removed when I found I had a function which wasn't asynchronous. We're currently writing 100k of records across 10 tenant databases per day without issue a the moment so it's definitely possible. I'll create a small repo with some examples later today (BST) of our usage.

[madrussa]

See my quick example gist, hopefully it helps.
https://gist.github.com/madrussa/9c0500bc2ecb78ea6ecfc82328a7ce98

[FabioBachi]

@madrussa thank you so much for your help. We will give it a try in the next few days.

Thank you!

[mortenko]

Hi guys I have same problem ..here is my solution:

Model:

class Theme extends Model {
  static boot() {
    super.boot();
    this.addTrait('DbConnection');
  }

  static get connection() {
    return this.conn;
  }
}

Trait:

class DbConnection {
  register(Model) {
    Model.dbConnection = function (conn = 'pg') {
      this.conn = conn;
      return this;
    };
  }
}

module.exports = DbConnection;

Some Controller method (you pass to dbConnection the type of db, which you are using):

async controllerMethod() {
const result = await Theme.dbConnection('mysql').query().select(...).fetch();
}