Lucid join across multiple databases

I am starting to write apps in AdonisJS for a new Job I have started.

The data I am working with is in MSSQL and spread across a number of databases on the same server. As an example, the following SQL brings in data from two databases PROCUREMENT and LNDW

select * from PROCUREMENT.static.ln_local_buyers lb
join LNDW.dbo.TDIPU001_ITEM_PURCHASE_DATA ipd on ipd.Buyer = lb.Buyer

I have setup a working connection to MSSQL, but the database is set as PROCUREMENT, is it possible to setup the other databases as seperate connections so lucid can create a join like in the example code above?

1 Like

Hi @simonjcarr

You can take a look at https://adonisjs.com/docs/4.1/lucid#_convention_over_configuration
You can specify tables and connections in Lucid models.

I have never used such thing myself, but you should be able to add multiple connections into your /config/database.js and then specify connections in models

Also if you need different connection for pivot table you can use pivot models
https://adonisjs.com/docs/4.1/relationships#_pivotmodel

In there you can specify database connections

Thanks @McSneaky, your solution did not work, but in playing around with it I found the solution anyway.

The solution is that you only need a single connection, but in your models you just need to make sure you fully qualify the table name

i.e.

static get table() {
    return "LNDW.dbo.TDIPU001_ITEM_PURCHASE_DATA"
  }

As long as the database exists on the same server, it does matter which database you initially connect to, a fully qualified name will do what it says on the tin.

By doing it this way, joins across databases and Lucid relationships continue to work.

1 Like

Aah nice!

For some reason I read this:

And thought you have multiple separate database servers running on same machine =)

But glad you got it working