Setting up an HTTP interface to a database can be a lot of work. Find out how PostgREST makes it quick and simple to provide RESTful access to PostgreSQL®.
Jonatas "jojo" Baldin
|RSS FeedDeveloper Advocate at Aiven
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
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.
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:
Loading code...
You now have a PostgreSQL service up and running!
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 IDquote: the quote itselfauthor: who the quote's authortags: a list of strings to categorize the quotelikes: how many likes the quote got on GoodreadsWe will create a database table to hold this data structure.
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:
Loading code...
On the console, execute the first SQL command of the day. We create a new Schema called api to hold our table and data:
Loading code...
Now we create the table called quotes based on the CSV columns we inspected before:
Loading code...
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:
Loading code...
The
api.quotes_index_seqwas 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:
Loading code...
PostgREST uses PostgreSQL roles as RESTful API users. Let's create a role named operator with all permissions on the data:
Loading code...
In our example,
operatorhas 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.
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.
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:
Loading code...
With the output in hand, create a file named postgrest.conf with the content below:
Loading code...
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:
Loading code...
If everything goes smoothly, the server is running locally on the http://localhost:3000 address.
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:
Loading code...
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:
Loading code...
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:
Loading code...
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:
Loading code...
You can check that the new entry was added correctly by executing:
Loading code...
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:
Loading code...
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!
Loading code...
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:
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.
avn service create --service-type pg --cloud google-europe-west3 --plan hobbyist pg-quotesavn service cli pg-quotesCREATE SCHEMA api;CREATE TABLE api.quotes (
index SERIAL,
quote TEXT,
author VARCHAR(255),
tags TEXT,
likes INTEGER
);ALTER SEQUENCE api.quotes_index_seq RESTART WITH 3000;\COPY api.quotes FROM 'quotes.csv' CSV HEADER;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;avn service get pg-quotes --format '{service_uri}'db-uri = "<the-output-from-the-previous-command-here>"
db-schema = "api"
db-anon-role = "operator"postgrest postgrest.confcurl "http://localhost:3000/quotes"curl "http://localhost:3000/quotes?limit=5"curl "http://localhost:3000/quotes?limit=5&likes=gt.1000&select=index,quote,author"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."
}
'curl "http://localhost:3000/quotes?index=eq.3000"curl "http://localhost:3000/quotes?index=eq.3000" \
-X PATCH -H "Content-Type: application/json" \
-d \
'
{
"tags": "education;inspirational;philosophy;wisdom"
}
'curl -X DELETE "http://localhost:3000/quotes?author=like.*??*"