Published: June 26 2022

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

Path: /_helpers/db.js

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

The initialize() function is executed once on API startup and performs the following actions:

  • Connects to MySQL db server using the mysql2 db 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 User model and attaches it to the exported db object.
  • 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
const config = require('config.json');
const mysql = require('mysql2/promise');
const { Sequelize } = require('sequelize');

module.exports = db = {};


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

Path: /users/user.model.js

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

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

Path: /users/user.service.js

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 = {
    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: } })) {
        throw 'Email "' + + '" is already registered';

    const user = new db.User(params);
    // hash password
    user.passwordHash = await bcrypt.hash(params.password, 10);

    // save user

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

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

On Twitter or RSS.

When I'm not coding...

Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!


Supported by