Following up on the previous article where we discussed what RESTful APIs are and how to create one in python using Django, in this article we will go over how to create a connection with a MySQL database and then perform crud operations using the data streamed through the database using node.js and express.js. Let us go over how to perform this step by step by creating a REST API for course info.

Creating a table in MySQL & populating it with data

First create a schema and name it courses. Then create a table for all_courses and define the required fields. For this demonstration we will have fields for id, year, course_name, faculty and slot. To do so run the following script.

-- Schema.sql

USE courses;
CREATE TABLE all_courses (
    id integer PRIMARY KEy AUTO_INCREMENT,
    year VARCHAR(255) NOT NULL,
    course_name VARCHAR(255) NOT NULL,
    faculty VARCHAR(255) NOT NULL,
    slot TEXT NOT NULL
);

Notice that the id field is a PRIMARY KEy and it auto-increments. We are not required to pass in the id parameter while pushing data into the table, the table with automatically assign it a value of the id of the previous entry plus one. The remaining fields must be passed when creating entries.
Now let us populate this table with some dummy data. You can change the following data to create your custom entries.

-- Schema.sql

INSERT INTO all_courses (semester, course_name, faculty, slot)
VALUES
('First','Biology101', 'Professor Smith', 'A1'),
('Second','Physics202', 'Dr. Johnson', 'B1'),
('First','Chemistry303', 'Professor Davis', 'C1'),
('Third','History404', 'Dr. Taylor', 'D1'),
('Fourth','Mathematics505', 'Professor Miller', 'E1'),
('First','English101', 'Dr. Brown', 'A1'),
('Second','ComputerScience202', 'Professor White', 'B1'),
('First','Psychology303', 'Dr. Martinez', 'C1'),
('Third','Economics404', 'Professor Turner', 'D1'),
('Second','Sociology505', 'Dr. Hill', 'E1'),
('Fourth','Philosophy101', 'Professor Carter', 'A1'),
('First','Music202', 'Dr. Reed', 'B1'),
('Second','PoliticalScience303', 'Professor Hall', 'C1'),
('First','Geography404', 'Dr. Allen', 'D1'),
('Second','EnvironmentalScience505', 'Professor Scott', 'E1'),
('Fourth','Art101', 'Dr. Garcia', 'A1'),
('First','Linguistics202', 'Professor Lee', 'B1'),
('First','Astronomy303', 'Dr. Adams', 'C1'),
('Third','Marketing404', 'Professor Wright', 'D1'),
('Third','Statistics505', 'Dr. Lopez', 'E1')

Creating a connection with database

Once we have created and populated the database with dummy data we can proceed to create a connection with our MySQL database. First create a new node project using the npm init command. This will initialize a new Node.js project and create a package.json file in the current directory. Now install the MySQL package for your node.js project using npm install mysql2. Now we can write a script to create a connection with our MySQL database.

const mysql = require('mysql2');

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

connection.connect((error) => {
  if (error) {
    console.error('Connection Failed.', error);
  } else {
    console.log('Connected Successfully!');
  }
});

connection.end();

Now we could use this connection to query data from the database like so
connection.query("SELECT * FROM all_courses") however we would be using a pool of connections instead of a single connection.

const mysql = require('mysql2'); 

const connection = mysql.createPool({ 
    host: 'localhost', 
    user: 'root', 
    password: 'password', 
    database: 'database_name' 
}).promise()

Notice the .promise(), it transforms the query method into a function that returns a Promise. This allows you to handle asynchronous operations more conveniently using promises or async/await syntax.
createPool maintains a pool of connections that can be reused. This is beneficial in scenarios where there are frequent database operations, as creating and closing connections repeatedly can be resource-intensive.

Writing a functions to perform crud operations

To perform CRUD operations we will write these functions – getCourses(), addCourse(), updateCourse() and deleteCourse.

async function getCourses() {
    const [rows] = await pool.query("SELECT * FROM all_courses");
        return rows
}

async function addCourse(year, course_name, faculty, slot) {
    const [result] = await pool.query(
        `INSERT INTO all_courses (year, course_name, faculty, slot)
        VALUES (?, ?, ?, ?, ?)`,
        [year, course_name, faculty, slot]
    );
    return result;
}

async function updateCourse(course_name, newFaculty, newSlot) {
    const [result] = await pool.query(
        `UPDATE all_courses
        SET faculty = ?, slot = ?
        WHERE course_name = ?`,
        [newFaculty, newSlot, course_name]
    );
    return result;
}

async function deleteCourse(course_name, faculty, slot) {
    const [result] = await pool.query(
        `DELETE FROM all_courses WHERE course_name = ?, faculty = ?, slot = ?`,
        [course_name, faculty, slot]
    );
    return result;
}

If you are confused why we have used ? to pass the arguments, instead of directly using the variables refer our article on SQL injections to see why we do this.

Setting up the express application for get and post requests

Install express using npm install express and import express, db.js the file where you wrote the functions to query data to and from the database and all the functions.

const express = require('express')
const db = require('./db.js');
const addCourse = db.addCourse;
const getCourses = db.getCourses;
const updateCourse = db.updateCourse;
const deleteCourse = db.deleteCourse;

app.use(express.json())

app.get('/allcourses', async (req, res) => {
    const courses = await getCourses()
    res.send(courses)
})

app.post('/add', async(req, res) => {
    const { year, course_name, faculty, slot } = req.body
    const added_courses = await addCourse(year, course_name, faculty, slot)
    res.status(201).send(added_courses[0])
})

app.put('/update/:course_name', async (req, res) => {
    const course_name = req.params.course_name;
    const { faculty, slot } = req.body;

    try {
        const updatedCourse = await updateCourse(course_name, faculty, slot);
        res.send(updatedCourse[0]);
    } catch (error) {
        console.error(error);
        res.status(500).send('Internal Server Error');
    }
})

app.delete('/delete', async (req, res) => {
    const { course_name, faculty, slot } = req.body

    try {
        const result = await deleteCourse(course_name, faculty, slot);
        if (result.affectedRows > 0) {
            res.status(204);
        } else {
            res.status(404).send('Course not found');
        }
    } catch (error) {
        console.error(error);
        res.status(500).send('Internal Server Error');
    }
});

Our REST API is ready and now you can integrate this in any javascript project.

Note of caution

When pushing code to production of a shared version control system make sure that your connection details to the database like the table, username, password are not accessible to the viewer. This can be done using dotenv. Simple install it using npm install dotenv. Import it into db.js like so
const dotenv = require('dotenv') and configure it using dotenv.config(). Now you can create a .env file in the same directory and replace the host, user, password and database like so.

const pool = mysql.createPool({
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE
}).promise()

Now your database details won’t be visible to people viewing your code.