How to see/update database version? Is there something like "fallbackToDestructiveMigration"?

Question

After modifying the sq file by renaming one of the tables and adding one more table I didn't find any place to specify that database schema or version has been changed and I want no migration - just recreate database. The documentation on official website does not contain any information about this.

Project compiles but when I run the app on both Android and iOS I get a runtime crash and in logs it says that added table does not exist.

I tried adding version in build.gradle.kts of shared module but I still get runtime crash:

sqldelight {
   database("MyDatabase") {
      packageName = "com.example.shared.cache"
      version = 2 // added this line only
   }
}

I don't want to delete and reinstall the app. Ideal solution would be something like fallbackToDestructiveMigration Room database has

I found this discussion about a destructive migration. Last comment suggests to handle that in platform specific drivers but it doesn't show how to do that.

So my questions are:

1. How to change the database version?
2. How to setup destructive migration?

I wish documentation was more detailed on various topics. It would make finding right information easier for developers like me who is not very experienced in Sqlite.

Answer 1

While waiting for a destructive migration, let’s follow the current migration docs.

The official docs it's quite well written.

The .sq file always describes how to create the latest schema in an empty database. If your database is currently on an earlier version, migration files bring those databases up-to-date

This means that if you change your original .sq file (database version 1), then if you want to migrate the already created databases in devices to the new version (version 2) you must write a 1.sqm file in which you add all the difference between version 1 and version 2.

Let's say your origial (first app release) db was created by

MyDb.sq :

-- src/main/sqldelight/com/example/sqldelight/MyDb.sq

CREATE TABLE Foo (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  foo TEXT NOT NULL
);

INSERT INTO Foo (foo)
VALUES ('bar');

This is said to be the version 1 of your db.

Now let's say you want to add a table and change the previous one, you have to change your MyDb.sq file accordingly for a newly database created in a new app fresh install, but you need also to add a 1.sqm (the number 1 is the db version to upgrade to the subsequent version — in this case 2) to migrate your existing original db in your current app installations.

MyDb.sq :

-- src/main/sqldelight/com/example/sqldelight/MyDb.sq

CREATE TABLE Foo (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  foo TEXT NOT NULL,
  bar TEXT
);

INSERT INTO foo (foo, bar)
VALUES ('bar', 'beer');

CREATE TABLE Beer (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  brew TEXT NOT NULL
);

1.sqm :

-- src/main/sqldelight/com/example/sqldelight/2.sqm

ALTER TABLE foo ADD COLUMN bar TEXT;

CREATE TABLE Beer (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  brew TEXT NOT NULL
);

If you extract your original db from one device you can put it along with these two files and named it 1.db. Or you can

generate a .db file from your latest schema, run the generateSqlDelightSchema task, which is available once you specify a schemaOutputDirectory, as described in the gradle.md. You should probably do this before you create your first migration.

Doing that you can validate the migration with the gradle task verifySqlDelightMigration . This task gives you errors if you wrote a wrong migration. It will be successful for a good migration instead.

Answer 2

As the official documentation doesn't support a rule such as fallbackToDestructiveMigration, we will need to delete table/database and create it again.

Following the documentation, you will need first, as you mentioned, increment the version number

sqldelight {
  database("MyDatabase") {
    packageName = "com.example.shared.cache"
    version = 2 // added this line only
  }
}

After that, you will need to create a file 1.sqm to "migrate" from previous version (version 1) to current version (version 2). On this file, first, we will remove the table:

DROP TABLE IF EXISTS tablename;

or remove database:

DROP DATABASE MyDatabase;

And then, bellow this line, we will create the table again:

CREATE TABLE tablename (
  name TEXT PRIMARY KEY,
)

Or the database and its tables:

CREATE DATABASE MyDatabase;
CREATE TABLE tablename (
  name TEXT PRIMARY KEY,
)