5 ms·

I don't really understand the value of a project like PostgREST. It feels like you're coupling your application schema to your database schema, which is someth

• dventimihasura 4y ago
  I don't want to avoid that.
  • vore 4y ago
    Then I think you are in for a world of pain when you need to e.g. change how the underlying storage of your data looks but don't want to change the end user API. A lot of the time, the access patterns of an end user talking to your backend really don't match up to the access patterns of your backend talking to your database.
    • dventimihasura 4y ago
      I have a layer of indirection between my end user API and my underlying storage, so that I can change the storage without changing the API. There's no pain involved.
      • vore 4y ago
        Then... why not just use Postgres directly from your end user API's backend? You might as well use an ORM and cut out a layer of overhead from having to marshal data in an out of PostgREST and point of failure from having to run it.
        • dventimihasura 4y ago
          PostgreSQL + PostgREST IS my "API backend." They're one and the same. There are no other layers. Using an ORM would ADD a layer, not subtract one. Perhaps what you're asking is, "Why not just have your user interface connect directly to PostgreSQL and issue SQL statements?"
      • sokoloff 4y ago
        How does that square with: > I don't want to avoid [coupling my application schema to my database schema] It seems like you built a layer of indirection to specifically allow the thing you said you didn’t want to do a couple posts up. (I think your indirection layer is a good idea; I’m curious what your previous post meant in light of that.)
        • dventimihasura 4y ago
          It doesn't. I wasn't addressing "coupling your application schema to your database schema." I was addressing "change how the underlying storage of your data looks but don't want to change the end user API" in the parent comment. > It seems like you built a layer of indirection to specifically allow the thing you said you didn’t want to do a couple posts up No, because my layer of indirection is in the database in the form of views and procedures. I could be wrong, but I took "coupling my application schema to my database schema" to be something like "having your HTTP API depend on objects in the database", which it does because of the way that postgREST works. If that's the kind of coupling we're talking about, then that's the kind of coupling I would rather embrace than avoid.
• arnsholt 4y ago
  We’re considering it for a use case at work. In our case, it’s to allow a team of analysts to be more or less self sufficient in publishing some data for external consumption without needing to deal with deployments and the like. This way, changing requirements can be handled by the analysts themselves by updating the tables or views published by PostgRest, without needing to think about changing a REST service and such.
  • maerF0x0 4y ago
    Sounds like a security nightmare, highly recommend pairing them with a dedicated security minded person to ensure correct configurations of access control (networklayer/hosts, row level, resource denial of service etc) at the very least have a read of https://postgrest.org/en/stable/admin.html https://postgrest.org/en/stable/admin.html
    • arnsholt 4y ago
      The idea is to have the IT folks managing the configuration of both Postgres and PostgRest, the analysis folks will obviously be working in SQL only.
      • maerF0x0 4y ago
        > SQL only. I assume you mean readonly SQL then... (perhaps creating views too)
• xemoka 4y ago
  In the past, I’ve used a specific ‘api’ schema that contains the views and functions that modifies a ‘data’ schema. You can then have multiple versions of the ‘api’ schema for versioning (with a different postgrest instance pointing at each versioned api schema). It’s possible…
• pyuser583 4y ago
  Speed. There might be other advantages, but it will be much faster than any non-DB framework. I’d want to limit it to simple schemes. But if you’re only exposing one table- it would be pretty darn fast.
• taffer 4y ago
  The recommended way to use Postgrest is to put a layer of views and optionally stored functions on top of your schema to decouple it from your API. Take a look at this Postgrest starter kit[1] which uses a separate API schema for this purpose. [1] https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit
  • deleted 4y ago
    [deleted]
• mike_hearn 4y ago
  If you aren't writing a web app then you can potentially scrap the web server tier entirely, which can yield security and simplicity benefits in some cases. For example, any app where the userbase size is well known and stable e.g. internal apps, apps for medical, military, industrial use cases. In such a two-tier architecture you implement your business logic using either SQL or server extensions like PL/Java (https://tada.github.io/pljava/ https://tada.github.io/pljava/) and then provide users with a desktop or mobile app to access the database directly. PostgREST is useful for languages without good DB drivers, or where you need to traverse HTTP only firewalls/proxies. Advantages: • Get back all the time spent boilerplating and bikeshedding ad-hoc app specific REST protocols. • Eliminates the (near) superuser privileged web servers that pose a security risk if compromised. Eliminate SQL injection, XSS, XSRF as bug classes. • Allows smart users like business analysts to bypass the UI partially or completely and go straight to a SQL console, because end users = db users 1:1 and ACLs are understood by the RDBMS directly. • Use UI frameworks and languages that aren't JavaScript. Use context menus, menu bars, hotkeys OS services or whatever else makes your users productive. • Use multi-threading, files, special hardware as part of your core app architecture if you need it. • If you can afford the server side resources: align DB transaction length with UI "transaction" length. Obviously there are also downsides. I wouldn't write Instagram this way. Postgres doesn't scale very well to lots of connections. Oracle/MSSQL scale a lot better and have other advantages like much better blob support, but you'd have to get comfortable with the idea of building new apps on them. You can mix and match, it doesn't have to be purist. Retain a thin, simple and rarely updated web server that just handles the requests you don't send directly to the DB e.g. for things like ElasticSearch. Or if you can (i.e. not on Supabase) write custom Postgres extensions that let you use SQL stored procedures as your RPC protocol. It has some advantages over HTTP. Lately I've been experimenting with this design a bit. The traditional hassle has been non-web distribution to desktops. https://conveyor.hydraulic.dev/ https://conveyor.hydraulic.dev/ fixes that. If you're using something like Electron or the JVM you can do a build+release cycle for Win/Mac/Linux in about 60 seconds all from your dev laptop, and you can make installed clients do a fast update check on each launch just like a web app would. There are some open questions about the best way to handle user authentication when connecting direct to a DB if you don't want passwords. The nice thing is you can e.g. bind the results of SQL query or an ORM directly into your UI toolkit. JSON, REST, custom paging code and all the other goop CRUD apps end up with just boil away.
• marcosdumay 4y ago
  The database schema is much harder to change than anything on the application layer. That's unarguable. From there people mostly decide on two philosophies: "I'll write an adapter layer so that it's easy to change my data" and "I'll take those robust, fixed facts and write my application around handling them". Honestly, I have no idea if one of those is any better than the other. I can't even say with confidence that one will lead to problems that another won't; they look equivalent to me. The choice seems to be always made based on worldview, and it's not even one of those "fast and loose" vs. "methodical" choices. All the differences I see people pointing are false ones.
  • giraffe_lady 4y ago
    I've actually worked on a large complex postgrest-based backend and the cons are all based on practical considerations imo: - the dev workflow on a db-as-codebase system is less familiar, less well understood, with tooling general several years behind "normal" code work. - branching and deployments similarly are just different in ways it's hard to prepare for, leading to low confidence in the deployed system. - testing and debugging: pgtap has different constraints than normal unit testing, debugging sql functions is tricky and awkward. again the tooling is missing or far behind. - in most profitable applications I've seen, the DB is the single largest cost and the most likely to become a bottleneck you can't loosen by throwing money at servers. having all your logic in there won't *necessarily* make this worse but it certainly won't make it better. DBAs have dealt with all of these things for decades and they have skills and tools and mental models for them. But devs and DBAs practice different disciplines with different goals, and not everything crosses over easily. Engineers working on a system like this from either side will end up acquiring a degree of competence even expertise in the other one. Making them desirable for other employers and difficult to replace. Overall I don't strictly prefer this approach, but it definitely has under appreciated strengths and should probably be used more. It's hard to say how it could end up if more resources were put into actually developing the tooling necessary to back it up.
    • taffer 4y ago
      > having all your logic in there won't necessarily make this worse but it certainly won't make it better. Logic is a very broad term, and as long as you're talking about number crunching / machine learning, I'd agree. But most web or LOB applications have pretty simple logic. According to Michael Stonbraker[1], a typical OLTP DBMS spends only 4% of its processing time doing useful work, which includes any kind of business logic, among other things. The rest is spent on housekeeping tasks such as context switching and transaction management. The more business logic you move out of the database, i.e. to the middle tier, the more roundtrips you need per transaction. During roundtrips, transactions can't do any meaningful work, which means more idle transactions, larger connection pool, more locking, and context switches. In other words, for typical OLTP workloads, each transaction should ideally occur in a single roundtrip, which requires the logic to reside within the DBMS. [1] https://blog.jooq.org/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ https://blog.jooq.org/mit-prof-michael-stonebraker-the-tradi...
• dimmke 4y ago
  So, I'm in the middle of building a backend for the first time and I was evaluating PostgREST just yesterday. Here's the value prop: Your database is the "source of truth" but can be accessed in many, MANY different ways. Usually via some kind of ORM system. This can give you a head start on building a more carefully considered REST API - where it gives you the base CRUD routes for every table in an acceptable format and you can build on top of it. Or if you're accessing your DB through some other interface for your web app but need something quick to build a new face for the service like a mobile app. I recently ditched building a traditional REST API in favor of just using what my ORM provides to interact with my DB. Something like this will come in handy if I ever need one.
• justsomehnguy 4y ago
  There are niches where db schema IS the app schema. Some time ago I wrote a REST wrapper for the .NET SQL connectors which allowed me to post and query data from the database. It was more than enough for my usage and I could interact with that 'service' from anywhere in the network without bothering on installing and configuring the SQL connector on the endpoints.
• ravenstine 4y ago
  I could see this or something similar to it being beneficial for generating a REST API initially when the schema just happens to pretty closely fit what the REST API should be. But after that initial step, I wouldn't want my API dependent on the schema (directly), nor would I want my database schema dependent on the API code. As a side note, that's one reason I didn't take a liking to Django. Honestly, I'd rather have an RPC based API in the year 2023 than a REST API. REST, I think, was a bit of a mistake in terms of a source for data that would be sent to a stateful frontend as JSON. REST makes sense for webpages, but nothing about data is inherently page-like. I've run into enough quirks dealing with RESTful APIs and the libraries that claim to handle them that I think we should be looking for a better fit.
• KronisLV 4y ago
  > It feels like you're coupling your application schema to your database schema, which is something you generally want to avoid. This is an interesting statement that probably should be expanded more upon! I agree with it, because it can be nice to be able to change how certain data is returned to any consumers of your API, for convenience or maybe some business rules. For example, you might want to aggregate data from multiple tables into a single list of JSON objects for filling out a table in some application downstream. Furthermore, you might be interested in being able to change the underlying DB schema without affecting how your API returns data, since its consumers don't necessarily care about how you name your tables or what references what internally. At the same time, I do disagree with my own point somewhat, because you can just use a DB view for pretty much the same outcome. There's no reason why MyAppUserListViewEntity couldn't match my_app.user_list_view in your database 1:1, I'd actually argue that such a mapping for reading data would be really easy to reason about and the discoverability would be pretty good, while at the same time still letting you introduce changes as necessary. Furthermore, there's something really nice about codegen: being able to tell some generator where your local development instance of your database is running and generating application entities with all of the mappings (for example, JPA) with a single command, or doing the opposite and creating the schema from your entities. Sadly in most cases such technologies are underutilized and for whatever reason many out there still write their ORM mappings manually for something like Hibernate (or write dynamic SQL manually, with something like myBatis). In the end, I'm not sure. Coupling might mean issues down the road, but decoupling now might mean introducing a level of abstraction/indirection that might just be needless cruft, like the tendency that you sometimes see in Java projects, along the lines of: MyBusinessObject/Dto <--> SomeMapper <--> MyEntity <--> MyEntityDao <--> MyEntityMapper/Repository; Not saying that that's necessary OR that it's a bad approach, Java just has lots of codebases out there that end up with many abstractions, hence the example.
• majkinetor 4y ago
  The value is that you can get ultra peformant CRUD app supporint bunch of filtering operations OTB within an hour or so. If you developed one youreself, it would probably be slower TBH. Depending on what you do, this can be lifesaver or thing to avoid.
• jeff-davis 4y ago
  I'll answer in theory, because I haven't used it. And I assume there are lots of ways this theory breaks down in practice. The first (theoretical) benefit is that it removes a lot of redundancy. Databases already offer a lot of things applications do for themselves, and typically it's a best practice to do those things in the database anyway to guard against application bugs. For instance, defining CHECK constraints is a best practice regardless of application validation. (There's a lot of disagreement over where the DB/app boundary is and how much overlap there should be.) Second, databases can be declarative because they are managing the data itself. The presence of a constraint makes a guarantee about the data regardless of history (versions, changes, bugs, etc.). Similarly for declarative authorization (GRANT, RLS, etc.). Third, these benefits compound when dealing with many smaller, hastily-written applications.