5 ms·

> 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

> 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.
- CafeRacer 2y ago
  I understand what you mean, however, I still think this is a bad DX overall. There are a lot of good suggestions in these threads about possible tools and approaches, but it just misses the point that these solutions are bad DX and require maintenance. I think general idea of postgres is good, it's just not ready yet. There needs to be a framework that handles views/functions in postgres as if it's a web app and then applies changes as if it's a terraform. All while taking back-ups in case something goes wrong. Otherwise it's just more maintenance and it's easier to build API's w/ something else.
  - naasking 2y ago
    > but it just misses the point that these solutions are bad DX and require maintenance. Everything requires maintenance, that's not an argument that it requires more maintenance than anything else. There are fewer moving parts overall, and fewer moving parts generally means less maintenance.

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.

CafeRacer 2y ago
I understand what you mean, however, I still think this is a bad DX overall. There are a lot of good suggestions in these threads about possible tools and approaches, but it just misses the point that these solutions are bad DX and require maintenance. I think general idea of postgres is good, it's just not ready yet. There needs to be a framework that handles views/functions in postgres as if it's a web app and then applies changes as if it's a terraform. All while taking back-ups in case something goes wrong. Otherwise it's just more maintenance and it's easier to build API's w/ something else.
- naasking 2y ago
  > but it just misses the point that these solutions are bad DX and require maintenance. Everything requires maintenance, that's not an argument that it requires more maintenance than anything else. There are fewer moving parts overall, and fewer moving parts generally means less maintenance.