How to change column type with migration with GRDB Swift?

Question

I have a couple of tables with UUID as the type of their columns.

I want to convert those columns to String rather than UUID as debugging is just pain in the arse (e.g. browsing the sqlite file using DB Browser for SQLite requires me to do SQL query just to convert the UUID objects to Strings to see the id values.

Going back to the question, what's the most practical way to do this?

I am thinking, and about to do, but I want to ask this first:

in the registerMigration, create a new table.
new table has the String columns now.
loop through the rows o the old table, and move those rows to the new table BUT making sure that the ids are now in Strings and not UUIDs.
drop old table
rename new table to the old table's name.

registerMigration("UUIDMigrationToString") { db in
      try db.create(table: "new_table") { table in
        table.autoIncrementedPrimaryKey("id")
        table.column("someIdStuff", .text).notNull()
      }

      // loop through the old table...
      // move the rows from the old table to the new table but with string Ids.
      // drop old table, and rename new table.
    }

score 0 · Accepted Answer · answered Nov 04 '21 at 20:14

This question has been answered by GRDB author here: https://github.com/groue/GRDB.swift/issues/1077

But here's my solution based on that answer, should be pretty straightforward:

import GRDB

extension DatabaseMigrator {
  /**
   Migrate UUIDs to String
   
   References:
   
   - https://github.com/groue/GRDB.swift/issues/1077
   - https://stackoverflow.com/questions/69598215/how-to-change-column-type-with-migration-with-grdb-swift
   */
  mutating func v1_8() {
    migrateStream()
  }
  
  // MARK: - Stream
  
  mutating func migrateStream() {
    registerMigration("1.8 - Stream") { db in
      
      try db.create(table: "new_stream") { table in
        table.autoIncrementedPrimaryKey("id")
        table.column("contentId", .text).notNull()
        table.column("streamId", .text).notNull()
      }
      
      let rows = try Row.fetchCursor(db, sql: "SELECT * FROM stream")
      while let row = try rows.next() {
        try db.execute(
          sql: "INSERT INTO new_stream (id, contentId, streamId) VALUES (?, ?, ?)",
          arguments: [
            row["id"],
            (row["contentId"] as UUID).uuidString,
            (row["streamId"] as UUID).uuidString
          ])
      }
      
      try db.drop(table: "stream")
      try db.rename(table: "new_stream", to: "stream")
    }
  }

How to change column type with migration with GRDB Swift?

1 Answers1