3 ms·

I’ve always wondered why tools like this cannot become stateless. Most have an up and down action, but I haven’t seen one yet that can run a query to determine

I’ve always wondered why tools like this cannot become stateless. Most have an up and down action, but I haven’t seen one yet that can run a query to determine if a migration has been applied or not. Then no state tables/artifacts are needed. Instead of one file with an up and down, there could be two files where each has a predicate and then an action, where the predicate would run to determine if the migration has been applied or not.

netghost 2y ago
You probably could, but you really want your database migrations to clear, repeatable, and dead simple. I can also imagine edge cases like a migration dropping a column and another recreating it where it may be unclear what the state is, and honestly you don't want to be surprised when you migrate thousands of paying customers' data.
- AdieuToLogic 2y ago
  Another reasonable example is a migration which adds a column ultimately intended to be "not null". The first migration might add the column as nullable, then subsequent migrations and/or server logic populate the column, culminating with a future migration which alters the constraint to be "not null".
simonw 2y ago
I wrote a version of that as part of my sqlite-utils Python library: https://sqlite-utils.datasette.io/en/stable/python-api.html#explicitly-creating-a-table https://sqlite-utils.datasette.io/en/stable/python-api.html#... You can do something like this: db["cats"].create({ "id": int, "name": str, "weight": float, }, pk="id", transform=True) The transform=True parameter means "if the table already exists but does not match the provided schema, transform it to add missing columns etc". I don't generally recommend it though, it's a risky way of working compared to stateful migrations. Here are the tests for that option: https://github.com/simonw/sqlite-utils/blob/577078fe01da6d876edda6f3ccbd1b5d074aeb6e/tests/test_create.py#L1234 https://github.com/simonw/sqlite-utils/blob/577078fe01da6d87... - and the accompanying issue: https://github.com/simonw/sqlite-utils/issues/467 https://github.com/simonw/sqlite-utils/issues/467
stanac 2y ago
> I haven’t seen one yet that can run a query to determine if a migration has been applied or not Entity Framework (both Core and legacy) does that, also DbUp uses migrations table for those that prefer lighter ORMs.
evanelias 2y ago
There are a number of declarative schema management tools, which operate on a repo of desired-state CREATE statements. No need to write up/down migrations or predicates/actions. You just have CREATE statements, and can modify them in-place, and the schema management tool figures out the appropriate DDL to transform any live database into the desired state. Conceptually this has many advantages for managing tables [1] as well as stored procedures (and functions, triggers, etc) [2]. My tool Skeema, first released in 2016, provides declarative schema management for hundreds of MySQL/MariaDB based companies, including GitHub, SendGrid, Cash App, Wix, Etsy, and many others you have likely heard of. Safety is the primary consideration throughout all of Skeema's design: https://www.skeema.io/docs/features/safety/ https://www.skeema.io/docs/features/safety/ For Postgres, a few declarative solutions include sqldef, Migra, Tusker (which builds on Migra), and Atlas. [1] https://www.skeema.io/blog/2019/01/18/declarative/ https://www.skeema.io/blog/2019/01/18/declarative/ [2] https://www.skeema.io/blog/2023/10/24/stored-proc-deployment/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
AdieuToLogic 2y ago
> I haven’t seen one yet that can run a query to determine if a migration has been applied or not MyBatis Migrations[0] does that with the use of a migrations management table. As to why most, if not all, SQL migration tools are stateful, "the good ones" often have migration descriptions and timestamps as well. Since a persistent store is by definition stateful, having tooling state stored alongside the concepts managed makes sense in many cases. 0 - https://mybatis.org/migrations/ https://mybatis.org/migrations/

netghost 2y ago

You probably could, but you really want your database migrations to clear, repeatable, and dead simple. I can also imagine edge cases like a migration dropping a column and another recreating it where it may be unclear what the state is, and honestly you don't want to be surprised when you migrate thousands of paying customers' data.

AdieuToLogic 2y ago
Another reasonable example is a migration which adds a column ultimately intended to be "not null". The first migration might add the column as nullable, then subsequent migrations and/or server logic populate the column, culminating with a future migration which alters the constraint to be "not null".

simonw 2y ago

I wrote a version of that as part of my sqlite-utils Python library: https://sqlite-utils.datasette.io/en/stable/python-api.html#explicitly-creating-a-table https://sqlite-utils.datasette.io/en/stable/python-api.html#... You can do something like this: db["cats"].create({ "id": int, "name": str, "weight": float, }, pk="id", transform=True) The transform=True parameter means "if the table already exists but does not match the provided schema, transform it to add missing columns etc". I don't generally recommend it though, it's a risky way of working compared to stateful migrations. Here are the tests for that option: https://github.com/simonw/sqlite-utils/blob/577078fe01da6d876edda6f3ccbd1b5d074aeb6e/tests/test_create.py#L1234 https://github.com/simonw/sqlite-utils/blob/577078fe01da6d87... - and the accompanying issue: https://github.com/simonw/sqlite-utils/issues/467 https://github.com/simonw/sqlite-utils/issues/467

stanac 2y ago

> I haven’t seen one yet that can run a query to determine if a migration has been applied or not Entity Framework (both Core and legacy) does that, also DbUp uses migrations table for those that prefer lighter ORMs.

evanelias 2y ago

There are a number of declarative schema management tools, which operate on a repo of desired-state CREATE statements. No need to write up/down migrations or predicates/actions. You just have CREATE statements, and can modify them in-place, and the schema management tool figures out the appropriate DDL to transform any live database into the desired state. Conceptually this has many advantages for managing tables [1] as well as stored procedures (and functions, triggers, etc) [2]. My tool Skeema, first released in 2016, provides declarative schema management for hundreds of MySQL/MariaDB based companies, including GitHub, SendGrid, Cash App, Wix, Etsy, and many others you have likely heard of. Safety is the primary consideration throughout all of Skeema's design: https://www.skeema.io/docs/features/safety/ https://www.skeema.io/docs/features/safety/ For Postgres, a few declarative solutions include sqldef, Migra, Tusker (which builds on Migra), and Atlas. [1] https://www.skeema.io/blog/2019/01/18/declarative/ https://www.skeema.io/blog/2019/01/18/declarative/ [2] https://www.skeema.io/blog/2023/10/24/stored-proc-deployment/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...

AdieuToLogic 2y ago

> I haven’t seen one yet that can run a query to determine if a migration has been applied or not MyBatis Migrations[0] does that with the use of a migrations management table. As to why most, if not all, SQL migration tools are stateful, "the good ones" often have migration descriptions and timestamps as well. Since a persistent store is by definition stateful, having tooling state stored alongside the concepts managed makes sense in many cases. 0 - https://mybatis.org/migrations/ https://mybatis.org/migrations/