TableRecord

The name of the database table used to build requests.

struct Player : TableRecord {
    static var databaseTableName = "player"
}

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

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)

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.

Creates a Belongs To association between Self and the destination type.

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

Creates a Has many association between Self and the destination type.

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

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

Creates a Has one association between Self and the destination type.

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

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

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]

Creates a request that prefetches an association.

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.

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.

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.

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.

Creates a request with aggregates appended to the selection.

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

Creates a request with aggregates appended to the selection.

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

Creates a request with the provided aggregate predicate.

// SELECT player.*
// FROM player LEFT JOIN book ...
// HAVING COUNT(DISTINCT book.rowid) = 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.

Creates a request which fetches all records.

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

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.

Creates a request which fetches no record.

Creates a request which selects selection.

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

Creates a request which selects selection.

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

Creates a request which selects sql.

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

Creates a request which selects an SQL literal.

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

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

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

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

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

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

Creates a request which appends selection.

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

Creates a request which appends selection.

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

Creates a request with the provided predicate.

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

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.

Creates a request with the provided primary key predicate.

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

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.

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

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.

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.

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.

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.

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.

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"])

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.

Creates a request with the provided predicate.

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

With Swift 5, you can safely embed raw values in your SQL queries, without any risk of syntax errors or SQL injection:

let request = Player.filter(literal: "name = \("O'Brien"))

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.

Creates a request sorted according to the provided orderings.

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

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.

Creates a request sorted according to the provided orderings.

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

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.

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

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.

Creates a request sorted according to sql.

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

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.

Creates a request sorted according to an SQL literal.

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

With Swift 5, you can safely embed raw values in your SQL queries, without any risk of syntax errors or SQL injection:

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

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.

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

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

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.

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")])

The number of records.

The selection as an SQL String.

For example:

struct Player: TableRecord {
    static let databaseTableName = "player"
}

// SELECT "player".* FROM player
let sql = "SELECT \(Player.selectionSQL()) FROM player"

// SELECT "p".* FROM player AS p
let sql = "SELECT \(Player.selectionSQL(alias: "p")) FROM player p"

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

Creates a ValueObservation which observes the record table, and notifies its count whenever it is modified by a database transaction.

For example:

let observation = Player.observationForCount()

let observer = try observation.start(in: dbQueue) { count: Int in
    print("Number of players has changed")
}

The returned observation has the default configuration:

• When started with the start(in:onError:onChange:) method, a fresh value is immediately notified on the main queue.
• Upon subsequent database changes, fresh values are notified on the main queue.

• The observation lasts until the observer returned by start is deallocated.

Creates a ValueObservation which observes the record table, and notifies fresh records whenever the request is modified by a database transaction.

For example:

let observation = Player.observationForAll()

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

The returned observation has the default configuration:

• When started with the start(in:onError:onChange:) method, a fresh value is immediately notified on the main queue.
• Upon subsequent database changes, fresh values are notified on the main queue.

• The observation lasts until the observer returned by start is deallocated.

Creates a ValueObservation which observes the table record, and notifies a fresh record whenever the request is modified by a database transaction.

For example:

let observation = Player.observationForFirst()

let observer = try observation.start(in: dbQueue) { player: Player? in
    print("Player has changed")
}

The returned observation has the default configuration:

• When started with the start(in:onError:onChange:) method, a fresh value is immediately notified on the main queue.
• Upon subsequent database changes, fresh values are notified on the main queue.

• The observation lasts until the observer returned by start is deallocated.