CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/maven-app-cash-sqldelight--runtime-iossimulatorarm64

SQLDelight multiplatform runtime library providing Kotlin APIs for type-safe database operations with compile-time SQL verification

—

Pending
Overview
Eval results
Files

schema-management.mddocs/

Schema Management

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

Capabilities

SqlSchema Interface

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

/**
 * API for creating and migrating a SQL database. The implementation of this interface 
 * is generated by SQLDelight.
 * @param T Says whether the generated code for this database is asynchronous or synchronous.
 *          Most implementations of SqlDriver will require QueryResult.Value for synchronous
 *          runtime, QueryResult.AsyncValue for asynchronous runtime
 */
interface SqlSchema<T : QueryResult<Unit>> {
    /**
     * The version of this schema
     */
    val version: Long
    
    /**
     * Use driver to create the schema from scratch. Assumes no existing database state
     * @param driver SqlDriver instance to execute schema creation statements
     * @return QueryResult indicating completion
     */
    fun create(driver: SqlDriver): T
    
    /**
     * Use driver to migrate from schema oldVersion to newVersion.
     * Each of the callbacks are executed during the migration whenever the upgrade 
     * to the version specified by AfterVersion.afterVersion has been completed
     * @param driver SqlDriver instance to execute migration statements
     * @param oldVersion Current schema version in the database
     * @param newVersion Target schema version to migrate to
     * @param callbacks Migration callbacks to execute at specific version milestones
     * @return QueryResult indicating migration completion
     */
    fun migrate(
        driver: SqlDriver, 
        oldVersion: Long, 
        newVersion: Long, 
        vararg callbacks: AfterVersion
    ): T
}

AfterVersion Class

Represents migration callbacks that execute after reaching specific schema versions.

/**
 * Represents a block of code that should be executed during a migration after the migration
 * has finished migrating to afterVersion
 * @param afterVersion The schema version after which this callback should execute
 * @param block Lambda to execute after reaching the specified version
 */
class AfterVersion(
    val afterVersion: Long,
    val block: (SqlDriver) -> Unit
)

Usage Examples:

import app.cash.sqldelight.db.SqlSchema
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.db.QueryResult
import app.cash.sqldelight.db.AfterVersion

// Example schema implementation (typically generated by SQLDelight)
class UserDatabaseSchema : SqlSchema<QueryResult.Value<Unit>> {
    override val version: Long = 3L
    
    override fun create(driver: SqlDriver): QueryResult.Value<Unit> {
        // Create all tables for the current schema version
        driver.execute(null, """
            CREATE TABLE users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                created_at TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'ACTIVE'
            )
        """.trimIndent(), 0)
        
        driver.execute(null, """
            CREATE TABLE user_profiles (
                user_id INTEGER PRIMARY KEY,
                bio TEXT,
                avatar_url TEXT,
                last_updated TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
            )
        """.trimIndent(), 0)
        
        driver.execute(null, """
            CREATE INDEX idx_users_email ON users (email)
        """.trimIndent(), 0)
        
        driver.execute(null, """
            CREATE INDEX idx_users_status ON users (status)
        """.trimIndent(), 0)
        
        return QueryResult.Value(Unit)
    }
    
    override fun migrate(
        driver: SqlDriver,
        oldVersion: Long,
        newVersion: Long,
        vararg callbacks: AfterVersion
    ): QueryResult.Value<Unit> {
        // Migration logic for each version increment
        var currentVersion = oldVersion
        
        // Migration from version 1 to 2: Add email column
        if (currentVersion < 2 && newVersion >= 2) {
            driver.execute(null, "ALTER TABLE users ADD COLUMN email TEXT", 0)
            driver.execute(null, "CREATE UNIQUE INDEX idx_users_email ON users (email)", 0)
            currentVersion = 2
            
            // Execute callbacks for version 2
            callbacks.filter { it.afterVersion == 2L }.forEach { callback ->
                callback.block(driver)
            }
        }
        
        // Migration from version 2 to 3: Add user_profiles table and status column
        if (currentVersion < 3 && newVersion >= 3) {
            driver.execute(null, "ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'ACTIVE'", 0)
            
            driver.execute(null, """
                CREATE TABLE user_profiles (
                    user_id INTEGER PRIMARY KEY,
                    bio TEXT,
                    avatar_url TEXT,
                    last_updated TEXT NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
                )
            """.trimIndent(), 0)
            
            driver.execute(null, "CREATE INDEX idx_users_status ON users (status)", 0)
            currentVersion = 3
            
            // Execute callbacks for version 3
            callbacks.filter { it.afterVersion == 3L }.forEach { callback ->
                callback.block(driver)
            }
        }
        
        return QueryResult.Value(Unit)
    }
}

// Database initialization with schema management
class UserDatabase private constructor(
    private val driver: SqlDriver,
    private val schema: SqlSchema<QueryResult.Value<Unit>>
) {
    companion object {
        fun create(driver: SqlDriver): UserDatabase {
            val schema = UserDatabaseSchema()
            
            // Check if database exists and get current version
            val currentVersion = getCurrentSchemaVersion(driver)
            
            if (currentVersion == 0L) {
                // Fresh database - create schema
                schema.create(driver)
                setSchemaVersion(driver, schema.version)
            } else if (currentVersion < schema.version) {
                // Existing database needs migration
                schema.migrate(
                    driver = driver,
                    oldVersion = currentVersion,
                    newVersion = schema.version,
                    // Migration callbacks
                    AfterVersion(2L) { driver ->
                        // Populate email field for existing users
                        driver.execute(null, """
                            UPDATE users 
                            SET email = name || '@example.com' 
                            WHERE email IS NULL
                        """.trimIndent(), 0)
                    },
                    AfterVersion(3L) { driver ->
                        // Create default profiles for existing users
                        driver.execute(null, """
                            INSERT INTO user_profiles (user_id, last_updated)
                            SELECT id, datetime('now')
                            FROM users
                            WHERE id NOT IN (SELECT user_id FROM user_profiles)
                        """.trimIndent(), 0)
                    }
                )
                setSchemaVersion(driver, schema.version)
            }
            
            return UserDatabase(driver, schema)
        }
        
        private fun getCurrentSchemaVersion(driver: SqlDriver): Long {
            return try {
                driver.executeQuery(
                    identifier = null,
                    sql = "PRAGMA user_version",
                    mapper = { cursor ->
                        cursor.next()
                        val version = cursor.getLong(0) ?: 0L
                        QueryResult.Value(version)
                    },
                    parameters = 0
                ).value
            } catch (e: Exception) {
                0L // Assume fresh database if version query fails
            }
        }
        
        private fun setSchemaVersion(driver: SqlDriver, version: Long) {
            driver.execute(
                identifier = null,
                sql = "PRAGMA user_version = $version",
                parameters = 0
            )
        }
    }
}

// Async schema example
class AsyncUserDatabaseSchema : SqlSchema<QueryResult.AsyncValue<Unit>> {
    override val version: Long = 2L
    
    override fun create(driver: SqlDriver): QueryResult.AsyncValue<Unit> {
        return QueryResult.AsyncValue {
            // Async schema creation
            driver.execute(null, """
                CREATE TABLE users (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    email TEXT NOT NULL UNIQUE
                )
            """.trimIndent(), 0).await()
            
            driver.execute(null, """
                CREATE INDEX idx_users_email ON users (email)
            """.trimIndent(), 0).await()
        }
    }
    
    override fun migrate(
        driver: SqlDriver,
        oldVersion: Long,
        newVersion: Long,
        vararg callbacks: AfterVersion
    ): QueryResult.AsyncValue<Unit> {
        return QueryResult.AsyncValue {
            var currentVersion = oldVersion
            
            if (currentVersion < 2 && newVersion >= 2) {
                driver.execute(null, "ALTER TABLE users ADD COLUMN email TEXT", 0).await()
                driver.execute(null, "CREATE UNIQUE INDEX idx_users_email ON users (email)", 0).await()
                currentVersion = 2
                
                // Execute async callbacks
                callbacks.filter { it.afterVersion == 2L }.forEach { callback ->
                    callback.block(driver)
                }
            }
        }
    }
}

// Complex migration with data transformation
class ComplexMigrationSchema : SqlSchema<QueryResult.Value<Unit>> {
    override val version: Long = 5L
    
    override fun migrate(
        driver: SqlDriver,
        oldVersion: Long,
        newVersion: Long,
        vararg callbacks: AfterVersion
    ): QueryResult.Value<Unit> {
        var currentVersion = oldVersion
        
        // Version 4: Split name into first_name and last_name
        if (currentVersion < 4 && newVersion >= 4) {
            // Add new columns
            driver.execute(null, "ALTER TABLE users ADD COLUMN first_name TEXT", 0)
            driver.execute(null, "ALTER TABLE users ADD COLUMN last_name TEXT", 0)
            
            // Migrate existing data
            callbacks.filter { it.afterVersion == 4L }.forEach { callback ->
                callback.block(driver)
            }
            
            // Remove old column (SQLite requires table recreation)
            driver.execute(null, """
                CREATE TABLE users_new (
                    id INTEGER PRIMARY KEY,
                    first_name TEXT NOT NULL,
                    last_name TEXT NOT NULL,
                    email TEXT NOT NULL UNIQUE,
                    status TEXT NOT NULL DEFAULT 'ACTIVE'
                )
            """.trimIndent(), 0)
            
            driver.execute(null, """
                INSERT INTO users_new (id, first_name, last_name, email, status)
                SELECT id, first_name, last_name, email, status FROM users
            """.trimIndent(), 0)
            
            driver.execute(null, "DROP TABLE users", 0)
            driver.execute(null, "ALTER TABLE users_new RENAME TO users", 0)
            
            currentVersion = 4
        }
        
        return QueryResult.Value(Unit)
    }
}

// Usage with migration callbacks
val database = UserDatabase.create(
    driver = sqlDriver,
    // Custom migration callbacks can be provided
    migrationCallbacks = arrayOf(
        AfterVersion(2L) { driver ->
            // Custom logic after migrating to version 2
            println("Migrated to version 2, populating email fields...")
            // Populate email addresses from external source
            populateEmailsFromApi(driver)
        },
        AfterVersion(3L) { driver ->
            // Custom logic after migrating to version 3
            println("Migrated to version 3, creating user profiles...")
            // Initialize user profiles with default settings
            initializeUserProfiles(driver)
        }
    )
)

Migration Best Practices

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

Version Management

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

Install with Tessl CLI

npx tessl i tessl/maven-app-cash-sqldelight--runtime-iossimulatorarm64

docs

column-adapters.md

database-driver.md

index.md

logging-debugging.md

query-execution.md

schema-management.md

transaction-management.md

tile.json