Jan 20, 2022
Quickly developing REST APIs with PostgREST
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®.
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
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 IDquote
: the quote itselfauthor
: who the quote's authortags
: a list of strings to categorize the quotelikes
: 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
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;
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 );
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;
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;
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;
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}'
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"
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
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"
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"
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"
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." } '
You can check that the new entry was added correctly by executing:
curl "http://localhost:3000/quotes?index=eq.3000"
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" } '
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.*??*"
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:
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
Subscribe to the Aiven newsletter
All things open source, plus our product updates and news in a monthly newsletter.
Related resources
Jul 12, 2022
The case for streaming over batch data continues to get stronger. Read on to see how our experience confirms this.
Aug 11, 2022
PostgreSQL extensions provide great functionality for users, but they are difficult to provide securely. Aiven has an open source solution to this problem!
Jan 5, 2023
It's normally best to use dynamic IP addresses for services, but occasionally a static IP address makes sense. Read on to find out why.