Previous

Express.js MySQL CRUD Operations

In this guide, we will walk through the process of integrating MySQL with Express.js to perform the most common CRUD operations—Create, Read, Update, and Delete. These operations allow you to interact with MySQL databases efficiently in your Express applications.

 

Key Features of Express.js MySQL CRUD Operations

  • Efficient Data Manipulation: Perform standard database operations like adding, retrieving, modifying, and deleting data in MySQL.
  • Simple Setup: Easy integration of MySQL with Express.js to manage your data.
  • Asynchronous Queries: MySQL queries run asynchronously, ensuring that your application remains responsive.

 

Setting Up Express.js with MySQL

Install Dependencies
Start by installing the required dependencies: express and mysql.

npm install express mysql

Create MySQL Database and Table
Ensure that you have a MySQL database and table set up for performing CRUD operations. Here’s an example of a users table:

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

Setup MySQL Connection in Express
Create a connection to the MySQL database.

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

 

CRUD Operations with MySQL

1. Create (Insert Data)

To insert data into the users table, we use the INSERT SQL query.
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)

Use the SELECT query to fetch data from the database.
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)

Update an existing record using the UPDATE query.
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 the DELETE query to remove a record from the 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

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

By integrating MySQL with Express.js, you can efficiently manage your application's data through standard CRUD operations. This approach ensures that your application is capable of handling a variety of database tasks, including adding, retrieving, updating, and removing records from a MySQL database.

Previous