Node.js MySQL with Express.js

Section (2.11) – Node.js MySQL with Express.js

In this tutorial, we will cover the following topics related to integrating Node.js, MySQL, and Express.js:

1. Setting up an Express.js project with MySQL
2. Configuring database connections using Sequelize
3. Creating API endpoints with Express.js
4. Handling user input and validation
5. Error handling and debugging in Express.js

1. Setting up an Express.js project with MySQL

To set up an Express.js project with MySQL, you need to install the required packages and create a basic Express.js application structure.

1.1. Installing required packages

First, create a new folder for your project and navigate to it:

mkdir express-mysql-demo
cd express-mysql-demo

Initialize a new Node.js project by running:

npm init -y

Next, install the required packages:

npm install express sequelize sequelize-cli mysql2

1.2. Creating the Express.js application structure

Create a new file named app.js in your project folder. This file will be the entry point of your Express.js application. Add the following basic Express.js setup code to app.js:

const express = require('express');
const app = express();
const PORT = process.env.PORT || 3000;

app.get('/', (req, res) => {
  res.send('Hello, World!');
});

app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Now you can start your Express.js application by running:

node app.js

Your application should be running at http://localhost:3000.

2. Configuring database connections using Sequelize

To configure database connections using Sequelize, you need to initialize Sequelize and set up your models.

2.1. Initializing Sequelize

Initialize Sequelize by running:

npx sequelize-cli init

This command creates a folder structure containing configuration files, models, migrations, and seeders. Update the config/config.json file with your database connection details:

{
  "development": {
    "username": "your_username",
    "password": "your_password",
    "database": "your_database",
    "host": "localhost",
    "dialect": "mysql"
  }
}

2.2. Creating and configuring models

Create a new model using the sequelize-cli command. For example, to create a User model, run:

npx sequelize-cli model:generate --name User --attributes name:string,email:string,password:string

This command creates a new User model file in the models directory. Open the file and review the generated code.

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    password: DataTypes.STRING
  }, {});
  User.associate = function(models) {
    // associations can be defined here
  };
  return User;
};

3. Creating API endpoints with Express.js

Now that you have configured Sequelize, it's time to create API endpoints using Express.js.

3.1. Defining routes

First, create a new folder named routes in your project folder. Inside the routes folder, create a new file named users.js. This file will contain the routes for the User model.

Add the following code to users.js:

const express = require('express');
const router = express.Router();
const { User } = require('../models');

// Get all users
router.get('/', async (req, res) => {
  try {
    const users = await User.findAll();
    res.json(users);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching users' });
  }
});

// Create a new user
router.post('/', async (req, res) => {
  try {
    const newUser = await User.create(req.body);
    res.status(201).json(newUser);
  } catch (error) {
    res.status(500).json({ message: 'Error creating user' });
  }
});

module.exports = router;

Next, import the users.js route file in your app.js file and register the route:

const userRoutes = require('./routes/users');

app.use('/api/users', userRoutes);

Now your application has two endpoints:

1. GET /api/users: Fetch all users from the database
2. POST /api/users: Create a new user

3.2. Testing the API endpoints

To test your API endpoints, you can use tools like Postman or Insomnia. Alternatively, you can use curl commands in the terminal:

To fetch all users:

curl -X GET http://localhost:3000/api/users

To create a new user:

curl -X POST -H "Content-Type: application/json" -d '{"name": "John Doe", "email": "john@example.com", "password": "password123"}' http://localhost:3000/api/users

4. Handling user input and validation

Handling user input correctly is crucial for the security and reliability of your application. In this section, we will cover input validation and sanitization.

4.1. Validating user input

Before saving user input to the database, it's essential to validate it to ensure it meets the required format and constraints. You can use Sequelize's built-in validation features, as covered in the previous tutorial, or use a library like Express-Validator.

To install Express-Validator, run:

npm install express-validator

Next, update your users.js file to include input validation:

const { check, validationResult } = require('express-validator');

const userValidationRules = [
  check('name')
    .notEmpty().withMessage('Name cannot be empty')
    .isLength({ min: 2, max: 50 }).withMessage('Name must be between 2 and 50 characters'),
  check('email')
    .notEmpty().withMessage('Email cannot be empty')
    .isEmail().withMessage('Email must be valid'),
  check('password')
    .notEmpty().withMessage('Password cannot be empty')
    .isLength({ min: 6, max: 100 }).withMessage('Password must be between 6 and 100 characters')
];

router.post('/', userValidationRules, async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(422).json({ errors: errors.array() });
  }

  // Continue with user creation
});

4.2. Sanitizing user input

In addition to validating user input, it's also essential to sanitize it to prevent security issues such as XSS (Cross-site Scripting) attacks. Express-Validator provides sanitization methods that can be added to your validation rules:

const userValidationRules = [
  // ...
  check('email')
    .notEmpty().withMessage('Email cannot be empty')
    .isEmail().withMessage('Email must be valid')
    .normalizeEmail(),
  // ...
];

In this example, the normalizeEmail() method is added to the email validation chain to sanitize the email address.

5. Error handling and debugging in Express.js

Handling errors and debugging your Express.js application effectively is crucial for maintaining a reliable and secure system.

5.1. Centralized error handling

Instead of handling errors individually in each route, it's a better practice to use centralized error handling middleware. This allows you to handle errors consistently and reduces code duplication.

Create a new file named errorHandler.js in your project folder and add the following code:

const errorHandler = (err, req, res, next) => {
  console.error(err.stack);
  res.status(err.status || 500).json({ message: err.message || 'Internal Server Error' });
};

module.exports = errorHandler;

Import the error handler in your app.js file and add it as the last middleware:

const errorHandler = require('./errorHandler');

// ...

app.use(errorHandler);

Now, whenever an error occurs in your application, it will be passed to the error handler middleware, which will log the error and send an appropriate response.

5.2. Debugging with the debug module

To make debugging easier, you can use the debug module. This module allows you to selectively enable or disable debug output.

First, install the debug module:

npm install debug

Next, replace console.log and console.error statements with the debug module:

const debug = require('debug')('app:main');
const error = require('debug')('app:error');

// ...

app.listen(PORT, () => {
  debug(`Server is running on port ${PORT}`);
});

In your errorHandler.js file, update the error logging:

const error = require('debug')('app:error');

// ...

const errorHandler = (err, req, res, next) => {
  error(err.stack);
  res.status(err.status || 500).json({ message: err.message || 'Internal Server Error' });
};

To enable debugging, set the DEBUG environment variable when starting your application:

DEBUG=app:* node app.js

This command enables both app:main and app:error namespaces. If you only want to enable error logging, you can use:

DEBUG=app:error node app.js

FAQs

Q: How do I connect my Express.js application to a MySQL database?

A: You can use an ORM like Sequelize to connect your Express.js application to a MySQL database. Install Sequelize and the mysql2 package, configure your database connection details, and define your models to interact with the MySQL database.

Q: How can I validate user input in an Express.js application?

A: You can use the Express-Validator library to validate user input. Install the package, define validation rules, and add the rules to your route handlers. The library will automatically validate user input and return an array of errors if the input does not meet the specified rules.

Q: How can I handle errors consistently in my Express.js application?

A: Use centralized error handling middleware to handle errors consistently across your application. Create a custom error handler middleware that logs the error and sends an appropriate response to the client.

Q: What is the purpose of the debug module in Express.js applications?

A: The debug module allows you to selectively enable or disable debug output in your application. It helps you control which debug messages are displayed, making it easier to debug your application during development.

Q: How do I create API endpoints in my Express.js application?

A: In Express.js, you can create API endpoints by defining routes using the express.Router() object. Define route handlers for different HTTP methods (GET, POST, PUT, DELETE, etc.) and associate them with specific paths. Register your routes in the main app.js file using app.use().