PostgREST: Get started with automated PostgreSQL APIs

This blog introduces you to PostgREST and helps you to get started with it quickly. What is PostgREST? First think about this: a modern database application usually doesn’t connect to the database directly and issue arbitrary SQL commands. In general, there is always some kind of abstraction layer in between. This layer could be Hibernate code or simply some kind of API.

The trouble is: Writing APIs by hand is a lot of work, and pretty cumbersome. Usually the database has most of the information already (= fields, data types, etc.) However, if the API is written by hand, you basically duplicate what the database already knows. PostgREST is a totally different approach: it inspects the database system catalog and generates an API out of your database automatically. As long as you have sufficient permissions tables, views and functions will be represented as API endpoints. Basically, the amount of work needed to create a fully functional and bug-free API is close to zero.

As you can see, PostgREST is between PostgreSQL and your client app. It is stateless and can therefore easily be scaled.

Installing PostgREST

Installing PostgREST is relatively easy. For the sake of this post I have used Mac OS X to run the tool. However, it works on most platforms.

To install on Mac OS X we can simply use “brew install”. It is the easiest way to get you going in seconds:

What about Linux?

On Linux the process is pretty simple too. All that you need to do is to download the package and untar the file, as shown in the next listing:

Once this is done we can run the “--help” command to see which options are available:

As you can see, all we have to do is to feed a config file to the tool and that’s it. It's really that simple and straightforward.

Creating sample data and starting PostgREST

Firing up PostgREST is easy, but let's create some demo data first -- to ensure that there is actually data which can be exposed to PostgREST later on. To demonstrate the tool, all we need is a table containing a handful of records:

Once this is done, the next step is to come up with a config file. Below you can see that it's really short and rarely contains more than a connect string and some basic user information:

The critical part is that the password has to be sufficiently long. The rest is straightforward.
Let 's start the tool:

PostgREST will cache the information and expose the API.

We can use a command line tool to access the API or do the same using any library provided by the programming language of your choice:

The first thing you can see here is that PostgREST already exposes a lot of information when calling the web service. You will receive a lengthy JSON document containing end points, the version and a lot more.

Accessing a table using API calls

Now that you've successfully started the tooling, you can try to access the table in your demo database. As you can see, an API endpoint is readily available. Again, use a command line call to fetch the JSON document which is then transformed into a nicer-looking representation using “jq” (which is a super-useful command line tool to display JSON documents):

In this case, you've fetched the data without filters. However, you can easily use a filter and only fetch rows “greater or equal than” (gte) 2:

The filter will reduce the result set to what you really want to see. PostgREST provides you with various operators to filter and order data, as described by the official PostgREST documentation.

Handling schema changes in PostgreSQL

PostgREST will cache the information taken from the PostgreSQL system catalog, as I stated earlier. This is done for performance reasons because inspecting the PostgreSQL catalog for every single API call is way too slow. Therefore the cache has to be refreshed in case the underlying data structure in your database has changed:

A simple signal is enough to make PostgREST refresh the caches. Some log lines will tell you that the cache has indeed been refreshed:

Additional functionality

PostgREST provides a rich set of features. This is true on the API as well as on the security side. In general, PostgREST will try to mimic the permission set found in the database and expose it to the outer world consistently. The API will therefore have the same security policies as your data structure. There is no need to change things manually.

On top of accessing tables you can also access views, stored procedures and a lot more. Endpoints will be provided automatically.

You can use a rich set of client libraries to access the API via HTTP or HTTPS. There are hardly any known limitations.

Finally …

If you want to dive deeper into PostgreSQL and you're interested in architecture, check out Pavlo Golub's latest post on using Mermaid and SQL to create ER (Entity Relationship) diagrams.