Connecting a Database to benhall/express-demo

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:

1. Install MySQL driver: First, you need to install the MySQL driver for Node.js. In your terminal, run the following command:

npm install mysql2

2. 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.

3. Set up Sequelize models: Create a new directory named models and add a new file named index.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;
};

4. 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;

5. 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.