SQLDelight in Android: Getting Started

Aug 3 2021 · Kotlin 1.4, Android 11, Android Studio 4.1

Part 1: Preparation & Setup

09. Write Migrations for Your Database

Episode complete

Play next episode

Next
About this episode
Leave a rating/review
See forum comments
Cinema mode Mark complete Download course materials
Previous episode: 08. Utilize Transactions & Rollbacks Next episode: 10. Create Views for Reusable Sub-Queries

Get immediate access to this and 4,000+ other videos and books.

Take your career further with a Kodeco Personal Plan. With unlimited access to over 40+ books and 4,000+ professional videos in a single subscription, it's simply the best investment you can make in your development career.

Learn more Already a subscriber? Sign in.

Heads up... You’re accessing parts of this content for free, with some sections shown as obfuscated text.

Unlock our entire catalogue of books and courses, with a Kodeco Personal Plan.

Unlock now

Heads up... You’re accessing parts of this content for free, with some sections shown as obfuscated text.

Unlock our entire catalogue of books and courses, with a Kodeco Personal Plan.

Unlock now

Let’s face it - the first version of your database will not be the last. Eventually, changes will need to be made to the data, or you want to add new tables or delete old ones.

ALTER TABLE bug
ADD COLUMN imageUrl TEXT;

CREATE TABLE bug (
    bugId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
+    imageUrl TEXT
);

insert {
-    INSERT INTO bug(name, description)
+    INSERT INTO bug(name, description, imageUrl)
    VALUES (:name, :description, :imageUrl);

    INSERT INTO bugAttributes(bugId, size, weight, attack, defense)
    VALUES ((SELECT last_insert_rowid()), :size, :weight, :attack, :defense);
}

findById:
-SELECT bug.bugId, name, description, size, weight, attack, defense FROM bug
+SELECT bug.bugId, name, description, imageUrl, size, weight, attack, defense FROM bug
NATURAL JOIN bugAttributes
WHERE bug.bugId = :bugId;

listBugsInCollection:
SELECT
  bug.bugId,
  name,
+  imageUrl,
  quantity
FROM inCollection
NATURAL JOIN bug
WHERE collectionId = :collectionId;

fun addBug(
    collectionId: Long,
    name: String,
    description: String?,
+    imageUrl: String?,
    size: String,
    weight: String,
    attack: Int,
    defense: Int,
    quantity: Int
) {
    database.bugQueries.transaction {
-        database.bugQueries.insert(name, description, imageUrl, size, weight, attack, defense)
+        database.bugQueries.insert(name, description, size, weight, attack, defense)

        val bugId = database.bugQueries.getLastInsertedId().executeAsOne()

        database.inCollectionQueries.addBugToCollection(
            collectionId = collectionId,
            bugId = bugId,
            quantity = quantity
        )
    }
}

fun getBugById(bugId: Long): Query<BugDetails> {
    return database.bugQueries
-        .findById(bugId) { bugId, name, description, size, weight, attack, defense ->
-            BugDetails(bugId, name, null, description, size, weight, attack, defense)
-        }
+        .findById(bugId) { bugId, name, description, imageUrl, size, weight, attack, defense ->
+            BugDetails(bugId, name, imageUrl, description, size, weight, attack, defense)
+        }
}

fun listBugsInCollection(collectionId: Long): Query<BugWithQuantity> {
    return database.inCollectionQueries
-        .listBugsInCollection(collectionId) { bugId, name, quantity ->
-            BugWithQuantity(bugId, name, imageUrl = null, quantity)
-        }
+        .listBugsInCollection(collectionId) { bugId, name, imageUrl, quantity ->
+            BugWithQuantity(bugId, name, imageUrl, quantity)
+        }
}

fun addBug() {
    // Generate some random data for the bug, then add it
    val bug = Random.nextBug()

    repository.addBug(
        collectionId = collectionId,
        name = bug.name,
        description = bug.description,
+        imageUrl = bug.imageUrl,
        size = bug.size,
        weight = bug.weight,
        attack = bug.atk,
        defense = bug.def,
        quantity = 1
    )
}

databaseRepository = DatabaseRepository(
    AndroidSqliteDriver(
        schema = Database.Schema,
        context = this,
        name = "bugs.db",
+        callback = object : AndroidSqliteDriver.Callback(
+            Database.Schema,
+            AfterVersion(2) {
+                Log.d("App", "Called when upgrading to version 2")
+            }
+        ) {
            override fun onConfigure(db: SupportSQLiteDatabase) {
            super.onConfigure(db)
            db.setForeignKeyConstraintsEnabled(true)
            }
        }
    )
)