17 ms·

PostgREST

• ruslan_talpa 7y ago
  Have you just discovered this and posted to HN? :)
  • skrebbel 7y ago
    That's roughly the idea of this site, right :-)
  • pictur 7y ago
    So?
    • ruslan_talpa 7y ago
      Nothing wrong with it, it’s just weird to me seing a link directly to the homepage of a product that’s been around for 5 years now, and not something like “how to do x with postgrest”. Don’t get me wrong, it’s a good thing, just a little weird (in a good way) to me
      • pbreit 7y ago
        I guess Hacker News is a little quirky like this. Your first comment seemed weirder.
        • ruslan_talpa 7y ago
          Yeah i know, didn’t know ho to exactly express the feeling (nice surprise)
    • ben_jones 7y ago
      The frequency by which this project appears at the top of HN does not correlate with its production usage and thus feels like gorilla marketing, at least to me.
      • detaro 7y ago
        Looking through the search results, it seems like it's been potentially high on the front page 3 times in 4 years.
        • ruslan_talpa 7y ago
          That was the surprise part behind my other (downvoted) comment, surprised that it still comes up on homepage with direct links (as oposed to some new development around it)
      • ruslan_talpa 7y ago
        It’s not marketing (100% sure :). Do you have some info on it’s usage in production?
      • mlyle 7y ago
        Damn gorillas.
      • james_s_tayler 7y ago
        I don't see why you're going so ape about it?
      • deleted 7y ago
        [deleted]
  • bdcravens 7y ago
    Older projects regularly find their way to the front of HN. For example, recently the NeverSSL project was on the front page. It has been submitted 10 times over the past 3 years (the homepage, not updates), and has been on the front page of HN before.
• haolez 7y ago
  I think PostgREST is the first big tool written in Haskell that I’ve used in production. From my experience, it’s flawless. Kudos to the team.
  • antpls 7y ago
    Having a bit of experience with OCaml, I hoped to see what production-ready Haskell code looked like with this library. I tried to read some files of the project and... IMHO "production-ready" Haskell code is still not easily readable, for example, the main file for the tests : https://github.com/PostgREST/postgrest/blob/master/test/Main.hs https://github.com/PostgREST/postgrest/blob/master/test/Main... and https://github.com/PostgREST/postgrest/blob/master/test/QueryCost.hs https://github.com/PostgREST/postgrest/blob/master/test/Quer... I don't know, maybe it lacks comments ? The code is really not easy to follow if you are not using Haskell 100% of your coding time. While the library may work well in practice, it's a maintainability red flag and, by using this library, you rely on rare Haskell programmers for the future.
    • haolez 7y ago
      I’m just a user and o have no visibility in the internal code (I can’t code in Haskell). What I feel, as a user, is that most features that I use are already implemented and unlikely to bit rot, since PostgreSQL itself doesn’t change a lot.
    • ruslan_talpa 7y ago
      it is hard to read if you don't have some knowledge of haskell indeed and the comments part is true but it's not any harder then folowing other codebases if you don't know the particular language so i don't think this is a strong argument. Another point is - it's not a library and you are not the one maintaining it :) the same way you are not maintaining, but still using things like postgresql,nginx,redis, rabbitmq. I bet it's a lot easier to learn haskell and patch postgrest then to know C for 10 years and patch postgresql :)
      • antpls 7y ago
• rgbrgb 7y ago
  Related graphql implementations with similar concepts: - https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ - https://hasura.io/ https://hasura.io/ Love the idea of having APIs flow out of a single set of schema definitions. The Rails style of speccing a model, migrations, and controller/serializer or graphql types feels overly verbose and repetitive. To me the biggest thing these groups could do to speed adoption is flesh out the feature development / test story. For instance, the postgraphile examples have development scripts that constantly clear out the DB and no tests. Compared to Rails, it's hard to imagine how you'd iterate on a product. Are there other reasons this hasn't seen more widespread adoption? Is there some inherent architectural flaw or just not enough incremental benefit?
  • ruslan_talpa 7y ago
    This is a solved problem https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit It’just the tools you linked didnt prioritize the development workflow from the point of view od a backend developer (code in files, git, tests, migrations) but from the frontend developer perspective (ui to create tables)
    • ruslan_talpa 7y ago
      more on this topic here https://docs.subzero.cloud/iterative-development-workflow/ https://docs.subzero.cloud/iterative-development-workflow/
    • felixyz 7y ago
      The SubZero starter kit is great, and made this whole architecture seem practical to me. Thanks for your hard work here! (I'm currently using PostGraphile, but the starter kit is still an important tool for me.)
      • BenjieGillam 7y ago
        Have you tried the PostGraphile Starter? It’s not officially released yet but you can find out more in the Discord chat #announcements channel. https://discord.gg/graphile https://discord.gg/graphile
• korijn 7y ago
  How do you version your API with this kind of tooling? As in, how do you change the data model without breaking clients?
  • ruslan_talpa 7y ago
    you don't expose your tables directly, you expose a schema that consists only of views and stored procedures. If you really need a totally different version then you jsut exppose a new schema but more often it's the same situation as in graphql ecosystem, you jsut add a new column/view/procedure and don't delete the old one. Postgrest has the same power to describe what you want as a graphql api woudl have (by using it's select parameter)
    • mlthoughts2018 7y ago
      It’s interesting that the project’s own tutorials do not discuss any of that and not only demonstrate making the API schema exactly equal to table schemas, but go further and claim that adding intermediate business logic or using tools that mediate between the data and business logic, like ORMs, are bad abstractions that should be intentionally avoided. I mean, I agree with the strategy you state about views or stored procedures, but those are just in-database ways of achieving the same kinds of things you might prefer to write in a different language (thus ORM or query engine) because it puts the app or business logic all into the same version controlled system, leverages programming language ecosystems and tools that are often way more valuable than raw database programming (even in Postgres), etc. Basically, if PostgREST needs you to do the old tricks of views & stored procedures to manage an abstraction layer that safely allows the underlying data schema to change, I just don’t see the benefit over doing this in a much better language ecosystem, like Python, and using much better web server tools to generate the APIs. PostgREST looks much more useful for quick prototypes, internal use cases where schema breakage might be OK occasionally, or just mirroring & monitoring data as-is for ops and diagnostics. From a performance perspective, it might be fast enough for production, but that’s almost never as big a concern as managing the intermediate abstraction layer and associated app tooling. Does not look like a good idea for production applications that need an intermediate API layer adapting the data to the use case.
• oftenwrong 7y ago
  Why not use a more descriptive title? For example: "PostgREST: a web server that turns a PostgreSQL database into a REST API"
  • loeg 7y ago
    HN policy is generally to use the literal title of the linked website, deficient as it may be.
    • detaro 7y ago
      Given it's the project website, and it's prominently visible, adding the tagline ("Serve a RESTful API from any Postgres database") would be fair to be part of the title.
• stefanchrobot 7y ago
  Somebody in our team put this on production. I guess this solution has some merits if you need something quick, but in the long run it turned out to be painful. It's basically SQL over REST. Additionally, your DB schema becomes your API schema and that either means you force one for the purposes of the other or you build DB views to fix that.
  • deleted 7y ago
    [deleted]
  • ruslan_talpa 7y ago
    what's wrong with views (which should have been used formt he start)? What were the pain points?
    • bdcravens 7y ago
      They are great when used well, but non-materialized views can kill performance with large data sets.
      • doh 7y ago
        That's the same as saying "unoptimized selects can kill performance with large data sets". Of course they can. That's what optimization is for. We have quite large amount of data (100TB+ and trillions of rows at this point [0]) and no problem with views. [0] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex
      • ruslan_talpa 7y ago
        a view is nothing but a query, so if the view is "killing" the performace for you, running the same query from the client will not change anything, the porformance will get "killed" in the exact same way.
        • bdcravens 7y ago
          Yes, if you are running the same query. Some of the worst use of views I've seen involve massive joins without filters, and then filtering further down, so you end up working with a recordset in the millions of records rather than a few thousand.
• deepersprout 7y ago
  I really like this approach for mostly crud apps. What is missing is - something to conveniently version control database objects - something to conveniently debug stored procedures. Maybe directly from vscode or your preferred editor. If those two things get solved somehow, pg could be a really awesome application server.
  • ruslan_talpa 7y ago
    for the first one, read my other comments, this is solved. The second one, you can start here https://www.pgadmin.org/docs/pgadmin4/4.13/debugger.html https://www.pgadmin.org/docs/pgadmin4/4.13/debugger.html
    • deepersprout 7y ago
      > for the first one, read my other comments, this is solved. > The second one, you can start here https://www.pgadmin.org/docs/pgadmin4/4.13/debugger.html https://www.pgadmin.org/docs/pgadmin4/4.13/debugger.html I think Starter Kit and the pgAdmin debugger lack in convenience. If you write C# or node js code in your preferred editor, you can debug it there. You can debug your express routes, webapi or resteasy controllers in vscode/vs/eclipse/intellij without leaving the file you later commit to git. Starter Kit and the pgAdmin debugger are fine tools, but they come nowhere close to how you work with a js, C#, java, python or whatever you like codebase. The development workflow with stored procedures imho is broken, and I think that is one of the main reasons people do not use them much.
      • ruslan_talpa 7y ago
        It's true that a tool developed by 1-2 ppl recently is not a convenint as the tools developed by armies of developers over decades :) but, when it comes to PostgREST way of building apis, the debugging does not have the same meaning as in other ecosystems. an api backed by postgres+postgrest is 80% tables and views declarations ... how do you debug a view ... it makes no sense. You just define it and say "select * from view" (even from your IDE) and see if you get what you expect, that's why one can do a lot (develop complex apis) with less (limited debug tools)
• deleted 7y ago
  [deleted]
• pointlessjon 7y ago
  I like this idea. Especially helpful for prototyping a web UI against an arbitrary existing dataset. PostgREST is much more full featured and as commented by a few others “production-ready”(?) but if you’re into this and looking for something a bit more naive but just as accessible I wrote a similar utility to expose some Postgres data over http: https://github.com/daetal-us/grotto https://github.com/daetal-us/grotto
  • steve-chavez 7y ago
    I'd say it has been production-ready for some years now. There are some documented cases of companies using it in production here: http://postgrest.org/en/v6.0/#in-production http://postgrest.org/en/v6.0/#in-production.
• hudo 7y ago
  "Object-relational mapping is a leaky abstraction leading to slow imperative code" So they added REST on top of ORM, few more layers of data transformation and even leakier abstraction, so poor dev doesn't have to worry about "low level" SQL. I lost count of how many different libs/frameworks i saw that exposed CRUD through HTTP, all failed miserably, because it is actually very dumb idea.
  • ruslan_talpa 7y ago
    postgrest is not (and does not use) a ORM Postgrest is more like a compiler, it takes one language as input (REST) and outputs another language (SQL) as output. It has 0 relation to the ORM concept
    • arithma 7y ago
      "ORM" is more like a compiler, it takes one language/(api can be seen as a language) as input and outputs another language (SQL) as output.
      • blondin 7y ago
        totally in agreement with you here. an ORM could also be viewed as a compiler. the original comment didn't deserve the backlash.
    • emrhzc 7y ago
      "PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API." How did you manage to understand the completely opposite from the very first definition?
      • ruslan_talpa 7y ago
        Becasue i know the codebase maybe :)? How does the tag line say "postgrest is a ORM" and how am i interpreting it in the exact oposite way?
  • fiatjaf 7y ago
• jayd16 7y ago
  So why use REST at all at this point? What is the benefit REST is bringing to the table here? Seems like if you want a declarative API you might as well do something like a local read replica a la Firebase. Seems like the natural progression of these API as single schema technologies. Is the main reasons for sticking to REST here compatibility or is there something in the RESTful design we want to hold on to?
  • z3t4 7y ago
    REST it pretty stupid, but it works over HTTP(S) and is state-less. And tools that use HTTP has nice abstraction layers already, and are very common, so it becomes simple to use. Personally for talking with a web front-end I would use Websocket's with long-polling as fallback. And use JSON instead of query-string for querying. It does however require yet an abstraction layer, and is more brittle and less secure then REST. REST is a school-bus. Other methods are like exotic sports-cars.
    • jayd16 7y ago
      Well sure it's common but my question is whether that's the only reason. If we're going to down the path of declarative requests and the like, why not push it further like firebase has done? I'd prefer that a lot more if there was a self hosted/open source alternative.
• z3t4 7y ago
  Often you do not want users to have access to a whole table, but only posts made by the user, or posts to to user. I could however see this replace Excel apps. But then you will also have to generate the user interface for it to be useful. The developer should only have to specify the views, the rest can be automated. I once made such a tool in order to save a few hundred man-hours on a tight budget, and it worked fairly well. But for most apps you want to customize every layer.
  • wichert 7y ago
    You can do that with row-level security. The PostgREST documentation has examples for that specific use case: https://postgrest.org/en/v6.0/auth.html#roles-for-each-web-user https://postgrest.org/en/v6.0/auth.html#roles-for-each-web-u...
    • fauigerzigerk 7y ago
      It sounds like this would limit scalability quite a bit because you'd either have to keep a DB connection open for each active user or close connections rather aggressively.
      • ruslan_talpa 7y ago
        The row level security is a feature of the database (postgresql), those rules are written and enforced by the database, they have nothing to do with PostgREST and how it connects to the database
        • deleted 7y ago
          [deleted]
        • fauigerzigerk 7y ago
          I am aware of that but I thought that this approach would effectively prevent sharing pooled connections between different users. But taffer says otherwise, so that solves the problem I was wondering about.
• siquick 7y ago
  What’s the benefit of using this over just using a small framework like Flask/Express with a Postgres lib?
  • doh 7y ago
    Derek recently wrote about it [0]. The fact that you can have the thinnest client between your front-end and backend makes things incredibly flexible. If you learn PG properly you can do 100% of data preparation on the database side and just expose it through an API. If you decide to change something, you change a view and it's now whatever you just did. No code changes, no redeployments. [0] https://sivers.org/pg2 https://sivers.org/pg2
    • ruslan_talpa 7y ago
      That is all true but postgrest goes a step further then Dereks approach (where you need to basically write a lot of stored procedures) and gives the fronend access to safe subset of SQL, so that frontend is not strictly limited to the capabilites of the available procedures that live in the db (thus eliminating the need for most of them)
      • doh 7y ago
        Derek's approach is one that can be carried between many old version of PG. Today, you can achieve a lot more with generated columns, (materialized) views, partitioning and other fun features added in recent versions. In any way, we used this approach in our company dealing with billions of rows of data and this allowed us to scale way past our "weight class".
        • ruslan_talpa 7y ago
          Thanks for this. People are always skeptical of this approach, not because the tried it and failed (or even thought 5 minutes about it) but because they read some blogpost somewhere. Not to say though that this solves everything, there are cases where it does not work (as someone commented correctly and gave an example where they needed to use linear algebra over the data)
• mrmonkeyman 7y ago
  Yo dawg, we heard you like abstractions so we put an abstraction on top of an abstraction.
• kissgyorgy 7y ago
  Don't do this with a public API with third-party clients!! This way you are directly tying the REST API to your database schema. The whole point of having a public API (you know, Application Programming Interface) is that you can serve your data in a controlled way, maybe totally different from your schema. In the moment you change a little bit on your schema, congratulations, you broke all clients.
• cik 7y ago
  I love the idea - and it's definitely something I'll put through the paces on one of my projects shortly. Being able to separate the schema from data ingestion, and data transmission is a very powerful scale option for one of the things I'm playing with.
• thijsvandien 7y ago
  Somewhat related discussion from a week ago: https://news.ycombinator.com/item?id=21362190 https://news.ycombinator.com/item?id=21362190
• janeshmane 7y ago
  This is intriguing, but how does one go about scaling this? Relational DBs are often where scaling breaks down and sticking more of the application in that problematic part of the stack seems like it could end poorly...
• ijidak 7y ago
  I've wanted this for a long time. Is there anything like this for Microsoft SQL Server?
  • steve-chavez 7y ago
    You could use PostgreSQL Foreign data wrappers[1] and leverage PostgREST for a SQL Server schema. tds_fdw[2] works pretty well for this(I've used it in a project related to open data). Basically, you'd have to map mssql tables to pg foreign tables[3] defined on a pg schema. Lastly expose this pg schema through PostgREST. [1]: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers [2]: https://github.com/tds-fdw/tds_fdw/ https://github.com/tds-fdw/tds_fdw/ [3]: https://github.com/tds-fdw/tds_fdw/blob/master/ForeignTableCreation.md#example https://github.com/tds-fdw/tds_fdw/blob/master/ForeignTableC...
• louis8799 7y ago
  I am not sure if it is a good idea to add PostgREST to your stack. As PostgREST can only interact with your DB, so you would probably be calling PostgREST from another REST. In this case, you would be better off using ORM.
• hippich 7y ago
  I am (slowly) working on a project with similar tool (postgraphile) to eliminate most of CRUD stuff. One thing I always wondered - how you would version control the schema itself? I settled on Skitch - https://sqitch.org/ https://sqitch.org/
• mleonhard 7y ago
  I'm using the JOOQ type-safe SQL generator with PostgreSQL. My application server build script runs PostgreSQL in a Docker container, creates the database and tables, applies all migrations (via Flyway), and then invokes JOOQ which connects to the database and creates Java classes based on the tables and columns in the database. JOOQ mostly prevents SQL syntax errors, column name errors, column type errors, supplying the wrong number of arguments, etc. These become compile-time errors. With PostgREST and other JSON APIs, you only get run-time errors. And you rely on test coverage to check code correctness. I prefer compile-time errors to runtime-errors. I find that software utilizing comnpile-time checks is easier to maintain.
  • steve-chavez 7y ago
    PostgreSQL already gives you SQL syntax errors(try creating a VIEW with a misspelled SELCT), column type errors(try doing a `select 'asdf'::int;`), wrong number of arguments on a sp call(try putting one more argument to `select int4_sum(2, 3);`). Thanks to PostgreSQL transactional DDL[1] you would get all of these errors at creation-time and without any change to your database if any migration is wrong. There's no need for a SQL codegen to get this already included safety. Btw, PostgREST is not only a JSON API. Out of the box, it supports CSV, plain text and binary output and it's extendable for supporting other media types[2]. If you have to output xml by using pg xml functions you can do so with PostgREST. [1]: https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr... [2]: http://postgrest.org/en/v6.0/configuration.html#raw-media-types http://postgrest.org/en/v6.0/configuration.html#raw-media-ty...
• no_wizard 7y ago
  Interesting to me I’d this is written in Haskell! I highly recommend reading the source code https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest
• jitans 7y ago
  This should have been: PostgGRPC
• consultSKI 7y ago
  This is so smart. Common sense to the max! Sad I didn't think of it.
• biolurker1 7y ago
  so basically this is like Firebase but in RDBMS which is quite awesome
• arunc 7y ago
  How is the REST API document generated? Looks neat!