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
Reinforcement Learning with Human Feedback (RLHF) for Large Language Models (LLMs)

techinnovation, rlhf, humanfeedback, deeplearning
Image
peranan modal dalam judi slots online
Image
5 Laws That Can Help The Small Sectional With Chaise Industry
Image
/#Love marriage spell Vashikaran astrologer +91-9855686755#/

webdev, javascript, beginners, programming
Image
The Most Hilarious Complaints We've Been Hearing About Small Sectional With Chaise
Image
Are You Responsible For An Chesterfield Corner Leather Sofa Budget? 12 Best Ways To Spend Your Money
Image
Why We Love Bifold Doors Repair (And You Should, Too!)
Image
There Is No Doubt That You Require Chesterfields Sofas
Image
Correlated Subquery in SQL
Image
Exploring Swift for iOS Development

programming, swift, blogging
Image
11 Ways To Completely Revamp Your Bi Folding Door Repair
Image
Sectional With Chaise Explained In Fewer Than 140 Characters
Image
Exploring Swift for iOS Development

programming, swift, blogging
Image
Kanapy Marzeń: Jak Wybrać Idealny Model do Twojego Wnętrza?
Image
teknik menang judi slots online untuk yang baru memulai
Image
Hi, i m new here
Image
persyaratan dalam download terapan slots online pkv game
Image
17 Signs That You Work With Electric Patio Heater
Image
Install of Packages with NPM-Store Using AWS CodeArtifact

aws, awscodeartifact, ec2, iam
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player