
PostgREST – REST API from any PostgreSQL database - cdjk
https://github.com/begriffs/postgrest
======
pilif
Contrary to many other "expose a RDBMS schema as an API" solutions, this one
is interesting due to its very close tie-in with postgres. It even uses
postgres users for authorization and it relies on the postgres stats collector
for caching headers.

I also very much liked the idea of using `Range` headers for pagination (which
should be out-of-band but rarely is).

I'm not convinced that this is the future of web development, but it's a nice
refreshing view that contains a few very practical ideas.

Even if you don't care about this at all, spend the 12 minutes to watch the
introductory presentation.

~~~
cbau
Resources only map 1-to-1 with database models for trivial applications, so
certainly not the future. Still, useful for getting up and running.

~~~
radiowave
Coming from the old world of business IT where "integrate everything with
_the_ relational database" is standard procedure, it's certainly not unusual
for the database to be a place where abstractions are defined(1), so what you
expose via PostgREST may not in fact be a close match for the underlying data
model.

(1 - In fact with me it's pretty much a matter of policy: any external system
should access data via views named for the external system. Then, as
inconvenient as it sometimes is, Postgres' dependency mechanism will keep me
right about which fields in which tables are depended upon by which external
systems.)

~~~
glogla
That's also what postgrest documentation suggests as the way to deploy it.

------
benkant
This is good work and if I ever did web development, it would be like this.
Why people in the web world don't use stored procedures and constraints is a
mystery to me. That this approach is seen as novel is in itself fascinating.

It's like all those web framework inventors didn't read past chapter 2 of
their database manuals. So they wrote a whole pile of code that forces you to
add semantics in another language elsewhere in your code in a language that
makes impedance stark. PostgreSQL is advanced technology. Whatever you might
consider doing in your CRUD software, PostgreSQL has a neat solution. You can
extend SQL, add new types, use PL/SQL in a bunch of different languages,
background workers, triggers, constraints, permissions. Obviously there are
limits but you don't reinvent web servers because Apache doesn't transcode
video on the fly. Well, you do if you're whoever makes Rubby on Rails.

The argument that you don't want to write any code that locks you to a
database is some stunning lack of awareness, as you decide to lock yourself
into the tsunami of unpredictability that is web frameworks to ward off the
evil of being locked into a 20 year database product built on some pretty
sound theoretical foundations.

Web developers really took the whole "let's make more work for ourselves" idea
and ran with it all the way to the bank.

You'd have to pay me a million dollars a year to do web development.

~~~
3pt14159
You are speaking from ignorance with the voice of authority.

I worked on a rails app that handled a billion requests per day. The problem
isn't performance of the web framework, those are easy to load balance and
split into C or cache when you need it. The problem is scaling your database,
keeping your data secure, and iterating to meet business goals with a growing
codebase and infrastructure. A mess of stored procedures would restrain you
from doing all three.

And I know, I worked on a codebase in 1999 that did this because of the
"performance gains". It ended up bricking the project due to inability to
iterate.

~~~
sergiosgc
> The problem is scaling your database, keeping your data secure, and
> iterating to meet business goals with a growing codebase and infrastructure.
> A mess of stored procedures would restrain you from doing all three.

Your argument has a non sequitur right here. A mess of [foo] is a mess; the
layer it is in does not matter; the language it is in does not matter. A mess
of application layer code is equally effective in preventing scale, security
and effectiveness.

The original post is right. Web developers treat their databases poorly[1]. A
database is an interface to your data _that maintains integrity_. Maintaining
integrity almost always means stored procedures, as some validation is not
expressible as relational integrity and basic type validation.

Now, if you are at the point where your database fully guarantees integrity of
data going in and coming out, a REST interface is a small step away. This
project is very welcome.

[1] The typical web developer treats a database as a data store. It is _also_
a data store, but a well designed database is much more than than.

~~~
bkeroack
A mess is a mess, true, but some are easier to clean up than others.

GP is correct. Methods for scaling/optimizing the application layer are clear
and well-known. Scaling the data layer is a huge challenge. This is why the
market is filled with snake oil databases promising linear scalability and
perfect consistency/reliability, etc.

~~~
threeseed
Scaling the data layer is a huge challenge. No doubt. But calling databases
that are designed for solving these problems "snake oil" undermines the huge
amount of work that serious engineers have invested in this. No one has ever
promised linear scalability and perfect consistency/reliability. No one.

Cassandra, HBase, CouchDB etc even MongoDB have built in scalability as a
first order priority from day one and have been largely successful at it e.g.
iCloud, EA Online, PSN. Databases like this are a nightmare to work with for
smaller datasets but work incredibly well with larger ones.

It's always a shame to see HN act like you scale vertically and magically
every problem is solved.

~~~
AlisdairO
> It's always a shame to see HN act like you scale vertically and magically
> every problem is solved.

When this is seen (and IME it's a pretty minority opinion) I think it's there
as a reaction to the massive overuse and hype regarding a lot of newer-gen
DBs. There's absolutely no doubt that there are good uses for them, but those
cases are pretty niche compared to the level of their uptake.

~~~
volaski
You should read "innovator's dilemma"

~~~
AlisdairO
Is that comment intended to imply that companies will go under if they fail to
deploy new technology that doesn't target their business needs?

------
jister
I'm sorry but why would I go through HTTP to query data? Why can't I just hit
the database directly without the overhead of HTTP? Does a cleaner and being
more standards-compliant worth the overhead of passing through HTTP?

And what happens when you start applying complex business rules that needs to
scale? So many questions about this approach...

~~~
mooreds
To force yourself to go through a service. To abstract away underlying
implementation details.

More here in the value of services (Yegge's rant):
[https://plus.google.com/+RipRowan/posts/eVeouesvaVX](https://plus.google.com/+RipRowan/posts/eVeouesvaVX)

~~~
bpicolo
And by doing so you remove all the performance from what's hopefully the most-
performant part of your stack. =/

A database IS a service. It's just not a 'restful web service'. Making it one
doesn't gain you any useful abstraction for SOA.

~~~
sitkack
Not true, I now can get read only data using curl inside of a cronjob. This is
really powerful. Someone now needs to make a similar system for Redis.

------
CloudLeaper
What is the use case of wrapping Postgres with REST? I can't think of many
apps that don't require custom logic between receiving an API request and
persisting something to the database. Is PostgREST trying to replace ORM by
wrapping Postgres in REST? Or am I missing something. When would one use this
tool. My naive perspective needs some enlightening.

~~~
fulafel
You implement any custom logic in PostgreSQL mechanisms (permissions,
triggers, constraints, views, stored procedures etc).

~~~
cube00
...and be locked in for life, joy.

~~~
bdcravens
Is it a common circumstance to completely switch your data layer without
having a massive rewrite of your application? Do people frequently flip from
pg to Oracle, or from SQL Server to MongoDB, and it was super-easy because
that layer was abstracted?

~~~
markbernard
Not frequently, but yes. Was forced to move from Oracle to Postgres, not that
I mind Postgres. Very little software changes were required to get up and
running. If everything was in views or stored procedures the change would have
taken months instead of weeks.

~~~
CHY872
On the other hand, I had to write for some software that supported both Oracle
and Postgres (many deployments, new using Postgres and old migrating over time
to Postgres) and it was a _chore_. All tests had to be run in both, SQL was
'same but different' with different types, and for perf reasons there was
tonnes of hinting, which obviously pg ignores so overall performance was very
different.

------
weitzj
Could maybe somebody of the older experienced people comment whether this is a
good idea?

I find it intriguing, but maybe I am just one generation behind and you were
to say:

"Been there done that. This strong dependency on the database was really not a
good idea in the long run because... "

~~~
rodgerd
In my experience (and I'm older these days...) databases are a much rarer
migration than programming languages. I deal with stuff that's still in
(heaven help us) VSAM files, accessed a mix of assembler, COBOL, C, Java, TCL,
C#, C++, Pascal, and so on and so forth.

~~~
bdcravens
I work on a system has has iterated through 3 distinct languages while relying
on the same database.

~~~
spacemanmatt
Integrating at the database is a powerful pattern. I see some value for larger
organizations to go a step further and integrate at a higher service layer,
because one database could never support the entire enterprise. But for orgs
that fit in one database, it's great, IMO.

------
xdanger
How about [http://pgre.st/](http://pgre.st/) ?

it does same kinda stuff + capable of loading Node.js modules, compatible with
MongoLab's REST API and Firebase's real-time API

------
bni
What about when changes are made to the schema, wont the API just be changed
in that case?

Wont this lock you in with very hard coupling between your db schema and
public REST API?

~~~
glogla
You can do that using SQL views. That means you can provide consistent api of
your choosing no matter how the original tables look like. Postgrest docs also
describe how you should use this feature to version you API.

~~~
kelseydh
Implementing SQL views is not exactly trivial if you are operating under an
existing Ruby on Rails app. I suspect most RoR developers don't use SQL views
unless they absolutely need to for a certain query that's heavy on
performance.

As a result, the coupling to the schema does seem to be concerning as moving
everything to SQL views appears to be a high amount of overhead if you're
already successfully relying on an ORM.

~~~
dragonwriter
> Implementing SQL views is not exactly trivial if you are operating under an
> existing Ruby on Rails app.

From the applications side, it should be completely transparent to replace
base tables with views.

From the database side, assuming you keep the views and the base tables in the
same schema, for the initial transition, you just need to rename the base
tables, and then create views with the names of the original tables
referencing them; they'll be simple views, and so, in postgres, automatically
updatable, so you won't even need to explicitly define update logic.

------
CookWithMe
Looks really cool. I was first thinking it saves the JSON with the new
Postgres JSON support, but saving it as relational data is even more
impressive!

I'd say if the OPTIONS would return a JSON Schema (+ RAML/Swagger) instead of
the json-fied DDL, it would be even more awesome. With a bit of code
generation this would be super-quick to integrate in the frontend then.

------
arturventura
"It provides a cleaner, more standards-compliant, faster API than you are
likely to write from scratch."

If you are using this as a web server persistence backend, I would agree with
the first, more or less accept the second and reject the third. HTTP + JSON
serialisation are way slower for that kind of job.

If you are just exposing the database using only the Postgres, in that case is
interesting, however, I have concerns about how more complex business logics
would work with such a CRUD view.

~~~
hippich
I believe idea here is to put all the permissions into DB and let frontend
code do all the business logic.

------
caseysoftware
APIs require more than database access, security, and nice routes. Those are
all necessary but a good API also includes flows linking things together so
you can progress through higher order processes and workflows. You need to
make sure that you're actually providing user value.

CRUD over HTTP (or an "access API") should be a first step, not your end goal.

~~~
Mahn
I would not put a direct DB to HTTP REST API front-facing to the public, but
it has its use-cases, I can imagine using it server-to-server for instance.

------
gizmodo59
With Data Virtualization providers like Denodo you can create a REST web
service with any relational database very easily..

[https://community.denodo.com/tutorials/browse/dataservices/2...](https://community.denodo.com/tutorials/browse/dataservices/2rest)

~~~
cies
I'd be interested to see a benchmark. PostgREST is fast! And it is also a
piece of software that tries to "do one thing really well". It deploys as a
binary, which is also a big plus compared to these "first install this list of
dependencies at these ranges of versions before using our product".

Last thing: is Denodo open source? It is not listed at "why use Denodo", so I
guess not...

~~~
jacques_chester
I've found that Spring Data REST makes wrapping a database pretty easy.

------
McElroy
Between this (yes, I know it's 3rd party) and the support for JSON, PostgreSQL
seems to be eating into the market of the NoSQL databases every day. I like
that. I like that because the fewer new things I must learn, the more time I
can spend on the things I find interesting.

~~~
spacemanmatt
That is entirely on purpose, too.

~~~
McElroy
Tell me about your devolution. (He said to ask him to in his profile.)

~~~
spacemanmatt
It's really going quite swimmingly! Thanks for asking.

------
why-el
Splendid work, truly. The documentation is pure class and the whole library is
extremely well prepared for actual use. Kudos to the developer.

------
marknadal
Wow, there is a lot of contention in this thread. So first off I want to say
congratulations to the author of PostgREST. Getting 2k req/s out of a Heroku
free tier is just awesome ontop of all the overhead convenience you provide.
Great job, great documentation, all around looking fantastic. You deserve to
be on HN homepage.

Second, I'm an author of a distributed database (VC backed, open-source), so
I'd like to respond to some of opinions on databases voiced in this thread -
particularly in the branched discussions. If you aren't interested in those
responses, you can ignore the rest of my comment.

\- "You'd have to pay me a million dollars a year to do web development."
Don't worry, most webdev jobs are about a tenth of that. If inflation goes up
even a little bit...

\- "The problem is scaling your database", I can confirm that this is my
experience as well. But there is a very specific reason for that. Most
databases are designed to be Strongly Consistent (of the CAP Theorem) and thus
use Master-Slave architecture. This ultimately requires having a centralized
server to handle all your writes, and this becomes extraordinarily prone to
failure. To solve this, I looked into Master-Master (or Peer-to-Peer /
Decentralized) algorithms for my [http://gunDB.io/](http://gunDB.io/)
database. Point being, I'm siding with @3pt14159 in this thread.

\- "Sorry but databases are just a hole to put your shit in when you want it
out of memory", I write a database and... uh, I unfortunately kind of have to
agree, probably at the cost of making fun of my own product. You see, the
reason why is because most databases now a days are doing the same thing -
they keep the active data set in memory and then have some fancy flush
mechanism to a journal on disk and then do some
cleanup/compression/reorganizing of the disk snapshot with some cool Fractal
Tree or whatever. But it does not matter how well you optimize your Big O
queries... if the data isn't in memory, it is going to be slow (to see why,
zoom in on this photo
[http://i.imgur.com/X1Hi1.gif](http://i.imgur.com/X1Hi1.gif) ). You just can't
get the performance (or scale) without preloading things into RAM, so if your
database doesn't do that... well what @batou said.

Overall, I urge you to listen to @3pt14159 and @batou. PostgreSQL is
undeniably awesome, but please don't fanboy yourself into ignorance. Machines
and systems have their limitations, and you can't get around them by throwing
more black boxes at it - your app will still break and so will your
fanboyness.

------
rcarmo
Haskell, huh? The Force is strong on this one.

------
restya
Our Restya stack (open source) is similar to this with tech agnostic approach.
We used it to build Restyaboard
[http://restya.com/board/](http://restya.com/board/) (open source trello
alternative/clone)

------
arianvanp
I see currently only "flat" urls are supported. are there any plans (and is it
even possible in postgresql) to add dynamic views? so that `/users/1/projects`
is a dynamic view, dependent on the $user_id ? . That'd be rad

------
spacemanmatt
Since I'll have to front this with nginx anyway, I may as well use OpenRESTy.
I happen to like its REST setup pattern quite a bit.

------
jawr
I wonder if this could easily be forked to provide a GraphQL interface to pg.

------
dylanvalade
After visiting the demo my browser is running spyware.

------
hliyan
Is the JSON JSON API [1] compliant, perchance?

[1]: [http://jsonapi.org/](http://jsonapi.org/)

------
eridal
oh I love the silence logo!!

In fact, I think I love any musical reference in software :-)

------
curiousjorge
The comments are unbelievably negative considering the quality and the range
of features this offers. This is extremely useful because I won't have to
spend time writing out REST api in order to expose the Postgre data. Often a
client just wants to access the data with REST api and to write an entire
stack just to serve a few doesn't make sense. There's no expectation that this
is going to serve a gazillion requests per minute out of the box, and that's
totally fine with me since you shouldn't rely on off the shelf solutions
anyways if you were building an architecture of that size, but really question
if you are going to have that many requests per second. It reminds me of the
customer who claims 'I need this done in node.js to support 10,000 concurrent
users' and when asked how many users he has now he replies 'none, but I hope I
can reach the number', solving problems he doesn't have yet and complaining
that 'php is too slow'.

Some of the best ideas and tools on HN are met with so much negativity it
reminds me of Reddit, where the small percentage of people who get off on
putting others down so they can feel good about themselves dominate the
comments.

Good on you cdjk, this is exactly what I was looking for. Thank you!

~~~
chrismarlow9
Best advice I ever got from any engineer that actually caused me to start
completing projects was "complicate as necessary, not as desired..."

~~~
curiousjorge
can you elaborate what that quote means?

~~~
WorldWideWayne
It means do only what is needed. If you do anything extra, you could be
wasting your effort and/or causing problems. Doing just what is needed is
sometimes difficult for developers.

I have made the mistake of spending too much time slavishly implementing some
pattern only to figure out later that it was just serving my need to implement
the pattern, versus just getting the job done with a simple procedural script.

------
fica
Would be cool to put Kong [1] on top of the API to handle JWT or CORS [2] out
of the box.

[1] [https://github.com/mashape/kong](https://github.com/mashape/kong)

[2] [http://getkong.org/plugins/](http://getkong.org/plugins/)

~~~
alfonsodev
I think that would a good separation of concerns, I didn't know Kong, but it
seems that is more specialised tool supporting Oauth2, Rate limit, Ip
filtering ..etc via plugins. I would like to see both tools running in Docker
and working together. I started this public gist to explore this solution
[https://gist.github.com/alfonsodev/6a6c66b4074248ed9702](https://gist.github.com/alfonsodev/6a6c66b4074248ed9702)
feel free to comment and collaborate there.

------
sz4kerto
You should be aware that this is a _bad_ pattern for anything more serious
than a university homework. Instead of exposing functionality that you can
guarantee and that's required by the clients, you expose your database schema,
essentially tightly coupling the DB with the clients.

I know it's tempting to do that, but spend some time thinking of your data and
what do you want to expose.

~~~
jawr
Aren't a lot of endpoints essentially bound to the database anyway? If you
were to do any sort of major schema change, chances are you would have to
create a new endpoint (i.e. /api/v2/) to handle the new schema changes. Also
this handles versioning.

------
wisty
Example is broken. It's returning a JSON doc, so if you leave it then return,
some browsers will just return the cached JSON (as text).

Should add some header to say that it's JSON, or add a .json file extension
for the main page data.

Very interesting project though.

~~~
pilif
_> Should add some header to say that it's JSON, or add a .json file extension
for the main page data._

The server sends `Content-Type: application/json` and provides no header
related to caching. Browsers that do anything but fetching the resource again
are not spec compliant.

Also, the only browser to ever look at the extension of a file in the URL was
IE ([https://msdn.microsoft.com/en-
us/library/ms775147(v=vs.85).a...](https://msdn.microsoft.com/en-
us/library/ms775147\(v=vs.85\).aspx)) and they have long since stopped doing
that as all it was doing was cause security issues and screw with web
developers.

~~~
msane
Correct answer. The demo is doing everything right, parent seems confused.
Browsers also don't have any special regard for '.json' in the path. The path
can be anything; the path doesn't suggest anything about content-type or
caching.

~~~
wisty
Yes, but if / returns a html file, and /.json returns the json, it's
impossible for a browser to display the json by accident (no matter what the
header is).

And yes, the header seems correct, Checking, that's the API demo, the GUI is
separate. Thus the confusion.

Yeah, headers are the right way to do it, but a different path is also the
right way to do it, and extensions like .json can make that simpler in some
cases. Or /api/ prefixes.

~~~
CHY872
No. The browser should send with its HTTP request:

    
    
        Accept: text/html, application/json;q=0.8
    

and then if the server supports returning HTML, it should return it ahead of
any json. For example, Firefox sends:

    
    
        Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
    

and then it will send HTML ahead of xml, ahead of anything else, where it can.

If a RESTful client can only accept JSON, it should send

    
    
        Accept: application/json
    

Headers are the right way to do it with a RESTful API. The reason why is that
the path should indicate the resource you're trying to access and the resource
is independent of the data format.

Adding '.json', path specifiers etc is a kludge; it requires web server
support to actually work properly (it requires the webserver to send the right
MIMEtype in the header). The browsers ignore it (I think).

For example, occasionally you'll click on an image (I see it about once a
year) and you get back a stream of weird unicode. This is because the
webserver is returning the .jpg as text/html or whatever, and the browser is
rendering it as such.

Likewise, when you go on
[https://raw.githubusercontent.com/resume/resume.github.com/m...](https://raw.githubusercontent.com/resume/resume.github.com/master/index.html)
you are presented with plaintext because it has returned as Content-type:
text/plain, even though the data is actually HTML.

