Ping Thing: Serverles Postgres at the Edge

In this post, I’d like to introduce Neon’s serverless driver which is suitable for use with Vercel Edge Functions. I’ll explain how to use it with Next.js and a free Neon serverless Postgres database

Here’s a demo I created. I’ve called it Ping Thing. 

What does Ping Thing do?

When users click Ping, the app sends a request to an Edge Function deployed on Vercel, where the geolocation data is extracted from the request and posted to a Neon database. I’ve added a 3D globe to visualise the journey and calculated some statistics about the request. 

Give it a Ping and see your data journey

Can I use ordinary Postgres at the Edge?

The short answer is no, not currently. The main reason being, Postgres connections are made over TCP/IP and generally speaking, “Edge” environments like Vercel Edge Functions, based on V8 isolates, don’t speak TCP

Can I use Neon serverless Postgres at the Edge?

Yes, and here’s why. Native Postgres couples compute and storage, but with Neon, we’ve separated them. Neon’s serverless driver is a drop-in replacement for node-postgres, aka pg. Everything you know and love about pg, will continue to work as expected as will any pg-compatible libraries. The difference is the way we handle the connection. 

The serverless driver can use either HTTP or Websockets to make a connection to a Neon proxy, which in turn makes a TCP connection to Postgres. This makes Neon a great solution when working in Edge environments.

How to use Neon’s serverless driver with Next.js

In the below example, I’ll show you how to use Neon’s serverless driver with Next.js API Routes.

To get started, sign up to Neon, then follow our Create your first project guide. You might also like to have a look at this guide from our docs: Query with Neon’s SQL Editor.  
Once you have a database set up, save the connection string to your Next.js .env file and give it a name of DATABASE_URL.

Neon’s serverless driver

To get started install the package.

npm install @neondatabase/serverless

Create an API Route

Any file added to pages/api will be treated as an API endpoint, you can read more about API routes in the Next.js docs. 
You’ll need to change table_name to the table name you create in your database.

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

export default async function handler() {
  const sql = neon(process.env.DATABASE_URL);
  const response = await sql`SELECT * FROM table_name`;

  return Response.json({
    message: 'A Ok!',
    data: response,
  });
}

export const config = {
  runtime: 'edge',
};

And that’s it! Postgres at the Edge.

SQL-over-HTTP or WebSockets?

In the above example using the neon export, you’re querying using SQL-over-HTTP. This is great for single-shot queries, but if you’re looking to perform multiple queries in a single connection you might want to take a look at Pool or Client which use WebSockets.

Pool and Client are part of the same npm package, and you can use Pool like this.

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

export default async function handler(req, ctx) {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });


  const response = await pool.query('SELECT * FROM table_name');
  ctx.waitUntil(pool.end());

  return Response.json({
    message: 'A Ok!',
    data: response.rows,
  });

  }

export const config = {
  runtime: 'edge',
};

Having the flexibility to use either, HTTP or WebSockets in one package is super helpful and here’s a great blog post from my colleague Raouf Chebri where he explains both approaches in more detail: HTTP vs. WebSockets: Which protocol for your Postgres queries at the Edge

What can you do with Edge Functions?

Good question, now you’re at the Edge, what ya gonna do? Hopefully, more than simply return hello world!

Using @vercel/edge for Geolocation Information

One area I’ve been investigating uses Vercel’s geolocation information. You can use this handy little helper package to see geolocation information from incoming requests.

To get started, install the package.

npm install @vercel/edge

Storing user’s geolocation data in Neon

The below code snippet is an example of destructuring a user’s country, city, latitude and longitude from the geolocation helper function and an INSERT statement to add the data to a table called locations.

import { neon } from '@neondatabase/serverless';
import { geolocation } from '@vercel/edge';

export default async function handler(req) {
  const sql = neon(process.env.DATABASE_URL);

  const { country, city, latitude, longitude } = geolocation(req);

  await sql('INSERT INTO locations (country, city, latitude, longitude) VALUES ($1, $2, $3, $4)', [
    country,
    city,
    latitude,
    longitude,
  ]);

  return Response.json({
    message: 'A Ok!',
  });
}

export const config = {
  runtime: 'edge',
};

And here’s the schema I used to create the locations table.

CREATE TABLE locations (
  id           SERIAL PRIMARY KEY,          
  country      VARCHAR,
  city         VARCHAR,
  latitude     DECIMAL,
  longitude    DECIMAL
);

You could use this approach to capture information about your site visitors, kinda like a “lite” version of Google Analytics, or maybe submit geolocation data along with newsletter sign-ups so you can start to build up a picture of where your user base is. 

Or, as I’ve done, use this information to show the journey data makes to and from the database.

Screen shot of Ping Thing with animated line showing the journey data makes from London to North Virginia

Conclusion

Setting up a Postgres database has never been this easy, or fast (~2s), and with Neon’s serverless driver, you can use Edge Functions to read and write data in your site or app. If you want to try this out yourself sign up at neon.tech.

Where you go from here is completely up to you, but if you’d like any further information, please come and find me on Twitter/X: @PaulieScanlon.

TTFN.