Faster Postgres Queries with Cloudflare Hyperdrive and Neon
Tags: neon, postgres, cloudflare, database
Published: 2024-02-07 14:36:14

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](https://neon.tech/?ref=hm), a fully managed serverless PostgreSQL, and [Cloudflare Hyperdrive](https://developers.cloudflare.com/hyperdrive?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity), 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](https://www.cloudflare.com/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) account.
- [Wrangler CLI](https://developers.cloudflare.com/workers/wrangler?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) installed on your local machine.
- Your local machine should have [Node.js and npm](https://nodejs.org/en) installed. [](https://volta.sh/)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](https://neon.tech/?ref=hm) to sign up for an account. 

![Neon Signup Page](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701103010915_image.png)


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.

![Neon Configuration Page](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701103267497_image.png)


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.

![Neon Connection Details](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701103512412_image.png)


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.
```sql
    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.

![Users Database Tabel on Neon](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701259681604_image.png)

## 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:
```bash
    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.
```bash
    npm create cloudflare@latest
```

> [Workers](https://developers.cloudflare.com/workers?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) 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:
```bash
    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:

![Cloudflare Hyperdrive Connection](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701447934551_image.png)


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:
```bash
    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.

![Hyperdrive Successful Setup](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701110968753_image.png)


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!

![wrangler.toml file](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701255103831_image.png)

## 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. 

![Project Folder Structure](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701255433841_image.png)

> 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](https://hono.dev/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).

First, install two key packages: the [](https://www.npmjs.com/package/pg?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)[Postgres driver](https://www.npmjs.com/package/pg?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) [](https://www.npmjs.com/package/pg?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)and [Hono](https://www.npmjs.com/package/hono?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity). Also, you’ll need to install the [Postgres driver types](https://www.npmjs.com/package/@types/pg?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) as a dev dependency since you are using Typescript.
```bash
    npm i pg hono
    npm i --save-dev @types/pg
```
Create a `dbconnect.ts` file in the `src` folder and paste the code below.
```js
    // 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.

```js
    // 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.
```js
    // 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
```bash
    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.

![Deploy to Cloudflare](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701262373669_image.png)

## 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](https://github.com/DesmondSanctity/neon-hyperdrive?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).

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.

![API query for Neon Hyperdrive Setup](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701265456519_image.png)


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.

![API query for Native Postgres and Sequelize](https://paper-attachments.dropboxusercontent.com/s_E03542534A79EC87E10D2FB5933065250A6B885C85D8C4854C5C0B1E5E1D3E84_1701266130260_image.png)


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

{% embed https://app.opentape.io/share/3b6d4fae-65d2-4f00-9341-f0cb574476be %}

## Why was the Workers + Hyperdrive approach faster?

A few reasons:

1. **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.
2. **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.
3. **Hyperdrive caches certain responses -** For `SELECT` queries, Hyperdrive [caches responses for 60 seconds by default](https://developers.cloudflare.com/hyperdrive/learning/query-caching/), so repetitive queries will never even hit the database.

## Conclusion
Congratulations! If you followed along, you learned to use [Neon](https://neon.tech/?ref=hm) [Serverless](https://neon.tech/?ref=hm) [Postgres](https://neon.tech/?ref=hm) 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.

## References
- [HyperDrive Docs](https://developers.cloudflare.com/hyperdrive?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Wrangler CLI](https://developers.cloudflare.com/workers/wrangler?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Cloudflare Workers](https://developers.cloudflare.com/workers?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Hono.dev Cloudflare Workers Docs](https://hono.dev/getting-started/cloudflare-workers?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Build a Web Application with Hono](https://blog.logrocket.com/build-web-application-hono/)
- [Neon Postgres Docs](https://neon.tech/docs/introduction?ref=hm)