TableRecord

public protocol TableRecord

Types that adopt TableRecord declare a particular relationship with a database table.

Types that adopt both TableRecord and FetchableRecord are granted with built-in methods that allow to fetch instances identified by key:

try Player.fetchOne(db, key: 123)  // Player?
try Citizenship.fetchOne(db, key: ["citizenId": 12, "countryId": 45]) // Citizenship?
  • databaseTableName Default implementation

    The name of the database table used to build requests.

    struct Player : TableRecord {
        static var databaseTableName = "player"
    }
    
    // SELECT * FROM player
    try Player.fetchAll(db)
    

    Default Implementation

    The default name of the database table used to build requests.

    • Player -> “player”
    • Place -> “place”
    • PostalAddress -> “postalAddress”
    • HTTPRequest -> “httpRequest”
    • TOEFL -> “toefl”

    Declaration

    Swift

    static var databaseTableName: String { get }
  • databaseSelection Default implementation

    The default request selection.

    Unless said otherwise, requests select all columns:

    // SELECT * FROM player
    try Player.fetchAll(db)
    

    You can provide a custom implementation and provide an explicit list of columns:

    struct RestrictedPlayer : TableRecord {
        static var databaseTableName = "player"
        static var databaseSelection = [Column("id"), Column("name")]
    }
    
    // SELECT id, name FROM player
    try RestrictedPlayer.fetchAll(db)
    

    You can also add extra columns such as the rowid column:

    struct ExtendedPlayer : TableRecord {
        static var databaseTableName = "player"
        static let databaseSelection: [SQLSelectable] = [AllColumns(), Column.rowID]
    }
    
    // SELECT *, rowid FROM player
    try ExtendedPlayer.fetchAll(db)
    

    Default Implementation

    Default value: [AllColumns()].

    Declaration

    Swift

    static var databaseSelection: [SQLSelectable] { get }

Full Text Search

  • matching(_:) Extension method

    Returns a QueryInterfaceRequest with a matching predicate.

    // SELECT * FROM book WHERE book MATCH '...'
    var request = Book.matching(pattern)
    

    If the search pattern is nil, the request does not match any database row.

    The selection defaults to all columns. This default can be changed for all requests by the TableRecord.databaseSelection property, or for individual requests with the TableRecord.select method.

    Declaration

    Swift

    public static func matching(_ pattern: FTS3Pattern?) -> QueryInterfaceRequest<Self>

Associations to TableRecord

  • belongsTo(_:key:using:) Extension method

    Creates a “Belongs To” association between Self and the destination type, based on a database foreign key.

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

    The association will let you define requests that load both the source and the destination type:

    // A request for all books with their authors:
    let request = Book.including(optional: Book.author)
    

    To consume those requests, define a type that adopts both the FetchableRecord and Decodable protocols:

    struct BookInfo: FetchableRecord, Decodable {
        var book: Book
        var author: Author?
    }
    
    let bookInfos = try dbQueue.read { db in
        return try BookInfo.fetchAll(db, request)
    }
    for bookInfo in bookInfos {
        print("\(bookInfo.book.title) by \(bookInfo.author.name)")
    }
    

    It is recommended that you define, alongside the static association, a property with the same name:

    struct Book: TableRecord, EncodableRecord {
        static let author = belongsTo(Author.self)
        var author: QueryInterfaceRequest<Author> {
            return request(for: Book.author)
        }
    }
    

    This property will let you navigate from the source type to the destination type:

    try dbQueue.read { db in
        let book: Book = ...
        let author = try book.author.fetchOne(db) // Author?
    }
    

    Declaration

    Swift

    public static func belongsTo<Destination>(
        _ destination: Destination.Type,
        key: String? = nil,
        using foreignKey: ForeignKey? = nil)
    -> BelongsToAssociation<Self, Destination>
    where Destination: TableRecord

    Parameters

    destination

    The record type at the other side of the association.

    key

    An eventual decoding key for the association. By default, it is destination.databaseTableName.

    foreignKey

    An eventual foreign key. You need to provide an explicit foreign key when GRDB can’t infer one from the database schema. This happens when the schema does not define any foreign key to the destination table, or when the schema defines several foreign keys to the destination table.

  • hasMany(_:key:using:) Extension method

    Creates a “Has many” association between Self and the destination type, based on a database foreign key.

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

    The association will let you define requests that load both the source and the destination type:

    // A request for all (author, book) pairs:
    let request = Author.including(required: Author.books)
    

    To consume those requests, define a type that adopts both the FetchableRecord and Decodable protocols:

    struct Authorship: FetchableRecord, Decodable {
        var author: Author
        var book: Book
    }
    
    let authorships = try dbQueue.read { db in
        return try Authorship.fetchAll(db, request)
    }
    for authorship in authorships {
        print("\(authorship.author.name) wrote \(authorship.book.title)")
    }
    

    It is recommended that you define, alongside the static association, a property with the same name:

    struct Author: TableRecord, EncodableRecord {
        static let books = hasMany(Book.self)
        var books: QueryInterfaceRequest<Book> {
            return request(for: Author.books)
        }
    }
    

    This property will let you navigate from the source type to the destination type:

    try dbQueue.read { db in
        let author: Author = ...
        let books = try author.books.fetchAll(db) // [Book]
    }
    

    Declaration

    Swift

    public static func hasMany<Destination>(
        _ destination: Destination.Type,
        key: String? = nil,
        using foreignKey: ForeignKey? = nil)
    -> HasManyAssociation<Self, Destination>
    where Destination: TableRecord

    Parameters

    destination

    The record type at the other side of the association.

    key

    An eventual decoding key for the association. By default, it is destination.databaseTableName.

    foreignKey

    An eventual foreign key. You need to provide an explicit foreign key when GRDB can’t infer one from the database schema. This happens when the schema does not define any foreign key from the destination table, or when the schema defines several foreign keys from the destination table.

  • hasOne(_:key:using:) Extension method

    Creates a “Has one” association between Self and the destination type, based on a database foreign key.

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

    The association will let you define requests that load both the source and the destination type:

    // A request for all countries with their demographic profile:
    let request = Country.including(optional: Country.demographics)
    

    To consume those requests, define a type that adopts both the FetchableRecord and Decodable protocols:

    struct CountryInfo: FetchableRecord, Decodable {
        var country: Country
        var demographics: Demographics?
    }
    
    let countryInfos = try dbQueue.read { db in
        return try CountryInfo.fetchAll(db, request)
    }
    for countryInfo in countryInfos {
        print("\(countryInfo.country.name) has \(countryInfo.demographics.population) citizens")
    }
    

    It is recommended that you define, alongside the static association, a property with the same name:

    struct Country: TableRecord, EncodableRecord {
        static let demographics = hasOne(Demographics.self)
        var demographics: QueryInterfaceRequest<Demographics> {
            return request(for: Country.demographics)
        }
    }
    

    This property will let you navigate from the source type to the destination type:

    try dbQueue.read { db in
        let country: Country = ...
        let demographics = try country.demographics.fetchOne(db) // Demographics?
    }
    

    Declaration

    Swift

    public static func hasOne<Destination>(
        _ destination: Destination.Type,
        key: String? = nil,
        using foreignKey: ForeignKey? = nil)
    -> HasOneAssociation<Self, Destination>
    where Destination: TableRecord

    Parameters

    destination

    The record type at the other side of the association.

    key

    An eventual decoding key for the association. By default, it is destination.databaseTableName.

    foreignKey

    An eventual foreign key. You need to provide an explicit foreign key when GRDB can’t infer one from the database schema. This happens when the schema does not define any foreign key from the destination table, or when the schema defines several foreign keys from the destination table.

Associations to Table

  • belongsTo(_:key:using:) Extension method

    Creates a “Belongs To” association between Self and the destination table, based on a database foreign key.

    For more information, see TableRecord.belongsTo(_:key:using:) where the first argument is TableRecord.

    Declaration

    Swift

    public static func belongsTo<Destination>(
        _ destination: Table<Destination>,
        key: String? = nil,
        using foreignKey: ForeignKey? = nil)
    -> BelongsToAssociation<Self, Destination>

    Parameters

    destination

    The table at the other side of the association.

    key

    An eventual decoding key for the association. By default, it is destination.tableName.

    foreignKey

    An eventual foreign key. You need to provide an explicit foreign key when GRDB can’t infer one from the database schema. This happens when the schema does not define any foreign key to the destination table, or when the schema defines several foreign keys to the destination table.

  • hasMany(_:key:using:) Extension method

    Creates a “Has many” association between Self and the destination table, based on a database foreign key.

    For more information, see TableRecord.hasMany(_:key:using:) where the first argument is TableRecord.

    Declaration

    Swift

    public static func hasMany<Destination>(
        _ destination: Table<Destination>,
        key: String? = nil,
        using foreignKey: ForeignKey? = nil)
    -> HasManyAssociation<Self, Destination>

    Parameters

    destination

    The table at the other side of the association.

    key

    An eventual decoding key for the association. By default, it is destination.tableName.

    foreignKey

    An eventual foreign key. You need to provide an explicit foreign key when GRDB can’t infer one from the database schema. This happens when the schema does not define any foreign key from the destination table, or when the schema defines several foreign keys from the destination table.

  • hasOne(_:key:using:) Extension method

    Creates a “Has one” association between Self and the destination table, based on a database foreign key.

    For more information, see TableRecord.hasOne(_:key:using:) where the first argument is TableRecord.

    Declaration

    Swift

    public static func hasOne<Destination>(
        _ destination: Table<Destination>,
        key: String? = nil,
        using foreignKey: ForeignKey? = nil)
    -> HasOneAssociation<Self, Destination>

    Parameters

    destination

    The table at the other side of the association.

    key

    An eventual decoding key for the association. By default, it is destination.databaseTableName.

    foreignKey

    An eventual foreign key. You need to provide an explicit foreign key when GRDB can’t infer one from the database schema. This happens when the schema does not define any foreign key from the destination table, or when the schema defines several foreign keys from the destination table.

Associations to CommonTableExpression

  • association(to:on:) Extension method

    Creates an association to a common table expression that you can join or include in another request.

    The key of the returned association is the table name of the common table expression.

    For example, you can build a request that fetches all chats with their latest message:

    let latestMessageRequest = Message
        .annotated(with: max(Column("date")))
        .group(Column("chatID"))
    
    let latestMessageCTE = CommonTableExpression(
        named: "latestMessage",
        request: latestMessageRequest)
    
    let latestMessage = Chat.association(
        to: latestMessageCTE,
        on: { chat, latestMessage in
            chat[Column("id")] == latestMessage[Column("chatID")]
        })
    
    // WITH latestMessage AS
    //   (SELECT *, MAX(date) FROM message GROUP BY chatID)
    // SELECT chat.*, latestMessage.*
    // FROM chat
    // LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
    let request = Chat
        .with(latestMessageCTE)
        .including(optional: latestMessage)
    

    Declaration

    Swift

    public static func association<Destination>(
        to cte: CommonTableExpression<Destination>,
        on condition: @escaping (_ left: TableAlias, _ right: TableAlias) -> SQLExpressible)
    -> JoinAssociation<Self, Destination>

    Parameters

    cte

    A common table expression.

    condition

    A function that returns the joining clause.

    left

    A TableAlias for the left table.

    right

    A TableAlias for the right table.

    Return Value

    An association to the common table expression.

  • association(to:) Extension method

    Creates an association to a common table expression that you can join or include in another request.

    The key of the returned association is the table name of the common table expression.

    Declaration

    Swift

    public static func association<Destination>(
        to cte: CommonTableExpression<Destination>)
    -> JoinAssociation<Self, Destination>

    Parameters

    cte

    A common table expression.

    Return Value

    An association to the common table expression.

“Through” Associations

  • Creates a “Has Many Through” association between Self and the destination type.

    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 association will let you define requests that load both the source and the destination type:

    // A request for all (country, citizen) pairs:
    let request = Country.including(required: Coutry.citizens)
    

    To consume those requests, define a type that adopts both the FetchableRecord and Decodable protocols:

    struct Citizenship: FetchableRecord, Decodable {
        var country: Country
        var citizen: Citizen
    }
    
    let citizenships = try dbQueue.read { db in
        return try Citizenship.fetchAll(db, request)
    }
    for citizenship in citizenships {
        print("\(citizenship.citizen.name) is a citizen of \(citizenship.country.name)")
    }
    

    It is recommended that you define, alongside the static association, a property with the same name:

    struct Country: TableRecord, EncodableRecord {
        static let passports = hasMany(Passport.self)
        static let citizens = hasMany(Citizen.self, through: passports, using: Passport.citizen)
        var citizens: QueryInterfaceRequest<Citizen> {
            return request(for: Country.citizens)
        }
    }
    

    This property will let you navigate from the source type to the destination type:

    try dbQueue.read { db in
        let country: Country = ...
        let citizens = try country.citizens.fetchAll(db) // [Country]
    }
    

    Declaration

    Swift

    public static func hasMany<Pivot, Target>(
        _ destination: Target.RowDecoder.Type,
        through pivot: Pivot,
        using target: Target,
        key: String? = nil)
    -> HasManyThroughAssociation<Self, Target.RowDecoder>
    where Pivot: Association,
          Target: Association,
          Pivot.OriginRowDecoder == Self,
          Pivot.RowDecoder == Target.OriginRowDecoder

    Parameters

    destination

    The record type at the other side of the association.

    pivot

    An association from Self to the intermediate type.

    target

    A target association from the intermediate type to the destination type.

    key

    An eventual decoding key for the association. By default, it is the same key as the target.

  • hasOne(_:through:using:key:) Extension method

    Creates a “Has One Through” association between Self and the destination type.

    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 { ... }
    

    The association will let you define requests that load both the source and the destination type:

    // A request for all (book, returnAddress) pairs:
    let request = Book.including(required: Book.returnAddress)
    

    To consume those requests, define a type that adopts both the FetchableRecord and Decodable protocols:

    struct Todo: FetchableRecord, Decodable {
        var book: Book
        var address: Address
    }
    
    let todos = try dbQueue.read { db in
        return try Todo.fetchAll(db, request)
    }
    for todo in todos {
        print("Please return \(todo.book) to \(todo.address)")
    }
    

    It is recommended that you define, alongside the static association, a property with the same name:

    struct Book: TableRecord, EncodableRecord {
        static let library = belongsTo(Library.self)
        static let returnAddress = hasOne(Address.self, through: library, using: library.address)
        var returnAddress: QueryInterfaceRequest<Address> {
            return request(for: Book.returnAddress)
        }
    }
    

    This property will let you navigate from the source type to the destination type:

    try dbQueue.read { db in
        let book: Book = ...
        let address = try book.returnAddress.fetchOne(db) // Address?
    }
    

    Declaration

    Swift

    public static func hasOne<Pivot, Target>(
        _ destination: Target.RowDecoder.Type,
        through pivot: Pivot,
        using target: Target,
        key: String? = nil)
    -> HasOneThroughAssociation<Self, Target.RowDecoder>
    where Pivot: AssociationToOne,
          Target: AssociationToOne,
          Pivot.OriginRowDecoder == Self,
          Pivot.RowDecoder == Target.OriginRowDecoder

    Parameters

    destination

    The record type at the other side of the association.

    pivot

    An association from Self to the intermediate type.

    target

    A target association from the intermediate type to the destination type.

    key

    An eventual decoding key for the association. By default, it is the same key as the target.

Joining Methods

  • including(all:) Extension method

    Creates a request that prefetches an association.

    Declaration

    Swift

    public static func including<A: AssociationToMany>(all association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self
  • including(optional:) Extension method

    Creates a request that includes an association. The columns of the associated record are selected. The returned association does not require that the associated database table contains a matching row.

    Declaration

    Swift

    public static func including<A: Association>(optional association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self
  • including(required:) Extension method

    Creates a request that includes an association. The columns of the associated record are selected. The returned association requires that the associated database table contains a matching row.

    Declaration

    Swift

    public static func including<A: Association>(required association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self
  • joining(optional:) Extension method

    Creates a request that includes an association. The columns of the associated record are not selected. The returned association does not require that the associated database table contains a matching row.

    Declaration

    Swift

    public static func joining<A: Association>(optional association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self
  • joining(required:) Extension method

    Creates a request that includes an association. The columns of the associated record are not selected. The returned association requires that the associated database table contains a matching row.

    Declaration

    Swift

    public static func joining<A: Association>(required association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self
  • annotated(withOptional:) Extension method

    Creates a request which appends columns of an associated record to the selection.

    // SELECT player.*, team.color
    // FROM player LEFT JOIN team ...
    let teamColor = Player.team.select(Column("color"))
    let request = Player.annotated(withOptional: teamColor)
    

    This method performs the same SQL request as including(optional:). The difference is in the shape of Decodable records that decode such a request: the associated columns can be decoded at the same level as the main record:

    struct PlayerWithTeamColor: FetchableRecord, Decodable {
        var player: Player
        var color: String?
    }
    let players = try dbQueue.read { db in
        try request
            .asRequest(of: PlayerWithTeamColor.self)
            .fetchAll(db)
    }
    

    Note: this is a convenience method. You can build the same request with TableAlias, annotated(with:), and joining(optional:):

    let teamAlias = TableAlias()
    let request = Player
        .annotated(with: teamAlias[Column("color")])
        .joining(optional: Player.team.aliased(teamAlias))
    

    Declaration

    Swift

    public static func annotated<A: Association>(withOptional association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self
  • annotated(withRequired:) Extension method

    Creates a request which appends columns of an associated record to the selection.

    // SELECT player.*, team.color
    // FROM player JOIN team ...
    let teamColor = Player.team.select(Column("color"))
    let request = Player.annotated(withRequired: teamColor)
    

    This method performs the same SQL request as including(required:). The difference is in the shape of Decodable records that decode such a request: the associated columns can be decoded at the same level as the main record:

    struct PlayerWithTeamColor: FetchableRecord, Decodable {
        var player: Player
        var color: String
    }
    let players = try dbQueue.read { db in
        try request
            .asRequest(of: PlayerWithTeamColor.self)
            .fetchAll(db)
    }
    

    Note: this is a convenience method. You can build the same request with TableAlias, annotated(with:), and joining(required:):

    let teamAlias = TableAlias()
    let request = Player
        .annotated(with: teamAlias[Column("color")])
        .joining(required: Player.team.aliased(teamAlias))
    

    Declaration

    Swift

    public static func annotated<A: Association>(withRequired association: A)
    -> QueryInterfaceRequest<Self>
    where A.OriginRowDecoder == Self

Aggregates

  • annotated(with:) Extension method

    Creates a request with aggregates appended to the selection.

    // SELECT player.*, COUNT(DISTINCT book.id) AS bookCount
    // FROM player LEFT JOIN book ...
    let request = Player.annotated(with: Player.books.count)
    

    Declaration

    Swift

    public static func annotated(with aggregates: AssociationAggregate<Self>...) -> QueryInterfaceRequest<Self>
  • annotated(with:) Extension method

    Creates a request with aggregates appended to the selection.

    // SELECT player.*, COUNT(DISTINCT book.id) AS bookCount
    // FROM player LEFT JOIN book ...
    let request = Player.annotated(with: [Player.books.count])
    

    Declaration

    Swift

    public static func annotated(with aggregates: [AssociationAggregate<Self>]) -> QueryInterfaceRequest<Self>
  • having(_:) Extension method

    Creates a request with the provided aggregate predicate.

    // SELECT player.*
    // FROM player LEFT JOIN book ...
    // HAVING COUNT(DISTINCT book.id) = 0
    var request = Player.all()
    request = request.having(Player.books.isEmpty)
    

    The selection defaults to all columns. This default can be changed for all requests by the TableRecord.databaseSelection property, or for individual requests with the TableRecord.select method.

    Declaration

    Swift

    public static func having(_ predicate: AssociationAggregate<Self>) -> QueryInterfaceRequest<Self>

Request Derivation

  • all() Extension method

    Creates a request which fetches all records.

    // SELECT * FROM player
    let request = Player.all()
    

    Declaration

    Swift

    public static func all() -> QueryInterfaceRequest<Self>
  • none() Extension method

    Creates a request which fetches no record.

    Declaration

    Swift

    public static func none() -> QueryInterfaceRequest<Self>
  • select(_:) Extension method

    Creates a request which selects selection.

    // SELECT id, email FROM player
    let request = Player.select(Column("id"), Column("email"))
    

    Declaration

    Swift

    public static func select(_ selection: SQLSelectable...) -> QueryInterfaceRequest<Self>
  • select(_:) Extension method

    Creates a request which selects selection.

    // SELECT id, email FROM player
    let request = Player.select([Column("id"), Column("email")])
    

    Declaration

    Swift

    public static func select(_ selection: [SQLSelectable]) -> QueryInterfaceRequest<Self>
  • select(sql:arguments:) Extension method

    Creates a request which selects sql.

    // SELECT id, email FROM player
    let request = Player.select(sql: "id, email")
    

    Declaration

    Swift

    public static func select(
        sql: String,
        arguments: StatementArguments = StatementArguments())
    -> QueryInterfaceRequest<Self>
  • select(literal:) Extension method

    Creates a request which selects an SQL literal.

    Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

    // SELECT id, email, score + 1000 FROM player
    let bonus = 1000
    let request = Player.select(literal: """
        id, email, score + \(bonus)
        """)
    

    Declaration

    Swift

    public static func select(literal sqlLiteral: SQL) -> QueryInterfaceRequest<Self>
  • select(_:as:) Extension method

    Creates a request which selects selection, and fetches values of type type.

    try dbQueue.read { db in
        // SELECT max(score) FROM player
        let request = Player.select([max(Column("score"))], as: Int.self)
        let maxScore: Int? = try request.fetchOne(db)
    }
    

    Declaration

    Swift

    public static func select<RowDecoder>(
        _ selection: [SQLSelectable],
        as type: RowDecoder.Type = RowDecoder.self)
    -> QueryInterfaceRequest<RowDecoder>
  • select(_:as:) Extension method

    Creates a request which selects selection, and fetches values of type type.

    try dbQueue.read { db in
        // SELECT max(score) FROM player
        let request = Player.select(max(Column("score")), as: Int.self)
        let maxScore: Int? = try request.fetchOne(db)
    }
    

    Declaration

    Swift

    public static func select<RowDecoder>(
        _ selection: SQLSelectable...,
        as type: RowDecoder.Type = RowDecoder.self)
    -> QueryInterfaceRequest<RowDecoder>
  • select(sql:arguments:as:) Extension method

    Creates a request which selects sql, and fetches values of type type.

    try dbQueue.read { db in
        // SELECT max(score) FROM player
        let request = Player.select(sql: "max(score)", as: Int.self)
        let maxScore: Int? = try request.fetchOne(db)
    }
    

    Declaration

    Swift

    public static func select<RowDecoder>(
        sql: String,
        arguments: StatementArguments = StatementArguments(),
        as type: RowDecoder.Type = RowDecoder.self)
    -> QueryInterfaceRequest<RowDecoder>
  • select(literal:as:) Extension method

    Creates a request which selects an SQL literal, and fetches values of type type.

    Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

    // SELECT IFNULL(name, 'Anonymous') FROM player
    let defaultName = "Anonymous"
    let request = Player.select(
        literal: "IFNULL(name, \(defaultName))",
        as: String.self)
    let name: String? = try request.fetchOne(db)
    

    Declaration

    Swift

    public static func select<RowDecoder>(
        literal sqlLiteral: SQL,
        as type: RowDecoder.Type = RowDecoder.self)
    -> QueryInterfaceRequest<RowDecoder>
  • annotated(with:) Extension method

    Creates a request which appends selection.

    // SELECT id, email, name FROM player
    let request = Player
        .select([Column("id"), Column("email")])
        .annotated(with: [Column("name")])
    

    Declaration

    Swift

    public static func annotated(with selection: [SQLSelectable]) -> QueryInterfaceRequest<Self>
  • annotated(with:) Extension method

    Creates a request which appends selection.

    // SELECT id, email, name FROM player
    let request = Player
        .select([Column("id"), Column("email")])
        .annotated(with: Column("name"))
    

    Declaration

    Swift

    public static func annotated(with selection: SQLSelectable...) -> QueryInterfaceRequest<Self>
  • filter(_:) Extension method

    Creates a request with the provided predicate.

    // SELECT * FROM player WHERE email = 'arthur@example.com'
    let request = Player.filter(Column("email") == "arthur@example.com")
    

    Declaration

    Swift

    @available(*, deprecated, message: "Did you mean filter(id:﹚ or filter(key:﹚? If not, prefer filter(value.databaseValue﹚ instead. See also none(﹚.")
    public static func filter(_ predicate: SQLExpressible) -> QueryInterfaceRequest<Self>
  • filter(_:) Extension method

    Creates a request with the provided predicate.

    // SELECT * FROM player WHERE email = 'arthur@example.com'
    let request = Player.filter(Column("email") == "arthur@example.com")
    

    Declaration

    Swift

    public static func filter(_ predicate: SQLSpecificExpressible) -> QueryInterfaceRequest<Self>
  • filter(key:) Extension method

    Creates a request with the provided primary key predicate.

    // SELECT * FROM player WHERE id = 1
    let request = Player.filter(key: 1)
    

    Declaration

    Swift

    public static func filter<PrimaryKeyType>(key: PrimaryKeyType?)
    -> QueryInterfaceRequest<Self>
    where PrimaryKeyType: DatabaseValueConvertible
  • filter(keys:) Extension method

    Creates a request with the provided primary key predicate.

    // SELECT * FROM player WHERE id IN (1, 2, 3)
    let request = Player.filter(keys: [1, 2, 3])
    

    Declaration

    Swift

    public static func filter<Sequence>(keys: Sequence)
    -> QueryInterfaceRequest<Self>
    where Sequence: Swift.Sequence, Sequence.Element: DatabaseValueConvertible
  • filter(key:) Extension method

    Creates a request with the provided primary key predicate.

    // SELECT * FROM passport WHERE personId = 1 AND countryCode = 'FR'
    let request = Passport.filter(key: ["personId": 1, "countryCode": "FR"])
    

    When executed, this request raises a fatal error if there is no unique index on the key columns.

    Declaration

    Swift

    public static func filter(key: [String : DatabaseValueConvertible?]?) -> QueryInterfaceRequest<Self>
  • filter(keys:) Extension method

    Creates a request with the provided primary key predicate.

    // SELECT * FROM passport WHERE (personId = 1 AND countryCode = 'FR') OR ...
    let request = Passport.filter(keys: [["personId": 1, "countryCode": "FR"], ...])
    

    When executed, this request raises a fatal error if there is no unique index on the key columns.

    Declaration

    Swift

    public static func filter(keys: [[String : DatabaseValueConvertible?]]) -> QueryInterfaceRequest<Self>
  • filter(sql:arguments:) Extension method

    Creates a request with the provided predicate.

    // SELECT * FROM player WHERE email = 'arthur@example.com'
    let request = Player.filter(sql: "email = ?", arguments: ["arthur@example.com"])
    

    Declaration

    Swift

    public static func filter(
        sql: String,
        arguments: StatementArguments = StatementArguments())
    -> QueryInterfaceRequest<Self>
  • filter(literal:) Extension method

    Creates a request with the provided predicate added to the eventual set of already applied predicates.

    Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

    // SELECT * FROM player WHERE name = 'O''Brien'
    let name = "O'Brien"
    let request = Player.filter(literal: "email = \(email)")
    

    Declaration

    Swift

    public static func filter(literal sqlLiteral: SQL) -> QueryInterfaceRequest<Self>
  • order(_:) Extension method

    Creates a request sorted according to the provided orderings.

    // SELECT * FROM player ORDER BY name
    let request = Player.order(Column("name"))
    

    Declaration

    Swift

    public static func order(_ orderings: SQLOrderingTerm...) -> QueryInterfaceRequest<Self>
  • order(_:) Extension method

    Creates a request sorted according to the provided orderings.

    // SELECT * FROM player ORDER BY name
    let request = Player.order([Column("name")])
    

    Declaration

    Swift

    public static func order(_ orderings: [SQLOrderingTerm]) -> QueryInterfaceRequest<Self>
  • orderByPrimaryKey() Extension method

    Creates a request sorted by primary key.

    // SELECT * FROM player ORDER BY id
    let request = Player.orderByPrimaryKey()
    
    // SELECT * FROM country ORDER BY code
    let request = Country.orderByPrimaryKey()
    

    Declaration

    Swift

    public static func orderByPrimaryKey() -> QueryInterfaceRequest<Self>
  • order(sql:arguments:) Extension method

    Creates a request sorted according to sql.

    // SELECT * FROM player ORDER BY name
    let request = Player.order(sql: "name")
    

    Declaration

    Swift

    public static func order(
        sql: String,
        arguments: StatementArguments = StatementArguments())
    -> QueryInterfaceRequest<Self>
  • order(literal:) Extension method

    Creates a request sorted according to an SQL literal.

    // SELECT * FROM player ORDER BY name
    let request = Player.order(literal: "name")
    

    Declaration

    Swift

    public static func order(literal sqlLiteral: SQL) -> QueryInterfaceRequest<Self>
  • limit(_:offset:) Extension method

    Creates a request which fetches limit rows, starting at offset.

    // SELECT * FROM player LIMIT 1
    let request = Player.limit(1)
    

    Declaration

    Swift

    public static func limit(_ limit: Int, offset: Int? = nil) -> QueryInterfaceRequest<Self>
  • aliased(_:) Extension method

    Creates a request that allows you to define expressions that target a specific database table.

    In the example below, the “team.avgScore < player.score” condition in the ON clause could be not achieved without table aliases.

    struct Player: TableRecord {
        static let team = belongsTo(Team.self)
    }
    
    // SELECT player.*, team.*
    // JOIN team ON ... AND team.avgScore < player.score
    let playerAlias = TableAlias()
    let request = Player
        .aliased(playerAlias)
        .including(required: Player.team.filter(Column("avgScore") < playerAlias[Column("score")])
    

    Declaration

    Swift

    public static func aliased(_ alias: TableAlias) -> QueryInterfaceRequest<Self>
  • with(_:) Extension method

    Returns a request which embeds the common table expression.

    If a common table expression with the same table name had already been embedded, it is replaced by the new one.

    For example, you can build a request that fetches all chats with their latest post:

    let latestMessageRequest = Message
        .annotated(with: max(Column("date")))
        .group(Column("chatID"))
    
    let latestMessageCTE = CommonTableExpression(
        named: "latestMessage",
        request: latestMessageRequest)
    
    let latestMessage = Chat.association(
        to: latestMessageCTE,
        on: { chat, latestMessage in
            chat[Column("id")] == latestMessage[Column("chatID")]
        })
    
    // WITH latestMessage AS
    //   (SELECT *, MAX(date) FROM message GROUP BY chatID)
    // SELECT chat.*, latestMessage.*
    // FROM chat
    // LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
    let request = Chat
        .with(latestMessageCTE)
        .including(optional: latestMessage)
    

    Declaration

    Swift

    public static func with<RowDecoder>(_ cte: CommonTableExpression<RowDecoder>) -> QueryInterfaceRequest<Self>

    Parameters

    cte

    A common table expression.

    Return Value

    A request.

Counting All

  • fetchCount(_:) Extension method

    The number of records.

    Declaration

    Swift

    public static func fetchCount(_ db: Database) throws -> Int

    Parameters

    db

    A database connection.

SQL Generation

  • numberOfSelectedColumns(_:) Extension method

    Returns the number of selected columns.

    For example:

    struct Player: TableRecord {
        static let databaseTableName = "player"
    }
    
    try dbQueue.write { db in
        try db.create(table: "player") { t in
            t.autoIncrementedPrimaryKey("id")
            t.column("name", .text)
            t.column("score", .integer)
        }
    
        // 3
        try Player.numberOfSelectedColumns(db)
    }
    

    Declaration

    Swift

    public static func numberOfSelectedColumns(_ db: Database) throws -> Int

Batch Delete

  • deleteAll(_:) Extension method

    Deletes all records; returns the number of deleted rows.

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    @discardableResult
    public static func deleteAll(_ db: Database) throws -> Int

    Parameters

    db

    A database connection.

    Return Value

    The number of deleted rows

Check Existence by Single-Column Primary Key

  • exists(_:key:) Extension method

    Returns whether a row exists for this primary key.

    try Player.exists(db, key: 123)
    try Country.exists(db, key: "FR")
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    try Document.exists(db, key: 1)
    

    Declaration

    Swift

    public static func exists<PrimaryKeyType>(_ db: Database, key: PrimaryKeyType)
    throws -> Bool
    where PrimaryKeyType: DatabaseValueConvertible

    Parameters

    db

    A database connection.

    key

    A primary key value.

    Return Value

    Whether a row exists for this primary key.

Check Existence by Key

  • exists(_:key:) Extension method

    Returns whether a row exists for this unique key (primary key or any key with a unique index on it).

    try Player.exists(db, key: ["name": Arthur"])
    

    Declaration

    Swift

    public static func exists(_ db: Database, key: [String : DatabaseValueConvertible?]) throws -> Bool

    Parameters

    db

    A database connection.

    key

    A dictionary of values.

    Return Value

    Whether a row exists for this key.

Deleting by Single-Column Primary Key

  • deleteAll(_:keys:) Extension method

    Delete records identified by their primary keys; returns the number of deleted rows.

    // DELETE FROM player WHERE id IN (1, 2, 3)
    try Player.deleteAll(db, keys: [1, 2, 3])
    
    // DELETE FROM country WHERE code IN ('FR', 'US', 'DE')
    try Country.deleteAll(db, keys: ["FR", "US", "DE"])
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    // DELETE FROM document WHERE rowid IN (1, 2, 3)
    try Document.deleteAll(db, keys: [1, 2, 3])
    

    Declaration

    Swift

    @discardableResult
    public static func deleteAll<Sequence>(_ db: Database, keys: Sequence)
    throws -> Int
    where Sequence: Swift.Sequence, Sequence.Element: DatabaseValueConvertible

    Parameters

    db

    A database connection.

    keys

    A sequence of primary keys.

    Return Value

    The number of deleted rows

  • deleteOne(_:key:) Extension method

    Delete a record, identified by its primary key; returns whether a database row was deleted.

    // DELETE FROM player WHERE id = 123
    try Player.deleteOne(db, key: 123)
    
    // DELETE FROM country WHERE code = 'FR'
    try Country.deleteOne(db, key: "FR")
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    // DELETE FROM document WHERE rowid = 1
    try Document.deleteOne(db, key: 1)
    

    Declaration

    Swift

    @discardableResult
    public static func deleteOne<PrimaryKeyType>(_ db: Database, key: PrimaryKeyType?)
    throws -> Bool
    where PrimaryKeyType: DatabaseValueConvertible

    Parameters

    db

    A database connection.

    key

    A primary key value.

    Return Value

    Whether a database row was deleted.

Deleting by Key

  • deleteAll(_:keys:) Extension method

    Delete records identified by the provided unique keys (primary key or any key with a unique index on it); returns the number of deleted rows.

    try Player.deleteAll(db, keys: [["email": "a@example.com"], ["email": "b@example.com"]])
    

    Declaration

    Swift

    @discardableResult
    public static func deleteAll(_ db: Database, keys: [[String : DatabaseValueConvertible?]]) throws -> Int

    Parameters

    db

    A database connection.

    keys

    An array of key dictionaries.

    Return Value

    The number of deleted rows

  • deleteOne(_:key:) Extension method

    Delete a record, identified by a unique key (the primary key or any key with a unique index on it); returns whether a database row was deleted.

    Player.deleteOne(db, key: ["name": Arthur"])
    

    Declaration

    Swift

    @discardableResult
    public static func deleteOne(_ db: Database, key: [String : DatabaseValueConvertible?]) throws -> Bool

    Parameters

    db

    A database connection.

    key

    A dictionary of values.

    Return Value

    Whether a database row was deleted.

Batch Update

  • updateAll(_:onConflict:_:) Extension method

    Updates all records; returns the number of updated records.

    For example:

    try dbQueue.write { db in
        // UPDATE player SET score = 0
        try Player.updateAll(db, [Column("score").set(to: 0)])
    }
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    @discardableResult
    public static func updateAll(
        _ db: Database,
        onConflict conflictResolution: Database.ConflictResolution? = nil,
        _ assignments: [ColumnAssignment])
    throws -> Int

    Parameters

    db

    A database connection.

    conflictResolution

    A policy for conflict resolution, defaulting to the record’s persistenceConflictPolicy.

    assignments

    An array of column assignments.

    Return Value

    The number of updated rows.

  • updateAll(_:onConflict:_:_:) Extension method

    Updates all records; returns the number of updated records.

    For example:

    try dbQueue.write { db in
        // UPDATE player SET score = 0
        try Player.updateAll(db, Column("score").set(to: 0))
    }
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    @discardableResult
    public static func updateAll(
        _ db: Database,
        onConflict conflictResolution: Database.ConflictResolution? = nil,
        _ assignment: ColumnAssignment,
        _ otherAssignments: ColumnAssignment...)
    throws -> Int

    Parameters

    db

    A database connection.

    conflictResolution

    A policy for conflict resolution, defaulting to the record’s persistenceConflictPolicy.

    assignment

    A column assignment.

    otherAssignments

    Eventual other column assignments.

    Return Value

    The number of updated rows.

Available where Self: EncodableRecord

  • request(for:) Extension method

    Creates a request that fetches the associated record(s).

    For example:

    struct Team: TableRecord, EncodableRecord {
        static let players = hasMany(Player.self)
        var players: QueryInterfaceRequest<Player> {
            return request(for: Team.players)
        }
    }
    
    let team: Team = ...
    let players = try team.players.fetchAll(db) // [Player]
    

    Declaration

    Swift

    public func request<A: Association>(for association: A)
    -> QueryInterfaceRequest<A.RowDecoder>
    where A.OriginRowDecoder == Self

Available where Self: Identifiable, ID: DatabaseValueConvertible

  • filter(id:) Extension method

    Creates a request filtered by primary key.

    // SELECT * FROM player WHERE id = 1
    let request = Player.filter(id: 1)
    

    Declaration

    Swift

    public static func filter(id: ID) -> QueryInterfaceRequest<Self>

    Parameters

    id

    A primary key

  • filter(ids:) Extension method

    Creates a request filtered by primary key.

    // SELECT * FROM player WHERE id IN (1, 2, 3)
    let request = Player.filter(ids: [1, 2, 3])
    

    Declaration

    Swift

    public static func filter<Collection>(ids: Collection)
    -> QueryInterfaceRequest<Self>
    where Collection: Swift.Collection, Collection.Element == ID

    Parameters

    ids

    A collection of primary keys

  • selectID() Extension method

    Creates a request which selects the primary key.

    // SELECT id FROM player
    let request = try Player.selectID()
    

    Declaration

    Swift

    public static func selectID() -> QueryInterfaceRequest<ID>

Available where Self: Identifiable, ID: _OptionalProtocol, ID.Wrapped: DatabaseValueConvertible

  • filter(id:) Extension method

    Creates a request filtered by primary key.

    // SELECT * FROM player WHERE id = 1
    let request = Player.filter(id: 1)
    

    Declaration

    Swift

    public static func filter(id: ID.Wrapped) -> QueryInterfaceRequest<Self>

    Parameters

    id

    A primary key

  • filter(ids:) Extension method

    Creates a request filtered by primary key.

    // SELECT * FROM player WHERE id IN (1, 2, 3)
    let request = Player.filter(ids: [1, 2, 3])
    

    Declaration

    Swift

    public static func filter<Collection>(ids: Collection)
    -> QueryInterfaceRequest<Self>
    where Collection: Swift.Collection, Collection.Element == ID.Wrapped

    Parameters

    ids

    A collection of primary keys

  • selectID() Extension method

    Creates a request which selects the primary key.

    // SELECT id FROM player
    let request = try Player.selectID()
    

    Declaration

    Swift

    public static func selectID() -> QueryInterfaceRequest<ID.Wrapped>

Available where Self: Identifiable, ID: DatabaseValueConvertible

  • exists(_:id:) Extension method

    Returns whether a row exists for this primary key.

    try Player.deleteOne(db, id: 123)
    try Country.deleteOne(db, id: "FR")
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    try Document.deleteOne(db, id: 1)
    

    Declaration

    Swift

    public static func exists(_ db: Database, id: ID) throws -> Bool

    Parameters

    db

    A database connection.

    id

    A primary key value.

    Return Value

    Whether a row exists for this primary key.

Available where Self: Identifiable, ID: _OptionalProtocol, ID.Wrapped: DatabaseValueConvertible

  • exists(_:id:) Extension method

    Returns whether a row exists for this primary key.

    try Player.deleteOne(db, id: 123)
    try Country.deleteOne(db, id: "FR")
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    try Document.deleteOne(db, id: 1)
    

    Declaration

    Swift

    public static func exists(_ db: Database, id: ID.Wrapped) throws -> Bool

    Parameters

    db

    A database connection.

    id

    A primary key value.

    Return Value

    Whether a row exists for this primary key.

Available where Self: Identifiable, ID: DatabaseValueConvertible

  • deleteAll(_:ids:) Extension method

    Delete records identified by their primary keys; returns the number of deleted rows.

    // DELETE FROM player WHERE id IN (1, 2, 3)
    try Player.deleteAll(db, ids: [1, 2, 3])
    
    // DELETE FROM country WHERE code IN ('FR', 'US', 'DE')
    try Country.deleteAll(db, ids: ["FR", "US", "DE"])
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    // DELETE FROM document WHERE rowid IN (1, 2, 3)
    try Document.deleteAll(db, ids: [1, 2, 3])
    

    Declaration

    Swift

    @discardableResult
    public static func deleteAll<Collection>(_ db: Database, ids: Collection)
    throws -> Int
    where Collection: Swift.Collection, Collection.Element == ID

    Parameters

    db

    A database connection.

    ids

    A collection of primary keys.

    Return Value

    The number of deleted rows

  • deleteOne(_:id:) Extension method

    Delete a record, identified by its primary key; returns whether a database row was deleted.

    // DELETE FROM player WHERE id = 123
    try Player.deleteOne(db, id: 123)
    
    // DELETE FROM country WHERE code = 'FR'
    try Country.deleteOne(db, id: "FR")
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    // DELETE FROM document WHERE rowid = 1
    try Document.deleteOne(db, id: 1)
    

    Declaration

    Swift

    @discardableResult
    public static func deleteOne(_ db: Database, id: ID) throws -> Bool

    Parameters

    db

    A database connection.

    id

    A primary key value.

    Return Value

    Whether a database row was deleted.

Available where Self: Identifiable, ID: _OptionalProtocol, ID.Wrapped: DatabaseValueConvertible

  • deleteAll(_:ids:) Extension method

    Delete records identified by their primary keys; returns the number of deleted rows.

    // DELETE FROM player WHERE id IN (1, 2, 3)
    try Player.deleteAll(db, ids: [1, 2, 3])
    
    // DELETE FROM country WHERE code IN ('FR', 'US', 'DE')
    try Country.deleteAll(db, ids: ["FR", "US", "DE"])
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    // DELETE FROM document WHERE rowid IN (1, 2, 3)
    try Document.deleteAll(db, ids: [1, 2, 3])
    

    Declaration

    Swift

    @discardableResult
    public static func deleteAll<Collection>(_ db: Database, ids: Collection)
    throws -> Int
    where Collection: Swift.Collection, Collection.Element == ID.Wrapped

    Parameters

    db

    A database connection.

    ids

    A collection of primary keys.

    Return Value

    The number of deleted rows

  • deleteOne(_:id:) Extension method

    Delete a record, identified by its primary key; returns whether a database row was deleted.

    // DELETE FROM player WHERE id = 123
    try Player.deleteOne(db, id: 123)
    
    // DELETE FROM country WHERE code = 'FR'
    try Country.deleteOne(db, id: "FR")
    

    When the table has no explicit primary key, GRDB uses the hidden “rowid” column:

    // DELETE FROM document WHERE rowid = 1
    try Document.deleteOne(db, id: 1)
    

    Declaration

    Swift

    @discardableResult
    public static func deleteOne(_ db: Database, id: ID.Wrapped) throws -> Bool

    Parameters

    db

    A database connection.

    id

    A primary key value.

    Return Value

    Whether a database row was deleted.