In this post, I'm going to talk about a handy Open Source tool called pRest.
With pRest, it is possible to create a RESTFul API to access the contents of a Postgres database in a fast and straightforward way. The project, written in Go, can be found on its official website and Github.
According to the documentation, there are several ways to install pRest. To write this post, I chose to use the Docker installation option. To do this, I created a docker-compose.yml file with a Postgres image to facilitate testing and the configuration of pRest itself:
version: "3"
services:
postgres:
image: postgres
volumes:
- "./data/postgres:/var/lib/postgresql/data"
environment:
- POSTGRES_USER=prest
- POSTGRES_DB=prest
- POSTGRES_PASSWORD=prest
ports:
- "5432:5432"
healthcheck:
test: ["CMD-SHELL", "pg_isready", "-U", "prest"]
interval: 30s
retries: 3
prest:
image: prest/prest:v1
links:
- "postgres:postgres"
environment:
- PREST_DEBUG=true
- PREST_AUTH_ENABLED=true
- PREST_PG_HOST=postgres
- PREST_PG_USER=prest
- PREST_PG_PASS=prest
- PREST_PG_DATABASE=prest
- PREST_PG_PORT=5432
- PREST_SSL_MODE=disable
- PREST_AUTH_ENCRYPT=SHA1
- PREST_QUERIES_LOCATION=/queries
volumes:
- "./queries:/queries"
depends_on:
postgres:
condition: service_healthy
ports:
- "3000:3000"
In the documentation, you can see the options available for customizing the pRest installation.
After running the command docker-compose up -d, the next step was to create the essential tables that pRest needs to perform API access control. For this, I executed the commands:
docker-compose exec prest prestd migrate up auth
With this, pRest will create the prest_users table, which we will feed with the following commands:
docker-compose exec postgres psql -d prest -U prest -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"
docker-compose exec postgres psql -d prest -U prest -c "INSERT INTO prest_users (name, username, password) VALUES ('pREST Full Name', 'prest', ENCODE(DIGEST('prest','sha1'),'hex'))"
With the username and password created, the next step was to generate the JWT token we will use in the requests. To do this, we can use:
curl -i -X POST http://127.0.0.1:3000/auth -H "Content-Type: application/json" -d '{"username": "prest", "password": "prest"}'
The result is a JSON with the user data and token:
{
"user_info": {
"id": 1,
"name": "pREST Full Name",
"username": "prest",
"metadata": null
},
"token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk"
}
To perform the tests, I created some tables, as shown in the diagram:
The commands that create the tables are:
docker-compose exec postgres psql -d prest -U prest -c "CREATE TABLE users (id serial PRIMARY KEY,email VARCHAR ( 50 ) UNIQUE NOT NULL,first_name VARCHAR ( 255 ),last_name VARCHAR ( 255 ))"
docker-compose exec postgres psql -d prest -U prest -c "create table books (id serial PRIMARY KEY,title varchar(255),author varchar(255), pages integer,quantity integer)"
docker-compose exec postgres psql -d prest -U prest -c "create table books_users (user_id INT NOT NULL,book_id INT NOT NULL, created_at TIMESTAMP, PRIMARY KEY (user_id,book_id),FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (book_id) REFERENCES books (id))"
With the tables created, the next step was to perform operations on them, using the API.
NOTE: for all the following examples, I'm using the token generated above.
Inserting records into the books table
curl -i -X POST http://127.0.0.1:3000/prest/public/books -H "Accept: application/json" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk" -d '{"title": "Dune", "author": "Frank Herbert", "pages":680, "quantity":100}'
And the result was the JSON of the created record:
{"id":158,"title":"Dune","author":"Frank Herbert","pages":680,"quantity":100}
For the following examples, I've inserted a batch of books using a shell script:
#!/bin/bash
for i in {1..50}
do
curl -i -X POST http://127.0.0.1:3000/prest/public/books -H "Accept: application/json" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDI2MjU0OSwianRpIjoiMSIsImlhdCI6MTYzMDI0MDk0OSwiaXNzIjoiMSJ9.C_j73eaMlNzOy_jKXBAXr6evmhcqKVlPPotwq5nsK9M" -d "{\"title\": \"Book title $i\", \"author\": \"Author $i\", \"pages\":666, \"quantity\":$i}"
done
Updating a record in the books table
curl -i -X PUT http://127.0.0.1:3000/prest/public/books?id=103 -H "Accept: application/json" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDI2MjU0OSwianRpIjoiMSIsImlhdCI6MTYzMDI0MDk0OSwiaXNzIjoiMSJ9.C_j73eaMlNzOy_jKXBAXr6evmhcqKVlPPotwq5nsK9M" -d '{"title": "updated title", "author": "updated author"}'
Removing a record from the books table
curl -i -X DELETE http://127.0.0.1:3000/prest/public/books?id=104 -H "Accept: application/json" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk"
Making queries on the books table
select * from books where title like %title%
curl "http://127.0.0.1:3000/prest/public/books?title:tsquery=dune" -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'
The result is a JSON array:
[
{
"id": 158,
"title": "Dune",
"author": "Frank Herbert",
"pages": 680,
"quantity": 100
}
]
select * from books where title=?
curl 'http://127.0.0.1:3000/prest/public/books?title=$eq.Dune' -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'
select * from books where quantity > 1
curl 'http://127.0.0.1:3000/prest/public/books?quantity=$gte.10' -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'
select * from books where quantity > 1 order by title desc limit 5
curl 'http://127.0.0.1:3000/prest/public/books?quantity=$gte.10&_page_size=5&_page=1&&_order=-title' -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'
Inserting a record into the users table
curl -i -X POST http://127.0.0.1:3000/prest/public/users -H "Accept: application/json" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk" -d '{"email": "elton@minetto.dev", "first_name":"Elton", "last_name":"Minetto"}'
Inserting a record into the books_users table
curl -i -X POST http://127.0.0.1:3000/prest/public/books_users -H "Accept: application/json" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk" -d '{"user_id": 1, "book_id":158}'
Inner Join between tables
To run the query:
select books.*
from books_users
inner join books on books_users.book_id = books.id
inner join users on books_users.user_id = users.id
where users.id = 1
The corresponding API would be:
curl 'http://127.0.0.1:3000/prest/public/books_users?_join=inner:users:books_users.user_id:$eq:users.id&_join=inner:books:books_users.book_id:$eq:books.id&user_id=1' -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'
To see more examples of query syntax, access the documentation. It is powerful and easy to understand.
SQL Queries
Another exciting feature is the possibility of executing saved SQL queries, which make use of the Go's template library. For this, we will create the queries directory, as configured in the PREST_QUERIES_LOCATION environment variable of the docker-compose file.yml. We will also create a subdirectory to make the queries more organized:
mkdir -p queries/books
Within this directory structure, I created the file: by-user.read.sql with the content:
select books.*
from books_users
inner join books on books_users.book_id = books.id
inner join users on books_users.user_id = users.id
where users.id = {{.user_id}}
And now we can run the query, sending the query parameter via the URL:
curl 'http://127.0.0.1:3000/_QUERIES/books/by-user?user_id=1' -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'
This feature allows us to create complex queries. To see more examples of query syntax, access the documentation.
In addition to these examples I showed in this post, the tool has other exciting features such as migrations, permissions control, and the possibility of creating extensions through custom middlewares.
pRest is a powerful tool that can be very useful for creating applications that make intensive use of databases, giving agility to the teams.
Top comments (0)