feat: make database migrations centric around turing complete code, rather than up.sql/down.sql

[drahnr]

Status Quo

Currently we use the vanilla diesel ORM migrations via embed_migrations!.

Limitations

We currently cannot execute rust code in lockstep in a structured way associated to sql migrations. The differentiation is also artificial. I.e. oftentimes we need to add an optional column, use rust to fill in the data, then make it mandatory which equals to SQL -> rust -> SQL sequence of execution domains.

Draft Impl

The initial impl. was a side effect of impl #1697 with the main changes in 3b1319f using a macro. However this did rely on the duality of schema and data migrations and tried to retain simplicity by continuing to use diesel_migrations::* types as much as possible.

Proposal

Directory Structure

Drop diesel_migrations and move to a fully custom approach, keeping the directory structure similar to what we have today.

I propose a letter prefix to be able to use them as rust modules which can be used without further adjustments. Note the m-prefix (yes, this is very artisan).

migrations
├── m2025062000000_setup
│   └── mod.rs
├── m2026020600000_cleanup_indices
│   └── mod.rs
├── m20260206163855_add_account_indices
│   └── mod.rs
├── m20260223160000_add_block_header_commitment
│   ├── down.sql
│   ├── mod.rs
│   └── up.sql

The next major change is relying entirely on the mod.rs rather than up.sql and down.sql - it entirely depends on the needs inside mod.rs.

This is then all picked up via a build.rs file to be included.

Inner

The expectation is, each of these contains an implementation of

trait Migration {
  fn up(&self, conn: &mut SqlConnection) -> Result<()> { .. }
  fn down(&self, conn: &mut SqlConnection) -> Result<()> { .. }
}

the build.rs the provides a list of these migrations. The ordering is derived based on lexicographical ordering of the modules.

We then can use a fixed harness to execute the sequence Vec<Box<dyn Migration>> of migrations using a fn apply.

Statefulness

We will oftentimes encounter partially updated state. To track which migrations were applied and which not, I suggest to maintain another table migrations to track which ones (by their module name) are already applied and only apply those post the last one applied.

Invariants

We cannot ever modify an existing migration post release, at least not with causing issues at the operational side.

Assumption

We care about down, if not, we might as well omit entirely which reduces trait Migration essentially to an FnOnce.

[Mirko-von-Leipzig]

We care about down, if not, we might as well omit entirely which reduces trait Migration essentially to an FnOnce.

My experience has been that down is never used in production, and is only useful for resetting a dev environment. Which I don't think applies to us - we don't have some automatic docker database that we run against. Tests and dev database instances would be "in-memory" and populated as part of test setup. My vote is therefore we don't care about down.

[drahnr]

Then let's drop it.

[Mirko-von-Leipzig]

the build.rs the provides a list of these migrations. The ordering is derived based on lexicographical ordering of the modules.

We then can use a fixed harness to execute the sequence Vec<Box> of migrations using a fn apply.

My question is why not just do a manual list, which we order ourselves as vector/slice? Adding a migration means just appending it to this list.

I'm also unsure why we want the date embedded in the filename - once again, why not just use a manual list?

And similarly

I suggest to maintain another table migrations to track which ones (by their module name)

lets just avoid text/strings/names and use an index as part of the list?

Or in summary, I don't see the benefit of

• embedded dates
• build.rs
• text based tracking in a table

over a vector + index.

[drahnr]

embedded dates - The upside for embedded dates is conflict freedom for i.e two PRs that attempt to add a migration each - it might also be an argument to catch that via conflict resolution.
migration detection/build.rs - forgetting a module is trivial, forgetting to add it to the list similarly - it's a consistency guarantee, not a strong opinion either way
text based tracking - it doesn't really matter, the intent was to simplify searching, but I can always use g blame, not a strong opinion either way

[Mirko-von-Leipzig]

embedded dates - The upside for embedded dates is conflict freedom for i.e two PRs that attempt to add a migration each - it might also be an argument to catch that via conflict resolution.

I don't think we should optimise for that. Its rare, and even then its just a merge conflict of a single line.

migration detection/build.rs - forgetting a module is trivial, forgetting to add it to the list similarly - it's a consistency guarantee, not a strong opinion either way

If you forget the list then you don't have anything running? I have to imagine you would notice that :)

[Mirko-von-Leipzig]

Two further pain points I have experienced in the past:

Partial migrations

Migrations which take forever on large databases sometimes need to be split into multiple transaction/commit cycles to keep the WAL file (and time) in check. What this can mean is that a migration was only partially completed if the process is killed midway.

If this is possible, the migration must take care to use CREATE IF NOT EXIST and friends.

struct versioning

A sneakier problem is if the migration depends on external structs. What can easily happen is that the struct is updated in such a way as to invalidate the migration long after we've implemented it.

As a trivial example, let's imagine the block header changes structure and some fields are removed. A migration which previously reads the BLOB and does things with it will now fail completely when run. This isn't trivial to solve; but it can often mean such structs must only live within the migration itself aka if this is a concern we may want to copy the structure into the migration code itself

[drahnr]

I think both issues require awareness as well as diligence in docs and review, but I don't see an obivous way to fix them through the migration "scaffolding" (I can imagine more involved approachs i.e. using crates + CI, but I don't think the these paths are viable in regards to complexity).

[bobbinth]

I'm not fully caught up - but maybe an easier path is to build a dedicated binary for a given migration. For example, an upgrade from v0.14 to v0.15 database would require:

• Manually writing a migration binary - e.g., miden-node-v0.15-migration.
• Shutting down the network.
• Running the above binary.
• Starting up the network using the new codebase.

One potential benefit of this is that miden-node-v0.15-migration could depend on both the v0.14 and v0.15 protocol crates so that we could, for example, read a block header in v0.14 format, transform it, and then save it back in v0.15 format.

[Mirko-von-Leipzig]

I like this quite a bit conceptually. It also simplifies the main binary by removing the migration code -- startup just becomes a validation of the schema version expected against the database.

Some problems to overcome:

• We now have several databases, do we need to maintain an additional binary for each?
• We'll want a database schema copy for the main binary so it can spawn a new database, and to verify against.
• Deployment / upgrades become more complex.
  • Is this possible for dApp developers to manage, or should we just tell them to nuke the chain each time.
• We'll need to be careful :)

[bobbinth]

We now have several databases, do we need to maintain an additional binary for each?

Ideally, we'd only migrate the main SQLite database and nuke everything else. RocksDB databases would the be re-built from SQLite, NTX builder database can be re-built from the main SQLite DB as well. I guess the only question is the validator database - but I'm not sure if we need to migrate it. If we do, I'd include the code in the same binary.

We'll need to be careful :)

We could always make a copy of the main database, run migration on it, then start the node. Then, we can check if things work as expected - but we'd never "lose" the original data.

[Mirko-von-Leipzig]

Given that the validator will mostly be storing transactions, we can avoid migrations by including a column for versioning of some sort. So that if we later need to get the data we know which version of protocol (or validator) to use. Most robust would likely be the git SHA of the validator, or the crate version for easier use.