adonis multiple database

I have lucid monggo installed, but i have to connect with my MySQL server
so i have to connect multiple databases
primary connection is mongodb
secondary connection is mysql

but i have a problem here, i already added code in my model like this

class Order extends Model {
    static get connection () {
        return 'mysql'
    }
}

but when i try, result like this:

RuntimeException
invalid connection type

how can i fix this?
thanks a lot, youre framework very awesome and helped me

Hi @elviskudo!

Do you have mysql setup in database config file?

yes I already config in env
with
MYSQL_HOST, MYSQL_PORT etc

Got it working meanwhile?

I meant mysql part in config/database.js

yes, already there

Did you ever get to workaround this issue? Even though I discovered that, it was a provider issue. If you remove or swap the lucid-mongo provider to the top in the app.js provider you will get a new error for knex library not supporting mongodb. Which am currently stock at.

I am guessing it maybe impossible to use both lucid-mongo ORM at the same time with the lucid ORM that comes out of the box with framework

So I was able to achieve this after hours of surfing the internet, copying some code and tweaking the framework here and there. Below are the steps I took:

  1. Installed lucid-mongo via npm (but adonis install lucid-mongo should work as well)

  2. Created the mongodb config block in the config/database.js file and added the providers in the start/app.js file as described in the lucid-mongo installation instructions found on github and/or on the npm website

  3. Modified the environment variables in the adonisjs framework’s (my project’s) .env file to contain both database configuration environment variables like so;

DB_CONNECTION=

MYSQL_DB_HOST=mysqlhost
MYSQL_DB_PORT=3306
MYSQL_DB_USER=mysqluser
MYSQL_DB_PASSWORD=mysqlpassword
MYSQL_DB_DATABASE=mysqldatabase

MONGO_DB_HOST=mongohost
MONGO_DB_PORT=27017
MONGO_DB_USER=mongouser
MONGO_DB_PASSWORD=mongpassword
MONGO_DB_DATABASE=mongodatabase
  1. I then used the configurations above in this way in the config/database.js file
 mysql: {
    client: 'mysql',
    connection: {
      host: Env.get('MYSQL_DB_HOST', 'mysql'),
      port: Env.get('MYSQL_DB_PORT', '3306'),
      user: Env.get('MYSQL_DB_USER', 'root'),
      password: Env.get('MYSQL_DB_PASSWORD', ''),
      database: Env.get('MYSQL_DB_DATABASE', 'embooks')
    }
  },

mongodb: {
  client: 'mongodb',
  connectionString: Env.get('DB_CONNECTION_STRING', ''),
  connection: {
    host: Env.get('MONGO_DB_HOST', 'mongo'),
    port: Env.get('MONGO_DB_PORT', 27017),
    username: Env.get('MONGO_DB_USER', ''),
    password: Env.get('MONGO_DB_PASSWORD', ''),
    database: Env.get('MONGO_DB_DATABASE', 'embooks'),
    options: {
      // replicaSet: Env.get('DB_REPLICA_SET', '')
      // ssl: Env.get('DB_SSL, '')
      // connectTimeoutMS: Env.get('DB_CONNECT_TIMEOUT_MS', 15000),
      // socketTimeoutMS: Env.get('DB_SOCKET_TIMEOUT_MS', 180000),
      // w: Env.get('DB_W, 0),
      // readPreference: Env.get('DB_READ_PREFERENCE', 'secondary'),
      // authSource: Env.get('DB_AUTH_SOURCE', ''),
      // authMechanism: Env.get('DB_AUTH_MECHANISM', ''),
      // other options
    }
  }
},
  1. Copied some code from this git opened issue in lucid-mongo repo and followed the instructions as described in the comments

  2. changed the Model alias to MongoModel for lucid-mongo in the LucidMongoProvider file found in node_modules/lucid-mongo/Providers/LucidMongoProvider in other to allow models to extend the appropriate Model classes

 _registerModel () {
    this.app.bind('Adonis/Src/MongoModel', (app) => require('../src/LucidMongo/Model'))
    this.app.alias('Adonis/Src/MongoModel', 'MongoModel')
  }
  1. For cleanness and separation of concerns, I created a new directory in the Models directory and called it Mongo. So as to store models that will be extending the lucid-mongo Model parent class.

  2. Now I created the mongo database models extending the MongoModel alias, while specifying the connection type in both the normal models and the MongoModels like so

const Model = use('MongoModel'); /*Models that will be using mysql database will be using the Model alias instead*/

class Post extends Model{
    
    static get connection(){

        return 'mongodb';  //this will be mysql in the models that will be using mysql
    }
}

module.exports = Post;
1 Like

So, I just made some additional discoveries and I just thought I should share it again. Although I tried to amend my previous answer, but it didn’t work so I decided to add it as a reply to it.

Anyways, I discovered that, I can still run migrations to both MySQL and MondoDB simultaneous by making some additional modifications to the lucid-mongo package and making the migration file to extend the lucid-mongo Schema with a custom alias that I called MongoSchema as follows;

  1. Add the lucid-mongo/providers/MigrationsProvider in the app.js aceProvider block as described in the lucid-mongo installation documentation

  2. Modify the MigrationsProvider as follows;

_registerSchema () {
    this.app.bind('Adonis/Src/MongoSchema', () => require('../src/Schema'))
    this.app.alias('Adonis/Src/MongoSchema', 'MongoSchema')
  }

_

registerCommands () {
    this.app.bind('Adonis/Commands/MongoMigration:Run', () => require('../commands/MigrationRun'))
    this.app.bind('Adonis/Commands/MongoMigration:Rollback', () => require('../commands/MigrationRollback'))
    this.app.bind('Adonis/Commands/MongoMigration:Refresh', () => require('../commands/MigrationRefresh'))
    this.app.bind('Adonis/Commands/MongoMigration:Reset', () => require('../commands/MigrationReset'))
    this.app.bind('Adonis/Commands/MongoMigration:Status', () => require('../commands/MigrationStatus'))
    this.app.bind('Adonis/Commands/MongoSeed', () => require('../commands/Seed'))
  }
_registerMigration () {
    this.app.singleton('Adonis/Src/MongoMigration', (app) => {
      const Config = app.use('Adonis/Src/Config')
      const Database = app.use('Adonis/Src/MongoDatabase')
      const Migration = require('../src/Migration')
      return new Migration(Config, Database)
    })
    this.app.alias('Adonis/Src/Migration', 'Migration')
  }
boot () {
    const ace = require('@adonisjs/ace')
    ace.addCommand('Adonis/Commands/MongoMigration:Run')
    ace.addCommand('Adonis/Commands/MongoMigration:Rollback')
    ace.addCommand('Adonis/Commands/MongoMigration:Refresh')
    ace.addCommand('Adonis/Commands/MongoMigration:Reset')
    ace.addCommand('Adonis/Commands/MongoMigration:Status')
    ace.addCommand('Adonis/Commands/MongoSeed')
  }
  1. Next modify the LucidMongoProvider as well to
_registerDatabase () {
/**
 * I am yet to figure out how to enable hybrid relationship without 
 * reverting the Database registration back to its initial config.
 * Hence anytime you want to enable hybrid relationship, 
 * you may need to take this  back to the initial 
 * database registration, So I will recommend 
 * duplicating this part and committing it 
 * out before modifying it
*/
    this.app.singleton('Adonis/Src/MongoDatabase', (app) => {
      const Config = app.use('Adonis/Src/Config')
      const Database = require('../src/Database/Manager')
      return new Database(Config)
    })
    this.app.alias('Adonis/Src/MongoDatabase', 'Database')
  }
  1. Then modify the lucid-mongo/src/Schema/index.js as follows to;
constructor (Database) {
    Database = use('Adonis/Src/MongoDatabase')
    this.db = Database.connection(this.constructor.connection)
    this._deferredActions = []
  }
  1. Now in your migration file extend the lucid-mongo Schema that was registered earlier as MongoSchema like so;
'use strict'

/** @type {import('lucid-mongo/src/Schema')} */
const MongoSchema = use('MongoSchema')

class CommentSchema extends MongoSchema {
  up () {
    this.create('comments', (collection) => {
      collection.index('title_index', {title: 1})
    })
  }

  down () {
    this.drop('comments')
  }
}

module.exports = CommentSchema

By the way I also tried the hasMany and belongsTo relationship from a model that was using MySQL to a model using mongoDB and it worked. I am yet to try others but I will update this comment as soon as I try anyone of them and it fails.

1 Like