7 ms·

In reality postgREST sucks... it's fine for simple apps, but for something bigger its pain in the butt. * there is no column level security – e.g. I want to sh

In reality postgREST sucks... it's fine for simple apps, but for something bigger its pain in the butt. * there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely). * with the above, you need to create separate views for each role or maintain complex functions that either render a column or return nothing. * then when you update views, you need to write sql... and you can't just write SQL, restart server and see it applied. You need to execute that SQL, meaning you probably need a migration file for prod system. * with each new migration it's very easy to loose context of what's going on and who changed what. * me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit. * writing functions in PlSQL is plain ass, testing them is even harder. I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production. Like when you can have a flask kind of app build in whatever metalanguage w/ ability to easily write tests, then and only then postgREST would be useful for large-scale systems. For us, it's just easier to build factories that generate collection/item endpoints w/ a small config change.

lishzen 2y ago
We solved some of this by having a "src" folder with subfolders: "functions", "triggers" and "views". Then a update-src.sql script that drops all of those and recreates them from source files. This way we can track history with git and ensure a database has the latest version of them by running the script and tests (using pgtap and pg_prove).
dragonwriter 2y ago
> there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely). Its a feature postgres has. GRANT SELECT ON public_table TO webuser; REVOKE SELECT ON public_table (payment_total) FROM webuser; Not sure why you think it doesn't exist or doesn't work with PostgREST.
- th0ma5 2y ago
  So either the web app has one policy as a whole, or you have to pass authentication to the database?
  - dragonwriter 2y ago
    The first is an option, but the second isn't if you choose not to do the first, because PostgREST is designed to handle this: https://postgrest.org/en/v12/references/auth.html#jwt-impersonation https://postgrest.org/en/v12/references/auth.html#jwt-impers...
  - everforward 2y ago
    I don’t think passing auth to the database is crazy. It’s almost an exact parallel to filesystems, which everyone is fine with. It’s far weirder to me is that the accepted status quo is to run transactions as a user that can read anything, and then try to have the app filter out stuff the user shouldn’t see. Just make the transaction run as a user that only has permissions to read what you want, and stop worrying about whether your auth system handles edge cases. This status quo is like having a filesystem where everything is owned by root, then writing a Fuse driver that lets users read those file by trying to infer permissions from the path. It’s a weird thing. I’ve always assumed the reason databases had such poor access control (or little usage of the good access control) was because it was too slow in ye olden days when disks were slow, clock cycles were limited, and read replicas were not yet common. In the modern world, I don’t know why anyone would prefer that their database can leak data in SQL injection attacks. The costs seem low, and the benefits seem high.
    - encoderer 2y ago
      What happens when I’m allowed to view a column for some entities but not others? In the real world authorization logic can get very complicated. And who wants to migrate this kind of stuff once you’ve implemented it one way? Honestly it’s best to keep your database doing what it does best: storing data.
      - 2y ago
gregw2 2y ago
"I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production." There is. Liquibase (or Flyway, or...) Add it to your build/CICD/deployment process. I have used it even for TDD stored procedure automated integration testing + CICD deployment alongside .jar/(JVM-using Scala) regular code + SQL DML/DDL deployment for a serious production app.
hobofan 2y ago
Yeah, I fully agree. The tooling for putting that much logic into the database is just not great. I've been decently happy with Sqitch[0] for DB change management, but even with that you don't really get a good basis for testing some of the logic you could otherwise test in isolation in app code. I've also tried to rely heavily on the database handling security and authorization, but as soon as you start to do somewhat non-trivial attribute-/relationship-based authorization (as you would find in many products nowadays), it really isn't fun anymore, and you spend a lot of the time you saved on manually building backend routes on trying to fit you authz model into those basic primitives (and avoiding performance bottlenecks). Especially compared to other modern authz solutions like OPA[1] or oso[2] it really doesn't stack up. [0]: https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch [1]: https://www.openpolicyagent.org https://www.openpolicyagent.org [2]: https://www.osohq.com https://www.osohq.com
steve-chavez 2y ago
> * there is no column level security – e.g. I want to show payment_total to admin, but not to user I think what you want is dynamic data masking. RDMBSes like IBM DB2[1] and SQL Server[2] offer it out of the box. For PostgreSQL, there's the postgresql_anonymizer[3] extension. For which you could do something like: SECURITY LABEL FOR user ON COLUMN invoice.payment_total IS 'MASKED WITH VALUE $$CONFIDENTIAL$$'; Last time I tried it though, it didn't play well with transaction variables (`current_setting(my.username)`), so you could not combine it with RLS logic and application users[4]. I'll be exploring an alternative on https://github.com/steve-chavez/pg_masking https://github.com/steve-chavez/pg_masking. To be fair, this discussion is more related to a PostgreSQL feature than PostgREST. PostgREST relies on PostgreSQL for all authorization logic by design. [1]: https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-create-mask#db2z_sql_createmask__title__8 https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-... [2]: https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16#granular-permission-examples https://learn.microsoft.com/en-us/sql/relational-databases/s... [3]: https://postgresql-anonymizer.readthedocs.io/en/latest/declare_masking_rules/ https://postgresql-anonymizer.readthedocs.io/en/latest/decla... [4]: https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/ https://www.2ndquadrant.com/en/blog/application-users-vs-row...
topspin 2y ago
When I used PostgREST for a readonly b2b API I recall that the recommended way (from the PostgREST docs) to employ it was creating postgresql schemas for different use cases: you create a schema that has views for tables in another schema and PostgREST exposes only the views in that schema. The views defined in the schema can, naturally, control what columns are visible, or provide whatever other abstractions one might want. I suspect a lot of people ignore this documentation and expose tables directly, and then wonder where/how to control such things. Yes, my memory is correct: from the PostgREST "Schema Isolation" documentation: "A PostgREST instance exposes all the tables, views, and stored procedures of a single PostgreSQL schema (a namespace of database objects). This means private data or implementation details can go inside different private schemas and be invisible to HTTP clients. It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world. This allows you to change the internals of your schema and maintain backwards compatibility. It also keeps your code easier to refactor, and provides a natural way to do API versioning." This has all been embiggened since I last used it. Now you can configure multiple schemas to be exposed. The active user ROLE (as determined by auth) controls access to exposed schemas, and a header in HTTP requests can specify the desired schema. Given all of this, it is entirely possible to achieve precise control over column visibility, version control, and surface whatever abstraction you can imagine. However, you are expected to fully inculcate PostgreSQL schemes, roles and other database affordances. This has always been the mentality of PostgREST: it's supposed to be a "function" that exposes some subset of a database without any magic beyond the database itself. Implicit in this is the need for adequate CI/CD tools to control database objects: you're doomed the minute you have >1 developer involved without such tools.
fatnoah 2y ago
>I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production. A long time ago, I joined a company where the "DB Migration Script" was an ever-growing set of migrations that took close to an hour to run. There were the same issues of lack of context, history, etc. Since we were a Microsoft shop, I took the following approach to use Visual Studio DB projects and the SQL Server sqlpackage tool. Every table, stored procedure, schema, user, index, etc. was represented by a single file in the project, so it would have full git history and DB changes and the code that relied on them would be in the same commmit. (Data migrations still had to be stored separately in up/down migration files) The "build" was to create the SQLPackage dacpac file from the DB project, and deploy was to apply the dacpac to the target database and then run data migrations (which were rare). Since the dacpac represented the desired end state of the database, it didn't require a DB to be in a specific state first, and it allowed the same deploy to run as part of CI, manual testing, staging deploy, and production deploys. It also generally took less than 5 seconds.
- jcrites 2y ago
  Why wouldn't the "script" be all of the necedssary commands to create the entire database? If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. It might be kept as a historical artifact, but why would you on average care beyond what the current schema is now, along with its documentation?
  - fatnoah 2y ago
    > Why wouldn't the "script" be all of the necedssary commands to create the entire database? That pre-dated me, so I have no idea. It's also why I simply jettisoned the schema migration script altogether, since the dacpac covered both migrations and creating new databases. Some other "fun" things in the database were "expansion columns" just in case new columns were needed. Many tables had "int_1", "int_2", "int_3" etc that were not used, but ready to be used should the need arise.
  - 2y ago
ravenstine 2y ago
REST itself is crap, at least when it comes to "APIs" serving things besides webpages. There's quite literally no obvious reason that an API actually should be RESTful besides that a lot of web developers believe that it's easier to understand. If REST is so great, then why do you keep finding yourself relying on documentation in order to use it?
- tysonfurytoo 2y ago
  I won't say REST is perfect, but I much prefer it to an unstructured api where anything goes. You didn't suggest that, but you really didn't suggest any alternative. What's the alternative to relying on documentation? Is relying on documentation even a bad thing?
  - ravenstine 2y ago
    I didn't suggest an alternative because, while I have more specific opinions on that matter, almost any alternative a person pulls out of a hat would be superior to REST. > I much prefer it to an unstructured api where anything goes. You're entitled to your opinion, and while I'm sure you didn't mean it to be a straw man, it's essentially the type of straw man I hear a lot when I broach this subject. Whether an API is "unstructured" doesn't depend that much on what said API is advertised to be acting like. Plenty of RESTful APIs in the wild don't completely adhere to REST or supplemental "standards" like JSON:API. My point about documentation is that, because using a REST API inevitably means reading documentation, and because assumptions about a REST API cannot always be made, then one might as well abandon REST and build an API that doesn't include the extras of REST that are rarely necessary. This doesn't imply unstructuredness. Most programmers don't like building things that don't have a useful amount of predictability to them, so to me the worry about structure is actually concern over very junior programmers doing very junior programmery things. I'm just not interested in that problem, and I don't think most programmers need to be. So let's just say a programmer, or a team of programmers, implement an API that uses their own convention that they invented, and they provide extremely readable and thorough documentation. Where's the problem? Documentation is a necessity. One of my arguments against REST is that it implies a high amount of intuitiveness that it can only even attempt to possess with extremely simplistic data. As soon as it makes sense to have a POST endpoint that acts more like an RPC than a REST API, that throws the entire decision to adhere to REST under question, and that sort of thing is not uncommon.
Merad 2y ago
Many of the migration tools I've worked with include the concept of scripts that are run every time migrations are applied, in addition to your standard migration files. So things like views, functions, SPs can go in these "every time" files rather than being duplicated in migrations every time they change.
est 2y ago
> In reality postgREST sucks... it's fine for simple apps, but for something bigger its pain in the butt. In reality REST sucks. It should and only be used for files like WebDAV or S3.
- starttoaster 2y ago
  I hear this sentiment echoed a fair amount without really saying why it sucks. And sometimes I wonder if the writer omitted it for brevity, or is actually just echoing something they read on the internet without really believing or understanding it themselves, because saying something sucks where clearly other people don’t know that it sucks (or else why would it be so popular) positions the writer in a dialogue as mysteriously knowledgeable. Hard to argue against points you don’t even know the other person is making and all.
  - tucnak 2y ago
    Yeah, he omitted it for brevity alright & you would just have to deal with it. Some day you might, too, become Mysterio Mister Knowledgeable
  - est 2y ago
    > without really saying why it sucks I think you've heard quite a lot. There is a reason why people invent GraphQL or BFF In a nutshell, I think today's Web APIs can't be simply descriped as a bunch of "resources" or "states" (which Roy Fielding used in his PhD paper about REST). A database system like PG is much more complex. The SQL itself is an API and comes with a data transfer protocol. You get a leaky abstraction by mapping it to REST.
    - dventimi 2y ago
      > There is a reason why people invent GraphQL or BFF Sure, it was because creating servers--REST or otherwise--with code (looking at you, Spring, Django, and RoR) is such a slow, laborsome task that rapidly iterating frontend teams got tired of waiting around for ill-fitting backends to catch up, that they built their own abstraction layers: BFF and then GraphQL. That wouldn't be a problem if backend teams were as nimble. Things like PostgREST, Prisma, Hasura, etc. allow them to be that nimble. Ironically, that brings folks like Wundergraph full circle, where they relegate GraphQL to being a development language for defining REST endpoints. If you're going to use a query language like GraphQL to define your REST services, then you might as well use the OG query language SQL to define your REST services, if circumstances allow. That's precisely what PostgREST does. So, yes, I have heard a lot arguments for why REST sucks. I just don't think they apply anymore (if they ever did).
sbstp 2y ago
The biggest issue with postgREST for me is that it doesn't support transactions well. You can't two 2 insert and keep a consistent state if one fails. That alone is a deal breaker.
- dventimi 2y ago
  Sure you can. Just write a function.
  - ecosystem 2y ago
    You posted a lot of comments in response to complaints or critiques of postgrest (almost every one at the time of my reading). Most of them are very terse, rude/dismissive, and in my view fall on the wrong side of hackernews etiquette. eg, "Sure you can. Just X" is not a educational or persuasive construction and neither are several other comments you've made on this submission. If nothing else, I'd encourage you to respond to the best version (steelman) of these critiques, which will improve the quality of discussion and be more persuasive.
    - dventimi 2y ago
      I encourage you to read the HN guidelines and then read the comments that I've replied to, because many of them could with considerable justification be regarded as unkind, snarky, and smug. I would also encourage you not to make and take things personally, and instead stick to the substance. Every one of my comments has been about the technological approach under consideration. If they're terse, it's because I don't want to waste people's time. If they're "not educational or persuasive" to you then you're free to disregard them, or say why exactly they fail. Plenty of the comments here, in my view, are "not educational or persuasive", so I said so and gave my reasons for having that view. I would encourage you to do the same.
- steve-chavez 2y ago
  You have explicit control over transactions with functions https://postgrest.org/en/latest/references/api/functions.html https://postgrest.org/en/latest/references/api/functions.htm.... I think this sentiment stems from users of postgrest-js[1], which is a JS library that gives an ORM feel to PostgREST requests. Under that abstraction, users don't realize they're using a REST API, instead of a direct postgres connection. So in this case users are really asking for "client-side transactions"[2], which are not supported in PostgREST. [1]: https://github.com/supabase/postgrest-js https://github.com/supabase/postgrest-js [2]: https://github.com/PostgREST/postgrest/issues/286 https://github.com/PostgREST/postgrest/issues/286
fzeindl 2y ago
Thanks for your response. You are right that care needs to be taken in the authentication layer. And yes, developing SQL in a shared environment is a complex task and needs to be accompanied by proper tooling.
naasking 2y ago
> there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely) So don't put it in the same table. > me and my colleague been making changes to the same view and we would override changes from each other You don't use version control and a test suite?
- CafeRacer 2y ago
  > So don't put it in the same table. Nope, this suggestion sucks. Meaning I have to come up w/ table structure that caters to front-end API. Better idea is to create schemas, e.g. `public_users` and create views inside. > You don't use version control and a test suite? that's what I'm saying... we have like a 1k migration files already, what I want instead is `my-function.sql` that always has the current code and will automatically generate necessary migrations and shenanigans, so I don't have to worry about it.
  - naasking 2y ago
    > Nope, this suggestion sucks. Meaning I have to come up w/ table structure that caters to front-end API. Yes custom schemas are preferable, but I think you're missing the point. If the only real cost of not having to write the font-end manually at all is massaging the db schema a little bit to partition access by role, that's still a pretty great tradeoff for a lot of scenarios. The system is simple to understand/audit and fast to create, the only apparent downside is that it rubs our sense of aesthetics the wrong way. > we have like a 1k migration files already, what I want instead is `my-function.sql` that always has the current code and will automatically generate necessary migrations and shenanigans, so I don't have to worry about it. I use entity framework a lot which does this sort of schema migration based on class definitions. It works well like 80% of the time, but when it doesn't it's annoying. In any case, what I was suggesting to address the specific program from your original post is that if you have lots of conflicting schema updates, then your table schema should be versioned and any migration should specify what schema version it depends on, eg. have a TableVersions table that names all of your tables and associates a version number with it. Your migration scripts then specify what version they were written against and fail if the number differs. It's the same basic optimistic concurrency control we use elsewhere in databases, just applied to schemas.
evanelias 2y ago
> * with each new migration it's very easy to loose context of what's going on and who changed what. > * me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit. > I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production. Declarative schema management solves this, and allows you to manage your database definitions (SQL CREATE statements) just like any other codebase: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment... With a declarative system, the tooling is responsible for diff'ing the CREATE statements between your current state and desired state, and then generating the appropriate DDL to transition between those states. My tool Skeema is specific to MySQL/MariaDB, but I've linked some solutions for Postgres here: https://news.ycombinator.com/item?id=39236111 https://news.ycombinator.com/item?id=39236111

lishzen 2y ago

We solved some of this by having a "src" folder with subfolders: "functions", "triggers" and "views". Then a update-src.sql script that drops all of those and recreates them from source files. This way we can track history with git and ensure a database has the latest version of them by running the script and tests (using pgtap and pg_prove).

dragonwriter 2y ago

> there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely). Its a feature postgres has. GRANT SELECT ON public_table TO webuser; REVOKE SELECT ON public_table (payment_total) FROM webuser; Not sure why you think it doesn't exist or doesn't work with PostgREST.

th0ma5 2y ago
So either the web app has one policy as a whole, or you have to pass authentication to the database?
- dragonwriter 2y ago
  The first is an option, but the second isn't if you choose not to do the first, because PostgREST is designed to handle this: https://postgrest.org/en/v12/references/auth.html#jwt-impersonation https://postgrest.org/en/v12/references/auth.html#jwt-impers...
- everforward 2y ago
  I don’t think passing auth to the database is crazy. It’s almost an exact parallel to filesystems, which everyone is fine with. It’s far weirder to me is that the accepted status quo is to run transactions as a user that can read anything, and then try to have the app filter out stuff the user shouldn’t see. Just make the transaction run as a user that only has permissions to read what you want, and stop worrying about whether your auth system handles edge cases. This status quo is like having a filesystem where everything is owned by root, then writing a Fuse driver that lets users read those file by trying to infer permissions from the path. It’s a weird thing. I’ve always assumed the reason databases had such poor access control (or little usage of the good access control) was because it was too slow in ye olden days when disks were slow, clock cycles were limited, and read replicas were not yet common. In the modern world, I don’t know why anyone would prefer that their database can leak data in SQL injection attacks. The costs seem low, and the benefits seem high.
  - encoderer 2y ago
    What happens when I’m allowed to view a column for some entities but not others? In the real world authorization logic can get very complicated. And who wants to migrate this kind of stuff once you’ve implemented it one way? Honestly it’s best to keep your database doing what it does best: storing data.
    - 2y ago

gregw2 2y ago

"I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production." There is. Liquibase (or Flyway, or...) Add it to your build/CICD/deployment process. I have used it even for TDD stored procedure automated integration testing + CICD deployment alongside .jar/(JVM-using Scala) regular code + SQL DML/DDL deployment for a serious production app.

hobofan 2y ago

Yeah, I fully agree. The tooling for putting that much logic into the database is just not great. I've been decently happy with Sqitch[0] for DB change management, but even with that you don't really get a good basis for testing some of the logic you could otherwise test in isolation in app code. I've also tried to rely heavily on the database handling security and authorization, but as soon as you start to do somewhat non-trivial attribute-/relationship-based authorization (as you would find in many products nowadays), it really isn't fun anymore, and you spend a lot of the time you saved on manually building backend routes on trying to fit you authz model into those basic primitives (and avoiding performance bottlenecks). Especially compared to other modern authz solutions like OPA[1] or oso[2] it really doesn't stack up. [0]: https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch [1]: https://www.openpolicyagent.org https://www.openpolicyagent.org [2]: https://www.osohq.com https://www.osohq.com

steve-chavez 2y ago

> * there is no column level security – e.g. I want to show payment_total to admin, but not to user I think what you want is dynamic data masking. RDMBSes like IBM DB2[1] and SQL Server[2] offer it out of the box. For PostgreSQL, there's the postgresql_anonymizer[3] extension. For which you could do something like: SECURITY LABEL FOR user ON COLUMN invoice.payment_total IS 'MASKED WITH VALUE $$CONFIDENTIAL$$'; Last time I tried it though, it didn't play well with transaction variables (`current_setting(my.username)`), so you could not combine it with RLS logic and application users[4]. I'll be exploring an alternative on https://github.com/steve-chavez/pg_masking https://github.com/steve-chavez/pg_masking. To be fair, this discussion is more related to a PostgreSQL feature than PostgREST. PostgREST relies on PostgreSQL for all authorization logic by design. [1]: https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-create-mask#db2z_sql_createmask__title__8 https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-... [2]: https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16#granular-permission-examples https://learn.microsoft.com/en-us/sql/relational-databases/s... [3]: https://postgresql-anonymizer.readthedocs.io/en/latest/declare_masking_rules/ https://postgresql-anonymizer.readthedocs.io/en/latest/decla... [4]: https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/ https://www.2ndquadrant.com/en/blog/application-users-vs-row...

topspin 2y ago

When I used PostgREST for a readonly b2b API I recall that the recommended way (from the PostgREST docs) to employ it was creating postgresql schemas for different use cases: you create a schema that has views for tables in another schema and PostgREST exposes only the views in that schema. The views defined in the schema can, naturally, control what columns are visible, or provide whatever other abstractions one might want. I suspect a lot of people ignore this documentation and expose tables directly, and then wonder where/how to control such things. Yes, my memory is correct: from the PostgREST "Schema Isolation" documentation: "A PostgREST instance exposes all the tables, views, and stored procedures of a single PostgreSQL schema (a namespace of database objects). This means private data or implementation details can go inside different private schemas and be invisible to HTTP clients. It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world. This allows you to change the internals of your schema and maintain backwards compatibility. It also keeps your code easier to refactor, and provides a natural way to do API versioning." This has all been embiggened since I last used it. Now you can configure multiple schemas to be exposed. The active user ROLE (as determined by auth) controls access to exposed schemas, and a header in HTTP requests can specify the desired schema. Given all of this, it is entirely possible to achieve precise control over column visibility, version control, and surface whatever abstraction you can imagine. However, you are expected to fully inculcate PostgreSQL schemes, roles and other database affordances. This has always been the mentality of PostgREST: it's supposed to be a "function" that exposes some subset of a database without any magic beyond the database itself. Implicit in this is the need for adequate CI/CD tools to control database objects: you're doomed the minute you have >1 developer involved without such tools.

fatnoah 2y ago

>I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production. A long time ago, I joined a company where the "DB Migration Script" was an ever-growing set of migrations that took close to an hour to run. There were the same issues of lack of context, history, etc. Since we were a Microsoft shop, I took the following approach to use Visual Studio DB projects and the SQL Server sqlpackage tool. Every table, stored procedure, schema, user, index, etc. was represented by a single file in the project, so it would have full git history and DB changes and the code that relied on them would be in the same commmit. (Data migrations still had to be stored separately in up/down migration files) The "build" was to create the SQLPackage dacpac file from the DB project, and deploy was to apply the dacpac to the target database and then run data migrations (which were rare). Since the dacpac represented the desired end state of the database, it didn't require a DB to be in a specific state first, and it allowed the same deploy to run as part of CI, manual testing, staging deploy, and production deploys. It also generally took less than 5 seconds.

jcrites 2y ago
Why wouldn't the "script" be all of the necedssary commands to create the entire database? If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. It might be kept as a historical artifact, but why would you on average care beyond what the current schema is now, along with its documentation?
- fatnoah 2y ago
  > Why wouldn't the "script" be all of the necedssary commands to create the entire database? That pre-dated me, so I have no idea. It's also why I simply jettisoned the schema migration script altogether, since the dacpac covered both migrations and creating new databases. Some other "fun" things in the database were "expansion columns" just in case new columns were needed. Many tables had "int_1", "int_2", "int_3" etc that were not used, but ready to be used should the need arise.
- 2y ago

ravenstine 2y ago

REST itself is crap, at least when it comes to "APIs" serving things besides webpages. There's quite literally no obvious reason that an API actually should be RESTful besides that a lot of web developers believe that it's easier to understand. If REST is so great, then why do you keep finding yourself relying on documentation in order to use it?

tysonfurytoo 2y ago
I won't say REST is perfect, but I much prefer it to an unstructured api where anything goes. You didn't suggest that, but you really didn't suggest any alternative. What's the alternative to relying on documentation? Is relying on documentation even a bad thing?
- ravenstine 2y ago
  I didn't suggest an alternative because, while I have more specific opinions on that matter, almost any alternative a person pulls out of a hat would be superior to REST. > I much prefer it to an unstructured api where anything goes. You're entitled to your opinion, and while I'm sure you didn't mean it to be a straw man, it's essentially the type of straw man I hear a lot when I broach this subject. Whether an API is "unstructured" doesn't depend that much on what said API is advertised to be acting like. Plenty of RESTful APIs in the wild don't completely adhere to REST or supplemental "standards" like JSON:API. My point about documentation is that, because using a REST API inevitably means reading documentation, and because assumptions about a REST API cannot always be made, then one might as well abandon REST and build an API that doesn't include the extras of REST that are rarely necessary. This doesn't imply unstructuredness. Most programmers don't like building things that don't have a useful amount of predictability to them, so to me the worry about structure is actually concern over very junior programmers doing very junior programmery things. I'm just not interested in that problem, and I don't think most programmers need to be. So let's just say a programmer, or a team of programmers, implement an API that uses their own convention that they invented, and they provide extremely readable and thorough documentation. Where's the problem? Documentation is a necessity. One of my arguments against REST is that it implies a high amount of intuitiveness that it can only even attempt to possess with extremely simplistic data. As soon as it makes sense to have a POST endpoint that acts more like an RPC than a REST API, that throws the entire decision to adhere to REST under question, and that sort of thing is not uncommon.

Merad 2y ago

Many of the migration tools I've worked with include the concept of scripts that are run every time migrations are applied, in addition to your standard migration files. So things like views, functions, SPs can go in these "every time" files rather than being duplicated in migrations every time they change.

est 2y ago

> In reality postgREST sucks... it's fine for simple apps, but for something bigger its pain in the butt. In reality REST sucks. It should and only be used for files like WebDAV or S3.

starttoaster 2y ago
I hear this sentiment echoed a fair amount without really saying why it sucks. And sometimes I wonder if the writer omitted it for brevity, or is actually just echoing something they read on the internet without really believing or understanding it themselves, because saying something sucks where clearly other people don’t know that it sucks (or else why would it be so popular) positions the writer in a dialogue as mysteriously knowledgeable. Hard to argue against points you don’t even know the other person is making and all.
- tucnak 2y ago
  Yeah, he omitted it for brevity alright & you would just have to deal with it. Some day you might, too, become Mysterio Mister Knowledgeable
- est 2y ago
  > without really saying why it sucks I think you've heard quite a lot. There is a reason why people invent GraphQL or BFF In a nutshell, I think today's Web APIs can't be simply descriped as a bunch of "resources" or "states" (which Roy Fielding used in his PhD paper about REST). A database system like PG is much more complex. The SQL itself is an API and comes with a data transfer protocol. You get a leaky abstraction by mapping it to REST.
  - dventimi 2y ago
    > There is a reason why people invent GraphQL or BFF Sure, it was because creating servers--REST or otherwise--with code (looking at you, Spring, Django, and RoR) is such a slow, laborsome task that rapidly iterating frontend teams got tired of waiting around for ill-fitting backends to catch up, that they built their own abstraction layers: BFF and then GraphQL. That wouldn't be a problem if backend teams were as nimble. Things like PostgREST, Prisma, Hasura, etc. allow them to be that nimble. Ironically, that brings folks like Wundergraph full circle, where they relegate GraphQL to being a development language for defining REST endpoints. If you're going to use a query language like GraphQL to define your REST services, then you might as well use the OG query language SQL to define your REST services, if circumstances allow. That's precisely what PostgREST does. So, yes, I have heard a lot arguments for why REST sucks. I just don't think they apply anymore (if they ever did).

sbstp 2y ago

The biggest issue with postgREST for me is that it doesn't support transactions well. You can't two 2 insert and keep a consistent state if one fails. That alone is a deal breaker.

dventimi 2y ago
Sure you can. Just write a function.
- ecosystem 2y ago
  You posted a lot of comments in response to complaints or critiques of postgrest (almost every one at the time of my reading). Most of them are very terse, rude/dismissive, and in my view fall on the wrong side of hackernews etiquette. eg, "Sure you can. Just X" is not a educational or persuasive construction and neither are several other comments you've made on this submission. If nothing else, I'd encourage you to respond to the best version (steelman) of these critiques, which will improve the quality of discussion and be more persuasive.
  - dventimi 2y ago
    I encourage you to read the HN guidelines and then read the comments that I've replied to, because many of them could with considerable justification be regarded as unkind, snarky, and smug. I would also encourage you not to make and take things personally, and instead stick to the substance. Every one of my comments has been about the technological approach under consideration. If they're terse, it's because I don't want to waste people's time. If they're "not educational or persuasive" to you then you're free to disregard them, or say why exactly they fail. Plenty of the comments here, in my view, are "not educational or persuasive", so I said so and gave my reasons for having that view. I would encourage you to do the same.
steve-chavez 2y ago
You have explicit control over transactions with functions https://postgrest.org/en/latest/references/api/functions.html https://postgrest.org/en/latest/references/api/functions.htm.... I think this sentiment stems from users of postgrest-js[1], which is a JS library that gives an ORM feel to PostgREST requests. Under that abstraction, users don't realize they're using a REST API, instead of a direct postgres connection. So in this case users are really asking for "client-side transactions"[2], which are not supported in PostgREST. [1]: https://github.com/supabase/postgrest-js https://github.com/supabase/postgrest-js [2]: https://github.com/PostgREST/postgrest/issues/286 https://github.com/PostgREST/postgrest/issues/286

fzeindl 2y ago

Thanks for your response. You are right that care needs to be taken in the authentication layer. And yes, developing SQL in a shared environment is a complex task and needs to be accompanied by proper tooling.

naasking 2y ago

> there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely) So don't put it in the same table. > me and my colleague been making changes to the same view and we would override changes from each other You don't use version control and a test suite?

CafeRacer 2y ago
> So don't put it in the same table. Nope, this suggestion sucks. Meaning I have to come up w/ table structure that caters to front-end API. Better idea is to create schemas, e.g. `public_users` and create views inside. > You don't use version control and a test suite? that's what I'm saying... we have like a 1k migration files already, what I want instead is `my-function.sql` that always has the current code and will automatically generate necessary migrations and shenanigans, so I don't have to worry about it.
- naasking 2y ago
  > Nope, this suggestion sucks. Meaning I have to come up w/ table structure that caters to front-end API. Yes custom schemas are preferable, but I think you're missing the point. If the only real cost of not having to write the font-end manually at all is massaging the db schema a little bit to partition access by role, that's still a pretty great tradeoff for a lot of scenarios. The system is simple to understand/audit and fast to create, the only apparent downside is that it rubs our sense of aesthetics the wrong way. > we have like a 1k migration files already, what I want instead is `my-function.sql` that always has the current code and will automatically generate necessary migrations and shenanigans, so I don't have to worry about it. I use entity framework a lot which does this sort of schema migration based on class definitions. It works well like 80% of the time, but when it doesn't it's annoying. In any case, what I was suggesting to address the specific program from your original post is that if you have lots of conflicting schema updates, then your table schema should be versioned and any migration should specify what schema version it depends on, eg. have a TableVersions table that names all of your tables and associates a version number with it. Your migration scripts then specify what version they were written against and fail if the number differs. It's the same basic optimistic concurrency control we use elsewhere in databases, just applied to schemas.

evanelias 2y ago

> * with each new migration it's very easy to loose context of what's going on and who changed what. > * me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit. > I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production. Declarative schema management solves this, and allows you to manage your database definitions (SQL CREATE statements) just like any other codebase: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment... With a declarative system, the tooling is responsible for diff'ing the CREATE statements between your current state and desired state, and then generating the appropriate DDL to transition between those states. My tool Skeema is specific to MySQL/MariaDB, but I've linked some solutions for Postgres here: https://news.ycombinator.com/item?id=39236111 https://news.ycombinator.com/item?id=39236111