
Automatic REST API for Any Postgres Database - begriffs
https://github.com/begriffs/postgrest
======
steve-rodrigue
Creating an API directly on top of your database schema brings the problem of
impedance mismatch in all the client applications built directly on top of
your new API.

However, if you create an Endpoint (Objects) based RESTful API on top of your
database schema and then use this new API in all your client's applications,
you will have the problem of impedance mismatch in only 1 application: your
REST API.

For more information related to impedance mismatch:
[http://en.wikipedia.org/wiki/Object-
relational_impedance_mis...](http://en.wikipedia.org/wiki/Object-
relational_impedance_mismatch)

It might be a good idea to create a "build and forget" application on top of a
RESTful API built directly on top of a database schema. I would probably use
it for a movie website, since movie sites are normally built to promote the
movie and forgotten after.

However, building client applications you need to maintain on a longer term,
on top of a RESTful API built directly on top of a database schema is a
terrible idea. It will get harder to maintain as the database schema evolves
and the amount of client applications grows.

~~~
rpedela
I don't see how this tool automatically introduces impedance mismatch? It is
certainly possible depending on how the application is written, but I don't
see how it _must_ happen?

~~~
steve-rodrigue
Impedance mismatch happens often when directly mapping table's data to an
object. When working with an API that match perfectly a database table, the
user will have 2 choices (inside his client's applications):

1) Building an object from the data received from a REST call (which is the
same as mapping a table's data to an object).

2) Create 1 or multiple REST calls to the API, transform the data and creates
an object.

The chances that #1 happens is far greater... since #2 is normally done when
building an endpoint (Object) RESTful API. So, if you would do #2, it might be
a better idea to create an endpoint API and comsume it in all your client's
applications...

If you do #1, your client's applications will be exposed to impedance
mismatch.

~~~
sinkasapa
I don't see how these problems from the object oriented paradigm necessarily
translate to this tool. It assumes a lot about how one plans to use their REST
API.

------
rpedela
Overall this is great, and I am thoroughly impressed. I have a couple
criticisms though.

1\. The API documentation is incomplete. I know this is fairly new, but
information about upsert and the other operations mentioned in the intro video
would be helpful.

2\. I don't like abusing schemas for versioning. I understand the purpose and
I think there are many use cases where this is desirable. However what happens
when you need to query tables and views in multiple schemas? There are cases
where you could use schema search path tricks, but there are cases where you
can't and I have such a use case. I would prefer being able to disable
versioning and set the schema in the route.

~~~
joevandyk
Why wouldn't you be able to query tables/views in multiple schemas?

~~~
rpedela
You can. I didn't say you couldn't absolutely, rather there are cases where
you can't. Using the schema search path and/or joining multiple tables into a
view allows you to query multiple schemas. The internal schema and relation
structure doesn't matter. But since this tool takes over setting the schema
for versioning at the API level, the end result is that, from the API's point
of view, there is only one schema in the database. My question remains, how do
I expose multiple schemas in the REST API?

I certainly could have missed something, but it doesn't seem like it is
possible to expose multiple schemas in the REST API itself. This tool seems
more geared toward providing a public REST API where the data happens to be
stored in Postgres rather than a more generic REST API for Postgres. I would
like the latter, but if that isn't the goal then that is okay.

~~~
joevandyk
I'm not exactly sure what you mean by "expose multiple schemas in the REST
API" \- you mean pg schemas or how the API is structured?

(I wish pg named schemas "namespaces" or something else, schema is an
overloaded term)

~~~
rpedela
Yes I agree with using the term namespace instead of PG schema. I have also
never understood why the default schema (namespace) is called "public". Why
not "default"? Anyway let us say how do I expose multiple namespaces in the
REST API?

------
dyadic
It's interesting that it can handle versioning, and could be something to look
into.

But generally, DO NOT tie your external APIs to your data model unless you can
can commit to never changing them. (hint: you can't).

\--- Edit: The versioning is whole API versioning instead of resource
versioning and works by using a different db schema for each version. This is
a terrible idea and I wouldn't recommend using this at all

~~~
joevandyk
It's not whole API versioning. If you have a resource that has different
behavior, you can bump the version just for that resource.

There's no reason you can't change your data model with this. Why do you think
you can't?

~~~
dyadic
The version numbers are tied to the schema. So if you have two resources, foo
and bar, then they're both at version 1.

You create a new schema called "2", change the foo table and release it. As a
side effect you also have two versions of bar that are identical. If a client
creates a bar+v1 then it goes into the first schema, and if a client creates a
bar+v2 then it goes into the second schema.

So you think "fine, I just won't publicise that, clients will only know
bar+v1". You continue to make a few more changes to foo, bumping up the
version number each time, and then you want to make a change to bar, so you do
that and you then have a bar+v1 and a bar+v8.

\-- "There's no reason you can't change your data model with this. Why do you
think you can't?"

You can with this through the versioning and different schemas. I meant more
generally that exposing your DB and then making changes would break consumers
of your API, forcing you to freeze your data model instead. Using schemas is a
novel solution, horribly hacky but I guess it works.

~~~
joevandyk
I'm not sure what you mean by "it goes into the first/second schema".

My understanding is that you use views to manipulate the data going in and
out. The views are in the first/second schemas, but the tables that hold the
data don't have to be.

~~~
dyadic
Interesting. So instead of having a full copy of the database in each schema
you would only create the delta and then simulate the rest with views?

And then creating a bar with version 1 to 7 would use the views in each schema
to insert it, but it would actually be inserted into the database in schema 1?

~~~
joevandyk
The database tables don't have to be in schema 1, they can be in any schema,
probably the default "public" one.

But yes, you don't have to have copies of the data in each schema. You only
put the view definition into each schema, and the view definition is
responsible for reading/putting data from/to the right place. And you bump the
version whenever the view definition's columns need to change.

------
michaelchisari
I have a use for this. I'll be evaluating it. Anyone know of any similar
projects?

~~~
spacemanmatt
You should probably consider OpenRESTy.

~~~
fit2rule
Another +1 vote for OpenRESTy, its simply brilliant ..

------
krick
I haven't really understood: it _generates code_ for your REST API based on db
schema, or it is an app which magically turns urls into db requests by itself?

I don't really see any reason whatsoever to use the latter if I can use the
former instead. It's simple and scalable approach, unlike relying on the lib
would "do everything just right".

------
fsiefken
How easy would it be to add Hypermedia support? Haskell does have some
libraries supporting it.

------
hackerews
What's the usecase for this?

~~~
dyadic
1\. People that have an application and want to insta-create a REST API for it

2\. A backend-as-a-service for people that don't want to do any backend coding
(mobile apps, web sites)

~~~
philstu
Those people need to think a bit harder before smashing out some automagical
API that things will rely on for basically forever.

~~~
dyadic
They sure do. I've written clients for services that have been autogenned from
a database like this, and experienced pain repeatedly because every change
they make breaks their API.

At least this product realises that and attempts to deal with versioning.
Unfortunately that way is by pushing the complexity into the database.

------
curiously
nice. this is really awesome as I've been looking to build a REST API just now
using the data in Postgresql.

Video seems to be down.

Does this support hierarchies? Ex. a table with material path.

------
curiously
What I really want is actually a way to turn a xlsx or a csv file into a REST
API. For example if there's a subcategory column, I want it to be hierarchial.

~~~
jhgg
You should be able to achieve that with this tool paired with postgres foreign
data wrappers!

~~~
curiously
do you have more information on this?

or do open source solutions already exist that convert spreadsheet into API?

~~~
jhgg
I'd start here:

[http://www.postgresql.org/docs/9.2/static/file-
fdw.htm](http://www.postgresql.org/docs/9.2/static/file-fdw.htm)

------
philstu
Seems relevant.

[https://twitter.com/philsturgeon/status/544965192883261441](https://twitter.com/philsturgeon/status/544965192883261441)

~~~
michaelchisari
I think most people here would prefer if your link had a better
explanation/defense.

~~~
curiously
PHP seems to attract the bottom of the barrel "brogrammer" types.

Kayne West of PHP. That says it all.

~~~
dang
"Please avoid introducing classic flamewar topics unless you have something
genuinely new to say about them."

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

------
al2o3cr
"If you're used to servers written in interpreted languages (or named after
precious gems), prepare to be pleasantly surprised by PostgREST performance."

then

"Ultimately the server (when load balanced) is constrained by database
performance. This may make it inappropriate for very large traffic load."

ROFL. Bragging about performance, then "but don't use it for anything big". So
instead of getting a solution which scales out with app servers in the
"language named after precious gems" (straightforward), you get to scale out
Postgres servers (not straightforward).

~~~
twic
Hold on, do you think the Ruby implementation would _not_ be constrained by
database performance? That would be impressive indeed.

The author's point is just that the implementation is fast enough that it will
never be the bottleneck.

~~~
zentrus
I'm not sure if this is exactly where al2o3cr was going, but a Ruby (or
whatever) application would normally include such things as caching and
queueing. Despite the slowness of Ruby et al, I actually would expect a modern
application with these layers to handle load much better than straight
database calls. So I guess I would consider the PostgREST implementation a
bottleneck because you can't add any of these layers.

~~~
icebraining
Why couldn't you add a plain HTTP caching layer (e.g. Varnish) on top of
PostgREST?

~~~
zentrus
Yes, you could. My only point is that PostgREST limits you in what you can do
in terms of handling load.

