12 ms·
PostgREST – REST API from any PostgreSQL database
- deleted 11y ago[deleted]
- hliyan 11y agoIs the JSON JSON API [1] compliant, perchance? [1]: http://jsonapi.org/ http://jsonapi.org/
- ossreality 11y agomfw when people still think this is going to be a thing and what's with the garbage pretty-print of the json on that page. Yuck!
- wisty 11y agoExample 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 11y ago>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).aspx https://msdn.microsoft.com/en-us/library/ms775147(v=vs.85).a...) and they have long since stopped doing that as all it was doing was cause security issues and screw with web developers.
- msane 11y agoCorrect 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.
- JoshTriplett 11y ago> Browsers also don't have any special regard for '.json' in the path. Browsers shouldn't care about file extensions, ever, but some versions of IE did.
- wisty 11y agoYes, 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.
- dijit 11y ago/ being an index which points to a .html file is completely arbitrary though. there's no specification that says it must be so.
- CHY872 11y agoNo. 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/master/index.html https://raw.githubusercontent.com/resume/resume.github.com/m... you are presented with plaintext because it has returned as Content-type: text/plain, even though the data is actually HTML.
- aidos 11y agoThanks for the flashback! I'd forgotten about the days when we used to add something like &ft=.pdf to the end of querystrings so IE would recognise the file as PDF. I can't remember the other tricks, but there was a whole raft of things you'd do to force downloading of content, or not.
- markbernard 11y agoAre you sure about that? Cache-Control, Expires? If you don't change the URL IE will cache the response whether you like it or not. 2 ways to handle this are to generate a random number to append as a parameter to change the URL. The other way is to have your web service to tell the browser not to cache with response headers. I have had IE do this to me and made all my web services send back "Cache-Control: no-cache" to prevent IE caching.
- pilif 11y agoContrary 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.
- tracker1 11y agoAgreed, even though I don't care for exposing the database quite this directly, it is a very interesting approach... I'm also unsure of using the DB's authentication system. While approachable, I'm at a place where imho, for most systems there is a conflation of users, accounts, and logins. IMHO, a user may only have a single account, but a modern system should support multiple logins... Supporting social/jwt/oauth style logins in addition to local database backed logins is generally the best option for public facing sites/applications.. and even then jwt/oauth will allow for more seamless integration with internal SSO options. Then again, this type of approach may work well if you want to be able to use it as an additional abstraction of your database access from a UI backing API.
- sitkack 11y ago> Agreed, even though I don't care for exposing the database quite this directly, it is a very interesting approach... It is up to the developer to use this responsibly. I don't think the creator is suggesting to drop PostgreSQL instances directly onto the net. This would be ideal in a layered backend, and in fact, makes systems way more composable.
- cbau 11y agoResources only map 1-to-1 with database models for trivial applications, so certainly not the future. Still, useful for getting up and running.
- fica 11y agoWould 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 11y agoI 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.
- framp 11y agoKong is great but adds dependencies to the project (I don't really want to deploy bloody Cassandra for a small API). And it would be great to have 3rd party logins working in Kong
- mrkmcknz 11y agoI came here to say this.
- CookWithMe 11y agoLooks 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.
- benkant 11y agoThis 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.
- kijin 11y ago> Why people in the web world don't use stored procedures and constraints is a mystery to me. You can blame MySQL 4.1 for that :( Most people who call themselves "web developers" haven't even heard of PostgreSQL, or even if they've heard of it, have no use for it because their usual clients are stuck with MySQL-only web hosts who have only just managed to upgrade to PHP 5.3.
- arturventura 11y ago"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 11y agoI believe idea here is to put all the permissions into DB and let frontend code do all the business logic.
- CloudLeaper 11y agoWhat 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.
- perlgeek 11y agoI think it's nice if you want to build some kind of web application that explores a database. I wouldn't use it a for a "normal" web app, for exactly the reasons you state.
- jawr 11y agoI had this initial response, but you can have another service running tasks on and in to the database, or more complicated views for interacting with more complex models. PostgREST is just a service for interacting with your data, logic has to be done client side/in another service.
- CloudLeaper 11y agoSo my server side code will now become a collection of triggers, views etc? That doesn't sound too appealing.
- jawr 11y agoWriting boilerplate to turn rows in to objects is also not appealing. As with many things I suspect there is a valid use case either side.
- wilsonfiifi 11y agoOne use case would be with Google App Engine. Other than it's Datastore [0] or CloudSQL [1], you don't have access to other databases. So this would be a great way to have Postgresql as a backend to your app. [0] https://cloud.google.com/appengine/articles/datastore/overview https://cloud.google.com/appengine/articles/datastore/overvi... [1] https://cloud.google.com/sql/docs/introduction https://cloud.google.com/sql/docs/introduction
- rcarmo 11y agoHaskell, huh? The Force is strong on this one.
- sz4kerto 11y agoYou 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 11y agoAren'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.
- vog 11y agoI share this sentiment, but this is mostly a question of organization, and not so much about whether the code is inside or outside the DB. I personally used some prefix for that, such as "service_" or "public_". All stored procedures (in PostgreSQL speak: "user-defined functions") that have this prefix are accessed from the client. Everything else is internal. Of course, it would be even nicer if the REST framework would enforce that convention. This is especially nice with JSON aggregation and SUB SELECTs, where you can directly aggregate your objects and lists of sub objects within the DB query, and generate the whole JSON result directly in the DB.
- glogla 11y agoThat's why the "official" way to deploy postgrest is to run it on views in different schema than your data is.
- jawr 11y agoI wonder if this could easily be forked to provide a GraphQL interface to pg.
- weitzj 11y agoCould 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 11y agoIn 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 11y agoI work on a system has has iterated through 3 distinct languages while relying on the same database.
- spacemanmatt 11y agoIntegrating 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.
- spopejoy 11y agoAgree, the received wisdom has always been database vendor choice as a ten-year commitment, as opposed to almost anything else. It's a reason to avoid databases :)
- spacemanmatt 11y agoStrongly disagreed that it's a good reason to avoid databases. If you are going to collect data of any value over a long period of time, pick a horse and stay on it. Over time, technology and business needs might force a re-evaluation of that position, but you don't want to build around unmeasured plans to maybe switch databases at random some day.
- jister 11y agoI'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 11y agoTo 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
- javaJake 11y agoIncredible read. Thank you for sharing that post.
- swah 11y agoThose are big organizations, though. Maybe smaller orgs / single devs should start with a monolith? http://martinfowler.com/articles/microservices.html http://martinfowler.com/articles/microservices.html http://martinfowler.com/bliki/MonolithFirst.html http://martinfowler.com/bliki/MonolithFirst.html http://martinfowler.com/articles/microservice-trade-offs.html http://martinfowler.com/articles/microservice-trade-offs.htm...
- bpicolo 11y agoAnd 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 11y agoNot 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.
- arianvanp 11y agoI 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
- bni 11y agoWhat 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 11y agoYou 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.
- jacques_chester 11y agoAnd, in fact, it's best practice to never expose your physical schema to applications.
- spacemanmatt 11y agoSo true. A good remote facade decouples the usage interface from the physical schema, giving you quite a bit of flexibility.
- kelseydh 11y agoImplementing 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.
- glogla 11y agoYou're right. It kind of depends on whether you look on the DB as a datastore, that is the DB and the Rails app are the same "system" with regards to other parts of the infrastructure, or of the database is system by itself, that provides services to other systems. It depends on what the DB does, really - eshop app + db might be the first one, while operational data warehouse would be the second. Usually you end up with something in the middle - like the eshop app, that provides views with summarized data to be ETLed to reporting.
- gizmodo59 11y agoWith 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/2rest https://community.denodo.com/tutorials/browse/dataservices/2...
- cies 11y agoI'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 11y agoI've found that Spring Data REST makes wrapping a database pretty easy.
- McElroy 11y agoBetween 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 11y agoThat is entirely on purpose, too.
- McElroy 11y agoTell me about your devolution. (He said to ask him to in his profile.)
- spacemanmatt 11y agoIt's really going quite swimmingly! Thanks for asking.
- caseysoftware 11y agoAPIs 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 11y agoI 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.
- _lce0 11y agooh I love the silence logo!! In fact, I think I love any musical reference in software :-)
- xdanger 11y agoHow 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
- curiousjorge 11y agoThe 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 11y agoBest advice I ever got from any engineer that actually caused me to start completing projects was "complicate as necessary, not as desired..."
- jfarmer 11y agoAnd unless an engineer is psychic, this usually results in better, more robust products. Bonus! :D
- curiousjorge 11y agocan you elaborate what that quote means?
- WorldWideWayne 11y agoIt 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.
- marknadal 11y agoWow, 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.
- why-el 11y agoSplendid work, truly. The documentation is pure class and the whole library is extremely well prepared for actual use. Kudos to the developer.
- spacemanmatt 11y agoSince 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.
- dylanvalade 11y agoAfter visiting the demo my browser is running spyware.
- restya 11y agoOur 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)