Sequelize One-To-Many association – NodeJS/Express, MySQL

sequelize-one-to-many-association-nodejs-express-mysql-feature-image

In the post, we got started with Sequelize One-To-One association. Today we’re gonna create Sequelize One-To-Many association models with NodeJS/Express, MySQL.

Related posts:
Sequelize One-To-One association – NodeJS/Express, MySQL
Sequelize Many-to-Many association – NodeJS/Express, MySQL

Sequelize One-To-Many

One-To-Many associations are connecting one source with multiple targets. But the targets are connected to only one source.


const Company = sequelize.define('company', {/* ... */})
const Product = sequelize.define('product', {/* ... */})

Company.hasMany(Product)

The above code will add the attribute companyId to Product. Instances of Company has accessors getProducts and setProducts.

We can use sourceKey option to associate records on different columns:


const Company = sequelize.define('company', {
  uuid: {
	type: Sequelize.UUID,
	defaultValue: Sequelize.UUIDV1,
	primaryKey: true
  },
  
  ...
  return Company;
}

const Product = sequelize.define('product', {
	/*attributes*/
	
	return Product;
}

Company.hasMany(Product, {foreignKey: 'fk_companyid', sourceKey: 'uuid'});
Product.belongsTo(Company, {foreignKey: 'fk_companyid', targetKey: 'uuid'});

How to persist one-to-many objects to database?


// Apple company
Company.create({ 
	name: 'Apple', 
	street: 'Cupertino, CA 95014', 
	phone: '1-408-996-1010',
	products: [
		// IPhone 7 
		{
			/*
				attributes
			*/
		},
		// IPadPro
		{
			/*
				attributes
			*/
		}
	]
}, {
	include: [ Product ]
})

How to fetch all Companies (include Products)?


Company.findAll({
	attributes: [['uuid', 'companyId'], 'name', 'street', 'phone'],
	include: [{
		model: Product,
		where: { fk_companyid: db.Sequelize.col('company.uuid') },
		attributes: ['code', 'name', 'details']
	}]
})

With attributes option, we can select only some attributes:


Company.findAll({
	attributes: ['uuid', 'name', 'street', 'phone']
});

And attributes can be renamed using a nested array:


// 'uuid' can be renamed to 'companyId' as below:

Company.findAll({
	attributes: [['uuid', 'companyId'], 'name', 'street', 'phone'],
});

Practice

We create a NodeJs/Express project as below structure:


/nodejs-sequelizejs-one-to-many-mysql
	/app
		/config
			db.config.js
			env.js
		/controller
			company.controller.js
		/model
			company.model.js
			product.model.js
		/route
			company.route.js
	/node_modules
	package.json
	server.js

Setting up NodeJs/Express project

Following the guide to create a NodeJS/Express project

Install Express, Sequelize, MySQL:


$npm install express sequelize mysql2 --save

-> ‘package.json’ file:


{
  "name": "Sequelize-One-to-Many",
  "version": "1.0.0",
  "description": "nodejs-express-sequelizejs-One-to-Many-mysql",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJs-Express-SequelizeJs-MySQL"
  ],
  "author": "JSA",
  "license": "ISC",
  "dependencies": {
    "express": "^4.16.3",
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.6"
  }
}

Setting up Sequelize MySQL connection

– Create ‘./app/config/env.js’ file:


const env = {
  database: 'testdb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
	  max: 5,
	  min: 0,
	  acquire: 30000,
	  idle: 10000
  }
};
 
module.exports = env;

– Setup Sequelize-MySQL connection in ‘./app/config/db.config.js’ file:


const env = require('./env.js');

const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,

  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.company = require('../model/company.model.js')(sequelize, Sequelize);
db.product = require('../model/product.model.js')(sequelize, Sequelize);

// Here we can connect companies and products base on company'id
db.company.hasMany(db.product, {foreignKey: 'fk_companyid', sourceKey: 'uuid'});
db.product.belongsTo(db.company, {foreignKey: 'fk_companyid', targetKey: 'uuid'});

module.exports = db;

Define Sequelize models

– Company model:


module.exports = (sequelize, Sequelize) => {
	const Company = sequelize.define('company', {
	  uuid: {
		type: Sequelize.UUID,
		defaultValue: Sequelize.UUIDV1,
		primaryKey: true
	  },
	  name: {
		  type: Sequelize.STRING
	  },
	  street: {
		  type: Sequelize.STRING
	  },
	  phone: {
		  type: Sequelize.STRING
	  }
	});
	
	return Company;
}

– Product model:


module.exports = (sequelize, Sequelize) => {
	const Product = sequelize.define('product', {
	  code: {
		  type: Sequelize.STRING
	  },
	  name: {
		  type: Sequelize.STRING
	  },
	  details: {
		  type: Sequelize.STRING
	  }
	});
	
	return Product;
}

Express RestAPIs

Route

Define Company’s routes in ‘./app/controller/company.route.js’ file:


module.exports = function(app) {
 
    const companies = require('../controller/company.controller.js');
 
    // Init data: Companies & Products
    app.get('/api/companies/init', companies.init);
 
    // Retrieve all Companies
    app.get('/api/companies/all', companies.findAll);
}

Controller

Implement Company’s controller in ‘./app/controller/company.controller.js’ file:


const db = require('../config/db.config.js');
const Company = db.company;
const Product = db.product;
 
// Init data: Companies & Products
exports.init = (req, res) => {	
	
	// Apple company
	Company.create({ 
		name: 'Apple', 
		street: 'Cupertino, CA 95014', 
		phone: '1-408-996-1010',
		products: [
			// IPhone 7 
			{
				code: "A-123",
				name: "Iphone7",
				details: "Price: 649.00 USD & FREE shipping"
			},
			// IPadPro
			{
				code: "A-456",
				name: "IPadPro",
				details: "Price: 417.67 USD & FREE shipping"
			}
		]
	}, {
		include: [ Product ]
	}).then(() => {		
	
		console.log("-----------> Apple is created");
		
		// Samsung company
		Company.create({ 
			name: 'Samsung', 
			street: 'Seocho District, Seoul, South Korea', 
			phone: '+82-2-2053-3000',
			products: [
				// GalaxyJ7 
				{
					code: "S-012",
					name: "GalaxyJ7",
					details: "Price: 219.00 USD & FREE shipping"
				},
				// GalaxyTabA
				{
					code: "S-456",
					name: "GalaxyTabA",
					details: "Price: 299.99 USD & FREE shipping"
				}
			]
		}, {
			include: [ Product ]
		}).then(() => {		
			console.log("-----------> Samsung is created");
		})
	}).then(() => {
		res.send("Done!");
	})
};

// Fetch all Companies include Products
exports.findAll = (req, res) => {
	Company.findAll({
		attributes: [['uuid', 'companyId'], 'name', 'street', 'phone'],
		include: [{
			model: Product,
			where: { fk_companyid: db.Sequelize.col('company.uuid') },
			attributes: ['code', 'name', 'details']
		}]
	}).then(companies => {
	   res.send(companies);
	});
};

Server.js


var express = require('express');
var app = express();
var bodyParser = require('body-parser');
app.use(bodyParser.json())

const db = require('./app/config/db.config.js');
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
});

require('./app/route/company.route.js')(app);
 
// Create a Server
var server = app.listen(8081, function () {
 
  var host = server.address().address
  var port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port)
})

Run & Check results

Start Node.js server:


>node server.js
App listening at http://:::8081
Executing (default): DROP TABLE IF EXISTS `products`;
Executing (default): DROP TABLE IF EXISTS `companies`;
Executing (default): DROP TABLE IF EXISTS `companies`;
Executing (default): CREATE TABLE IF NOT EXISTS `companies` (`uuid` CHAR(36) BINARY , `name` VARCHAR(255), `street` VARCHAR(255), `phone` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`uuid`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `companies`
Executing (default): DROP TABLE IF EXISTS `products`;
Executing (default): CREATE TABLE IF NOT EXISTS `products` (`id` INTEGER NOT NULL auto_increment , `code` VARCHAR(255), `name` VARCHAR(255), `details` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `fk_companyid` CHAR(36) BINARY, PRIMARY KEY (`id`), FOREIGN KEY (`fk_companyid`) REFERENCES `companies` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `products`
Drop and Resync with { force: true }

sequelize one to many - nodejs, express - tables

Make an initial data request:
localhost:8081/api/companies/init


Executing (default): INSERT INTO `companies` (`uuid`,`name`,`street`,`phone`,`createdAt`,`updatedAt`) VALUES ('656949b0-467f-11e8-8acb-0514b30c9136','Apple','Cupertino, CA 95014','1-408-996-1010','2018-04-22 22:49:21','2018-04-22 22:49:21');
Executing (default): INSERT INTO `products` (`id`,`code`,`name`,`details`,`createdAt`,`updatedAt`,`fk_companyid`) VALUES (DEFAULT,'A-123','Iphone7','Price: 649.00 USD & FREE shipping','2018-04-22 22:49:21','2018-04-22 22:49:21','656949b0-467f-11e8-8acb-0514b30c9136');
Executing (default): INSERT INTO `products` (`id`,`code`,`name`,`details`,`createdAt`,`updatedAt`,`fk_companyid`) VALUES (DEFAULT,'A-456','IPadPro','Price: 417.67 USD & FREE shipping','2018-04-22 22:49:21','2018-04-22 22:49:21','656949b0-467f-11e8-8acb-0514b30c9136');
-----------> Apple is created
Executing (default): INSERT INTO `companies` (`uuid`,`name`,`street`,`phone`,`createdAt`,`updatedAt`) VALUES ('65858440-467f-11e8-8acb-0514b30c9136','Samsung','Seocho District, Seoul, South Korea','+82-2-2053-3000','2018-04-22 22:49:21','2018-04-22 22:49:21');
Executing (default): INSERT INTO `products` (`id`,`code`,`name`,`details`,`createdAt`,`updatedAt`,`fk_companyid`) VALUES (DEFAULT,'S-012','GalaxyJ7','Price: 219.00 USD & FREE shipping','2018-04-22 22:49:21','2018-04-22 22:49:21','65858440-467f-11e8-8acb-0514b30c9136');
Executing (default): INSERT INTO `products` (`id`,`code`,`name`,`details`,`createdAt`,`updatedAt`,`fk_companyid`) VALUES (DEFAULT,'S-456','GalaxyTabA','Price: 299.99 USD & FREE shipping','2018-04-22 22:49:21','2018-04-22 22:49:21','65858440-467f-11e8-8acb-0514b30c9136');
-----------> Samsung is created

sequelize one to many - nodejs, express - tables

sequelize one to many - nodejs, express - select products

Get all Companies, include Products:

localhost:8081/api/companies/all


Executing (default): SELECT `company`.`uuid`, `company`.`uuid` AS `companyId`, `company`.`name`, `company`.`street`, `company`.`phone`, `products`.`id` AS `products.id`, `products`.`code` AS `products.code`, `products`.`name` AS `products.name`, `products`.`details` AS `products.details` FROM `companies` AS `company` INNER JOIN `products` AS `products` ON `company`.`uuid` = `products`.`fk_companyid` AND `company`.`uuid`;

sequelize one to many - nodejs, express - fetch all companies products

Sourcecode

Nodejs-Express-Sequelizejs-One-to-Many-Mysql



By grokonez | April 24, 2018.

Last updated on May 12, 2021.



Related Posts


7 thoughts on “Sequelize One-To-Many association – NodeJS/Express, MySQL”

  1. Should all relationships be in the connection setup?
    If I have multiple many-to-many associations, should they all be in this file ‘./app/config/db.config.js’?

    Thanks!

  2. are the relationships virtual or not? I tried the following two migrations but I don’t see related columns created. Am I missing something?

    'use strict';
    //import Driver from './driver';
    
    const Driver=require('./driver');
    
    module.exports = (sequelize, DataTypes) => {
      const CarDriver = sequelize.define('CarDriver', {
        status: DataTypes.INTEGER,
        last_lat: DataTypes.STRING,
        last_long: DataTypes.STRING,
        last_location_updated_on: DataTypes.DATE
      }, {});
      CarDriver.associate = function(models) {
        // associations can be defined here
    
      };
      return CarDriver;
    };
    
    Driver.hasMany(CarDriver, {foreignKey: 'fk_driverid', sourceKey: 'id'});
    CarDriver.belongsTo(Driver, {foreignKey: 'fk_driverid', targetKey: 'id'});
    

    or

    'use strict';
    //import Driver from './driver';
    
    const Driver=require('./driver');
    
    module.exports = (sequelize, DataTypes) => {
      const CarDriver = sequelize.define('CarDriver', {
        status: DataTypes.INTEGER,
        last_lat: DataTypes.STRING,
        last_long: DataTypes.STRING,
        last_location_updated_on: DataTypes.DATE
      }, {});
      CarDriver.associate = function(models) {
        // associations can be defined here
    Driver.hasMany(CarDriver, {foreignKey: 'fk_driverid', sourceKey: 'id'});
    CarDriver.belongsTo(Driver, {foreignKey: 'fk_driverid', targetKey: 'id'});
      };
      return CarDriver;
    };
    

    I created Driver model first then when setting up cardriver model, Iwant to attach it before I migrate it. there is no error; table CrDriver is indeeded created but I don’t see driver_id column added to the model in the table. How do I do that?

  3. Hi,
    You created both company and products in the same create method, but I need to insert products separately, how I achieve that?

Got Something To Say:

Your email address will not be published. Required fields are marked *

*