Accessing Postgres via REST using pRest

Elton Minetto - Sep 3 '21 - - Dev Community

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.

Image
CAPM Certification Training Course
Image
The Reason Why Adding A Dripping Coffee Machine To Your Life Can Make All The Different
Image
Bervariasi Rahasia Judi Online Biar Sukses Taruhan
Image
Manual Coding vs Auto-Generated Code: Which One Improves Code Quality?

manualcoding, autogeneratedcode, devtools, codeautomation
Image
11 Creative Ways To Write About Asbestos Lawsuit History
Image
20 Important Questions To To Ask About Repair Bifold Door Bottom Pivot Before Buying It
Image
14 Businesses Doing A Superb Job At Repair Bifold Door Bottom Pivot
Image
c# advanced: Pattern Matching in c#

csharp, patternmatching, cleancode, objectorientedprogramming
Image
Understanding KPIs: Driving Business Performance Through Effective Measurement

kpi, performance
Image
5 Laws Anybody Working In Personal Injury Claims Should Be Aware Of
Image
Solidity: upgradable smart contracts explained.
Image
Ten Ways To Build Your Injury Compensation Empire
Image
Mastering the Top 4 JavaScript Frameworks: My Epic Journey

react, vue, angular, svelte
Image
rahasia kaya main judi slots online
Image
Understanding source generators

dotnet, csharp, roslyn
Image
Kanapy, które zmieniają każdy kąt: Jak wybrać idealną sofę do swojego wnętrza?
Image
Keep calm and learn Linux
Image
tipe model pembayaran di website slots online dapat dipercaya
Image
15 Best Pinterest Boards Of All Time About Bi Fold Door Repairs
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .