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

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

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

Related posts:
Sequelize ORM – Build CRUD RestAPIs with NodeJs/Express, Sequelize, MySQL
Sequelize One-To-Many association – NodeJS/Express, MySQL
Sequelize Many-to-Many association – NodeJS/Express, MySQL

Sequelize One-to-One association

Note: When calling a method such as Customer.hasOne(Address), we say that the Customer model is the source and the Address model is the target.

We define 2 models:


Customer = sequelize.define('customer', {
  /* attributes */
});

Address = sequelize.define('address', {
	/* attributes */
});

How to create One-to-One association between them?
-> Sequelize provides 2 ways:

belongsTo


Address.belongsTo(Customer); // Will add a customerId attribute to Address to hold the primary key value for Customer.

hasOne


Customer.belongsTo(Address); // Will add an attribute customerId to the Address model.

What is difference between belongsTo and hasOne?
-> hasOne inserts the association key in target model whereas BelongsTo inserts the association key in the source model.

We can create a solution by combine foreignKey and targetKey with belongsTo and hasOne relation as below code:


Customer = sequelize.define('customer', {
  uuid: {
	type: Sequelize.UUID,
	defaultValue: Sequelize.UUIDV1,
	primaryKey: true
  },
  /*
	more attributes
  */
});

Address = sequelize.define('address', {
	/* attributes */
});
	
Address.belongsTo(Customers, {foreignKey: 'fk_customerid', targetKey: 'uuid'});
Customers.hasOne(Address, {foreignKey: 'fk_customerid', targetKey: 'uuid'});

Foreign Key

– In belongsTo relation, foreign key will be generated from the target model name and the target primary key name. Sequelize provides a foreignKey option to override defaultValue.

Target Key

– The target key is the column on the target model that the foreign key column on the source model points to. In belongsTo relation, by default the target key will be the target model’s primary key. Sequelize provides a targetKey option to define a custom column.

How to save it?


var customer;
Customer.create({ 
	firstname: 'Jack',
	...
	}).then(createdCustomer => {		
		// Send created customer to client
		customer = createdCustomer;
		
		return Address.create({
			street: 'W NORMA ST',
			...
		})
	}).then(address => {
		customer.setAddress(address)
	})
};

How to fetch entities?

-> Way to get all Customers that includes Addresses:


Customer.findAll({
	attributes: [['uuid', 'customerId'], ['firstname', 'name'], 'age'],
	include: [{
		model: Address,
		where: { fk_customerid: db.Sequelize.col('customer.uuid') },
		attributes: ['street', 'phone']
	}]
}).then(customers => {
   console.log(customers);
});

With attributes option, we can select only some attributes:


Customer.findAll({
    attributes: ['uuid', 'firstname', 'age']
});

And attributes can be renamed using a nested array:


// 'uuid, firstname' can be renamed to 'customerId, name' as below:

Customer.findAll({
    attributes: [['uuid', 'customerId'], ['firstname', 'name'], 'age']
});

Practice

We create a NodeJs/Express project as below structure:


/nodejs-sequelizejs-one-to-one-mysql
	/app
		/config
			db.config.js
			env.js
		/controller
			customer.controller.js
		/model
			customer.model.js
		/route
			customer.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-One",
  "version": "1.0.0",
  "description": "nodejs-express-sequelizejs-One-to-One-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.customers = require('../model/customer.model.js')(sequelize, Sequelize);
db.address = require('../model/address.model.js')(sequelize, Sequelize);

db.address.belongsTo(db.customers, {foreignKey: 'fk_customerid', targetKey: 'uuid'});
db.customers.hasOne(db.address, {foreignKey: 'fk_customerid', targetKey: 'uuid'});

module.exports = db;

Define Sequelize models

– Address model:


module.exports = (sequelize, Sequelize) => {
	const Address = sequelize.define('address', {
	  street: {
		  type: Sequelize.STRING
	  },
	  phone: {
		  type: Sequelize.STRING
	  }
	});
	
	return Address;
}

– Customer model:


module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {
	  uuid: {
		type: Sequelize.UUID,
		defaultValue: Sequelize.UUIDV1,
		primaryKey: true
	  },
	  firstname: {
		type: Sequelize.STRING
	  },
	  lastname: {
		type: Sequelize.STRING
	  },
	  age: {
		  type: Sequelize.INTEGER
	  }
	});
	
	return Customer;
}

Express RestAPIs

Route

Define Customer’s routes in ‘./app/controller/customer.route.js’ file:


module.exports = function(app) {
 
    const customers = require('../controller/customer.controller.js');
 
    // Create a new Customer
    app.post('/api/customers', customers.create);
 
    // Retrieve all Customer
    app.get('/api/customers', customers.findAll);
}

Controller

Implement Customer’s controller in ‘./app/controller/customer.controller.js’ file:


const db = require('../config/db.config.js');
const Customer = db.customers;
const Address = db.address;

// Post a Customer
exports.create = (req, res) => {	
	// Save to MySQL database
	
	var customer;
	Customer.create({ 
	  //customerid: db.sequelize.Utils.generateUUID(),
	  firstname: req.body.firstname,
	  lastname: req.body.lastname,
	  age: req.body.age
	}).then(createdCustomer => {		
		// Send created customer to client
		customer = createdCustomer;
		
		return Address.create({
			street: req.body.street,
			phone: req.body.phone
		})
	}).then(address => {
		customer.setAddress(address)
		res.send('OK');
	})
};
 
// FETCH all Customers include Addresses
exports.findAll = (req, res) => {
	Customer.findAll({
		attributes: [['uuid', 'customerId'], ['firstname', 'name'], 'age'],
		include: [{
			model: Address,
			where: { fk_customerid: db.Sequelize.col('customer.uuid') },
			attributes: ['street', 'phone']
		}]
	}).then(customers => {
	   res.send(customers);
	});

};

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/customer.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 NodeJs server:


nodejs-express-sequelizejs-mysql>node server.js
App listening at http://:::8081
Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): CREATE TABLE IF NOT EXISTS `customers` (`id` INTEGER NOT NULL auto_increment , `firstname` VARCHAR(255), `lastname` VARCHAR(255), `age` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `customers`
Drop and Resync with { force: true }

Use Postman to check result ->

– POST customers:

Sequelize One-to-One association - CRUD RestAPI NodeJSExpress MySQL - post request

Check database after do POST:

Sequelize One-to-One association - CRUD RestAPI NodeJSExpress MySQL - customer table

Sequelize One-to-One association - CRUD RestAPI NodeJSExpress MySQL - address table


Executing (default): INSERT INTO `customers` (`uuid`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES ('9316ba60-4313-11e8-84e8-85431d41448c','Mary','Taylor',37,'2018-04-18 14:19:58','2018-04-18 14:19:58');
Executing (default): INSERT INTO `addresses` (`id`,`phone`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'(251) 546-9442','2018-04-18 14:19:58','2018-04-18 14:19:58');
Executing (default): INSERT INTO `customers` (`uuid`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES ('53697ff0-4314-11e8-84e8-85431d41448c','Jack','Davis',37,'2018-04-18 14:25:21','2018-04-18 14:25:21');
Executing (default): INSERT INTO `addresses` (`id`,`phone`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'(671) 925-1352','2018-04-18 14:25:21','2018-04-18 14:25:21');

– Find All Customers:

Sequelize One-to-One association - CRUD RestAPI NodeJSExpress MySQL - get all customers request

Sourcecode

Sequelize-One-to-One-NodeJS-Express-MySQL



By grokonez | April 20, 2018.

Last updated on May 2, 2021.



Related Posts


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

  1. this line of code is giving error saying “col is not defined”;
    where: { fk_customerid: db.Sequelize.col(‘customer.uuid’) },

  2. I tried above Sequelize One-To-One association – NodeJS/Express, MySQL, This example generates a one-to-many cardinality relationship in the ERD diagram(generated using reverse engineering from MySQL workbench) though we specify one to one relationships.
    Address.belongsTo(Customers, {foreignKey: ‘fk_customerid’, targetKey: ‘uuid’});
    Customers.hasOne(Address, {foreignKey: ‘fk_customerid’, targetKey: ‘uuid’});

    Can you please help me to resolve this issue?

Got Something To Say:

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

*