When working with SQLDelight migrations, I have se...
# multiplatform
h
When working with SQLDelight migrations, I have set up migration to execute using the following code:
Copy 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).
m
Won't your migration files be automatically applied upon instantiation of DB?
m
That’s how I am doing it:
Copy code
Database.Schema.migrate(databaseDriver, config.localDatabaseVersion, Database.Schema.version)
Config is read from a configuration file which I update after the migration.
h
Do you store the config as a table @Michael Paus?
m
No, in a JSON file together with some other values.
h
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.
m
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
Thank you. You are right, it is the only thing that made sense, just wanted to verify 🙂
j
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:
h
@Jhaman das Thanks. Ended up going away from the approach of doing migration by code, and it works with no problems now 🙂
m
Could you explain how you do it now?
h
Create initial table:
Copy code
CREATE TABLE hockeyPlayer (
  player_number INTEGER PRIMARY KEY NOT NULL,
  full_name TEXT NOT NULL,
  birthYear INTEGER NOT NULL
);
Add new field:
Copy code
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
Copy code
ALTER TABLE hockeyPlayer ADD COLUMN birthMonth INTEGER NOT NULL DEFAULT 12;
Create a new table:
Copy code
CREATE TABLE hockeyTeam (
    team_id INTEGER PRIMARY KEY NOT NULL,
    team_Name TEXT NOT NULL
);
Add migration file 2.sqm:
Copy code
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:
m
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.
601 Views