Skip to content
This repository has been archived by the owner on Oct 23, 2023. It is now read-only.

Latest commit

 

History

History
1901 lines (1398 loc) · 57.3 KB

Index.md

File metadata and controls

1901 lines (1398 loc) · 57.3 KB
Raw

SQLite.swift Documentation

Installation

Note: SQLite.swift requires Swift 5 (and Xcode 10.2) or greater.

Carthage

Carthage is a simple, decentralized dependency manager for Cocoa. To install SQLite.swift with Carthage:

  1. Make sure Carthage is installed.

  2. Update your Cartfile to include the following:

    github "stephencelis/SQLite.swift" ~> 0.12.0

  3. Run carthage update and add the appropriate framework.

CocoaPods

CocoaPods is a dependency manager for Cocoa projects. To install SQLite.swift with CocoaPods:

  1. Make sure CocoaPods is installed (SQLite.swift requires version 1.6.1 or greater).

    # Using the default Ruby install will require you to use sudo when
# installing and updating gems.
[sudo] gem install cocoapods

  2. Update your Podfile to include the following:

    use_frameworks!

target 'YourAppTargetName' do
    pod 'SQLite.swift', '~> 0.12.0'
end

  3. Run pod install --repo-update.

Requiring a specific version of SQLite

If you want to use a more recent version of SQLite than what is provided with the OS you can require the standalone subspec:

target 'YourAppTargetName' do
  pod 'SQLite.swift/standalone', '~> 0.12.0'
end

By default this will use the most recent version of SQLite without any extras. If you want you can further customize this by adding another dependency to sqlite3 or one of its subspecs:

target 'YourAppTargetName' do
  pod 'SQLite.swift/standalone', '~> 0.12.0'
  pod 'sqlite3/fts5', '= 3.15.0'  # SQLite 3.15.0 with FTS5 enabled
end

See the sqlite3 podspec for more details.

Using SQLite.swift with SQLCipher

If you want to use SQLCipher with SQLite.swift you can require the SQLCipher subspec in your Podfile:

target 'YourAppTargetName' do
  pod 'SQLite.swift/SQLCipher', '~> 0.12.0'
end

This will automatically add a dependency to the SQLCipher pod as well as extend Connection with methods to change the database key:

import SQLite

let db = try Connection("path/to/db.sqlite3")
try db.key("secret")
try db.rekey("another secret")

Swift Package Manager

The Swift Package Manager is a tool for managing the distribution of Swift code. It’s integrated with the Swift build system to automate the process of downloading, compiling, and linking dependencies.

It is the recommended approach for using SQLite.swift in OSX CLI applications.

  1. Add the following to your Package.swift file:
dependencies: [
  .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.12.0")
]
  1. Build your project:
$ swift build

Manual

To install SQLite.swift as an Xcode sub-project:

  1. Drag the SQLite.xcodeproj file into your own project. (Submodule, clone, or download the project first.)

    Installation Screen Shot

  2. In your target’s General tab, click the + button under Linked Frameworks and Libraries.

  3. Select the appropriate SQLite.framework for your platform.

  4. Add.

You should now be able to import SQLite from any of your target’s source files and begin using SQLite.swift.

Some additional steps are required to install the application on an actual device:

  1. In the General tab, click the + button under Embedded Binaries.

  2. Select the appropriate SQLite.framework for your platform.

  3. Add.

Getting Started

To use SQLite.swift classes or structures in your target’s source file, first import the SQLite module.

import SQLite

Connecting to a Database

Database connections are established using the Connection class. A connection is initialized with a path to a database. SQLite will attempt to create the database file if it does not already exist.

let db = try Connection("path/to/db.sqlite3")

Read-Write Databases

On iOS, you can create a writable database in your app’s Documents directory.

let path = NSSearchPathForDirectoriesInDomains(
    .documentDirectory, .userDomainMask, true
).first!

let db = try Connection("\(path)/db.sqlite3")

On macOS, you can use your app’s Application Support directory:

var path = NSSearchPathForDirectoriesInDomains(
    .applicationSupportDirectory, .userDomainMask, true
).first! + "/" + Bundle.main.bundleIdentifier!

// create parent directory iff it doesn’t exist
try FileManager.default.createDirectoryAtPath(
    path, withIntermediateDirectories: true, attributes: nil
)

let db = try Connection("\(path)/db.sqlite3")

Read-Only Databases

If you bundle a database with your app (i.e., you’ve copied a database file into your Xcode project and added it to your application target), you can establish a read-only connection to it.

let path = Bundle.main.pathForResource("db", ofType: "sqlite3")!

let db = try Connection(path, readonly: true)

Note: Signed applications cannot modify their bundle resources. If you bundle a database file with your app for the purpose of bootstrapping, copy it to a writable location before establishing a connection (see Read-Write Databases, above, for typical, writable locations).

See these two Stack Overflow questions for more information about iOS apps with SQLite databases: 1, 2. We welcome sample code to show how to successfully copy and use a bundled "seed" database for writing in an app.

In-Memory Databases

If you omit the path, SQLite.swift will provision an in-memory database.

let db = try Connection() // equivalent to `Connection(.inMemory)`

To create a temporary, disk-backed database, pass an empty file name.

let db = try Connection(.temporary)

In-memory databases are automatically deleted when the database connection is closed.

Thread-Safety

Every Connection comes equipped with its own serial queue for statement execution and can be safely accessed across threads. Threads that open transactions and savepoints will block other threads from executing statements while the transaction is open.

If you maintain multiple connections for a single database, consider setting a timeout (in seconds) and/or a busy handler:

db.busyTimeout = 5

db.busyHandler({ tries in
    if tries >= 3 {
        return false
    }
    return true
})

Note: The default timeout is 0, so if you see database is locked errors, you may be trying to access the same database simultaneously from multiple connections.

Building Type-Safe SQL

SQLite.swift comes with a typed expression layer that directly maps Swift types to their SQLite counterparts.

Swift Type SQLite Type
Int64* INTEGER
Double REAL
String TEXT
nil NULL
SQLite.Blob† BLOB

*While Int64 is the basic, raw type (to preserve 64-bit integers on 32-bit platforms), Int and Bool work transparently.

†SQLite.swift defines its own Blob structure, which safely wraps the underlying bytes.

See Custom Types for more information about extending other classes and structures to work with SQLite.swift.

See Executing Arbitrary SQL to forego the typed layer and execute raw SQL, instead.

These expressions (in the form of the structure, Expression) build on one another and, with a query (QueryType), can create and execute SQL statements.

Expressions

Expressions are generic structures associated with a type (built-in or custom), raw SQL, and (optionally) values to bind to that SQL. Typically, you will only explicitly create expressions to describe your columns, and typically only once per column.

let id = Expression<Int64>("id")
let email = Expression<String>("email")
let balance = Expression<Double>("balance")
let verified = Expression<Bool>("verified")

Use optional generics for expressions that can evaluate to NULL.

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

Note: The default Expression initializer is for quoted identifiers (i.e., column names). To build a literal SQL expression, use init(literal:).

Compound Expressions

Expressions can be combined with other expressions and types using filter operators and functions (as well as other non-filter operators and functions). These building blocks can create complex SQLite statements.

Queries

Queries are structures that reference a database and table name, and can be used to build a variety of statements using expressions. We can create a query by initializing a Table, View, or VirtualTable.

let users = Table("users")

Assuming the table exists, we can immediately insert , select, update, and delete rows.

Creating a Table

We can build CREATE TABLE statements by calling the create function on a Table. The following is a basic example of SQLite.swift code (using the expressions and query above) and the corresponding SQL it generates.

try db.run(users.create { t in     // CREATE TABLE "users" (
    t.column(id, primaryKey: true) //     "id" INTEGER PRIMARY KEY NOT NULL,
    t.column(email, unique: true)  //     "email" TEXT UNIQUE NOT NULL,
    t.column(name)                 //     "name" TEXT
})                                 // )

Note: Expression<T> structures (in this case, the id and email columns), generate NOT NULL constraints automatically, while Expression<T?> structures (name) do not.

Create Table Options

The Table.create function has several default parameters we can override.

  • temporary adds a TEMPORARY clause to the CREATE TABLE statement (to create a temporary table that will automatically drop when the database connection closes). Default: false.

    try db.run(users.create(temporary: true) { t in /* ... */ })
// CREATE TEMPORARY TABLE "users" -- ...

  • ifNotExists adds an IF NOT EXISTS clause to the CREATE TABLE statement (which will bail out gracefully if the table already exists). Default: false.

    try db.run(users.create(ifNotExists: true) { t in /* ... */ })
// CREATE TABLE "users" IF NOT EXISTS -- ...

Column Constraints

The column function is used for a single column definition. It takes an expression describing the column name and type, and accepts several parameters that map to various column constraints and clauses.

  • primaryKey adds a PRIMARY KEY constraint to a single column.

    t.column(id, primaryKey: true)
// "id" INTEGER PRIMARY KEY NOT NULL

t.column(id, primaryKey: .autoincrement)
// "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

    Note: The primaryKey parameter cannot be used alongside references. If you need to create a column that has a default value and is also a primary and/or foreign key, use the primaryKey and foreignKey functions mentioned under Table Constraints.

    Primary keys cannot be optional (e.g., Expression<Int64?>).

    Only an INTEGER PRIMARY KEY can take .autoincrement.

  • unique adds a UNIQUE constraint to the column. (See the unique function under Table Constraints for uniqueness over multiple columns).

    t.column(email, unique: true)
// "email" TEXT UNIQUE NOT NULL

  • check attaches a CHECK constraint to a column definition in the form of a boolean expression (Expression<Bool>). Boolean expressions can be easily built using filter operators and functions. (See also the check function under Table Constraints.)

    t.column(email, check: email.like("%@%"))
// "email" TEXT NOT NULL CHECK ("email" LIKE '%@%')

  • defaultValue adds a DEFAULT clause to a column definition and only accepts a value (or expression) matching the column’s type. This value is used if none is explicitly provided during an INSERT.

    t.column(name, defaultValue: "Anonymous")
// "name" TEXT DEFAULT 'Anonymous'

    Note: The defaultValue parameter cannot be used alongside primaryKey and references. If you need to create a column that has a default value and is also a primary and/or foreign key, use the primaryKey and foreignKey functions mentioned under Table Constraints.

  • collate adds a COLLATE clause to Expression<String> (and Expression<String?>) column definitions with a collating sequence defined in the Collation enumeration.

    t.column(email, collate: .nocase)
// "email" TEXT NOT NULL COLLATE "NOCASE"

t.column(name, collate: .rtrim)
// "name" TEXT COLLATE "RTRIM"

  • references adds a REFERENCES clause to Expression<Int64> (and Expression<Int64?>) column definitions and accepts a table (SchemaType) or namespaced column expression. (See the foreignKey function under Table Constraints for non-integer foreign key support.)

    t.column(user_id, references: users, id)
// "user_id" INTEGER REFERENCES "users" ("id")

    Note: The references parameter cannot be used alongside primaryKey and defaultValue. If you need to create a column that has a default value and is also a primary and/or foreign key, use the primaryKey and foreignKey functions mentioned under Table Constraints.

Table Constraints

Additional constraints may be provided outside the scope of a single column using the following functions.

  • primaryKey adds a PRIMARY KEY constraint to the table. Unlike the column constraint, above, it supports all SQLite types, ascending and descending orders, and composite (multiple column) keys.

    t.primaryKey(email.asc, name)
// PRIMARY KEY("email" ASC, "name")

  • unique adds a UNIQUE constraint to the table. Unlike the column constraint, above, it supports composite (multiplecolumn) constraints.

    t.unique(local, domain)
// UNIQUE("local", "domain")

  • check adds a CHECK constraint to the table in the form of a boolean expression (Expression<Bool>). Boolean expressions can be easily built using filter operators and functions. (See also the check parameter under Column Constraints.)

    t.check(balance >= 0)
// CHECK ("balance" >= 0.0)

  • foreignKey adds a FOREIGN KEY constraint to the table. Unlike the references constraint, above, it supports all SQLite types, both ON UPDATE and ON DELETE actions, and composite (multiple column) keys.

    t.foreignKey(user_id, references: users, id, delete: .setNull)
// FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE SET NULL

Inserting Rows

We can insert rows into a table by calling a query’s insert function with a list of setters—typically typed column expressions and values (which can also be expressions)—each joined by the <- operator.

try db.run(users.insert(email <- "[email protected]", name <- "Alice"))
// INSERT INTO "users" ("email", "name") VALUES ('[email protected]', 'Alice')

try db.run(users.insert(or: .replace, email <- "[email protected]", name <- "Alice B."))
// INSERT OR REPLACE INTO "users" ("email", "name") VALUES ('[email protected]', 'Alice B.')

The insert function, when run successfully, returns an Int64 representing the inserted row’s ROWID.

do {
    let rowid = try db.run(users.insert(email <- "[email protected]"))
    print("inserted id: \(rowid)")
} catch {
    print("insertion failed: \(error)")
}

The update and delete functions follow similar patterns.

Note: If insert is called without any arguments, the statement will run with a DEFAULT VALUES clause. The table must not have any constraints that aren’t fulfilled by default values.

try db.run(timestamps.insert())
// INSERT INTO "timestamps" DEFAULT VALUES

Handling SQLite errors

You can pattern match on the error to selectively catch SQLite errors. For example, to specifically handle constraint errors (SQLITE_CONSTRAINT):

do {
    try db.run(users.insert(email <- "[email protected]"))
    try db.run(users.insert(email <- "[email protected]"))
} catch let Result.error(message, code, statement) where code == SQLITE_CONSTRAINT {
    print("constraint failed: \(message), in \(statement)")
} catch let error {
    print("insertion failed: \(error)")
}

The Result.error type contains the English-language text that describes the error (message), the error code (see SQLite result code list for details) and a optional reference to the statement which produced the error.

Setters

SQLite.swift typically uses the <- operator to set values during inserts and updates.

try db.run(counter.update(count <- 0))
// UPDATE "counters" SET "count" = 0 WHERE ("id" = 1)

There are also a number of convenience setters that take the existing value into account using native Swift operators.

For example, to atomically increment a column, we can use ++:

try db.run(counter.update(count++)) // equivalent to `counter.update(count -> count + 1)`
// UPDATE "counters" SET "count" = "count" + 1 WHERE ("id" = 1)

To take an amount and “move” it via transaction, we can use -= and +=:

let amount = 100.0
try db.transaction {
    try db.run(alice.update(balance -= amount))
    try db.run(betty.update(balance += amount))
}
// BEGIN DEFERRED TRANSACTION
// UPDATE "users" SET "balance" = "balance" - 100.0 WHERE ("id" = 1)
// UPDATE "users" SET "balance" = "balance" + 100.0 WHERE ("id" = 2)
// COMMIT TRANSACTION
Infix Setters
Operator Types
<- Value -> Value
+= Number -> Number
-= Number -> Number
*= Number -> Number
/= Number -> Number
%= Int -> Int
<<= Int -> Int
>>= Int -> Int
&= Int -> Int
||= Int -> Int
^= Int -> Int
+= String -> String
Postfix Setters
Operator Types
++ Int -> Int
-- Int -> Int

Selecting Rows

Query structures are SELECT statements waiting to happen. They execute via iteration and other means of sequence access.

Iterating and Accessing Values

Prepared queries execute lazily upon iteration. Each row is returned as a Row object, which can be subscripted with a column expression matching one of the columns returned.

for user in try db.prepare(users) {
    print("id: \(user[id]), email: \(user[email]), name: \(user[name])")
    // id: 1, email: [email protected], name: Optional("Alice")
}
// SELECT * FROM "users"

Expression<T> column values are automatically unwrapped (we’ve made a promise to the compiler that they’ll never be NULL), while Expression<T?> values remain wrapped.

⚠ Column subscripts on Row will force try and abort execution in error cases. If you want to handle this yourself, use Row.get(_ column: Expression<V>):

for user in try db.prepare(users) {
    do {
        print("name: \(try user.get(name))")
    } catch {
        // handle
    }
}

Plucking Rows

We can pluck the first row by passing a query to the pluck function on a database connection.

if let user = try db.pluck(users) { /* ... */ } // Row
// SELECT * FROM "users" LIMIT 1

To collect all rows into an array, we can simply wrap the sequence (though this is not always the most memory-efficient idea).

let all = Array(try db.prepare(users))
// SELECT * FROM "users"

Building Complex Queries

Queries have a number of chainable functions that can be used (with expressions) to add and modify a number of clauses to the underlying statement.

let query = users.select(email)           // SELECT "email" FROM "users"
                 .filter(name != nil)     // WHERE "name" IS NOT NULL
                 .order(email.desc, name) // ORDER BY "email" DESC, "name"
                 .limit(5, offset: 1)     // LIMIT 5 OFFSET 1

Selecting Columns

By default, queries select every column of the result set (using SELECT *). We can use the select function with a list of expressions to return specific columns instead.

for user in try db.prepare(users.select(id, email)) {
    print("id: \(user[id]), email: \(user[email])")
    // id: 1, email: [email protected]
}
// SELECT "id", "email" FROM "users"

We can access the results of more complex expressions by holding onto a reference of the expression itself.

let sentence = name + " is " + cast(age) as Expression<String?> + " years old!"
for user in users.select(sentence) {
    print(user[sentence])
    // Optional("Alice is 30 years old!")
}
// SELECT ((("name" || ' is ') || CAST ("age" AS TEXT)) || ' years old!') FROM "users"

Joining Other Tables

We can join tables using a query’s join function.

users.join(posts, on: user_id == users[id])
// SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")

The join function takes a query object (for the table being joined on), a join condition (on), and is prefixed with an optional join type (default: .inner). Join conditions can be built using filter operators and functions, generally require namespacing, and sometimes require aliasing.

Column Namespacing

When joining tables, column names can become ambiguous. E.g., both tables may have an id column.

let query = users.join(posts, on: user_id == id)
// assertion failure: ambiguous column 'id'

We can disambiguate by namespacing id.

let query = users.join(posts, on: user_id == users[id])
// SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")

Namespacing is achieved by subscripting a query with a column expression (e.g., users[id] above becomes users.id).

Note: We can namespace all of a table’s columns using *.

let query = users.select(users[*])
// SELECT "users".* FROM "users"
Table Aliasing

Occasionally, we need to join a table to itself, in which case we must alias the table with another name. We can achieve this using the query’s alias function.

let managers = users.alias("managers")

let query = users.join(managers, on: managers[id] == users[managerId])
// SELECT * FROM "users"
// INNER JOIN ("users") AS "managers" ON ("managers"."id" = "users"."manager_id")

If query results can have ambiguous column names, row values should be accessed with namespaced column expressions. In the above case, SELECT * immediately namespaces all columns of the result set.

let user = try db.pluck(query)
user[id]           // fatal error: ambiguous column 'id'
                   // (please disambiguate: ["users"."id", "managers"."id"])

user[users[id]]    // returns "users"."id"
user[managers[id]] // returns "managers"."id"

Filtering Rows

SQLite.swift filters rows using a query’s filter function with a boolean expression (Expression<Bool>).

users.filter(id == 1)
// SELECT * FROM "users" WHERE ("id" = 1)

users.filter([1, 2, 3, 4, 5].contains(id))
// SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))

users.filter(email.like("%@mac.com"))
// SELECT * FROM "users" WHERE ("email" LIKE '%@mac.com')

users.filter(verified && name.lowercaseString == "alice")
// SELECT * FROM "users" WHERE ("verified" AND (lower("name") == 'alice'))

users.filter(verified || balance >= 10_000)
// SELECT * FROM "users" WHERE ("verified" OR ("balance" >= 10000.0))

We can build our own boolean expressions by using one of the many filter operators and functions.

Instead of filter we can also use the where function which is an alias:

users.where(id == 1)
// SELECT * FROM "users" WHERE ("id" = 1)
Filter Operators and Functions

SQLite.swift defines a number of operators for building filtering predicates. Operators and functions work together in a type-safe manner, so attempting to equate or compare different types will prevent compilation.

Infix Filter Operators
Swift Types SQLite
== Equatable -> Bool =/IS*
!= Equatable -> Bool !=/IS NOT*
> Comparable -> Bool >
>= Comparable -> Bool >=
< Comparable -> Bool <
<= Comparable -> Bool <=
~= (Interval, Comparable) -> Bool BETWEEN
&& Bool -> Bool AND
|| Bool -> Bool OR

*When comparing against nil, SQLite.swift will use IS and IS NOT accordingly.

Prefix Filter Operators
Swift Types SQLite
! Bool -> Bool NOT
Filtering Functions
Swift Types SQLite
like String -> Bool LIKE
glob String -> Bool GLOB
match String -> Bool MATCH
contains (Array<T>, T) -> Bool IN

Sorting Rows

We can pre-sort returned rows using the query’s order function.

E.g., to return users sorted by email, then name, in ascending order:

users.order(email, name)
// SELECT * FROM "users" ORDER BY "email", "name"

The order function takes a list of column expressions.

Expression objects have two computed properties to assist sorting: asc and desc. These properties append the expression with ASC and DESC to mark ascending and descending order respectively.

users.order(email.desc, name.asc)
// SELECT * FROM "users" ORDER BY "email" DESC, "name" ASC

Limiting and Paging Results

We can limit and skip returned rows using a query’s limit function (and its optional offset parameter).

users.limit(5)
// SELECT * FROM "users" LIMIT 5

users.limit(5, offset: 5)
// SELECT * FROM "users" LIMIT 5 OFFSET 5

Aggregation

Queries come with a number of functions that quickly return aggregate scalar values from the table. These mirror the core aggregate functions and are executed immediately against the query.

let count = try db.scalar(users.count)
// SELECT count(*) FROM "users"

Filtered queries will appropriately filter aggregate values.

let count = try db.scalar(users.filter(name != nil).count)
// SELECT count(*) FROM "users" WHERE "name" IS NOT NULL

  • count as a computed property on a query (see examples above) returns the total number of rows matching the query.

    count as a computed property on a column expression returns the total number of rows where that column is not NULL.

    let count = try db.scalar(users.select(name.count)) // -> Int
// SELECT count("name") FROM "users"

  • max takes a comparable column expression and returns the largest value if any exists.

    let max = try db.scalar(users.select(id.max)) // -> Int64?
// SELECT max("id") FROM "users"

  • min takes a comparable column expression and returns the smallest value if any exists.

    let min = try db.scalar(users.select(id.min)) // -> Int64?
// SELECT min("id") FROM "users"

  • average takes a numeric column expression and returns the average row value (as a Double) if any exists.

    let average = try db.scalar(users.select(balance.average)) // -> Double?
// SELECT avg("balance") FROM "users"

  • sum takes a numeric column expression and returns the sum total of all rows if any exist.

    let sum = try db.scalar(users.select(balance.sum)) // -> Double?
// SELECT sum("balance") FROM "users"

  • total, like sum, takes a numeric column expression and returns the sum total of all rows, but in this case always returns a Double, and returns 0.0 for an empty query.

    let total = try db.scalar(users.select(balance.total)) // -> Double
// SELECT total("balance") FROM "users"

Note: Expressions can be prefixed with a DISTINCT clause by calling the distinct computed property.

let count = try db.scalar(users.select(name.distinct.count) // -> Int
// SELECT count(DISTINCT "name") FROM "users"

Updating Rows

We can update a table’s rows by calling a query’s update function with a list of setters—typically typed column expressions and values (which can also be expressions)—each joined by the <- operator.

When an unscoped query calls update, it will update every row in the table.

try db.run(users.update(email <- "[email protected]"))
// UPDATE "users" SET "email" = '[email protected]'

Be sure to scope UPDATE statements beforehand using the filter function .

let alice = users.filter(id == 1)
try db.run(alice.update(email <- "[email protected]"))
// UPDATE "users" SET "email" = '[email protected]' WHERE ("id" = 1)

The update function returns an Int representing the number of updated rows.

do {
    if try db.run(alice.update(email <- "[email protected]")) > 0 {
        print("updated alice")
    } else {
        print("alice not found")
    }
} catch {
    print("update failed: \(error)")
}

Deleting Rows

We can delete rows from a table by calling a query’s delete function.

When an unscoped query calls delete, it will delete every row in the table.

try db.run(users.delete())
// DELETE FROM "users"

Be sure to scope DELETE statements beforehand using the filter function.

let alice = users.filter(id == 1)
try db.run(alice.delete())
// DELETE FROM "users" WHERE ("id" = 1)

The delete function returns an Int representing the number of deleted rows.

do {
    if try db.run(alice.delete()) > 0 {
        print("deleted alice")
    } else {
        print("alice not found")
    }
} catch {
    print("delete failed: \(error)")
}

Transactions and Savepoints

Using the transaction and savepoint functions, we can run a series of statements in a transaction. If a single statement fails or the block throws an error, the changes will be rolled back.

try db.transaction {
    let rowid = try db.run(users.insert(email <- "[email protected]"))
    try db.run(users.insert(email <- "[email protected]", managerId <- rowid))
}
// BEGIN DEFERRED TRANSACTION
// INSERT INTO "users" ("email") VALUES ('[email protected]')
// INSERT INTO "users" ("email", "manager_id") VALUES ('[email protected]', 2)
// COMMIT TRANSACTION

Note: Transactions run in a serial queue.

Altering the Schema

SQLite.swift comes with several functions (in addition to Table.create) for altering a database schema in a type-safe manner.

Renaming Tables

We can build an ALTER TABLE … RENAME TO statement by calling the rename function on a Table or VirtualTable.

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

Adding Columns

We can add columns to a table by calling addColumn function on a Table. SQLite.swift enforces the same limited subset of ALTER TABLE that SQLite supports.

try db.run(users.addColumn(suffix))
// ALTER TABLE "users" ADD COLUMN "suffix" TEXT

Added Column Constraints

The addColumn function shares several of the same column function parameters used when creating tables.

  • check attaches a CHECK constraint to a column definition in the form of a boolean expression (Expression<Bool>). (See also the check function under Table Constraints.)

    try db.run(users.addColumn(suffix, check: ["JR", "SR"].contains(suffix)))
// ALTER TABLE "users" ADD COLUMN "suffix" TEXT CHECK ("suffix" IN ('JR', 'SR'))

  • defaultValue adds a DEFAULT clause to a column definition and only accepts a value matching the column’s type. This value is used if none is explicitly provided during an INSERT.

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

    Note: Unlike the CREATE TABLE constraint, default values may not be expression structures (including CURRENT_TIME, CURRENT_DATE, or CURRENT_TIMESTAMP).

  • collate adds a COLLATE clause to Expression<String> (and Expression<String?>) column definitions with a collating sequence defined in the Collation enumeration.

    try db.run(users.addColumn(email, collate: .nocase))
// ALTER TABLE "users" ADD COLUMN "email" TEXT NOT NULL COLLATE "NOCASE"

try db.run(users.addColumn(name, collate: .rtrim))
// ALTER TABLE "users" ADD COLUMN "name" TEXT COLLATE "RTRIM"

  • references adds a REFERENCES clause to Int64 (and Int64?) column definitions and accepts a table or namespaced column expression. (See the foreignKey function under Table Constraints for non-integer foreign key support.)

    try db.run(posts.addColumn(userId, references: users, id)
// ALTER TABLE "posts" ADD COLUMN "user_id" INTEGER REFERENCES "users" ("id")

Indexes

Creating Indexes

We can build CREATE INDEX statements by calling the createIndex function on a SchemaType.

try db.run(users.createIndex(email))
// CREATE INDEX "index_users_on_email" ON "users" ("email")

The index name is generated automatically based on the table and column names.

The createIndex function has a couple default parameters we can override.

  • unique adds a UNIQUE constraint to the index. Default: false.

    try db.run(users.createIndex(email, unique: true))
// CREATE UNIQUE INDEX "index_users_on_email" ON "users" ("email")

  • ifNotExists adds an IF NOT EXISTS clause to the CREATE TABLE statement (which will bail out gracefully if the table already exists). Default: false.

    try db.run(users.createIndex(email, ifNotExists: true))
// CREATE INDEX IF NOT EXISTS "index_users_on_email" ON "users" ("email")

Dropping Indexes

We can build DROP INDEX statements by calling the dropIndex function on a SchemaType.

try db.run(users.dropIndex(email))
// DROP INDEX "index_users_on_email"

The dropIndex function has one additional parameter, ifExists, which (when true) adds an IF EXISTS clause to the statement.

try db.run(users.dropIndex(email, ifExists: true))
// DROP INDEX IF EXISTS "index_users_on_email"

Dropping Tables

We can build DROP TABLE statements by calling the dropTable function on a SchemaType.

try db.run(users.drop())
// DROP TABLE "users"

The drop function has one additional parameter, ifExists, which (when true) adds an IF EXISTS clause to the statement.

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

Migrations and Schema Versioning

You can add a convenience property on Connection to query and set the PRAGMA user_version.

This is a great way to manage your schema’s version over migrations.

extension Connection {
    public var userVersion: Int32 {
        get { return Int32(try! scalar("PRAGMA user_version") as! Int64)}
        set { try! run("PRAGMA user_version = \(newValue)") }
    }
}

Then you can conditionally run your migrations along the lines of:

if db.userVersion == 0 {
    // handle first migration
    db.userVersion = 1
}
if db.userVersion == 1 {
    // handle second migration
    db.userVersion = 2
}

For more complex migration requirements check out the schema management system SQLiteMigrationManager.swift.

Custom Types

SQLite.swift supports serializing and deserializing any custom type as long as it conforms to the Value protocol.

protocol Value {
    typealias Datatype: Binding
    class var declaredDatatype: String { get }
    class func fromDatatypeValue(datatypeValue: Datatype) -> Self
    var datatypeValue: Datatype { get }
}

The Datatype must be one of the basic Swift types that values are bridged through before serialization and deserialization (see Building Type-Safe SQL for a list of types).

⚠ Note: Binding is a protocol that SQLite.swift uses internally to directly map SQLite types to Swift types. Do not conform custom types to the Binding protocol.

Date-Time Values

In SQLite, DATETIME columns can be treated as strings or numbers, so we can transparently bridge Date objects through Swift’s String types.

We can use these types directly in SQLite statements.

let published_at = Expression<Date>("published_at")

let published = posts.filter(published_at <= Date())
// SELECT * FROM "posts" WHERE "published_at" <= '2014-11-18T12:45:30.000'

let startDate = Date(timeIntervalSince1970: 0)
let published = posts.filter(startDate...Date() ~= published_at)
// SELECT * FROM "posts" WHERE "published_at" BETWEEN '1970-01-01T00:00:00.000' AND '2014-11-18T12:45:30.000'

Binary Data

We can bridge any type that can be initialized from and encoded to Data.

extension UIImage: Value {
    public class var declaredDatatype: String {
        return Blob.declaredDatatype
    }
    public class func fromDatatypeValue(blobValue: Blob) -> UIImage {
        return UIImage(data: Data.fromDatatypeValue(blobValue))!
    }
    public var datatypeValue: Blob {
        return UIImagePNGRepresentation(self)!.datatypeValue
    }

}

Note: See the Archives and Serializations Programming Guide for more information on encoding and decoding custom types.

Codable Types

Codable types were introduced as a part of Swift 4 to allow serializing and deserializing types. SQLite.swift supports the insertion, updating, and retrieval of basic Codable types.

Inserting Codable Types

Queries have a method to allow inserting an Encodable type.

struct User: Encodable {
    let name: String
}
try db.run(users.insert(User(name: "test")))

There are two other parameters also available to this method:

  • userInfo is a dictionary that is passed to the encoder and made available to encodable types to allow customizing their behavior.

  • otherSetters allows you to specify additional setters on top of those that are generated from the encodable types themselves.

Updating Codable Types

Queries have a method to allow updating an Encodable type.

try db.run(users.filter(id == userId).update(user))

⚠ Unless filtered, using the update method on an instance of a Codable type updates all table rows.

There are two other parameters also available to this method:

  • userInfo is a dictionary that is passed to the encoder and made available to encodable types to allow customizing their behavior.

  • otherSetters allows you to specify additional setters on top of those that are generated from the encodable types themselves.

Retrieving Codable Types

Rows have a method to decode a Decodable type.

let loadedUsers: [User] = try db.prepare(users).map { row in
    return try row.decode()
}

You can also create a decoder to use manually yourself. This can be useful for example if you are using the Facade pattern to hide subclasses behind a super class. For example, you may want to encode an Image type that can be multiple different formats such as PNGImage, JPGImage, or HEIFImage. You will need to determine the correct subclass before you know which type to decode.

enum ImageCodingKeys: String, CodingKey {
    case kind
}

enum ImageKind: Int, Codable {
    case png, jpg, heif
}

let loadedImages: [Image] = try db.prepare(images).map { row in
    let decoder = row.decoder()
    let container = try decoder.container(keyedBy: ImageCodingKeys.self)
    switch try container.decode(ImageKind.self, forKey: .kind) {
    case .png:
        return try PNGImage(from: decoder)
    case .jpg:
        return try JPGImage(from: decoder)
    case .heif:
        return try HEIFImage(from: decoder)
    }
}

Both of the above methods also have the following optional parameter:

  • userInfo is a dictionary that is passed to the decoder and made available to decodable types to allow customizing their behavior.

Restrictions

There are a few restrictions on using Codable types:

  • The encodable and decodable objects can only use the following types:
    • Int, Bool, Float, Double, String
    • Nested Codable types that will be encoded as JSON to a single column
  • These methods will not handle object relationships for you. You must write your own Codable and Decodable implementations if you wish to support this.
  • The Codable types may not try to access nested containers or nested unkeyed containers
  • The Codable types may not access single value containers or unkeyed containers
  • The Codable types may not access super decoders or encoders

Other Operators

In addition to filter operators, SQLite.swift defines a number of operators that can modify expression values with arithmetic, bitwise operations, and concatenation.

Other Infix Operators
Swift Types SQLite
+ Number -> Number +
- Number -> Number -
* Number -> Number *
/ Number -> Number /
% Int -> Int %
<< Int -> Int <<
>> Int -> Int >>
& Int -> Int &
| Int -> Int |
+ String -> String ||

Note: SQLite.swift also defines a bitwise XOR operator, ^, which expands the expression lhs ^ rhs to ~(lhs & rhs) & (lhs | rhs).

Other Prefix Operators
Swift Types SQLite
~ Int -> Int ~
- Number -> Number -

Core SQLite Functions

Many of SQLite’s core functions have been surfaced in and type-audited for SQLite.swift.

Note: SQLite.swift aliases the ?? operator to the ifnull function.

name ?? email // ifnull("name", "email")

Aggregate SQLite Functions

Most of SQLite’s aggregate functions have been surfaced in and type-audited for SQLite.swift.

Date and Time functions

SQLite's date and time functions are available:

DateFunctions.date("now")
// date('now')
Date().date
// date('2007-01-09T09:41:00.000')
Expression<Date>("date").date
// date("date")

Custom SQL Functions

We can create custom SQL functions by calling createFunction on a database connection.

For example, to give queries access to MobileCoreServices.UTTypeConformsTo, we can write the following:

import MobileCoreServices

let typeConformsTo: (Expression<String>, Expression<String>) -> Expression<Bool> = (
    try db.createFunction("typeConformsTo", deterministic: true) { UTI, conformsToUTI in
        return UTTypeConformsTo(UTI, conformsToUTI)
    }
)

Note: The optional deterministic parameter is an optimization that causes the function to be created with SQLITE_DETERMINISTIC.

Note typeConformsTo’s signature:

(Expression<String>, Expression<String>) -> Expression<Bool>

Because of this, createFunction expects a block with the following signature:

(String, String) -> Bool

Once assigned, the closure can be called wherever boolean expressions are accepted.

let attachments = Table("attachments")
let UTI = Expression<String>("UTI")

let images = attachments.filter(typeConformsTo(UTI, kUTTypeImage))
// SELECT * FROM "attachments" WHERE "typeConformsTo"("UTI", 'public.image')

Note: The return type of a function must be a core SQL type or conform to Value.

We can create loosely-typed functions by handling an array of raw arguments, instead.

db.createFunction("typeConformsTo", deterministic: true) { args in
    guard let UTI = args[0] as? String, conformsToUTI = args[1] as? String else { return nil }
    return UTTypeConformsTo(UTI, conformsToUTI)
}

Creating a loosely-typed function cannot return a closure and instead must be wrapped manually or executed using raw SQL.

let stmt = try db.prepare("SELECT * FROM attachments WHERE typeConformsTo(UTI, ?)")
for row in stmt.bind(kUTTypeImage) { /* ... */ }

Custom Collations

We can create custom collating sequences by calling createCollation on a database connection.

try db.createCollation("NODIACRITIC") { lhs, rhs in
    return lhs.compare(rhs, options: .diacriticInsensitiveSearch)
}

We can reference a custom collation using the Custom member of the Collation enumeration.

restaurants.order(collate(.custom("NODIACRITIC"), name))
// SELECT * FROM "restaurants" ORDER BY "name" COLLATE "NODIACRITIC"

Full-text Search

We can create a virtual table using the FTS4 module by calling create on a VirtualTable.

let emails = VirtualTable("emails")
let subject = Expression<String>("subject")
let body = Expression<String>("body")

try db.run(emails.create(.FTS4(subject, body)))
// CREATE VIRTUAL TABLE "emails" USING fts4("subject", "body")

We can specify a tokenizer using the tokenize parameter.

try db.run(emails.create(.FTS4([subject, body], tokenize: .Porter)))
// CREATE VIRTUAL TABLE "emails" USING fts4("subject", "body", tokenize=porter)

We can set the full range of parameters by creating a FTS4Config object.

let emails = VirtualTable("emails")
let subject = Expression<String>("subject")
let body = Expression<String>("body")
let config = FTS4Config()
    .column(subject)
    .column(body, [.unindexed])
    .languageId("lid")
    .order(.desc)

try db.run(emails.create(.FTS4(config))
// CREATE VIRTUAL TABLE "emails" USING fts4("subject", "body", notindexed="body", languageid="lid", order="desc")

Once we insert a few rows, we can search using the match function, which takes a table or column as its first argument and a query string as its second.

try db.run(emails.insert(
    subject <- "Just Checking In",
    body <- "Hey, I was just wondering...did you get my last email?"
))

let wonderfulEmails: QueryType = emails.match("wonder*")
// SELECT * FROM "emails" WHERE "emails" MATCH 'wonder*'

let replies = emails.filter(subject.match("Re:*"))
// SELECT * FROM "emails" WHERE "subject" MATCH 'Re:*'

FTS5

When linking against a version of SQLite with FTS5 enabled we can create the virtual table in a similar fashion.

let emails = VirtualTable("emails")
let subject = Expression<String>("subject")
let body = Expression<String>("body")
let config = FTS5Config()
    .column(subject)
    .column(body, [.unindexed])

try db.run(emails.create(.FTS5(config))
// CREATE VIRTUAL TABLE "emails" USING fts5("subject", "body" UNINDEXED)

// Note that FTS5 uses a different syntax to select columns, so we need to rewrite
// the last FTS4 query above as:
let replies = emails.filter(emails.match("subject:\"Re:\"*))
// SELECT * FROM "emails" WHERE "emails" MATCH 'subject:"Re:"*'

// https://www.sqlite.org/fts5.html#_changes_to_select_statements_

Executing Arbitrary SQL

Though we recommend you stick with SQLite.swift’s type-safe system whenever possible, it is possible to simply and safely prepare and execute raw SQL statements via a Database connection using the following functions.

  • execute runs an arbitrary number of SQL statements as a convenience.

    try db.execute("""
    BEGIN TRANSACTION;
    CREATE TABLE users (
        id INTEGER PRIMARY KEY NOT NULL,
        email TEXT UNIQUE NOT NULL,
        name TEXT
    );
    CREATE TABLE posts (
        id INTEGER PRIMARY KEY NOT NULL,
        title TEXT NOT NULL,
        body TEXT NOT NULL,
        published_at DATETIME
    );
    PRAGMA user_version = 1;
    COMMIT TRANSACTION;
    """
)

  • prepare prepares a single Statement object from a SQL string, optionally binds values to it (using the statement’s bind function), and returns the statement for deferred execution.

    let stmt = try db.prepare("INSERT INTO users (email) VALUES (?)")

    Once prepared, statements may be executed using run, binding any unbound parameters.

    try stmt.run("[email protected]")
db.changes // -> {Some 1}

    Statements with results may be iterated over, using the columnNames if useful.

    let stmt = try db.prepare("SELECT id, email FROM users")
for row in stmt {
    for (index, name) in stmt.columnNames.enumerated() {
        print ("\(name):\(row[index]!)")
        // id: Optional(1), email: Optional("[email protected]")
    }
}

  • run prepares a single Statement object from a SQL string, optionally binds values to it (using the statement’s bind function), executes, and returns the statement.

    try db.run("INSERT INTO users (email) VALUES (?)", "[email protected]")

  • scalar prepares a single Statement object from a SQL string, optionally binds values to it (using the statement’s bind function), executes, and returns the first value of the first row.

    let count = try db.scalar("SELECT count(*) FROM users") as! Int64

    Statements also have a scalar function, which can optionally re-bind values at execution.

    let stmt = try db.prepare("SELECT count (*) FROM users")
let count = try stmt.scalar() as! Int64

Logging

We can log SQL using the database’s trace function.

#if DEBUG
    db.trace { print($0) }
#endif