Express.js MySQL CRUD Operations

In the world of web development, CRUD (Create, Read, Update, Delete) represents the four basic functions of persistent storage. Building a data-driven application requires a solid foundation in how your backend communicates with your database. In this guide, we will walk through the process of integrating MySQL with Express.js to perform these essential operations, transforming a static server into a dynamic, data-aware application.

Key Features of Express.js MySQL CRUD Operations

• Reliable Data Persistence: Leverage MySQL's relational structure to store complex user data, product catalogs, or transaction logs securely.
• Standardized Communication: Use Structured Query Language (SQL) to interact with your data, a skill that transfers across almost all relational database systems.
• Non-Blocking Performance: Express.js handles database queries asynchronously, allowing your server to process other incoming requests while waiting for the database to respond.

Setting Up Express.js with MySQL

Install Dependencies
To get started, you need to install the Express framework and the MySQL driver for Node.js. Run the following command in your project terminal:

npm install express mysql

Create MySQL Database and Table
Before writing any JavaScript, you need a place for your data to live. Open your MySQL workbench or terminal and create a table. For this tutorial, we’ll build a simple user management system:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT
);

Setup MySQL Connection in Express
Now, let's establish the bridge between your Express server and the MySQL database.

const mysql = require('mysql');
const express = require('express');
const app = express();

// Database configuration
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'mydatabase'
});

// Attempt to connect
db.connect((err) => {
    if (err) {
        console.error('Database connection failed: ' + err.stack);
        return;
    }
    console.log('Connected to MySQL successfully as id ' + db.threadId);
});

CRUD Operations with MySQL

1. Create (Insert Data)

The "Create" operation allows you to add new records. In a REST API, this is usually handled by a POST request where the data is sent in the request body.

app.post('/add-user', (req, res) => {
    const { name, email, age } = req.body;
    const query = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
    
    db.query(query, [name, email, age], (err, result) => {
        if (err) {
            console.error(err);
            return res.status(500).send('Error adding user to database');
        }
        res.status(201).send(`User added with ID: ${result.insertId}`);
    });
});

2. Read (Fetch Data)

Fetching data is typically done via a GET request. You can fetch all records or filter them based on specific criteria.

app.get('/users', (req, res) => {
    const query = 'SELECT * FROM users';
    db.query(query, (err, results) => {
        if (err) {
            return res.status(500).send('Error fetching users');
        }
        res.json(results);
    });
});

3. Update (Modify Data)

Updating records requires an identifier (usually an id) to ensure you are changing the correct entry. This is standard for PUT or PATCH requests.

app.put('/update-user/:id', (req, res) => {
    const { name, email, age } = req.body;
    const { id } = req.params;
    const query = 'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?';
    
    db.query(query, [name, email, age, id], (err, result) => {
        if (err) {
            return res.status(500).send('Error updating user');
        }
        if (result.affectedRows === 0) {
            return res.status(404).send('User not found');
        }
        res.send('User updated successfully');
    });
});

4. Delete (Remove Data)

The DELETE operation removes a record permanently. Like updates, this always needs a WHERE clause to target a specific row.

app.delete('/delete-user/:id', (req, res) => {
    const query = 'DELETE FROM users WHERE id = ?';
    db.query(query, [req.params.id], (err, result) => {
        if (err) {
            return res.status(500).send('Error deleting user');
        }
        res.send('User deleted successfully');
    });
});

Complete Example of CRUD Operations

Here is how all these pieces fit together into a single, functional Express application. Note that we use express.json() to allow the server to read JSON data sent in the request bodies.

const express = require('express');
const mysql = require('mysql');

const app = express();
app.use(express.json()); // Built-in middleware to parse JSON

// MySQL connection configuration
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'mydatabase'
});

db.connect((err) => {
    if (err) {
        console.error('Database connection failed: ' + err.stack);
        return;
    }
    console.log('Connected to MySQL');
});

// --- CRUD Routes ---

// CREATE: Add a new user
app.post('/add-user', (req, res) => {
    const { name, email, age } = req.body;
    const query = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
    db.query(query, [name, email, age], (err, result) => {
        if (err) return res.status(500).json({ error: err.message });
        res.status(201).json({ message: 'User added', id: result.insertId });
    });
});

// READ: Get all users
app.get('/users', (req, res) => {
    const query = 'SELECT * FROM users';
    db.query(query, (err, results) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(results);
    });
});

// UPDATE: Modify user by ID
app.put('/update-user/:id', (req, res) => {
    const { name, email, age } = req.body;
    const query = 'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?';
    db.query(query, [name, email, age, req.params.id], (err, result) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ message: 'User updated successfully' });
    });
});

// DELETE: Remove user by ID
app.delete('/delete-user/:id', (req, res) => {
    const query = 'DELETE FROM users WHERE id = ?';
    db.query(query, [req.params.id], (err, result) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ message: 'User deleted successfully' });
    });
});

// Start Server
const PORT = 3000;
app.listen(PORT, () => {
    console.log(`Server running on http://localhost:${PORT}`);
});

Summary

By integrating MySQL with Express.js, you've learned how to manage the lifecycle of data within your application. From creating new user records to updating and deleting them, these CRUD operations form the backbone of most web services. As you progress, consider exploring Connection Pooling to handle multiple users simultaneously and ORMs like Sequelize to simplify your SQL queries into JavaScript objects.