17. The Application Bridge (API Gateway)

The Three-Tier Architecture

Modern web applications are built in three distinct layers, each with a specific responsibility.

The Database Tier is SQL Server — the engine storing all the data. It responds only to authenticated requests from the application layer. Users never connect to it directly.

The Application Tier is the backend — a program running on a server, written in a language like Node.js, Python, or Java. It receives requests from the frontend, translates them into database queries, retrieves the results, and formats them for delivery. This is the layer where your SQL knowledge and your programming knowledge meet.

The Presentation Tier is the frontend — what the user sees in their browser or mobile app, built with HTML, CSS, and JavaScript. It sends requests to the application tier and renders the responses it receives.

Data flows in one direction only: the frontend asks the application, the application asks the database, the database answers the application, the application answers the frontend. The database is never exposed directly to the internet.

What an API Is

The application tier communicates with the frontend through an API — Application Programming Interface. For web applications, this is typically a REST API: a collection of URL endpoints that respond to HTTP requests with JSON data.

When you use a university portal and click View My Results, your browser sends an HTTP GET request to an API endpoint like /api/results?studentId=12345. The backend receives this, runs a database query for that student's grades, and responds with a JSON object containing the grade data. Your browser then renders that JSON as a readable table on screen.

Every web application you use — social media, banking, e-commerce — is built on exactly this pattern.

🔌 The Connection String (SQL Server / mssql)

To connect Node.js to SQL Server, install the official Microsoft driver and configure it with your server details:

mkdir university-api
cd university-api
npm init -y
npm install express mssql

// dbConfig.js
const dbConfig = {
  server: 'localhost',
  port: 1433,
  database: 'VoidX_Academy',
  user: 'WebAppUser',
  password: process.env.DB_PASSWORD,
  options: {
    encrypt: true,
    trustServerCertificate: true
  }
};

module.exports = dbConfig;

port: 1433 is the default SQL Server port. trustServerCertificate: true is required for local developer environments. Always store passwords in environment variables — never hardcode them in source files.

🏊 Connection Pooling: The Performance Secret

Opening a database connection requires a TCP handshake, SSL negotiation, and password verification. If 1,000 users click Refresh simultaneously, opening 1,000 brand-new connections will crash the server from Port Exhaustion.

Connection Pooling solves this. Your backend opens a pool of connections when it boots up and leaves them permanently open. When a user needs data, the backend grabs a warm connection, runs the query, and throws the connection back into the pool for the next user.

🌉 Building the API Route (Node.js & mssql)

Here is a complete Express API route that connects to SQL Server and returns the top students as JSON:

const express = require('express');
const sql = require('mssql');
const dbConfig = require('./dbConfig');
const app = express();

// Initialize the global Connection Pool
const poolPromise = sql.connect(dbConfig);

// GET all students ordered by GPA
app.get('/api/students', async (req, res) => {
  try {
    const pool = await poolPromise;
    const result = await pool.request().query(
      'SELECT StudentID, FirstName, LastName, GPA FROM Student ORDER BY GPA DESC'
    );
    res.json(result.recordset);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Database error' });
  }
});

sql.connect(dbConfig) establishes the global connection pool. pool.request().query(...) sends your T-SQL to the SQL Server engine. result.recordset is an array of JavaScript objects — one per row. res.json() converts that array to JSON and sends it as the HTTP response.

🛡️ Accepting URL Parameters & Preventing SQL Injection

When accepting input from a user, you must never concatenate strings into your SQL. Use the .input() method to bind parameters securely:

app.get('/api/students/:id', async (req, res) => {
  try {
    const pool = await poolPromise;
    const result = await pool.request()
      .input('InputID', sql.Int, req.params.id)
      .query('SELECT * FROM Student WHERE StudentID = @InputID');

    if (result.recordset.length === 0) {
      return res.status(404).json({ error: 'Student not found' });
    }
    res.json(result.recordset[0]);
  } catch (error) {
    res.status(500).json({ error: 'Database error' });
  }
});

.input('InputID', sql.Int, req.params.id) tells the mssql driver exactly what type the parameter is and what value to use. The driver sends the SQL and the parameter separately to SQL Server, which treats the parameter as pure data — it can never be interpreted as SQL code, completely neutralizing injection attacks.

Starting the Server

Add these lines at the bottom of server.js to start the server:

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`API running on port ${PORT}`));

Run it from your terminal:

node server.js

Open your browser and navigate to http://localhost:3000/api/students. If your database connection is configured, you will see a JSON array of student records in the browser — exactly what a React or mobile app frontend receives and renders as a user interface.

How to Use the Web App Capstone Lab

The lab gives you a Node.js code editor on the left and a simulated frontend client on the right. The client is waiting for data from a GET /api/leaderboard endpoint. Your task is to complete the API route by writing the database query and the JSON response using the mssql driver.

app.get('/api/leaderboard', async (req, res) => {
  try {
    const pool = await poolPromise;
    const result = await pool.request().query(
      'SELECT TOP 3 Callsign, XP_Score FROM Operators WHERE IsActive = 1 ORDER BY XP_Score DESC'
    );
    res.status(200).json(result.recordset);
  } catch (error) {
    res.status(500).json({ error: 'Database breach detected' });
  }
});

Your code must use pool.request().query() to execute the query. The SQL must SELECT from the Operators table and include TOP 3 to return only the top three. It must use res.json() to send the response. When all requirements pass, the simulated frontend on the right comes alive — completing the full journey from database to screen.