22 ms·
PostgREST – A fully RESTful API from any existing PostgreSQL database
- andyhoang 10y agoWhat cases I should use this? I mean, look like it used for simple/beginning project, when ORM can do quite handy
- kornish 10y agoAn ORM is a library integrated into a language runtime. Postgrest is a service – a separate process – which sits in front of a Postgres database, offering a RESTful HTTP API over that database. This means web or mobile HTTP clients can access the database in a safe, controlled manner. Postgrest basically shifts the work of writing a basic CRUD API (a task for which you would probably use an ORM) to declaring a SQL schema. From that schema, it infers which endpoints should exist and what they should do. For a certain class of web app, this can be a HUGE time saver. Beyond that, consider checking out the "Motivation" section of the website: https://postgrest.com/en/v0.4/intro.html https://postgrest.com/en/v0.4/intro.html
- bpicolo 10y agoTo be fair, you could do the same with an abstraction layer over an ORM.
- travmatt 10y agoI'm designing a web app that doesn't need much actual backend, save some static data slightly too large to ship with the static files. I have nginx serving static files and proxying db requests to postgrest.
- topspin 10y agoWhat specific ORM do you have in mind when you write "handy?" I'd really like to know in case I've missed something.
- vikingcaffiene 10y agoThis is neat and I am going to definitely dig in an play around with it. I guess my biggest worry with projects like these is what happens when something breaks? If I decide to integrate a piece of tech into my stack, I need to be able to intimately understand what's going on under the hood. If something breaks or works in a way I don't expect, I need to know it well so I can diagnose and fix the problem. Something that abstracts away this much of the dirty work makes it less attractive to me for anything serious. Its also written in Haskell which is an awesome language I hear. However the syntax is foreign compared to "traditional" languages and its less well understood due to a smaller community. That means I can't just fork the code and fix a bug if I end up in a bind. Just seems kinda risky. Hope to be proven wrong because it is a really cool idea. Best of luck to the authors.
- kornish 10y agoNote for the submitter that "Show HN" is generally for things you yourself have made – see [0]. On the other hand, thanks for submitting; this is a great project and it deserves attention. Postgrest is a great example of a real-world Haskell codebase. It's concise for the amount of functionality it offers, which is characteristic of functional languages (<3000SLOC of source, not counting tests). I'd encourage anyone interested in working with non-toy functional codebases to take a look around with it, or better yet, submit a PR (there are a few "beginner" issues!). [0]: https://news.ycombinator.com/showhn.html https://news.ycombinator.com/showhn.html
- gothrowaway 10y ago> Postgrest is a great example of a real-world Haskell codebase. It's hard to read. Very messy. https://github.com/begriffs/postgrest/tree/master/src/PostgREST https://github.com/begriffs/postgrest/tree/master/src/PostgR... Despite 15 years of programming experience in python, js, C, etc. I feel like I'm going to have to duck my head down to be chastised for not understanding the language and not being "intelligent" enough to see the depth. It looks like jibberish, to me. Not trying to be offensive. I'm sure the person who written it had it make sense to them. You likely also notice, a lack of code documentation. Bad form. Don't tell me it's because I don't know haskell, that's why I'm not expending the time to learn it, despite the buzz. Meanwhile, SQLAlachemy and Hibernate isn't reporting complaints and a human being could actually parse it to understand what the hecks going on there. And despite it being Python or Java - easier and far more widely adopted languages - they're documented extensively, the authors didn't solipsistically assume others would "get it". Which is a pattern I've been seeing with hardcore functional advocates in communities. They are the kind of people who'd work 2 weeks on a paper for a mathematical proof, shove it to you in the hallway to look smart, and say "It's obvious". It's not, you're just trying to show you're smart, but no one's understanding you - and that is important in you winning people over and not looking arrogant. > It's concise for the amount of functionality it offers Make it 4 times as many lines. Because there is so much condensed inside of this. >8k stars. Less than 50 contributors. Of which, only the top 7 changes more than 100 lines. If this considered a real world haskell, it's no wonder there's not a lot of people using it. It does more to demonstrate functional programmers lack empathy for enterprise ones. Because even with solid grasp of CS concepts, even Haskell's own proponents are having a hard time stomaching contributing to it.
- twelve40 10y agoI guess this is like a lower-level version of Parse (on a different, transactional stack too). Pretty cool. I wonder though, often times I have cases that are mostly CRUD but with a little extra: e.g., "create this object and kick off a Stripe payment", or "create this object and send an MQ message". With Parse, you just write Node triggers to do that. Would I have to dig through Haskell code (or hire Haskell developers) to do the same here, or does postgrest support an easier way to do that?
- kornish 10y agoPostgres supports async notifications, so you could just write a little service which gets a message when a record changes, and put the Stripe logic there. https://postgrest.com/en/v0.4/intro.html#external-notification https://postgrest.com/en/v0.4/intro.html#external-notificati... Not as simple as Parse, I guess, but that's a tradeoff of using lower-level technology.
- begriffs 10y agoYou can trigger external actions by connecting PostgreSQL pubsub (LISTEN/NOTIFY) with an external job queue. https://postgrest.com/en/v0.4/intro.html#external-notification https://postgrest.com/en/v0.4/intro.html#external-notificati... A NOTIFY SQL command can be sent out from either a stored procedure or a table trigger. (The docs could use some examples of this, but that's the idea.)
- gnud 10y agoBe aware that Postgres' NOTIFY is not stored/queued in any way. If your external client is not listening, it will get lost. I would instead have the trigger insert a row into a "pending tasks" table - and then send a NOTIFY.
- lima 10y agohttp://debezium.io/ http://debezium.io/ is a better way to do this. It uses the logical decoding feature to get all row changes and writes them to a Kafka queue. This means it can pick up where it left when it crashes.
- fiatjaf 10y agoNot valid for Show HN.
- deleted 10y ago[deleted]
- Walkman 10y agoThis guy doesn't understand the point of a REST API at all. Why don't you simply give access to the database directly?
- dalailambda 10y agoA web page, for example, should not have direct access to the database.
- zepolen 10y agoIf an app can't have direct access to the database, why would letting it access via a web api be better?
- FooBarWidget 10y agoI can think of a few use cases. - The developer is writing apps in languages that do not have PostgreSQL drivers. Or the available PostgreSQL drivers have major drawbacks. HTTP libraries are pretty much ubiquitous, and the benefits of using HTTP might outweight the drawbacks of the existing drivers or the drawbacks of using HTTP. - HTTP as a protocol is very good in the sense that there is a lot of tooling around it for load balancing, proxying, security, etc. Depending on the skill level and distribution in the organization, it may make a lot of sense to use HTTP as a protocol for accessing the database so that certain aspects of security, high availability, etc. can be the responsibility of system administrators, rather than developers who must hack into the database driver. The two use cases above are not theoretical. Someone invented DBSlayer a decade ago, which is like a PostgREST for MySQL. You can read their rationale here: https://open.blogs.nytimes.com/2007/07/25/introducing-dbslayer/?_r=0 https://open.blogs.nytimes.com/2007/07/25/introducing-dbslay... And a third use case: - The author deliberately wants to expose a public database, as a public learning environment of some sort. No production data is stored in the database.
- ruslan_talpa 10y agobecause the web api (PostgREST) has a strict control on the types of queries the client is allowed to execute thus preventing DOS attack against the db that force it to run complicated/unoptimised joins or function that use a lot of CPU
- theprotocol 10y agoSome feedback: I need to see some kind of "big picture" usage highlights. I find it hard to picture what endpoints are generated based on the tables: does each table/row become a resource with its own url? How are relational queries and joins handled? I looked at the docs and they seem to discuss various concepts and other minutiae but there is no real overview that cuts through the fat. It's not immediately obvious to me how it fulfills its stated claim: > PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. > Using PostgREST is an alternative to manual CRUD programming. Custom API servers suffer problems. Writing business logic often duplicates, ignores or hobbles database structure. Object-relational mapping is a leaky abstraction leading to slow imperative code. The PostgREST philosophy establishes a single declarative source of truth: the data itself. The second paragraph in particular is a pretty bold claim, so I find it strange that there is very little elaboration on how this project can eliminate the need for custom APIs.
- ruslan_talpa 10y agoYou are right in saying that the docs lack "the big picture", but that will be fixed soon(ish). The big picture is that it's not PostgREST alone that accomplishes this "big claim" of eliminating the need for custom APIs. It's the combination of using openresty(nginx)/postgrest/postgres/rabbitmq together that gives you the possibility of "defining" apis rather then "manually coding" apis.
- theprotocol 10y agoSounds like an interesting tightrope walk. I feel a bit of concern about the number of moving parts being part of a single solution, having configured similar selections of software myself, but I'll wait and see. Best of luck.
- ruslan_talpa 10y agohttps://lobste.rs/s/g6cu5r/rest_api_haskell/comments/ecggfa#c_ecggfa https://lobste.rs/s/g6cu5r/rest_api_haskell/comments/ecggfa#...
- manojlds 10y agoWanted a throwaway API recently and then finally realized that this cannot be deployed to Heroku with the PG add-on. Disappointing.
- postila 10y agoWhy?
- apapli 10y agoOut of interest is there an equivalent of this for MySQL?
- davidlee1435 10y agoHere's a Node-based framework: http://loopback.io/ http://loopback.io/
- travisilu 10y agoI think this framework is a better way if clients need access database via RESTful API. At least, it can encapsulate business logic easily to be a microservice. https://www.reddit.com/r/ruby/comments/61bb6h/squad_simple_efficient_restful_framework_in_ruby/ https://www.reddit.com/r/ruby/comments/61bb6h/squad_simple_e...
- Entangled 10y agoIs it possible to include YAML in the response format? - [name, age, sex, phone] - [Taylor Swift, 27, female, 555-SWIFT] I find YAML to be the best format for everything.
- throwme321 10y agoSince you can get JSON and JSON is a subset of YAML, you also have YAML.
- merricksb 10y agoPrevious discussions: https://news.ycombinator.com/item?id=9927771 https://news.ycombinator.com/item?id=9927771 (613 days ago) https://news.ycombinator.com/item?id=8831960 https://news.ycombinator.com/item?id=8831960 (812 days ago)
- pella 10y agoAlternative: pREST : GOlang based RESTful API ( PostgreSQL database ) : https://github.com/nuveo/prest https://github.com/nuveo/prest http://postgres.rest/ http://postgres.rest/ "Problem: There is the PostgREST written in haskell, keep a haskell software in production is not easy job, with this need that was born the pREST."
- Mister_Snuggles 10y agoNot a Haskell user, but what makes it difficult to keep it in Production?
- buckie 10y agoNothing. I honestly have no idea what they're talking about. Haskell apps tend to be tanks once they're deployed. The only issue I've ever seen was a Haskell app getting OOMed because the dev box only had 8GB of RAM and someone deployed some data science infra to the box sometime later (which needed >8GB RAM). I think this statement is more accurate: > "Problem: There is the PostgREST written in Haskell, not in Go" Moreover, if you want to use something like PostgREST but need to extend it, and don't know Haskell, this is a completely legitimate problem. While I'm all for competing implementations, the pREST author's claim for why it's needed is inaccurate.
- Mister_Snuggles 10y agoExtension of something written in a language like Haskell[0] definitely poses challenges. Even if the language makes it un-modifiable in your environment, there's no reason it can't be treated like a black-box component and used. [0] By "like Haskell", I really mean "unreadable/unwritable by the team supporting it". This could apply equally to Ruby, Java, bash, SQL, JavaScript, and Python depending on the makeup of the team supporting it.
- jose_zap 10y agoWhat's difficult about deploying a single binary?
- lauretas 10y agoIs there anything like this for SPARQL endpoints?
- scosman 10y agoOdd choice to push JSON serialization onto the DB while touting horizontal scaling. Still pretty cool that this much is possible with Postgres and a minimal frontend.
- ruslan_talpa 10y agoDoing json serialisation in the db is the only way to extract tree like data from the db. Another point to consider is that this is a much lower burden on the database then you think (roughly speaking, it adds 15%-20% more cpu load then a normal query), this is C code doing this, can't get much faster that. This type of load is easily horizontally scalable using read replicas which in RDS is basically one click. But forget all that talk about horizontal scalability, 99% of the projects will never outgrow a single (big) database so it's no use in complicating things with "elastic" setups and "webscale". People don't realise just how fast postgres is http://akorotkov.github.io/blog/2016/05/09/scalability-towards-millions-tps/ http://akorotkov.github.io/blog/2016/05/09/scalability-towar... Who among us have worked on projects that have 1M queries per second, not many.
- DrJokepu 10y agoIt's not really the only way (PostgreSQL has recursive queries), but it's definitely the most convenient (and in many cases the most efficient) way if you're not worried about referential integrity.
- ruslan_talpa 10y agoCare to explain how you would extract in a single query tree like data (without duplicate data going over the wire)?
- willglynn 10y agoI use `WITH RECURSIVE` to traverse trees: https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with....
- ezekg 10y agoWhat's the use case for something like this? The claim that it writes APIs better than I could by hand doesn't make a lot of sense to me--writing an API-ORM-thing, sure, but not a non-trivial API. I've never built an API that is simply a CRUD front-end to a database--there's always business logic + the output of the API very rarely matches the database tables underneath e.g. you may be rendering 2-3 different models, but that's never revealed to the end-user.
- ruslan_talpa 10y agoDon't confuse PostgREST with something that you just point at a (poorly designed) database schema and magic happens and you get a nice api. You point it to a schema that consist only of views (that you define) and stored procedures (that you write) that abstract away the underlying tables. You define constraints on all your columns so junk does not get into the db. You define database roles and RLS policies and give them privileges so that you control who has access to what. You still in a way write backend code, but in this case backend code is mostly views/constraints/triggers and in rare cases stored procedures.
- ezekg 10y agoI see. Either way, that's not really obvious from the readme or the tagline, "REST API from any existing PostgreSQL database."
- dsc__ 10y agoCan't help to ask myself "What problem is this project solving" - Perhaps so that more front-end oriented folks can easily access a data source otherwise only exposed by SQL.