When working with SQLDelight migrations, I have se...

# multiplatform

[Håkon Pettersen]

When working with SQLDelight migrations, I have set up migration to execute using the following code:

Database.Schema.migrate(
    driver = driver,
    oldVersion = 0,
    newVersion = Database.Schema.version,
)

I assume that this won't work if the user is currently on version 2, and I am trying to migrate to version 3. I assume that I have to check which version of the database the user is currently on and only execute migration if it is lower than the latest version. If that's the case, how can I check the current database-verison that the user is on? (Also, If this is the wrong way to handle migrations, please elaborate).

[Matt Nelson]

Won't your migration files be automatically applied upon instantiation of DB?

[Michael Paus]

That’s how I am doing it:

Database.Schema.migrate(databaseDriver, config.localDatabaseVersion, Database.Schema.version)

Config is read from a configuration file which I update after the migration.

[Håkon Pettersen]

Do you store the config as a table @Michael Paus?

[Michael Paus]

No, in a JSON file together with some other values.

[Håkon Pettersen]

Is the JSON file stored locally on the phone? I experienced that the migration crashed because it attempted to create duplicate columns if I didn't migrate from the current version the existing db was using.

[Michael Paus]

Where else? The application knows its current database version and writes that out locally to a file after the initialization of the database. On next startup of the application this value is read before the database is initialized. If the version read from the file is older than the current version of the software I perform the migration as shown above. So, whenever the software is updated I know from which version I have to migrate.

[Håkon Pettersen]

Thank you. You are right, it is the only thing that made sense, just wanted to verify 🙂

[Jhaman das]

Just create a folder named migration and inside that create a versionName.sqm file and add your table query.

Migration took place automatically.

Like this:

[Jhaman das]

Here is documentation:

https://cashapp.github.io/sqldelight/2.0.0-alpha05/multiplatform_sqlite/migrations/

[Håkon Pettersen]

@Jhaman das Thanks. Ended up going away from the approach of doing migration by code, and it works with no problems now 🙂

[Michael Paus]

Could you explain how you do it now?

[Håkon Pettersen]

Create initial table:

CREATE TABLE hockeyPlayer (
  player_number INTEGER PRIMARY KEY NOT NULL,
  full_name TEXT NOT NULL,
  birthYear INTEGER NOT NULL
);

Add new field:

CREATE TABLE hockeyPlayer (
  player_number INTEGER PRIMARY KEY NOT NULL,
  full_name TEXT NOT NULL,
  birthYear INTEGER NOT NULL,
  birthMonth INTEGER NOT NULL
);

Add migration file 1.sqm

ALTER TABLE hockeyPlayer ADD COLUMN birthMonth INTEGER NOT NULL DEFAULT 12;

Create a new table:

CREATE TABLE hockeyTeam (
    team_id INTEGER PRIMARY KEY NOT NULL,
    team_Name TEXT NOT NULL
);

Add migration file 2.sqm:

CREATE TABLE hockeyTeam (
      team_id INTEGER PRIMARY KEY NOT NULL,
      team_Name TEXT NOT NULL
);

Verify migration: if schemaOutputDirectory.set(file("src/main/sqldelight/databases")) is defined in build.gradle you can run ./gradlew generateDebugDatabaseSchema to generate a .db file. Once the .db file has been generated you can run task ./gradlew verifySqlDelightMigration that will verify that the migration is working as expected.

Image showing file-structure:

[Michael Paus]

I still don’t see how this can magically migrate thousands of databases on as many devices in different states of their databases. As far as I understand it you are just checking locally that your migrations are technically correct. Correct me if I am wrong.