Dbmate: A lightweight, framework-agnostic database migration tool

Can confirm.

I've been using raw SQL for migrations in some projects for years.

Boring and predictable. Just how I like it.

Agree, makes it easy to apply a migration manually if necessary as it’s really just adding a row to a table to indicate that it ran and the migration itself.

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.

I wrote a version of that as part of my sqlite-utils Python library: 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/577078fe01da6d87... - and the accompanying issue: https://github.com/simonw/sqlite-utils/issues/467

> 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.

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/

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/

[2] https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...

> 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/