Modifying the Database Schema
How to modify the database schema
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
func alter(table: String, body: (TableAlteration) -> Void
) throws Modifies a database table.
func create(table: String, options: TableOptions, body: (TableDefinition) throws -> Void
) throws Creates a database table.
func create(virtualTable: String, ifNotExists: Bool, using: String
) throws Creates a virtual database table.
func create<Module>(virtualTable: String, ifNotExists: Bool, using: Module, ((Module.TableDefinition) throws -> Void)?
) throws Creates a virtual database table.
func drop(table: String
) throws Deletes a database table.
func dropFTS4SynchronizationTriggers(forTable: String
) throws Deletes the synchronization triggers for a synchronized FTS4 table.
func dropFTS5SynchronizationTriggers(forTable: String
) throws Deletes the synchronization triggers for a synchronized FTS5 table.
func rename(table: String, to: String
) throws Renames a database table.
struct ColumnType
An SQL column type.
enum ConflictResolution
An SQLite conflict resolution.
enum ForeignKeyAction
A foreign key action.
class TableAlteration
A
TableDefinition
lets you modify the components of a database table.class TableDefinition
A
TableDefinition
lets you define the components of a database table.struct TableOptions
Table creation options.
protocol VirtualTableModule
The protocol for SQLite virtual table modules.
Database Views
func create(view: String, options: ViewOptions, columns: [String]?, as: SQLSubqueryable
) throws Creates a database view.
func create(view: String, options: ViewOptions, columns: [String]?, asLiteral: SQL
) throws Creates a database view.
func drop(view: String
) throws Deletes a database view.
struct ViewOptions
View creation options
Database Indexes
func create(indexOn: String, columns: [String], options: IndexOptions, condition: (any SQLExpressible)?
) throws Creates an index with a default name on the specified table and columns.
func create(index: String, on: String, columns: [String], options: IndexOptions, condition: (any SQLExpressible)?
) throws Creates an index on the specified table and columns.
func create(index: String, on: String, expressions: [any SQLExpressible], options: IndexOptions, condition: (any SQLExpressible)?
) throws Creates an index on the specified table and expressions.
func drop(indexOn: String, columns: [String]
) throws Deletes the database index on the specified table and columns if exactly one such index exists.
func drop(index: String
) throws Deletes a database index.
struct IndexOptions
Index creation options
Sunsetted Methods
Those are legacy interfaces that are preserved for backwards compatibility. Their use is not recommended.
func create(index: String, on: String, columns: [String], unique: Bool, ifNotExists: Bool, condition: (any SQLExpressible)?
) throws Creates an index on the specified table and columns.
func create(table: String, temporary: Bool, ifNotExists: Bool, withoutRowID: Bool, body: (TableDefinition) throws -> Void
) throws Creates a database table.