Initialize a mysql connection in adonis v4

Question

I want to initialize a MySQL connection with credentials that i'm getting from a master database, is possible to do this on AdonisV4?

using adonis lucid

I can't set the credentials in config, because they're specific for every request that is made.

Answer 1

You would invoke npm library related to mysql in terminal shell.

npm i --save mysql

AdonisJs uses the connection value defined inside the config/database.js file. Thus, you may to create a configuration file for a single database connection in your JavaScript application like following:

You can create an initial environment file for your master database like :

HOST=127.0.0.1
PORT=3306
NODE_ENV=development
CACHE_VIEWS=false

For several connections, you would setup your config file like this:

'use strict'
const Env = use('Env')
const Helpers = use('Helpers')

module.exports = {

connection: Env.get('DB_CONNECTION', 'mysql1'),
  
  mysql1: {
    client: 'mysql',
    connection: {
      host: Env.get('DB_HOST', 'database_host_1'),
      port: Env.get('DB_PORT', '3306'),
      user: Env.get('DB_USER', 'my_database_user_1'),
      password: Env.get('DB_PASSWORD', 'OhMyAdonis'),
      database: Env.get('DB_DATABASE', 'adonis')
    }
  },
  
  mysql2: {
    client: 'mysql',
    connection: {
      host: Env.get('DB_HOST', 'database_host_2'),
      port: Env.get('DB_PORT', '3306'),
      user: Env.get('DB_USER', 'my_database_user_2'),
      password: Env.get('DB_PASSWORD', 'OhMySecondAdonis'),
      database: Env.get('DB_DATABASE', 'another_adonis')
    }
  }
  
}

So, you can select any of the connections defined by the database configuration file at runtime with:

Database
  .connection('mysql1')
  .table('users')

Database
  .connection('mysql2')
  .table('users')

However, if yo have a lot of databases, this approach impacts the RAM server. So, if you have a master database like your case, choose connection dynamically following an ORM for Node.JS using a SQL query builder like Objection.js with knex - based support for MySQL.

A multitenancy approach could be adopted from here.