Using Neon + PostgREST to Build a Supabase-like Developer Experience

I'm a big fan of using Supabase to quickly spin up a Postgres DB and start building a POC. It's truly a product that has mapped out the entire developer workflow, really honing in on how devs are using the product and what features they can build to accommodate. One of my favorite features of Supabase is that they offer a REST API and Javascript SDK for interacting with your database, making it easy to quickly start querying data from your application. In fact, I've used extensively it for many samples.

Unfortunately, we can't all use Supabase at work, for one reason or another. You might be hosting your Postgres on AWS, or using a different hosted Postgres service like Neon. I didn't want to give up the Supabase DevX so I did some digging into how to recreate it. If you'd like to skip to the final result, check out the repository.

Table of Contents

1. Choosing a DB Platform
2. Using PostgREST to Build a Postgres API
3. Constructing PostgREST Queries using postgrest-js
4. Hosting is Hard (For Me)
5. Running PostgREST on Vercel
6. Making Queries
7. (Optional) API Gateway
8. Wrapping-Up

Choosing a DB Platform

You likely already have Postgres running somewhere on the cloud, but in case you don't - I'd recommend using a serverless Postgres platform with a free tier. For production workloads where you're cost sensitive, AWS Postgres is also an option - but I don't want to deal with setting that up. For this tutorial, I am going to use Neon. Creating a project is fairly straightforward.

Using PostgREST to Build a Postgres API

If you read through Supabase's REST API documentation, you'll find that the backend is actually built using a project called PostgREST. PostgREST is a web server that creates a REST API over any Postgres database. It has a specific query structure for translating query parameters into Postgres queries.

GET /rest/v1/books?select=title,description&description=ilike.*cheese*&order=title.desc&limit=5&offset=10 HTTP/1.1
Host: localhost:54321

Supabase offers a playground for learning the syntax if you'd like to see it in action. You might find constructing these queries difficult on your client.

Constructing PostgREST Queries using postgrest-js

Thankfully, Supabase already provided a PostgREST client library for constructing queries from your front-end. It seems to have the exact same syntax as the Supabase JS library - which helps us immensely in our mission to recreate their devx.

import { PostgrestClient } from "@supabase/postgrest-js";

const REST_URL = "http://localhost:3000";
const postgrest = new PostgrestClient(REST_URL);

const { data, error } = await postgrest.from("countries").select();

Hosting is Hard (For Me)

Alright, so now we have a database, PostgREST server library, and a query client. If you're proficient with tools like Docker and Digital Ocean, you could easily deploy the server and frontend to droplets. Just grab your Neon DB's connection string (from the quickstart tab), and follow this guide.

I am one of the unfortunate souls who is not proficient with these tools. Instead, I am going to challenge myself to build this API using tools a typical web hobbyist (ie. the typical Supabase dev) would know - namely Typescript + NodeJS, Vercel for hosting, and NextJS as my client and server framework. This approach will be fully serverless, so you won't need to manage any infrastructure besides your database.

Running PostgREST on Vercel

There is no native library for PostgREST in Typescript, so I did some digging and found that Subzero created a node library that translates PostgREST queries into SQL queries. Their Vercel sample is a bit out of date, but it will work with any Postgres database host. I decided to adapt the implementation to use Neon's serverless Node driver.

In your NextJS repo, create a file called [...query.ts] under src/pages/api. If you are using the app directory - there are currently some bugs with NextJS on Vercel with wasm bundling (which subzero needs). Here's a sample of what your code would look like once these issues are fixed. I am working with the Subzero team to get a working sample out.

Once you've created the file, the first step is to initialize Subzero with an introspection query. Don't worry about the sql parameter just yet.

import Subzero, {
  SubzeroError,
  getIntrospectionQuery,
  Env as QueryEnv,
} from "@subzerocloud/nodejs";
import { neon, NeonQueryFunction } from "@neondatabase/serverless";

const publicSchema = "public";
const dbType = "postgresql";
let subzero: Subzero;

async function initSubzero(sql: NeonQueryFunction<false, false>) {
  const { query, parameters } = getIntrospectionQuery(
    dbType,
    publicSchema, // the schema name that is exposed to the HTTP api (ex: public, api)
  );
  const data = await sql(query, parameters);

  // the result of the introspection query is a json string representation of the database schema/structure
  // this schema object is used to generate the queries and check the permissions
  // to make the function startup faster, one can cache the schema object
  const schema = JSON.parse(data[0].json_schema);
  subzero = new Subzero(dbType, schema);
}

Now that Subzero is initialized, we can start making queries against our Neon database. Set process.env.DATABASE_URL to your Postgres connection string from the previous step.

import type { NextApiRequest, NextApiResponse } from "next";

const role = "anonymous";
const urlPrefix = "/api";

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  const method = req.method || "GET";
  if (!process.env.DATABASE_URL) {
    throw new Error("DATABASE_URL is not set");
  }
  if (!["GET", "POST", "PUT", "DELETE", "PATCH"].includes(method)) {
    throw new SubzeroError(`Method ${method} not allowed`, 400);
  }

  const sql = neon(process.env.DATABASE_URL!);
  // initialize the subzero instance if it is not initialized yet
  if (!subzero) {
    await initSubzero(sql);
  }

  const queryEnv: QueryEnv = [
    ["role", role],
    ["request.method", method],
    ["request.headers", JSON.stringify(req.headers)],
    ["request.jwt.claims", JSON.stringify({ role })],
  ];
  const { query, parameters } = await subzero.fmtStatement(
    publicSchema,
    `${urlPrefix}/`,
    role,
    req,
    queryEnv,
  );

  let result: Record<string, unknown>[];

  try {
    result = await sql(query, parameters);
  } catch (e) {
    console.error(
      `Error performing query ${query} with parameters ${parameters}`,
      e,
    );
    throw e;
  }
  res.setHeader("Content-Type", "application/json");
  res.json(result);
}

The code above will handle any calls to /api/{table_name}, translating PostgREST requests into Postgres queries.

Making Queries

Now that we have an API endpoint running, you can use the postgres.js library to start making requests from your NextJS frontend. You can create a simple page.tsx at the root of your project for this.

"use client";

import { PostgrestClient } from "@supabase/postgrest-js";

const REST_URL = `${process.env.NEXT_PUBLIC_API_URL}/api`;

export default function Home() {
  const performFetch = async () => {
      const postgrest = new PostgrestClient(REST_URL);
      const { data, error } = await postgrest
        .from("playing_with_neon")
        .select("*")
        .order("id", { ascending: false });
      if (data) {
        console.log(data);
      }
      if (error) {
        console.error(JSON.stringify(error, null, 2))
      }
    };

  return (
    <button
      onClick={performFetch}
    >
      Fetch Table Data
    </button>
  );
}

If you'd like to see this in action, check out my demo application.

(Optional) API Gateway

Before exposing an API publicly, I'd typically recommend you put it behind an API gateway (ex. Zuplo). Some features you might find valuable include:

• Rate Limiting requests
• Caching DB fetches
• Authentication

Setup is pretty straightforward, simply clone and run this repo, and change your process.env.NEXT_PUBLIC_API_URL to match your gateway's URL. The demo I provided above actually rate limits you to 10 GET requests per minute.

Wrapping-Up

Congratulations, you now have a fully serverless PostgreSQL REST API with a Supabase developer experience! All of the code can be found in this repo so you can run it yourself. All of the services used above have generous free tiers, so this is a good choice for hobby projects.

As an alternative, you can consider using an ORM like Prisma (which has docs for Neon).

If you would prefer a more automated solution - check out these options.

Lastly, if you want to be able to customize your CRUD APIs while still matching your database table schemas - check out how to generate OpenAPI from your database.