Create a Powerful RESTful API for SQL Server CRUD Operations

Introduction

In the fast-changing world of web development, having a strong and effective REST API is important. This article is here to help you understand how to build a flexible RESTful API using Node.js, smoothly connected with a SQL Server database. We'll mainly look into adding CRUD operations, which are basic but crucial for dealing with databases. This way, you'll get a clear picture of how the development process works.

Tools and Technologies Utilized

For this project, we will harness the power of Node.js, a lightweight and speedy JavaScript runtime known for its versatility in building scalable applications. Complementing Node.js, we'll employ Azure SQL Server as our database solution, taking advantage of its reliability, security features, and seamless compatibility with Node.js.

Installation of Node.js

Before we dive into coding, make sure you have Node.js installed. You can get the latest version from the official Node.js website (https://nodejs.org). Follow the installation instructions to set up Node.js on your computer.

Configuring Azure SQL Server

To establish our database, we'll leverage the capabilities of Azure SQL Server. Create an Azure account if you don't have one, and proceed to provision a SQL Server database. Retrieve the connection string, as it will serve as the bridge between our Node.js application and the database.

Node.js code Implementation and Explanation

Now, let's get into the code. We'll start by setting up our Node.js project, installing the necessary packages, and connecting to the Azure SQL Server. The code will be well-organized and explained step by step, making it easy to follow.

Create a new project folder

Create a new folder for your project and navigate to it using the command line.

mkdir my-rest-api
cd my-rest-api

Initialize a Node.js project

Run the following command to initialize a new Node.js project. It will create a package.json file.

npm init -y

Install required packages

Install Express and MySQL packages using the following commands.

npm install express mssql

Create the main server file

Create a file named app.js and add the following code.

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

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

app.use(bodyParser.json());

const config = {
  user: 'username',                                //Your User Name
  password: 'password',                            //Your Password
  server: 'harunserver.database.windows.net',      //Your Server Name
  database: 'harundb',                             //Your DB Name
  options: {
    port: 1433,
    connectionTimeout: 60000,
  },
};

// Helper function to generate SET clause for UPDATE operation
const generateSetClause = (columns, values) => {
  return columns.map((col, index) => `${col} = '${values[index]}'`).join(', ');
};

app.post('/api/insert', async (req, res) => {
  try {
    await sql.connect(config);

    const { tableName, columns, values } = req.body;

    if (!Array.isArray(columns) || !Array.isArray(values) || columns.length !== values.length) {
      return res.status(400).send('Invalid request body');
    }

    const columnNames = columns.join(', ');
    const columnValues = values.map(value => `'${value}'`).join(', ');

    const result = await sql.query(`INSERT INTO ${tableName} (${columnNames}) VALUES (${columnValues})`);
    res.send(result);
  } catch (err) {
    console.error(err);
    res.status(500).send('Internal Server Error');
  } finally {
    sql.close();
  }
});

app.get('/api/select/:tableName', async (req, res) => {
  try {
    await sql.connect(config);

    const tableName = req.params.tableName;

    const { columns, conditions } = req.query;

    let query = `SELECT ${columns || '*'} FROM ${tableName}`;
    if (conditions) {
      query += ` WHERE ${conditions}`;
    }

    const result = await sql.query(query);
    res.send(result.recordset);
  } catch (err) {
    console.error(err);
    res.status(500).send('Internal Server Error');
  } finally {
    sql.close();
  }
});

app.put('/api/update/:tableName/:id', async (req, res) => {
  try {
    await sql.connect(config);

    const tableName = req.params.tableName;
    const id = req.params.id;
    const { columns, values } = req.body;

    if (!Array.isArray(columns) || !Array.isArray(values) || columns.length !== values.length) {
      return res.status(400).send('Invalid request body');
    }

    const setClause = generateSetClause(columns, values);

    const result = await sql.query(`UPDATE ${tableName} SET ${setClause} WHERE id = ${id}`);
    res.send(result);
  } catch (err) {
    console.error(err);
    res.status(500).send('Internal Server Error');
  } finally {
    sql.close();
  }
});

app.delete('/api/delete/:tableName/:id', async (req, res) => {
  try {
    await sql.connect(config);

    const tableName = req.params.tableName;
    const id = req.params.id;

    const result = await sql.query(`DELETE FROM ${tableName} WHERE id = ${id}`);
    res.send(result);
  } catch (err) {
    console.error(err);
    res.status(500).send('Internal Server Error');
  } finally {
    sql.close();
  }
});

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

API Endpoint Overview

Our REST API will consist of endpoints catering to the complete CRUD spectrum: Create, Read, Update, and Delete. Each endpoint will be thoroughly explained, detailing the purpose it serves, the expected input parameters, and the corresponding output. The Node.js code's role in orchestrating these operations will be elucidated to provide a holistic view.

1. Create (Insert) Endpoint

   • Endpoint: POST /api/create
   • Description: This endpoint is used to add new records to the SQL Server database. It expects a JSON payload containing the necessary data for creating a new entry.

2. Read (Select) Endpoint

   • Endpoint: GET /api/read/:tableName
   • Description: Retrieves data from the SQL Server database. The :tableName parameter allows dynamic selection of the table to read from. Optional query parameters like columns and conditions enable filtering and selecting specific columns.

3. Update Endpoint

   • Endpoint: PUT /api/update/:tableName/:id
   • Description: Updates an existing record in the SQL Server database. The :tableName parameter allows specifying the table to update, and :id is the unique identifier of the record to be modified. The request body should contain the columns and values to be updated.

4. Delete Endpoint

   • Endpoint: DELETE /api/delete/:tableName/:id
   • Description: Deletes a record from the SQL Server database. The :tableName parameter allows specifying the table to delete from, and :id is the unique identifier of the record to be removed.

These endpoints provide a complete set of CRUD operations, enabling users to interact with the SQL Server database through a RESTful API.

Postman: Executing CRUD Operations

To validate and test the functionality of our API, we'll employ Postman, a versatile API testing tool. A step-by-step guide will be provided on how to utilize Postman to make requests to our API endpoints. This hands-on section will demonstrate the seamless execution of CRUD operations, showcasing the API's capability to interact with the SQL Server database.

Create (Insert) Endpoint

In this example, we're using the Insert API to add new records to the SQL Server database. We send the necessary information in the request body.

Read (Select) Endpoint

Here, we're showcasing the Select API to get data from the SQL Server database. The details are included directly in the API request, making it easy to filter data and select specific columns.

Update Endpoint

This sample demonstrates the Update endpoint. We use it to change existing records in a table. The request body carries the columns and their new values for the update. The WHERE clause key in the request ensures we update the right record.

Before Running Update API

After Running Update API

Delete Endpoint

In this example, we're using the Delete API to remove a specific record from the 'Employee_records' table. We specify the record to delete by providing the ID (set to 5) in the request, following the endpoint structure.

Conclusion

In conclusion, this article has guided you through the comprehensive process of building a REST API in Node.js seamlessly integrated with an Azure SQL Server database. From the initial setup to practical testing using Postman, we've covered the key aspects of development. This knowledge equips you to customize the API according to your project requirements, offering a solid foundation for future endeavors in web development. Happy coding!