Basic Usage

Overview

Query builders make up the primary API surface for SQLKit. A query builder is an object associated with a database used to build and execute a query, as shown in the following example:

/// A simple data model 
struct Planet: Codable {
    let id: Int
    let name: String
}

/// Database connection objects are vended by SQLKit drivers; the details
/// differ from driver to driver.
let database: any SQLDatabase = ...

/// This value can come from user input, such a query parameter.
let planetName: String = ...

let planets = try await database
    .select()
    .columns("id", "name")
    .from("planets")
    .where("name", .equal, planetName)
    .all(decoding: Planet.self)

The actual query executed by this example depends on the driver used to get the database object. The PostgreSQL driver generates this query:

SELECT "id", "name" FROM "planets" WHERE "name" = $1

… and the SQLite driver’s output is very similar:

SELECT "id", "name" FROM "planets" WHERE "name" = ?1

… whereas the MySQL driver’s output is less so:

SELECT `id`, `name` FROM `planets` WHERE `name` = ?

Databases, Expressions, and Builders

Instances of SQLDatabase are capable of executing arbitrary SQLExpressions:

let db: any SQLDatabase = // obtain a database from an SQLKit driver
let query = db
    .select()
    .column(SQLLiteral.string("a"))
    .query 

try await db.execute(
    sql: query,
    onRow: { (row: any SQLRow) in
        // ...
    }
)

The SQLExpression protocol provides a common interface for transforming an arbitrary set of syntactical building blocks into a string of SQL code. A comprehensive set of SQL building blocks for SQL syntax is provided, along with numerous expressions representing composed clauses, and even complete SQL queries. Expressions are serialized to a combination of a raw string of SQL text and an array of zero or more bound parameter values.

Here is an example of constructing a SELECT query using the SQLSelect expression type, along with several syntactical building blocks, directly:

var select = SQLSelect()

select.columns = [
    SQLColumn("column1"),
    SQLColumn("column2", table: "table2"),
]
select.tables = [
    SQLIdentifier("table1")
]
select.joins = [
    SQLJoin(
        method: SQLJoinMethod.inner,
        table: SQLIdentifier("table2"),
        expression: SQLBinaryExpression(
            SQLColumn("column1", table: "table1"),
            .equal,
            SQLColumn("column2", table: "table2")
        )
    )
]
select.predicate = SQLBinaryExpression(
    SQLBinaryExpression(SQLColumn("column1"), .equal, SQLBind("value")),
    .and,
    SQLBinaryExpression(SQLColumn("column2"), .is, SQLLiteral.null)
)

When serialized against a database using the PostgreSQL dialect, the resulting query looks like this (whitespace has been added for readability):

SELECT "column1", "table2"."column2"
FROM "table1"
INNER JOIN "table2" ON "table1"."column1" = "table2"."column2"
WHERE "column1" = $1 AND "column2" IS NULL

Of course, this is an awful lot of code to achieve such a relatively straightforward result, which is why SQLKit provides query builders.

Rows

For query builders that support returning results (e.g. any builder conforming to the SQLQueryFetcher protocol), there are additional methods for handling the database output:

• all(): Returns an array of rows.

• first(): Returns an optional row.

• run(_:): Accepts a closure that handles rows as they are returned.

Each of these methods provides one or more SQLRows. SQLRow is a protocol providing methods for accessing column values:

let row: any SQLRow
let name = try row.decode(column: "name", as: String.self)
print(name) // String

Codable

SQLRow also supports decoding Codable models directly:

struct Planet: Codable {
    var name: String
}

let planet = try row.decode(model: Planet.self)

Query builders that support returning results have convenience methods for automatically decoding models.

let planets: [Planet] = try await db.select()
    ...
    .all(decoding: Planet.self)

Select

The select() method creates a SELECT query builder:

let planets: [any SQLRow] = try await db.select()
    .columns("id", "name")
    .from("planets")
    .where("name", .equal, "Earth")
    .all()

This code generates the following SQL when used with the PostgresKit driver:

SELECT "id", "name" FROM "planets" WHERE "name" = $1 -- bindings: ["Earth"]

Notice that Encodable values are automatically bound as parameters instead of being serialized directly to the query.

The select builder includes the following methods (most of which have numerous variations):

• columns() (specify a list of columns and/or expressions to return)

• from() (specify a table to select from)

• join() (specify additional tables and how to relate them to others)

• where() and orWhere() (specify conditions that narrow down the possible results)

• limit() and offset() (specify a limited and/or offsetted range of results to return)

• orderBy() (specify how to sort results before returning them)

• groupBy() (specify columns and/or expressions for aggregating results)

• having() and orHaving() (specify secondary conditions to apply to the results after aggregation)

• distinct() (specify coalescing of duplicate results)

• for() and lockingClause() (specify locking behavior for rows that appear in results)

Conditional expressions provided to where() or having() are joined with AND. Corresponding orWhere() and orHaving() methods join conditions with OR instead.

builder.where("name", .equal, "Earth").orWhere("name", .equal, "Mars")

This code generates the following SQL when used with the MySQL driver:

WHERE `name` = ? OR `name` = ? -- bindings: ["Earth", "Mars"]

where(), orWhere(), having(), and orHaving() also support creating grouped clauses:

builder.where("name", .notEqual, SQLLiteral.null).where {
    $0.where("name", .equal, SQLBind("Milky Way"))
      .orWhere("name", .equal, SQLBind("Andromeda"))
}

This code generates the following SQL when used with the SQLite driver:

WHERE "name" <> NULL AND ("name" = ?1 OR "name" = ?2) -- bindings: ["Milky Way", "Andromeda"]

Insert

The insert(into:) and insert(into:) methods create an INSERT query builder:

try await db.insert(into: "galaxies")
    .columns("id", "name")
    .values(SQLLiteral.default, SQLBind("Milky Way"))
    .values(SQLLiteral.default, SQLBind("Andromeda"))
    .run()

This code generates the following SQL when used with the PostgreSQL driver:

INSERT INTO "galaxies" ("id", "name") VALUES (DEFAULT, $1), (DEFAULT, $2) -- bindings: ["Milky Way", "Andromeda"]

The insert builder also has methods for encoding Codable types as sets of values:

struct Galaxy: Codable {
    var name: String
}

try builder.model(Galaxy(name: "Milky Way"))

This code generates the same SQL as would builder.columns("name").values("Milky Way").

Update

The update(_:) and update(_:) methods create an UPDATE query builder:

try await db.update("planets")
    .set("name", to: "Jupiter")
    .where("name", .equal, "Jupiter")
    .run()

This code generates the following SQL when used with the MySQL driver:

UPDATE `planets` SET `name` = ? WHERE `name` = ? -- bindings: ["Jupiter", "Jupiter"]

The update builder supports the same where() and orWhere() methods as the select builder, via the SQLPredicateBuilder protocol.

Delete

The delete(from:) and delete(from:) methods create a DELETE query builder:

try await db.delete(from: "planets")
    .where("name", .equal, "Jupiter")
    .run()

This code generates the following SQL when used with the SQLite driver:

DELETE FROM "planets" WHERE "name" = ?1 -- bindings: ["Jupiter"]

The delete builder also conforms to SQLPredicateBuilder.

Raw

The raw(_:) method allows passing custom SQL query strings, with support for parameterized bindings and correctly-quoted identifiers:

let planets = try await db.raw("SELECT \(SQLLiteral.all) FROM \(ident: table) WHERE \(ident: name) = \(bind: "planet")")
    .all()

This code generates the following SQL when used with the PostgreSQL driver:

SELECT * FROM "planets" WHERE "name" = $1 -- bindings: ["planet"]

The appendInterpolation(bind:) interpolation should be used for any user input to avoid SQL injection. The appendInterpolation(ident:) interpolation is used to safely specify identifiers such as table and column names.