Scenario:
You have a benhall/express-demo application that you want to extend by storing and retrieving data from a MySQL database. In this example, we will walk you through the process of installing the MySQL driver, configuring the connection, and using Sequelize as an Object-Relational Mapping (ORM) tool to interact with the database.
Solution:
- Install MySQL driver: First, you need to install the MySQL driver for Node.js. In your terminal, run the following command:
npm install mysql2
- Configure the database connection:
Create a new file named
db.js
in the root directory of your project. Add the following code to configure the database connection:
const { Sequelize } = require('sequelize');
const config = require('config');
const sequelize = new Sequelize(
config.get('db.name'),
config.get('db.user'),
config.get('db.password'),
{
host: config.get('db.host'),
dialect: 'mysql',
},
);
module.exports = sequelize;
Replace config.get('db.name')
, config.get('db.user')
, config.get('db.password')
, and config.get('db.host')
with your MySQL database name, username, password, and host, respectively. You can store these values in a .env
file or a config.json
file and use a library like dotenv
or config
to load them into your application.
- Set up Sequelize models:
Create a new directory named
models
and add a new file namedindex.js
inside it. Add the following code to set up Sequelize models:
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const db = require('./db');
const models = {};
fs.readdirSync(__dirname)
.filter((file) => file !== 'index.js')
.forEach((file) => {
const model = require(path.join(__dirname, file))(db, Sequelize);
models[model.name] = model;
});
db.models = models;
module.exports = db;
Create a new file for each model you want to create, e.g., user.js
, and add the following code:
module.exports = (sequelize, Sequelize) => {
const User = sequelize.define('User', {
name: {
type: Sequelize.STRING,
allowNull: false,
},
email: {
type: Sequelize.STRING,
allowNull: false,
unique: true,
},
password: {
type: Sequelize.STRING,
allowNull: false,
},
});
return User;
};
- Use Sequelize models:
Update your routes or controllers to use the Sequelize models. For example, in
routes/users.js
, add the following code:
const express = require('express');
const router = express.Router();
const User = require('../models').User;
router.get('/', async (req, res) => {
try {
const users = await User.findAll();
res.json(users);
} catch (err) {
res.status(500).json({ message: err.message });
}
});
router.post('/', async (req, res) => {
try {
const newUser = await User.create(req.body);
res.status(201).json(newUser);
} catch (err) {
res.status(400).json({ message: err.message });
}
});
module.exports = router;
- Test the connection: To test the database connection, you can run the following command in your terminal:
node -e "const db = require('./db'); db.authenticate().then(() => console.log('Database connection established.'));"
If the connection is successful, you should see the message “Database connection established.” in your terminal.
Tests:
To verify the database connection and the functionality of the database models, you can add the following tests to your test
directory:
test/db.test.js
: Test the database connection.test/users.test.js
: Test the User model.
These tests can include checking if the database connection is established, testing the creation and retrieval of User records, and testing error handling.