SQLDelight Migration

Question

I am trying to add more columns in table in SQLDelight. I have made a migration file 1.sqm. In migration file it gives error that no table found.

My build.gradle.kts:

sqldelight {
    database("AppDatabase") {
        packageName = "com.jetbrains.handson.kmm.shared.cache"
        schemaOutputDirectory = file("shared/src/commonMain/sqldelight/com/jetbrains/handson/kmm/shared/cache/AppDatabase.sq")
        migrationOutputDirectory = file("shared/src/commonMain/sqldelight/migrations/1.sqm")
        migrationOutputFileFormat = ".sqm" 
        schemaOutputDirectory
        deriveSchemaFromMigrations = true
    }
}

Create table statement:

CREATE TABLE pos_orders(id INTEGER AS Int PRIMARY KEY, orderId INTEGER AS Int , dateandtime TEXT, restaurant_id INTEGER AS Int, restaurant_name TEXT, deliveryType_description TEXT, paymentType_description TEXT, totalAmount REAL AS Float,
process_order INTEGER AS Int, pos_accept INTEGER AS Int, pos_printed INTEGER AS Int, status INTEGER AS Int, prep_time INTEGER AS Int, desired_delivery_time TEXT, restaurant_address TEXT, customerNo TEXT, title TEXT, firstName TEXT, lastName TEXT,company TEXT, street TEXT, houseNo TEXT, zip TEXT, city TEXT, state TEXT,floor TEXT, bezirk TEXT, bellname TEXT, email TEXT, phoneNo TEXT, fax TEXT, taxAmount REAL AS Float, total REAL AS Float , deliverycharges REAL AS Float,
deliveryType INTEGER AS Int, paymentType INTEGER AS Int, comment TEXT, order_note TEXT, sender TEXT, cancelation_cause TEXT, demo_order INTEGER AS Int, orderDate TEXT, updated TEXT);

Hatch

What does the create table statement look like? – Watercool

Sorry, i have added in Question body. – Hatch

I would remove everything from that config except packageName. See example: github.com/touchlab/KaMPKit/blob/main/shared/…. Everything from schemaOutputDirectory on down is just going to confuse the IDE plugin. – Watercool

nothing helped i am writing these Alter table quesries in migration file. – Hatch

How do you alter a table to add new column in SQLDeligt? – Hatch

I have same problem. Did you solved this problem for you? What helped? – Corollary

Answer 1

sqldelight {
    database("AppDatabase") {
        packageName = "com.jetbrains.handson.kmm.shared.cache"
        schemaOutputDirectory = file("com.jetbrains.handson.kmm.shared.cache")
        migrationOutputDirectory = file("com.jetbrains.handson.kmm.shared.cache")
        deriveSchemaFromMigrations = true
        verifyMigrations = true
    }
}

change the gradle.build to the above one then in the path exactly as i write with the two folders sqldelight

shared/src/commonMain/sqldelight/com/jetbrains/handson/kmm/shared/cache/sqldelight

---

in the sqldelight folder put all your database files AppDatabase.sq, 1.sqm, 2.sqm ... so on

the trick is here in the AppDatabase.sq put only the db queries.

AppDatabase should looklike

getAll:
SELECT * FROM pos_orders;

deleteAll:
DELETE FROM pos_orders;

and create another one 1.sqm to put your original tables and create 2.sqm to put your migration.

1.sqm should looklike

CREATE TABLE pos_orders(id INTEGER AS Int PRIMARY KEY, orderId INTEGER AS Int , dateandtime TEXT, restaurant_id INTEGER AS Int, restaurant_name TEXT, deliveryType_description TEXT, paymentType_description TEXT, totalAmount REAL AS Float,
process_order INTEGER AS Int, pos_accept INTEGER AS Int, pos_printed INTEGER AS Int, status INTEGER AS Int, prep_time INTEGER AS Int, desired_delivery_time TEXT, restaurant_address TEXT, customerNo TEXT, title TEXT, firstName TEXT, lastName TEXT,company TEXT, street TEXT, houseNo TEXT, zip TEXT, city TEXT, state TEXT,floor TEXT, bezirk TEXT, bellname TEXT, email TEXT, phoneNo TEXT, fax TEXT, taxAmount REAL AS Float, total REAL AS Float , deliverycharges REAL AS Float,
deliveryType INTEGER AS Int, paymentType INTEGER AS Int, comment TEXT, order_note TEXT, sender TEXT, cancelation_cause TEXT, demo_order INTEGER AS Int, orderDate TEXT, updated TEXT);

2.sqm should looklike

ALTER TABLE pos_orders ADD COLUMN year INTEGER ;

Ladd

Just one confirmation, to complete the migration if the above setup is made, Do we need to write any code or SQLDelight will handle the migration internally ? Thanks in advance – Ingroup

Let me know when you see this thanks @Fady Emad – Ingroup

@Ingroup the migration will automatically be triggered once you create a new file .sqm with respect to the numeric sequence because the SQLDelight will take the file sequentially and run it, 1.sqm then 2.sqm then 3.sqm and so on like with the above example if you want to create a new table you can create 3.sqm and put CREATE TABLE table_name – Ladd

Any ideas why the .sqm files cannot resolve the table names etc in Android Studio e.g. No table found with name X. The migration still works, so it's not a huge deal, but verifyMigrations=true fails. EDIT see github.com/cashapp/sqldelight/issues/2520 – Doran

@Doran I don't have a reference for that, but by experience, I noticed that the .sqm should hold the migration scripts and you can not create a table in it. – Ladd

It looks like the SQLDelight plugin for Android Studio don't recognize table names but the migrations runs OK in my case. – Cootie

Answer 2

Had the same issue. When I deleted everything from gradle except packageName, the error "No table found..." was still there, but migration worked as intended.

Here's my example, I needed to delete some columns from table

sqldelight {
    database("AppDatabase") {
        packageName = "com.my.domain.db"
    }
}

1.sqm file

CREATE TEMPORARY TABLE profileTemp (
id TEXT NOT NULL PRIMARY KEY,
name TEXT,
birthdate TEXT,
);
INSERT INTO profileTemp SELECT id, name, birthdate, FROM ProfileSQL;
DROP TABLE ProfileSQL;
CREATE TABLE ProfileSQL (
id TEXT NOT NULL PRIMARY KEY,
name TEXT,
birthdate TEXT,
);
INSERT INTO ProfileSQL SELECT id, name, birthdate, FROM profileTemp;
DROP TABLE profileTemp;

Brander