The comprehensive step by step tutorial on Node, Express, Sequelize, and PostgreSQL model associations example. As we know that PostgreSQL as Relational Database, using ORM (Object-Relational Mapping) also has relation or association feature between table which in this ORM using the model as the representation of Database table. We will show you an example of a one-to-one, one-to-many and many-to-many relationship. Previously, we have shown you basic Sequelize associations using Node, Express, Sequelize.js, and PostgreSQL. Now, we will show you more of the Sequelize Associations example.

Shortcut to the steps:

Using Sequelize associations for RDBMS relationships is easier because as their official said that Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

The following tools, frameworks, and modules are required for this tutorial:

Node.js (Recommended version)

Express.js

Sequelize.js (Latest version)

PostgreSQL (We are using 9.5.13 version)

Terminal (Mac/Linux) or Command Line (Windows)

Text Editor or IDE

Before started to make sure above required items are installed properly. You can find an installation guide on each official websites.



Create and Configure a Node Express Web Application

Open your terminal or node command line the go to your projects folder. First, install express-generator using this command.

sudo npm install express-generator -g

Next, create an Express.js app using this command.

express sequelize-assoc --view=ejs

This will create Express.js project with the EJS view instead of Jade view template because using '--view=ejs' parameter. Next, go to the newly created project folder then install node modules.

cd sequelize-assoc && npm install

You should see the folder structure like this.

. |-- app.js |-- bin | `-- www |-- node_modules |-- package-lock.json |-- package.json |-- public | |-- images | |-- javascripts | `-- stylesheets | `-- style.css |-- routes | |-- index.js | `-- users.js `-- views |-- error.ejs `-- index.ejs



Add and Configure Sequelize Module and Dependencies

To use Sequelize as ORM for PostgreSQL table, first, we have to install the Sequelize CLI. Type this command to install the latest Sequelize CLI.

sudo npm install -g sequelize-cli

Now, you can install the latest Sequelize module for this Node Express project.

npm install --save sequelize

To connect with PostgreSQL database from Node application, we have to install pg and pg-hstore module by type this command.

npm install --save pg pg-hstore

Next, create a new file at the root of the project folder.

touch .sequelizerc

Open and edit that file then add these lines of codes.

const path = require('path'); module.exports = { "config": path.resolve('./config', 'config.json'), "models-path": path.resolve('./models'), "seeders-path": path.resolve('./seeders'), "migrations-path": path.resolve('./migrations') };

That files will tell Sequelize initialization to generate config, models, seeders and migrations files to specific directories. Next, type this command to initialize the Sequelize.

sequelize init

That command will create `config/config.json`, `models/index.js`, `migrations` and `seeders` directories and files. Next, open and edit `config/config.json` then make it like this.

We use the same configuration for all the environment because we are using the same machine, server, and database for this tutorial.

Before run and test connection, make sure you have created a database as described in the above configuration. You can use the `psql` command to create a user and database.

psql postgres --u postgres

Next, type this command for creating a new user with password then give access for creating the database.

postgres-# CREATE ROLE djamware WITH LOGIN PASSWORD '[email protected]@r3'; postgres-# ALTER ROLE djamware CREATEDB;

Quit `psql` then log in again using the new user that previously created.

postgres-# \q psql postgres -U djamware

Enter the password, then you will enter this `psql` console.

psql (9.5.13) Type "help" for help. postgres=>

Type this command to creating a new database.

postgres=> CREATE DATABASE sequelize_assoc;

Then give that new user privileges to the new database then quit the `psql`.

postgres=> GRANT ALL PRIVILEGES ON DATABASE sequelize_assoc TO djamware; postgres=> \q



Create One-To-One Associations Sequelize Models

For one-to-one association we will use two tables as an example they are User and Profile table. User table has one Profile table and Profile table belongs to the User table. Here's the relation diagram for it.

On that diagram, User as the source model and Profile as the target model. There are two kinds of 1:1 relationship in Sequelize, `hasOne` and `belongsTo`. HasOne put the association key in the target model and the BelongsTo put the associations key in the source model. To generate `hasOne` models using Sequelize CLI, type this command.

sequelize model:create --name User --attributes username:string,password:string sequelize model:create --name Profile --attributes user_id:integer,fullname:string,birthdate:date,gender:string,position:string

That command creates a model file to the model's folder and a migration file to folder migrations. Next, modify `models/user.js` then add an association with `Profile` model inside `associate` function.

User.associate = function(models) { User.hasOne(models.Profile, { foreignKey: 'user_id', as: 'profile', }); };

To generate `belongsTo` models using Sequelize CLI, type this command.

sequelize model:create --name Person --attributes fullname:string,birthdate:date,card_id:integer sequelize model:create --name CreditCard --attributes cardname:string,cardnbr:string,expired_date:string

That command creates a model file to the model's folder and a migration file to folder migrations. Next, modify `models/creditcard.js` then add an association with `Person` model inside `associate` function.

CreditCard.associate = function(models) { CreditCard.belongsTo(models.Person, { foreignKey: 'card_id', }); };

Above association, examples are associate using the primary key which is default ID of the models or table. If you like to use a different field or column as association reference, you can use `TargetKey` like below.

sequelize model:create --name Person --attributes fullname:string,birthdate:date,cardnbr:string sequelize model:create --name CreditCard --attributes cardname:string,cardnbr:string,expired_date:string

CreditCard.associate = function(models) { CreditCard.belongsTo(models.Person, { foreignKey: 'cardnbr', targetKey: 'cardnbr' }); };



Create One-To-Many Associations Sequelize Models

For one-to-many association we will use two tables as an example they are Company and Branch table. Company table has many Branch table and Branch table belongs to the Company table. Here's the relation diagram for it.

On that diagram, Company as the source model and Branch as the target model. There are two kinds of 1:n relationship in Sequelize, `hasMany` and `belongsTo`. The HasMany put the association key in the target model and the BelongTo put the association key in the source model. To generate `hasMany` models using Sequelize CLI, type this command.

sequelize model:create --name Company --attributes company_name:string,company_address:string,company_city:string sequelize model:create --name Branch --attributes company_id:integer,branch_name:string,branch_address:string,branch_city:string

That command creates a model file to the model's folder and a migration file to folder migrations. Next, modify `models/company.js` then add an association with `Branch` model inside `associate` function.

Company.associate = function(models) { Company.hasMany(models.Branch, { foreignKey: 'company_id', as: 'branches' }); };

Next, add `belongsTo` association in `Branch` model by open and edit `models/branch.js` then add or replace `Branch.associate`.

Branch.associate = function(models) { Branch.belongsTo(models.Company, { foreignKey: 'company_id' }); };

That example is an association between models through `id` of Company model/table. If you like to associate through another field, you should add the `SourceKey`. For example, we have two tables or models Province and City which the association using three-letter Province Code. Let's try to generate the models.

sequelize model:create --name Province --attributes prov_code:string,prov_name:string sequelize model:create --name City --attributes prov_code:string,city_name:string

Next, open and edit `models/province` then modify the `Province.associate`.

Province.associate = function(models) { Province.hasMany(models.City, { foreignKey: 'prov_code', sourceKey: 'prov_code' }); };

Open and edit `models/city.js` then modify the `City.associate`.

City.associate = function(models) { City.belongsTo(models.Province, { foreignKey: 'prov_code', targetKey: 'prov_code' }); };



Create Many-To-Many Associations Sequelize Models

For `many-to-many` relationship we have to use Sequelize Belongs-To-Many association which declare association on the both of models through the new Join Model/Table. For example, we will use existing `User` models and create new `Role` models with Join models `UserRole`. Type this command to generate `Role` and `UserRole` models.

sequelize model:create --name Role --attributes role_name:string sequelize model:create --name UserRole --attributes user_id:integer,role_id:integer

Next, open and edit `models/user.js` then add this association inside `User.associate`.

User.associate = function(models) { User.hasOne(models.Profile, { foreignKey: 'user_id', as: 'profile', }); User.belongsToMany(models.Role, { through: 'UserRole', as: 'roles', foreignKey: 'user_id' }); };

Next, open and edit `models/role.js` then modify the `Role.associate`.

Role.associate = function(models) { Role.belongsToMany(models.User, { through: 'UserRole', as: 'users', foreignKey: 'role_id' }); };

There's nothing to do with `models/userrole.js` because these models just hold both User and Role ID.



Create Node Express Controllers and Routers

We will test those association between models using the REST API. So, we just need to create controllers and routers for that. Create new controllers for all models.

mkdir controllers touch controllers/user.js touch controllers/profile.js touch controllers/company.js touch controllers/branch.js touch controllers/role.js touch controllers/index.js

Next, open and edit `controllers/role.js` then replace all codes with this.

const Role = require('../models').Role; const User = require('../models').User; module.exports = { list(req, res) { return Role .findAll({ include: [{ model: User, as: 'users' }], }) .then((roles) => res.status(200).send(roles)) .catch((error) => { res.status(400).send(error); }); }, getById(req, res) { return Role .findById(req.params.id, { include: [{ model: User, as: 'users' }], }) .then((role) => { if (!role) { return res.status(404).send({ message: 'Role Not Found', }); } return res.status(200).send(role); }) .catch((error) => res.status(400).send(error)); }, add(req, res) { return Role .create({ role_name: req.body.role_name, }) .then((role) => res.status(201).send(role)) .catch((error) => res.status(400).send(error)); }, addUser(req, res) { return Role .findById(req.body.role_id, { include: [{ model: User, as: 'users' }], }) .then((role) => { if (!role) { return res.status(404).send({ message: 'Role Not Found', }); } User.findById(req.body.role_id).then((course) => { if (!course) { return res.status(404).send({ message: 'User Not Found', }); } role.addUser(course); return res.status(200).send(role); }) }) .catch((error) => res.status(400).send(error)); }, update(req, res) { return Role .findById(req.params.id, { include: [{ model: User, as: 'users' }], }) .then(role => { if (!role) { return res.status(404).send({ message: 'Role Not Found', }); } return role .update({ role_name: req.body.role_name || classroom.role_name, }) .then(() => res.status(200).send(role)) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, delete(req, res) { return Role .findById(req.params.id) .then(role => { if (!role) { return res.status(400).send({ message: 'Role Not Found', }); } return role .destroy() .then(() => res.status(204).send()) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, };

Next, open and edit `controllers/user.js` then replace all codes with this.

const User = require('../models').User; const Profile = require('../models').Profile; const Role = require('../models').Role; const UserRole = require('../models').UserRole; module.exports = { list(req, res) { return User .findAll({ include: [{ model: Profile, as: 'profile' }, { model: Role, as: 'roles' }], }) .then((users) => res.status(200).send(users)) .catch((error) => { res.status(400).send(error); }); }, getById(req, res) { return User .findById(req.params.id, { include: [{ model: Profile, as: 'profile' }, { model: Role, as: 'roles' }], }) .then((user) => { if (!user) { return res.status(404).send({ message: 'User Not Found', }); } return res.status(200).send(user); }) .catch((error) => res.status(400).send(error)); }, add(req, res) { return User .create({ class_name: req.body.class_name, }) .then((user) => res.status(201).send(user)) .catch((error) => res.status(400).send(error)); }, update(req, res) { return User .findById(req.params.id, { include: [{ model: Profile, as: 'profile' }, { model: Role, as: 'roles' }], }) .then(user => { if (!user) { return res.status(404).send({ message: 'User Not Found', }); } return user .update({ username: req.body.username || user.username, password: req.body.password || user.password, }) .then(() => res.status(200).send(user)) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, delete(req, res) { return User .findById(req.params.id) .then(user => { if (!user) { return res.status(400).send({ message: 'User Not Found', }); } return user .destroy() .then(() => res.status(204).send()) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, };

Next, open and edit `controllers/profile.js` then replace all codes with this.

const Profile = require('../models').Profile; const User = require('../models').User; module.exports = { list(req, res) { return Profile .findAll({ include: [{ model: User, as: 'user' }], }) .then((profiles) => res.status(200).send(profiles)) .catch((error) => { res.status(400).send(error); }); }, getById(req, res) { return Profile .findById(req.params.id, { include: [{ model: User, as: 'user' }], }) .then((profile) => { if (!profile) { return res.status(404).send({ message: 'Profile Not Found', }); } return res.status(200).send(profile); }) .catch((error) => res.status(400).send(error)); }, add(req, res) { return Profile .create({ user_id: req.body.user_id, fullname: req.body.fullname, birthdate: req.body.birthdate, gender: req.body.gender, position: req.body.position, }) .then((profile) => res.status(201).send(profile)) .catch((error) => res.status(400).send(error)); }, update(req, res) { return Profile .findById(req.params.id, { include: [{ model: User, as: 'user' }], }) .then(profile => { if (!profile) { return res.status(404).send({ message: 'Profile Not Found', }); } return profile .update({ user_id: req.body.user_id || classroom.user_id, fullname: req.body.fullname || classroom.fullname, birthdate: req.body.birthdate || classroom.birthdate, gender: req.body.gender || classroom.gender, position: req.body.position || classroom.position, }) .then(() => res.status(200).send(profile)) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, delete(req, res) { return Profile .findById(req.params.id) .then(profile => { if (!profile) { return res.status(400).send({ message: 'Profile Not Found', }); } return profile .destroy() .then(() => res.status(204).send()) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, };

Next, open and edit `controllers/company.js` then replace all codes with this.

const Company = require('../models').Company; const Branch = require('../models').Branch; module.exports = { list(req, res) { return Company .findAll({ include: [{ model: Branch, as: 'branches' }], }) .then((companies) => res.status(200).send(companies)) .catch((error) => { res.status(400).send(error); }); }, getById(req, res) { return Company .findById(req.params.id, { include: [{ model: Branch, as: 'branches' }], }) .then((company) => { if (!company) { return res.status(404).send({ message: 'Company Not Found', }); } return res.status(200).send(company); }) .catch((error) => res.status(400).send(error)); }, add(req, res) { return Company .create({ company_name: req.body.company_name, company_address: req.body.company_address, company_city: req.body.company_city, }) .then((company) => res.status(201).send(company)) .catch((error) => res.status(400).send(error)); }, addWithBranchs(req, res) { return Company .create({ company_name: req.body.company_name, company_address: req.body.company_address, company_city: req.body.company_city, branches: req.body.branches, }, { include: [{ model: Branch, as: 'branches' }] }) .then((company) => res.status(201).send(company)) .catch((error) => res.status(400).send(error)); }, update(req, res) { console.log(req.body); return Company .findById(req.params.id, { include: [{ model: Branch, as: 'branches' }], }) .then(company => { if (!company) { return res.status(404).send({ message: 'Company Not Found', }); } return company .updateAttributes({ company_name: req.body.company_name || company.company_name, company_address: req.body.company_address || company.company_address, company_city: req.body.company_city || company.company_city, branches: req.body.branches || company.branches, }, { include: [{ model: Branch, as: 'branches' }] }) .then(() => res.status(200).send(company)) .catch((error) => {console.log(error);res.status(400).send(error);}); }) .catch((error) => {console.log(error);res.status(400).send(error);}); }, delete(req, res) { return Company .findById(req.params.id) .then(company => { if (!company) { return res.status(400).send({ message: 'Company Not Found', }); } return company .destroy() .then(() => res.status(204).send()) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, };

Next, open and edit `controllers/branch.js` then replace all codes with this.

const Branch = require('../models').Branch; const Company = require('../models').Company; module.exports = { list(req, res) { return Branch .findAll({ include: [{ model: Company, as: 'company' }], }) .then((branches) => res.status(200).send(branches)) .catch((error) => { res.status(400).send(error); }); }, getById(req, res) { return Branch .findById(req.params.id, { include: [{ model: Company, as: 'company' }], }) .then((branch) => { if (!branch) { return res.status(404).send({ message: 'Branch Not Found', }); } return res.status(200).send(branch); }) .catch((error) => res.status(400).send(error)); }, add(req, res) { return Branch .create({ company_id: req.body.company_id, branch_name: req.body.branch_name, branch_address: req.body.branch_address, branch_city: req.body.branch_city, }) .then((branch) => res.status(201).send(branch)) .catch((error) => res.status(400).send(error)); }, update(req, res) { return Branch .findById(req.params.id, { include: [{ model: Company, as: 'company' }], }) .then(branch => { if (!branch) { return res.status(404).send({ message: 'Branch Not Found', }); } return branch .update({ branch_name: req.body.branch_name || company.branch_name, branch_address: req.body.branch_address || company.branch_address, branch_city: req.body.branch_city || company.branch_city, }) .then(() => res.status(200).send(branch)) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, delete(req, res) { return Branch .findById(req.params.id) .then(branch => { if (!branch) { return res.status(400).send({ message: 'Branch Not Found', }); } return branch .destroy() .then(() => res.status(204).send()) .catch((error) => res.status(400).send(error)); }) .catch((error) => res.status(400).send(error)); }, };

Next, open and edit `controllers/index.js` then replace all codes with this.

const company = require('./company'); const branch = require('./branch'); const profile = require('./profile'); const role = require('./role'); const user = require('./user'); module.exports = { company, branch, profile, role, user, };

Next, create routers for all of those controllers. Open and edit `routes/index.js` then replace all codes with this.

var express = require('express'); var router = express.Router(); const companyController = require('../controllers').company; const branchController = require('../controllers').branch; const profileController = require('../controllers').profile; const userController = require('../controllers').user; const roleController = require('../controllers').role; /* GET home page. */ router.get('/', function(req, res, next) { res.render('index', { title: 'Express' }); }); /* Company Router */ router.get('/api/company', companyController.list); router.get('/api/company/:id', companyController.getById); router.post('/api/company', companyController.add); router.put('/api/company/:id', companyController.update); router.delete('/api/company/:id', companyController.delete); /* Branch Router */ router.get('/api/branch', branchController.list); router.get('/api/branch/:id', branchController.getById); router.post('/api/branch', branchController.add); router.put('/api/branch/:id', branchController.update); router.delete('/api/branch/:id', branchController.delete); /* Profile Router */ router.get('/api/profile', profileController.list); router.get('/api/profile/:id', profileController.getById); router.post('/api/profile', profileController.add); router.put('/api/profile/:id', profileController.update); router.delete('/api/profile/:id', profileController.delete); /* User Router */ router.get('/api/user', userController.list); router.get('/api/user/:id', userController.getById); router.post('/api/user', userController.add); router.put('/api/user/:id', userController.update); router.delete('/api/user/:id', userController.delete); /* User Role */ router.get('/api/role', roleController.list); router.get('/api/role/:id', roleController.getById); router.post('/api/role', roleController.add); router.put('/api/role/:id', roleController.update); router.delete('/api/role/:id', roleController.delete); /* Advance Router */ router.post('/api/role/add_user', roleController.addUser); router.post('/api/company/add_with_branches', companyController.addWithBranches); module.exports = router;



Test and Run The Application the Node Express Sequelize PostgreSQL Associations

Before the test the association with RESTful API, first, we have to generate or migrate models to PostgreSQL tables. Type this command to migrate it.

sequelize db:migrate

Now, you can run the Node Express application by type this command.

nodemon

Open the new terminal tab or command line tab then type this command for save or persist company data include with branches.

curl -i -X POST -H "Content-Type: application/json" -d '{ "company_name":"Djamware.com","company_address":"Setiabudhi km.9, Ledeng","company_city":"Bandung","branches": [{ "branch_name":"Local Branch", "branch_address":"Setiabudhi km.9, Ledeng", "branch_city":"Bandung" },{ "branch_name":"International Branch", "branch_address":"Salt Lake View", "branch_city":"Salt Lake City" }] }' localhost:3000/api/company/add_with_branches

That command will show the response in the terminal like this.

HTTP/1.1 201 Created X-Powered-By: Express Content-Type: application/json; charset=utf-8 Content-Length: 600 ETag: W/"258-3M9Xq82sAdGh0t4qTJhe/+qqhJs" Date: Mon, 01 Oct 2018 09:21:26 GMT Connection: keep-alive {"id":1,"company_name":"Djamware.com","company_address":"Setiabudhi km.9, Ledeng","company_city":"Bandung","branches":[{"id":1,"branch_name":"Local Branch","branch_address":"Setiabudhi km.9, Ledeng","branch_city":"Bandung","company_id":1,"updatedAt":"2018-10-01T09:21:26.375Z","createdAt":"2018-10-01T09:21:26.375Z"},{"id":2,"branch_name":"International Branch","branch_address":"Salt Lake View","branch_city":"Salt Lake City","company_id":1,"updatedAt":"2018-10-01T09:21:26.375Z","createdAt":"2018-10-01T09:21:26.375Z"}],"updatedAt":"2018-10-01T09:21:26.273Z","createdAt":"2018-10-01T09:21:26.273Z"}

To see data persist to PostgreSQL table, open new terminal tab then run `psql`.

psql sequelize_assoc -U djamware

Supply the password then you will take to this console.

psql (9.5.13) Type "help" for help. sequelize_assoc=>

Next, run this query.

SELECT * FROM public."Companies";

You will see these results in the PostgreSQL console.

id | company_name | company_address | company_city | createdAt | updatedAt ----+--------------+-------------------------+--------------+----------------------------+---------------------------- 1 | Djamware.com | Setiabudhi km.9, Ledeng | Bandung | 2018-10-01 16:21:26.273+07 | 2018-10-01 16:21:26.273+07 (1 row)

That's it, the Node, Express, Sequelize, and PostgreSQL Association Example. You can get the full working source code on our GitHub.

That just the basic. If you need more deep learning about Node, Express, Sequelize, PostgreSQL/MySQL or related you can take the following cheap course:

Thanks!