Collapsing migrations

[bok-]

I just wanted to double check something that I've been thinking about for a while, in case my understanding is very wrong.

We're at 193 migrations at the time of posting in a project I'm a part of (there are ~80 developers) and applying those migrations takes about 300-500 milliseconds depending on device, which adds up in unit tests and in the collective user experience.

I've been thinking about collapsing migrations that are 12+ months old into a single one in order to speed things up. That's well beyond our typical support period of 6 months, so while anyone who hasn't updated the app in more than 12 months would get a slightly worse experience we we delete and re-create the database, most people would not be impacted.

So I wanted to check my logic here. If I have 6 migrations:

let migrations = [
    1,
    2,
    3,
    4,
    5,
    6,
]

If I want to collapse migrations 1-4 into a single one I can:

1. Apply migrations up to and including migration 4.
2. Execute SELECT sql FROM sqlite_schema to get the schema at that point in time.
3. Replace the contents of migration 4 with the captured schema.
4. Delete migrations 1-3.

Giving the following:

let migrations = [
    4,     // Includes migrations 1-4
    5,
    6,
]

This would mean anyone who has already applied migrations 5+ would not notice any difference, but anyone creating a new database would only apply migrations 4-6, but ultimately the schema of the database would still be identical in both cases.

Would that work, or have I missed something fundamental?

Thanks!

[groue]

Hello @bok- :)

Wow, 193 migrations is a lot! Your question is certainly relevant.

The strategy you describe looks sensible to me. You name the collapsed migration like the last one, and this sounds good. You have thought of people who may need a special case. Some tests will help you making sure various scenarios are handled as expected.

The raw result of sqlite_master might need some massage, in case SQLite would be sensitive about the order of applied statements. I had some success trying your strategy against the result of dumpSchema(to:), which outputs indexes after their table, but I'm not sure it would significantly help for all schemas - I designed the output for clarity, not for replay.

Take care that if the database still contains the identifiers of old migrations, the methods that query migrations will be confused by the unknown obsolete migration names. These methods will return unexpected results, with various nefarious consequences, even if you do not call them directly.

I recommend that the collapsed migration removes the names of obsolete migrations. To do so, delete rows in the grdb_migrations table from within the body of the collapsed migration.

The name and schema of this table is supposedly private. I'll add a public api soon. You don't have to wait for me though: just delete and call it a day, I'll tell you when a public api is ready.

[bok-]

Awesome, thanks for the validation! I hadn't seen dumpSchema(to:) before so will definitely look into that as the first point of call.

As far as I know we don't have any code that queries migrations, but I can't guarantee that will always be the case so your point to clean them up is well taken.

Thanks for the advice, will let you know how it goes 🙂

[groue]

All right @bok- :-)

[groue]

@bok-, there is a pull request for you: #1818. Will you please tell me if it fits your needs?

You no longer have to deal with the private grdb_migrations table.

[bok-]

Hey @groue. That's fantastic! Sorry I went on holiday and only just got back to this. This definitely fits my needs and has worked well in my testing. Thanks very much.

[groue]

Glad it fits :) Happy GRDB!

[groue]

⛵ Merged migrations have shipped in v7.8.0

[guillaumehcht]

I'm curious to understand how you guys manage 193 migrations in your app:

• Is everything just stored in the same file (which I assume would be 5000+ lines)?

• Is there any best practice/example to organise migrations?

(Sorry if this is an obvious question. I'm trying if I need to anticipate certain things like this "merge migration" for the future, I'm only at 30 migrations.)

[groue]

(I'm not the original poster, but I have a plausible answer to your question)

Inspired by Ruby on Rails migrations, GRDB migrations support multiple developers working in parallel on the same code base. Each branch can define their own focused migration. As branches get merged, migrations get intertwined and their merged order does not matter much. In particular, the diffs below are both a correct way to end up with A, B, C migrations:

 migrator.registerMigration("A") { ... }
+migrator.registerMigration("B") { ... }
 migrator.registerMigration("C") { ... }

 migrator.registerMigration("A") { ... }
 migrator.registerMigration("B") { ... }
+migrator.registerMigration("C") { ... }

When this code runs on a database that has A and C applied, the missing B migration is run (after C, yes). This is very generally fine. Conflict can happen, but this is infrequent. Supporting the optimistic scenario is important for developers, QA and beta-testers. All those people pull branches, merge code, download new builds from CI or TestFlight, and everything works just fine.

In this context, I can easily imagine an app that grows a long list of migrations over the years. You just need many teams/pull requests, and a schema that frequently needs to be modified. Nothing prevents migrations from piling up, so they do.

Since GRDB v7.8.0, apps can use registerMigration(_:foreignKeyChecks:merging:migrate:) and merge all those "small" migrations, say, between two public releases of the app. See also #1818 (comment) for a merging strategy that resets the database in case of partially applied merged migrations. In all cases, the correct strategy depends on the development and release processes of the app. Just use the available apis so that they fit your needs.

[groue]

BTW, GRDB 7.8.0 is a little too picky about the list of merged migrations. If you are merging v1, v2 and v3 into v3 (reusing the same migration identifier), make sure you DO NOT put v3 in the list of merged migrations, because it is not correctly handled, and leaves the database in an incorrect state 😬:

// New code:
// - Migrations v1 and v2 are deleted.
// - Migration v3 is redefined and merges v1 and v2:
migrator.registerMigration("v3", merging: ["v1", "v2"]) { db, appliedIDs in
    // DO NOT include "v3" here:          ~~~~~~~~~~~~
    ...
}

Arguably, the semantics is ambiguous, and developers may include v3 in the merged set in good faith. The development branch thus accepts merging a migration onto itself. But it is not released yet:

// (NOT RELEASED YET) Both versions are fine:
// - Migrations v1 and v2 are deleted.
// - Migration v3 is redefined and merges v1 and v2:
migrator.registerMigration("v3", merging: ["v1", "v2"]) {  ... }
migrator.registerMigration("v3", merging: ["v1", "v2", "v3"]) {  ... }

[guillaumehcht]

So ideally, to be safe, if I’m at migration 50, it’s better to create migration v51 for the merge.
That way there’s no chance of overlap in merging: ["v1", ..., "v50"], right?

[groue]

I'm sorry that I created FUD.

The safe options (GRDB 7.8.0) are:

• Redefine a migration (preserving its identifier) so that it merges previous migrations. In this case, take care to NOT include the id of the redefined migration in the list of merged identifiers. That's the point that will be relaxed in a future release.
• Define a new migration (with a new identifier) that merges previous migrations. In this case, it is clear that you need to specify the ids of all merged migrations.

Those are the two scenarios described in the documentation, along with sample codes: https://swiftpackageindex.com/groue/grdb.swift/documentation/grdb/databasemigrator/registermigration(_:foreignkeychecks:merging:migrate:)

Personnally, I'd prefer to redefine an existing migration - because the schema v3 ought to be the same schema, whether it is built from v1+v2+v3 migrations, or from the single merged v3 migration. I don't see the point of coming up with a new name for describing the same thing, so I'd rather not having to come up with a new name ;-)

[bok-]

I'm curious to understand how you guys manage 193 migrations in your app:

Is everything just stored in the same file (which I assume would be 5000+ lines)?

Is there any best practice/example to organise migrations?

(Sorry if this is an obvious question. I'm trying if I need to anticipate certain things like this "merge migration" for the future, I'm only at 30 migrations.)

Sorry I only just saw this discussion had more than one post in it 🙂

We manage each migration in its own type + file, so something like:

// MigrationProtocol.swift
protocol MigrationProtocol {
    static var id: String { get }
    static func perform(_ database: Database) throws
}

extension MigrationProtocol {
    static var id: String { "\(Self.self)" }
}

// Migrations/_Index.swift

enum Migrations {
    static var allMigrations: [MigrationProtocol.Type] = [
        AuthorTable_20250102.self,    // defined in Migrations/2025-01-02-AuthorTable.swift
        BooksTable_20250103.self,     // defined in Migrations/2025-01-03-BooksTable.swift
        BooksIndexes_20250104.self,   // defined in Migrations/2025-01-04-BooksIndexes.swift
    ]
}

And then we just iterate over Migrations.allMigrations when registering.

This is probably a little heavy handed for most needs, but I have 80 devs adding migrations so we wanted something that was resistant to casual conflicts and resolution was obvious. Including the date in the migration ID is just some low effort entropy to avoid migrations having identical names. If you get a merge conflict appending to Migrations.allMigrations you are instructed to just always move your migration to the bottom of the array.

We've been doing this for ~5 years and we've had no issues so far, and no one has done anything silly so hopefully that means it is obvious enough.

I haven't had a chance to upgrade this project to 7.8.0 and apply the merges just yet (I've been testing in smaller projects) but I'll be taking the Redefine a migration approach, maybe adding a var merging: [String]? property to the protocol (still thinking about this) and then just removing elements off the front of the array.