Plug multiple SQL databases

Search…
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 advise 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
1
const fs = require('fs');
2
const path = require('path');
3
const Sequelize = require('sequelize');
4
​
5
let databases = [{
6
  name: 'demo',
7
  connectionString: process.env.DATABASE_DEMO
8
}, {
9
  name: 'billing',
10
  connectionString: process.env.DATABASE_BILLING
11
}];
12
​
13
const sequelize = {};
14
const db = {};
15
const models = {};
16
​
17
databases.forEach((databaseInfo) => {
18
  models[databaseInfo.name] = {};
19
​
20
  const isDevelopment = process.env.NODE_ENV === 'development' || !process.env.NODE_ENV;
21
  const databaseOptions = {
22
    logging: isDevelopment ? console.log : false,
23
    pool: { maxConnections: 10, minConnections: 1 },
24
    dialectOptions: {}
25
  };
26
​
27
  if (process.env.DATABASE_SSL && JSON.parse(process.env.DATABASE_SSL.toLowerCase())) {
28
    databaseOptions.dialectOptions.ssl = true;
29
  }
30
​
31
  const connection = new Sequelize(databaseInfo.connectionString, databaseOptions);
32
  sequelize[databaseInfo.name] = connection;
33
​
34
  fs
35
    .readdirSync(path.join(__dirname, databaseInfo.name))
36
    .filter((file) => file.indexOf('.') !== 0 && file !== 'index.js')
37
    .forEach((file) => {
38
      try {
39
        const model = connection.import(path.join(__dirname, databaseInfo.name, file));
40
        models[databaseInfo.name][model.name] = model;
41
      } catch (error) {
42
        console.error('Model creation error: ' + error);
43
      }
44
    });
45
​
46
  Object.keys(models[databaseInfo.name]).forEach((modelName) => {
47
    if ('associate' in models[databaseInfo.name][modelName]) {
48
      models[databaseInfo.name][modelName].associate(sequelize[databaseInfo.name].models);
49
    }
50
  });
51
});
52
​
53
db.sequelize = sequelize;
54
db.Sequelize = Sequelize;
55
​
56
module.exports = db;
Copied!
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
1
DATABASE_DEMO=postgres://forest:[email protected]:5416/forest_demo
2
DATABASE_BILLING=postgres://forest:[email protected]:5417/forest_billing
3
FOREST_ENV_SECRET=...
4
FOREST_AUTH_SECRET=...
Copied!
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
1
ls -l models 
Copied!
1
total 80
2
-rw-r--r--  1 seyz  staff   741 Oct 12 12:19 addresses.js
3
-rw-r--r--  1 seyz  staff   658 Oct 12 12:19 appointments.js
4
-rw-r--r--  1 seyz  staff  1154 Oct 19 14:22 companies.js
5
-rw-r--r--  1 seyz  staff   606 Oct 12 12:19 customers.js
6
-rw-r--r--  1 seyz  staff   601 Oct 12 12:19 deliveries.js
7
-rw-r--r--  1 seyz  staff   408 Oct 12 12:19 documents.js
8
-rw-r--r--  1 seyz  staff  1281 Oct 12 12:19 index.js
9
-rw-r--r--  1 seyz  staff  1114 Oct 12 12:19 orders.js
10
-rw-r--r--  1 seyz  staff   543 Oct 12 12:19 products.js
11
-rw-r--r--  1 seyz  staff  1239 Oct 12 12:19 transactions.js
Copied!
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
1
tree models 
Copied!
1
models
2
├── demo
3
│   ├── addresses.js
4
│   ├── appointments.js
5
│   ├── companies.js
6
│   ├── customers.js
7
│   ├── deliveries.js
8
│   ├── documents.js
9
│   ├── orders.js
10
│   ├── products.js
11
│   └── transactions.js
12
└── index.js
Copied!
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.
1
module.exports = (sequelize, DataTypes) => {
2
  const Model = sequelize.define('billings', {
3
    'customer_id': {
4
      type: DataTypes.INTEGER,
5
    },
6
    'VAT': {
7
      type: DataTypes.STRING,
8
    },
9
    'address': {
10
      type: DataTypes.STRING,
11
    },
12
    'zip_code': {
13
      type: DataTypes.STRING,
14
    },
15
    'state': {
16
      type: DataTypes.STRING,
17
    },
18
    'city': {
19
      type: DataTypes.STRING,
20
    },
21
  }, {
22
    tableName: 'billings',
23
    underscored: true
24
  });
25
​
26
  Model.associate = (models) => {
27
  };
28
​
29
  return Model;
30
};
Copied!
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.js
1
const requireAll = require('require-all');
2
const chalk = require('chalk');
3
const path = require('path');
4
const Liana = require('forest-express-sequelize');
5
const models = require('../models');
6
​
7
module.exports = async function (app) {
8
  app.use(await Liana.init({
9
    modelsDir: path.join(__dirname, '../models'),
10
    configDir: path.join(__dirname, '../forest'),
11
    envSecret: process.env.FOREST_ENV_SECRET,
12
    authSecret: process.env.FOREST_AUTH_SECRET,
13
    sequelize: models.Sequelize,
14
    connections: [
15
      models.sequelize.demo,
16
      models.sequelize.billing
17
    ],
18
  }));
19
​
20
  console.log(chalk.cyan('Your admin panel is available here: https://app.forestadmin.com/projects'));
21
};
Copied!
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
1
POST /forest/actions/mark-as-live 500 10.501 ms - 3264
2
TypeError: Cannot read property 'update' of undefined
3
    at router.post (/Users/seyz/workspace/lumber-projects/QuickStart/routes/companies.js:14:6)
Copied!
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
1
const express = require('express');
2
const router = express.Router();
3
const Liana = require('forest-express-sequelize');
4
const models = require('../models');
5
// ...
6
​
7
// Declare the database connections.
8
const dbDemo = models.sequelize.demo.models;
9
const dbBilling = models.sequelize.billing.models;
10
​
11
router.post('/actions/mark-as-live', Liana.ensureAuthenticated, (req, res) => {
12
  let companyId = req.body.data.attributes.ids[0];
13
​
14
  return dbDemo.companies // Change the syntax here.
15
    .update({ status: 'live' }, { where: { id: companyId }})
16
    .then(() => res.send({ success: 'Company is now live!' }));
17
});
Copied!
5. Create relationships between two models from different databases
Adding a belongsTo relationship
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 belongsTo Smart Relationship and code the logic to reconcile the data between our Customers (database demo) and Billings (database billings).
forest/customers.js
1
const { collection } = require('forest-express-sequelize');
2
​
3
const dbBilling = models.sequelize.billing.models;
4
​
5
collection('customers', {
6
  fields: [{
7
    field: 'billing',
8
    type: 'String',
9
    reference: 'billings.id',
10
    get: function (customer) {
11
      return dbBilling.billings.findByPk({ customer_id: customer.id });
12
    }
13
  }]
14
});
Copied!
And of course, you can create the inverse relationship:
forest/billings.js
1
const { collection } = require('forest-express-sequelize');
2
​
3
const dbDemo = models.sequelize.demo.models;
4
​
5
collection('billings', {
6
  fields: [{
7
    field: 'customer',
8
    type: 'String',
9
    reference: 'customers.id',
10
    get: function (billing) {
11
      return dbDemo.customers.findOne({ id: billing.customer_id });
12
    }
13
  }]
14
});
Copied!
Adding a hasMany relationship
The main problem of having two databases is that you cannot rely on SQL joins anymore to browser your data over relationships.
As above, we will create a hasMany Smart Relationship and code the logic to reconcile the data between our Companies (database companies) and Users (database users ).
1
forest/companies.js
2
​
3
const { collection } = require('forest-express-sequelize');
4
​
5
collection('companies', {
6
  fields: [{
7
    field: 'users',
8
    type: ['String'],
9
    reference: 'users.id'
10
  }],
11
});
Copied!
1
routes/companies.js
2
​
3
const express = require('express');
4
const { PermissionMiddlewareCreator, RecordSerializer } = require('forest-express-sequelize');
5
const { companies } = require('../models');
6
const { sequelize } = require('../models');
7
​
8
const databaseUsers = sequelize.users.models;
9
​
10
const router = express.Router();
11
const permissionMiddlewareCreator = new PermissionMiddlewareCreator('companies');
12
​
13
router.get('/companies/:recordId/relationships/users', permissionMiddlewareCreator.list(), (request, response, next) => {
14
  const companyId = request.params.recordId;
15
  const recordSerializer = new RecordSerializer(databaseUsers.users);
16
​
17
  databaseUsers.users
18
    .findAll({ where: { companyId: companyId } })
19
    .then(records => recordSerializer.serialize(records, { count: records.length }))
20
    .then(recordsSerialized => response.send(recordsSerialized))
21
    .catch(next);
22
});
Copied!