Schema Migrations

As your application evolves, you will inevitably need to change your database schema. SQLite.swift provides tools to manage these changes, from simple column additions to more complex refactoring.

Basic Schema Alterations

SQLite has limited direct support for ALTER TABLE statements. SQLite.swift exposes these directly.

Renaming Tables

Use the rename method on a Table object.

let users = Table("users")
let users_old = Table("users_old")

try db.run(users.rename(users_old))
// ALTER TABLE "users" RENAME TO "users_old"

Dropping Tables

Use the drop method.

try db.run(users.drop(ifExists: true))
// DROP TABLE IF EXISTS "users"

Adding Columns

Use the addColumn method. You can specify constraints like defaultValue, check, and references.

let suffix = Expression<String?>("suffix")

try db.run(users.addColumn(suffix, defaultValue: "Jr."))
// ALTER TABLE "users" ADD COLUMN "suffix" TEXT DEFAULT 'Jr.'

SQLite has several restrictions on adding columns, such as not being able to add a PRIMARY KEY column or one with a UNIQUE constraint. For more complex changes, you'll need the SchemaChanger API.

Advanced Migrations with SchemaChanger

For schema changes that SQLite doesn't support directly (like renaming or dropping columns), SQLite.swift provides a SchemaChanger API. It automates the process of creating a new table, copying the data, dropping the old table, and renaming the new one.

Using SchemaChanger

Instantiate a SchemaChanger and use its alter method.

let schemaChanger = SchemaChanger(connection: db)

try schemaChanger.alter(table: "users") { table in
    table.rename(column: "email", to: "email_address")
    table.drop(column: "age")
}

Behind the scenes, this performs a multi-step migration to safely apply your changes without data loss.

Schema Versioning

It's crucial to manage the version of your database schema to apply migrations correctly, especially for users updating your app.

SQLite provides a user_version PRAGMA for this purpose, which SQLite.swift exposes as a convenient property on the Connection object.

You can structure your migration code like this:

let db = try Connection("...")

let currentVersion = db.userVersion

if currentVersion < 1 {
    // Run migration to version 1
    try db.run(users.addColumn(Expression<Date?>("created_at")))
    db.userVersion = 1
}

if currentVersion < 2 {
    // Run migration to version 2
    let schemaChanger = SchemaChanger(connection: db)
    try schemaChanger.alter(table: "users") { t in
        t.rename(column: "age", to: "birth_year")
    }
    db.userVersion = 2
}

This ensures that migrations are applied sequentially and only once. For more complex migration needs, consider using a dedicated library like SQLiteMigrationManager.swift.