GitHub - sujeet-mehra/node-mysql-api

Creating a simple REST API with CRUD operations (Create, Read, Update, Delete) for managing "todos" using Node.js and MySQL involves several steps.

How to set up the API using `Express.js`, `mysql2` for database interaction, and basic CRUD operations.

Create a new ToDo
Read all ToDos or a single ToDo
Update a ToDo
Delete a ToDo

Steps to Create a REST API with Node.js and MySQL

Set Up the Project:
- Install Node.js and MySQL (if not installed already).
- Initialize a new Node.js project.
```
mkdir nodejs-todo-api
cd nodejs-todo-api
npm init -y
```
Install Required Dependencies:

You'll need the following dependencies:
- express: Web framework for building the REST API.
- mysql2: MySQL database client.
- body-parser: To parse incoming request bodies.
Install them using npm:
```
npm install express mysql2
```

Create the MySQL Database and Table:

First, set up the database and table. Open MySQL and run:

CREATE DATABASE todos_db;
USE todos_db;

CREATE TABLE todos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Write the Node.js Code:

Create a file called server.js to define the API.

// This line imports the Express library and assigns it to the express variable.
const express = require("express");

// This line imports the MySQL2 library and assigns it to the mysql variable.
const mysql = require("mysql2");

//This line creates an Express application instance.
const app = express();

// This sets the server to listen on port 3000.
// Port numbers are like doors into your server — port 3000 is a common default for development.
const port = 3000;

// Middleware
// express.json() is a built-in middleware function that parses incoming JSON requests.
app.use(express.json());

 // How to check your MySQL port?
 // SHOW VARIABLES LIKE 'port';
// MySQL connection setup
const db = mysql.createConnection({
  host: "localhost", // The hostname where your MySQL server runs
  port: 3306, //// Default MySQL port; add this if your server runs on a non-default port
  user: "root", // Your MySQL username — change this if different
  password: "root", // Your MySQL password — fill this with your actual password
  database: "todos_db", //// The name of the MySQL database you want to connect to
});

 // .connect() is a method provided by the MySQL2 client to initiate a connection to the MySQL server.
db.connect((err) => {
 //err: an error object if the connection failed,
  if (err) {
    console.error("Error connecting to the database:", err);
    return;
  }
  //or null/undefined if the connection succeeded.
  // Log a success message indicating the connection is established.
  console.log("Connected to MySQL database.");
});

// Create a new todo
// req/res is the request/response object in an Express route handler.
// res to send data back to the client who made the HTTP request.
// http://localhost:3000/todos
app.post("/todos", (req, res) => {
 // req.body contains the parsed body of the incoming HTTP request (usually JSON).
  const { title, completed } = req.body;

  // const title = req.body.title;
  // const completed = req.body.completed;

  // This is a SQL query string that will be used to insert a new record into a MySQL table called todos.
  const query = "INSERT INTO todos (title, completed) VALUES (?, ?)";

  // query is the SQL string: "INSERT INTO todos (title, completed) VALUES (?, ?)".
  // [title, completed] is an array of values that replaces the ? placeholders safely.
  // The last argument is a callback function executed when the query finishes.
  // It receives:
     // err: an error object if the query failed,
     // result: the result object returned by MySQL on success.
  db.query(query, [title, completed], (err, result) => {
    if (err) {
      return res.status(500).send(err);
    }

 // If no error, it sends back a 201 Created status (standard for successful resource creation).
 // .json() sends a JSON response with:
     // id: the unique ID automatically generated for the new row (result.insertId).
     // title: the todo title from the request.
     // completed: the todo completion status from the request.
    res.status(201).json({
      id: result.insertId,
      title,
      completed,
    });
  });
});

// Get all todos
// http://localhost:3000/todos
app.get("/todos", (req, res) => {
  const query = "SELECT * FROM todos";
  db.query(query, (err, results) => {
    if (err) {
      return res.status(500).send(err);
    }
    res.status(200).json(results);
  });
});

// Get a single todo by id
// http://localhost:3000/todos/1
app.get("/todos/:id", (req, res) => {
  const { id } = req.params;
  const query = "SELECT * FROM todos WHERE id = ?";
  db.query(query, [id], (err, result) => {
    if (err) {
      return res.status(500).send(err);
    }
    if (result.length === 0) {
      return res.status(404).json({ message: "Todo not found" });
    }
    res.status(200).json(result[0]);
  });
});

// Update a todo by id
// http://localhost:3000/todos/1
app.put("/todos/:id", (req, res) => {
  const { id } = req.params;
  const { title, completed } = req.body;

  const query = "UPDATE todos SET title = ?, completed = ? WHERE id = ?";
  db.query(query, [title, completed, id], (err, result) => {
    if (err) {
      return res.status(500).send(err);
    }
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: "Todo not found" });
    }
    res.status(200).json({ id, title, completed });
  });
});

// Delete a todo by id
// http://localhost:3000/todos/1
app.delete("/todos/:id", (req, res) => {
  const { id } = req.params;
  const query = "DELETE FROM todos WHERE id = ?";
  db.query(query, [id], (err, result) => {
    if (err) {
      return res.status(500).send(err);
    }
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: "Todo not found" });
    }
    res.status(204).send();
  });
});

// Start the server
app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});

Explanation:

Dependencies:
- express is used to handle routing and HTTP requests.
- mysql2 is used to connect to the MySQL database and execute queries.
API Endpoints:
- POST /todos: Creates a new todo.
- GET /todos: Retrieves all todos.
- GET /todos/:id: Retrieves a todo by its id.
- PUT /todos/:id: Updates a todo by its id.
- DELETE /todos/:id: Deletes a todo by its id.

Running the Application:

To start the server, run:
```
node server.js
```
The API should now be running at http://localhost:3000.

app.use(express.json()) is a line of code commonly found in Node.js applications using the Express framework. It enables your Express app to parse incoming JSON data in HTTP request bodies.

🔹 Basic Overview

app.use(express.json());

app is your Express application instance (usually created via const app = express();).
.use() is a method to register middleware.
express.json() is a built-in middleware function that parses incoming JSON requests.

🔹 What is Middleware?

Middleware in Express is a function that has access to the request (req), the response (res), and the next() function. It's used to:

Modify the request or response objects.
End the request-response cycle.
Call the next middleware in the stack.

🔹 Why `express.json()` is Needed

By default, Express doesn't know how to handle the body of a request — especially if it's in JSON format.

For example, when a client sends a POST request with a JSON body like:

{
  "username": "johndoe",
  "password": "123456"
}

Without express.json(), the req.body would be undefined.

With express.json(), Express will:

Parse the JSON string in the request body.
Convert it into a JavaScript object.
Attach that object to req.body.

So you can now do:

app.post("/login", (req, res) => {
  console.log(req.body.username); // johndoe
});

🔹 How It Works Internally

When a request comes in with Content-Type: application/json, express.json() will:
- Read the request stream.
- Parse the raw JSON string.
- Attach the resulting object to req.body.
If the JSON is malformed, it throws a 400 Bad Request error.

🔹 Under the Hood: Based on `body-parser`

Previously, you had to do this explicitly:

const bodyParser = require("body-parser");
app.use(bodyParser.json());

But since Express 4.16.0+, express.json() is built-in, so you don’t need to install body-parser separately for JSON parsing.

🔹 Optional Configuration

You can pass options to express.json():

app.use(
  express.json({
    limit: "10kb", // Limits the size of incoming JSON
    strict: true, // Only parses objects and arrays
    type: "application/json", // Only parse requests with this Content-Type
  })
);

🔹 Common Use Case Example

const express = require("express");
const app = express();

app.use(express.json());

app.post("/api/user", (req, res) => {
  const user = req.body;
  console.log(user); // Logs parsed JSON object
  res.send("User received!");
});

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

Now, sending a POST request with JSON:

POST /api/user
Content-Type: application/json

{
  "name": "Alice",
  "age": 25
}

The server will correctly log:

{ name: 'Alice', age: 25 }

✅ Summary

Feature	Explanation
Purpose	Parses incoming JSON request bodies
Result	Attaches parsed object to `req.body`
Needed for	APIs expecting JSON input
Built-in Since	Express v4.16.0
Replaces	`body-parser.json()`
Error Handling	Sends 400 error for invalid JSON

Testing the API:

You can use tools like Postman or cURL to test the API.

Create a Todo:

POST /todos

{
  "title": "Learn Node.js",
  "completed": false
}

Get All Todos:

GET /todos
Get a Todo by ID:

GET /todos/1

Update a Todo:

PUT /todos/1

{
  "title": "Learn Node.js & Express",
  "completed": true
}

Delete a Todo:

DELETE /todos/1

This is a basic implementation of a REST API using Node.js and MySQL for a "todos" application.

Name		Name	Last commit message	Last commit date
Latest commit History 4 Commits
.gitignore		.gitignore
README.md		README.md
package-lock.json		package-lock.json
package.json		package.json
server.js		server.js

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

How to set up the API using `Express.js`, `mysql2` for database interaction, and basic CRUD operations.

Steps to Create a REST API with Node.js and MySQL

Explanation:

🔹 Basic Overview

🔹 What is Middleware?

🔹 Why `express.json()` is Needed

🔹 How It Works Internally

🔹 Under the Hood: Based on `body-parser`

🔹 Optional Configuration

🔹 Common Use Case Example

✅ Summary

Testing the API:

About

Uh oh!

Releases

Packages

Languages

sujeet-mehra/node-mysql-api

Folders and files

Latest commit

History

Repository files navigation

How to set up the API using Express.js, mysql2 for database interaction, and basic CRUD operations.

Steps to Create a REST API with Node.js and MySQL

Explanation:

🔹 Basic Overview

🔹 What is Middleware?

🔹 Why express.json() is Needed

🔹 How It Works Internally

🔹 Under the Hood: Based on body-parser

🔹 Optional Configuration

🔹 Common Use Case Example

✅ Summary

Testing the API:

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

How to set up the API using `Express.js`, `mysql2` for database interaction, and basic CRUD operations.

🔹 Why `express.json()` is Needed

🔹 Under the Hood: Based on `body-parser`

Packages