
PostgREST – A fully RESTful API from any existing PostgreSQL database - developeron29
https://github.com/begriffs/postgrest
======
kornish
Note for the submitter that "Show HN" is generally for things you yourself
have made – see [0]. On the other hand, thanks for submitting; this is a great
project and it deserves attention.

Postgrest is a great example of a real-world Haskell codebase. It's concise
for the amount of functionality it offers, which is characteristic of
functional languages (<3000SLOC of source, not counting tests). I'd encourage
anyone interested in working with non-toy functional codebases to take a look
around with it, or better yet, submit a PR (there are a few "beginner"
issues!).

[0]:
[https://news.ycombinator.com/showhn.html](https://news.ycombinator.com/showhn.html)

~~~
gothrowaway
> Postgrest is a great example of a real-world Haskell codebase.

It's hard to read. _Very messy_.

[https://github.com/begriffs/postgrest/tree/master/src/PostgR...](https://github.com/begriffs/postgrest/tree/master/src/PostgREST)

Despite 15 years of programming experience in python, js, C, etc. I feel like
I'm going to have to duck my head down to be chastised for not understanding
the language and not being "intelligent" enough to see the depth.

It looks like jibberish, to me. Not trying to be offensive. I'm sure the
person who written it had it make sense to _them_. You likely also notice, _a
lack of code documentation_. Bad form. Don't tell me it's because I don't know
haskell, _that 's why I'm not expending the time to learn it, despite the
buzz_.

Meanwhile, SQLAlachemy and Hibernate isn't reporting complaints and a human
being could actually parse it to understand what the hecks going on there. And
despite it being Python or Java - easier and far more widely adopted languages
- they're documented _extensively_ , the authors didn't solipsistically assume
others would "get it".

Which is a pattern I've been seeing with hardcore functional advocates in
communities. They are the kind of people who'd work 2 weeks on a paper for a
mathematical proof, shove it to you in the hallway to look smart, and say
"It's obvious". It's not, you're just trying to show you're smart, but no
one's understanding you - and that is important in you winning people over and
not looking arrogant.

> It's concise for the amount of functionality it offers

Make it 4 times as many lines. Because there is so much condensed inside of
this.

>8k stars. Less than 50 contributors. Of which, only the top 7 changes more
than 100 lines.

If this considered a real world haskell, it's no wonder there's not a lot of
people using it.

It does more to demonstrate functional programmers lack empathy for enterprise
ones. Because even with solid grasp of CS concepts, even Haskell's own
proponents are having a hard time stomaching contributing to it.

~~~
dtech
> It's hard to read. [...] Despite 15 years of programming experience in
> python, js, C, etc. I feel like I'm going to have to duck my head down to e
> chastised for not understanding the language. [...] Don't tell me it's
> because I don't know haskell, that's why I'm not expending the time to learn
> it, despite the buzz.

To me you're a classic example of the "Blub Paradox" [1]: languages with other
features/language constructs than you're used to just seem worthless, complex,
without value and foreign to you.

You mention a lot of experience in hardcore imperative/imperative-OOP
languages (C, JS, Python). Haskell, Rust, Scala etc. require a bit of a
different mindset and thus have an initial steep learning curve.

Compare it to lamda's. Did you see the value in anonymous functions the very
first time you were introduced to the concept? Now that you've probably used
them for a while, would you be happy to use a programming language without
them?

You're not willing to learn the language because you don't understand it, and
dismiss the language as having no value because you don't understand it. Is
that really fair without at least having a basic understanding of the
language?

[1] [http://wiki.c2.com/?BlubParadox](http://wiki.c2.com/?BlubParadox) /
[http://www.paulgraham.com/avg.html](http://www.paulgraham.com/avg.html) . I'm
not a fan of the "I Know LISP So I'm Better Than You" tone of the original
article, but I do fully agree with the key point that it's hard to evaluate
language constructs you're not used to.

~~~
gothrowaway
> languages with other features/language constructs than you're used to just
> seem worthless, complex, without value and foreign to you.

Document the Haskell code and not stuff so much logic into one line. I think
that's a fair thing to ask for in software engineering.

 _Anyone_ who complains about Haskell seems to have either a.) not programmed
it b.) weren't programming "correctly" c.) a xenophobe/blub/NOS

I know what lambdas are, I know there are functions and folds. That in and of
itself doesn't tell the story of the data that's actually being manipulated
and the expected output. So that's why you have a 20-30 line comment block
explaining what's going on in important API functions.

You could program python, JS or ruby similarly; wrapping multiple functions on
the same line until you have to actually trial each function to figure out why
your not getting an expected result. It _feels_ clever, at the moment, but we
don't do it because we're not helping colleagues understand it.

> You're not willing to learn the language because you don't understand it,
> and dismiss the language as having no value because you don't understand it.
> Is that really fair without at least having a basic understanding of the
> language?

The commenter was heralding the project as a "great example" of "real-world
haskell" and recommended contributing to it. I was shocked to see 8k stars and
a paltry 50 contributors; of which, only the top 10 have contributed over 100
lines of code. That's half the contributors, proportionally, to something like
neovim or redis, and even worse compared to node.js projects.

And, I looked at the code of the project, which is open source, and didn't see
code documentation.

I'm trying to be generous. My advice: stop packing the code so close and
document it, probably _more_ than normal, since the symbology in their is
highly dense.

~~~
ruslan_talpa
Here is a project with about the same number of
stars/contributors/contributors with more then 100 lines/time since release

[https://github.com/graphql/graphql-
js/graphs/contributors?fr...](https://github.com/graphql/graphql-
js/graphs/contributors?from=2015-06-28&to=2017-03-25&type=a)

:) So by your logic, i could say:

"If this considered a real world javascript/node, it's no wonder there's not a
lot of people using it." which is obviously wrong.

You are using the wrong metrics to judge the language popularity. Virtually
every OSS project has only a handful of core contributors.

PS1: Note that i am not arguing PostgREST is a great example of real world
haskell, it's not for me to say since i have bias :)

PS2: You are right that the source code is not commented and it could use a
little of that but what you are missing (because you are not used to haskell)
is that a lot of the documentation for haskell projects comes from the types
and type signature of the functions. So just by looking at that, in a lot of
cases, a haskell programmer can reliably tell what the function is doing
without any documentation

~~~
gothrowaway
It's indefensible to have code that tightly compacted, throwing functions
around and _not_ documented.

Don't care if it's Python (basically pseudocode), Javascript, Haskell or
Erlang.

For all the time Haskell commenters turn criticism into a Socratic dialog,
they could be considering ways to make their code digestable. It's been what,
20 years now?

Not just to non-haskell programmers, but all the haskell programmers I see
swarming around threads like these on the internet; you'd think they'd
actually be teaming up and collaborating with each other. I can't put my
finger on it, I think there's a trend, a feeling, that it's not suitable for
programmers focused on achieving business ends. It's more of a hobby thing

Programming in the enterprise means confronting the reality that there are
design trade-offs and someone else has to read the code. Haskell programmers
seem to go off the deep end trying to pull off a hack of making haskell work a
certain way, then its "pencils down", _sayonara_. The real world doesn't
accept that, programs have to be maintained by others.

~~~
lucasdicioccio
I have a (non-contributor) experience with the PostgREST codebase. It's true
PostgREST codebase is not great (lots of long functions, need some work to
extract the main program into a library etc.) but the codebase definitely is
easy to navigate and modify. For a transient project at work I've asked a dev
to evaluate how much work it would be to port PostgREST to MSSQL. My dev had a
working proof-of-concept in a few days. The bottleneck was not navigating and
patching PostgREST codebase but dealing with MSSQL. We decided to go another
route only for the lack of a native MSSQL driver in Haskell: there's an ODBC
driver (which my dev used for his PoC) but I don't really trust ODBC+FreeTDS.
Hence, we'll write a good-old C# binary with a Microsoft-blessed driver to
expose the few ad-hoc queries our own team needs rather than a generic
solution for the whole company and we'll move on. In short, I have some data
invalidating your argument about the PostgREST code style being indefensible.
Although I don't enjoy PostgREST codebase I love the product because it
delivers what the website claims. Rather than diminishing the hard work of
devs using a throwaway/slandering account, I just reckon that Haskell and
"ship it culture" are reconcilable.

~~~
Apaec
I also did something similar to postgREST for MSSQL in java, not in haskell
because of the same driver issues you mentioned. I managed to implement jwt
auth pgREST style taking advantage of MSSQL user impersonation though I did
jumped into some limitations, for example the lack of enviroment variables in
transaction scope in MSSQL forces you to have to declare one db user for an
app user.

I have little experience in Haskell despite that it was easy for me to
understand the gist of what postgREST does and port it to java.

------
theprotocol
Some feedback: I need to see some kind of "big picture" usage highlights. I
find it hard to picture what endpoints are generated based on the tables: does
each table/row become a resource with its own url? How are relational queries
and joins handled?

I looked at the docs and they seem to discuss various concepts and other
minutiae but there is no real overview that cuts through the fat.

It's not immediately obvious to me how it fulfills its stated claim:

 _> PostgREST is a standalone web server that turns your PostgreSQL database
directly into a RESTful API. The structural constraints and permissions in the
database determine the API endpoints and operations._

 _> Using PostgREST is an alternative to manual CRUD programming. Custom API
servers suffer problems. Writing business logic often duplicates, ignores or
hobbles database structure. Object-relational mapping is a leaky abstraction
leading to slow imperative code. The PostgREST philosophy establishes a single
declarative source of truth: the data itself._

The second paragraph in particular is a pretty bold claim, so I find it
strange that there is very little elaboration on how this project can
eliminate the need for custom APIs.

~~~
ruslan_talpa
You are right in saying that the docs lack "the big picture", but that will be
fixed soon(ish).

The big picture is that it's not PostgREST alone that accomplishes this "big
claim" of eliminating the need for custom APIs. It's the combination of using
openresty(nginx)/postgrest/postgres/rabbitmq together that gives you the
possibility of "defining" apis rather then "manually coding" apis.

~~~
theprotocol
Sounds like an interesting tightrope walk. I feel a bit of concern about the
number of moving parts being part of a single solution, having configured
similar selections of software myself, but I'll wait and see. Best of luck.

~~~
ruslan_talpa
those moving parts are all there already in every project (nginx/db/rabbitmq),
people are just not using their full power.

~~~
theprotocol
Fair point. I can't really judge until I see some kind of overview that deals
with the pragmatic aspects of working with it, or trying it myself (which I
may do some time).

------
pella
Alternative:

pREST : GOlang based RESTful API ( PostgreSQL database ) :
[https://github.com/nuveo/prest](https://github.com/nuveo/prest)
[http://postgres.rest/](http://postgres.rest/)

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

~~~
arianvanp
Haskell literally has the same deployment benefits as go. They both deliver
single static binaries with just a few dynamic dependencies (for haskell libc,
libgmp)...

Honestly, the authors seem to have no clue what they're talking about and were
looking for a good excuse to build something cool :). Which is fine!

------
vikingcaffiene
This is neat and I am going to definitely dig in an play around with it. I
guess my biggest worry with projects like these is what happens when something
breaks? If I decide to integrate a piece of tech into my stack, I need to be
able to intimately understand what's going on under the hood. If something
breaks or works in a way I don't expect, I need to know it well so I can
diagnose and fix the problem. Something that abstracts away this much of the
dirty work makes it less attractive to me for anything serious. Its also
written in Haskell which is an awesome language I hear. However the syntax is
foreign compared to "traditional" languages and its less well understood due
to a smaller community. That means I can't just fork the code and fix a bug if
I end up in a bind. Just seems kinda risky. Hope to be proven wrong because it
is a really cool idea. Best of luck to the authors.

------
merricksb
Previous discussions:

[https://news.ycombinator.com/item?id=9927771](https://news.ycombinator.com/item?id=9927771)
(613 days ago)

[https://news.ycombinator.com/item?id=8831960](https://news.ycombinator.com/item?id=8831960)
(812 days ago)

------
ezekg
What's the use case for something like this? The claim that it writes APIs
better than I could by hand doesn't make a lot of sense to me--writing an API-
ORM-thing, sure, but not a non-trivial API. I've never built an API that is
simply a CRUD front-end to a database--there's always business logic + the
output of the API _very_ rarely matches the database tables underneath e.g.
you may be rendering 2-3 different models, but that's never revealed to the
end-user.

~~~
ruslan_talpa
Don't confuse PostgREST with something that you just point at a (poorly
designed) database schema and magic happens and you get a nice api.

You point it to a schema that consist only of views (that you define) and
stored procedures (that you write) that abstract away the underlying tables.
You define constraints on all your columns so junk does not get into the db.
You define database roles and RLS policies and give them privileges so that
you control who has access to what.

You still in a way write backend code, but in this case backend code is mostly
views/constraints/triggers and in rare cases stored procedures.

~~~
ezekg
I see. Either way, that's not really obvious from the readme or the tagline,
"REST API from any existing PostgreSQL database."

------
twelve40
I guess this is like a lower-level version of Parse (on a different,
transactional stack too). Pretty cool. I wonder though, often times I have
cases that are mostly CRUD but with a little extra: e.g., "create this object
and kick off a Stripe payment", or "create this object and send an MQ
message". With Parse, you just write Node triggers to do that. Would I have to
dig through Haskell code (or hire Haskell developers) to do the same here, or
does postgrest support an easier way to do that?

~~~
begriffs
You can trigger external actions by connecting PostgreSQL pubsub
(LISTEN/NOTIFY) with an external job queue.
[https://postgrest.com/en/v0.4/intro.html#external-
notificati...](https://postgrest.com/en/v0.4/intro.html#external-notification)

A NOTIFY SQL command can be sent out from either a stored procedure or a table
trigger. (The docs could use some examples of this, but that's the idea.)

~~~
gnud
Be aware that Postgres' NOTIFY is not stored/queued in any way. If your
external client is not listening, it will get lost.

I would instead have the trigger insert a row into a "pending tasks" table -
and then send a NOTIFY.

~~~
lima
[http://debezium.io/](http://debezium.io/) is a better way to do this.

It uses the logical decoding feature to get all row changes and writes them to
a Kafka queue.

This means it can pick up where it left when it crashes.

~~~
ruslan_talpa
along the same lines [https://github.com/confluentinc/bottledwater-
pg](https://github.com/confluentinc/bottledwater-pg)

------
scosman
Odd choice to push JSON serialization onto the DB while touting horizontal
scaling. Still pretty cool that this much is possible with Postgres and a
minimal frontend.

~~~
ruslan_talpa
Doing json serialisation in the db is the only way to extract tree like data
from the db. Another point to consider is that this is a much lower burden on
the database then you think (roughly speaking, it adds 15%-20% more cpu load
then a normal query), this is C code doing this, can't get much faster that.
This type of load is easily horizontally scalable using read replicas which in
RDS is basically one click.

But forget all that talk about horizontal scalability, 99% of the projects
will never outgrow a single (big) database so it's no use in complicating
things with "elastic" setups and "webscale". People don't realise just how
fast postgres is [http://akorotkov.github.io/blog/2016/05/09/scalability-
towar...](http://akorotkov.github.io/blog/2016/05/09/scalability-towards-
millions-tps/)

Who among us have worked on projects that have 1M queries per second, not
many.

~~~
DrJokepu
It's not really the only way (PostgreSQL has recursive queries), but it's
definitely the most convenient (and in many cases the most efficient) way if
you're not worried about referential integrity.

~~~
ruslan_talpa
Care to explain how you would extract in a single query tree like data
(without duplicate data going over the wire)?

~~~
willglynn
I use `WITH RECURSIVE` to traverse trees:

[https://www.postgresql.org/docs/current/static/queries-
with....](https://www.postgresql.org/docs/current/static/queries-with.html)

~~~
ruslan_talpa
it's not about traversing trees, it's about "returning" trees, there is a
difference :)

~~~
willglynn
Yes, and I use it to do things like "starting from this point in the tree,
recursively return all of its children". Usually I ask for `parent_id`, other
times I ask PostgreSQL to accumulate an array describing the path the database
took to get from here to there. In no case have I found `WITH RECURSIVE` to be
insufficiently powerful for working with trees.

~~~
ruslan_talpa
I don't think you understand what i am referring to when i say "returning
data". How do you return this
[http://pastebin.com/9cqzQETj](http://pastebin.com/9cqzQETj) information from
the database, in a single query, without duplicate data.

consider there are 3 tables clients/projects/tasks with FKs between them.

Please paste the query that returns all the information above using "with
recursive"

This is how the communication between the client an PG goes
[https://www.pgcon.org/2014/schedule/attachments/330_postgres...](https://www.pgcon.org/2014/schedule/attachments/330_postgres-
for-the-wire.pdf)

\- client sends an SQL command \- server replies with RowDescription detailing
the structure \- after that a DataRow message is sent for every row \-
finally, the server sends CommandComplete and ReadyForQuery

So inherently the protocol can only represent a 2D array.

~~~
jklehm
You’d use these [0] functions to build a json string and return your
structure, one row per root of each hierarchy (you might only return one row
if you’re after a single hierarchical json object). [0]
[https://www.postgresql.org/docs/9.5/static/functions-
json.ht...](https://www.postgresql.org/docs/9.5/static/functions-json.html)

~~~
ruslan_talpa
Please read the whole thread, this is the exact point i was making, json is
the only way to return tree like data. willglynn was saying you can do it with
"with recursive" and i think he was confusing "traversing/inspecting" tree
like data with "returning" tree like data.

------
travisilu
I think this framework is a better way if clients need access database via
RESTful API. At least, it can encapsulate business logic easily to be a
microservice.

[https://www.reddit.com/r/ruby/comments/61bb6h/squad_simple_e...](https://www.reddit.com/r/ruby/comments/61bb6h/squad_simple_efficient_restful_framework_in_ruby/)

------
manojlds
Wanted a throwaway API recently and then finally realized that this cannot be
deployed to Heroku with the PG add-on. Disappointing.

~~~
postila
Why?

------
andyhoang
What cases I should use this? I mean, look like it used for simple/beginning
project, when ORM can do quite handy

~~~
kornish
An ORM is a library integrated into a language runtime. Postgrest is a service
– a separate process – which sits in front of a Postgres database, offering a
RESTful HTTP API over that database. This means web or mobile HTTP clients can
access the database in a safe, controlled manner.

Postgrest basically shifts the work of writing a basic CRUD API (a task for
which you would probably use an ORM) to declaring a SQL schema. From that
schema, it infers which endpoints should exist and what they should do. For a
certain class of web app, this can be a HUGE time saver.

Beyond that, consider checking out the "Motivation" section of the website:
[https://postgrest.com/en/v0.4/intro.html](https://postgrest.com/en/v0.4/intro.html)

~~~
bpicolo
To be fair, you could do the same with an abstraction layer over an ORM.

------
dsc__
Can't help to ask myself "What problem is this project solving" \- Perhaps so
that more front-end oriented folks can easily access a data source otherwise
only exposed by SQL.

------
apapli
Out of interest is there an equivalent of this for MySQL?

~~~
davidlee1435
Here's a Node-based framework: [http://loopback.io/](http://loopback.io/)

------
lauretas
Is there anything like this for SPARQL endpoints?

------
fiatjaf
Not valid for Show HN.

------
Entangled
Is it possible to include YAML in the response format?

    
    
        - [name, age, sex, phone]
        - [Taylor Swift, 27, female, 555-SWIFT]
    

I find YAML to be the best format for everything.

~~~
throwme321
Since you can get JSON and JSON is a subset of YAML, you also have YAML.

