# Source: https://neon.com/llms/guides-postgrest.txt

# Create a REST API from Postgres with PostgREST

> The document guides Neon users on creating a REST API from a PostgreSQL database using PostgREST, detailing the setup process and configuration steps specific to Neon's environment.

## Source

- [Create a REST API from Postgres with PostgREST HTML](https://neon.com/docs/guides/postgrest): The original HTML version of this documentation

What you will learn:
- What is PostgREST and how it works
- Setting up a Neon project for PostgREST
- Running PostgREST with Docker
- Adding authentication with JWT
- Implementing Row-Level Security

Related resources:
- [PostgREST Documentation](https://docs.postgrest.org/en/v12/)
- [PostgREST Tutorials](https://postgrest.org/en/v12/tutorials/tut0.html)

Source code:
- [PostgREST GitHub Repository](https://github.com/PostgREST/postgrest)

## What is PostgREST?

PostgREST is a standalone web server that automatically turns your PostgreSQL database schema into a RESTful API. It uses the database's structure, constraints, and permissions to create API endpoints without requiring you to write any backend code. The API follows REST conventions and supports full CRUD operations, filtering, pagination, and even complex joins.

This guide shows you how to set up PostgREST with a Neon Postgres database using Docker for local development. You'll learn how to configure basic read access, add authenticated endpoints with JWT tokens, and implement row-level security for fine-grained access control.

## Create a Neon project

If you do not have one already, create a Neon project.

1. Navigate to the [Projects](https://console.neon.tech/app/projects) page in the Neon Console.
2. Click **New Project**.
3. Specify your project settings and click **Create Project**.

## Set up your database

From the [Neon SQL Editor](https://neon.com/docs/get-started/query-with-neon-sql-editor) or any SQL client such as [psql](https://neon.com/docs/connect/query-with-psql-editor), set up your database using the following queries:

```sql
CREATE SCHEMA api;

CREATE TABLE api.students (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);

INSERT INTO api.students (first_name, last_name) VALUES
('Ada', 'Lovelace'),
('Alan', 'Turing');

CREATE ROLE anonymous NOLOGIN;
GRANT anonymous TO neondb_owner;
GRANT USAGE ON SCHEMA api TO anonymous;
GRANT SELECT ON ALL TABLES IN SCHEMA api TO anonymous;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT ON TABLES TO anonymous;
```

   **Tip**: While this example uses SERIAL for simplicity, consider using UUID as a primary key in production systems—especially in distributed environments, when exposing identifiers in URLs, or when avoiding predictable sequences is important.

## Copy your database connection string

Retrieve an unpooled database connection string — PostgREST requires a direct connection to your database.

1. Navigate to your **Project Dashboard** in the Neon Console.
2. Click the **Connect** button to open the **Connect to your database modal**.
3. Toggle **Connection pooling** to disable it — you need an unpooled connection string.
4. Copy the connection string.



## Run PostgREST

Use Docker to run PostgREST locally, specifying the **unpooled** database connection string.

Tab: Linux

```bash
docker run --rm --net=host \
  -e PGRST_DB_URI="<non-pooled-connection-string-from-neon-console>" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_DB_ANON_ROLE="anonymous" \
  postgrest/postgrest
```

Tab: macOS

```bash
docker run --rm \
  -e PGRST_DB_URI="<non-pooled-connection-string-from-neon-console>" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_DB_ANON_ROLE="anonymous" \
  -p 3000:3000 \
  postgrest/postgrest
```

Tab: Windows

```bash
docker run --rm \
  -e PGRST_DB_URI="<non-pooled-connection-string-from-neon-console>" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_DB_ANON_ROLE="anonymous" \
  -p 3000:3000 \
  postgrest/postgrest
```

Once running, visit http://localhost:3000/students to confirm the API is working. You should see the following records in your browser:



## Add authenticated access

To support full CRUD operations (inserts, updates, and deletes), you need to set up permissions in your database by creating a role for authenticated users. Here, we create an `authenticated` role, assign privileges, and grant the role to our database owner (`neondb_owner`). Run these commands from the Neon SQL Editor or an SQL client.

```sql
CREATE ROLE authenticated NOLOGIN;
GRANT authenticated TO neondb_owner;
GRANT USAGE ON SCHEMA api TO authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA api TO authenticated;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA api TO authenticated;
```

## Run PostgREST with a JWT secret

Run PostgREST again, this time with a JWT secret that will be used by PostgREST to verify the JWT that we will attach to our requests in a later step.

Tab: Linux

```bash
docker run --rm --net=host \
  -e PGRST_DB_URI="<non-pooled-connection-string-from-neon-console>" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_JWT_SECRET="reallyreallyreallyreallyverysafe" \
  -e PGRST_DB_ANON_ROLE="anonymous" \
  postgrest/postgrest
```

Tab: macOS

```bash
docker run --rm \
  -e PGRST_DB_URI="<non-pooled-connection-string-from-neon-console>" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_JWT_SECRET="reallyreallyreallyreallyverysafe" \
  -e PGRST_DB_ANON_ROLE="anonymous" \
  -p 3000:3000 \
  postgrest/postgrest
```

Tab: Windows

```bash
docker run --rm \
  -e PGRST_DB_URI="<non-pooled-connection-string-from-neon-console>" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_JWT_SECRET="reallyreallyreallyreallyverysafe" \
  -e PGRST_DB_ANON_ROLE="anonymous" \
  -p 3000:3000 \
  postgrest/postgrest
```

## Authenticate requests using JWT

Now that we have defined our JWT secret above (`reallyreallyreallyreallyverysafe`), we'll create a sample JWT that's signed with this secret. If you didn't change the secret used above, you can use this JWT:

```text
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aGVudGljYXRlZCJ9.XOGSeHS8usEzEELkUl8SWOrsOLP7xWmHckRSTgpyP3o
```

   **Tip**: You can use [jwt.io](https://jwt.io/) to generate your own JWT. Make sure to use the **HS256** algorithm.

Now let's test different CRUD operations using standard HTTP methods. Notice that we've attached the JWT in the `Authorization` header as a bearer token.

**Insert a student:**

```bash
curl http://localhost:3000/students \
  -X POST \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aGVudGljYXRlZCJ9.XOGSeHS8usEzEELkUl8SWOrsOLP7xWmHckRSTgpyP3o" \
  -d '{"first_name": "Grace", "last_name": "Hopper"}'
```

You should see the following records after refreshing your browser:



**Update a student:**

```bash
curl "http://localhost:3000/students?id=eq.1" \
  -X PATCH \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aGVudGljYXRlZCJ9.XOGSeHS8usEzEELkUl8SWOrsOLP7xWmHckRSTgpyP3o" \
  -d '{"first_name": "Ada I.", "last_name": "Lovelace"}'
```

Refresh your browser to see the updated records:



**Delete a student:**

```bash
curl "http://localhost:3000/students?id=eq.3" \
  -X DELETE \
  -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aGVudGljYXRlZCJ9.XOGSeHS8usEzEELkUl8SWOrsOLP7xWmHckRSTgpyP3o"
```

You should now see these records:



## Use Row-Level Security (RLS)

PostgREST supports Postgres RLS for fine-grained access control. Here's an example policy to restrict access to a user's own records. Run these statements on your database in the Neon SQL Editor or an SQL client.

```sql
ALTER TABLE api.students ENABLE ROW LEVEL SECURITY;

CREATE POLICY students_policy ON api.students
    FOR ALL
    TO authenticated
    USING (id = (SELECT current_setting('request.jwt.claims', true)::json->>'sub')::integer)
    WITH CHECK (id = (SELECT current_setting('request.jwt.claims', true)::json->>'sub')::integer);
```

The JWT token used in the CRUD examples above contains a payload with `{"role": "authenticated"}`, which tells PostgREST to use the `authenticated` role for those requests.

In a real application, you would want to:

- Generate tokens with proper expiration times
- Include user-specific claims in the JWT (most likely, a "sub" field which corresponds to users' IDs)
- Implement a proper authentication server/service (or use a third-party managed auth provider)

Now let's test this with a JWT that includes a user ID. We'll create a new JWT with a payload that includes a user ID in the "sub" claim:

Now, let's generate a new JWT that has the following payload defining the student ID (and sign it with the same JWT secret from above):

```
{
  "role": "authenticated",
  "sub": "1"
```

Here's the new token:

```text
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aGVudGljYXRlZCIsInN1YiI6IjEifQ.U_EgeU0y0pAM5cTsMXndJe_cR1vG5Vf9dq4DkqfMAxs
```

Now, run this command with the new token:

```bash
$ curl "http://localhost:3000/students" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aGVudGljYXRlZCIsInN1YiI6IjEifQ.U_EgeU0y0pAM5cTsMXndJe_cR1vG5Vf9dq4DkqfMAxs"
[{"id":1,"first_name":"Ada I.","last_name":"Lovelace"}]
```

Because the `students` table has a RLS policy attached to the student's ID, the student can only view their own records.

## Summary

The examples shown above are simple, but they illustrate how PostgREST works. With Neon and PostgREST, you can instantly turn your Postgres database into a REST API—no backend code required. This setup is ideal for rapid prototyping, internal tools, or even production workloads where you want to focus on your data and business logic rather than boilerplate API code.

## Next steps

Now that you have a basic PostgREST API running with Neon, here are some things you can try next:

- **Explore advanced querying**: Implement [filtering](https://docs.postgrest.org/en/v12/api.html#horizontal-filtering-rows), [ordering](https://docs.postgrest.org/en/v12/api.html#ordering), and [pagination](https://docs.postgrest.org/en/v12/api.html#limits-and-pagination) in your API requests
- **Add resource embedding**: Use [resource embedding](https://docs.postgrest.org/en/v12/api.html#resource-embedding) to fetch related data in a single request
- **Implement stored procedures**: Expose [PostgreSQL functions](https://docs.postgrest.org/en/v12/api.html#stored-procedures) as API endpoints for complex operations
- **Example applications**: Explore [example applications](https://docs.postgrest.org/en/v12/ecosystem.html#example-apps) built with PostgREST to get inspiration for your own projects
- **Try out templates**: These [templates](https://docs.postgrest.org/en/v12/ecosystem.html#templates) combine PostgREST with various frontend technologies