prest/prest

PostgreSQL ➕ REST, low-code, simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

pRESTd

pREST (PostgreSQL REST), is a simple production-ready API, that delivers a instant, realtime, and high-performance application on top of your existing or new Postgres database.

PostgreSQL version 9.5 or higher

Contributor License Agreement -

Problems we solve

The pREST project is the API that addresses the need for fast and efficient solution in building RESTful APIs on PostgreSQL databases. It simplifies API development by offering:

1. A lightweight server with easy configuration;
2. Direct SQL queries with templating in customizable URLs;
3. Optimizations for high performance;
4. Enhanced developer productivity;
5. Authentication and authorization features;
6. Pluggable custom routes and middlewares.

Overall, pREST simplifies the process of creating secure and performant RESTful APIs on top of your new or old PostgreSQL database.

Read more.

Why we built pREST

When we built pREST, we originally intended to contribute and build with the PostgREST project, although it took a lot of work as the project is in Haskell. At the time, we did not have anything similar or intended to keep working with that tech stack. We've been building production-ready Go applications for a long time, so building a similar project with Golang as its core was natural.

Additionally, as Go has taken a huge role in many other vital projects such as Kubernetes and Docker, and we've been able to use the pREST project in many different companies with success over the years, it has shown to be an excellent decision.

1-Click Deploy

Heroku

Deploy to Heroku and instantly get a realtime RESTFul API backed by Heroku Postgres:

Documentation

Visit https://docs.prestd.com/

prest's Issues

Auth via JWT

By default must have authentication via JWT, via config is possible disable

Recommend using (we can use another, only justify why don't use this):
https://github.com/dgrijalva/jwt-go

toml syntax check

Running prest with some typo or error in config file (prest.toml) is just ignoring. Is not showing warnings or errors

select with greater than, less than, different, etc.

Queries with where greater than, less than, different, etc.

Add some documentation on how to use

e.g.:

Connect to Postgres, do:

CREATE TABLE prest.public.test2
( 
  intField integer,
  strField VARCHAR(40)
);

then:
GET: http://localhost:3000/prest/public/test2
get empty results

then:
POST http://localhost:3000/prest/public/test2

{
    "data": {
        "strField": "string value",
        "intField": 1234567890
    }
}

then:
GET: http://localhost:3000/prest/public/test2
get expected results

Remove _VIEW Before database name in the query.

The _VIEW tag before the name of the database in the query is not required.
The ideal is to have only one endpoint for both tables and views.

Select fields (I don't need all the fields)

?_select=field1,field2

Generate: SELECT field1, field2 FROM ...

TOML config file

Read TOML file to configure pREST

Environment var: PREST_CONF (path receive)
If the environment variable has within the TOML: environment variable has more relevance

https://github.com/BurntSushi/toml (struct tag support)

disable endpoint cache

Depends #112

configuration (TOML/ENV):

[cache]
time = 10M
store = bolt
sotrePath = "./"

    [[cache.endpoints]]
    endpoint = "/databases"

    [[cache.endpoints]]
    endpoint = "/schemas"

    [[cache.endpoints]]
    endpoint = "/databaseName/schemaName/tableName"
    time = 2M

Wrong renderer

• pREST version (or commit ref): a50fcf1
• pREST endpoint: prest/public/test4?_renderer=xml
• PostgreSQL version: 9.6
• Operating system: Linux
• Go version: 1.7
• Log gist: nil

Description

Expected xml as return but json is returned.

Providing initial data for models

It’s sometimes useful to pre-populate your database with hard-coded data when you’re first setting up an app. You can provide initial data with fixtures or migrations.

Supports:

• JSON
• YAML

Lib: https://github.com/go-testfixtures/testfixtures (Test to see if it works well)

Process view

http://127.0.0.1:8000/_VIEW/SCHEMA/VIEW?parameters...

The VIEW in PostgreSQL is a query processed, where we use as a table, see an example below:

SELECT field1, field2 FROM MY_VIEW;

The VIEW must receive all parameters like SELECT (query)

count databases

• pREST version (or commit ref): a50fcf1
• pREST endpoint: /databases?_count=*
• PostgreSQL version: 9.6
• Operating system: Linux
• Go version: 1.7
• Log gist: https://gist.github.com/cassiobotaro/846d0183c9d33bda7124d0cebf250f4c

Description

Expected status code 200 but 500 is returned.

prest client

Create a client API To facilitate the use and standardization of calls

examples:
prestc.Query(
or
prest.client.Query(

Executing sql query (like/alternative database views)

maintain views within the database is always tricky for developers, thinking about this problem to develop an alternative SQL execution.

Folder that will be read:

./queries/foldername/script.read.sql

URI:

GET /_QUERIES/foldername/script/

Extension which will be executed on each http method:

• GET *.read.sql
• POST *.write.sql
• PATCH/PUT *.update.sql
• DELETE *.delete.sql

Access permissions:

[access]
restrict = true

    [[access.queries]]
    name = "foldername/script"
    permissions = ["read"]

Template

Vars:

?FIELD1=abc&FIELD2=123

Template:

SELECT * FROM TABLE WHERE FIELD1="{{.FIELD1}}" OR FIELD2>={{.FIELD2}}

Render:

SELECT * FROM TABLE WHERE FIELD1="abc" OR FIELD2>=123

Possibility of reorder middlewares when execute pREST as a module

This will give us more flexibility when add a custom middleware

Transaction support

It would be great to have transaction support.

My suggestion is to make a call to pREST requesting the start of a transaction and receive an ID. Then pass that ID on the next requisitions and finally finish with a commit or rollback.

The idea of using an ID is to solve the problem of stateless calls.

Support Update

Support update data using PATCH and PUT http verb

dynamic modules

A way to create and import custom modules inside prest and register endpoint's for it.

like example:
Path: prest/modules/module01.go

And this can register URLs that will call the functions inside the module.

Any suggestions?

Renderer type, xml support

By default is JSON, we must support the xml (the purpose is to be a REST).

Query string:

_renderer=xml

Queries with dynamic params

In the template/files queries https://github.com/nuveo/prest#scripts-templates-rules

Currently are requiring the exactly number of params. But should be possible to use optional params.

Error: sql: statement expects 4 inputs; got 5

Optional param example:

SELECT * FROM table01 {{if where}} WHERE field = {{.wherevalue}} {{end}}

JSONb filter support

Query string:

?FILTER:jsonb=VALUE

:jsonb Identifies which structure the field this

Not run test on gocover

Check because not running the test at gocover.io

http://gocover.io/github.com/nuveo/prest

Crashing when postgresql closes connection or timeout

When postgresql stop with timeout or error like DatabaseError: server closed the connection unexpectedly pREST is crashing too.

Log:

runtime stack:
runtime.throw(0x948e50, 0x16)
	/usr/local/go/src/runtime/panic.go:566 +0x95
runtime.sysMap(0xc450af0000, 0x100000, 0x0, 0xbe2f18)
	/usr/local/go/src/runtime/mem_linux.go:219 +0x1d0
runtime.(*mheap).sysAlloc(0xbc9bc0, 0x100000, 0x0)
	/usr/local/go/src/runtime/malloc.go:407 +0x37a
runtime.(*mheap).grow(0xbc9bc0, 0x1, 0x0)
	/usr/local/go/src/runtime/mheap.go:726 +0x62
runtime.(*mheap).allocSpanLocked(0xbc9bc0, 0x1, 0x0)
	/usr/local/go/src/runtime/mheap.go:630 +0x4f2
runtime.(*mheap).alloc_m(0xbc9bc0, 0x1, 0x7f000000001f, 0x410cdb)
	/usr/local/go/src/runtime/mheap.go:515 +0xe0
runtime.(*mheap).alloc.func1()
	/usr/local/go/src/runtime/mheap.go:579 +0x4b
runtime.systemstack(0x7fe643234888)
	/usr/local/go/src/runtime/asm_amd64.s:314 +0xab
runtime.(*mheap).alloc(0xbc9bc0, 0x1, 0x1000000001f, 0x40e7b9)
	/usr/local/go/src/runtime/mheap.go:580 +0xa0
runtime.(*mcentral).grow(0xbcbc20, 0x0)
	/usr/local/go/src/runtime/mcentral.go:210 +0x94
runtime.(*mcentral).cacheSpan(0xbcbc20, 0xc420017228)
	/usr/local/go/src/runtime/mcentral.go:91 +0xfa
runtime.(*mcache).refill(0x7fe6432c1000, 0xc40000001f, 0xc42006ae00)
	/usr/local/go/src/runtime/mcache.go:121 +0xae
runtime.(*mcache).nextFree.func1()
	/usr/local/go/src/runtime/malloc.go:505 +0x33
runtime.systemstack(0xc420016000)
	/usr/local/go/src/runtime/asm_amd64.s:298 +0x79
runtime.mstart()
	/usr/local/go/src/runtime/proc.go:1079

goroutine 184133 [IO wait]:
net.runtime_pollWait(0x7fe6426fe738, 0x72, 0xc6)
	/usr/local/go/src/runtime/netpoll.go:160 +0x59
net.(*pollDesc).wait(0xc4344d1bf0, 0x72, 0xc4205b4a00, 0xc42000c160)
	/usr/local/go/src/net/fd_poll_runtime.go:73 +0x38
net.(*pollDesc).waitRead(0xc4344d1bf0, 0xb99d40, 0xc42000c160)
	/usr/local/go/src/net/fd_poll_runtime.go:78 +0x34
net.(*netFD).Read(0xc4344d1b90, 0xc44a826000, 0x1000, 0x1000, 0x0, 0xb99d40, 0xc42000c160)
	/usr/local/go/src/net/fd_unix.go:243 +0x1a1
net.(*conn).Read(0xc449dcaff8, 0xc44a826000, 0x1000, 0x1000, 0x0, 0x0, 0x0)
	/usr/local/go/src/net/net.go:173 +0x70
bufio.(*Reader).fill(0xc449239c20)
	/usr/local/go/src/bufio/bufio.go:97 +0x10c
bufio.(*Reader).Read(0xc449239c20, 0xc44a935360, 0x5, 0x200, 0x2, 0x0, 0x0)
	/usr/local/go/src/bufio/bufio.go:209 +0x1bc
io.ReadAtLeast(0xb96ac0, 0xc449239c20, 0xc44a935360, 0x5, 0x200, 0x5, 0xc4206a0500, 0x20, 0x8)
	/usr/local/go/src/io/io.go:307 +0xa4
io.ReadFull(0xb96ac0, 0xc449239c20, 0xc44a935360, 0x5, 0x200, 0x410e0f, 0xc4205b4c50, 0xc4201c5400)
	/usr/local/go/src/io/io.go:325 +0x58
shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq.(*conn).recvMessage(0xc44a935340, 0xc4206a0500, 0x88fe40, 0x1, 0xbe0ee8)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq/conn.go:957 +0x117
shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq.(*conn).recv1Buf(0xc44a935340, 0xc4206a0500, 0xc4205b4cd8)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq/conn.go:1007 +0x39
shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq.(*conn).recv1(0xc44a935340, 0x630dca, 0xc449dcaff8)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq/conn.go:1028 +0x87
shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq.(*conn).readParseResponse(0xc44a935340)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq/conn.go:1574 +0x2f
shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq.(*conn).prepareTo(0xc44a935340, 0xc450ad25f0, 0x4a, 0xc450ac5e10, 0x5, 0x4a)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq/conn.go:784 +0x63c
shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq.(*conn).Prepare(0xc44a935340, 0xc450ad25f0, 0x4a, 0x0, 0x0, 0x0, 0x0)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/lib/pq/conn.go:804 +0x11f
database/sql.(*driverConn).prepareLocked(0xc4481843f0, 0xc450ad25f0, 0x4a, 0x0, 0x0, 0x411904, 0x7fe6413628e2)
	/usr/local/go/src/database/sql/sql.go:301 +0x55
database/sql.(*DB).prepare(0xc42010bd90, 0xc450ad25f0, 0x4a, 0xc4205b4f01, 0xc4201c5400, 0x450560, 0xc4201c5400)
	/usr/local/go/src/database/sql/sql.go:987 +0xa5
database/sql.(*DB).Prepare(0xc42010bd90, 0xc450ad25f0, 0x4a, 0xc4205b5080, 0x410ead, 0xc4507d5b88)
	/usr/local/go/src/database/sql/sql.go:964 +0x60
shelfgit.com/filmow/filmow-api/vendor/github.com/nuveo/prest/adapters/postgres.Query(0xc450ad25f0, 0x4a, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
	/go/src/shelfgit.com/filmow/filmow-api/vendor/github.com/nuveo/prest/adapters/postgres/postgres.go:260 +0xc1

Accept is null and not null operators in querystring

Access control

I suggest implementing a way to identify the connection and validate the user/client.
So we can allow or deny access to tables, views, etc.

Also que can define a way to set allowed access to features such as a table being "insert only", or even a field being "read-only".

RPC support

Create Code Conventions markdown

Migrations - manage data structure within the database

Migrations are pREST's way of propagating changes you make to your schema (adding a field, deleting a schema, etc.) into your database schema. They’re designed to be mostly automatic, but you’ll need to know when to make migrations, when to run them, and the common problems you might run into.

Vars:

PREST_MIGRATIONS

Commands:

# create new migration file in path
prest migrate create migration_file_xyz

# apply all available migrations
prest migrate up

# roll back all migrations
prest migrate down

# roll back the most recently applied migration, then run it again.
prest migrate redo

# show the current migration version
prest migrate version

Others way to search in JSONB fields

Today, pREST only accepts searches in key/value. It would be good to accept search in array.

Possibility of generating cache

In some applications (or endpoint, depends on the use) cache is required to not be accessing the database (PostgreSQL) in every request.

by default it should be a key-value bank embedded inside prestd (no external dependencies) and support other external key-value banks with adapter architecture

Store key/value:

• key: endpoint + query string (GET)
• value: return body

configuration (TOML/ENV):

[cache]
time = 10M
store = bolt
sotrePath = "./"

sotrePath: native memory storage support (no path declaration required, :memory:)

Stores (adapters):

• buntdb - we are not thinking about performance, but it should be faster than running a query in postgresql, (bolt deprecated)
• badger
• redis
• memcached

Times:

• S or s: second
• M or m: minute
• H or h: hour
• D or d: day

If there is no cache in the configuration register (access the Bank in real time).

OR filter (where)

Allow calling internal PostgreSQL functions

A useful feature would be able to call functions of Postgresql from pREST

example:

nextval('sequence_name'::regclass)

Perhaps the same effect I want can be resolved by creating a view that calls the function.

Problems in the where

I am having problems when using jsonb and normal fields in the same where.

An example of what I'm trying to do:

curl -i -X GET "http://localhost:3000/database/public/table?field_json->>code:jsonb="test"&value=10"

The behavior is erratic, sometimes it works but most of the time it returns an error.
Looks pretty much like the problems we were having to keep the map order in Go.

This is the error that prest is returning:

2017/01/16 16:26:41 pq: operator does not exist: numeric = text
[negroni] Completed 500 Internal Server Error in 1.181757ms

pool database connection

Any request it receives in the API it opens a connection to the database and how it ends to process closes.
Implement a connection pool, we need some configuration variables.

DB.SetMaxIdleConns(n int)
DB.SetMaxOpenConns(n int)

more infor: http://jmoiron.github.io/sqlx/#connectionPool

Support Insert

Support insert data using POST http verb

ORDER BY

ASC:
?_order=datetime

DESC:
?_order=-datetime

Multiple orders
?_order=id,-datetime

How about this syntax? Any suggestions?

SQL injection is possible

Using fmt.Sprintf from a user input data:
https://github.com/nuveo/prest/blob/master/adapters/postgres/postgres.go#L58
and then calling postgres.Query:
https://github.com/nuveo/prest/blob/master/controllers/databases.go#L25
is a very bad practice that allows to user make SQL injections.

Invalid insert query if table not has column id

If table not has column id pREST generate an invalid query because query write RETURNING id

Next steps issues

Can you create issues with features/TODO you want to add to the project?

count schema

• pREST version (or commit ref): a50fcf1
• pREST endpoint: /schemas?_count=*
• PostgreSQL version: 9.6
• Operating system: Linux
• Go version: 1.7
• Log gist: nil

Description

When i count the number of schemas, the correct would not be the sum of the aggregate of values?
...

"invalid where clause" when using file with extension in the where

• pREST version (or commit ref): 0a1c7bc
• pREST endpoint: /database/public/tablename
• PostgreSQL version: 9.6.1.0
• Operating system: macOS Sierra
• Go version: 1.7
• Log gist:

Description

When I search for filenames that are registered in a field of a table the pREST gets confused because of the dot and the extension in the filename and return "invalid where clause".

Example of how to reproduce:

curl -i -X GET http://localhost:3000/database/public/tablename?local_file=teste.txt

I believe the error is happening because of the way the new where operators are coded.
A dot can occur in any string so this seems to be a bug.

JOIN feature

It would be interesting to have a JOIN (inner, left, etc) feature?

Create a mock API

The idea is to provide an easy way for pREST users to mock their API

Custom routes stopped working

The custom routes stopped working when restrict = True

It's returning the message: required authorization to table

This middleware middlewares.AccessControl should not handle the custom URL's

Send prest (repo) to awesome-go

https://github.com/avelino/awesome-go

homebrew formula

Mock postgresql data base

Example on PostgreSQL drive:
https://golang.org/src/database/sql/sql_test.go#L1030

Paginate results

Add support to paginate results
?_page=2&_page_size=10 (pagination, page_size 10 by default)

All URLs via GET need to support paging

Support Delete

Support delete data using DELETE http verb

rewrite tests using native go package