Query your Postgres database using Azure Functions

In this guide, you'll query a Postgres database hosted on Neon using Azure Functions. Neither side requires you to manage servers.

Prerequisites

You will need:

An Azure account with a subscription to deploy Azure Functions.
A Neon account. If you don’t have one yet, you can sign up.
Basic knowledge of Node.js and SQL.
Familiarity with using Visual Studio Code.

Why Neon?

Neon is a complete set of cloud backend primitives built around Lakebase Postgres, for developers, startups, and agent platforms, from Databricks. Lakebase Postgres separates compute from storage, so compute autoscales with your application's load and scales to zero when idle, without you managing the infrastructure.

Azure Functions is a serverless compute service that runs event-driven code, such as handlers for HTTP requests, without you deploying or managing servers. It works well for microservices and APIs, and pairs naturally with a database that also scales on demand.

To illustrate this, you'll build a hotel reservation management example that queries and processes data with both services.

Context

Imagine you're building an application to manage hotel reservations. You want to allow users (via an app or website) to view available reservations and interact with a Postgres database hosted on Neon.

The application's features will include:

View available rooms: The application will allow users to check available hotel rooms for booking.
Add a new reservation: When a customer makes a reservation, their information is stored in the Neon database.
Cancel a reservation: Customers can cancel a reservation by deleting the corresponding record from the database.

Step 1: Create and configure the database on Neon

Sign up and create the database

After creating the database, make sure to copy the connection details (such as host, user, password, database) somewhere safe, as they will be used to configure Azure Functions to connect to Neon.

Creating the tables

Once the database is created, you should see an option named "SQL Editor" on the left to write and execute queries.

In the query editor, copy and paste the SQL code below to create the clients and hotels tables. These are reference tables, as the reservations table will refer to these tables via foreign keys:

CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100),
    phone_number VARCHAR(20)
);

CREATE TABLE hotels (
    hotel_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100)
);

Here is the SQL script to create the reservations table:

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL,
    hotel_id INT NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    number_of_guests INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(client_id),
    FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id)
);

Inserting test data

Insert some example data into the database to check that everything works so far.

Here is the SQL script to insert data into the clients table:

INSERT INTO clients (first_name, last_name, email, phone_number) VALUES
('Alice', 'Dupont', 'alice.dupont@example.com', '0123456789'),
('Bob', 'Martin', 'bob.martin@example.com', '0987654321'),
('Chloé', 'Lefevre', 'chloe.lefevre@example.com', '0147253689');

Here is the SQL script to insert data into the hotels table:

INSERT INTO hotels (name, location) VALUES
('Hôtel Le Paris', 'Paris'),
('Hôtel des Alpes', 'Annecy'),
('Hôtel de la Plage', 'Nice');

Here is the SQL script to insert data into the reservations table:

INSERT INTO reservations (client_id, hotel_id, check_in_date, check_out_date, number_of_guests) VALUES
(1, 1, '2024-11-01', '2024-11-05', 2),
(2, 2, '2024-11-10', '2024-11-15', 1),
(3, 3, '2024-12-01', '2024-12-10', 4);

Step 2: Create an Azure Function to manage reservations

Sign in to Azure

If you don't already have an account, sign up on the Microsoft Azure portal.

We will initialize an Azure Functions project where we will create an HTTP Trigger function in Visual Studio Code (VS Code) using the Azure Functions extension.
Install the Azure Functions extension:
- Open VS Code, or install Visual Studio Code if it's not yet installed.
- Go to the extensions tab or press Ctrl+Shift+X.
- Search for "Azure Functions" and install the official extension.
Create an Azure Functions project

Open the command palette or press Ctrl+Shift+P to open the command palette.
- Type Azure Functions: Create New Project... and select that option.
- Choose a directory where you want to create the project.
- Select the programming language (JavaScript in our case).
- Choose a JavaScript programming model (Model V4).
- Choose a function template, and select HTTP trigger.
- Give your function a name, for example, manageClients.
Once confirmed, the project will be created with some default code.
Install the Postgres client

In the terminal of your Azure Functions project, install either Neon serverless driver or the node-postgres (pg) package, which will be used to connect to Postgres:
```
npm install @neondatabase/serverless
```

Azure Functions Core Tools

Install Azure Functions Core Tools to run functions locally.

npm install -g azure-functions-core-tools@4 --unsafe-perm true

Suggested folder structure

Since there are three tables in the database (Clients, Hotels, and Reservations), use a separate file for each feature or interaction with the database to keep the code organized.

src/
  ├──index.js
  ├──functions/
  │   ├──manageClients.js
  │   ├──manageHotels.js
  │   └──manageReservations.js
  └──database/
      ├──client.js
      ├──hotel.js
      └──reservation.js

Configure environment variables

In the Neon Console, click Connect in the Console nav to open the Connect to your branch modal and copy the database connection string. Make sure the copied string includes the password; if the password is masked, reveal it before copying.

Create a .env file at the root of the project to store your Neon connection string.

Here's an example of the connection string you'll copy:
```
DATABASE_URL='postgresql://neondb_owner:************@ep-quiet-leaf-a85k5wbg.us-east-2.aws.neon.tech/neondb?sslmode=require&channel_binding=require'
```
Modify the local.settings.json file

The local.settings.json file is used by Azure Functions for local executions. Azure Functions does not directly read the .env file. Instead, it relies on local.settings.json to inject environment variable values during local execution. In production, you will define the same settings through App Settings in the Azure portal.

Here's an example of the local.settings.json file:
```
{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node",
    "DATABASE_URL": "postgresql://neondb_owner:************@ep-quiet-leaf-a85k5wbg.us-east-2.aws.neon.tech/neondb?sslmode=require&channel_binding=require"
  }
}
```
Install the dotenv package by opening the terminal in your Azure Functions project. This package will allow you to load environment variables from the .env file:
```
npm install dotenv
```

Manage each table

a. Create a separate file for each table in the database/ folder.

Here, you can use either the neon package or the pg package to connect to the database.

Example code for client.js

import { neon } from '@neondatabase/serverless';
import dotenv from 'dotenv';

dotenv.config();

const sql = neon(process.env.DATABASE_URL);

const getAllClients = async () => {
  const rows = await sql`SELECT * FROM clients`;
  return rows;
};

const addClient = async (first_name, last_name, email, phone_number) => {
  const [newClient] = await sql`
    INSERT INTO clients (first_name, last_name, email, phone_number)
    VALUES (${first_name}, ${last_name}, ${email}, ${phone_number})
    RETURNING *`;
  return newClient;
};

export { getAllClients, addClient };

Example code for hotel.js

import { neon } from '@neondatabase/serverless';
import dotenv from 'dotenv';

dotenv.config();

const sql = neon(process.env.DATABASE_URL);

const getAllHotels = async () => {
  const rows = await sql`SELECT * FROM hotels`;
  return rows;
};

export { getAllHotels };

Example code for reservation.js

import { neon } from '@neondatabase/serverless';
import dotenv from 'dotenv';

dotenv.config();

const sql = neon(process.env.DATABASE_URL);

const getAvailableReservations = async () => {
  const rows = await sql`
        SELECT * FROM reservations WHERE status = ${'available'}
    `;
  return rows;
};

export { getAvailableReservations };

b. Modify the functions/ folder by adding the function files:

In the functions/ folder, remove the default file, and then add three function management files (manageClients.js, manageHotels.js, and manageReservations.js).

Example for manageClients.js

// src/functions/manageClients.js
const { app } = require('@azure/functions');
const { getAllClients, addClient } = require('../database/client');

app.http('manageClients', {
  methods: ['GET', 'POST'],
  authLevel: 'anonymous',
  handler: async (request, context) => {
    context.log(`HTTP function processed request for url "${request.url}"`);

    if (request.method === 'GET') {
      try {
        const clients = await getAllClients();
        console.table(clients);
        return {
          body: clients,
        };
      } catch (error) {
        context.log('Error fetching clients:', error);
        return {
          status: 500,
          body: 'Error retrieving clients.',
        };
      }
    }

    if (request.method === 'POST') {
      try {
        const { first_name, last_name, email, phone_number } = await request.json();

        if (!first_name || !last_name || !email || !phone_number) {
          return {
            status: 400,
            body: 'Missing required fields: first_name, last_name, email, phone_number.',
          };
        }

        const newClient = await addClient(first_name, last_name, email, phone_number);
        console.table(newClient);
        return {
          status: 201,
          body: newClient,
        };
      } catch (error) {
        context.log('Error adding client:', error);
        return {
          status: 500,
          body: 'Error adding client.',
        };
      }
    }
  },
});

Example for manageHotels.js

// src/functions/manageHotels.js
const { app } = require('@azure/functions');
const { getAllHotels } = require('../database/hotel');

app.http('manageHotels', {
  methods: ['GET'],
  authLevel: 'anonymous',
  handler: async (request, context) => {
    context.log(`HTTP function processed request for url "${request.url}"`);

    try {
      const hotels = await getAllHotels();
      return {
        body: hotels,
      };
    } catch (error) {
      context.log('Error fetching hotels:', error);
      return {
        status: 500,
        body: 'Error retrieving hotels.',
      };
    }
  },
});

Example for manageReservations.js

// src/functions/manageReservations.js
const { app } = require('@azure/functions');
const { getAvailableReservations } = require('../database/reservation');

app.http('manageReservations', {
  methods: ['GET'],
  authLevel: 'anonymous',
  handler: async (request, context) => {
    context.log(`HTTP function processed request for url "${request.url}"`);

    try {
      const reservations = await getAvailableReservations();
      return {
        body: reservations,
      };
    } catch (error) {
      context.log('Error fetching reservations:', error);
      return {
        status: 500,
        body: 'Error retrieving available reservations.',
      };
    }
  },
});

You can extend this structure with features such as adding new clients, creating new reservations, or even updating and deleting data, each with its own file and its own logic.

Step 3: Test the function locally

Run the function locally:
- Open the integrated terminal in VS Code.
- Run the following command npm run start, which will execute func start to start the project and launch the functions:
```
npm run start
```
Test with a browser or Postman:
- Open a browser and navigate to http://localhost:7071/api/manageClients to test your function.
- You can also use a tool like Postman to send HTTP requests.

Step 4: Deploy and test the function on Azure

Deploy your function:
- Open the command palette with Ctrl+Shift+P and type Azure Functions: Deploy to Function App....
- Follow the instructions to select your Azure subscription and choose or create a Function App, then complete the deployment process.
Test the function:

Use a tool like Postman to send an HTTP request to the Azure Function, for example:
```
https://your-azure-function-url?client_id=1234
```
This will return the information of the client with the ID 1234, if present in the database.

Conclusion

You built Azure Functions that read and write hotel reservation data in a Postgres database on Neon, tested them locally, and deployed them to Azure. As a next step, add functions for creating and updating reservations, each in its own file.

Additional resources

Neon documentation: guides, tutorials, and API references for Neon.
Azure Functions documentation

Need help?

Join our Discord Server to ask questions or see what others are doing with Neon. For paid plan support options, see Support.