How to change column type with migration?

[glennposadas]

What did you do?

I wanted to change the types of my columns from UUID to String through migration.

What did you expect to happen?

Users will experience seamless updates of the app.

What happened instead?

Nothing yet. I wanted to ask first before I proceed with the approach I have in mind.

Environment

GRDB flavor(s): (GRDB, SQLCipher, Custom SQLite build?) GRDB
GRDB version: 5.7.2
Installation method: (CocoaPods, SPM, manual?) SPM
Xcode version: 13
Swift version: 5.x
Platform(s) running GRDB: (iOS, macOS, watchOS?) iOS
macOS version running Xcode: Catalina

Demo Project

Stackoverflow question: https://stackoverflow.com/questions/69598215/how-to-change-column-type-with-migration-with-grdb-swift

[groue]

Hello @glennposadas,

Nothing yet. I wanted to ask first before I proceed with the approach I have in mind.

Hiding your own research is NOT a good way to ask a technical question online. This just makes you look like a lazy person. Please don't do that.

Fortunately, the linked stack overflow tells what you had in mind. You are correct. Since SQLite can't change a column type, you have to create a whole new table, dump the content of the old table inside, and switch them in the end.

The tricky part is to fill the new table with correct data, and this may be why you came here and asked your question.

A simple technique is read old rows one after the other, and insert them in the new table:

var migrator = DatabaseMigrator()
migrator.registerMigration("1") { db in
    try db.create(table: "player") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("uuid", .blob).notNull()
        t.column("name", .text).notNull()
    }
}
migrator.registerMigration("2") { db in
    // Turn uuid into a text column
    try db.create(table: "new_player") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("uuid", .text).notNull()
        t.column("name", .text).notNull()
    }
    
    let rows = try Row.fetchCursor(db, sql: "SELECT * FROM player")
    while let row = try rows.next() {
        try db.execute(
            sql: "INSERT INTO new_player (id, uuid, name) VALUES (?, ?, ?)",
            arguments: [
              row["id"],
              (row["uuid"] as UUID).uuidString,
              row["name"],
              ])
    }
    
    try db.drop(table: "player")
    try db.rename(table: "new_player", to: "player")
}

A second technique lets SQLite perform the loop, with INSERT INTO ... SELECT .... But now we need a function that turns an UUID blob into a proper UUID string. Maybe it can be written with HEX and various SQLite string manipulation techniques. But I prefer sharing a general solution instead: just provide the function in Swift:

migrator.registerMigration("2") { db in
    try db.create(table: "new_player") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("uuid", .text).notNull()
        t.column("name", .text).notNull()
    }
    
    let uuidString = DatabaseFunction("uuidString", argumentCount: 1, pure: true) { dbValues in
        UUID.fromDatabaseValue(dbValues[0])?.uuidString
    }
    db.add(function: uuidString)
    defer { db.remove(function: uuidString) }
    
    try db.execute(sql: """
        INSERT INTO new_player
        SELECT id, uuidString(uuid), name
        FROM player
        """)
    
    try db.drop(table: "player")
    try db.rename(table: "new_player", to: "player")
}

You'll pick your favorite technique.

[glennposadas]

Thanks a lot, @groue! I appreciate the teaching, and especially the solutions. I think Imma go with the first one, more understandable and probably less error-prone. If you have time, feel free to post this answer to my Stackoverflow question.

[alexlee002]

I'm facing the same problems, but my table has toooo many rows(more than 1000000 rows), is that a better way to do that without blocking the user's operations?

[glennposadas]

In that case, @alexlee002 , why not support both columns (old and new)?

[groue]

I'm facing the same problems, but my table has toooo many rows(more than 1000000 rows), is that a better way to do that without blocking the user's operations?

See The Database Schema for your various schema modification options.

If you need to recreate a database table and dump the content of the old table into the new table, then prefer the INSERT INTO ... SELECT ... technique, because you can't get faster.

And if this is still too slow, maybe don't run this code on the main thread.