Sequelize Tutorial with Node.js
In this tutorial explains about How to integrate Sequelize ORM in your application with node js.
What is Sequelize
Sequelize is a promise-based ORM for Node.js. It supports various databases like PostgreSQL, MySQL, MSSQL, and SQLite. This features are transaction support, relations and read replications.
Install Sequelize
We initialize a Node application and install Sequelize and MySQL adapter.
$ node -v
$ npm init
// Using NPM
$ npm install --save sequelize
Install one of the following database driver in your application.
$ npm install --save pg pg-hstore
$ npm install --save mysql2
$ npm install --save sqlite3
$ npm install --save tedious // MSSQL
Setting up a connection
After the installation steps, create an connection with following contents. Here we can create an connection in two ways.
First method, we can customize the connection pool in models/db.js.
const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
host: 'localhost',
dialect: 'mysql', //|'sqlite'|'postgres'|'mssql'
operatorsAliases: false,
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
},
// SQLite only
storage: 'path/to/database.sqlite'
});
Second one you can simply use a connection uri in models/db.js
const Sequelize = require('sequelize');
const sequelize = new Sequelize('mysql://user:pass@localhost:3306/dbname');
You can use the .authenticate()
function like this to test the connection.
sequelize
.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.error('Unable to connect to the database:', err);
});Added above code at the end of the models/db.js file.
Create a Model
Models are defined with following contents in models/user.model.js
const { Sequelize, DataTypes } = require('sequelize');
module.exports = (Sequelize, DataTypes) => {
const User = Sequelize.define('user', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: {
type: DataTypes.STRING
},
email: {
type: DataTypes.STRING
},
password: {
type: DataTypes.STRING
}
});
return User;
}
force: true will drop the table if it already exists. This method is recommended for development mode.
User.sync({force: true}).then(() => {
// Table created
return User.create({
name: 'John',
email: 'john123@example.com',
password: '123456'
});
});
How to write a query
This helps you how to make the standard CRUD queries.
Simple INSERT Query
// Create a new user
const user = await User.create({ name: "John", email: "John123@exmaple.com", password: "12346" });
console.log("PK ID:", user.id);
Simple SELECT queries
// Find all users
const users = await User.findAll();
console.log("All users:", JSON.stringify(users, null, 2));
Simple UPDATE queries
await User.destroy({
where: {
email: 'John123@example.com'
}
});
Simple DELETE queries
await User.destroy({
truncate: true
});
Use below code for Truncate the table.
await User.destroy({ truncate: true });
Originally published at https://www.phpexpertise.com on August 4, 2020.
Read article at https://www.phpexpertise.com/sequelize-orm-tutorial-with-node-js-and-express/