Create Node js API with SQL server

In this tutorial, we will learn how to create a Node.js API with SQL server. We will use SQL queries and stored procedures to interact with the database.

Requirements

Before we begin, make sure you have Node.js and SQL server installed on your machine.

Setting up the project

First, let’s create a new folder for our project and navigate into it.


mkdir nodejs-sql-api
cd nodejs-sql-api


Now, let’s initialize a new Node.js project by running the following command:


npm init -y


Install required packages

We will need to install the ‘express’ and ‘mssql’ packages to create our API and interact with the SQL server. Run the following command to install these packages:


npm install express mssql


Create the API

Create a new file named ‘app.js’ and add the following code to create a simple API using Node.js and Express:

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

app.get('/api/users', async (req, res) => {
  // Code for getting users from SQL server using SQL query or stored procedure
});

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

Use SQL Query or Stored Procedure

Now that we have our basic API set up, we can use SQL queries or stored procedures to interact with the SQL server. We can use the ‘mssql’ package to connect to the database and execute queries or stored procedures.

Here’s an example of how to use a SQL query to fetch users from the database:

const sql = require('mssql');

const config = {
  server: 'YOUR_SERVER',
  database: 'YOUR_DATABASE',
  user: 'YOUR_USER',
  password: 'YOUR_PASSWORD',
  options: {
    trustedConnection: true
  }
};

app.get('/api/users', async (req, res) => {
  try {
    let pool = await sql.connect(config);
    let result = await pool.request().query('SELECT * FROM Users');
    res.json(result.recordsets[0]);
  } catch (error) {
    res.status(500).send(error.message);
  }
});
  

And here’s an example of how to use a stored procedure to fetch users from the database:

app.get('/api/users', async (req, res) => {
  try {
    let pool = await sql.connect(config);
    let result = await pool.request()
      .execute('GetUsers');
    res.json(result.recordsets[0]);
  } catch (error) {
    res.status(500).send(error.message);
  }
});
  

Conclusion

In this tutorial, we learned how to create a Node.js API with SQL server. We used SQL queries and stored procedures to interact with the database. Now you can create your own APIs and perform CRUD operations on your SQL server using Node.js.