PostgREST: Revolutionizing Web Development with Instant APIs

François Zaninotto
François ZaninottoNovember 04, 2024
#database#js#node-js#react-admin

PostgREST has been around for a long time, but many web developers still haven't discovered how it can simplify their workflow. In my experience, it's a game-changer, shifting the focus of web development to the frontend since building REST APIs becomes a solved problem.

Let me show you how PostgREST makes working with a database so much easier.

Querying a PostgreSQL Database Using a Backend Framework

To set the scene, let's imagine we have a working PostgreSQL database.

psql -U username -d your_database -h localhost -p 5432

Suppose we have a table called products that looks like this:

SELECT * FROM products WHERE stock > 50;
 id |    name    | price  | stock
----+------------+--------+-------
  2 | Smartphone |  800.0 |   120
  3 | Tablet     |  500.0 |    80

Traditionally, making this information accessible through a web API would require creating a web server using a framework like Ruby on Rails or API Platform. This approach is often time-consuming, involving a lot of boilerplate: defining models, controllers, DTOs, etc.

Some of that work can be automated, but it still results in lots of code and configuration files. For example, creating a Product resource in Rails might look like this:

$ bin/rails g scaffold Product name:string price:float stock:integer
      invoke  active_record
      create    db/migrate/20241031130027_create_products.rb
      create    app/models/product.rb
      invoke    test_unit
      create      test/models/product_test.rb
      create      test/fixtures/products.yml
      invoke  resource_route
       route    resources :products
      invoke  scaffold_controller
      create    app/controllers/products_controller.rb
      invoke    resource_route
      invoke    test_unit
      create      test/controllers/products_controller_test.rb

Here's what one of the seven generated files might look like:

class ProductsController < ApplicationController
  before_action :set_product, only: %i[ show update destroy ]

  # GET /products
  def index
    @products = Product.all

    render json: @products
  end

  # GET /products/1
  def show
    render json: @product
  end

  # POST /products
  def create
    @product = Product.new(product_params)

    if @product.save
      render json: @product, status: :created, location: @product
    else
      render json: @product.errors, status: :unprocessable_entity
    end
  end

  # PATCH/PUT /products/1
  def update
    if @product.update(product_params)
      render json: @product
    else
      render json: @product.errors, status: :unprocessable_entity
    end
  end

  # DELETE /products/1
  def destroy
    @product.destroy!
  end

  private
    # Use callbacks to share common setup or constraints between actions.
    def set_product
      @product = Product.find(params[:id])
    end

    # Only allow a list of trusted parameters through.
    def product_params
      params.require(:product).permit(:name, :price, :stock)
    end
end

Customizing and maintaining this codebase can be a costly and time consuming. And we only have one resource! Imagine having to do this for every table in your database.

Presenting relational data as a REST API was done by virtually every web developer, so we don't need to reinvent the wheel and generate code every time we need it. There is a better way. What if we could just drop an executable in front of the DB to have it serve the data as a REST API? That's the idea behind PostgREST.

PostgREST: A REST API for Your PostgreSQL Database

PostgREST turns your existing PostgreSQL database into a fully-functional REST API—instantly. Instead of spending hours writing your own backend, PostgREST does the heavy lifting for you. Here's how:

  1. Install PostgREST: Download the executable from the official releases page. You can install it on your local machine or a server hosting your database.

  2. Grant Access in the Database: Create a role and grant it permissions to access your schema and tables:

    create role web_anon nologin;
grant usage on schema public to web_anon;
grant select, update, insert, delete on public.products to web_anon;

  3. Configure PostgREST: Create a configuration file (postgrest.conf) with details like the database URL, schema, and the API port:

    db-uri = "postgres://username:password@localhost:5432/your_database"
db-schema = "public"
db-anon-role = "web_anon"

  4. Run PostgREST: Start the API server with:

    postgrest postgrest.conf

That's it! PostgREST exposes all your database tables as a REST API on port 3000.

For example, to fetch all products with stock greater than 50:

curl "http://localhost:3000/products?stock=gt.50" \
     -X GET
     -H "Content-Type: application/json"

The response:

[
  {
    "id": 2,
    "name": "Smartphone",
    "price": 800.0,
    "stock": 120
  },
  {
    "id": 3,
    "name": "Tablet",
    "price": 500.0,
    "stock": 80
  }
]

Suddenly, without having to write a single line of backend code, your PostgreSQL data is available through a standard web API. You can now query, filter, and access all your data using just HTTP requests.

You can even add new data:

curl "http://localhost:3000/products" \
     -X POST
     -H "Content-Type: application/json" \
     -d '{"name": "Smartwatch", "price": 250.0, "stock": 30}'

The Power of Plug-and-Play APIs

Since PostgREST provides a standard API, you can easily integrate it with other tools and frameworks that understand REST.

For instance, if you need an admin panel for your products table, you could use React-Admin. React-Admin can generate a full admin dashboard from the PostgREST API with minimal configuration:

import { Admin, Resource, ListGuesser, EditGuesser } from 'react-admin';
import postgrestDataProvider from 'ra-data-postgrest';

const dataProvider = postgrestDataProvider('http://localhost:3000');

const App = () => (
  <Admin dataProvider={dataProvider}>
    <Resource name="products" list={ListGuesser} edit={EditGuesser} />
  </Admin>
);

export default App;

React-Admin takes care of the UI, generating forms, tables, and more, allowing you to list, edit, and delete products effortlessly.

This is an autogenerated admin, but developers can customize the UI by providing their own components for listing, editing, and creating resources. Check the Posters Galore demo to see what a customize React-Admin app looks like.

YouTube might track you and we would rather have your consent before loading this video.

Always allow

Using Supabase for Authentication, Storage, and Edge Functions

The API on port 3000 isn't protected yet, but you can add JWT authentication using PostgREST's authentication guide. For client-side authentication, you can use a React-Admin auth provider.

PostgREST is not limited to pet projects or CRUD-only APIs. You can add custom business logic by placing a proxy in front of PostgREST that catches custom routes and redirects standard CRUD operations to PostgREST.

If you don't want to manage these configurations yourself, consider using Supabase, an open-source backend-as-a-service powered by PostgREST.

Supabase

Supabase comes with built-in authentication and supports all the major authentication methods, including password, magic link, one-time password (OTP), social login, and single sign-on (SSO). You can secure your data with fine-grained permissions, using row-level security.

Supabase also provides edge functions where you can put your custom business logic, or the controllers for RPC routes. Supabase has a storage service for file uploads, and it even has a real-time database that pushes updates to clients in real time.

You can host Supabase on the managed service at supabase.com. It is free for small projects, and you only pay for the resources you use. For new projects, it's a no-brainer. And if your project grows, you can always switch to a self-hosted version of Supabase. The managed service is built on top of the open-source project, so you can easily migrate your data and configuration.

Note: I am not affiliated with Supabase, but I am a happy user of the service.

Why PostgREST is a Game-Changer

The combination of PostgREST, Supabase, and tools like React-Admin changes the game for web developers. You can:

  • Start a new project in minutes: Define your data model in SQL, get an autogenerated UI, and start tweaking it to fit your needs.
  • Deploy your prototype quickly: Use Supabase for the backend and any CDN (like GitHub Pages or Netlify) for the frontend.
  • Focus on delivering value: Forget boilerplate and CRUD operations. Spend your time building features that matter.
  • Reduce maintenance costs: PostgREST and Supabase handle the backend, so there is less code to maintain and fewer bugs to fix.

In most of the new projects we start at Marmelab, we no longer need a custom backend. Data is served by Supabase, server-side logic runs on Edge Functions, and we focus on crafting user workflows with React-Admin. Full-stack web development effectively becomes frontend development.

Beyond REST and CRUD

While PostgREST is powerful, it has limitations. The API is as relational as the database it exposes, which can be a challenge when aggregating data for specific use cases.

Fortunately, PostgREST supports resource embedding and aggregate functions:

curl "http://localhost:3000/films?select=title,directors(id,last_name)"
[
  {
    "title": "Workers Leaving The Lumière Factory In Lyon",
    "directors": {
      "id": 2,
      "last_name": "Lumière"
    }
  },
  {
    "title": "The Dickson Experimental Sound Film",
    "directors": {
      "id": 1,
      "last_name": "Dickson"
    }
  },
  {
    "title": "The Haunted Castle",
    "directors": {
      "id": 3,
      "last_name": "Méliès"
    }
  }
]

If that's not enough, you can define views in the database, and postgREST will serve them as new resources.

And if this is still not enough, you probably need a Backend-for-Frontend. But Supabase can help you with that, too: if offers a GraphQL API on top of your Postgres database.

curl -X POST https://<PROJECT_REF>.supabase.co/graphql/v1 \
    -H 'apiKey: <API_KEY>' \
    -H 'Content-Type: application/json' \
    --data-raw '{"query": "{ accountCollection(first: 1) { edges { node { id } } } }", "variables": {}}'
{
  accountCollection(first: 1) {
    edges {
      node {
        id
      }
    }
  }
}

Conclusion

Traditional web development can be slow and repetitive. PostgREST and related tools are simplifying the process, letting developers build fully functional web apps quickly and efficiently. It’s about speed, simplicity, and focusing on the creative part of development.

If you haven't tried PostgREST yet, it's time to see why it's one of the best tools for modern web development.

Did you like this article? Share it!