
pREST – A fully RESTful API from any existing PostgreSQL database writting in Go - avelino
https://github.com/nuveo/prest
======
kornish
If the author is here, I'm curious about what they mean by this from the
README:

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

Have people faced operational difficulties with PostgREST? From what I hear
it's pretty battle-tested and performant.

~~~
gourneau
I have been running PostgREST in production for about a year with zero issues.
ProTip: One neat PostgREST feature is that it will output swagger specs for
your db.

I know nothing about Haskell and that has not been an problem. I am always
happy to see more open source software, though.

~~~
ruslan_talpa
Where are you using it, can you share a link?

~~~
thelambentonion
Not GP, but a listing of groups using PostgREST in production can be found
here: [https://postgrest.com/en/v4.1/intro.html#in-
production](https://postgrest.com/en/v4.1/intro.html#in-production)

------
leepowers
Additional security is absolutely necessary. The docs have table and field
level permissions but that's not enough. A user should only be able to edit
their own row in the database, and their password should always be one-way
encrypted, etc. Also don't want a user to be able to add themselves to a user
group, say add themselves to the "Administrator" user group by inserting a
table row.

This framework may simply be too "thin" \- there needs to be a place to code
business logic and access control, neither of which map neatly to how RDMS
treat users and security permissions. In which case might as well use a
framework like Django or Rails or Bottle or Laravel or any other of the dozens
of mature systems available.

~~~
ruslan_talpa
All authorisation requirements are solved easier and better at the db level.
Since you have permissions by columns and row level security, it means you can
define permissions down to a cell.

As someone said, people just don't read the manual of their database past page
100 :) and use "root" when connection to the database from their code.

[https://blog.2ndquadrant.com/application-users-vs-row-
level-...](https://blog.2ndquadrant.com/application-users-vs-row-level-
security/)

~~~
saosebastiao
Yeah, but users are a connection level concept, meaning you have to abandon
your connection pooling system in order to use the db's user authorization and
authentication system. Every new query is a new connection.

~~~
begriffs
The link Ruslan shared explains how to use row level security with application
users as opposed to db users.

Basically PostgREST translates cryptographically signed JWT claims from the
client request into local SQL variables accessible by postgresql's
"current_setting" function. The additional claims can identify the user beyond
their db role. They can specify, for instance, the user's email address.

Row level security policies can use current_setting to refer to the extra
claims. So multiple users can share a db role and connection pooling works
fine.

------
vervas
This is probably part of the ongoing craze and ridiculous propaganda of _let
's just rewrite everything in Go_. And they are all headlined as "written in
Go" like somehow this now just fixed everything.

~~~
Apaec
And seems now this propaganda is even borderline unethical, using FUD(claiming
production issues with PostgREST) to promote their own projects. I'm fine with
the fact that people become fanatics of their language, but that kind of
behavior is just harmful for OSS.

------
sriram_malhar
Can someone explain why it is necessary or useful to have a RESTful API for a
database that is never exposed to the Web? If I am going to use a library to
access the database, why not just use the native driver? Why the JSON
indirection?

~~~
icot
It's not really necessary because as you say, there are other ways.

But it's useful if what you want to do is create a simple API to access your
data. If your use case includes buildinga CRUD app, or API, you basically get
that out of the box.

If you have several components/clients (in different languages) which need to
access your database it allows you keep a common interface instead of having
to work with different specific libraries/frameworks for each language.

(PostgREST user)

~~~
ruslan_talpa
The point is to allow untrusted clients (browser/mobile) to run
(safe/predictable) queries against your database without the need for
"backend/api" as a middle layer because nowadays most of the business logic is
mostly on the frontend and it needs a way to get to the database.

It's not necessarily useful if you have trusted code running on your servers,
it should just connect to the database. But if this works for you ... why not.

(At least that's how i think of it, others may use it in different ways)

~~~
thedarkproject
> nowadays most of the business logic is mostly on the frontend

This is a design flaw imho and should not be used as a reason to motivate the
use of such projects. If your logic is in the frontend you should rather build
a backend that captures the logic instead of just putting a rest layer on your
DB and call it a day.

~~~
denisw
Unless you are writing an app that should also work offline. In this case,
most of your logic _must_ be your frontend, and your backend is mostly a data
repository for data backup and synchronization. In such a scenario, database-
as-an-API seems to be exactly the right approach.

------
Existenceblinks
Is there a wiki for security? It sounds its performance is good to not have a
middle layer between interface and database, but then I have no idea how do
you apply SQL-injection prevention, rate limit and other QoS.

~~~
floatboth
idk about this thingy but the original PostgREST is not vulnerable to SQL
injection. You're not writing SQL, you're writing URL query strings, and
PostgREST processes them correctly (I think it uses prepared statements for
most things, and careful filtration for table names and such).

------
jstoiko
postgrest.com vs postgres.rest: this is a bit confusing. Why not pick a
different name?

~~~
avelino
programming language writter, postgrest.com = haskell postgres.rest = golang

Read more in:
[https://github.com/nuveo/prest/issues/41](https://github.com/nuveo/prest/issues/41)

------
sntran
Now we wait for somebody to port PostGraphQL to Go. Tools like these are
great. Having a single executable binary is even better.

------
treve
It's not RESTful if it doesn't use hypermedia. Look into HAL, it actually
probably makes this server a lot easier to use and discover when dealing with
things like foreign keys.

~~~
bpicolo
Very few people refer to the pedantic HATEOS version of "REST" when referring
to REST these days (it's really just a repetitive no true scotsman argument
now) , and that's fine. Almost nobody implements it as part of what's now
considered a restful api.

~~~
danellis
What is the non-HATEOS version of REST? Can you usefully define it?

~~~
jholman
In practice it means "HTTP, plus certain conventions about Method and Path
that David Heinemeier Hansson happens to like".

I still cannot figure out why anyone would ever want to meet such a standard,
other than to trick the ignorant.

------
garyclarke27
Looks good, but only for direct table access. I only use functions to read and
write to Postgres. An API for calling functions seems to be missing, so is
useless for me. Have I missed something? Obviously could be achieved by
executing SQL scripts, but this is not very elegant. Any plans to add a
function API?

------
ams6110
Is there something mature along these lines for MySQL?

~~~
smt88
Why would you want that? Genuine question: why would you or anyone use MySQL
for a new project?

~~~
ams6110
I have an existing MySQL database that isn't going anywhere soon, and might
want to utilize something like this with it.

~~~
drunkcatsdgaf
check out sandman,
[https://github.com/jeffknupp/sandman2](https://github.com/jeffknupp/sandman2)

------
whatnotests
If you're putting REST in front of your database, you're gonna have a bad
time.

Performance suffers, and transactionality is a non-starter.

Best to forego this style and instead go with more of an RPC system.

~~~
ruslan_talpa
I am guessing you did even look an how PostgREST works :)

~~~
rattray
For the lazy, would you mind summarizing?

EDIT: actually, after a few minutes on their docs page, I'm still not sure
"how it works" regarding perf, so this could be useful even for the not-so-
lazy :-)

~~~
ruslan_talpa
Writing docs is hard ... You can think of PostgREST as one big pure(ish)
function It translates ONE http request to ONE SQL query and executes it in a
transaction (after setting some context). There is very little overhead. 90%+
of the time of a request is consumed by the db running the query. So usually,
a request is about 5ms-10ms (without network time).

~~~
rattray
Interesting. Thanks for explaining!

