Automatic REST API for Any Postgres Database | Hacker News Reader

Automatic REST API for Any Postgres Database

github.com

197 pointsbegriffs68 comments

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

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.

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?

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.

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.

Why would you map your REST API to tables and not views (or stored procedures)? When would you ever want to have tables interfacing with your application unless you're doing something absurdly under-/de-normalized?

You can avoid this in Postgres which supports objects and collections.

So... no. O/R impedence doesn't apply to an ORDBMS

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.

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

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.

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)

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?

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

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?

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.

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.

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?

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.

You use views to "freeze"how clients view the data model. You can rename/change/delete the data model as much as you want, if the view output and behavior stays the same, no versioning changes are needed.

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

Yes: http://www.zenqry.com/

ZenQuery is similar in that it creates REST API endpoints for your database tables / specific SQL queries. However, it's database-agnostic and runs as an independent web application.

May I ask what your use case is?

http://rny.io/nginx/postgresql/2013/07/26/simple-api-with-ng...

If you don't mind propietary software, my employer Denodo has recently released an "Express" version of its data virtualization platform: http://www.denodo.com/denodo-platform/denodo-express

One of its features is to provide a RESTful view of relational tables in a database, in a manner reminiscent of PostgREST. One difference is that with Denodo Express you must explicitly import tables from the "wrapped" database into the Denodo server that works as intermediary. The Denodo server can wrap other databases besides Postgres.

I wrote a bit about it here: http://productivedetour.blogspot.com.es/2014/12/connecting-t... (although I don't really cover the REST aspect.)

You should probably consider OpenRESTy.

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

HTSQL[1,2] by Clark Evans is similar.

1- http://htsql.org/doc/overview.html

2- https://en.wikipedia.org/wiki/HTSQL

sandman for python

i.e. https://github.com/jeffknupp/sandman a REST api for all sql databases using sqlalchemy.

The website http://www.sandman.io/ seems to be down - expired last November.

That probably is because he rewrote it:

https://github.com/jeffknupp/sandman2

https://github.com/pgrest/pgrest

https://wiki.postgresql.org/wiki/HTTP_API

www.zazler.com works with Postgres, MySQL, SQLite and SQL server

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

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

What's the usecase for this?

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)

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

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.

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.

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.

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

do you have more information on this?

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

I'd start here:

http://www.postgresql.org/docs/9.2/static/file-fdw.htm

Something like this that that could operate efficiently on 100M+ rows would be hugely valuable in biology

Not quite there yet (no hierarchical structure etc.) but I think sheetlabs seems like what you're looking for: https://sheetlabs.com

Two suggestions for csv -> api:

1. Parse: http://blog.parse.com/2012/05/22/import-your-csv-data-to-par...

2. csv to api https://github.com/project-open-data/csv-to-api

Though if your data really is as simple as a single table (a csv files tend to be), you could probably put together a sinatra or express app in a day or two depending on your skill level.

Seems relevant.

https://twitter.com/philsturgeon/status/544965192883261441

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

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

Kayne West of PHP. That says it all.

I'm far more than the typical user.

Oh and I know Go, so I must be super intelligent, because only super intelligent people use Go.

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

https://news.ycombinator.com/newsguidelines.html

PHP is just a tool that makes it very easy to create quick websites using logic directly in html. I totally agree that this is a bad practice and should be avoided. This is why it attracted a lot of script kiddies at its beginning.

On the othr hand, according to Wikipedia ( http://en.wikipedia.org/wiki/Brogrammer ) a "brogrammer" is a social programmer, normally attracted in hacking jobs at startups. Therefore, I believe PHP doesn't attract "brogrammers" since most startups do not work with PHP anymore. I believe new languages attracts more "brogrammers" than PHP.

PS: Please keep in mind that, nowadays, there are a lot of good programmers that write clean PHP code. Just have a look at the Symfony2 code source.

Perhaps the explanation is so self-evident that it seems redundant. The entire point of an API is to abstract away the underlying database schema.

I have to disagree. The most painful APIs I have had to work with are ones that try to abstract things away, and fail. They usually create bad abstractions (don't full understand what they are trying to expose) or they don't expose enough and I waste time trying to extend them.

There is no way to know what information clients will need in the future, so it is better to not try and guess. I have no problems with a chatty client.

A good example of this is the Google APIs. Sure when you open up the godoc for the google-api-clients it seems needlessly verbose, but I have found the patterns sane and much better than making another layer of pointless abstraction.

A good API is an API that abstract its database schema and expose designed Endpoints (Objects) to its clients.

By exposing Objects instead of your schema, your API has less chance to be updated as frequently, since its internal data storage is separated from its public API.

For the same reason, it is a bad practice to have multiple applications reading and writing data directly in your database. Building an Endpoint based API on top of your database and sharing this API across your clients is a better option.

Building an API directly on top of a database schema also brings the problem of impedance mismatch in all your client's applications: http://en.wikipedia.org/wiki/Object-relational_impedance_mis...

The problems here have nothing to do with the fact that the API might be chatty.

Views are the mechanism this uses to separate the representation from the underlying data model. You don't expose your schema directly with this tool, you expose views that can stay the same when the data model changes.

Even if it uses views, it still binds your data storage to your RESTful API. If you use such an application, it won't be possible to move some data to other data storage, such as Redis, to normalize some data, for example, without modifying all your client's applications.

If you create an endpoint (object) based API, you will be able to modify it the way you like, and if you change your data storage, your client's applications won't even know that you modified your internal databases.

EDIT Please keep in mind that my comments are only valid if you are building an API that you will have to maintain on a long term basis. Otherwise, this project is great... like I said in another comment, I'll probably use it for simple "build and forget" projects.

I think you have not adequately asked yourself the question: "why does my database change so much?" and "why does this matter?", because in reality - for such projects as this tool is targeted, which I would mostly estimate is for quick prototyping needs, this is immensely useful.

Here, I see it looking a bit like this:

    $ make db
    $ make onboard_data
    $ make generate_rest
    $ make client_bindings
    $ make clients
    $ make tests && make release

I see nothing wrong with a properly managed project perpetuating this set of commands through multiple versions..

It truly blows my mind that any further explanation would be required.

If you want a data store that exposes your database over HTTP then use on of the billion data stores that is designed to do that. None of those are meant to be a public API, and doing this is so incredibly wreckless and short sighted I could write at least a chapter in a book about it.

Actually, I did write a chapter in a book about it.

https://leanpub.com/build-apis-you-wont-hate

A RESTful API is about so many more things than just shoving a generic CRUD interface on top of your data schema.

> A RESTful API is about so many more things than just shoving a generic CRUD interface on top of your data schema.

Am I mistaken in thinking that this project offers much more control than shoving a generic CRUD interface on top of a data schema?

Obviously, something like this would provide a great way to bootstrap a REST api for the basics you need. Sure, it may run into limits when more specificity is necessary, but that's the trap that all autogen frameworks run into.

You can use this for CRUD api's, and then move to something else when you need to. Clients won't care.

API developer and authur who handcrafts via middleware (PHP) hates framework that automatically generates APIs from database features? Quelle f'ing surprise.

A developer who has been invited into the USA twice because of his skills at building complex APIs for complex companies suggests that automated solutions are a cancer on the API building industry?

Quelle f'ing surprise.

I also believe that creating a CRUD RESTful API directly on top of your database schema is a very bad idea because of the potential impedance mismatch.

Normally, a REST API is formed of Endpoints (Objects), so this article should explain the problem fairly well: http://en.wikipedia.org/wiki/Object-relational_impedance_mis...

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

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.

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.

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

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

Marshaling json and the ORM in Ruby is very likely to be the bottleneck in high-traffic api's.

This application takes the role of rails in an api. If you have a rails or python or whatever api backed by a PostgreSQL database, postgrest is basically no different.

Reply on news.ycombinator.com