Creating APIs with NodeJS and Microsoft SQL Server , Beginners Guide

Introduction

creating robust APIs is crucial for ensuring that your applications can communicate effectively with databases and other services. NodeJS, with its non-blocking, event-driven architecture, is an excellent choice for building APIs.

When paired with Microsoft SQL Server, a powerful relational database, it provides a solid foundation for scalable, enterprise-grade applications. In this guide, we’ll walk you through creating APIs with NodeJS and Microsoft SQL Server, deploying them on AWS Lightsail, and ensuring they stay live on both Linux and Windows servers.

Prerequisites

Before we dive into the details, make sure you have the following prerequisites:

• AWS Account: To set up Lightsail instances.

• NodeJS Installed: Ensure you have NodeJS (version 14 or higher) installed on your machine.

• Microsoft SQL Server: A running instance of SQL Server (can be on AWS, local, or Azure).

• Basic Knowledge of SQL: Familiarity with SQL queries and database operations.

• Git: Version control system for managing your code.

• SSH Access: For deploying your application on AWS Lightsail.

Setting Up Your Environment

1. Creating a Lightsail Instance

• Log in to your AWS account and navigate to Lightsail.

• Create a new Lightsail instance. Choose either Linux/Unix or Windows as the platform.

• Select the appropriate instance plan (depending on your expected traffic).

• Launch the instance and make a note of the public IP address.

2. Installing NodeJS on the Server

For Linux:

# Update package list
sudo apt-get update

# Install NodeJS
sudo apt-get install -y nodejs

# Install npm
sudo apt-get install -y npm

# Verify installation
node -v
npm -v

For Windows:

• Download and install NodeJS from the official website.

• Ensure NodeJS and npm are accessible from the command line by adding them to your system PATH.

Creating Your API with NodeJS and SQL Server

1. Setting Up a New NodeJS Project

Start by creating a new directory for your project and initializing it with npm:

mkdir my-node-api
cd my-node-api
npm init -y

2. Installing Required Packages

You’ll need a few npm packages to connect NodeJS with SQL Server:

npm install express mssql dotenv

• Express: A web framework for NodeJS.

• mssql: A Microsoft SQL Server client for NodeJS.

• dotenv: For managing environment variables.

3. Creating Your API

Create a new file index.js and set up a basic Express server:

const express = require('express');
const sql = require('mssql');
require('dotenv').config();

const app = express();
app.use(express.json());

const config = {
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    server: process.env.DB_SERVER, 
    database: process.env.DB_NAME,
    options: {
        encrypt: true, // for Azure
        trustServerCertificate: true // for local dev/testing
    }
};

// Connect to the database
sql.connect(config, err => {
    if (err) console.log(err);

    app.get('/api/users', async (req, res) => {
        try {
            const result = await sql.query`SELECT * FROM Users`;
            res.json(result.recordset);
        } catch (err) {
            res.status(500).send(err.message);
        }
    });

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

4. Environment Variables

Create a .env file to store your database credentials:

DB_USER=your_db_username
DB_PASS=your_db_password
DB_SERVER=your_db_server
DB_NAME=your_db_name

Deploying Your API on AWS Lightsail

1. Copying Files to Lightsail

Use scp (for Linux/Unix) or a tool like WinSCP (for Windows) to copy your project files to your Lightsail instance.

For Linux:

scp -r /path/to/your/project username@your_instance_ip:/home/username/

For Windows:

• Use WinSCP to transfer files to your server.

2. Installing Dependencies on the Server

Once the files are copied, SSH into your instance and navigate to your project directory:

ssh username@your_instance_ip
cd /home/username/my-node-api
npm install

3. Running Your API

To run your API on Linux:

node index.js

On Windows, simply execute the same command in the command prompt or PowerShell.

Keeping Your API Live

1. Using PM2

PM2 is a popular process manager that ensures your NodeJS application stays live, even after server reboots.

Install PM2:

sudo npm install -g pm2

Start Your Application with PM2:

pm2 start index.js
pm2 save
pm2 startup

This will keep your application running in the background. The pm2 startup command ensures it restarts on server boot.

Deployment on Windows

For Windows, you can use a task scheduler or services like NSSM (Non-Sucking Service Manager) to run your NodeJS application as a service.

FAQ

1. How do I connect to a remote SQL Server?

Ensure that the SQL Server is configured to allow remote connections and that you have the necessary firewall rules in place.

2. Can I use other databases with NodeJS?

Yes, NodeJS supports a wide range of databases including MySQL, MongoDB, and PostgreSQL.

3. What if my API stops working?

Use process managers like PM2 to monitor your API. PM2 also offers logs to help diagnose issues.

Creating APIs with NodeJS and Microsoft SQL Server is a powerful way to build scalable applications. Whether you're deploying on a Linux or Windows server, AWS Lightsail offers a reliable and affordable platform. With the use of PM2, you can ensure your application stays live, providing continuous service to your users.

Disclaimer

The steps mentioned here assume a basic understanding of AWS Lightsail, NodeJS, and SQL Server. Please ensure that you follow security best practices, especially when dealing with sensitive data.