Posted inDatabase

Stop Writing CRUD: Build a REST API Directly from PostgreSQL with PostgREST

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Boilerplate Trap

I’ve spent countless weekends trapped in a loop. I design a clean PostgreSQL schema, then waste the next three days writing repetitive CRUD (Create, Read, Update, Delete) logic in Node.js or Python. I build controllers, define DTOs, and manually map every database column to an API endpoint. It is tedious, error-prone, and kills project momentum.

This friction exists because we have accepted a massive architectural gap as the norm. We treat the database as a passive storage bucket and the backend as the only source of intelligence. However, PostgreSQL is already incredibly sophisticated. It features a robust type system, complex constraints, and a battle-tested permission model. Rewriting that logic in a middle tier is often redundant.

PostgREST eliminates this middleman by serving a RESTful API directly from your schema. It inspects your database structure and generates endpoints on the fly. You get a production-ready API without writing a single line of backend code.

Quick Start: From Zero to API in 5 Minutes

I prefer tools that stay out of the way. PostgREST is a single binary compiled in Haskell. It is remarkably lightweight, usually consuming less than 30MB of RAM, yet it can handle over 2,000 requests per second on a basic $5-a-month VPS. Here is how I set up a live API on a fresh machine.

1. Prepare the Database

Security starts with isolation. I never expose the public schema. Instead, I create a dedicated api schema to control exactly what the web sees. Let’s build a simple task manager.

-- Connect to your Postgres instance
CREATE SCHEMA api;

CREATE TABLE api.todos (
  id SERIAL PRIMARY KEY,
  done BOOLEAN DEFAULT false,
  task TEXT NOT NULL,
  due TIMESTAMPTZ
);

-- Create a role for anonymous web requests
CREATE ROLE web_anon NOLOGIN;
GRANT USAGE ON SCHEMA api TO web_anon;
GRANT SELECT ON api.todos TO web_anon;

2. Install and Run PostgREST

Installation is straightforward. You can use Docker, but for local development, the binary is faster. Grab the latest release for your OS and extract it.

# Example for Linux users
wget https://github.com/PostgREST/postgrest/releases/download/v12.0.2/postgrest-v12.0.2-linux-static-x64.tar.xz
tar xf postgrest-v12.0.2-linux-static-x64.tar.xz

Next, create a tutorial.conf file to tell PostgREST how to talk to your database:

db-uri = "postgres://authenticator:mysecretpassword@localhost:5432/postgres"
db-schema = "api"
db-anon-role = "web_anon"

Fire it up with ./postgrest tutorial.conf. Your API is now live at http://localhost:3000/todos. A simple GET request will return your data as a clean JSON array immediately.

Deep Dive: Security with JWT and RLS

The most common concern I hear is: “How do I handle user permissions?” PostgREST doesn’t bother with custom login logic. It delegates security to JSON Web Tokens (JWT) and PostgreSQL’s native Row Level Security (RLS). This approach is significantly more secure than traditional application-level checks.

The Authenticator Pattern

Think of the authenticator role as a trusted proxy. It connects to the database but has no permissions of its own. Its only job is to switch to a specific user role based on the claims found in a JWT.

-- Create a role for logged-in users
CREATE ROLE todo_user NOLOGIN;
GRANT USAGE ON SCHEMA api TO todo_user;
GRANT ALL ON api.todos TO todo_user;
GRANT USAGE, SELECT ON SEQUENCE api.todos_id_seq TO todo_user;

-- Allow the authenticator to switch to todo_user
GRANT todo_user TO authenticator;

Configuring the JWT Secret

Add a jwt-secret to your tutorial.conf. This key must be at least 32 characters to prevent brute-force attacks.

jwt-secret = "a-very-secure-32-character-secret-key"

When a client sends an Authorization: Bearer <token> header, PostgREST verifies it. If the token contains "role": "todo_user", the database executes the query as that user. No more manually checking if (user.id === record.owner_id) in your Node.js code.

Advanced Usage: Beyond Simple CRUD

PostgREST is not just a basic wrapper. It handles complex data requirements that usually require hundreds of lines of code.

Powerful Filtering

You don’t need to build a search engine. PostgREST maps URL parameters to SQL conditions. To find incomplete tasks that mention ‘coffee’ and are due soon, just use:

GET /todos?done=is.false&task=like.*coffee*&limit=10

Resource Embedding (Joins)

Fetching related data often leads to the “N+1 query” performance trap. PostgREST solves this with Resource Embedding. If your users and todos tables are linked by a foreign key, you can fetch them together in one request:

GET /users?select=name,todos(*)

Efficient Data Imports

Handling bulk data is a common pain point. When I need to migrate legacy spreadsheets, I use toolcraft.app/en/tools/data/csv-to-json to convert the rows into a JSON array locally. I then POST that array directly to the PostgREST endpoint. The database handles the bulk insert in a single transaction, ensuring data integrity.

Production Hardening

After deploying PostgREST for several high-traffic projects, I’ve found these three practices essential:

  • Use SQL Views: Treat your tables as private. Expose Views to the API instead. This allows you to change your database schema without breaking the frontend’s API contract.
  • Force RLS: Always enable Row Level Security. It acts as a safety net, ensuring a user can never access someone else’s row even if an API endpoint is misconfigured.
  • Stored Procedures for Logic: For actions like sending a Welcome Email, use a PostgreSQL trigger or a function. You can trigger these via POST /rpc/send_welcome_email.

By moving logic into the database, you create a “single source of truth.” Whether your users connect via a web app, a mobile app, or a CLI tool, your business rules remain consistent. It forces you to master SQL—a skill that will remain relevant long after today’s trendy backend frameworks have disappeared.

Share:
Tags: