Structures

The following structures are available globally.

  • A column of a database table.

    This type closely matches the information returned by the table_info pragma.

    > CREATE TABLE player (
        id INTEGER PRIMARY KEY,
        firstName TEXT,
        lastName TEXT)
    > PRAGMA table_info("player")
    cid   name   type     notnull   dflt_value  pk
    ----  -----  -------  --------  ----------  ---
    0     id     INTEGER  0         NULL        1
    1     name   TEXT     0         NULL        0
    2     score  INTEGER  0         NULL        0
    

    See Database.columns(in:) and https://www.sqlite.org/pragma.html#pragma_table_info

    See more

    Declaration

    Swift

    public struct ColumnInfo : FetchableRecord
  • An index on a database table.

    See Database.indexes(on:)

    See more

    Declaration

    Swift

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

    When the table’s primary key is the rowid:

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

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

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

    Declaration

    Swift

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

    See more

    Declaration

    Swift

    public struct ForeignKeyInfo
  • 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:

    |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| | | |   | | | | |   | | | | |   | | | | |
    

    To create a database region, you use one of those methods:

    • DatabaseRegion.fullDatabase: the region that covers all database tables.

    • DatabaseRegion(): the empty region.

    • DatabaseRegion(table:): the region that covers one database table.

    • SelectStatement.databaseRegion:

      let statement = try db.makeSelectStatement(sql: "SELECT name, score FROM player")
      let region = statement.databaseRegion
      
    • FetchRequest.databaseRegion(_:)

      let request = Player.filter(key: 1)
      let region = try request.databaseRegion(db)
      
    See more

    Declaration

    Swift

    public struct DatabaseRegion : CustomStringConvertible, Equatable
  • DatabaseRegionObservation tracks changes in the results of database requests, and notifies each database transaction whenever the database changes.

    For example:

    let observation = DatabaseRegionObservation(tracking: Player.all)
    let observer = try observation.start(in: dbQueue) { db: Database in
        print("Players have changed.")
    }
    
    See more

    Declaration

    Swift

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

    Declaration

    Swift

    public struct RowIndex : Comparable, Strideable
  • EmptyRowAdapter is a row adapter that hides all columns.

    See more

    Declaration

    Swift

    public struct EmptyRowAdapter : RowAdapter
  • 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"
    
    // [foo:"bar"]
    try Row.fetchOne(db, sql: sql, adapter: adapter)
    
    See more

    Declaration

    Swift

    public struct ColumnMapping : RowAdapter
  • 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"
    
    // [baz:3]
    try Row.fetchOne(db, sql: sql, adapter: adapter)
    
    See more

    Declaration

    Swift

    public struct SuffixRowAdapter : RowAdapter
  • 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"
    
    // [bar:2 baz:3]
    try Row.fetchOne(db, sql: sql, adapter: adapter)
    
    See more

    Declaration

    Swift

    public struct RangeRowAdapter : RowAdapter
  • 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: sql, adapter: adapter)!
    
    // Scoped rows:
    if let fooRow = row.scopes["foo"] {
        fooRow["value"]    // "foo"
    }
    if let barRow = row.scopes["bar"] {
        barRow["value"]    // "bar"
    }
    
    See more

    Declaration

    Swift

    public struct ScopeAdapter : RowAdapter
  • SQLLiteral is a type which support SQL Interpolation.

    For example:

    try dbQueue.write { db in
        let name: String = ...
        let id: Int64 = ...
        let query: SQLLiteral = "UPDATE player SET name = \(name) WHERE id = \(id)"
        try db.execute(literal: query)
    }
    
    See more

    Declaration

    Swift

    public struct SQLLiteral
  • 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(
        sql: "INSERT ... (?, ?)",
        arguments: StatementArguments(["Arthur", 41]))
    
    // Array literals are automatically converted:
    db.execute(
        sql: "INSERT ... (?, ?)",
        arguments: ["Arthur", 41])
    

    Named Arguments

    To fill named arguments, feed StatementArguments with a dictionary:

    db.execute(
        sql: "INSERT ... (:name, :score)",
        arguments: StatementArguments(["name": "Arthur", "score": 41]))
    
    // Dictionary literals are automatically converted:
    db.execute(
        sql: "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(sql: "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

    It is possible to mix named and positional arguments. Yet this is usually confusing, and it is best to avoid this practice:

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

    Mixed arguments exist as a support for requests like the following:

    let players = try Player
        .filter(sql: "team = :team", arguments: ["team": "Blue"])
        .filter(sql: "score > ?", arguments: [1000])
        .fetchAll(db)
    
    See more

    Declaration

    Swift

    public struct StatementArguments: CustomStringConvertible, Equatable,
        ExpressibleByArrayLiteral, ExpressibleByDictionaryLiteral
  • FTS3 lets you define fts3 virtual tables.

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

    Declaration

    Swift

    public struct FTS3 : VirtualTableModule
  • FTS5 lets you define fts5 virtual tables.

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

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

    See more
  • 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()
    
    // 1st migration
    migrator.registerMigration("createLibrary") { db in
        try db.create(table: "author") { t in
            t.autoIncrementedPrimaryKey("id")
            t.column("creationDate", .datetime)
            t.column("name", .text).notNull()
        }
    
        try db.create(table: "book") { t in
            t.autoIncrementedPrimaryKey("id")
            t.column("authorId", .integer)
                .notNull()
                .references("author", onDelete: .cascade)
            t.column("title", .text).notNull()
        }
    }
    
    // 2nd migration
    migrator.registerMigration("AddBirthYearToAuthors") { db in
        try db.alter(table: "author") { t
            t.add(column: "birthYear", .integer)
        }
    }
    
    // Migrations for future versions will be inserted here:
    //
    // // 3rd migration
    // migrator.registerMigration("...") { db in
    //     ...
    // }
    
    try migrator.migrate(dbQueue)
    
    See more

    Declaration

    Swift

    public struct DatabaseMigrator
  • An AssociationAggregate is able to compute aggregated values from a population of associated records.

    For example:

    struct Author: TableRecord {
        static let books = hasMany(Book.self)
    }
    
    let bookCount = Author.books.count // AssociationAggregate<Author>
    

    Association aggregates can be used in the annotated(with:) and having(_:) request methods:

    let request = Author.annotated(with: bookCount)
    let request = Author.having(bookCount >= 10)
    

    The RowDecoder generic type helps the compiler prevent incorrect use of aggregates:

    // Won't compile because Fruit is not Author.
    let request = Fruit.annotated(with: bookCount)
    
    See more

    Declaration

    Swift

    public struct AssociationAggregate<RowDecoder>
  • The BelongsTo association sets up a one-to-one connection from a record type to another record type, such as each instance of the declaring record belongs to an instance of the other record.

    For example, if your application includes authors and books, and each book is assigned its author, you’d declare the association this way:

    struct Author: TableRecord { ... }
    struct Book: TableRecord {
        static let author = belongsTo(Author.self)
        ...
    }
    

    A BelongsTo associations should be supported by an SQLite foreign key.

    Foreign keys are the recommended way to declare relationships between database tables because not only will SQLite guarantee the integrity of your data, but GRDB will be able to use those foreign keys to automatically configure your association.

    You define the foreign key when you create database tables. For example:

    try db.create(table: "author") { t in
        t.autoIncrementedPrimaryKey("id")             // (1)
        t.column("name", .text)
    }
    try db.create(table: "book") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("authorId", .integer)                // (2)
            .notNull()                                // (3)
            .indexed()                                // (4)
            .references("author", onDelete: .cascade) // (5)
        t.column("title", .text)
    }
    
    1. The author table has a primary key.
    2. The book.authorId column is used to link a book to the author it belongs to.
    3. Make the book.authorId column not null if you want SQLite to guarantee that all books have an author.
    4. Create an index on the book.authorId column in order to ease the selection of an author’s books.
    5. Create a foreign key from book.authorId column to authors.id, so that SQLite guarantees that no book refers to a missing author. The onDelete: .cascade option has SQLite automatically delete all of an author’s books when that author is deleted. See https://sqlite.org/foreignkeys.html#fk_actions for more information.

    The example above uses auto-incremented primary keys. But generally speaking, all primary keys are supported.

    If the database schema does not define foreign keys between tables, you can still use BelongsTo associations. But your help is needed to define the missing foreign key:

    struct Book: FetchableRecord, TableRecord {
        static let author = belongsTo(Author.self, using: ForeignKey(...))
    }
    

    See ForeignKey for more information.

    Declaration

    Swift

    public struct BelongsToAssociation<Origin, Destination> : AssociationToOne where Origin : TableRecord, Destination : TableRecord
  • The HasMany association indicates a one-to-many connection between two record types, such as each instance of the declaring record has many instances of the other record.

    For example, if your application includes authors and books, and each author is assigned zero or more books, you’d declare the association this way:

    struct Book: TableRecord { ... }
    struct Author: TableRecord {
        static let books = hasMany(Book.self)
        ...
    }
    

    HasMany associations should be supported by an SQLite foreign key.

    Foreign keys are the recommended way to declare relationships between database tables because not only will SQLite guarantee the integrity of your data, but GRDB will be able to use those foreign keys to automatically configure your association.

    You define the foreign key when you create database tables. For example:

    try db.create(table: "author") { t in
        t.autoIncrementedPrimaryKey("id")             // (1)
        t.column("name", .text)
    }
    try db.create(table: "book") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("authorId", .integer)                // (2)
            .notNull()                                // (3)
            .indexed()                                // (4)
            .references("author", onDelete: .cascade) // (5)
        t.column("title", .text)
    }
    
    1. The author table has a primary key.
    2. The book.authorId column is used to link a book to the author it belongs to.
    3. Make the book.authorId column not null if you want SQLite to guarantee that all books have an author.
    4. Create an index on the book.authorId column in order to ease the selection of an author’s books.
    5. Create a foreign key from book.authorId column to authors.id, so that SQLite guarantees that no book refers to a missing author. The onDelete: .cascade option has SQLite automatically delete all of an author’s books when that author is deleted. See https://sqlite.org/foreignkeys.html#fk_actions for more information.

    The example above uses auto-incremented primary keys. But generally speaking, all primary keys are supported.

    If the database schema does not define foreign keys between tables, you can still use HasMany associations. But your help is needed to define the missing foreign key:

    struct Author: TableRecord {
        static let books = hasMany(Book.self, using: ForeignKey(...))
    }
    

    See ForeignKey for more information.

    Declaration

    Swift

    public struct HasManyAssociation<Origin, Destination> : AssociationToMany where Origin : TableRecord, Destination : TableRecord
  • The HasManyThrough association is often used to set up a many-to-many connection with another record. This association indicates that the declaring record can be matched with zero or more instances of another record by proceeding through a third record.

    For example, consider the practice of passport delivery. One coutry has many citizens through its passports:

    struct Country: TableRecord {
        static let passports = hasMany(Passport.self)
        static let citizens = hasMany(Citizen.self, through: passports, using: Passport.citizen)
        ...
    }
    
    struct Passport: TableRecord {
        static let citizen = belongsTo(Citizen.self)
        ...
    }
    
    struct Citizen: TableRecord { ... }
    

    The HasManyThrough association is also useful for setting up shortcuts through nested HasMany associations. For example, if a document has many sections, and a section has many paragraphs, you may sometimes want to get a simple collection of all paragraphs in the document. You could set that up this way:

    struct Document: TableRecord {
        static let sections = hasMany(Section.self)
        static let paragraphs = hasMany(Paragraph.self, through: sections, using: Section.paragraphs)
    }
    
    struct Section: TableRecord {
        static let paragraphs = hasMany(Paragraph.self)
    }
    
    struct Paragraph: TableRecord {
    }
    

    As in the examples above, HasManyThrough association is always built from two other associations: the through: and using: arguments. Those associations can be any other association (BelongsTo, HasMany, HasManyThrough, etc).

    Declaration

    Swift

    public struct HasManyThroughAssociation<Origin, Destination> : AssociationToMany where Origin : TableRecord, Destination : TableRecord
  • The HasOne association indicates a one-to-one connection between two record types, such as each instance of the declaring record has one instances of the other record.

    For example, if your application has one database table for countries, and another for their demographic profiles, you’d declare the association this way:

    struct Demographics: TableRecord { ... }
    struct Country: TableRecord {
        static let demographics = hasOne(Demographics.self)
        ...
    }
    

    HasOne associations should be supported by an SQLite foreign key.

    Foreign keys are the recommended way to declare relationships between database tables because not only will SQLite guarantee the integrity of your data, but GRDB will be able to use those foreign keys to automatically configure your association.

    You define the foreign key when you create database tables. For example:

    try db.create(table: "country") { t in
        t.column("code", .text).primaryKey()           // (1)
        t.column("name", .text)
    }
    try db.create(table: "demographics") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("countryCode", .text)                 // (2)
            .notNull()                                 // (3)
            .unique()                                  // (4)
            .references("country", onDelete: .cascade) // (5)
        t.column("population", .integer)
        t.column("density", .double)
    }
    
    1. The country table has a primary key.
    2. The demographics.countryCode column is used to link a demographic profile to the country it belongs to.
    3. Make the demographics.countryCode column not null if you want SQLite to guarantee that all profiles are linked to a country.
    4. Create a unique index on the demographics.countryCode column in order to guarantee the unicity of any country’s profile.
    5. Create a foreign key from demographics.countryCode column to country.code, so that SQLite guarantees that no profile refers to a missing country. The onDelete: .cascade option has SQLite automatically delete a profile when its country is deleted. See https://sqlite.org/foreignkeys.html#fk_actions for more information.

    The example above uses a string primary for the country table. But generally speaking, all primary keys are supported.

    If the database schema does not follow this convention, and does not define foreign keys between tables, you can still use HasOne associations. But your help is needed to define the missing foreign key:

    struct Country: FetchableRecord, TableRecord {
        static let demographics = hasOne(Demographics.self, using: ForeignKey(...))
    }
    

    See ForeignKey for more information.

    Declaration

    Swift

    public struct HasOneAssociation<Origin, Destination> : AssociationToOne where Origin : TableRecord, Destination : TableRecord
  • A HasOneThrough association sets up a one-to-one connection with another record. This association indicates that the declaring record can be matched with one instance of another record by proceeding through a third record. For example, if each book belongs to a library, and each library has one address, then one knows where the book should be returned to:

    struct Book: TableRecord {
        static let library = belongsTo(Library.self)
        static let returnAddress = hasOne(Address.self, through: library, using: library.address)
        ...
    }
    
    struct Library: TableRecord {
        static let address = hasOne(Address.self)
        ...
    }
    
    struct Address: TableRecord { ... }
    

    As in the example above, HasOneThrough association is always built from two other associations: the through: and using: arguments. Those associations can be any other association to one (BelongsTo, HasOne, HasOneThrough).

    Declaration

    Swift

    public struct HasOneThroughAssociation<Origin, Destination> : AssociationToOne where Origin : TableRecord, Destination : TableRecord
  • A ColumnAssignment can update rows in the database.

    You create an assignment from a column and an assignment operator, such as <- or +=:

    try dbQueue.write { db in
        // UPDATE player SET score = 0
        let assignment = Column("score") <- 0
        try Player.updateAll(db, assignment)
    }
    

    Declaration

    Swift

    public struct ColumnAssignment
  • Experimental

    SQLExpressionLiteral is an expression built from a raw SQL snippet.

    SQLExpressionLiteral(sql: "1 + 2")
    

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

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

    Declaration

    Swift

    public struct SQLExpressionLiteral : SQLExpression
  • AllColumns is the * in SELECT *.

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

    For example:

    struct Player : TableRecord {
        static var databaseTableName = "player"
        static let databaseSelection: [SQLSelectable] = [AllColumns(), Column.rowID]
    }
    
    // SELECT *, rowid FROM player
    let request = Player.all()
    
    See more

    Declaration

    Swift

    public struct AllColumns
  • A ForeignKey helps building associations when GRDB can’t infer a foreign key from the database schema.

    Sometimes the database schema does not define any foreign key between two tables. And sometimes, there are several foreign keys from a table to another:

    | Table book   |       | Table person |
    | ------------ |       | ------------ |
    | id           |   +--> id           |
    | authorId     ---+   | name         |
    | translatorId ---+
    | title        |
    

    When this happens, associations can’t be automatically inferred from the database schema. GRDB will complain with a fatal error such as Ambiguous foreign key from book to person, or Could not infer foreign key from book to person.

    Your help is needed. You have to instruct GRDB which foreign key to use:

    struct Book: TableRecord {
        // Define foreign keys
        static let authorForeignKey = ForeignKey(["authorId"]))
        static let translatorForeignKey = ForeignKey(["translatorId"]))
    
        // Use foreign keys to define associations:
        static let author = belongsTo(Person.self, using: authorForeignKey)
        static let translator = belongsTo(Person.self, using: translatorForeignKey)
    }
    

    Foreign keys are always defined from the table that contains the columns at the origin of the foreign key. Person’s symmetric HasMany associations reuse Book’s foreign keys:

    struct Person: TableRecord {
        static let writtenBooks = hasMany(Book.self, using: Book.authorForeignKey)
        static let translatedBooks = hasMany(Book.self, using: Book.translatorForeignKey)
    }
    

    Foreign keys can also be defined from query interface columns:

    struct Book: TableRecord {
        enum Columns: String, ColumnExpression {
            case id, title, authorId, translatorId
        }
    
        static let authorForeignKey = ForeignKey([Columns.authorId]))
        static let translatorForeignKey = ForeignKey([Columns.translatorId]))
    }
    

    When the destination table of a foreign key does not define any primary key, you need to provide the full definition of a foreign key:

    struct Book: TableRecord {
        static let authorForeignKey = ForeignKey(["authorId"], to: ["id"]))
        static let author = belongsTo(Person.self, using: authorForeignKey)
    }
    
    See more

    Declaration

    Swift

    public struct ForeignKey
  • Use persistence containers in the encode(to:) method of your encodable records:

    struct Player: EncodableRecord {
        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
  • ValueObservation tracks changes in the results of database requests, and notifies fresh values whenever the database changes.

    For example:

    let observation = Player.observationForAll()
    let observer = try observation.start(in: dbQueue) { players: [Player] in
        print("Players have changed.")
    }
    
    See more

    Declaration

    Swift

    public struct ValueObservation<Reducer>