Creating a basic CRUD (create, read, update, delete) API in NodeJS using the ExpressJS framework and a MySQL database is a straightforward process. In this tutorial, we will walk through the steps required to build a simple API that allows users to create, read, update, and delete records in a database.
Prerequisites
Before you begin, you will need to have the following tools installed on your system:
- NodeJS: NodeJS is a JavaScript runtime that allows you to execute JavaScript code outside of a browser. You can download and install NodeJS from the official website (https://nodejs.org/).
- MySQL: MySQL is a popular open-source relational database management system. You can download and install MySQL from the official website (https://www.mysql.com/downloads/).
Step 1: Setting up the project
First, we will create a new NodeJS project and install the required dependencies. Open a terminal and navigate to the directory where you want to create the project. Then, run the following commands:
mkdir crud-apicd crud-apinpm init -y
The npm init command will create a package.json file in the project directory. This file stores information about the project and its dependencies.
Next, we will install the ExpressJS and MySQL libraries. Run the following command:
npm install express mysql --save
This will install the ExpressJS and MySQL libraries and save them as dependencies in the package.json file.
Step 2: Setting up the database
Next, we will set up the MySQL database. Open the MySQL command-line client and create a new database by running the following command:
1 | CREATE DATABASE crud_api; |
Next, create a new table to store records by running the following command:
1 2 3 4 5 6 7 | USE crud_api; CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL ); |
This will create a new table called users with three columns: id, name, and email. The id column is set as the primary key and will be automatically incremented for each new record.
Step 3: Setting up the server
Now, we will set up the server using the ExpressJS framework. Create a new file called server.js in the project directory and add the following code:
1 2 3 4 5 6 7 | const express = require('express'); const app = express(); const port = 3000; app.listen(port, () => { console.log(`Server listening on port ${port}`); }); |
This code creates an ExpressJS server and sets it to listen on port 3000. When you run the server, you should see the message “Server listening on port 3000” in the terminal.
Step 4: Connecting to the database
Next, we will connect to the MySQL database from the server. Add the following code to the server.js file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | const mysql = require('mysql'); const connection = mysql.createConnection({ host: 'localhost', user: '<your_username>', password: '<your_password>', database: 'crud_api' }); connection.connect((error) => { if (error) { console.error(error); } else { console.log('Connected to the database'); } }); |
This code uses the `mysql.createConnection()` function to establish a connection to the database. You will need to replace `<your_username>` and `<your_password>` with your own MySQL username and password. If the connection is successful, you should see the message “Connected to the database” in the terminal.
Step 5: Creating Routes for HTTP Methods
You may not be parsing the request body correctly. Make sure that you have the `body-parser` middleware installed and correctly configured in your Express app. You can do this by adding the following lines of code at the top of your file:
1 2 | const bodyParser = require('body-parser'); app.use(bodyParser.json()); |
Now, we will create routes for the different CRUD operations. Add the following code to the `server.js` file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | app.get('/users', (request, response) => { connection.query('SELECT * FROM users', (error, data) => { if (error) { console.error(error); response.status(500).send('Error retrieving users'); } else { response.send(data); } }); }); app.post('/users', (request, response) => { const { name, email } = request.body; connection.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], (error) => { if (error) { console.error(error); response.status(500).send('Error creating user'); } else { response.send('User created successfully'); } }); }); app.put('/users/:id', (request, response) => { const { id } = request.params; const { name, email } = request.body; connection.query('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, id], (error) => { if (error) { console.error(error); response.status(500).send('Error updating user'); } else { response.send('User updated successfully'); } }); }); app.delete('/users/:id', (request, response) => { const { id } = request.params; connection.query('DELETE FROM users WHERE id = ?', [id], (error) => { if (error) { console.error(error); response.status(500).send('Error deleting user'); } else { response.send('User deleted successfully'); } }); }); |
This code sets up four routes for the different CRUD operations:
- GET /users: This route retrieves all records from the users table and sends them back in the response.
- POST /users: This route creates a new record in the users table using the data sent in the request body.
- PUT /users/:id: This route updates an existing record in the users table using the data sent in the request body and the id parameter in the URL.
- DELETE /users/:id: This route deletes an existing record in the users table using the id parameter in the URL.
Step 6: Testing the API
We can test the API using a tool such as Postman. Start the server by running the following command:
node server.js
Then, open Postman and send a GET request to the `/users` route. You should see an empty array in the response since the users table is currently empty.
Next, send a POST request to the `/users` route with a request body containing a name and email for a new user. For example:
1 2 3 4 | { "name": "John Doe", "email": "john.doe@example.com" } |
You should see the message “User created successfully” in the response.
Then, send a GET request to the `/users` route again. This time, you should see an array containing the user you just created.
You can also test the PUT and DELETE routes by sending a PUT request to `/users/1` with a request body containing the updated name and email, and a DELETE request to `/users/1` to delete the user.
That’s it! You now have a basic CRUD API up and running using NodeJS, ExpressJS, and MySQL. Of course, there are many other things you can do to improve and expand upon this API, such as adding validation and error handling, implementing authentication and authorization, and integrating with other databases or APIs.