Creating REST API for Weekly Trip Summaries with Cash & Online Payment using Node.js Express and MySQL

In this tutorial, we will be creating a REST API using Node.js, Express, and MySQL to manage weekly trip summaries with cash and online payment options. This API will allow us to create, read, update, and delete trip summaries and track payments made for each trip.

To get started, make sure you have Node.js installed on your machine. You can download it from the official Node.js website and follow the installation instructions.

Step 1: Setting up the project

Create a new directory for your project and navigate to it in your terminal. Run the following command to initialize a new Node.js project:

npm init -y

This will create a package.json file in your project directory.

Next, install the necessary dependencies for our project:

npm install express mysql body-parser

Express is a minimalist web framework for Node.js, while mysql is a MySQL client for Node.js, and body-parser is a middleware for parsing request bodies.

Step 2: Setting up the MySQL database

Create a new MySQL database and table to store trip summaries and payments. Here is an example schema for our table:

CREATE TABLE trip_summaries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trip_name VARCHAR(255) NOT NULL,
  trip_date DATE NOT NULL,
  cash_payment DECIMAL(10, 2) NOT NULL,
  online_payment DECIMAL(10, 2) NOT NULL
);

You can run this SQL command in your MySQL client to create the table. Make sure to add some sample data to test the API later on.

Step 3: Creating the Express server

Create a new file called server.js in your project directory and add the following code to set up an Express server:

const express = require('express');
const bodyParser = require('body-parser');

const app = express();
const port = 3000;

app.use(bodyParser.json());

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

This code initializes an Express app, sets up the bodyParser middleware to parse JSON request bodies, and starts a server listening on port 3000.

Step 4: Creating REST endpoints

Now, let’s create the REST endpoints to manage trip summaries. Add the following code to your server.js file:

const mysql = require('mysql');

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

// Connect to the database
db.connect((err) => {
  if (err) {
    throw err;
  }
  console.log('Connected to MySQL database');
});

// Get all trip summaries
app.get('/trips', (req, res) => {
  db.query('SELECT * FROM trip_summaries', (err, results) => {
    if (err) {
      throw err;
    }
    res.json(results);
  });
});

// Create a new trip summary
app.post('/trips', (req, res) => {
  const { trip_name, trip_date, cash_payment, online_payment } = req.body;
  db.query('INSERT INTO trip_summaries (trip_name, trip_date, cash_payment, online_payment) VALUES (?,?,?,?)',
    [trip_name, trip_date, cash_payment, online_payment],
    (err, result) => {
      if (err) {
        throw err;
      }
      res.send('Trip summary added');
    });
});

// Update a trip summary
app.put('/trips/:id', (req, res) => {
  const { trip_name, trip_date, cash_payment, online_payment } = req.body;
  db.query('UPDATE trip_summaries SET trip_name=?, trip_date=?, cash_payment=?, online_payment=? WHERE id=?',
    [trip_name, trip_date, cash_payment, online_payment, req.params.id],
    (err, result) => {
      if (err) {
        throw err;
      }
      res.send('Trip summary updated');
    });
});

// Delete a trip summary
app.delete('/trips/:id', (req, res) => {
  db.query('DELETE FROM trip_summaries WHERE id=?', [req.params.id], (err, result) => {
    if (err) {
      throw err;
    }
    res.send('Trip summary deleted');
  });
});

This code creates four endpoints for handling CRUD operations on trip summaries. The GET /trips endpoint fetches all trip summaries, POST /trips creates a new trip summary, PUT /trips/:id updates a trip summary by ID, and DELETE /trips/:id deletes a trip summary by ID.

Step 5: Testing the API

You can now test your API using a tool like Postman or curl. Make various HTTP requests to the endpoints you created to verify that they are working correctly.

For example, you can create a new trip summary by sending a POST request to http://localhost:3000/trips with a JSON payload containing the trip details:

{
  "trip_name": "Weekend getaway",
  "trip_date": "2023-06-10",
  "cash_payment": 100.0,
  "online_payment": 50.0
}

You should receive a success message indicating that the trip summary was added. Similarly, you can test other endpoints to perform CRUD operations on trip summaries.

Congratulations! You have successfully created a REST API using Node.js, Express, and MySQL to manage weekly trip summaries with cash and online payment options. You can further enhance this API by adding authentication, validation, error handling, and other features as needed. Happy coding!