Don't query your PostgreSQL db anymore, prefer PostgREST

Last year I had a large application developed in Laravel that required a MySQL database. When I was migrating to PostgreSQL I discovered PostgREST, which allowed me to completely remove queries from my code.

Don't get me wrong: my Laravel/PHP code was launching dozens of queries to the database and, after migration, none at all.

My tables, my models, my SQL queries, I was able to remove everything from my code. My PHP code has been greatly lightened and simplified.

What PostgREST does for you​

There is a todos table in a PostgreSQL database. No controller, no route file, no model — nothing has been written. And yet:

$ curl http://localhost:3000/todos | jq▋

[
  {
    "id": 1,
    "done": false,
    "task": "finish tutorial 0",
    "due": null
  },
  {
    "id": 2,
    "done": false,
    "task": "pat self on back",
    "due": null
  }
]

Ready-to-consume JSON, straight from the table, without a single line of backend code. It's awfully easy, isn't it?

Why it works​

• The database schema is the API. "PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permission in the database determine the API endpoints and operations" (the official documentation). A table becomes an endpoint, a column becomes a field.
• Permissions are the security layer, and they live where they belong: in PostgreSQL. A users table you never grant to the anonymous role simply doesn't exist as far as the API is concerned.
• There's nothing left to keep in sync. After my own migration, I deleted 100% of the code declaring tables and fields, the relationship declarations, and the queries. What used to be a model layer became URLs.

Let's play​

For this post, let's create a temporary folder in your /tmp folder: start a Linux console and run mkdir /tmp/postgrest && cd $_.

Step 1 - Create and populate our PostgreSQL database​

We'll create a Docker container for our PostgreSQL database:

$ docker run --name tutorial -p 5433:5432 

-e POSTGRES_PASSWORD=mysecretpassword 

-d postgres▋

Now, we'll enter in our PostgreSQL container and run psql:

$ docker exec -it tutorial psql -U postgres▋

Copy/Paste the code below in your console. This will create a database called api with a table called todos; with two records. This will also create a web_anon user that we'll use with PostgREST to query our data:

create schema api;

create table api.todos (
  id serial primary key,?
  done boolean not null default false,?
  task text not null,
  due timestamptz?
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

create role web_anon nologin;?

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

create role authenticator noinherit login password 'mysecretpassword';?
grant web_anon to authenticator;?

Now, to leave the postgres console, just type \q.

Step 2 - Install and execute PostgREST​

PostgREST is a binary, download it by running:

curl -o postgrest-v14.16-linux-static-x86-64.tar.xz -L https://github.com/PostgREST/postgrest/releases/download/v14.16/postgrest-v14.16-linux-static-x86-64.tar.xz
tar xJf postgrest-v14.16-linux-static-x86-64.tar.xz && rm -f postgrest-v14.16-linux-static-x86-64.tar.xz▋

You've now a file called postgrest in your folder.

We need to create a configuration, please create a file called tutorial.conf with this content:

db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"?
db-schemas = "api"?
db-anon-role = "web_anon"?

server-port = 3000?

Now, we'll run a Docker container for PostgREST:

$ ./postgrest tutorial.conf▋

More queries​

So, in step 1, we've created and populated our PostgreSQL database and, in step 2, we've installed and configured PostgREST to use that database. From now, we can directly use it like any API.

Every query is made of a fixed part (the URL of our PostgREST server), which is http://localhost:3000, followed by the query itself. To get the content of a table, just mention its name — that's the http://localhost:3000/todos call shown at the top of this article.

For aesthetic reasons here, I'm using | jq (you can remove that part if you want). See my The jq utility for Linux article to learn more about jq.

Using a filter​

You can use filters by typing f.i. ? followed by a field name and a criteria. To get only the record having the id 2, here is how to do:

$ curl http://localhost:3000/todos?id=eq.2 | jq
▋

And if you wish to make a Full text search to retrieve a content based on a value, here is how to do:

$ curl http://localhost:3000/todos?task=fts.tutorial | jq
▋

In the example above, we'll search any todo where the field task contains the word tutorial.

Below, a query on the field done which should be true.

$ curl http://localhost:3000/todos?done=is.true | jq
▋

Below, we are asking for getting only fields id and task:

$ curl http://localhost:3000/todos?select=id,task | jq
▋

From the frontend​

Since these are plain HTTP endpoints, your frontend consumes them directly. In JavaScript, using axios:

const todos = axios.create({
    baseURL: 'http://localhost:3000/todos',
    headers: {
      'Accept': 'application/json'
    }
})

Under the hood (skip this if you just want to use it)​

Permissions required​

By using PostgREST you expose your tables and records through a RESTful API. Naturally, there is a system of permission so that you can define what can be accessed (e.g. a users table will remain secret) and what can be done (e.g. one user will only have read access but another will have read-write access).

OpenAPI​

PostgREST is compliant with OpenAPI. It's then possible to auto-document its routes using the Swagger UI Docker image.

This means that running curl http://localhost:3000 (the PostgREST URL), you'll get the list of all tables accessible to you (using your access key). This makes your database open to the world (once again, only what you've allowed using correct permission).

Close PostgREST​

Return to the console in which you've started PostgREST and press CTRL-C to stop PostgREST from running.

If you've started PostgreSQL here above, you can stop and kill it using docker container stop tutorial ; docker container rm tutorial.

Illustration of some calls​

Citizens​

• Get the list of all citizens: clear ; curl http://127.0.0.1:3000/citizens | jq
• Only citizen ID 69: clear ; curl http://127.0.0.1:3000/citizens?id=eq.69 | jq
• Only citizen ID 69 and id, firstname and lastname: clear ; curl http://127.0.0.1:3000/citizens?select=id,first_name,last_name&id=eq.69 | jq
• Get Jean but only the one speaking Dutch (language ID 2) (i.e. a WHERE with two conditions): clear ; curl http://127.0.0.1:3000/citizens?first_name=fts.Jean&language_id=eq.2 | jq

Workers​

• Get the list of all workers, the first five: clear ; curl http://127.0.0.1:3000/workers?select=id,email&limit=5 | jq

• Get the list of all workers, the next five: clear ; curl http://127.0.0.1:3000/workers?select=id,email&limit=5&offset=5 | jq

• Reverse order, get the last 10: clear ; curl http://127.0.0.1:3000/workers?select=id,email&limit=10&offset=0&order=id.desc | jq

• Get worker id 15: clear ; curl http://127.0.0.1:3000/workers?id=eq.15 | jq — as we can see, the output is an array with only one record

• Get worker id 15 - no more array: clear ; curl http://127.0.0.1:3000/workers?id=eq.15 -H "Accept: application/vnd.pgrst.object+json" | jq (here it's easier and more logic for the frontend)

Using inner join​

• Get the list of workers and their first_name and last_name (limit to worker ID 73): clear ; curl http://127.0.0.1:3000/workers?select=id,email,citizens(first_name,last_name)&id=eq.73 | jq
• Add the language code: clear ; curl http://127.0.0.1:3000/workers?select=id,email,citizens(first_name,last_name,language_id),languages(code)&id=eq.73 | jq

Levels​

• Get the list of all levels: clear ; curl http://127.0.0.1:3000/levels | jq
• Get the list of levels where ID is greater than 10, only ID and code: clear ; curl http://127.0.0.1:3000/levels?select=id,code&id=gt.10 | jq

Translations​

• Get the list of translations with the word Technical: clear ; curl http://127.0.0.1:3000/translations?value=fts.Technical | jq
• Get the list of translations starting with the word Technical, case insensitive: clear ; curl http://127.0.0.1:3000/translations?value=ilike.technical* | jq

Generic profiles​

• Get the list of active generic profiles: clear ; curl http://127.0.0.1:3000/generic_profiles?active=is.true | jq
• and rename the field code to GenericProfileCode: curl http://127.0.0.1:3000/generic_profiles?select=id,GenericProfileCode:code&active=is.true | jq

Output format​

• Get the list as JSON: clear ; curl http://127.0.0.1:3000/workers -H "Accept: application/json"
• Get the list as CSV: clear ; curl http://127.0.0.1:3000/workers -H "Accept: text/csv"
• Get the list as TEXT - Here we need to return only one value: clear ; curl http://127.0.0.1:3000/workers?select=email&id=eq.15 -H "Accept: text/plain"

Casting​

• Get the ID and email of worker 15: clear ; curl http://127.0.0.1:3000/workers?select=id,email&id=eq.75 | jq
• Cast the ID as string: clear ; curl http://127.0.0.1:3000/workers?select=id::text,email&id=eq.75 | jq

Conclusion​

A container, a binary and a .conf file, and your database answers HTTP. What I find remarkable isn't the speed of the setup, it's what disappears afterwards: the models, the relationship declarations, the query builder calls — an entire layer whose only job was to describe, in your language, something PostgreSQL already knew.

Oracle has its own equivalent, described in Transform an Oracle DB as OpenData using Oracle REST Data Services. And to look at the database behind the API, Adminer, pgadmin or phpmyadmin remains handy.