CRUD Operations with Node JS, Express, and MySQL #programming #javascript #nodejs #backend

In this tutorial, we will learn how to perform CRUD operations using Node.js, Express, and MySQL. CRUD stands for Create, Read, Update, and Delete, which are the basic operations that can be performed on a database.

Before we begin, make sure you have Node.js and MySQL installed on your system. You can download Node.js from the official website (https://nodejs.org) and MySQL from the official website (https://www.mysql.com).

Let’s start by creating a new Node.js project. Open your terminal and navigate to the directory where you want to create your project. Run the following command to create a new Node.js project:

npm init -y

This will create a new package.json file in your project directory. Next, we need to install the required dependencies. Run the following command to install express and mysql:

npm install express mysql

Next, create a new file called app.js in your project directory. This will be our main application file. Open app.js and add the following code to set up our Express application and connect to the MySQL database:

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

const app = express();

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

connection.connect((error) => {
  if (error) {
    console.error('Error connecting to MySQL: ' + error.stack);
    return;
  }

  console.log('Connected to MySQL as id ' + connection.threadId);
});

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

Replace localhost, root, password, and mydatabase with your MySQL server details. This code will create a connection to the MySQL database and start an Express server on port 3000.

Now let’s implement the CRUD operations. We will create routes for creating, reading, updating, and deleting records in a users table. Add the following routes to app.js:

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

    res.json(results);
  });
});

app.post('/users', (req, res) => {
  const { name, email } = req.body;

  connection.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], (error, result) => {
    if (error) {
      res.status(500).json({ error: error.message });
      return;
    }

    res.json({ message: 'User created successfully', id: result.insertId });
  });
});

app.put('/users/:id', (req, res) => {
  const { name, email } = req.body;
  const { id } = req.params;

  connection.query('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, id], (error, result) => {
    if (error) {
      res.status(500).json({ error: error.message });
      return;
    }

    res.json({ message: 'User updated successfully' });
  });
});

app.delete('/users/:id', (req, res) => {
  const { id } = req.params;

  connection.query('DELETE FROM users WHERE id = ?', [id], (error, result) => {
    if (error) {
      res.status(500).json({ error: error.message });
      return;
    }

    res.json({ message: 'User deleted successfully' });
  });
});

These routes will allow us to perform CRUD operations on the users table. The GET route fetches all users from the database, the POST route creates a new user, the PUT route updates an existing user, and the DELETE route deletes a user by their ID.

Finally, start your Express server by running the following command in the terminal:

node app.js

You can now test your CRUD operations using a tool like Postman or curl. Make sure to replace users with the appropriate endpoint for each operation (e.g., /users, /users/:id, etc.).

That’s it! You have successfully implemented CRUD operations using Node.js, Express, and MySQL. Happy coding!