Sequelize One-to-One Example – Nodejs Association

Sequelize one to one association example

In the tutorial, I guide how to create Sequelize One-To-One example association models with NodeJS/Express, MySQL.

* Technologies for Sequelize Nodejs Association One to One:
– Nodejs
– Express
– Sequelize
– MySQL

Sequelize One-to-One Example 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? – Tutorial: “Sequelize One-to-One Example Association – Nodejs MySQL”

-> 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 – Sequelize one-to-one Example

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

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": "loizenjava.com",
  "license": "ISC",
  "dependencies": {
    "express": "^4.16.3",
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.6"
  }
}

Setting up Sequelize MySQL connection – Sequelize One-to-One Example

We 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 – Sequelize One-to-One Example

– 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 – Router

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);
}

Nodejs Express RestAPI Controller – Sequelize One-to-One Example

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);
	});

};

Nodejs Express Server.js

In the tutorial “Sequelize One-to-One Example”, we create a server which listens at port 8080 with below code:


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)
})

Read More

Related posts:


– Reference Link: Node.js Express Sequelize Association

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 NodeJS Express - Post Request - Sequelize one-to-one Example
Sequelize One to One Association CRUD RestAPI NodeJS Express – Post Request

Check database after do POST:

Sequelize One to One CRUD RestAPI NodeJS Express MySQL Customer Table - Sequelize one-to-one Example
Sequelize One to One CRUD RestAPI NodeJS Express MySQL Customer Table
Sequelize One to One Association CRUD RestAPI NodeJS -Express MySQL - Address Table
Sequelize One to One Association CRUD RestAPI NodeJS -Express 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 NodeJS Express MySQL - Get All Customers Request
Sequelize One to One Association CRUD RestAPI NodeJS Express MySQL – Get All Customers Request

Sourcecode

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

Leave a Reply

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