Khuzaima Khalid

Posted on Aug 9

Episode 3: Setting Up a Managed PostgreSQL Database on Azure & Connecting Your Express API

#webdev #javascript #postgres #devops

In Episode 1, we hosted an Express API on Azure App Service, and in Episode 2, we deployed a React frontend to Azure Static Web Apps. Now, we will connect our full-stack application to a managed, production-ready relational database: Azure Database for PostgreSQL Flexible Server.

In this guide, we will provision a PostgreSQL instance, configure its firewall rules, secure connection credentials using environment variables, and query the database directly from our Express backend.

Prerequisites
Before getting started, make sure you have:

The Express project from Episode 1 running locally and on Azure.

PostgreSQL client tool or extension installed (such as pgAdmin, DBeaver, or the PostgreSQL extension in VS Code).

An active Azure Account (or Azure for Students).

Visual Studio Code installed.

Step 1: Create an Azure Database for PostgreSQL Flexible Server

Search for Azure Database for PostgreSQL flexible servers in the top search bar and click Create.

Configure the Basics tab:

Subscription: Select your subscription.

Resource Group: Choose the same resource group used in Episode 1 (e.g., rg-express-azure).

Server name: Choose a unique server name (e.g., pg-express-db-server).

Region: Select the same region as your App Service.

Workload type: Select Development or Hobby to stay cost-effective.

Authentication method: Select PostgreSQL authentication only.

Admin username: Set an admin username (e.g., pgadmin).

Password: Enter a strong password and save it securely.

Move to the Networking tab:

Connectivity method: Select Public access (allowed IP addresses).

Check Allow public access from any Azure service within Azure to this server (this allows your Azure App Service to connect).

Click + Add current client IP address so you can connect locally from your development machine.

Click Review + create, then click Create. Wait 2–3 minutes for deployment to complete.

Step 2: Install PostgreSQL Client in Express

Open your local Express project in VS Code and install the official PostgreSQL node driver (pg) along with dotenv to manage secrets locally:

npm install pg dotenv

Step 3: Configure Local Environment Variables

Create a .env file in the root of your Express project (ensure .env is added to your .gitignore file):

PORT=5000
DB_HOST=pg-express-db-server.postgres.database.azure.com
DB_USER=pgadmin
DB_PASSWORD=YourStrongPassword123!
DB_NAME=postgres
DB_PORT=5432

Step 4: Create Database Connection Module

Create a file named db.js in your Express project root:

const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT || 5432,
  ssl: {
    rejectUnauthorized: false // Required for Azure PostgreSQL SSL connections
  }
});

pool.on('connect', () => {
  console.log('Connected to Azure PostgreSQL database successfully.');
});

module.exports = pool;

Step 5: Update Your Express API Routes

Update your index.js or server.js file to verify the connection and query data:

const express = require('express');
const cors = require('cors');
const pool = require('./db');
require('dotenv').config();

const app = express();
const PORT = process.env.PORT || 5000;

app.use(cors());
app.use(express.json());

// Root endpoint testing DB connection time
app.get('/', async (req, res) => {
  try {
    const result = await pool.query('SELECT NOW()');
    res.json({
      message: 'Express API is running and connected to Azure PostgreSQL!',
      serverTime: result.rows[0].now
    });
  } catch (err) {
    console.error('Database connection error:', err.message);
    res.status(500).json({ error: 'Database connection failed' });
  }
});

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

Test it locally by running node index.js and visiting http://localhost:5000.

Step 6: Configure Environment Variables in Azure App Service

Never commit passwords or database host credentials to GitHub. To pass credentials securely to your live Azure deployment:

Go to the Azure Portal and open your App Service resource from Episode 1.

In the left menu, under Settings, select Environment variables (or Configuration).

Under App settings, click + Add to add each variable:

DB_HOST = pg-express-db-server.postgres.database.azure.com

DB_USER = pgadmin

DB_PASSWORD = YourStrongPassword123!

DB_NAME = postgres

DB_PORT = 5432

Click Apply at the bottom, then click Confirm. Your App Service will restart automatically with the new environment variables active.

Step 7: Push Changes and Verify Deployment

Push your updated code to GitHub:

git add .
git commit -m "Add PostgreSQL connection logic"
git push origin main

Once your CI/CD pipeline or deployment finishes, visit your live Azure App Service URL in the browser (https://.azurewebsites.net/). You should see the success response along with the real-time timestamp generated directly by your Azure PostgreSQL database!

Common Issues & Troubleshooting

error: no pg_hba.conf entry for host...: Azure PostgreSQL requires SSL connections by default. Ensure ssl: { rejectUnauthorized: false } is included in your pg Pool configuration.

Connection Timeout / ECONNREFUSED:

Check the Networking firewall rules on your PostgreSQL server in Azure. Make sure "Allow public access from any Azure service within Azure" is checked.