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.
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.jsconst fs = require('fs');const path = require('path');const Sequelize = require('sequelize');let databases = [{name: 'demo',connectionString: process.env.DATABASE_DEMO}, {name: 'billing',connectionString: process.env.DATABASE_BILLING}];const sequelize = {};const db = {};const models = {};databases.forEach((databaseInfo) => {models[databaseInfo.name] = {};const isDevelopment = process.env.NODE_ENV === 'development' || !process.env.NODE_ENV;const databaseOptions = {logging: isDevelopment ? console.log : false,pool: { maxConnections: 10, minConnections: 1 },dialectOptions: {}};if (process.env.DATABASE_SSL && JSON.parse(process.env.DATABASE_SSL.toLowerCase())) {databaseOptions.dialectOptions.ssl = true;}const connection = new Sequelize(databaseInfo.connectionString, databaseOptions);sequelize[databaseInfo.name] = connection;fs.readdirSync(path.join(__dirname, databaseInfo.name)).filter((file) => file.indexOf('.') !== 0 && file !== 'index.js').forEach((file) => {try {const model = connection.import(path.join(__dirname, databaseInfo.name, file));models[databaseInfo.name][model.name] = model;} catch (error) {console.error('Model creation error: ' + error);}});Object.keys(models[databaseInfo.name]).forEach((modelName) => {if ('associate' in models[databaseInfo.name][modelName]) {models[databaseInfo.name][modelName].associate(sequelize[databaseInfo.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.
.envDATABASE_DEMO=postgres://forest:secret@localhost:5416/forest_demoDATABASE_BILLING=postgres://forest:secret@localhost:5417/forest_billingFOREST_ENV_SECRET=...FOREST_AUTH_SECRET=...
At the moment, you should have a models/
directory that looks like this:
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
.
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;};
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.jsconst requireAll = require('require-all');const chalk = require('chalk');const path = require('path');const Liana = require('forest-express-sequelize');const models = require('../models');module.exports = async function (app) {app.use(await Liana.init({modelsDir: path.join(__dirname, '../models'),configDir: path.join(__dirname, '../forest'),envSecret: process.env.FOREST_ENV_SECRET,authSecret: process.env.FOREST_AUTH_SECRET,sequelize: models.Sequelize,connections: [models.sequelize.demo,models.sequelize.billing],}));console.log(chalk.cyan('Your admin panel is available here: https://app.forestadmin.com/projects'));};
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.
stacktracePOST /forest/actions/mark-as-live 500 10.501 ms - 3264TypeError: Cannot read property 'update' of undefinedat 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.jsconst 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!' }));});
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.jsconst { collection } = require('forest-express-sequelize');const dbBilling = models.sequelize.billing.models;collection('customers', {fields: [{field: 'billing',type: 'String',reference: 'billings.id',get: function (customer) {return dbBilling.billings.findByPk({ customer_id: customer.id });}}]});
And of course, you can create the inverse relationship:
forest/billings.jsconst { collection } = require('forest-express-sequelize');const dbDemo = models.sequelize.demo.models;collection('billings', {fields: [{field: 'customer',type: 'String',reference: 'customers.id',get: function (billing) {return dbDemo.customers.findOne({ id: billing.customer_id });}}]});
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 ).
forest/companies.jsconst { collection } = require('forest-express-sequelize');collection('companies', {fields: [{field: 'users',type: ['String'],reference: 'users.id'}],});
routes/companies.jsconst express = require('express');const { PermissionMiddlewareCreator, RecordSerializer } = require('forest-express-sequelize');const { companies } = require('../models');const { sequelize } = require('../models');const databaseUsers = sequelize.users.models;const router = express.Router();const permissionMiddlewareCreator = new PermissionMiddlewareCreator('companies');router.get('/companies/:recordId/relationships/users', permissionMiddlewareCreator.list(), (request, response, next) => {const companyId = request.params.recordId;const recordSerializer = new RecordSerializer(databaseUsers.users);databaseUsers.users.findAll({ where: { companyId: companyId } }).then(records => recordSerializer.serialize(records, { count: records.length })).then(recordsSerialized => response.send(recordsSerialized)).catch(next);});