Next.js 13 + MySQL - Connect to MySQL Database with Sequelize in Next.js

Tutorial built with Next.js 13.3.1, Sequelize 6.31.0 and MySQL

This is a quick post to show how to connect a Next.js App to a MySQL database with using Sequelize, and automatically create/update the MySQL database and tables from code.

The example code is from of a Next.js authentication tutorial I posted recently, the full project and documentation is available at Next.js 13 + MySQL - User Registration and Login Tutorial with Example App.

MySQL Database

MySQL is a free and open-source full featured relational database, it's a good option for any size application in production or development.

Sequelize ORM (Object Relational Mapper)

The Sequelize ORM is used to connect, query and manage data in the MySQL database. Sequelize also supports model synchronization to automatically generate database tables and columns based on models defined in code.

Node MySQL 2 (mysql2)

mysql2 is a database client library specific to MySQL. It's the underlying connector library used by Sequelize for MySQL. It's also used directly in the MySQL data context below to automatically create a MySQL database if the db doesn't already exist.

Connect to MySQL in Next.js with Sequelize and MySQL2

Below is the data context class from the example Next.js App, it is used to connect to MySQL with Sequelize and exports an object containing all of the database model objects in the application (currently only User). It provides an easy way to access any part of the database from a single point.

MySQL database initialization

The initialize() function creates the MySQL database if it doesn't exist, initializes db models and adds them to the exported db object. It is called from the API handler when the first request is sent to the Next.js API.

Sequelize User Model

The userModel() function 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.

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/docs/v6/other-topics/scopes/.

import getConfig from 'next/config';
import mysql from 'mysql2/promise';
import { Sequelize, DataTypes } from 'sequelize';

const { serverRuntimeConfig } = getConfig();

export const db = {
    initialized: false,
    initialize
};

// initialize db and models, called on first api request from /helpers/api/api-handler.js
async function initialize() {
    // create db if it doesn't already exist
    const { host, port, user, password, database } = serverRuntimeConfig.dbConfig;
    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 = userModel(sequelize);

    // sync all models with database
    await sequelize.sync({ alter: true });

    db.initialized = true;
}

// sequelize models with schema definitions

function userModel(sequelize) {
    const attributes = {
        username: { type: DataTypes.STRING, allowNull: false },
        hash: { type: DataTypes.STRING, allowNull: false },
        firstName: { type: DataTypes.STRING, allowNull: false },
        lastName: { type: DataTypes.STRING, allowNull: false }
    };

    const options = {
        defaultScope: {
            // exclude password hash by default
            attributes: { exclude: ['hash'] }
        },
        scopes: {
            // include hash with this scope
            withHash: { attributes: {}, }
        }
    };

    return sequelize.define('User', attributes, options);
}

Next.js API Handler

The API handler is a wrapper function for all API route handlers in the Next.js App.

It enables adding global middleware to the Next.js request pipeline and adds support for global exception handling. The wrapper function accepts a handler object that contains a method for each HTTP method that is supported by the handler (e.g. get, post, put, delete etc). If a request is received for an unsupported HTTP method a 405 Method Not Allowed response is returned.

MySQL database initialization

The MySQL database is initialized on the first API request by calling db.initialize(), the method sets the db.initialized property to true after initialization.

NOTE: In development db.initialize() may be triggered multiple times due to hot reloading which is enabled when you start the app with npm run dev, but this does not occur in production (i.e. when you build the app with npm run build then start it with npm start).

import { db, errorHandler, jwtMiddleware } from 'helpers/api';

export { apiHandler };

function apiHandler(handler) {
    return async (req, res) => {
        const method = req.method.toLowerCase();

        // check handler supports HTTP method
        if (!handler[method])
            return res.status(405).end(`Method ${req.method} Not Allowed`);

        try {
            // init db if required
            if (!db.initialized)
                await db.initialize();

            // global middleware
            await jwtMiddleware(req, res);

            // route handler
            await handler[method](req, res);
        } catch (err) {
            // global error handler
            errorHandler(err, res);
        }
    }
}