5 ms·

To me it seems a bizarre approach to have no actual goal state and have your source of truth be a chain of scripts. Why not: - Have an ORM definition/schema.s

To me it seems a bizarre approach to have no actual goal state and have your source of truth be a chain of scripts. Why not: - Have an ORM definition/schema.sql explicitly defined - Continually test that production_schema+pending_changes=goal_schema This way, no chain is required: The only scripts you need to keep hanging around are any pending changes yet to be applied. And you can mostly autogenerate any changes required, so the need to cobble together scripts manually is much reduced.

ThePhysicist 6y ago
You can have both. I usually have numbered SQL scripts that represent up & down migrations (example: https://github.com/algoneer/algonaut/tree/master/algonaut/models/migrations https://github.com/algoneer/algonaut/tree/master/algonaut/mo...). The first migration simply contains the initial data schema, the following migrations contain amendments made during development. As you said it can be confusing to have to look at a series of files to figure out how the schema would look in practice. To alleviate this problem we regularly "compact" our changes by generating a new initial migration representing the current state of the database (this is trivial with most database systems e.g. using the "pgdump" command for Postgres), replacing all migration files and resetting the schema version in the production DB to 1. In practice it's hard to avoid having incremental migrations, as you need a way to evolve your production schema. Your approach would be awesome of course but I don't know any tool that would be able to robustly come up with a migration by comparing a database schema and a schema specification. Often it's not even possible to derive the necessary changes using such an approach: If you e.g. change the name of a column in your schema.sql, how can the tool know if you want to remove the original column and add a new one instead or simply rename and possibly type-cast it? If you have multiple changes like that it only gets worse. So I'm not very optimistic about that approach.
- djrobstep 6y ago
  I wrote a tool that does exactly this (migra, mentioned in the post). You obviously have to review the output to catch things like renames, but most of the time, for typical operations like adding a column/updating a view/whatever, it's going to give you what you want.
jayd16 6y ago
You usually keep the full chain because you don't track the state of every environment (like dev local environments).
- djrobstep 6y ago
  Your dev environment is invariably just set to a goal schema. Why is a chain required for that?
  - jayd16 6y ago
    Ah I see what you mean. It doesn't work for data migrations though.

ThePhysicist 6y ago

You can have both. I usually have numbered SQL scripts that represent up & down migrations (example: https://github.com/algoneer/algonaut/tree/master/algonaut/models/migrations https://github.com/algoneer/algonaut/tree/master/algonaut/mo...). The first migration simply contains the initial data schema, the following migrations contain amendments made during development. As you said it can be confusing to have to look at a series of files to figure out how the schema would look in practice. To alleviate this problem we regularly "compact" our changes by generating a new initial migration representing the current state of the database (this is trivial with most database systems e.g. using the "pgdump" command for Postgres), replacing all migration files and resetting the schema version in the production DB to 1. In practice it's hard to avoid having incremental migrations, as you need a way to evolve your production schema. Your approach would be awesome of course but I don't know any tool that would be able to robustly come up with a migration by comparing a database schema and a schema specification. Often it's not even possible to derive the necessary changes using such an approach: If you e.g. change the name of a column in your schema.sql, how can the tool know if you want to remove the original column and add a new one instead or simply rename and possibly type-cast it? If you have multiple changes like that it only gets worse. So I'm not very optimistic about that approach.

djrobstep 6y ago
I wrote a tool that does exactly this (migra, mentioned in the post). You obviously have to review the output to catch things like renames, but most of the time, for typical operations like adding a column/updating a view/whatever, it's going to give you what you want.

jayd16 6y ago

You usually keep the full chain because you don't track the state of every environment (like dev local environments).

djrobstep 6y ago
Your dev environment is invariably just set to a goal schema. Why is a chain required for that?
- jayd16 6y ago
  Ah I see what you mean. It doesn't work for data migrations though.