TheLazy.Dev
Portfolio GitHub

Blog

Accessing Postgres via REST using pRest

eminetto

Elton Minetto

Posted on September 3, 2021

Accessing Postgres via REST using pRest

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'))"
Enter fullscreen mode Exit fullscreen mode

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"}'
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

To perform the tests, I created some tables, as shown in the diagram:

prest_demo

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))"
Enter fullscreen mode Exit fullscreen mode

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}'
Enter fullscreen mode Exit fullscreen mode

And the result was the JSON of the created record:

{"id":158,"title":"Dune","author":"Frank Herbert","pages":680,"quantity":100}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"}'
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

The result is a JSON array:

[
  {
    "id": 158,
    "title": "Dune",
    "author": "Frank Herbert",
    "pages": 680,
    "quantity": 100
  }
]
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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"}'
Enter fullscreen mode Exit fullscreen mode

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}'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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}}
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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.

💖 💪 🙅 🚩
eminetto
Elton Minetto

Posted on September 3, 2021

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

If you're a beginner, definitely check this open source guide. I've explained almost everything you need to know.
undefined If you're a beginner, definitely check this open source guide. I've explained almost everything you need to know.

November 28, 2024

Exploring Typesafe design tokens in Tailwind 4
tailwindcss Exploring Typesafe design tokens in Tailwind 4

November 29, 2024

Where GitOps Meets ClickOps
devops Where GitOps Meets ClickOps

November 29, 2024

The best way to get better at writing code is...
development The best way to get better at writing code is...

November 28, 2024

How to Use KitOps with MLflow
beginners How to Use KitOps with MLflow

November 29, 2024