pREST

Serve a RESTful API from any PostgreSQL database

Problem

There is the PostgREST written in haskell, keep a haskell software in production is not easy job, with this need that was born the pREST.

Docker

https://hub.docker.com/r/nuveo/prest/

docker run -e PREST_HTTP_PORT=3000 \
	-e PREST_PG_HOST=127.0.0.1 \
	-e PREST_PG_USER=postgres \
	-e PREST_PG_PASS=pass \
	nuveo/prest:0.1

Tags

• 0.1 (stable)
• lastest (developer)

Install

go get github.com/nuveo/prest

Run

Params:

• PREST_HTTP_PORT (default 3000)
• PREST_PG_HOST (default 127.0.0.1)
• PREST_PG_USER
• PREST_PG_PASS
• PREST_PG_DATABASE
• PREST_PG_PORT (default 5432)
• PREST_JWT_KEY

PREST_PG_USER=postgres PREST_PG_DATABASE=prest PREST_PG_PORT=5432 PREST_HTTP_PORT=3010 prest # Binary installed

Migrations

--url and --path flags are optional if pREST configurations already set

# env var for migrations directory
PREST_MIGRATIONS

# create new migration file in path
prest migrate --url driver://url --path ./migrations create migration_file_xyz

# apply all available migrations
prest migrate --url driver://url --path ./migrations up

# roll back all migrations
prest migrate --url driver://url --path ./migrations down

# roll back the most recently applied migration, then run it again.
prest migrate --url driver://url --path ./migrations redo

# run down and then up command
prest migrate --url driver://url --path ./migrations reset

# show the current migration version
prest migrate --url driver://url --path ./migrations version

# apply the next n migrations
prest migrate --url driver://url --path ./migrations next +1
prest migrate --url driver://url --path ./migrations next +2
prest migrate --url driver://url --path ./migrations next +n

# roll back the previous n migrations
prest migrate --url driver://url --path ./migrations next -1
prest migrate --url driver://url --path ./migrations next -2
prest migrate --url driver://url --path ./migrations next -n

# go to specific migration
prest migrate --url driver://url --path ./migrations goto 1
prest migrate --url driver://url --path ./migrations goto 10
prest migrate --url driver://url --path ./migrations goto v

TOML

Optionally the pREST can be configured by TOML file

• Set PREST_CONF environment variable with file path

migrations = "./migrations"

[http]
port = 6000

[jwt]
key = "mysecretkey"

[pg]
host = "127.0.0.1"
user = "postgres"
pass = "mypass"
port = 5432
database = "prest"

API's

HEADER:

• To start JWT middleware the PREST_JWT_KEY environment variable must be set

Authorization: JWT eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.TJVA95OrM7E2cBab30RMHrHDcEfxjoYZgeFONFh7HgQ

Filter (WHERE)

GET /DATABASE/SCHEMA/TABLE?FIELD=$eq.VALUE

Query Operators:

Name	Description
$eq	Matches values that are equal to a specified value.
$gt	Matches values that are greater than a specified value.
$gte	Matches values that are greater than or equal to a specified value.
$lt	Matches values that are less than a specified value.
$lte	Matches values that are less than or equal to a specified value.
$ne	Matches all values that are not equal to a specified value.
$in	Matches any of the values specified in an array.
$nin	Matches none of the values specified in an array.
$null	Matches if field is null
$notnull	Matches if field is not null

Filter (WHERE) with JSONb field

http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?FIELD->>JSONFIELD:jsonb=VALUE (filter)

Select - GET

http://127.0.0.1:8000/databases (show all databases)
http://127.0.0.1:8000/databases?_count=* (count all databases)
http://127.0.0.1:8000/schemas (show all schemas)
http://127.0.0.1:8000/schemas?_count=* (count all schemas)
http://127.0.0.1:8000/tables (show all tables)
http://127.0.0.1:8000/DATABASE/SCHEMA (show all tables, find by schema)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE (show all rows, find by database and table)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?_select=column (select statement by columns)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?_select=* (select all from TABLE)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?_count=* (use count function)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?_count=column (use count function)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?_page=2&_page_size=10 (pagination, page_size 10 by default)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?FIELD=VALUE (filter)
http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?_renderer=xml (JSON by default)


Select operations over a VIEW
http://127.0.0.1:8000/DATABASE/SCHEMA/VIEW?_select=column (select statement by columns in VIEW)
http://127.0.0.1:8000/DATABASE/SCHEMA/VIEW?_select=* (select all from VIEW)
http://127.0.0.1:8000/DATABASE/SCHEMA/VIEW?_count=* (use count function)
http://127.0.0.1:8000/DATABASE/SCHEMA/VIEW?_count=column (use count function)
http://127.0.0.1:8000/DATABASE/SCHEMA/VIEW?_page=2&_page_size=10 (pagination, page_size 10 by default)
http://127.0.0.1:8000/DATABASE/SCHEMA/VIEW?FIELD=VALUE (filter)

Insert - POST

http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE

JSON DATA:

{
    "data": {
        "FIELD1": "string value",
        "FIELD2": 1234567890
    }
}

Update - PATCH/PUT

Using query string to make filter (WHERE), example:

http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?FIELD1=xyz

JSON DATA:

{
    "data": {
        "FIELD1": "string value",
        "FIELD2": 1234567890
    }
}

Delete - DELETE

Using query string to make filter (WHERE), example:

http://127.0.0.1:8000/DATABASE/SCHEMA/TABLE?FIELD1=xyz

JOIN

Using query string to JOIN tables, example:

/DATABASE/SCHEMA/TABLE?_join=inner:users:friends.userid:$eq:users.id

Parameters:

1. Join type
2. Table
3. Table field 1
4. Operator (=, <, >, <=, >=)
5. Table field 2

Query Operators

Name	Description
$eq	Matches values that are equal to a specified value.
$gt	Matches values that are greater than a specified value.
$gte	Matches values that are greater than or equal to a specified value.
$lt	Matches values that are less than a specified value.
$lte	Matches values that are less than or equal to a specified value.
$ne	Matches all values that are not equal to a specified value.
$in	Matches any of the values specified in an array.
$nin	Matches none of the values specified in an array.

ORDER BY

Using ORDER BY in queries you must pass in GET request the attribute _order with fieldname(s) as value. For DESC order, use the prefix -. For multiple orders, the fields are separated by comma.

Examples:

ASC

GET /DATABASE/SCHEMA/TABLE/?_order=fieldname

DESC

GET /DATABASE/SCHEMA/TABLE/?_order=-fieldname

Multiple Orders

GET /DATABASE/SCHEMA/TABLE/?_order=fieldname01,-fieldname02,fieldname03

Executing SQL scripts

If need perform an advanced SQL, you can write some scripts SQL and access them by REST. These scripts are templates where you can pass by URL, values to them.

awesome_folder/example_of_powerful.read.sql:

SELECT * FROM table WHERE name = "{{.field1}}" OR name = "{{.field2}}";

Get result:

GET /_QUERIES/awesome_folder/example_of_powerful?field1=foo&field2=bar

To activate it, you need configure a location to scripts in your prest.toml like:

[queries]
location = /path/to/queries/

Scripts templates rules

In your scripts, the fields to replace have to look like: field1 or field2 are examples

SELECT * FROM table WHERE name = "{{.field1}}" OR name = "{{.field2}}";

Script file must have a suffix based on http verb:

HTTP Verb	Suffix
GET	.read.sql
POST	.write.sql
PUT, PATCH	.update.sql
DELETE	.delete.sql

In queries.location, you need given a folder to your scripts:

queries/
└── foo
    └── some_get.read.sql
    └── some_create.write.sql
    └── some_update.update.sql
    └── some_delete.delete.sql
└── bar
    └── some_get.read.sql
    └── some_create.write.sql
    └── some_update.update.sql
    └── some_delete.delete.sql

URL's to foo folder:

GET    /_QUERIES/foo/some_get?field1=bar
POST   /_QUERIES/foo/some_create?field1=bar
PUT    /_QUERIES/foo/some_update?field1=bar
PATCH  /_QUERIES/foo/some_update?field1=bar
DELETE /_QUERIES/foo/some_delete?field1=bar


URL's to bar folder:

GET    /_QUERIES/bar/some_get?field1=foo
POST   /_QUERIES/bar/some_create?field1=foo
PUT    /_QUERIES/bar/some_update?field1=foo
PATCH  /_QUERIES/bar/some_update?field1=foo
DELETE /_QUERIES/bar/some_delete?field1=foo

Permissions

Restrict mode

In the prest.toml you can configure read/write/delete permissions of each table.

[access]
restrict = true  # can access only the tables listed below

restrict = false: (default) the prest will serve in publish mode. You can write/read/delete everydata without configure permissions.

restruct = true: you need configure the permissions of all tables.

Table permissions

Example:

[[access.tables]]
name = "test"
permissions = ["read", "write", "delete"]
fields = ["id", "name"]

attribute	description
table	Table name
permissions	Table permissions. Options: read, write and delete
fields	Fields permitted for select

Configuration example: prest.toml