Faster Postgres Queries with Cloudflare Hyperdrive and Neon

If you have a database that gets queried from multiple regions (e.g. via edge functions) and you want to improve overall performance, this guide may help. To demonstrate, we’ll use Neon, a fully managed serverless PostgreSQL, and Cloudflare Hyperdrive, a lightning-fast globally distributed database acceleration platform. When you put Hyperdrive between your database and globally distributed applications and functions that query it, Cloudflare will route and cache queries to reduce latency and improve overall performance.

This guide will walk you through each step of the process, from setting up Neon Postgres and Cloudflare Hyperdrive to writing custom API services and deploying your application. We'll also cover essential topics such as utilizing Wrangler CLI for Hyperdrive setup, learning how Hyperdrive makes queries faster, and benchmarking native Postgres queries against the Hyperdrive accelerated queries.

Prerequisites

A Cloudflare account.
Wrangler CLI installed on your local machine.
Your local machine should have Node.js and npm installed. Wrangler CLI requires a Node version of 16.13.0 or later to avoid permission issues.
A basic knowledge of SQL.

Setting up Neon Postgres

Setting up your first Neon Postgres project is straightforward—follow the steps below to get started!

Visit Neon to sign up for an account.

After a successful registration, the system will redirect you to create a new project. Here, you can fill in what Postgres version you want to use, the project name, the database name, and the region you want the database instance to run on.

A pop-up with the connection link to your Neon Postgres database will appear when your project is successfully created. Select the psql option from the dropdown list, copy the string, and save it somewhere. You will use it to connect to Hyperdrive in your demo application later.

Finally, create a table and insert some data into your database using the Neon SQL Editor. From the side menu, locate the SQL Editor option, click on it, replace the command with the one below, and run it.

    CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, phone TEXT NOT NULL);
    INSERT INTO users (name, email, phone) VALUES
      ('Kehinde', 'kh@nmail.com', '1209867574'),
      ('Desmond', 'des@nmail.com', '4584930999'),
      ('Franklin', 'frk@nmail.com', '3458976048'),
      ('Aisha', 'as@nmail.com', '0987954890'),
      ('Adaeze', 'az@nmial.com', '1234567890');
    SELECT * FROM users;

This SQL command does three things specifically:

The first command, CREATE TABLE users, creates a table in your database called "users” with columns: id, name, email, and phone. The id is set to be the primary key and should follow a series, while the other three are text strings and should not be null; that is to say, they shouldn’t be empty when creating a user record.
The second command, INSERT INTO users, inserts data into each column of the table as specified in the VALUES section.
The last one, SELECT * FROM users, returns all the data in the users table.

Upon successfully running the query above, you’ll get a screen like this one below. This screen is found in the Tables Menu. Click on Tables from the side menu and select users to see the new content created.

Setting up Cloudflare Hyperdrive and Workers

In this section, you will learn how to set up a Cloudflare Worker project with the Wrangler CLI, bind your Hyperdrive proxied database, and use it in your application. You will be creating the application from scratch.

If you already have Wrangler CLI installed, you don’t need to worry about this first step. If you don’t have Wrangler CLI installed, run this command in a new terminal to install it globally:

    npm install wrangler@latest

Next, navigate to the directory where you want to store your project on your machine and run this command to set up a Cloudflare Workers application.

    npm create cloudflare@latest

Workers is a Cloudflare service that provides an execution environment that allows you to create new applications or augment existing ones without configuring or maintaining infrastructure.
Workers run on Cloudflare’s global network in hundreds of cities worldwide.

The above command will prompt you to install the create-cloudflare package and lead you through the setup steps.

For this guide, set up a basic Workers application by:

Naming your new Worker directory by specifying where you want to create your application.
Selecting the "Hello World" script as the type of application you want to create.
Answering "yes" to using TypeScript.
You will be asked if you want to deploy the project to Cloudflare. Choose not to deploy and go to the newly created project directory to begin writing code. You will do the deployment later.

When the setup is done and before creating your Hyperdrive binding, log in with your Cloudflare account for authentication by running the command below:

    cd <your-worker-project> && npx wrangler login

This command will open your browser to log into Cloudflare and acquire access to your terminal. Upon a successful connection, you will get a screen like the one below:

Let’s connect to Hyperdrive now that you have configured Wrangler CLI and have logged in to your Cloudflare account. Run the command below to connect your Neon Postgres to Hyperdrive:

    npx wrangler hyperdrive create pg-hyperdrive --connection-string="your-neon-db-connecion-string"

A few things to note in the above command:

The wrangler hyperdrive create command initializes the Hyperdrive creation.
The pg-hyperdrive is the name of your Hyperdrive. It can be any name of your choice.
The --connection-string flag="your-neon-db-connection-string" is for passing your database connection string as a parameter to the create command.

You will get a response in your terminal like the one below upon a successful connection.

The final step for this section is to set the generated Hyperdrive ID to your wrangler.toml file, which you will use to connect to Neon Postgres in the next section, as shown below. You must remove the initial content from the package and update your wrangler.toml file like the one below. Remember to replace the placeholder with your Hyperdrive ID!

Writing the API services

If you did everything above, you should have a folder structure like the one below. In this section, you will set up the database connection, create custom query functions, and update the /src/index.ts file to match your configurations. Also, since you are deploying to Workers, you will use the Hono framework instead of Express.

Hono is an Ultrafast web framework for Cloudflare Workers, Fastly Compute, Deno, Bun, Vercel, Lagon, Node.js, and others. You can learn more about Hono here.

First, install two key packages: the Postgres driver and Hono. Also, you’ll need to install the Postgres driver types as a dev dependency since you are using Typescript.

    npm i pg hono
    npm i --save-dev @types/pg

Create a dbconnect.ts file in the src folder and paste the code below.

    // src/dbconnect.ts
    import { Context } from 'hono';
    import { Client } from 'pg';

    export const db = async (c: Context) => {
        const client = new Client({ connectionString: c.env.HYPERDRIVE.connectionString });
        // Connect to your database
        await client.connect();
        return client
    };

In the above code, you set up a database client from the Postgres driver to use the link generated by Hyperdrive as a proxy to your database and return the client instance for your app to use in reading and writing to the database.

Next, you will write some helper functions that will be used to perform read and write operations to your database through Hyperdrive proxy. You will create a queries.ts file in the src directory and paste the code below.

    // src/queries.ts
    import { Context } from "hono";
    import { db } from "./dbconnect";

    type User = {
        name: string;
        email: string;
        phone: string;
    }
    export const getUsers = async (c: Context) => {
        const limit = c.req.query('limit') || 10;
        const offset = c.req.query('offset') || 0;
        const dbClient = await db(c);
        let result = await dbClient.query
            (`SELECT * FROM users LIMIT ${limit} OFFSET ${offset}`);
        return result;
    };

    export const getUser = async (c: Context) => {
        const id = parseInt(c.req.param('id'))
        const dbClient = await db(c);
        let result = await dbClient.query
            (`SELECT * FROM users WHERE id = ${id}`);
        return result;
    }
    export const createUser = async (c: Context) => {
        const user = await c.req.json<User>()
        const dbClient = await db(c);
        let result = await dbClient.query
            (`INSERT INTO users (name, email, phone) VALUES ('${user.name}', '${user.email}', '${user.phone}')`);
        return result;
    }

Here, you are writing three different functions that get all the users, get a single user, and create a user in the database. For each function, you are passing the context parameter, which contains the request body or parameters, to the database client. The database client will instantiate with these parameters. After that, you can use the database client to make your queries or insertion. After a successful query or insertion, it returns the result to be read in the application.

Finally, update the contents of the index.ts file in the src folder to match the code below.

    // src/index.ts
    import { Hono } from 'hono';
    import { cors } from 'hono/cors'
    import { prettyJSON } from 'hono/pretty-json'
    import { getUsers, getUser, createUser } from './queries'
    import { db } from './dbconnect';


    type Env = {
        HYPERDRIVE: Hyperdrive;
    };
    const app = new Hono();
    app.get('/', (c) => c.text('Pretty Users API'))
    app.use('*', prettyJSON())
    app.notFound((c) => c.json({ message: 'Route Not Found', ok: false }, 404))

    const api = new Hono<{ Bindings: Env }>()
    api.use('/users/*', cors())
    api.get('/users', async (c) => {
        const users = await getUsers(c)
        return c.json({ users })
    })
    api.get('/users/:id', async (c) => {
        const user = await getUser(c)
        return c.json({ user })
    })
    api.post('/users', async (c) => {
        const ok = createUser(c)
        return c.json({ ok })
    })
    app.route('/api', api)

    export default app;

In the code above, you obtained the Hyperdrive data from wrangler.toml. You first created an app instance with Hono and set the basic endpoint that you can use to check your routing works fine and make the JSON response formatted with the prettyJSON() method.

After that, you created another instance of Hono called API, which has the Env binding. This is so that you can read the value of the Hyperdrive ID from the context anywhere in the app. Hopefully, it now makes sense why you passed the context to your query functions and database connection function too.

Finally, you defined each route to call their respective query functions and pass the context down to them. The API route is now defined so that your endpoint calls will be to api/users/…

Remember, this is a Cloudflare Worker application using Hono, so in the index, you can read the data in your .toml file as a type of Env.

Deploying to Cloudflare Workers

Now that everything is set up, the next step is to deploy your application to Cloudflare Workers, the serverless execution environment. To deploy your application, run

    npx wrangler deploy

This will deploy your application to Workers. If you are not logged in to the Wrangler CLI, the command will prompt you to log in for authentication; your application will then be deployed. You will get a response upon a successful deployment, as shown in the image below.

Testing and benchmarking with vs without Hyperdrive

The core reason for using Hyperdrive to proxy the database instead of using the Neon connection URL directly in your application is the optimization for speed and performance when making queries. This section compares the response time for a large query from across the world, first using our new API on Hyperdrive and then using a traditional single-region API server. As you can see below, the Hyperdrive route is significantly faster.

The link to the applications used for the demo can be found here on GitHub.

Here’s a query to fetch all users in the database for Neon Postgres and Cloudflare Hyperdrive proxy. It responded at 780ms on the first query and between 300ms and 450ms on subsequent queries.

Here’s a similar query to fetch all users in the database that’s routing through a single API server. It responded at 1888ms at the first query and between 600ms and 3s on subsequent queries.

This video demonstrates the speed test between Neon Postgres connected through Hyperdrive and Neon Postgres connected through an API App in a single region.

Speed Benchmarking For Databases Queries | Opentape

Desmond Obisi - Feb 7th, 2:21pm

app.opentape.io

Why was the Workers + Hyperdrive approach faster?

A few reasons:

Workers are deployed globally, close to users - for most of the world, the network latency between local device and worker function is lower than when using a single API server.
Hyperdrive keeps a connection to the database alive - Hyperdrive keeps a “warm” connection to the database alive across function calls. This eliminates the multiple round trips usually required to establish a new connection.
Hyperdrive caches certain responses - For SELECT queries, Hyperdrive caches responses for 60 seconds by default, so repetitive queries will never even hit the database.

Conclusion

Congratulations! If you followed along, you learned to use Neon Serverless Postgres with Cloudflare Hyperdrive to achieve faster database queries. You also learned about Workers, Wrangler CLI for interfacing with Cloudflare's products, and Hono for writing Node.js APIs that can be deployed to Workers.

Other things that can be explored on this subject leveraging Hyperdrive for intensive database operations are transactions and query caching. You can check the reference section for a feel of other things Cloudflare offers with the Hyperdrive developer tool.

Blog