QueryInterfaceRequest

Creates a request which selects selection promise.

// SELECT id, email FROM player
var request = Player.all()
request = request.select { db in [Column("id"), Column("email")] }

Any previous selection is replaced:

// SELECT email FROM player
request
    .select { db in [Column("id")] }
    .select { db in [Column("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.all().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.all().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.

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.all().select(
    literal: "IFNULL(name, \(defaultName))",
    as: String.self)
let name: String? = try request.fetchOne(db)

Creates a request which selects the primary key.

All primary keys are supported:

// SELECT id FROM player WHERE ...
let request = try Player.filter(...).selectPrimaryKey(as: Int64.self)

// SELECT code FROM country WHERE ...
let request = try Country.filter(...).selectPrimaryKey(as: String.self)

// SELECT citizenId, countryCode FROM citizenship WHERE ...
let request = try Citizenship.filter(...).selectPrimaryKey(as: Row.self)

Creates a request which appends selection promise.

// SELECT id, email, name FROM player
var request = Player.all()
request = request
    .select([Column("id"), Column("email")])
    .annotated(with: { db in [Column("name")] })

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

// SELECT * FROM player WHERE 1
var request = Player.all()
request = request.filter { db in true }

Creates a request with the provided orderings promise.

// SELECT * FROM player ORDER BY name
var request = Player.all()
request = request.order { _ in [Column("name")] }

Any previous ordering is replaced:

// SELECT * FROM player ORDER BY name
request
    .order{ _ in [Column("email")] }
    .reversed()
    .order{ _ in [Column("name")] }

Creates a request that reverses applied orderings.

// SELECT * FROM player ORDER BY name DESC
var request = Player.all().order(Column("name"))
request = request.reversed()

If no ordering was applied, the returned request is identical.

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

Creates a request without any ordering.

// SELECT * FROM player
var request = Player.all().order(Column("name"))
request = request.unordered()

Creates a request grouped according to expressions promise.

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

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
    .all()
    .aliased(playerAlias)
    .including(required: Player.team.filter(Column("avgScore") < playerAlias[Column("score")])

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

// SELECT * FROM player LIMIT 10 OFFSET 20
var request = Player.all()
request = request.limit(10, offset: 20)

Any previous limit is replaced.

Creates a request bound to type RowDecoder.

The returned request can fetch if the type RowDecoder is fetchable (Row, value, record).

// Int?
let maxScore = try Player
    .select(max(scoreColumn))
    .asRequest(of: Int.self)    // <--
    .fetchOne(db)

Returns true if the request matches no row in the database.

try Player.filter(Column("name") == "Arthur").isEmpty(db)

Deletes matching rows, and returns the number of deleted rows.

Returns a DELETE ... RETURNING ... prepared statement.

For example:

// Delete all players and return their names
// DELETE FROM player RETURNING name
let request = Player.all()
let statement = try request.deleteAndFetchStatement(db, selection: [Column("name")])
let deletedNames = try String.fetchSet(statement)

Returns a cursor over a DELETE ... RETURNING ... statement.

For example:

// Fetch all deleted players
// DELETE FROM player RETURNING *
let request = Player.all()
let cursor = try request.deleteAndFetchCursor(db)
let deletedPlayers = try Array(cursor) // [Player]

Executes a DELETE ... RETURNING ... statement and returns the deleted records.

For example:

// Fetch all deleted players
// DELETE FROM player RETURNING *
let request = Player.all()
let deletedPlayers = try request.deleteAndFetchAll(db)

Executes a DELETE ... RETURNING ... statement and returns the deleted records.

For example:

// Fetch all deleted players
// DELETE FROM player RETURNING *
let request = Player.all()
let deletedPlayers = try request.deleteAndFetchSet(db)

Updates matching rows, and returns the number of updated rows.

For example:

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

Returns an UPDATE ... RETURNING ... prepared statement.

For example:

// Fetch all updated scores
// UPDATE player SET score = score + 10 RETURNING score
let request = Player.all()
let statement = try request.updateAndFetchStatement(
    db, [Column("score") += 10],
    select: [Column("score")])
let updatedScores = try Int.fetchAll(statement)

Returns a cursor over an UPDATE ... RETURNING ... statement.

For example:

// Fetch all updated players
// UPDATE player SET score = score + 10 RETURNING *
let request = Player.all()
let cursor = try request.updateAndFetchCursor(db, [Column("score") += 10])
let updatedPlayers = try Array(cursor) // [Player]

Execute an UPDATE ... RETURNING ... statement and returns the updated records.

For example:

// Fetch all updated players
// UPDATE player SET score = score + 10 RETURNING *
let request = Player.all()
let updatedPlayers = try request.updateAndFetchAll(db, [Column("score") += 10])

Execute an UPDATE ... RETURNING ... statement and returns the updated records.

For example:

// Fetch all updated players
// UPDATE player SET score = score + 10 RETURNING *
let request = Player.all()
let updatedPlayers = try request.updateAndFetchSet(db, [Column("score") += 10])