0
# Schema Management
1

2
Database schema creation, versioning, and migration support with callback-based upgrade logic and version management for maintaining database structure consistency.
3

4
## Capabilities
5

6
### SqlSchema Interface
7

8
API for creating and migrating SQL database schemas with version management.
9

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

50
### AfterVersion Class
51

52
Represents migration callbacks that execute after reaching specific schema versions.
53

54
```kotlin { .api }
55
/**
56
 * Represents a block of code that should be executed during a migration after the migration
57
 * has finished migrating to afterVersion
58
 * @param afterVersion The schema version after which this callback should execute
59
 * @param block Lambda to execute after reaching the specified version
60
 */
61
class AfterVersion(
62
    val afterVersion: Long,
63
    val block: (SqlDriver) -> Unit
64
)
65
```
66

67
**Usage Examples:**
68

69
```kotlin
70
import app.cash.sqldelight.db.SqlSchema
71
import app.cash.sqldelight.db.SqlDriver
72
import app.cash.sqldelight.db.QueryResult
73
import app.cash.sqldelight.db.AfterVersion
74

75
// Example schema implementation (typically generated by SQLDelight)
76
class UserDatabaseSchema : SqlSchema<QueryResult.Value<Unit>> {
77
    override val version: Long = 3L
78
    
79
    override fun create(driver: SqlDriver): QueryResult.Value<Unit> {
80
        // Create all tables for the current schema version
81
        driver.execute(null, """
82
            CREATE TABLE users (
83
                id INTEGER PRIMARY KEY,
84
                name TEXT NOT NULL,
85
                email TEXT NOT NULL UNIQUE,
86
                created_at TEXT NOT NULL,
87
                status TEXT NOT NULL DEFAULT 'ACTIVE'
88
            )
89
        """.trimIndent(), 0)
90
        
91
        driver.execute(null, """
92
            CREATE TABLE user_profiles (
93
                user_id INTEGER PRIMARY KEY,
94
                bio TEXT,
95
                avatar_url TEXT,
96
                last_updated TEXT NOT NULL,
97
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
98
            )
99
        """.trimIndent(), 0)
100
        
101
        driver.execute(null, """
102
            CREATE INDEX idx_users_email ON users (email)
103
        """.trimIndent(), 0)
104
        
105
        driver.execute(null, """
106
            CREATE INDEX idx_users_status ON users (status)
107
        """.trimIndent(), 0)
108
        
109
        return QueryResult.Value(Unit)
110
    }
111
    
112
    override fun migrate(
113
        driver: SqlDriver,
114
        oldVersion: Long,
115
        newVersion: Long,
116
        vararg callbacks: AfterVersion
117
    ): QueryResult.Value<Unit> {
118
        // Migration logic for each version increment
119
        var currentVersion = oldVersion
120
        
121
        // Migration from version 1 to 2: Add email column
122
        if (currentVersion < 2 && newVersion >= 2) {
123
            driver.execute(null, "ALTER TABLE users ADD COLUMN email TEXT", 0)
124
            driver.execute(null, "CREATE UNIQUE INDEX idx_users_email ON users (email)", 0)
125
            currentVersion = 2
126
            
127
            // Execute callbacks for version 2
128
            callbacks.filter { it.afterVersion == 2L }.forEach { callback ->
129
                callback.block(driver)
130
            }
131
        }
132
        
133
        // Migration from version 2 to 3: Add user_profiles table and status column
134
        if (currentVersion < 3 && newVersion >= 3) {
135
            driver.execute(null, "ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'ACTIVE'", 0)
136
            
137
            driver.execute(null, """
138
                CREATE TABLE user_profiles (
139
                    user_id INTEGER PRIMARY KEY,
140
                    bio TEXT,
141
                    avatar_url TEXT,
142
                    last_updated TEXT NOT NULL,
143
                    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
144
                )
145
            """.trimIndent(), 0)
146
            
147
            driver.execute(null, "CREATE INDEX idx_users_status ON users (status)", 0)
148
            currentVersion = 3
149
            
150
            // Execute callbacks for version 3
151
            callbacks.filter { it.afterVersion == 3L }.forEach { callback ->
152
                callback.block(driver)
153
            }
154
        }
155
        
156
        return QueryResult.Value(Unit)
157
    }
158
}
159

160
// Database initialization with schema management
161
class UserDatabase private constructor(
162
    private val driver: SqlDriver,
163
    private val schema: SqlSchema<QueryResult.Value<Unit>>
164
) {
165
    companion object {
166
        fun create(driver: SqlDriver): UserDatabase {
167
            val schema = UserDatabaseSchema()
168
            
169
            // Check if database exists and get current version
170
            val currentVersion = getCurrentSchemaVersion(driver)
171
            
172
            if (currentVersion == 0L) {
173
                // Fresh database - create schema
174
                schema.create(driver)
175
                setSchemaVersion(driver, schema.version)
176
            } else if (currentVersion < schema.version) {
177
                // Existing database needs migration
178
                schema.migrate(
179
                    driver = driver,
180
                    oldVersion = currentVersion,
181
                    newVersion = schema.version,
182
                    // Migration callbacks
183
                    AfterVersion(2L) { driver ->
184
                        // Populate email field for existing users
185
                        driver.execute(null, """
186
                            UPDATE users 
187
                            SET email = name || '@example.com' 
188
                            WHERE email IS NULL
189
                        """.trimIndent(), 0)
190
                    },
191
                    AfterVersion(3L) { driver ->
192
                        // Create default profiles for existing users
193
                        driver.execute(null, """
194
                            INSERT INTO user_profiles (user_id, last_updated)
195
                            SELECT id, datetime('now')
196
                            FROM users
197
                            WHERE id NOT IN (SELECT user_id FROM user_profiles)
198
                        """.trimIndent(), 0)
199
                    }
200
                )
201
                setSchemaVersion(driver, schema.version)
202
            }
203
            
204
            return UserDatabase(driver, schema)
205
        }
206
        
207
        private fun getCurrentSchemaVersion(driver: SqlDriver): Long {
208
            return try {
209
                driver.executeQuery(
210
                    identifier = null,
211
                    sql = "PRAGMA user_version",
212
                    mapper = { cursor ->
213
                        cursor.next()
214
                        val version = cursor.getLong(0) ?: 0L
215
                        QueryResult.Value(version)
216
                    },
217
                    parameters = 0
218
                ).value
219
            } catch (e: Exception) {
220
                0L // Assume fresh database if version query fails
221
            }
222
        }
223
        
224
        private fun setSchemaVersion(driver: SqlDriver, version: Long) {
225
            driver.execute(
226
                identifier = null,
227
                sql = "PRAGMA user_version = $version",
228
                parameters = 0
229
            )
230
        }
231
    }
232
}
233

234
// Async schema example
235
class AsyncUserDatabaseSchema : SqlSchema<QueryResult.AsyncValue<Unit>> {
236
    override val version: Long = 2L
237
    
238
    override fun create(driver: SqlDriver): QueryResult.AsyncValue<Unit> {
239
        return QueryResult.AsyncValue {
240
            // Async schema creation
241
            driver.execute(null, """
242
                CREATE TABLE users (
243
                    id INTEGER PRIMARY KEY,
244
                    name TEXT NOT NULL,
245
                    email TEXT NOT NULL UNIQUE
246
                )
247
            """.trimIndent(), 0).await()
248
            
249
            driver.execute(null, """
250
                CREATE INDEX idx_users_email ON users (email)
251
            """.trimIndent(), 0).await()
252
        }
253
    }
254
    
255
    override fun migrate(
256
        driver: SqlDriver,
257
        oldVersion: Long,
258
        newVersion: Long,
259
        vararg callbacks: AfterVersion
260
    ): QueryResult.AsyncValue<Unit> {
261
        return QueryResult.AsyncValue {
262
            var currentVersion = oldVersion
263
            
264
            if (currentVersion < 2 && newVersion >= 2) {
265
                driver.execute(null, "ALTER TABLE users ADD COLUMN email TEXT", 0).await()
266
                driver.execute(null, "CREATE UNIQUE INDEX idx_users_email ON users (email)", 0).await()
267
                currentVersion = 2
268
                
269
                // Execute async callbacks
270
                callbacks.filter { it.afterVersion == 2L }.forEach { callback ->
271
                    callback.block(driver)
272
                }
273
            }
274
        }
275
    }
276
}
277

278
// Complex migration with data transformation
279
class ComplexMigrationSchema : SqlSchema<QueryResult.Value<Unit>> {
280
    override val version: Long = 5L
281
    
282
    override fun migrate(
283
        driver: SqlDriver,
284
        oldVersion: Long,
285
        newVersion: Long,
286
        vararg callbacks: AfterVersion
287
    ): QueryResult.Value<Unit> {
288
        var currentVersion = oldVersion
289
        
290
        // Version 4: Split name into first_name and last_name
291
        if (currentVersion < 4 && newVersion >= 4) {
292
            // Add new columns
293
            driver.execute(null, "ALTER TABLE users ADD COLUMN first_name TEXT", 0)
294
            driver.execute(null, "ALTER TABLE users ADD COLUMN last_name TEXT", 0)
295
            
296
            // Migrate existing data
297
            callbacks.filter { it.afterVersion == 4L }.forEach { callback ->
298
                callback.block(driver)
299
            }
300
            
301
            // Remove old column (SQLite requires table recreation)
302
            driver.execute(null, """
303
                CREATE TABLE users_new (
304
                    id INTEGER PRIMARY KEY,
305
                    first_name TEXT NOT NULL,
306
                    last_name TEXT NOT NULL,
307
                    email TEXT NOT NULL UNIQUE,
308
                    status TEXT NOT NULL DEFAULT 'ACTIVE'
309
                )
310
            """.trimIndent(), 0)
311
            
312
            driver.execute(null, """
313
                INSERT INTO users_new (id, first_name, last_name, email, status)
314
                SELECT id, first_name, last_name, email, status FROM users
315
            """.trimIndent(), 0)
316
            
317
            driver.execute(null, "DROP TABLE users", 0)
318
            driver.execute(null, "ALTER TABLE users_new RENAME TO users", 0)
319
            
320
            currentVersion = 4
321
        }
322
        
323
        return QueryResult.Value(Unit)
324
    }
325
}
326

327
// Usage with migration callbacks
328
val database = UserDatabase.create(
329
    driver = sqlDriver,
330
    // Custom migration callbacks can be provided
331
    migrationCallbacks = arrayOf(
332
        AfterVersion(2L) { driver ->
333
            // Custom logic after migrating to version 2
334
            println("Migrated to version 2, populating email fields...")
335
            // Populate email addresses from external source
336
            populateEmailsFromApi(driver)
337
        },
338
        AfterVersion(3L) { driver ->
339
            // Custom logic after migrating to version 3
340
            println("Migrated to version 3, creating user profiles...")
341
            // Initialize user profiles with default settings
342
            initializeUserProfiles(driver)
343
        }
344
    )
345
)
346
```
347

348
### Migration Best Practices
349

350
1. **Incremental Migrations**: Always migrate one version at a time to ensure data integrity
351
2. **Backup Critical Data**: Use AfterVersion callbacks to backup important data before destructive changes
352
3. **Test Migrations**: Thoroughly test migration paths with realistic data sets
353
4. **Rollback Planning**: Consider how to handle migration failures and potential rollbacks
354
5. **Performance**: For large datasets, consider batching operations and progress tracking
355
6. **Validation**: Use callbacks to validate data integrity after each migration step
356

357
### Version Management
358

359
- **Schema versions** should be incremented for any schema changes
360
- **Version storage** is typically handled using database-specific mechanisms (e.g., PRAGMA user_version in SQLite)
361
- **Version validation** ensures migrations are applied in the correct order
362
- **Callback execution** allows custom logic at specific migration milestones