0
# Schema Management
1

2
SQLDelight's schema management system provides database creation, migration, and version management capabilities. It enables safe schema evolution across application versions with automated migration scripts and post-migration callback support.
3

4
## Capabilities
5

6
### Schema Interface
7

8
Core interface for database schema management and versioning.
9

10
```kotlin { .api }
11
/**
12
 * API for creating and migrating a SQL database. The implementation of this interface is generated by SQLDelight.
13
 * @param T Says whether the generated code for this database is asynchronous or synchronous. 
14
 *          Most implementations of SqlDriver will require one or the other.
15
 */
16
interface SqlSchema<T : QueryResult<Unit>> {
17
    /**
18
     * The version of this schema
19
     */
20
    val version: Long
21
    
22
    /**
23
     * Use driver to create the schema from scratch. Assumes no existing database state.
24
     * @param driver The database driver to use for schema creation
25
     * @returns QueryResult indicating completion
26
     */
27
    fun create(driver: SqlDriver): T
28
    
29
    /**
30
     * Use driver to migrate from schema oldVersion to newVersion.
31
     * Each of the callbacks are executed during the migration whenever the upgrade 
32
     * to the version specified by AfterVersion.afterVersion has been completed.
33
     * @param driver The database driver to use for migration
34
     * @param oldVersion The current schema version in the database
35
     * @param newVersion The target schema version to migrate to
36
     * @param callbacks Code blocks to execute after specific version upgrades
37
     * @returns QueryResult indicating completion
38
     */
39
    fun migrate(
40
        driver: SqlDriver,
41
        oldVersion: Long,
42
        newVersion: Long,
43
        vararg callbacks: AfterVersion
44
    ): T
45
}
46
```
47

48
**Usage Examples:**
49

50
```kotlin
51
import app.cash.sqldelight.db.SqlSchema
52
import app.cash.sqldelight.db.SqlDriver
53

54
// Generated schema implementation (typically auto-generated by SQLDelight)
55
class DatabaseSchema : SqlSchema<QueryResult.Value<Unit>> {
56
    override val version: Long = 3L
57
    
58
    override fun create(driver: SqlDriver): QueryResult.Value<Unit> {
59
        // Execute all CREATE TABLE statements
60
        driver.execute(null, """
61
            CREATE TABLE users (
62
                id INTEGER PRIMARY KEY AUTOINCREMENT,
63
                name TEXT NOT NULL,
64
                email TEXT UNIQUE NOT NULL,
65
                created_at INTEGER NOT NULL
66
            )
67
        """.trimIndent(), 0)
68
        
69
        driver.execute(null, """
70
            CREATE TABLE profiles (
71
                id INTEGER PRIMARY KEY AUTOINCREMENT,
72
                user_id INTEGER NOT NULL REFERENCES users(id),
73
                bio TEXT,
74
                avatar_url TEXT
75
            )
76
        """.trimIndent(), 0)
77
        
78
        return QueryResult.Unit
79
    }
80
    
81
    override fun migrate(
82
        driver: SqlDriver,
83
        oldVersion: Long,
84
        newVersion: Long,
85
        vararg callbacks: AfterVersion
86
    ): QueryResult.Value<Unit> {
87
        // Handle migrations between versions
88
        for (version in (oldVersion + 1)..newVersion) {
89
            when (version) {
90
                2L -> migrateToVersion2(driver)
91
                3L -> migrateToVersion3(driver)
92
            }
93
            
94
            // Execute callbacks for this version
95
            callbacks.filter { it.afterVersion == version }
96
                .forEach { it.block(driver) }
97
        }
98
        
99
        return QueryResult.Unit
100
    }
101
}
102

103
// Initialize new database
104
val schema = DatabaseSchema()
105
val driver = createSqlDriver()
106

107
// Create fresh database
108
schema.create(driver)
109

110
// Or migrate existing database
111
val currentVersion = getCurrentDatabaseVersion(driver)
112
if (currentVersion < schema.version) {
113
    schema.migrate(driver, currentVersion, schema.version)
114
}
115
```
116

117
### Migration Callbacks
118

119
Execute custom code after specific schema version upgrades.
120

121
```kotlin { .api }
122
/**
123
 * Represents a block of code that should be executed during a migration after 
124
 * the migration has finished migrating to afterVersion.
125
 * @param afterVersion Version after which to execute the block
126
 * @param block Code block to execute, receives the SqlDriver for database operations
127
 */
128
class AfterVersion(
129
    val afterVersion: Long,
130
    val block: (SqlDriver) -> Unit
131
)
132
```
133

134
**Usage Examples:**
135

136
```kotlin
137
import app.cash.sqldelight.db.AfterVersion
138

139
// Data migration after schema upgrade
140
val migrateUserData = AfterVersion(2L) { driver ->
141
    // After migrating to version 2, populate new columns with default values
142
    driver.execute(
143
        identifier = null,
144
        sql = "UPDATE users SET created_at = ? WHERE created_at IS NULL",
145
        parameters = 1
146
    ) {
147
        bindLong(1, System.currentTimeMillis())
148
    }
149
    
150
    println("Populated created_at column for existing users")
151
}
152

153
// Index creation after table modifications
154
val createIndices = AfterVersion(3L) { driver ->
155
    driver.execute(
156
        identifier = null,
157
        sql = "CREATE INDEX idx_users_email ON users(email)",
158
        parameters = 0
159
    )
160
    
161
    driver.execute(
162
        identifier = null,
163
        sql = "CREATE INDEX idx_profiles_user_id ON profiles(user_id)",
164
        parameters = 0
165
    )
166
    
167
    println("Created performance indices")
168
}
169

170
// Data cleanup after structural changes
171
val cleanupOldData = AfterVersion(4L) { driver ->
172
    // Remove orphaned records after foreign key constraints were added
173
    driver.execute(
174
        identifier = null,
175
        sql = "DELETE FROM profiles WHERE user_id NOT IN (SELECT id FROM users)",
176
        parameters = 0
177
    )
178
    
179
    println("Cleaned up orphaned profile records")
180
}
181

182
// Apply migration with callbacks  
183
schema.migrate(
184
    driver = driver,
185
    oldVersion = 1L,
186
    newVersion = 4L,
187
    migrateUserData,
188
    createIndices,
189
    cleanupOldData
190
)
191
```
192

193
### Schema Creation and Initialization
194

195
Create database schema from scratch for new installations.
196

197
**Usage Examples:**
198

199
```kotlin
200
import app.cash.sqldelight.db.SqlSchema
201
import app.cash.sqldelight.db.SqlDriver
202

203
// Complete database initialization
204
fun initializeDatabase(driver: SqlDriver): Database {
205
    val schema = Database.Schema
206
    
207
    // Check if database exists and get version
208
    val currentVersion = try {
209
        getCurrentSchemaVersion(driver)
210
    } catch (e: SQLException) {
211
        // Database doesn't exist yet
212
        0L
213
    }
214
    
215
    when {
216
        currentVersion == 0L -> {
217
            // Fresh installation
218
            println("Creating new database...")
219
            schema.create(driver)
220
            setSchemaVersion(driver, schema.version)
221
            println("Database created with version ${schema.version}")
222
        }
223
        
224
        currentVersion < schema.version -> {
225
            // Migration needed
226
            println("Migrating database from version $currentVersion to ${schema.version}")
227
            schema.migrate(driver, currentVersion, schema.version)
228
            setSchemaVersion(driver, schema.version)
229
            println("Migration completed")
230
        }
231
        
232
        currentVersion > schema.version -> {
233
            // Downgrade scenario (usually not supported)
234
            throw IllegalStateException(
235
                "Database version $currentVersion is newer than app version ${schema.version}"
236
            )
237
        }
238
        
239
        else -> {
240
            println("Database is up to date (version ${schema.version})")
241
        }
242
    }
243
    
244
    return Database(driver)
245
}
246

247
// Helper functions for version management
248
fun getCurrentSchemaVersion(driver: SqlDriver): Long {
249
    return try {
250
        driver.executeQuery(
251
            identifier = null,
252
            sql = "PRAGMA user_version",
253
            mapper = { cursor ->
254
                if (cursor.next().value) {
255
                    QueryResult.Value(cursor.getLong(0) ?: 0L)
256
                } else {
257
                    QueryResult.Value(0L)
258
                }
259
            },
260
            parameters = 0
261
        ).value
262
    } catch (e: SQLException) {
263
        0L
264
    }
265
}
266

267
fun setSchemaVersion(driver: SqlDriver, version: Long) {
268
    driver.execute(
269
        identifier = null,
270
        sql = "PRAGMA user_version = $version",
271
        parameters = 0
272
    )
273
}
274
```
275

276
### Complex Migration Scenarios
277

278
Handle complex database migrations with data transformation and schema restructuring.
279

280
**Usage Examples:**
281

282
```kotlin
283
import app.cash.sqldelight.db.SqlSchema
284
import app.cash.sqldelight.db.AfterVersion
285

286
// Migration with table restructuring
287
class ComplexMigrationSchema : SqlSchema<QueryResult.Value<Unit>> {
288
    override val version: Long = 5L
289
    
290
    override fun migrate(
291
        driver: SqlDriver,
292
        oldVersion: Long,
293
        newVersion: Long,
294
        vararg callbacks: AfterVersion
295
    ): QueryResult.Value<Unit> {
296
        
297
        for (version in (oldVersion + 1)..newVersion) {
298
            when (version) {
299
                2L -> {
300
                    // Add new column
301
                    driver.execute(null, "ALTER TABLE users ADD COLUMN phone TEXT", 0)
302
                }
303
                
304
                3L -> {
305
                    // Restructure table by creating new table and copying data
306
                    driver.execute(null, """
307
                        CREATE TABLE users_new (
308
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
309
                            name TEXT NOT NULL,
310
                            email TEXT UNIQUE NOT NULL,
311
                            phone TEXT,
312
                            created_at INTEGER NOT NULL,
313
                            updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
314
                        )
315
                    """.trimIndent(), 0)
316
                    
317
                    // Copy data from old table
318
                    driver.execute(null, """
319
                        INSERT INTO users_new (id, name, email, phone, created_at, updated_at)
320
                        SELECT id, name, email, phone, created_at, created_at FROM users
321
                    """.trimIndent(), 0)
322
                    
323
                    // Drop old table and rename new one
324
                    driver.execute(null, "DROP TABLE users", 0)
325
                    driver.execute(null, "ALTER TABLE users_new RENAME TO users", 0)
326
                }
327
                
328
                4L -> {
329
                    // Split table into multiple tables
330
                    driver.execute(null, """
331
                        CREATE TABLE user_profiles (
332
                            user_id INTEGER PRIMARY KEY REFERENCES users(id),
333
                            bio TEXT,
334
                            avatar_url TEXT,
335
                            created_at INTEGER NOT NULL
336
                        )
337
                    """.trimIndent(), 0)
338
                    
339
                    // Create user_settings table
340
                    driver.execute(null, """
341
                        CREATE TABLE user_settings (
342
                            user_id INTEGER PRIMARY KEY REFERENCES users(id),
343
                            theme TEXT NOT NULL DEFAULT 'light',
344
                            notifications BOOLEAN NOT NULL DEFAULT 1,
345
                            language TEXT NOT NULL DEFAULT 'en'
346
                        )
347
                    """.trimIndent(), 0)
348
                }
349
                
350
                5L -> {
351
                    // Add foreign key constraints (recreate table on SQLite)
352
                    driver.execute(null, "PRAGMA foreign_keys = OFF", 0)
353
                    
354
                    // Create new table with constraints
355
                    driver.execute(null, """
356
                        CREATE TABLE profiles_new (
357
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
358
                            user_id INTEGER NOT NULL,
359
                            bio TEXT,
360
                            avatar_url TEXT,
361
                            created_at INTEGER NOT NULL,
362
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
363
                        )
364
                    """.trimIndent(), 0)
365
                    
366
                    // Copy data
367
                    driver.execute(null, """
368
                        INSERT INTO profiles_new SELECT * FROM profiles
369
                    """.trimIndent(), 0)
370
                    
371
                    // Replace old table
372
                    driver.execute(null, "DROP TABLE profiles", 0)
373
                    driver.execute(null, "ALTER TABLE profiles_new RENAME TO profiles", 0)
374
                    driver.execute(null, "PRAGMA foreign_keys = ON", 0)
375
                }
376
            }
377
            
378
            // Execute version-specific callbacks
379
            callbacks.filter { it.afterVersion == version }
380
                .forEach { it.block(driver) }
381
        }
382
        
383
        return QueryResult.Unit
384
    }
385
}
386

387
// Data transformation callbacks
388
val transformUserData = AfterVersion(3L) { driver ->
389
    // Normalize phone numbers after adding phone column
390
    val users = driver.executeQuery(
391
        null,
392
        "SELECT id, phone FROM users WHERE phone IS NOT NULL",
393
        { cursor ->
394
            val users = mutableListOf<Pair<Long, String>>()
395
            while (cursor.next().value) {
396
                val id = cursor.getLong(0)!!
397
                val phone = cursor.getString(1)
398
                if (phone != null) {
399
                    users.add(id to normalizePhoneNumber(phone))
400
                }
401
            }
402
            QueryResult.Value(users)
403
        },
404
        0
405
    ).value
406
    
407
    // Update with normalized numbers
408
    users.forEach { (id, normalizedPhone) ->
409
        driver.execute(
410
            null,
411
            "UPDATE users SET phone = ? WHERE id = ?",
412
            2
413
        ) {
414
            bindString(1, normalizedPhone)
415
            bindLong(2, id)
416
        }
417
    }
418
}
419

420
val populateDefaultSettings = AfterVersion(4L) { driver ->
421
    // Create default settings for all existing users
422
    driver.execute(null, """
423
        INSERT INTO user_settings (user_id, theme, notifications, language)
424
        SELECT id, 'light', 1, 'en' FROM users
425
        WHERE id NOT IN (SELECT user_id FROM user_settings)
426
    """.trimIndent(), 0)
427
}
428

429
// Apply complex migration
430
schema.migrate(
431
    driver = driver,
432
    oldVersion = 1L,
433
    newVersion = 5L,
434
    transformUserData,
435
    populateDefaultSettings
436
)
437
```
438

439
### Error Handling and Rollback
440

441
Implement robust error handling and rollback strategies for failed migrations.
442

443
**Usage Examples:**
444

445
```kotlin
446
import app.cash.sqldelight.db.SqlSchema
447
import app.cash.sqldelight.Transacter
448

449
// Safe migration with transaction rollback
450
fun safeMigrate(
451
    schema: SqlSchema<QueryResult.Value<Unit>>,
452
    driver: SqlDriver,
453
    oldVersion: Long
454
): Boolean {
455
    return try {
456
        // Wrap migration in transaction for automatic rollback
457
        (driver as? Transacter)?.transactionWithResult { 
458
            // Backup current version
459
            val currentVersion = getCurrentSchemaVersion(driver)
460
            
461
            try {
462
                schema.migrate(driver, oldVersion, schema.version)
463
                setSchemaVersion(driver, schema.version)
464
                true
465
            } catch (e: Exception) {
466
                println("Migration failed: ${e.message}")
467
                // Transaction will automatically rollback
468
                throw e
469
            }
470
        } ?: run {
471
            // Fallback for drivers that don't support transactions
472
            schema.migrate(driver, oldVersion, schema.version)
473
            setSchemaVersion(driver, schema.version)
474
            true
475
        }
476
    } catch (e: Exception) {
477
        println("Migration failed and was rolled back: ${e.message}")
478
        false
479
    }
480
}
481

482
// Migration with backup and restore capability
483
fun migrateWithBackup(
484
    schema: SqlSchema<QueryResult.Value<Unit>>,
485
    driver: SqlDriver,
486
    oldVersion: Long
487
): MigrationResult {
488
    return try {
489
        // Create backup before migration
490
        val backupPath = createDatabaseBackup(driver)
491
        
492
        try {
493
            schema.migrate(driver, oldVersion, schema.version)
494
            setSchemaVersion(driver, schema.version)
495
            
496
            MigrationResult.Success(schema.version)
497
        } catch (e: Exception) {
498
            // Restore from backup on failure
499
            restoreDatabaseBackup(driver, backupPath)
500
            MigrationResult.Failed(e.message ?: "Unknown error", oldVersion)
501
        }
502
    } catch (e: Exception) {
503
        MigrationResult.Failed("Backup creation failed: ${e.message}", oldVersion)
504
    }
505
}
506

507
sealed class MigrationResult {
508
    data class Success(val newVersion: Long) : MigrationResult()
509
    data class Failed(val error: String, val currentVersion: Long) : MigrationResult()
510
}
511

512
// Validation after migration
513
fun validateMigration(driver: SqlDriver, expectedVersion: Long): Boolean {
514
    return try {
515
        val actualVersion = getCurrentSchemaVersion(driver)
516
        if (actualVersion != expectedVersion) {
517
            println("Version mismatch: expected $expectedVersion, got $actualVersion")
518
            return false
519
        }
520
        
521
        // Validate table structure
522
        val tables = getTableNames(driver)
523
        val requiredTables = setOf("users", "profiles", "user_settings")
524
        
525
        if (!tables.containsAll(requiredTables)) {
526
            println("Missing required tables. Found: $tables, Required: $requiredTables")
527
            return false
528
        }
529
        
530
        // Validate data integrity
531
        val userCount = driver.executeQuery(
532
            null,
533
            "SELECT COUNT(*) FROM users",
534
            { cursor ->
535
                cursor.next()
536
                QueryResult.Value(cursor.getLong(0) ?: 0L)
537
            },
538
            0
539
        ).value
540
        
541
        val profileCount = driver.executeQuery(
542
            null,
543
            "SELECT COUNT(*) FROM profiles",
544
            { cursor ->
545
                cursor.next()
546
                QueryResult.Value(cursor.getLong(0) ?: 0L)
547
            },
548
            0
549
        ).value
550
        
551
        println("Migration validation passed: $userCount users, $profileCount profiles")
552
        true
553
    } catch (e: Exception) {
554
        println("Migration validation failed: ${e.message}")
555
        false
556
    }
557
}
558

559
// Usage with validation
560
val migrationResult = migrateWithBackup(schema, driver, currentVersion)
561
when (migrationResult) {
562
    is MigrationResult.Success -> {
563
        if (validateMigration(driver, migrationResult.newVersion)) {
564
            println("Migration completed and validated successfully")
565
        } else {
566
            println("Migration completed but validation failed")
567
        }
568
    }
569
    is MigrationResult.Failed -> {
570
        println("Migration failed: ${migrationResult.error}")
571
        println("Database remains at version ${migrationResult.currentVersion}")
572
    }
573
}
574
```