Acessando o Postgres via REST usando o pRest

31/08/2021 - Tempo estimado de leitura: 5 minutos - Traduções: En

Neste post vou falar sobre uma ferramenta Open Source bem útil chamada pRest.

Com o pRest é possível criar uma API RESTFul para acessar o conteúdo de um banco de dados Postgres de uma maneira simples e rápida. O projeto, escrito em Go e com grande participação de desenvolvedores brasileiros, pode ser encontrado no seu site oficial, bem como no Github.

Segundo a documentação, existem diversas formas de se instalar o pRest. Para fazer os testes deste post eu optei por usar a opção de instalação via Docker. Criei um arquivo docker-compose.yml com uma imagem do Postgres, para facilitar os testes, e a configuração do próprio pRest:

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"

Na documentação é possível ver as opções disponíveis para customização da instalação do pRest.

Após executar o comando docker-compose up -d o próximo passo foi criar as tabelas básicas que o pRest necessita para realizar o controle de acesso a API. Para isso executei os comandos:

docker-compose exec prest prestd migrate up auth

Com esse comando o pRest vai criar a tabela prest_users, que vamos alimentar com os próximos comandos:

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'))"

O primeiro comando cria a extensão do Postgres para gerar hashes SHA1, que é usada para gerar a senha que vai ser usada pelo usuário.

Com o usuário e senha criados, o próximo passo foi criar o token JWT que precisa ser fornecido em todas as próximas requisições:

curl -i -X POST http://127.0.0.1:3000/auth -H "Content-Type: application/json" -d '{"username": "prest", "password": "prest"}'

O resultado é um JSON com os dados do usuário e o token:

{
  "user_info": {
    "id": 1,
    "name": "pREST Full Name",
    "username": "prest",
    "metadata": null
  },
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk"
}

Para realizar os testes eu criei uma estrutura bem simples de tabelas, conforme o diagrama:

Os comandos que criam as tabelas são:

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))"

Com as tabelas criadas, o próximo passo foi realizar as operações sobre elas, usando a API.

OBS: para todos os exemplos a seguir estou usando o token gerado acima.

Inserindo registros na tabela books

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}'

E o resultado foi o JSON do registro criado:

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

Para os próximos exemplos eu inseri um lote de livros, usando um 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

Atualizando um registro na tabela books

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"}'

Removendo um registro da tabela books

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"

Fazendo consultas na tabela books

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'

O resultado é um array JSON:

[
  {
    "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'

Inserindo um registro na tabela users

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"}'

Inserindo um registro na tabela books_users

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}'

Fazendo inner Join entre as tabelas

Para executar a consulta:

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

A API correspondente seria:

curl 'http://127.0.0.1:3000/prest/public/books_users?_join=inner:users:books_users.user_id:$eq:users.id&_join=inner📚books_users.book_id:$eq:books.id&user_id=1' -H 'Accept: application/json' -H 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySW5mbyI6eyJpZCI6MSwibmFtZSI6InBSRVNUIEZ1bGwgTmFtZSIsInVzZXJuYW1lIjoicHJlc3QiLCJtZXRhZGF0YSI6bnVsbH0sImV4cCI6MTYzMDQ4MzM4MywianRpIjoiMSIsImlhdCI6MTYzMDQ2MTc4MywiaXNzIjoiMSJ9.KHBaxxSUf_mjlj3EUD1H9oegLXLYWmLlo2LYPbwTCmk'

Mais exemplos da sintaxe de consultas pode ser visto na documentação. Ela é bem poderosa e de fácil entendimento.

SQL Queries

Outro recurso interessante é a possibilidade de executarmos queries SQL previamente salvas, que fazem uso do poderio de templates da linguagem Go. Para isso vamos criar o diretório queries, conforme configurado na variável de ambiente PREST_QUERIES_LOCATION do arquivo docker-compose.yml. Vamos também criar um subdiretório, para deixar as consultas mais organizadas:

mkdir -p queries/books

Dentro desta estrutura de diretórios criei o arquivo: by-user.read.sql, com o conteúdo:

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}}

E agora podemos executar a consulta, enviando o parâmetro da query através da 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'

Este recurso permite a criação de consultas mais complexas e mais exemplos podem ser vistos na documentação.

Além destes exemplos que mostrei neste post, a ferramenta tem outros recursos interessantes como migrações, controle de permissões e a possibilidade de criação de extensões através de middlewares customizados.

O pRest é uma ferramenta bem poderosa e que pode ser de grande utilidade para a criação de aplicações que fazem uso intensivo de bancos de dados, dando agilidade para as equipes.