Go Shell Makefile
Latest commit 832b854 Mar 21, 2017 @avelino avelino set new release 0.1.6
Permalink
Failed to load latest commit information.
.github Initial contributing guide (#84) Jan 26, 2017
adapters/postgres fix memory leak (#128) Mar 21, 2017
api change Request struct to accept numbers Dec 16, 2016
cmd Added version command line (#125) Mar 11, 2017
config connection timeout configuration (#127) Mar 18, 2017
controllers fix some warnings (#116) Feb 27, 2017
helpers set new release 0.1.6 Mar 21, 2017
middlewares possibility to reorder (#120) Mar 4, 2017
statements [WIP] Change Access Control verification (#80) Jan 24, 2017
testdata BUGFIX where with jsonb bug (#87) Jan 27, 2017
vendor added cors new vendors Feb 17, 2017
.gitignore Make release (#92) Jan 28, 2017
.travis.yml add 1.8 and tip (#118) Feb 27, 2017
CONTRIBUTING.md Initial contributing guide (#84) Jan 26, 2017
CREATING_MODULES.md possibility to reorder (#120) Mar 4, 2017
DCO Initial contributing guide (#84) Jan 26, 2017
Dockerfile change postgres version and use prest binary Dec 7, 2016
LICENSE Initial contributing guide (#84) Jan 26, 2017
MAINTAINERS add felipeweb as a maintainer (#91) Jan 28, 2017
Makefile Added version command line (#125) Mar 11, 2017
README.md added cors support Feb 17, 2017
coverage.txt add test.sh and update travis config file. Dec 8, 2016
doc.go solve merge conflicts Dec 26, 2016
docker-compose.yml fix docker compose environment variable (#99) Feb 8, 2017
main.go Added version command line (#125) Mar 11, 2017
prest.toml json to toml Dec 19, 2016
test.sh Add others comparisons in WHERE (#83) Jan 26, 2017

README.md

pREST

Build Status GoDoc Go Report Card codecov Gitter

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)


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

CORS Support

In the prest.toml you can configurate the CORS allowed origin:

Example:

[cors]
alloworigin = ["http://postgres.rest", "http://foo.com"]