Express.js with MySQL

Integrating MySQL with Express.js allows developers to create robust applications that can handle structured data efficiently. MySQL is a widely used relational database management system that stores data in tables and supports SQL queries. In this guide, we'll demonstrate how to set up MySQL with Express.js and perform basic database operations.

 

Key Features of Express.js with MySQL

• Easy Integration: MySQL can be easily connected to Express.js through a simple Node.js MySQL client.
• SQL Queries: Perform various SQL operations such as SELECT, INSERT, UPDATE, and DELETE.
• Asynchronous Queries: Express.js works with MySQL asynchronously, making it more efficient and fast.

 

Setting Up Express.js with MySQL

Install Dependencies
First, install the mysql package to connect your Express application with MySQL.

npm install express mysql

Setup MySQL Connection
Set up the connection to your MySQL database using the mysql module in your Express app.

Example:

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

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');
});

 

Performing Basic CRUD Operations

1. Create (Insert Data)

Use an SQL INSERT query to add new data to a table in your MySQL database.

Example:

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).send('Error adding user');
        }
        res.send('User added successfully');
    });
});

2. Read (Fetch Data)

Retrieve data from your MySQL database using a SELECT query.

Example:

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)

Use an UPDATE query to modify data in your MySQL database.

Example:

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).send('Error updating user');
        }
        res.send('User updated successfully');
    });
});

4. Delete (Remove Data)

Use a DELETE query to remove data from your MySQL database.

Example:

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 with MySQL

const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');

const app = express();
app.use(bodyParser.json());

// MySQL connection
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
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).send('Error adding user');
        }
        res.send('User added successfully');
    });
});

// Read
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);
    });
});

// Update
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).send('Error updating user');
        }
        res.send('User updated successfully');
    });
});

// Delete
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');
    });
});

// Server
app.listen(3000, () => {
    console.log('Server running on port 3000');
});

 

Summary

Express.js and MySQL provide a powerful combination for building web applications that require structured data management. By performing CRUD operations, developers can easily interact with MySQL databases, enabling effective data manipulation in their applications.