Sailsjs fetch data from two different mysql databases

Question

I'm building a rest api using sails js v 1.x I need to connect two mysql database so I have defined them in config/datastores.js file life this:

module.exports.datastores = {
 default: {
      adapter: require('sails-mysql'),
      url: 'mysql://root:12345@192.168.0.5:3306/test',
    },
    mysqldb: {
      adapter: require('sails-mysql'),
      url: 'mysql://root:12345@192.168.0.5:3306/test2',
    },

};

In my controller, I have this function which needs to get data by joining the database test as well as test2

module.exports = {

    index: function (req, res) {
        User.getDatastore().sendNativeQuery("SELECT * from test.users u INNER JOIN test2.users t ON u.id=t.id limit 10",function(err, rawResult) {
            res.send(rawResult);
        })
    },

};

But this gives me an error :

{
   "code": "ER_NO_SUCH_TABLE",
    "errno": 1146,
    "sqlMessage": "Table 'test.users' doesn't exist",
}

Also I have a blank User model and execution of raw sql queries work perfecty when the query is like select * from users (it uses the default database i.e test) How do I achieve this kind of query by connecting more than one MySQL database in sails js?

select * from users works because `table users` exist, but table `test.users` obviously doesn't exist on default table. If i understand, u want join 2 tables from different databases? — Dejan Kubaša, Mar 15 '19 at 08:59

node_man · Answer 1 · 2019-03-15T09:12:24.067

I was able to solve the issue this way :

my config.datastore file :

module.exports.datastores = {
 default: {
      adapter: require('sails-mysql'),
      url: 'mysql://root:12345@192.168.0.5:3306/',
    },

};

My controller :

module.exports = {

    index: function (req, res) {
        User.getDatastore().sendNativeQuery("SELECT * from test.users u INNER JOIN test2.users t ON u.id=t.id limit 10",function(err, rawResult) {
            res.send(rawResult);
        })
    },

};

Was having trouble figuring it out at first as there is no such example showing a join on multiple tables from different databases when using raw MySQL queries.

The only change I did is I just removed the database name from the default connection URL. Now I'm able to access all the databases on this particular server and also able to join multiple databases.

my config: ` default: { adapter: "sails-mysql", host: "127.0.0.1", port: "3306", user: "root", password: "mysql", database: "erp" } ` my query: ` let test = await Document.getDatastore().sendNativeQuery(" SELECT d.id, dl.name_hrv from erp.doc_documents d JOIN crm.common_documentLabels dl ON dl.id = d.label"); ` And all works, u can try this type of configuration of database. Let us know is this work for u. — Dejan Kubaša, Mar 15 '19 at 09:12

Sailsjs fetch data from two different mysql databases

1 Answers1