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.

Custody

As destructive migration is still not supported, do you wanna help in how to do a migration or not? The question title say 'yup' but the question body seems to say 'nope'. – Jone

Sorry for confusion :) Well if we don't have destructive migration yet, my guess is that proper migration is the only solution. I would appreciate if you could show how to do it. – Custody

To make things more clear: what I tried to say in the question was that I want to set up destructive migration, but to trigger it I need to find a place where I could see current db scheme version (I think by default it is 1) and hence modify it. For normal migration I still need to change the scheme version. And I could not find any example of how to do that. I also don't know how the complete process of migration looks like. – Custody

@Jone I saw your answer and tried it out today. I faced some issues with gradle but I was able to get migration working by following instructions in your post. I wanted to accept it now and saw that it was removed. If you will post it again I would gladly accept it. Thanks for your help! – Custody

Hi @Marat. I've removed it 'cause I thought you would consider a different answer, leaving space for it. But if you've found it helpful, I'm glad to put it back. – Jone

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.

Jone

I had some follow-up questions about some steps in migration. I hope you don't mind if I write them here in the comments. I will appreciate any help. As I understood overall process looks like: 1) create 1.db file 2) modify database 3) create 1.sqm file 4) create 2.db 5) run verifySqlDelightMigration task. Did I get it right? – Custody

Should I always keep all sqm and db files? I think yes because I can never be sure that all of the app users have updated the app to latest version. Is that correct way of thinking? – Custody

I have almost no experience with gradle outside of default and basic usecases. When creating .db file, after specifying schemaOutputDirectory I went to terminal tab and run ./gradlew tasks. There I saw under SQLDelight section there are 5 generateXXXSchema tasks but no task named exactly generateSqlDelightSchema. I took the first that I saw - generateAndroidDebugMyDatabaseSchema and it created .db file. Is this enough or should I run all 5 separately? Or is these all wrong? – Custody

Yes, it's right to keep all files for a complete migration of an original db. I did it (I'm actually on version 3 of my db). To be honest I always take the db directly from one of my devices, also to keep a look inside of it (on macOS I use DB Browser for SQLite application) and I use it directly by copying and pasting it. – Jone

Anyway I see that all generateXXXSchema preduce the same *.db file in my case. – Jone

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,
)

Chromoprotein