---
title: "Using Neon + PostgREST to Build a Supabase-like Developer Experience"
description: "Learn how to build a Supabase-inspired developer experience for interacting with your hosted Postgres Database via REST API and Javascript/Typescript SDK."
canonicalUrl: "https://zuplo.com/learning-center/neon-postgrest-rest-api"
pageType: "learning-center"
authors: "adrian"
tags: "APIs, API Tooling, Supabase, Tutorial, Node.js, TypeScript"
image: "https://cdn.zuplo.com/cdn-cgi/image/fit=crop,width=1200,height=630/www/media/posts/2024-11-25-neon-postgrest-rest-api/image-5.png"
---
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](https://supabase.com/docs/guides/api) and
[Javascript SDK](https://supabase.com/docs/reference/javascript/introduction)
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](/learning-center?search=Supabase).
![Supabase generated API docs](/media/posts/2024-11-25-neon-postgrest-rest-api/image-1.png)

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](https://neon.tech/home). 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](https://github.com/zuplo-samples/neon-postgrest).

## Table of Contents

1. [Choosing a DB Platform](#choosing-a-db-platform)
2. [Using PostgREST to Build a Postgres API](#using-postgrest-to-build-a-postgres-api)
3. [Constructing PostgREST Queries using postgrest-js](#constructing-postgrest-queries-using-postgrest-js)
4. [Hosting is Hard (For Me)](#hosting-is-hard-for-me)
5. [Running PostgREST on Vercel](#running-postgrest-on-vercel)
6. [Making Queries](#making-queries)
7. [(Optional) API Gateway](#optional-api-gateway)
8. [Wrapping-Up](#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](https://neon.tech/home). Creating a project is fairly
straightforward.

![Neon create project](/media/posts/2024-11-25-neon-postgrest-rest-api/image-2.png)

## 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](https://docs.postgrest.org/en/v12/). PostgREST is a web server that
creates a REST API over any Postgres database. It has a
[specific query structure](/learning-center/sql-query-to-api-request#postgrest)
for translating query parameters into Postgres queries.

```http
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](https://supabase.com/docs/guides/api/sql-to-rest)
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](https://github.com/supabase/postgrest-js) 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.

```javascript
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](https://docs.postgrest.org/en/v12/tutorials/tut0.html).

![Neon connection string](/media/posts/2024-11-25-neon-postgrest-rest-api/image-3.png)

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](https://github.com/subzerocloud/showcase/tree/main) that
translates PostgREST queries into SQL queries. Their
[Vercel sample](https://github.com/subzerocloud/showcase/tree/main/vercel-postgresql-neon)
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](https://www.npmjs.com/package/@neondatabase/serverless).

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](https://github.com/zuplo-samples/neon-postgrest/blob/55ab5d69d174637af7d50ee9e168b0381dc7e6f8/src/app/api/%5Bquery%5D/route.ts.txt#L1)
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.

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

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

```typescript
"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](https://neon-postgrest.zuplopreview.net/).

![Neon serverless demo](/media/posts/2024-11-25-neon-postgrest-rest-api/image-1.gif)

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

![System diagram](/media/posts/2024-11-25-neon-postgrest-rest-api/image-4.png)

Setup is pretty straightforward, simply clone and run
[this repo](https://github.com/zuplo-samples/neon-postgrest-proxy), 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](https://github.com/zuplo-samples/neon-postgrest) 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](https://www.prisma.io/docs/orm/overview/databases/neon)).

If you would prefer a more automated solution -
[check out these options](/learning-center/generate-api-from-database).

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](/learning-center/generate-openapi-from-database).