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
– 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:
Check database after do POST:
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:
