WittCode💻

Node and Express - Connect to MySQL Database with Sequelize

By

Connect Express to a MySQL database using Sequelize. Learn about Sequelize models, the MVC design pattern, and MySQL.

Table of Contents 📖

What is Persistence?

In programming, persistence means saving data for future use. This is typically done with a database. A database is simply a collection of structured data. A common example of persistence in an application is saving user information, such as username and password, when a user creates an account. When the user visits the application again and logs in, we compre the provided login credentials to the ones stored in the database.

Installing Sequelize

To connect to a database with our Express application, we are going to be using Sequelize. We need to install it with npm.

npm install sequelize

What is Sequelize?

Sequelize is a promise-based object-relational mapper (ORM) library. Essentially, Sequelize allows us to write SQL queries with JavaScript.

What is an ORM?

An object-relational mapper (ORM) is a technique that allows us to query and manipulate data from a database in an object oriented way. The object is the object we will create with JavaScript, relational is the RDBMS we will use (for us this is MySQL), and mapper is the bridge between our JavaScript objects and MySQL tables.

What is MySQL?

MySQL is a relational database management system (RDBMS). A RDBMS is a type of database management system that stores data in rows in tables. These tables often have relationships with each other. For example, our application could have a users table and a posts table. A relationship between these tables could be that a user owns a post (as they wrote it).

Installing MySQL Driver

To use MySQL with Sequelize, we need to install the MySQL driver. The MySQL driver is an npm package called mysql2.

npm install mysql2

What is mysql2?

mysql2 is an npm package that gives us a MySQL client for communicating with MySQL using Node.

Installing MySQL and MySQL Workbench

To start storing information, we need to create a database. We will create a database using MySQL Workbench. If MySQL Workbench, or MySQL, are not installed they can be installed by watching the following video: https://www.youtube.com/watch?v=OM4aZJW_Ojs&t=322s&ab_channel=AmitThinks.

What is MySQL Workbench?

MySQL Workbench is a GUI for working with MySQL. MySQL Workbench integrates database design, creation, maintenance, etc. into an easy to use GUI. MySQL Workbench is essentially a GUI for the MySQL Server.

Creating a Database with MySQL Workbench

Before we begin storing data from our application, we need to first create a database. We will do this with MySQL Workbench. To create a database, click on the following button on MySQL Workbench.

Image

When prompted, name this schema my-express-app and then press next until finish.

Creating a Database Configuration File

Before we connect to the database we made, lets create a configuration folder and place a file called db-config.js inside.

Image

This configuration folder will hold all our configuration files. One of these files, db-config.js, will contain all the configuration information for connecting to our database.

module.exports = {
    HOST: 'localhost',
    USER: 'root',
    PASSWORD: 'toor',
    DATABASE: 'my-express-app',
    DIALECT: 'mysql'
}

Specifically, these variables will be passed to Sequelize to form a connection to our database. Note that the user and password values of this exported object might be different depending on how MySQL was setup.

Creating a Models Folder to Work with our Database

The creation of our database connection and MySQL tables will all be done inside a folder called models.

Image

What is a Model?

A model is a sequelize term that represents a table in our database. For example, if we have a users table in our database we can represent it with a Sequelize model called users. Whatever changes we make to our Sequelize model will be reflected in our MySQL table.

Adding Files to our Models Folder

Lets add two files to our models folder: index.js and user.js. The index.js file will instantiate our database connection and the user.js file will house our user model.

Connecting to our Database with Sequelize

Now, lets connect to the database we made inside MySQL Workbench. To do this, lets import Sequelize and our database configuration file into models/index.js.

const dbConfig = require('../config/db-config');
const Sequelize = require('sequelize');

To connect to our database with Sequelize, we need to first create an instance of Sequelize and then provide it some information about our database.

const sequelize = new Sequelize(dbConfig.DATABASE, dbConfig.USER, dbConfig.PASSWORD, {
    host: dbConfig.HOST,
    dialect: dbConfig.DIALECT
});

To our Sequelize constructor, we first pass the name of our database. This is the name (my-express-app) that we gave our database when creating it with MySQL Workbench. Next, we provide Sequelize the username and password to access our database. These will be the username and password that were configured when setting up MySQL.

We then pass Sequelize the hostname of the database. As this database is on our local machine, the hostname is simply localhost. Finally, as this is a MySQL database, we need to let Sequelize know by providing mysql as the dialect.

Exporting our Sequelize Instance

We want this database connection to be accessible to the rest of our application whenever we need to work with it. As such, we want to export our Sequelize isntance. We will export an object called db from this file, so lets attach our Sequelize instance to it.

const db = {};
db.sequelize = sequelize;

Exporting our Sequelize Model

We also want to export each Sequelize model we create. Remember, a model is a representation of a MySQL table. For example, this application will have a table of all registered users.

db.models = {};
db.models.User = require('./user')(sequelize, Sequelize.DataTypes);

The file user.js will represent our user table, it will export a function that takes two arguments, our Sequelize instance and Sequelize data types. These data types will be used to set the data type of our user model columns. Now, lets export the db object we made.

module.exports = db;

Creating our Sequelize User Model

Now lets create our user model. This model will hold the data for our registered users. We will create this model inside model/user.js. This file will export a function that returns our user model.

module.exports = (sequelize, DataTypes) => {
}

Lets use the passed in Sequelize instance to create our model. We can create a model with Sequelize using the method define.

const User = sequelize.define('user', {}, {});

The first argument supplied to sequelize.define() is the name of the model. We want to name our model user. The next argument is an object that describes the columns in our model. The third argument is an object that can be used to customize our model even further.

Giving our Sequelize Model Columns

In our user model we want two columns: username and password. We also want each of these columns to be strings.

module.exports = (sequelize, DataTypes) => {

    const User = sequelize.define('user',
    {
        username: DataTypes.STRING,
        password: DataTypes.STRING
    },
    {
        
    });
}

So, we pass an object as the second argument to sequelize.define() where each property is a column. Specifically, the key is the name of the column and the value is the type of column it will be. For example, if we wanted an age column we would add "age: DataTypes.INTEGER" to the second argument.

Customizing our Sequelize Model

Something Sequelize does automatically is pluralize the model name we provide it. For example, the name "user" we provided here would be transformed to "users" in MySQL. If we don't want this to happen, we can customize our model by supplying properties to the third object argument.

module.exports = (sequelize, DataTypes) => {

    const User = sequelize.define('user',
    {
        username: DataTypes.STRING,
        password: DataTypes.STRING
    },
    {
        freezeTableName: true
    });
}

The property freezeTableName, when set to true, will tell Sequelize not to pluralize our model name.

Returning our Sequelize Model

The last thing we need to do is return this Sequelize model.

module.exports = (sequelize, DataTypes) => {

    const User = sequelize.define('user',
    {
        username: DataTypes.STRING,
        password: DataTypes.STRING
    },
    {
        freezeTableName: true
    });
    
    return User;
}

When we require this file in models/index.js this user model will be attached to the db object we created.

Syncing Sequelize with MySQL

We have connected our Express application to our database and created a Sequelize model. Now we need to tell MySQL to create a table in our database using our Sequelize model. We want to do this inside our main index.js file. First, lets import our Sequelize instance.

const db = require('./models');

This is the db object that we exported from models/index.js. Remember, attached to this object is our Sequelize instance and all the models we created. Now, to sync up our Sequelize models with MySQL, we need to use sequelize.sync(). This is an asynchronous method so lets wrap it in an async IIFE so we can use the await keyword.

(async () => {
    await db.sequelize.sync();
})();

What is an IIFE?

An IIFE or immediately invoked function expression is a JavaScript function that runs when it is defined. An IIFE is first wrapped with parenthesis and then called. IIFEs are often used to avoid polluting the global namespace.

Checking The User Table in MySQL Workbench

If we head over to MySQL Workbench we can see the created table. To see more information about the user table, double click on our database name (my-express-app) and then create a new SQL file at the top left. Then if we write the statement "SELECT * FROM user" we should see something similar to our Sequelize Model.

Image

Extra Columns in our MySQL Table

When inspecting the results from our SQL query, there are 3 columns present that we did not make: id, createdAt, and updatedAt. The id column will be the primary key of our table. We can create our own primary key column but it is simpler to let Sequelize do this for us. This default primary key is an integer and will increment by one with each new row.

What is a Primary Key?

A primary key is a special column in a relational database that uniquely identifies each row in a table.

createdAt and updatedAt Columns

The createdAt column lists the time a row was added to the table. The updatedAt column lists the time a row was most recently updated. We can also choose to not have the createdAt and updatedAt columns by customizing our Sequelize model, but there is no harm in having them here.

Our Application is a MVC Design

We are setting up our application with a Model View Controller (MVC) design pattern. Lets create a folder called controllers and add a user.js file and an index.js file.

Image

What is MVC?

Model View Controller, or MVC, is a software design pattern that separates the program into three parts: the model, view, and controller. The model is the part that works with data. The view is the part that works with the UI. The controller is the interface between the model and view that manipulates the data and interacts with the UI. In this framework, the model is our sequelize models, the view is our pug templates, and the controller is what we are about to make.

Creating our User Controller

The controller file user.js is going to be responsible for anything related to users in our database. For example, removing a user from the database would be performed by our user controller, so would adding a user, updating a user, etc. As our user.js controller interacts with our user database, lets import our user Sequelize model.

const { models: { User } } = require('../models');

The object we want from our models/index.js file is db.models.User. As db is exported from models/index.js, we can access it by destructuring the nested object. Our user.js file will export an object containing all our controller functions. As of now, we are just going to be creating a method to add users to our database.

module.exports = {
    create: async (request, response) => {
    }
}

Request and Response?

The request and response objects that this method takes will come from a POST call to /login. When a user submits the login form, the /login POST route will be called, we will pass this function as the middleware to the route. As the request will be a POST request, we know that the data will be inside the request's body. So, lets first check if the username and password were filled out.

if (request.body.username && request.body.password) {
}

If both the username and password are present, then we want to add that user to our database. We do this with the Sequelize model create() method.

if (request.body.username && request.body.password) {

    const { username, password } = request.body;

    await User.create({
        username,
        password
    });
}

Sequelize Model Create Method

The sequelize create() method adds a row to a table in our database. The create() method is asynchronous so we need to wait for it to finish. After the user has been added to the database, we want to render their profile page and pass it their username.

response.render('profile', { username });

Creating a Profile Pug File

Lets create a file called profile.pug in our views that will display the user's username after they have been added to the database.

doctype html
html
    head
    title #{username}
    body
    h1 #{username}'s Profile
    h2 Hey everyone! Welcome to my profile!

No Username or Password were Entered

If the username and password were not present in the request, we will just send a message to the client saying "Not added to the database!".

else {
    response.send('Not added to database.');
}

Adding our User Controller to index.js

Now, lets add our user controller to our controllers/index.js file. This isn't necessary as we can directly import our user.js file, but in this case we will use index.js to keep track of all our controllers at once. Place the following inside controllers/index.js.

const controllers = {};
controllers.user = require('./user');
module.exports = controllers;

Adding User Controller to /login POST Route

Now, lets add our user controller create function to our /login POST route. First, lets import our user controller by using object deconstruction.

const { user }  = require('../../controllers');

Then lets add our create function to the routes/login/index.js POST route.

router.post('/', user.create);

Now, anytime a POST request is made to /login, we will call our user.create function. In other words, everytime a POST request is made to /login, if a username and password are present in the payload, that user will be added to our database.

Running our Application

So lets fill out our user registration form and submit it.

Image

After submitting the form, lets check our MySQL database for the user.

Image

After the data is stored in the database, the user is sent to their profile page.

Image

Summary

But there we have it! If this article we helpful, please consider donating at the top of the page, sharing this article, and subscribing to my YouTube channel WittCode!

Node and Express - Connect to MySQL Database with Sequelize