Quickly developing REST APIs with PostgREST

PostgreSQL^® + RESTful API = PostgREST!

Nowadays, it's a very common development pattern to store information in a relational database and interact with the data via a RESTful HTTP interface.

Today I'm going to show you a handy tool called PostgREST. It generates a RESTful API directly from your PostgreSQL data model, removing the need to write your own interface. We will use Aiven for PostgreSQL as our database layer and a nice dataset from Kaggle.

Create a PostgreSQL service on Aiven

The database of choice today is Aiven for PostgreSQL. If you don't have an account yet, sign up and enjoy your free trial! We will also use the Aiven CLI, follow the installation and authentication instructions on the GitHub page.

With the CLI in place, type the following command on your terminal to create a PostgreSQL service:

avn service create --service-type pg --cloud google-europe-west3 --plan hobbyist pg-quotes
Copy to clipboard

You now have a PostgreSQL service up and running!

Get the data

To populate our database, we use this lovely Goodreads Quotes dataset from Kaggle. Go to the page and download the quotes.csv file on your current directory. This file contains all the data we need to start our adventure.

By inspecting the CSV file, we see the following columns:

• index: the quote ID
• quote: the quote itself
• author: who the quote's author
• tags: a list of strings to categorize the quote
• likes: how many likes the quote got on Goodreads

We will create a database table to hold this data structure.

Configure the PostgreSQL

With the dataset in hands, we now import it into PostgreSQL. We also create a Schema and Role to interact with PostgREST later.

In your terminal, use the handy avn service cli to connect to the PostgreSQL console:

avn service cli pg-quotes
Copy to clipboard

On the console, execute the first SQL command of the day. We create a new Schema called api to hold our table and data:

CREATE SCHEMA api;
Copy to clipboard

Now we create the table called quotes based on the CSV columns we inspected before:

CREATE TABLE api.quotes (
    index SERIAL,
    quote TEXT,
    author VARCHAR(255),
    tags TEXT,
    likes INTEGER
);
Copy to clipboard

The index field has the SERIAL type, meaning it will auto increment on every new quote added. However, since our CSV file already have indexes up to 2999, new quotes need to have the index field starting from 3000. The following command does just that:

ALTER SEQUENCE api.quotes_index_seq RESTART WITH 3000;
Copy to clipboard

The api.quotes_index_seq was created automatically when we created the table above. The name pattern is <SCHEMA>.<TABLE>_<COLUMN>_seq.

PostgreSQL has a SQL command called COPY to move data between a file and a table. It is quite useful with CSV files. The console command \COPY uses COPY, but knows how to read from the local filesystem. Run the code below to import all the lines from the quotes.csv into your table:

\COPY api.quotes FROM 'quotes.csv' CSV HEADER;
Copy to clipboard

PostgREST uses PostgreSQL roles as RESTful API users. Let's create a role named operator with all permissions on the data:

CREATE ROLE operator nologin;

GRANT usage ON SCHEMA api TO operator;
GRANT all ON api.quotes TO operator;
GRANT usage, SELECT ON SEQUENCE api.quotes_index_seq TO operator;
Copy to clipboard

In our example, operator has very broad data access and you probably don't want that in a production system. PostgREST supports authentication with JSON Web Tokens, read the official documentation if you want to know more!

The PostgreSQL configuration is ready!

Next, let's dive into PostgREST to get your HTTP interface.

Meet your new friend: PostgREST

PostgREST is a webserver which magically exposes your PostgreSQL database as an HTTP RESTful API, offering all the CRUD operations – create, read, update and delete.

It relies on well known PostgreSQL features. For example, roles becomes API users, SQL operations become query parameters and stored procedures are available as API endpoints.

Today we are running PostgREST on our local machine, but it can be deployed on any platform that can run the single binary or a Docker container. You may use a virtual machine or a Heroku app, for example.

Several major tools, like Supabase and Retool, use PostgREST as the API layer. It also has a lively community - you can catch up with the developers and users in their Gitter room.

Running PostgREST

PostgREST is available as a single binary in all major package managers. Before moving on, follow the official instructions to install it on your machine.

The last bit we need to configure is the connection between PostgREST and PostgreSQL. We start by retrieving the PostgreSQL connection string using the Aiven CLI:

avn service get pg-quotes --format '{service_uri}'
Copy to clipboard

With the output in hand, create a file named postgrest.conf with the content below:

db-uri = "<the-output-from-the-previous-command-here>"
db-schema = "api"
db-anon-role = "operator"
Copy to clipboard

As you can see, db-schema and db-anon-role refer to the previously created schema and role.

Finally, let's start the PostgREST server by executing the command below:

postgrest postgrest.conf
Copy to clipboard

If everything goes smoothly, the server is running locally on the http://localhost:3000 address.

Explore the RESTful API

It feels like a lot of configuration, however, it is less than writing your own CRUD backend app. With everything in place, let's explore what kind of requests we can make.

This basic HTTP request returns all quotes:

curl "http://localhost:3000/quotes"
Copy to clipboard

PostgREST supports all sorts of filters – based on the SQL language you may already know. For example, we can limit the output to 5 quotes by using the limit=N query parameter:

curl "http://localhost:3000/quotes?limit=5"
Copy to clipboard

We can extend our HTTP query filter to return only quotes with more than 1000 likes using the field=filter.value pattern, and select which fields we want to display with the select option:

curl "http://localhost:3000/quotes?limit=5&likes=gt.1000&select=index,quote,author"
Copy to clipboard

If it feels a lot like SQL, is because PostgREST tries its best to use already existing features from PostgreSQL, so you don't need to learn many new things.

So far we been exploring only the read from CRUD, what about other operations?

Use the HTTP POST method to add a new quote:

curl "http://localhost:3000/quotes" \
    -X POST -H "Content-Type: application/json" \
    -d \
    '
    {
        "author": "Paulo Freire",
        "quote": "When education is not liberating, the dream of the oppressed is to become the oppressor."
    }
    '
Copy to clipboard

You can check that the new entry was added correctly by executing:

curl "http://localhost:3000/quotes?index=eq.3000"
Copy to clipboard

To update the quote we just added, use the HTTP PATCH verb to add the tags field to the quote with the index value of 3000:

curl "http://localhost:3000/quotes?index=eq.3000" \
    -X PATCH -H "Content-Type: application/json" \
    -d \
    '
    {
        "tags": "education;inspirational;philosophy;wisdom"
    }
    '
Copy to clipboard

Lastly, use the HTTP DELETE verb to delete all quotes where the author field contains the ?? string – the database has some corrupted entries, this operation will clean it up!

curl -X DELETE "http://localhost:3000/quotes?author=like.*??*"
Copy to clipboard

Next steps!

PostgREST is definitely a handy tool to spin up a RESTful API without needing to code in a high-level programming language or using a web framework. Today we only touched the basics of PostgREST, but if you are eager to try out more, here are some topics from their documentation that we recommend:

• Authentication System
• Creating Stored Procedures
• PostgREST Apps and Extensions

Wrapping up

Not using Aiven services yet? Sign up now for your free trial at https://console.aiven.io/signup!

In the meantime, make sure you follow our changelog and blog RSS feeds or our LinkedIn and Twitter accounts to stay up-to-date with product and feature-related news.

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

Further reading

• Use cases for PostgreSQL®

• Remote read replicas - the what, why, and how

• (Postgre)SQL concepts and terms