Steps to operate MySQL in Express project

As a lightweight framework, it is widely used for its flexibility and efficiency. As a mature relational database management system, MySQL provides powerful data storage and query functions. This article will explain in detail how to operate MySQL database in Express projects, including key steps such as installing configuration, connecting to database, executing SQL statements, and processing query results.

1. Install MySQL module

Before installing, we need to make sure that the MySQL database is installed and run. Their versions can be checked by:

node -v
mysql --version

Next, install the MySQL module in the Express project. Usually we usemysql2package because it provides better performance and asynchronous/Promise support. It can be installed via npm:

npm install mysql2

2. Configure MySQL connection

In Express projects, we usually put the database configuration information in a separate file.config/. Here is a configuration example:

// Introduce mysql2 moduleconst mysql = require('mysql2');
// Create a connection poolconst pool = ({
    host: 'localhost',       // Database host name    user: 'root',            // Database username    password: 'your_password', // Database Password    database: 'your_database' // Database name});
// Export the connection pool = pool;

In this configuration file, we useThe method creates a connection pool. Connection pools can reuse database connections, thereby improving performance and resource utilization.

3. Connect to the database and execute SQL statements

In Express routing or controller, we can introduce a configured database connection pool and execute various SQL statements. Here is a simple example:

const express = require('express');
const app = express();
const pool = require('./config/db-config'); // Introduce database connection pool// Query example('/users', (req, res) => {
    const sql = 'SELECT * FROM users';
    (sql, (error, results) => {
        if (error) throw error;
        (results);
    });
});
// Insert example('/users', (req, res) => {
    const user = ;
    const sql = 'INSERT INTO users (username, password) VALUES (?, ?)';
    (sql, [, ], (error, results) => {
        if (error) throw error;
        ({ message: 'User created successfully', id:  });
    });
});
// Delete example('/users/:id', (req, res) => {
    const id = ;
    const sql = 'DELETE FROM users WHERE id = ?';
    (sql, [id], (error, results) => {
        if (error) throw error;
        ({ message: 'User deleted successfully', affectedRows:  });
    });
});
// Start the serverconst PORT =  || 3000;
(PORT, () => {
    (`Server is running on port ${PORT}`);
});

In the above code, we created three routes to handle the pairsusersAdd, delete, modify and check the table. By using the connection poolqueryMethod, we can easily execute SQL statements and handle results or errors in callback functions.

4. Handle query results and errors

When executing SQL statements, we usually encounter two situations: success or failure. When processing query results, we need to further process and display the results according to business logic. When encountering errors, we should capture and handle the errors in time to avoid program crashes or data loss.

For example, when querying a list of users, we can traverse the query results and print the name of each user:

('SELECT * FROM users', (error, results) => {
    if (error) throw error;
    (user => {
        (`User: ${}`);
    });
});

When inserting, updating or deleting data, we can checkaffectedRowsAttributes to determine whether the operation is successful:

('INSERT INTO users (username, password) VALUES (?, ?)', [username, password], (error, results) => {
    if (error) throw error;
    if ( === 1) {
        ('User created successfully');
    } else {
        ('Failed to create user');
    }
});

This is the end of this article about how to operate MySQL in Express projects. For more related content on Express projects, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!