Tutorial: REST API using Free Azure SQL Managed Instance and Node.js Express Server

REST API for web app to sell concert tickets

7 min readJan 6, 2024

SQL Managed Instance due to it’s near 100% compatibility with SQL and full isolation is ideal for Healthcare Analytics, Business Intelligence (BI) and Reporting, Line-of-Business (LOB) Applications, Enterprise Resource Planning (ERP) Systems, Customer Relationship Management (CRM) Systems, E-commerce Platforms, Custom Applications with SQL Server Dependencies etc. The following is a fun, real world tutorial that I’ve needed many times for my applications.

Use case: Web Application for sales of concert tickets

This tutorial will be concerned with creating back end — REST API for handling sales of concert tickets. Due to the nature of the sales cycle with high spikes, we need a highly available and secure solution which is why I opted for SQL Managed Instance (surely a “little biased” since I’m a product manager working on SQL Managed Instance). Additionally, we want this project to be highly secure due to real value stored in the tickets.

Step 1: Create free SQL Managed Instance

To create a SQL Managed Instance, create an Azure account and sign up for a Pay-As-You-Go subscription. You can follow official docs or look at the summarized next steps for a super quick set up:

Search SQL Managed Instance
Click create in the top left
Select subscription and a resource group
When the create pop up modal displays, click Apply free offer

4. It will default everything for you so you don’t have to worry about public endpoints or any networking issues

5. Give your instance an admin login name and a password (we’re using SQL Login for this tutorial not Microsoft Entra)

6. Create instance

7. Once instance is created (about 30 minutes) go to databases tab in the side menu and create a database called — tickets-app

Now let’s define a schema for our database:

-- Create the Venues table to store information about concert venues
CREATE TABLE Venues (
    VenueID INT PRIMARY KEY,
    VenueName VARCHAR(255),
    Location VARCHAR(255)
);

-- Create the Concerts table to store information about concerts
CREATE TABLE Concerts (
    ConcertID INT PRIMARY KEY,
    Artist VARCHAR(255),
    VenueID INT,
    ConcertDate DATE,
    TicketPrice DECIMAL(10, 2),
    CONSTRAINT FK_Venue_Concert FOREIGN KEY (VenueID) REFERENCES Venues(VenueID)
);


-- Create the Customers table to store information about customers
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Email VARCHAR(255)
);

-- Create the Tickets table to store information about sold tickets
CREATE TABLE Tickets (
    TicketID INT PRIMARY KEY,
    ConcertID INT,
    CustomerID INT,
    PurchaseDate DATETIME,
    CONSTRAINT FK_Concert_Ticket FOREIGN KEY (ConcertID) REFERENCES Concerts(ConcertID),
    CONSTRAINT FK_Customer_Ticket FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE TicketValidations (
    ValidationID INT PRIMARY KEY,
    TicketID INT,
    ValidationDate DATETIME,
    CONSTRAINT FK_Ticket_Validation FOREIGN KEY (TicketID) REFERENCES Tickets(TicketID)
);

To actually create these tables for our SQL Managed Instance database, let’s open Azure Data Studio (or any other datastudio you might be using), connect and execute the SQL queries above. To connect Azure Data Studio to Azure SQL Managed Instance copy the entire public endpoint (yes including the ,3342) from the networking tab like below:

and paste to Azure Data Studio connection side menu into the field called Server (don’t forget to input your username and password):

If you have succesfully connected, click new query, paste the script from above and execute it.

Run the query below to check if the tables have been created:

SELECT table_name = t.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY t.name;

Now that we set up the database, onto the fun part!

Step 2: Create a Node.js Express Project

Create a new directory for your project and initialize it:

mkdir sql-managed-instance-example
cd sql-managed-instance-example
npm init -y

Let’s install express — Node.js package that let’s us easily set up a server, body-parser — that will allow us to read what’s on the body of our request and mssql-package that will let us connect to SQL Managed Instance

npm install express mssql body-parser

Step 3: Set Up Your Express Server

Create a file named app.js and paste the code below to set up a basic Express server:

const express = require('express');
const app = express();
const PORT = process.env.PORT || 3000;
app.get('/', (req, res) => {
  res.send('Hello, this is my Express server!');
});
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

After you’ve done this open your terminal/cmd and type

node app.js

In your browser localhost:3000 should open and you will see the message Server is running on port 3000

Step 4: Configure Database Connection

Add the necessary code to connect to your Azure SQL Managed Instance. Update the config object with your actual database connection details:

To get the value for the server go to Networking and copy the public endpoint connection string

const express = require('express');
const app = express();
const sql = require('mssql');
const PORT = process.env.PORT || 3000;

// TODO: Database configuration
const config = {
  user: 'your_username',
  password: 'your_password',
  // Notice public keyword in the connection string 
  // if you were to host this server on Azure you wouldn't need the public part
  server: 'free-sql-mi.public.daff4276.database.windows.net',
  database: 'ticket-app',
  options: {
    // THIS IS VERY IMPORTANT - Public endpoint is 3342, default is 1443
    port: 3342, 
    encrypt: true,
  },
};
// Connect to the database
sql.connect(config, (err) => {
  if (err) {
    console.error('Database connection failed:', err);
  } else {
    console.log('Connected to the database');
  }
});
app.get('/', (req, res) => {
  res.send('Hello, this is my Express server!');
});
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Replace 'your_username', 'your_password', 'your_server_name.public.database.windows.net', and 'your_database_name' with your actual database credentials.

Step 5: Examples of generic simple Queries

Extend your route to execute a simple query against your database:

// Route to insert data into the database
app.post('/insert', async (req, res) => {
  try {
    // Extract data from the request body
    const { columnName1, columnName2, columnName3 } = req.body;

    // Perform the database insert
    const result = await sql.query`INSERT INTO YourTable (Column1, Column2, Column3) VALUES (${columnName1}, ${columnName2}, ${columnName3})`;

    res.json({ message: 'Row added successfully!' });
  } catch (error) {
    console.error('Error inserting data:', error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

app.get('/list', async (req, res) => {
  try {
    const result = await sql.query`SELECT * FROM YourTable`;
    res.json(result.recordset);
  } catch (error) {
    console.error('Error executing query:', error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

Replace YourTable with an actual table name in your database.

Step 6: Create a ticket, retrieve a ticket

Let’s customize our routes to issue a ticket and retrieve it later:

// Route to insert data into the database
app.post('/createTicket', async (req, res) => {
    try {
        // Extract data from the request body
        const {
            TicketID,
            ConcertID,
            CustomerID,
            PurchaseDate
        } = req.body;

        // Perform the database insert
        const result = await sql.query`INSERT INTO Tickets (
            TicketID,
            ConcertID,
            CustomerID,
            PurchaseDate) VALUES (${TicketID}, ${ConcertID}, ${CustomerID}, ${PurchaseDate})`;

        res.json({message: 'Row added successfully!'});
    } catch (error) {
        console.error('Error inserting data:', error);
        res.status(500).json({error: 'Internal Server Error'});
    }
});

app.get('/getTicket', async (req, res) => {
    try {
        const result = await sql.query`SELECT * FROM Tickets Where TicketID = ${req.body.TicketID}`;
        res.json(result.recordset);
    } catch (error) {
        console.error('Error executing query:', error);
        res.status(500).json({error: 'Internal Server Error'});
    }
})

SQL Injection note — All values are automatically sanitized against sql injection. This is because it is rendered as prepared statement, and thus all limitations imposed in MS SQL on parameters apply (docs link)

Step 7: Run Your Express Server

Run your Express server:

node app.js

Visit http://localhost:3000 in your browser, and you should see the "Hello, this is my Express server!" message.

Step 8: Test the API

Since we added foreign key constraints we need to add some dummy data to customer table and concerts table

insert into [concerts] (ConcertID, Artist) VALUES (2, Beetles)
insert into [customers] (CustomerId, FirstName, LastName) VALUES (3, Uros, Randelovic)

Open Postman, create a POST request and select body, select option raw — it should look like the following:

🎉🎉🎉🎉

Row added successfully!