Performing CRUD in MYSQL with Express JS

Introduction

CRUD stands for Create, Read, Update, and Delete. It is a set of four fundamental procedures often used while working with databases, especially relational databases such as MySQL, PostgreSQL, and Oracle.

• Create: This process involves adding new data to the database. The INSERT statement is commonly used to do this in SQL.
• Read: This process involves finding the data from the database. The SELECT statement is commonly used to do this in SQL.
• Update: This process involves modifying the database's current data. The UPDATE statement in SQL is often used to do this.
• Delete: This process involves removing the data from the database. In SQL, this is done via the DELETE statement.

Install Prerequisite for Express.js

To get started with Express.js performing CRUD in MYSQL, we have to install node.js and npm (Node Package Manager) on our machines. The following are the steps for installing the prerequisites:

• Nodejs: Download the latest and most stable version of Node.js for the operating system. Install Node.js in the system.

• Now we will check that Node.js is completely installed in our system or not by running a command in the command prompt or the terminal window:

The above command will return the version installed on the machine.

• Npm: npm is the package manager for Node.js which allows users to easily install and manage packages and dependencies.
• Now we will check that npm is completely installed in our system or not by running a command in the command prompt or the terminal window:

The above command will return the version installed in the machine and helps in performing CRUD in MYSQL with Express.js.

Create CRUD Express App

Now, let’s discuss how to create a crud express app in the below steps:

• Create a new directory for the project and navigate it in the terminal:

• Initialising a new Node.js project using npm:

• Installing the Express framework, body-parser and MySQL using npm:

  Here, body-parser is used to parse HTTP request bodies and the MySQL package which is a Nodejs driver for MySQL.

• Creating a new file called index.js.

• Setting up a basic express.js server in index.js.

  In the above code, we have created an express.js app, defined a basic route for each CRUD operation and set up the MYSQL connection pool. We will then add the route handlers for each operation in the next steps.

• Adding a route handler for the create operation(INSERT).

  Here we have added a new route handler for the /users endpoint that accepts a POST request with a JSON payload containing the name and email address fields for the new user. We will use the pool.query method to execute the SQL INSERT statement to insert a new user into the database. If there is an error, we will return a 500 error response, otherwise, we will return a 200 success response.

• Adding route handler for the Read (SELECT), Delete (DELETE) operations, and Update (UPDATE).

  Here we have added the route handlers for the Read, Update, and Delete operations. The user's endpoint will accept a GET request to retrieve all the users, a PUT request to update the user by ID, and a DELETE request to delete the user by ID. We will use the pool.query method to execute SQL statements for each operation, and return appropriate responses based on the success or the failure of each operation.

  Now, the CRUD Express.js app is completed. We can start it by running the command in the terminal:

Create Database, Table, and Connection for node.js CRUD Example with MySQL

In the below steps, we will discuss how to create an MYSQL database, and table, and establish a connection to it for a Node.js CRUD application:

• Installing required packages:

• Creating an MYSQL database and table:

• Establishing a connection to MYSQL in our Node.js application:

• We can now use this connection object to perform CRUD operations on the my_table table.

  Below is an example of how to fetch all the records from the table.

Create CRUD Routes

Below are the steps on how to create CRUD Routes in Nodejs using Express and MYSQL.

• Installing the required packages

• Creating a server.js file

Run the Express Files

To run the express.js files we need to first save the code in the file named server.js. Then we will start the server with the following command:

This command will start the server at port 3000. We can access the routes we defined in the code by opening a web browser and navigating to http://localhost:3000/.

Deployment

We can deploy the Node js and MYSQL application via the below steps:

• Choose a hosting provider – There are many hosting providers such as AWS, Heroku, and Google Cloud Platform which help in performing CRUD in MYSQL with Express.js.

• Server setup – Depending on the hosting provider you choose, you may need to create a server instance. You may need to configure your server to meet your application's needs.

• Upload your code - You can upload your code to your server using FTP or other file transfer methods. Alternatively, you can deploy your code using a version control tool such as Git.

• Install dependencies - After uploading your code, you need to run npm install to install dependencies.

• Set environment variables - You may need to set environment variables for your application.

• Start the server - Finally, run node server.js or use a process manager like PM2 to start the server to see Express.js performing CRUD in MYSQL.

These are general deployment instructions only. The exact steps may vary depending on your chosen hosting provider and application needs.