Node.js + MySQL - Connect to MySQL database with Sequelize & MySQL2
Tutorial built with Node.js, Sequelize and MySQL
Other versions available:
This is a quick post to show how to connect from Node.js to MySQL using Sequelize & MySQL2, and automatically create/update the MySQL database from code.
The below code snippets are from a Node.js + MySQL CRUD API tutorial I posted recently, for the full project and instructions on how to run it locally see Node.js + MySQL - CRUD API Example and Tutorial.
MySQL Database Wrapper
The MySQL database wrapper connects to MySQL using Sequelize & the mysql2 library. It exports an object that exposes all of the database models for the application (currently only User), and provides an easy way to access any part of the database from a single point.
MySQL2 is the MySQL connector library used by Sequelize to connect to the MySQL db server. The DB wrapper uses mysql2 directly to connect to MySQL and create the database if it doesn't exist. For more info on MySQL2 see https://github.com/sidorares/node-mysql2.
The initialize() function is executed once on API startup and performs the following actions:
- Connects to MySQL db server using the
mysql2db client and executes a query to create the API database if it doesn't already exist. - Connects to the API database with the Sequelize ORM.
- Initializes the
Usermodel and attaches it to the exporteddbobject. - Automatically creates/updates tables in the MySQL database to match the Sequelize model (if required) by calling
await sequelize.sync({ alter: true }). For more info on Sequelize model synchronization options see https://sequelize.org/master/manual/model-basics.html#model-synchronization.
const config = require('config.json');
const mysql = require('mysql2/promise');
const { Sequelize } = require('sequelize');
module.exports = db = {};
initialize();
async function initialize() {
// create db if it doesn't already exist
const { host, port, user, password, database } = config.database;
const connection = await mysql.createConnection({ host, port, user, password });
await connection.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
// connect to db
const sequelize = new Sequelize(database, user, password, { dialect: 'mysql' });
// init models and add them to the exported db object
db.User = require('../users/user.model')(sequelize);
// sync all models with database
await sequelize.sync({ alter: true });
}
Sequelize User Model
The user model uses Sequelize to define the schema for the users table in the MySQL database. The exported Sequelize model object gives full access to perform CRUD (create, read, update, delete) operations on users in MySQL, see the user service below for examples of it being used (via the db helper).
The defaultScope configures the model to exclude the password hash from query results by default. The withHash scope can be used to query users and include the password hash in results. For more info on Sequelize scopes see https://sequelize.org/master/manual/scopes.html.
const { DataTypes } = require('sequelize');
module.exports = model;
function model(sequelize) {
const attributes = {
email: { type: DataTypes.STRING, allowNull: false },
passwordHash: { type: DataTypes.STRING, allowNull: false },
title: { type: DataTypes.STRING, allowNull: false },
firstName: { type: DataTypes.STRING, allowNull: false },
lastName: { type: DataTypes.STRING, allowNull: false },
role: { type: DataTypes.STRING, allowNull: false }
};
const options = {
defaultScope: {
// exclude password hash by default
attributes: { exclude: ['passwordHash'] }
},
scopes: {
// include hash with this scope
withHash: { attributes: {}, }
}
};
return sequelize.define('User', attributes, options);
}
User Service
The user service is responsible for all database interaction and core business logic related to user CRUD operations, it encapsulates all interaction with the Sequelize user model and exposes a simple set of methods to access and manage the data in MySQL.
The top of the file contains the exported service object with just the method names to make it easy to see all the methods at a glance, the rest of the file contains the implementation functions for each service method, followed by local helper functions.
const bcrypt = require('bcryptjs');
const db = require('_helpers/db');
module.exports = {
getAll,
getById,
create,
update,
delete: _delete
};
async function getAll() {
return await db.User.findAll();
}
async function getById(id) {
return await getUser(id);
}
async function create(params) {
// validate
if (await db.User.findOne({ where: { email: params.email } })) {
throw 'Email "' + params.email + '" is already registered';
}
const user = new db.User(params);
// hash password
user.passwordHash = await bcrypt.hash(params.password, 10);
// save user
await user.save();
}
async function update(id, params) {
const user = await getUser(id);
// validate
const usernameChanged = params.username && user.username !== params.username;
if (usernameChanged && await db.User.findOne({ where: { username: params.username } })) {
throw 'Username "' + params.username + '" is already taken';
}
// hash password if it was entered
if (params.password) {
params.passwordHash = await bcrypt.hash(params.password, 10);
}
// copy params to user and save
Object.assign(user, params);
await user.save();
}
async function _delete(id) {
const user = await getUser(id);
await user.destroy();
}
// helper functions
async function getUser(id) {
const user = await db.User.findByPk(id);
if (!user) throw 'User not found';
return user;
}
Need Some NodeJS Help?
Search fiverr for freelance NodeJS developers.
Follow me for updates
When I'm not coding...
Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!