Query Postgres from Netlify Functions with Neon serverless driver

Neon is a serverless Postgres that makes database development and management easy and efficient. Offering modern features like autoscaling, auto-suspending, and branching, Neon separates compute and storage and scales automatically up or down based on demand, so developers can focus on building their applications without the hassle of managing servers or databases.

Imagine a game company running a massively multiplayer online (MMO) game service that experiences fluctuating user numbers throughout the day. By continuously monitoring the number of online players, the serverless driver can automatically scale the database up or down to meet the changing demand. In peak hours, this ensures that the database can accommodate the growing number of concurrent queries and maintain optimal performance, providing a smooth and lag-free gaming experience for all players.

Conversely, during off-peak hours, when the player counts drop, the serverless driver efficiently recognizes the reduced demand and initiates scaling down the database. This involves releasing unused database resources, such as CPU and memory, to optimize resource utilization and reduce costs. This ensures that companies only pay for used resources, contributing to cost savings and overall efficiency.

In a broader context, the Neon serverless driver is crafted explicitly for data querying in serverless and edge environments, using HTTP or WebSockets rather than conventional TCP. This design choice streamlines operations by removing the requirement for extra infrastructure configuration or worries about TCP connections, saving you time and money. Furthermore, you’ll have the flexibility to leverage Neon's PostgreSQL database service in diverse environments, encompassing Cloudflare Workers, Vercel Edge Functions, and Netlify Functions.

This article will explore how to query Postgres from Netlify Functions using the Neon serverless driver.

GitHub

Check out the complete source code here.

Prerequisites

To best follow along with this demo, you’ll need a few things:

• Installation of Node.js
• Basic knowledge of SQL
• Basic understanding of JavaScript
• A neon account. Sign up here
• Creating a free Netlify account

Benefits of using Neon serverless driver

• Low latency: The Neon serverless driver is optimized for low latency, making it ideal for use in serverless and edge environments.
• Easy to use: It is also a drop-in replacement for the popular node-postgres driver, so starting is straightforward.
• Scalable: The Neon serverless driver is scalable and can handle the most diverse workload demands.

What are Netlify Functions?

Netlify Functions are serverless functions executed in response to specific events, such as HTTP requests, when users visit a particular URL. Netlify Functions acts as a bridge between the frontend and backend, allowing developers to write backend code that can be integrated with the frontend code and deployed as part of the same project.

With the introduction of the Neon serverless driver, developers can also query the Neon serverless Postgres database directly within Netlify Functions to further enhance the versatility and efficiency of serverless architecture.

Connecting to Neon Postgres

If you’re a first-time Neon user, go ahead and create a Neon project by signing up; then, we can create a project. Neon provides a simplified way of connecting to a created Postgres database using psql and a connection string. To obtain a connection string, we will navigate to the Neon dashboard, and within the connection details, we will select the database we want to connect to and the type of connection. Neon then creates the connection string as seen below:

We will take note of the connection string as we will use it to connect and query data from the Neon DB later in the article.

Creating tables on Neon

Now that we have our dashboard created, we can create a table. First, navigate to the dashboard of interest and access the SQL editor.

In our case, we will create a table named “POSTS” and add the data below:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL
);

INSERT INTO posts (title, content) 
  VALUES ('Query Postgres from Netlify Functions with Neon serverless driver', 'This post goes over querying ...');

INSERT INTO posts (title, content) 
  VALUES ('5 reasons why you should start using Neon', 'This post talks about the features Neon provides.');

In the SQL code above, we created a table named “posts” with columns for an auto-incrementing primary key (id), a title (title), and content (content). Then, we inserted two rows of data into the table, each representing a post with a title and content.

At this point, our SQL editor should look like the image below:

To add this table to the dashboard, click “Run”.

Now, if we navigate to "Tables,” you’ll see the table “posts” with the two rows of data we inserted.

We’re ready to query this data now that we have our table.

Creating our application

For this tutorial, we’ll create a new application to query Postgres and use Netlify functions and Neon serverless driver. Start by running the commands below:

mkdir <folder-name> //creates a new folder
cd <folder-name> //navigate into the created folder

Next, we’ll initialize a new Node.js project by running the command below:

npm init -y

This allows us to install and manage dependencies for our project easily.

We will also set up the dotenv package to manage environment variables locally and after Netlify deployment. To do this, we will install dotenv using the command below:

npm i dotenv

Installing the Neon serverless driver package

To use the Neon serverless driver within our application, install it using the command below:

npm install @neondatabase/serverless

Creating a Netlify Function

Netlify Functions are essentially serverless functions that can be deployed and executed on Netlify's serverless platform. To create a Netlify Function, start by creating a directory for these functions at the root of your project. Conventionally, this directory is named functions.

We will now create a .mjs file for our function inside the functions directory. This file should export a handler function, and within this file, we will connect the Neon serverless driver and query Postgres.

We will start by importing the Neon serverless driver.

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

Then, we’ll import dotenv to use in our functions by adding the code below:

require("dotenv").config();

Next, we will create a new Neon client:

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

Now, we can write our first SQL queries:

const result = await sql("SELECT * FROM posts WHERE id = $1", [postId]);   

We will get a result that we can then return from the Netlify function:

return {
  statusCode: 200,
  body: JSON.stringify(post),
};

Bringing it all together, the Netlify function will look like this:

import { neon } from "@neondatabase/serverless";
require("dotenv").config();

const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) {
  throw new Error("DATABASE_URL is not defined");
}
const sql = neon(databaseUrl);

exports.handler = async function (event, context) {
  try {
    const postId = parseInt(event.queryStringParameters.postId, 10);
    const result = await sql("SELECT * FROM posts WHERE id = $1", [postId]);
    if (!result || result.length === 0) {
      return {
        statusCode: 404,
        body: JSON.stringify({ error: "Post not found" }),
      };
    }
    const post = result[0];
    return {
      statusCode: 200,
      body: JSON.stringify(post),
    };
  } catch (error) {
    console.error("Error:", error);
    return {
      statusCode: 500,
      body: JSON.stringify({ error: "Internal Server Error" }),
    };
  }
};

Here’s what’s going on in the previous code block:

• We imported the neon function, which we used to establish a connection to the Neon database using the DATABASE_URL.
• We also imported the config function from the dotenv library to handle environment variables.
• We are reading the value of the DATABASE_URL from the environment variable set in the Netlify project dashboard, and we set it to throw an error if the variable is not defined.
• We used the neon function to establish a connection to the database using the retrieved URL.
• We also executed an SQL query to retrieve posts from the database based on the postId
• If the post variable is null, the code will return a 404 error message. Otherwise, the code will return a 200 OK response and the post data.
• The code returns a 500 status code in the case of an internal server error.

Configuring netlify.toml:

We will create a netlify.toml file at the root of our project and specify the functions directory, like so:

[build]
  functions = "functions"

This tells Netlify where to find our functions.

Testing the Netlify Function

Testing locally
After creating the Netlify function, we can test our integration locally by ensuring we have Netlify’s latest CLI version. Do this by running the command below:

npm install netlify-cli -g 

Configuring the .env file
We will also create a .env file in our project’s root folder and add our connection string:

DATABASE_URL=postgresql://[user]:[password]@[neon_hostname]/[dbname]

Doing this allows our function to access the process.env.DATABASE_URL, which is necessary to connect our function to Neon DB.

We can then start Netlify’s development environment using the command below:

netlify dev

At this point, we will see a message like the one below in our terminal:

Server now ready on http://localhost:8888  

We can test the success of our implementation by opening up Postman or downloading the latest version here. Afterwards, follow the steps below:

1. Create a GET request.
2. Then, set the URL to the “localhost’s URL/.netlify//”. In our case, we will set the URL to this: http://localhost:8888/.netlify/functions/function.
3. Add a query parameter named postId with the value 1.
4. Click “Send”.

Now, we should have our Postman looking like the image below, with the result showing the query of the data with the postId of 1 in our Neon DB.

Testing after deployment
Usually, we will not always use our applications locally, and there will be cause to deploy our applications beyond our local machines. To do this, we can use Netlify.

As a new user, we can sign up with Netlify here. Then, we can select the project we would like to work with; under the project’s “site configuration,” we can select the “environment variables” option.

Using the connection string from the Neon dashboard, we can set the project’s environment variable to the DATABASE_URL, like so:

DATABASE_URL=postgresql://[user]:[password]@[neon_hostname]/[dbname]

By storing the DATABASE_URL as an environment variable in Netlify, we can protect the connection string from unauthorized access. It also allows the Netlify function in our code to access the Neon connection string to query the Neon database.

After creating the environment variable on Netlify, we can deploy our application. To complete the final stage of our testing, navigate to the “site configuration” tab and copy the site’s name.

Let’s navigate back to our Postman and query the data using the site's names. Then, follow these steps:

1. Create a new GET request.
2. Then, we will set the URL to the “/.netlify//”. In our case, we will set the URL to this: https://neon-serverless-driver.netlify.app/.netlify/functions/function.
3. Add a query parameter named postId with the value 2 to get the second item in the Neon DB.
4. Click “Send”.

At this point, we should see the second row of data from the posts table showing in our Postman:

We have successfully queried Postgres from Netlify Functions using the Neon serverless driver.

Conclusion

This article explained how to query Postgres from Netlify Functions using the Neon serverless driver. This approach allows developers to streamline their workflow and query Postgres efficiently, saving time and money.

Resources

Here are a few additional resources to help you through this process:

• Neon serverless driver documentation
• Connecting Neon with psql
• Netlify functions