How to manage data migrations?

[kyogesha]

For context:
We use alembic for migrations and follow a rule that no down migrations, and do dry run in alembic in local to avoid problems.
For managing deployment, we have a job in k8s that runs alembic migrations (in a single transaction), in case it succeeds upgrades services, or else it won't.

Few questions

• How to insert default data? is it possible in declarative workflow?
• How to update column values/apply a function? Say I need to transform some cols in a table, or update some key in jsonb on the basis of some condition. I know that this is an anti pattern and not advised, but is there a way?
• Can I use both declarative (for schema migrations) & versioned (for data migrations)? Is it a good practice?

[reritom]

I am also interested in an answer for this. I like the idea of the declarative approach, but I'm not sure what the best approach is for also handling up+down data migrations (which are commonly done in my alembic migrations).

[giautm]

Hey, you guys may missing this update:

https://atlasgo.io/blog/2024/04/01/migrate-down

[nickchomey]

I asked a question about data migrations in the discord the other day, then discovered that they're completely possible with Atlas (or seemingly any schema migration tool, like goose etc).

Atlas itself only generates schema stuff, but you can manually or programmatically add any Sql that you want to the resulting Sql files. They talk about it in various places in the docs, particularly with regards to seeding.

https://atlasgo.io/versioned/checkpoint#seeding-data

You can even use templates that read from other files
https://atlasgo.io/guides/migration-dirs/template-directory#read-data-variables-from-file

And a combined declarative +versioned approach is discussed here https://atlasgo.io/versioned/diff

[reritom]

I am still not sure how a declarative approach can be applied with data migrations in practice.

Given:

• I have a project with multiple devs, working on multiple branches, and making lots of database schema changes.

Then:

• I would assume that I wouldn’t want a static schema.hcl file that needs to be manually updated. Instead the schema.hcl should be automatically generated and regenerated whenever there are schema changes.
• The generation/regeneration should be done prior to a deployment.
• I don’t think it makes sense for data migration stuff to be part of the schema.hcl, but if I did then:
  • If there were no data migrations then the schema.hcl wouldn’t need to be stored with the source code that it relates to because it could be generated at any point from that same source code. So it would make sense to do it in the deployment flow (or release candidate flow)
  • The schema.hcls can’t be generated too early (per PR, or merge, or whatever) because the they would conflict.
  • So if we are generating the schema.hcl as part of the deployment flow then it’s not practical to handle the data migrations (we need the data migration info to be provided by the devs doing the schema changes, earlier in the dev flow)
  • The data migrations being part of the schema.hcl only seems to be practical if the schema.hcl is generated by the dev as part of their PR so that they can add their migration commands to it (but this would likely have conflicts)
  • It also means if you skip a version on an environment then those specific data migrations defined in then schema.hcl would be missed.

The combined approach that you linked to seems to use the schema.hcls as a stepping stone.

So am I missing something or perhaps some of my understandings or assumptions are incorrect? Because I can’t see where data migrations fit it with the declarative approach.

Perhaps we use the declarative approach to define the expected schema, and then use the versioned approach purely for the data migrations. But I am not sure whether that us handled well by atlas and I don’t think it would support a normal case that I see in alembic migrations where a single migration would “Create a nullable row, perform a data migration to populate the row, Update the row to make it non-nullable”.

[nickchomey]

As far as I can tell, data migrations need to be done in the versioned Sql files, not in hcl. That's how I intend to use it, and probably not hcl at all. I was just sharing links that you might find useful. Please let us know if you figure something out though!