Eric Radman : a Journal

REST APIs based on PostgreSQL Schemas

A standard REST API for PostgreSQL have been discussed for some time, and thanks for PostgREST there is now an implementation that works very well.

Alternate Strategies

Once upon a time we would spend weeks and months creating elaborate getters and setters that essentially map a URL to a function which would use "model" objects to spawn queries to the database an then return a list of maps.

Building PostgREST

First install the GHC compiler and Cabal packaging system

pkg_add ghc cabal-install git postgresql-server
cabal update
cabal install -j --disable-documentation stack

Next checkout the latest release branch

git clone
cd postgrest
~/.cabal/bin/stack setup
git checkout v0.4.4.0
~/.cabal/bin/stack build
~/.cabal/bin/stack test

Database Schemas

Each instance of PostgREST will explicitly use the schema specified at server startup. List schemas using

SELECT nspname FROM pg_catalog.pg_namespace;
-- or

Creating a mapping from a new schema to the default is public is simple

  CREATE VIEW api1.machine AS SELECT * FROM machine;

Schemas and all of their contents can also be erased for easy reconstruction


Adding and Updating Records

If you do a lot of interaction with a REST service, especially one that accepts JSON, then you may find that httpie is easier than curl. By default the command http (what an unfortunate name) lists the reply headers and pretty-prints the payload.

Adding a record

$ http POST "$url/farmer" org_name='New Farm' city=Owego state=NY zip=1111
HTTP/1.1 201 Created
Date: Tue, 05 Jul 2016 20:56:30 GMT
Location: /farmer?farmer_id=eq.6
Server: postgrest/
Transfer-Encoding: chunked

Updating a record

$ http PATCH "$url/farmer?farmer_id=eq.6" zip=13827
HTTP/1.1 204 No Content
Content-Range: 0-0/1
Date: Tue, 05 Jul 2016 21:04:13 GMT
Server: postgrest/

Calling Functions

Any function that is visible in the schema can be called directly:

$ curl -d '' -X POST "$url/rpc/first_friday"

Using Python requests

The requests module for Python makes interacting with PostgREST very smooth. All of the common HTTP verbs are supported out of the box. This example uses PATCH to update a record:

import yaml
import requests

conf = yaml.load(open("/path/endpoints.yaml"))
url = conf['rest-writer-url']
r = requests.patch(url + "/rack", params={"id": "eq.26"},
  json={"fan_speed": 80})

Similarly, fetching data is as easy as supplying some selection criteria and parsing the reply

r = requests.get(url + "/rack", params={"fan_speed": "eq.80"})
for row in r.json():
    print row

Disarming a Trap

The documentation for PostgREST acknowledges that updating or deleting records is dangerous , but it is only so because postgrest accepts PATCH and DELETE without criteria

$ curl -s -X PATCH $url/employees \
    -H "Content-Type: application/json" -d '{"first_name": "John"}'

There are a couple workarounds for this. The first is to front HTTP requests using a web server and do assert that a query string was provided

# nginx.conf
server {
    listen  *:80 default_server;

    set $is_bad "";
    if ( $request_method = PATCH ) { set $is_bad "PATCH"; }
    if ( $request_method = DELETE ) { set $is_bad "DELETE"; }
    if ( $args = "" ) { set $is_bad "${is_bad}:noid"; }
    if ( $is_bad = "PATCH:noid" ) {
        return 400 '{"details":"no records specified for PATCH"}';
    if ( $is_bad = "DELETE:noid" ) {
        return 400 '{"details":"no records specified for DELETE"}';

    location /api1/ {
        proxy_pass http://localhost:5002/;

This is provides some safety, but if the url includes any non-filter parameters such as select=field1,field2 then this check is effectively disabled.

The second solution is to use the Postgres extension pg-safeupdate to prohibit this behavior on the database server.

# postgresql.conf

Last updated on November 29, 2019