Express.js with MySQL

Integrating MySQL with Express.js is a fundamental skill for building data-driven web applications. While NoSQL databases like MongoDB are popular, MySQL remains a standard for applications requiring high data integrity, complex relationships, and structured schemas. By combining the speed of the Express framework with the reliability of MySQL, you can build scalable backends for everything from e-commerce platforms to internal dashboard tools.

Key Features of Express.js with MySQL

• Relational Data Mapping: MySQL allows you to define strict relationships between tables (like users and their orders), ensuring data consistency.
• Performant SQL Queries: Leverage the full power of SQL to filter, join, and aggregate data directly on the database server.
• Asynchronous Execution: Node.js handles database queries non-blockingly. This means your server can continue processing other user requests while waiting for the database to return results.
• Mature Ecosystem: With decades of community support, finding solutions for MySQL-specific challenges is easy.

Setting Up Express.js with MySQL

1. Install Dependencies
To get started, you need the Express framework and a driver to communicate with MySQL. Open your terminal in your project folder and run:

npm install express mysql

2. Setup MySQL Connection
You need to create a connection object that contains your database host, username, password, and the specific database name you want to use.

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

// Configure the connection
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password', // Replace with your actual password
    database: 'mydatabase'
});

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

Performing Basic CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These four operations form the backbone of almost any dynamic website.

1. Create (Insert Data)

To add a new record, we use the INSERT INTO SQL command. We use placeholder marks (?) to pass data safely into the query.

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 successfully with ID: ${result.insertId}`);
    });
});

2. Read (Fetch Data)

To retrieve data, use the SELECT command. This is commonly used for profile pages or listing items in an admin panel.

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');
        }
        // Send the data back as a JSON array
        res.json(results);
    });
});

3. Update (Modify Data)

Updating allows users to change their existing information. We use the UPDATE command combined with a WHERE clause to target a specific record.

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

4. Delete (Remove Data)

To remove a record, use the DELETE command. Be careful: without a WHERE clause, you could accidentally delete all rows in a table!

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

Complete Example of CRUD Operations with MySQL

Here is a complete, copy-pasteable example. This script sets up a basic Express server, connects to MySQL, and provides a full set of API endpoints.

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

const app = express();

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

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

// Connect to the database
db.connect((err) => {
    if (err) {
        console.error('Failed to connect to MySQL: ' + err.message);
        return;
    }
    console.log('Connected to MySQL database.');
});

// --- API ROUTES ---

// CREATE: Add a user
app.post('/users', (req, res) => {
    const { name, email } = req.body;
    const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
    db.query(sql, [name, email], (err, result) => {
        if (err) throw err;
        res.json({ message: 'User created', id: result.insertId });
    });
});

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

// UPDATE: Modify a user by ID
app.put('/users/:id', (req, res) => {
    const { name, email } = req.body;
    const sql = 'UPDATE users SET name = ?, email = ? WHERE id = ?';
    db.query(sql, [name, email, req.params.id], (err, result) => {
        if (err) throw err;
        res.json({ message: 'User updated' });
    });
});

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

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

Summary

Express.js and MySQL are a powerful duo for building modern, data-intensive web applications. By mastering these CRUD operations, you’ve built the foundation for any software that needs to store and manage user data. Remember to always sanitize your inputs using parameterized queries and consider moving to connection pooling as your application grows in complexity.