7 ms·
Automatic REST API for Any Postgres Database
- curiously 12y agonice. 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.
- michaelchisari 12y agoI have a use for this. I'll be evaluating it. Anyone know of any similar projects?
- BjoernKW 12y agoYes: http://www.zenqry.com/ 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?
- bsg75 12y agohttp://rny.io/nginx/postgresql/2013/07/26/simple-api-with-nginx-and-postgresql.html http://rny.io/nginx/postgresql/2013/07/26/simple-api-with-ng...
- danidiaz 12y agoIf 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 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-to-denodo-virtual-dataport.html http://productivedetour.blogspot.com.es/2014/12/connecting-t... (although I don't really cover the REST aspect.)
- spacemanmatt 12y agoYou should probably consider OpenRESTy.
- fit2rule 12y agoAnother +1 vote for OpenRESTy, its simply brilliant ..
- bayareaguy 12y agoHTSQL[1,2] by Clark Evans is similar. 1- http://htsql.org/doc/overview.html http://htsql.org/doc/overview.html 2- https://en.wikipedia.org/wiki/HTSQL https://en.wikipedia.org/wiki/HTSQL
- andrewstuart 12y agosandman for python
- emmelaich 12y agoi.e. https://github.com/jeffknupp/sandman https://github.com/jeffknupp/sandman a REST api for all sql databases using sqlalchemy. The website http://www.sandman.io/ http://www.sandman.io/ seems to be down - expired last November.
- detaro 12y agoThat probably is because he rewrote it: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
- pella 12y agohttps://github.com/pgrest/pgrest https://github.com/pgrest/pgrest https://wiki.postgresql.org/wiki/HTTP_API https://wiki.postgresql.org/wiki/HTTP_API
- rudolfosman 12y agowww.zazler.com works with Postgres, MySQL, SQLite and SQL server
- rpedela 12y agoOverall 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 12y agoWhy wouldn't you be able to query tables/views in multiple schemas?
- rpedela 12y agoYou 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 12y agoI'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)
- philstu 12y agoSeems relevant. https://twitter.com/philsturgeon/status/544965192883261441 https://twitter.com/philsturgeon/status/544965192883261441
- michaelchisari 12y agoI think most people here would prefer if your link had a better explanation/defense.
- steve-rodrigue 12y agoI 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_mismatch http://en.wikipedia.org/wiki/Object-relational_impedance_mis...
- curiously 12y agoPHP seems to attract the bottom of the barrel "brogrammer" types. Kayne West of PHP. That says it all.
- philstu 12y agoI'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.
- dang 12y ago"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
- steve-rodrigue 12y agoPHP 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 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.
- dyadic 12y agoIt'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 12y agoIt'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 12y agoThe 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 12y agoI'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.
- curiously 12y agoWhat 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 12y agoYou should be able to achieve that with this tool paired with postgres foreign data wrappers!
- bthomas 12y agoSomething like this that that could operate efficiently on 100M+ rows would be hugely valuable in biology
- BjoernKW 12y agoNot quite there yet (no hierarchical structure etc.) but I think sheetlabs seems like what you're looking for: https://sheetlabs.com https://sheetlabs.com
- kennywinker 12y agoTwo suggestions for csv -> api: 1. Parse: http://blog.parse.com/2012/05/22/import-your-csv-data-to-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 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.
- fsiefken 12y agoHow easy would it be to add Hypermedia support? Haskell does have some libraries supporting it.
- al2o3cr 12y ago"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 12y agoHold 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 12y agoI'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 12y agoWhy couldn't you add a plain HTTP caching layer (e.g. Varnish) on top of PostgREST?
- zentrus 12y agoYes, you could. My only point is that PostgREST limits you in what you can do in terms of handling load.
- joevandyk 12y ago
- steve-rodrigue 12y agoCreating 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_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.
- rpedela 12y agoI 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 12y agoImpedance 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.
- 12y ago
- hackerews 12y agoWhat's the usecase for this?
- dyadic 12y ago1. 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 12y agoThose people need to think a bit harder before smashing out some automagical API that things will rely on for basically forever.
- dyadic 12y agoThey 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.
- krick 12y agoI 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".