Modifying the Database Schema

How to modify the database schema

DatabaseSchemaModifications.md

Overview

For modifying the database schema, prefer Swift methods over raw SQL queries. They allow the compiler to check if a schema change is available on the target operating system. Only use a raw SQL query when no Swift method exist (when creating triggers, for example).

When a schema change is not directly supported by SQLite, or not available on the target operating system, database tables have to be recreated. See Migrations for the detailed procedure.

Create Tables

The create(table:options:body:) method covers nearly all SQLite table creation features. For virtual tables, see Full-Text Search, or use raw SQL.

// CREATE TABLE place (
//   id INTEGER PRIMARY KEY AUTOINCREMENT,
//   title TEXT,
//   favorite BOOLEAN NOT NULL DEFAULT 0,
//   latitude DOUBLE NOT NULL,
//   longitude DOUBLE NOT NULL
// )
try db.create(table: "place") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("title", .text)
    t.column("favorite", .boolean).notNull().defaults(to: false)
    t.column("longitude", .double).notNull()
    t.column("latitude", .double).notNull()
}

Configure table creation

// CREATE TABLE player ( ... )
try db.create(table: "player") { t in ... }
    
// CREATE TEMPORARY TABLE player IF NOT EXISTS (
try db.create(table: "player", options: [.temporary, .ifNotExists]) { t in ... }

Reference: TableOptions

Add regular columns with their name and eventual type (text, integer, double, real, numeric, boolean, blob, date, datetime, any, and json) - see SQLite data types and JSON Support:

// CREATE TABLE player (
//   score,
//   name TEXT,
//   creationDate DATETIME,
//   address TEXT,
try db.create(table: "player") { t in
    t.column("score")
    t.column("name", .text)
    t.column("creationDate", .datetime)
    t.column("address", .json)

Reference: column(_:_:)

Define not null columns, and set default values:

    // email TEXT NOT NULL,
    t.column("email", .text).notNull()
    
    // name TEXT NOT NULL DEFAULT 'Anonymous',
    t.column("name", .text).notNull().defaults(to: "Anonymous")

Reference: ColumnDefinition

Define primary, unique, or foreign keys. When defining a foreign key, the referenced column is the primary key of the referenced table (unless you specify otherwise):

    // id INTEGER PRIMARY KEY AUTOINCREMENT,
    t.autoIncrementedPrimaryKey("id")

    // uuid TEXT PRIMARY KEY NOT NULL,
    t.primaryKey("uuid", .text)

    // teamName TEXT NOT NULL,
    // position INTEGER NOT NULL,
    // PRIMARY KEY (teamName, position),
    t.primaryKey {
        t.column("teamName", .text)
        t.column("position", .integer)
    }

    // email TEXT UNIQUE,
    t.column("email", .text).unique()

    // teamId TEXT REFERENCES team(id) ON DELETE CASCADE,
    // countryCode TEXT REFERENCES country(code) NOT NULL,
    t.belongsTo("team", onDelete: .cascade)
    t.belongsTo("country").notNull()

Reference: TableDefinition, unique(onConflict:)

Create an index on a column

    t.column("score", .integer).indexed()

Reference: ColumnDefinition

For extra index options, see Create Indexes below.

Perform integrity checks on individual columns, and SQLite will only let conforming rows in. In the example below, the $0 closure variable is a column which lets you build any SQL expression.

    // name TEXT CHECK (LENGTH(name) > 0)
    // score INTEGER CHECK (score > 0)
    t.column("name", .text).check { length($0) > 0 }
    t.column("score", .integer).check(sql: "score > 0")

Reference: ColumnDefinition

Columns can also be defined with a raw sql String, or an SQL literal in which you can safely embed raw values without any risk of syntax errors or SQL injection:

    t.column(sql: "name TEXT")
    
    let defaultName: String = ...
    t.column(literal: "name TEXT DEFAULT \(defaultName)")

Reference: TableDefinition

Other table constraints can involve several columns:

    // PRIMARY KEY (a, b),
    t.primaryKey(["a", "b"])
    
    // UNIQUE (a, b) ON CONFLICT REPLACE,
    t.uniqueKey(["a", "b"], onConflict: .replace)
    
    // FOREIGN KEY (a, b) REFERENCES parents(c, d),
    t.foreignKey(["a", "b"], references: "parents")
    
    // CHECK (a + b < 10),
    t.check(Column("a") + Column("b") < 10)
    
    // CHECK (a + b < 10)
    t.check(sql: "a + b < 10")
    
    // Raw SQL constraints
    t.constraint(sql: "CHECK (a + b < 10)")
    t.constraint(literal: "CHECK (a + b < \(10))")

Reference: TableDefinition

Generated columns:

    t.column("totalScore", .integer).generatedAs(sql: "score + bonus")
    t.column("totalScore", .integer).generatedAs(Column("score") + Column("bonus"))
}

Reference: ColumnDefinition

Modify Tables

SQLite lets you modify existing tables:

// ALTER TABLE referer RENAME TO referrer
try db.rename(table: "referer", to: "referrer")

// ALTER TABLE player ADD COLUMN hasBonus BOOLEAN
// ALTER TABLE player RENAME COLUMN url TO homeURL
// ALTER TABLE player DROP COLUMN score
try db.alter(table: "player") { t in
    t.add(column: "hasBonus", .boolean)
    t.rename(column: "url", to: "homeURL")
    t.drop(column: "score")
}

Reference: TableAlteration

Drop Tables

Drop tables with the drop(table:) method:

try db.drop(table: "obsolete")

Create Indexes

Create an index on a column:

try db.create(table: "player") { t in
    t.column("email", .text).unique()
    t.column("score", .integer).indexed()
}

Create indexes on an existing table:

// CREATE INDEX index_player_on_email ON player(email)
try db.create(indexOn: "player", columns: ["email"])

// CREATE UNIQUE INDEX index_player_on_email ON player(email)
try db.create(indexOn: "player", columns: ["email"], options: .unique)

Create indexes with a specific collation:

// CREATE INDEX index_player_on_email ON player(email COLLATE NOCASE)
try db.create(
    index: "index_player_on_email",
    on: "player",
    expressions: [Column("email").collating(.nocase)])

Create indexes on expressions:

// CREATE INDEX index_player_on_total_score ON player(score+bonus)
try db.create(
    index: "index_player_on_total_score",
    on: "player",
    expressions: [Column("score") + Column("bonus")])

// CREATE INDEX index_player_on_country ON player(address ->> 'country')
try db.create(
    index: "index_player_on_country",
    on: "player",
    expressions: [
        JSONColumn("address")["country"],
    ])

Unique constraints and unique indexes are somewhat different: don’t miss the tip in doc:DatabaseSchemaRecommendations/Unique-keys-should-be-supported-by-unique-indexes below.

Database Tables

Database Views

Database Indexes

Sunsetted Methods

Those are legacy interfaces that are preserved for backwards compatibility. Their use is not recommended.