Structures

The following structures are available globally.

  • An index on a database table.

    See Database.indexes(on:)

    See more

    Declaration

    Swift

    public struct IndexInfo
    Show on GitHub

  • Primary keys are returned from the Database.primaryKey(_:) method.

    When the table’s primary key is the rowid:

    // CREATE TABLE items (name TEXT)
let pk = try db.primaryKey("items")
pk.columns     // ["rowid"]
pk.rowIDColumn // nil
pk.isRowID     // true

// CREATE TABLE citizens (
//   id INTEGER PRIMARY KEY,
//   name TEXT
// )
let pk = try db.primaryKey("citizens")!
pk.columns     // ["id"]
pk.rowIDColumn // "id"
pk.isRowID     // true

    When the table’s primary key is not the rowid:

    // CREATE TABLE countries (
//   isoCode TEXT NOT NULL PRIMARY KEY
//   name TEXT
// )
let pk = db.primaryKey("countries")!
pk.columns     // ["isoCode"]
pk.rowIDColumn // nil
pk.isRowID     // false

// CREATE TABLE citizenships (
//   citizenID INTEGER NOT NULL REFERENCES citizens(id)
//   countryIsoCode TEXT NOT NULL REFERENCES countries(isoCode)
//   PRIMARY KEY (citizenID, countryIsoCode)
// )
let pk = db.primaryKey("citizenships")!
pk.columns     // ["citizenID", "countryIsoCode"]
pk.rowIDColumn // nil
pk.isRowID     // false
    See more

    Declaration

    Swift

    public struct PrimaryKeyInfo
    Show on GitHub

  • You get foreign keys from table names, with the foreignKeys(on:) method.

    See more

    Declaration

    Swift

    public struct ForeignKeyInfo
    Show on GitHub

  • A DatabaseMigrator registers and applies database migrations.

    Migrations are named blocks of SQL statements that are guaranteed to be applied in order, once and only once.

    When a user upgrades your application, only non-applied migration are run.

    Usage:

    var migrator = DatabaseMigrator()

// v1.0 database
migrator.registerMigration("createAuthors") { db in
    try db.execute("""
        CREATE TABLE authors (
            id INTEGER PRIMARY KEY,
            creationDate TEXT,
            name TEXT NOT NULL
        )
        """)
}

migrator.registerMigration("createBooks") { db in
    try db.execute("""
        CREATE TABLE books (
            uuid TEXT PRIMARY KEY,
            authorID INTEGER NOT NULL
                     REFERENCES authors(id)
                     ON DELETE CASCADE ON UPDATE CASCADE,
            title TEXT NOT NULL
        )
        """)
}

// v2.0 database
migrator.registerMigration("AddBirthYearToAuthors") { db in
    try db.execute("ALTER TABLE authors ADD COLUMN birthYear INT")
}

try migrator.migrate(dbQueue)
    See more

    Declaration

    Swift

    public struct DatabaseMigrator
    Show on GitHub

  • DatabaseRegion defines a region in the database. DatabaseRegion is dedicated to help transaction observers recognize impactful database changes in their observes(eventsOfKind:) and databaseDidChange(with:) methods.

    A database region is the union of any number of table regions, which can cover a full table, or the combination of columns and rows (identified by their rowids):

    |Table1 | |Table2 | |Table3 | |Table4 | |Table5 | |——-| |——-| |——-| |——-| |——-| |x|x|x|x| |x| | | | |x|x|x|x| |x|x| |x| | | | | | |x|x|x|x| |x| | | | | | | | | | | | | | | |x| | | |x|x|x|x| |x| | | | | | | | | |x|x| |x| | | | | | |x|x|x|x| |x| | | | | | | | | | | | | | | | | | |

    You don’t create a database region directly. Instead, you use one of those methods:

    • SelectStatement.fetchedRegion:

      let statement = db.makeSelectStatement(SELECT name, score FROM players) print(statement.fetchedRegion) // prints players(name,score)

    • Request.fetchedRegion(_:)

      let request = Player.filter(key: 1) try print(request.fetchedRegion(db)) // prints players(*)[1]

    Database regions returned by requests can be more precise than regions returned by select statements. Especially, regions returned by statements don’t know about rowids:

    // A plain statement
let statement = db.makeSelectStatement("SELECT * FROM players WHERE id = 1")
statement.fetchedRegion       // "players(*)"

// A query interface request that executes the same statement:
let request = Player.filter(key: 1)
try request.fetchedRegion(db) // "players(*)[1]"
    See more

    Declaration

    Swift

    public struct DatabaseRegion
    Show on GitHub

DatabaseValue

UITableView Support

  • FTS3 lets you define fts3 virtual tables.

    // CREATE VIRTUAL TABLE documents USING fts3(content)
try db.create(virtualTable: "documents", using: FTS3()) { t in
    t.column("content")
}
    See more

    Declaration

    Swift

    public struct FTS3 : VirtualTableModule
    Show on GitHub

  • FTS5 lets you define fts5 virtual tables.

    // CREATE VIRTUAL TABLE documents USING fts5(content)
try db.create(virtualTable: "documents", using: FTS5()) { t in
    t.column("content")
}

    See https://www.sqlite.org/fts5.html

    See more

PersistenceContainer

  • Use persistence containers in the encode(to:) method of your persistable records:

    struct Player : MutablePersistable {
    var id: Int64?
    var name: String?

    func encode(to container: inout PersistenceContainer) {
        container["id"] = id
        container["name"] = name
    }
}
    See more

    Declaration

    Swift

    public struct PersistenceContainer
    Show on GitHub

MutablePersistable

RowIndex

  • Indexes to (ColumnName, DatabaseValue) pairs in a database row.

    See more

    Declaration

    Swift

    public struct RowIndex : Comparable
    Show on GitHub

  • Experimental

    LayoutedColumnMapping is a type that supports the RowAdapter protocol.

    See more

    Declaration

    Swift

    public struct LayoutedColumnMapping
    Show on GitHub

  • ColumnMapping is a row adapter that maps column names.

    let adapter = ColumnMapping(["foo": "bar"])
let sql = "SELECT 'foo' AS foo, 'bar' AS bar, 'baz' AS baz"

// <Row foo:"bar">
try Row.fetchOne(db, sql, adapter: adapter)
    See more

    Declaration

    Swift

    public struct ColumnMapping : RowAdapter
    Show on GitHub

  • SuffixRowAdapter is a row adapter that hides the first columns in a row.

    let adapter = SuffixRowAdapter(fromIndex: 2)
let sql = "SELECT 1 AS foo, 2 AS bar, 3 AS baz"

// <Row baz:3>
try Row.fetchOne(db, sql, adapter: adapter)
    See more

    Declaration

    Swift

    public struct SuffixRowAdapter : RowAdapter
    Show on GitHub

  • RangeRowAdapter is a row adapter that only exposes a range of columns.

    let adapter = RangeRowAdapter(1..<3)
let sql = "SELECT 1 AS foo, 2 AS bar, 3 AS baz, 4 as qux"

// <Row bar:2 baz: 3>
try Row.fetchOne(db, sql, adapter: adapter)
    See more

    Declaration

    Swift

    public struct RangeRowAdapter : RowAdapter
    Show on GitHub

  • ScopeAdapter is a row adapter that lets you define scopes on rows.

    // Two adapters
let fooAdapter = ColumnMapping(["value": "foo"])
let barAdapter = ColumnMapping(["value": "bar"])

// Define scopes
let adapter = ScopeAdapter([
    "foo": fooAdapter,
    "bar": barAdapter])

// Fetch
let sql = "SELECT 'foo' AS foo, 'bar' AS bar"
let row = try Row.fetchOne(db, sql, adapter: adapter)!

// Scoped rows:
if let fooRow = row.scoped(on: "foo") {
    fooRow["value"]    // "foo"
}
if let barRow = row.scopeed(on: "bar") {
    barRow["value"]    // "bar"
}
    See more

    Declaration

    Swift

    public struct ScopeAdapter : RowAdapter
    Show on GitHub

  • SQLCollatedExpression taints an expression so that every derived expression is eventually evaluated using an SQLite collation.

    You create one by calling the SQLSpecificExpressible.collating() method.

    let email: SQLCollatedExpression = Column("email").collating(.nocase)

// SELECT * FROM players WHERE email = 'arthur@example.com' COLLATE NOCASE
Players.filter(email == "arthur@example.com")
    See more

    Declaration

    Swift

    public struct SQLCollatedExpression
    Show on GitHub

SQLExpressionLiteral

  • Experimental

    SQLExpressionLiteral is an expression built from a raw SQL snippet.

    SQLExpressionLiteral("1 + 2")

    The SQL literal may contain ? and colon-prefixed tokens:

    SQLExpressionLiteral("? + ?", arguments: [1, 2])
SQLExpressionLiteral(":one + :two", arguments: ["one": 1, "two": 2])
    See more

    Declaration

    Swift

    public struct SQLExpressionLiteral : SQLExpression
    Show on GitHub

SQLExpressionUnary

SQLExpressionBinary

SQLExpressionFunction

AllColumns

  • AllColumns is the * in SELECT *.

    You use AllColumns in your custom implementation of TableMapping.databaseSelection.

    For example:

    struct Player : TableMapping {
    static var databaseTableName = "players"
    static let databaseSelection: [SQLSelectable] = [AllColumns(), Column.rowID]
}

// SELECT *, rowid FROM players
let request = Player.all()
    See more

    Declaration

    Swift

    public struct AllColumns
    Show on GitHub

StatementArguments

  • StatementArguments provide values to argument placeholders in raw SQL queries.

    Placeholders can take several forms (see https://www.sqlite.org/lang_expr.html#varparam for more information):

    • ?NNN (e.g. ?2): the NNN-th argument (starts at 1)
    • ?: the N-th argument, where N is one greater than the largest argument number already assigned

    • :AAAA (e.g. :name): named argument

    • $AAAA (e.g. $name): named argument

    Positional Arguments

    To fill question marks placeholders, feed StatementArguments with an array:

    db.execute(
    "INSERT ... (?, ?)",
    arguments: StatementArguments(["Arthur", 41]))

// Array literals are automatically converted:
db.execute(
    "INSERT ... (?, ?)",
    arguments: ["Arthur", 41])

    Named Arguments

    To fill named arguments, feed StatementArguments with a dictionary:

    db.execute(
    "INSERT ... (:name, :score)",
    arguments: StatementArguments(["name": "Arthur", "score": 41]))

// Dictionary literals are automatically converted:
db.execute(
    "INSERT ... (:name, :score)",
    arguments: ["name": "Arthur", "score": 41])

    Concatenating Arguments

    Several arguments can be concatenated and mixed with the append(contentsOf:) method and the +, &+, += operators:

    var arguments: StatementArguments = ["Arthur"]
arguments += [41]
db.execute("INSERT ... (?, ?)", arguments: arguments)

    + and += operators consider that overriding named arguments is a programmer error:

    var arguments: StatementArguments = ["name": "Arthur"]
arguments += ["name": "Barbara"]
// fatal error: already defined statement argument: name

    &+ and append(contentsOf:) allow overriding named arguments:

    var arguments: StatementArguments = ["name": "Arthur"]
arguments = arguments &+ ["name": "Barbara"]
print(arguments)
// Prints ["name": "Barbara"]

    Mixed Arguments

    When a statement consumes a mix of named and positional arguments, it prefers named arguments over positional ones. For example:

    let sql = "SELECT ?2 AS two, :foo AS foo, ?1 AS one, :foo AS foo2, :bar AS bar"
let row = try Row.fetchOne(db, sql, arguments: [1, 2, "bar"] + ["foo": "foo"])!
print(row)
// Prints <Row two:2 foo:"foo" one:1 foo2:"foo" bar:"bar">
    See more

    Declaration

    Swift

    public struct StatementArguments
    Show on GitHub

Database events