Plug multiple SQL databases

It's becoming quite common to have multiple databases when building a web application. Especially when designing your app with micro services.

Whenever possible, we strongly advice to have one and only one SQL database to facilitate native joins between your relationships.

Lumber relies on the Sequelize ORM to access and manipulate your data. With Sequelize, you can easily connect to multiple databases and create your models accordingly.

1. Update the models/index.js file

To do so, you first need to update the file models/index.js which is where the logic to connect to the database(s) and load your models is implemented.

models/index.js
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
let dbs = [{
name: 'demo',
connectionString: process.env.DATABASE_DEMO
}, {
name: 'billing',
connectionString: process.env.DATABASE_BILLING
}];
let sequelize = {};
let db = {};
let models = {};
dbs.forEach((info) => {
models[info.name] = {};
let databaseOptions = {
logging: console.log,
pool: { maxConnections: 10, minConnections: 1 },
dialectOptions: {}
};
if (process.env.DATABASE_SSL) {
databaseOptions.dialectOptions.ssl = true;
}
if (process.env.DATABASE_ENCRYPT) {
databaseOptions.dialectOptions.encrypt = true;
}
let connection = new Sequelize(info.connectionString, databaseOptions);
sequelize[info.name] = connection;
fs
.readdirSync(path.join(__dirname,info.name))
.filter(function (file) {
return (file.indexOf('.') !== 0) && (file !== 'index.js');
})
.forEach(function (file) {
try {
var model = connection['import'](path.join(__dirname, info.name, file));
sequelize[model.name] = model;
models[info.name][model.name] = model;
} catch (error) {
console.error('Model creation error: ' + error);
}
});
Object.keys(models[info.name]).forEach(function(modelName) {
if ('associate' in models[info.name][modelName]) {
models[info.name][modelName].associate(sequelize[info.name].models);
}
});
});
db.Sequelize = Sequelize;
db.sequelize = sequelize;
module.exports = db;

Between the line 5 and 11, edit the dbs variable according to your databases. In this example, we have two databases.

  • demo where the connection string is set to the environment variable DATABASE_DEMO

  • billing where the connection string is set to the environment variable DATABASE_BILLING

You're free to use any alias in the name option but you need to make sure the connectionString value is set to a database connection string.

.env
DATABASE_DEMO=postgres://forest:secret@localhost:5416/forest_demo
DATABASE_BILLING=postgres://forest:secret@localhost:5417/forest_billing
FOREST_ENV_SECRET=...
FOREST_AUTH_SECRET=...

2. Create a directory for each database's models

At the moment, you should have a models/ directory that looks like this:

Command line
Output
ls -l models
total 80
-rw-r--r-- 1 seyz staff 741 Oct 12 12:19 addresses.js
-rw-r--r-- 1 seyz staff 658 Oct 12 12:19 appointments.js
-rw-r--r-- 1 seyz staff 1154 Oct 19 14:22 companies.js
-rw-r--r-- 1 seyz staff 606 Oct 12 12:19 customers.js
-rw-r--r-- 1 seyz staff 601 Oct 12 12:19 deliveries.js
-rw-r--r-- 1 seyz staff 408 Oct 12 12:19 documents.js
-rw-r--r-- 1 seyz staff 1281 Oct 12 12:19 index.js
-rw-r--r-- 1 seyz staff 1114 Oct 12 12:19 orders.js
-rw-r--r-- 1 seyz staff 543 Oct 12 12:19 products.js
-rw-r--r-- 1 seyz staff 1239 Oct 12 12:19 transactions.js

Move all your models file to a directory named exactly by the database alias name you chose on the models/index.js file (Step 1). In our case here, it's demo.

Command line
Output
tree models
models
├── demo
│   ├── addresses.js
│   ├── appointments.js
│   ├── companies.js
│   ├── customers.js
│   ├── deliveries.js
│   ├── documents.js
│   ├── orders.js
│   ├── products.js
│   └── transactions.js
└── index.js

Make sure you keep the index.js file in the models/ directory.

Then, create a directory for each of your database and create the Sequelize models that map your tables.

You can take inspiration from the existing models to create your new ones or read the Sequelize documentation about Model definition.

module.exports = (sequelize, DataTypes) => {
const Model = sequelize.define('billings', {
'customer_id': {
type: DataTypes.INTEGER,
},
'VAT': {
type: DataTypes.STRING,
},
'address': {
type: DataTypes.STRING,
},
'zip_code': {
type: DataTypes.STRING,
},
'state': {
type: DataTypes.STRING,
},
'city': {
type: DataTypes.STRING,
},
}, {
tableName: 'billings',
underscored: true
});
Model.associate = (models) => {
};
return Model;
};

3. Update the Forest middleware options

You need to indicate to the Forest middleware all the database connections. To do so, update the middlewares/forestadmin/index.js file and add/change the options sequelize and connections.

middlewares/forestadmin/index.js
const models = require('../../models');
const requireAll = require('require-all');
module.exports = function (app) {
require('lumber-forestadmin').run(app, {
modelsDir: __dirname + '/../../models',
envSecret: process.env.FOREST_ENV_SECRET,
authSecret: process.env.FOREST_AUTH_SECRET,
sequelize: models.Sequelize,
connections: [
models.sequelize.demo,
models.sequelize.billing
],
});
requireAll({
dirname: __dirname + '/../../routes',
recursive: true,
resolve: Module => app.use('/forest', Module)
});
});

4. Replace models.<modelName> everywhere

You can't use the syntax models.<modelName> anymore to trigger CRUD operations on your data. On our example, the action Mark as live is broken.

stacktrace
POST /forest/actions/mark-as-live 500 10.501 ms - 3264
TypeError: Cannot read property 'update' of undefined
at router.post (/Users/seyz/workspace/lumber-projects/QuickStart/routes/companies.js:14:6)

To fix this issue, we have to import the databases' connections on top of each file and change the syntax of the models' references like this:

routes/companies.js
const express = require('express');
const router = express.Router();
const Liana = require('forest-express-sequelize');
const models = require('../models');
// ...
// Declare the database connections.
const dbDemo = models.sequelize.demo.models;
const dbBilling = models.sequelize.billing.models;
router.post('/actions/mark-as-live', Liana.ensureAuthenticated, (req, res) => {
let companyId = req.body.data.attributes.ids[0];
return dbDemo.companies // Change the syntax here.
.update({ status: 'live' }, { where: { id: companyId }})
.then(() => res.send({ success: 'Company is now live!' }));
});

Create a relationship between two models from different databases

The main problem of having two databases is that you cannot rely on SQL joins anymore to browser your data over relationships.

To fix that, we will create a Smart Relationship and code the logic to reconcile the data between our Customers (database demo) and Billings (database: billings).

forest/customers.js
const Liana = require('forest-express-sequelize');
const models = require('../models');
const dbBilling = models.sequelize.billing.models;
Liana.collection('customers', {
fields: [{
field: 'billing',
type: 'String',
reference: 'billings.id',
get: function (customer) {
return dbBilling.billings.findOne({ customer_id: customer.id });
}
}]
});

And of course, you can create the inverse relationship:

forest/billings.js
const Liana = require('forest-express-sequelize');
const models = require('../models');
const dbDemo = models.sequelize.demo.models;
Liana.collection('billings', {
fields: [{
field: 'customer',
type: 'String',
reference: 'customers.id',
get: function (billing) {
return dbDemo.customers.findOne({ id: billing.customer_id });
}
}]
});