Tuesday, August 04 2020
In this tutorial explains about How to integrate Sequelize ORM in your application with node js.
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.
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
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:[email protected]: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.
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: '[email protected]', password: '123456' }); });
This helps you how to make the standard CRUD queries.
// Create a new user const user = await User.create({ name: "John", email: "[email protected]", password: "12346" }); console.log("PK ID:", user.id);
// Find all users const users = await User.findAll(); console.log("All users:", JSON.stringify(users, null, 2));
await User.update({ password: "67895" }, { where: { email: '[email protected]' } });
await User.destroy({ where: { email: '[email protected]' } });
Use below code for Truncate the table.
await User.destroy({ truncate: true });