Building REST APIs quickly using PostgREST
--
Welcome. We’re going to implement a sample REST API quickly using PostgREST.
Prerequisites
This is the list of all the prerequisites:
- Docker installed
- Docker compose is installed
- Postman / insomnia or any other API testing tool.
Overview
What is PostgREST?
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. CRUD endpoints (create, read, update and delete) no longer need to be manually created, which reduces the complexity of backend systems.
Setup PostgREST with Docker
There are several ways to install PostgREST (Binary OS source, Docker, and docker-compose, Deploying to Heroku). We’ll use Docker containers. We will create a docker-compose YAML file containing all the instructions to run the services PostgreSQL database, PostgREST, and Swagger OpenAPI.
The local ./scripts folder contains the database initialization scripts that will be executed once the Postgres instance is started.
Let’s take a look at the SQL script scripts/init-db.sql:
The first thing we’ll do is create a schema named “rest” for the database objects which will be exposed to the PostgREST service. We have two tables author and book with a one-to-many mapping between the tables.
The next section consists to create database roles. The anonymous role has permission to access things in the rest schema, and to read rows in the author and book tables. The webuser role has read, write, update, and delete rights to the schema and its tables.
Once you’ve created yml and configured the database script, open your CLI and run the following command:
docker-compose up -d
We start by opening a visual preview of our API in your browser with swagger-ui. http://localhost:8080
It’s now ready to serve requests. Let’s try to retrieve the list of authors and books from the database.
Done. It works fine. 🙂
As mentioned above, the anonymous role does not have permission to write to the database. We cannot add a new author.
JSON Web Token (JWT) Authentication
All PostgREST authorization happens through database roles and permissions. There are three types of roles used by PostgREST, the authenticator, anonymous, and user roles.
We use JSON Web Tokens (JWT) to authenticate API requests.
We’ll start by modifying our docker-compose.yml file and adding a new configuration to allow users to authenticate to our API.
The first thing is the addition of a new extension on our Postgres instance which will allow cryptographically signing passwords on our server. We’ll create JWT tokens in SQL using the pgjwt extension.
Then the second thing is the creation of a new schema in our database which will manage all the authentications. It is composed of a user table that will manage our users. Finally, we add all the functions that allow us to manage user access.
Let’s restart our docker services and then test.
We need to create a user account.
We need now login to have a token. Function paths must be prefixed with /rpc.
We will need a Bearer Authorization with jwt token to make our requests. Let’s try to create a new author.
Congratulations! It’s done.
Conclusion
In this story, we created a Rest API without writing any code or using a web framework with PostgREST. It is really convenient to use to quickly produce simple REST APIs with fewer resources.
The complete source code is available on GitHub.
If you enjoyed this story, please give it a few claps for support.
Happy coding!
