3 ms·

Agree, would love to see this built into TablePlus. What I really want is a state-based way to define my database. EdgeDB & Prism have made some progress here

Agree, would love to see this built into TablePlus. What I really want is a state-based way to define my database. EdgeDB & Prism have made some progress here but they really only handle table changes. Really wish this were built into Postgres somehow (or any solid migration story). I want to be able to define my tables, views, functions and everything and the engine behind it automatically figures out all the necessary changes. For example, change a table (add a column or something) and it is smart enough to update any dependent views and functions. Including handling of data migrations.

kiwicopple 3y ago
fwiw, I personally am interested in this approach too[0]. I keep running into roadblocks around the ordering of events and some of the hairy issues around "destructive" actions (eg: renaming columns). i think we can get there, especially once we make progress with this LSP. There are other notable mentions in this space: Reshape: https://fabianlindfors.se/blog/schema-migrations-in-postgres-using-reshape/ https://fabianlindfors.se/blog/schema-migrations-in-postgres... Atlas: https://atlasgo.io/ https://atlasgo.io/ A "custom" example on GitLab: https://gitlab.com/delibrium/delibrium-postgrest/-/tree/master/database https://gitlab.com/delibrium/delibrium-postgrest/-/tree/mast... [0] https://github.com/kiwicopple/declarative-schemas https://github.com/kiwicopple/declarative-schemas
- rotemtam 3y ago
  Thanks for the mention of Atlas <3
- cpursley 3y ago
  Glad to hear that! We’re using Hasura (started before Supabase was a thing, otherwise we’d be using Supabase) and their Rails inspired migration is quite good. However, we’ve run into the problem of modifying functions and views that are dependent on other things (often views and custom functions). It’s a real bear to make changes - it involves dropping multiple views/functions (in the correct order) before applying the new migration - and then carefully adding it all back and hoping nothing breaks. I don’t like the solution of “stop using views and functions” as I prefer to use use the full power of Postgres (everything close to the data, less application level logic, great plugins). Not sure what approach should be taken to creat a state based migration solution, but it feels like the tech around Postgres, language servers, MML, etc has matured enough to solve the issue in an automated way.
  - cpursley 3y ago
    On that topic, I’d love to see database logic presented in a folder like structure in the way that we’re used to with code (instead of just hidden in migration files. Maybe language server can help with that.
codeptualize 3y ago
Supabase cli has a decent diffing option to generate migrations (not sure what they use, but it’s open source), it works quite decent for schema changes. Not as advanced as you describe though, it won’t handle your data migrations.

kiwicopple 3y ago

fwiw, I personally am interested in this approach too[0]. I keep running into roadblocks around the ordering of events and some of the hairy issues around "destructive" actions (eg: renaming columns). i think we can get there, especially once we make progress with this LSP. There are other notable mentions in this space: Reshape: https://fabianlindfors.se/blog/schema-migrations-in-postgres-using-reshape/ https://fabianlindfors.se/blog/schema-migrations-in-postgres... Atlas: https://atlasgo.io/ https://atlasgo.io/ A "custom" example on GitLab: https://gitlab.com/delibrium/delibrium-postgrest/-/tree/master/database https://gitlab.com/delibrium/delibrium-postgrest/-/tree/mast... [0] https://github.com/kiwicopple/declarative-schemas https://github.com/kiwicopple/declarative-schemas

rotemtam 3y ago
Thanks for the mention of Atlas <3
cpursley 3y ago
Glad to hear that! We’re using Hasura (started before Supabase was a thing, otherwise we’d be using Supabase) and their Rails inspired migration is quite good. However, we’ve run into the problem of modifying functions and views that are dependent on other things (often views and custom functions). It’s a real bear to make changes - it involves dropping multiple views/functions (in the correct order) before applying the new migration - and then carefully adding it all back and hoping nothing breaks. I don’t like the solution of “stop using views and functions” as I prefer to use use the full power of Postgres (everything close to the data, less application level logic, great plugins). Not sure what approach should be taken to creat a state based migration solution, but it feels like the tech around Postgres, language servers, MML, etc has matured enough to solve the issue in an automated way.
- cpursley 3y ago
  On that topic, I’d love to see database logic presented in a folder like structure in the way that we’re used to with code (instead of just hidden in migration files. Maybe language server can help with that.

codeptualize 3y ago

Supabase cli has a decent diffing option to generate migrations (not sure what they use, but it’s open source), it works quite decent for schema changes. Not as advanced as you describe though, it won’t handle your data migrations.