Functions

The following functions are available globally.

  • Returns an array of row adapters that split a row according to the input number of columns.

    For example:

    let sql = "SELECT 1, 2,3,4, 5,6, 7,8"
    //               <.><. . .><. .><. .>
    let adapters = splittingRowAdapters([1, 3, 2])
    let adapter = ScopeAdapter([
        "a": adapters[0],
        "b": adapters[1],
        "c": adapters[2],
        "d": adapters[3]])
    let row = try Row.fetchOne(db, sql: sql, adapter: adapter)
    row.scopes["a"] // [1]
    row.scopes["b"] // [2, 3, 4]
    row.scopes["c"] // [5, 6]
    row.scopes["d"] // [7, 8]
    

    Declaration

    Swift

    public func splittingRowAdapters(columnCounts: [Int]) -> [RowAdapter]

Logical Operators (AND, OR, NOT)

Egality and Identity Operators (=, <>, IS, IS NOT)

Comparison Operators (<, >, <=, >=)

Arithmetic Operators (+, -, *, /)

IFNULL(…)

ColumnAssignment

  • Creates an assignment to a value.

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

    Declaration

    Swift

    public func <- (column: ColumnExpression, value: SQLExpressible) -> ColumnAssignment
  • Creates an assignment that adds a value

    Column("score") += 1
    Column("score") += Column("bonus")
    
    try dbQueue.write { db in
        // UPDATE player SET score = score + 1
        try Player.updateAll(db, Column("score") += 1)
    }
    

    Declaration

    Swift

    public func += (column: ColumnExpression, value: SQLExpressible) -> ColumnAssignment
  • Creates an assignment that subtracts a value

    Column("score") -= 1
    Column("score") -= Column("bonus")
    
    try dbQueue.write { db in
        // UPDATE player SET score = score - 1
        try Player.updateAll(db, Column("score") -= 1)
    }
    

    Declaration

    Swift

    public func -= (column: ColumnExpression, value: SQLExpressible) -> ColumnAssignment
  • Creates an assignment that multiplies by a value

    Column("score") *= 2
    Column("score") *= Column("factor")
    
    try dbQueue.write { db in
        // UPDATE player SET score = score * 2
        try Player.updateAll(db, Column("score") *= 2)
    }
    

    Declaration

    Swift

    public func *= (column: ColumnExpression, value: SQLExpressible) -> ColumnAssignment
  • Creates an assignment that divides by a value

    Column("score") /= 2
    Column("score") /= Column("factor")
    
    try dbQueue.write { db in
        // UPDATE player SET score = score / 2
        try Player.updateAll(db, Column("score") /= 2)
    }
    

    Declaration

    Swift

    public func /= (column: ColumnExpression, value: SQLExpressible) -> ColumnAssignment

ABS(…)

  • Returns an expression that evaluates the ABS SQL function.

    // ABS(amount)
    abs(Column("amount"))
    

    Declaration

    Swift

    public func abs(_ value: SQLSpecificExpressible) -> SQLExpression

AVG(…)

  • Returns an expression that evaluates the AVG SQL function.

    // AVG(length)
    average(Column("length"))
    

    Declaration

    Swift

    public func average(_ value: SQLSpecificExpressible) -> SQLExpression

COUNT(…)

  • Returns an expression that evaluates the COUNT SQL function.

    // COUNT(email)
    count(Column("email"))
    

    Declaration

    Swift

    public func count(_ counted: SQLSelectable) -> SQLExpression

COUNT(DISTINCT …)

  • Returns an expression that evaluates the COUNT(DISTINCT) SQL function.

    // COUNT(DISTINCT email)
    count(distinct: Column("email"))
    

    Declaration

    Swift

    public func count(distinct value: SQLSpecificExpressible) -> SQLExpression

IFNULL(…)

  • Returns an expression that evaluates the IFNULL SQL function.

    // IFNULL(name, 'Anonymous')
    Column("name") ?? "Anonymous"
    

    Declaration

    Swift

    public func ?? (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression

LENGTH(…)

  • Returns an expression that evaluates the LENGTH SQL function.

    // LENGTH(name)
    length(Column("name"))
    

    Declaration

    Swift

    public func length(_ value: SQLSpecificExpressible) -> SQLExpression

MAX(…)

  • Returns an expression that evaluates the MAX SQL function.

    // MAX(score)
    max(Column("score"))
    

    Declaration

    Swift

    public func max(_ value: SQLSpecificExpressible) -> SQLExpression

MIN(…)

  • Returns an expression that evaluates the MIN SQL function.

    // MIN(score)
    min(Column("score"))
    

    Declaration

    Swift

    public func min(_ value: SQLSpecificExpressible) -> SQLExpression

SUM(…)

  • Returns an expression that evaluates the SUM SQL function.

    // SUM(amount)
    sum(Column("amount"))
    

    Declaration

    Swift

    public func sum(_ value: SQLSpecificExpressible) -> SQLExpression

Egality and Identity Operators (=, <>, IS, IS NOT)

  • An SQL expression that compares two expressions with the = SQL operator.

    // name = 'Arthur'
    Column("name") == "Arthur"
    

    When the right operand is nil, IS NULL is used instead.

    // name IS NULL
    Column("name") == nil
    

    Declaration

    Swift

    public func == (lhs: SQLSpecificExpressible, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that compares two expressions with the = SQL operator.

    // name = 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) == "Arthur"
    

    When the right operand is nil, IS NULL is used instead.

    // name IS NULL
    Column("name").collating(.nocase) == nil
    

    Declaration

    Swift

    public func == (lhs: SQLCollatedExpression, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that checks the boolean value of an expression.

    The comparison is done with the built-in boolean evaluation of SQLite:

    // validated
    Column("validated") == true
    
    // NOT validated
    Column("validated") == false
    

    Declaration

    Swift

    public func == (lhs: SQLSpecificExpressible, rhs: Bool) -> SQLExpression
  • An SQL expression that compares two expressions with the = SQL operator.

    // 'Arthur' = name
    "Arthur" == Column("name")
    

    When the left operand is nil, IS NULL is used instead.

    // name IS NULL
    nil == Column("name")
    

    Declaration

    Swift

    public func == (lhs: SQLExpressible?, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the = SQL operator.

    // 'Arthur' = name COLLATE NOCASE
    "Arthur" == Column("name").collating(.nocase)
    

    When the left operand is nil, IS NULL is used instead.

    // name IS NULL
    nil == Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func == (lhs: SQLExpressible?, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that checks the boolean value of an expression.

    The comparison is done with the built-in boolean evaluation of SQLite:

    // validated
    true == Column("validated")
    
    // NOT validated
    false == Column("validated")
    

    Declaration

    Swift

    public func == (lhs: Bool, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the = SQL operator.

    // email = login
    Column("email") == Column("login")
    

    Declaration

    Swift

    public func == (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <> SQL operator.

    // name <> 'Arthur'
    Column("name") != "Arthur"
    

    When the right operand is nil, IS NOT NULL is used instead.

    // name IS NOT NULL
    Column("name") != nil
    

    Declaration

    Swift

    public func != (lhs: SQLSpecificExpressible, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that compares two expressions with the <> SQL operator.

    // name <> 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) != "Arthur"
    

    When the right operand is nil, IS NOT NULL is used instead.

    // name IS NOT NULL
    Column("name").collating(.nocase) != nil
    

    Declaration

    Swift

    public func != (lhs: SQLCollatedExpression, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that checks the boolean value of an expression.

    The comparison is done with the built-in boolean evaluation of SQLite:

    // NOT validated
    Column("validated") != true
    
    // validated
    Column("validated") != false
    

    Declaration

    Swift

    public func != (lhs: SQLSpecificExpressible, rhs: Bool) -> SQLExpression
  • An SQL expression that compares two expressions with the <> SQL operator.

    // 'Arthur' <> name
    "Arthur" != Column("name")
    

    When the left operand is nil, IS NOT NULL is used instead.

    // name IS NOT NULL
    nil != Column("name")
    

    Declaration

    Swift

    public func != (lhs: SQLExpressible?, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <> SQL operator.

    // 'Arthur' <> name COLLATE NOCASE
    "Arthur" != Column("name").collating(.nocase)
    

    When the left operand is nil, IS NOT NULL is used instead.

    // name IS NOT NULL
    nil != Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func != (lhs: SQLExpressible?, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that checks the boolean value of an expression.

    The comparison is done with the built-in boolean evaluation of SQLite:

    // NOT validated
    true != Column("validated")
    
    // validated
    false != Column("validated")
    

    Declaration

    Swift

    public func != (lhs: Bool, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <> SQL operator.

    // email <> login
    Column("email") != Column("login")
    

    Declaration

    Swift

    public func != (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the IS SQL operator.

    // name IS 'Arthur'
    Column("name") === "Arthur"
    

    Declaration

    Swift

    public func === (lhs: SQLSpecificExpressible, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that compares two expressions with the IS SQL operator.

    // name IS 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) === "Arthur"
    

    Declaration

    Swift

    public func === (lhs: SQLCollatedExpression, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that compares two expressions with the IS SQL operator.

    // name IS 'Arthur'
    "Arthur" === Column("name")
    

    Declaration

    Swift

    public func === (lhs: SQLExpressible?, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the IS SQL operator.

    // name IS 'Arthur' COLLATE NOCASE
    "Arthur" === Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func === (lhs: SQLExpressible?, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that compares two expressions with the IS SQL operator.

    // email IS login
    Column("email") === Column("login")
    

    Declaration

    Swift

    public func === (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the IS NOT SQL operator.

    // name IS NOT 'Arthur'
    Column("name") !== "Arthur"
    

    Declaration

    Swift

    public func !== (lhs: SQLSpecificExpressible, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that compares two expressions with the IS NOT SQL operator.

    // name IS NOT 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) !== "Arthur"
    

    Declaration

    Swift

    public func !== (lhs: SQLCollatedExpression, rhs: SQLExpressible?) -> SQLExpression
  • An SQL expression that compares two expressions with the IS NOT SQL operator.

    // name IS NOT 'Arthur'
    "Arthur" !== Column("name")
    

    Declaration

    Swift

    public func !== (lhs: SQLExpressible?, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the IS NOT SQL operator.

    // name IS NOT 'Arthur' COLLATE NOCASE
    "Arthur" !== Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func !== (lhs: SQLExpressible?, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that compares two expressions with the IS NOT SQL operator.

    // email IS NOT login
    Column("email") !== Column("login")
    

    Declaration

    Swift

    public func !== (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression

Comparison Operators (<, >, <=, >=)

  • An SQL expression that compares two expressions with the < SQL operator.

    // score < 18
    Column("score") < 18
    

    Declaration

    Swift

    public func < (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the < SQL operator.

    // name < 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) < "Arthur"
    

    Declaration

    Swift

    public func < (lhs: SQLCollatedExpression, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the < SQL operator.

    // 18 < score
    18 < Column("score")
    

    Declaration

    Swift

    public func < (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the < SQL operator.

    // 'Arthur' < name COLLATE NOCASE
    "Arthur" < Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func < (lhs: SQLExpressible, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that compares two expressions with the < SQL operator.

    // width < height
    Column("width") < Column("height")
    

    Declaration

    Swift

    public func < (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <= SQL operator.

    // score <= 18
    Column("score") <= 18
    

    Declaration

    Swift

    public func <= (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <= SQL operator.

    // name <= 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) <= "Arthur"
    

    Declaration

    Swift

    public func <= (lhs: SQLCollatedExpression, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <= SQL operator.

    // 18 <= score
    18 <= Column("score")
    

    Declaration

    Swift

    public func <= (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the <= SQL operator.

    // 'Arthur' <= name COLLATE NOCASE
    "Arthur" <= Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func <= (lhs: SQLExpressible, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that compares two expressions with the <= SQL operator.

    // width <= height
    Column("width") <= Column("height")
    

    Declaration

    Swift

    public func <= (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the > SQL operator.

    // score > 18
    Column("score") > 18
    

    Declaration

    Swift

    public func > (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the > SQL operator.

    // name > 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) > "Arthur"
    

    Declaration

    Swift

    public func > (lhs: SQLCollatedExpression, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the > SQL operator.

    // 18 > score
    18 > Column("score")
    

    Declaration

    Swift

    public func > (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the > SQL operator.

    // 'Arthur' > name COLLATE NOCASE
    "Arthur" > Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func > (lhs: SQLExpressible, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that compares two expressions with the > SQL operator.

    // width > height
    Column("width") > Column("height")
    

    Declaration

    Swift

    public func > (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the >= SQL operator.

    // score >= 18
    Column("score") >= 18
    

    Declaration

    Swift

    public func >= (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the >= SQL operator.

    // name >= 'Arthur' COLLATE NOCASE
    Column("name").collating(.nocase) >= "Arthur"
    

    Declaration

    Swift

    public func >= (lhs: SQLCollatedExpression, rhs: SQLExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the >= SQL operator.

    // 18 >= score
    18 >= Column("score")
    

    Declaration

    Swift

    public func >= (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL expression that compares two expressions with the >= SQL operator.

    // 'Arthur' >= name COLLATE NOCASE
    "Arthur" >= Column("name").collating(.nocase)
    

    Declaration

    Swift

    public func >= (lhs: SQLExpressible, rhs: SQLCollatedExpression) -> SQLExpression
  • An SQL expression that compares two expressions with the >= SQL operator.

    // width >= height
    Column("width") >= Column("height")
    

    Declaration

    Swift

    public func >= (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression

Arithmetic Operators (+, -, *, /)

  • An SQL arithmetic multiplication.

    // width * 2
    Column("width") * 2
    

    Declaration

    Swift

    public func * (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL arithmetic multiplication.

    // 2 * width
    2 * Column("width")
    

    Declaration

    Swift

    public func * (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic multiplication.

    // width * height
    Column("width") * Column("height")
    

    Declaration

    Swift

    public func * (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic division.

    // width / 2
    Column("width") / 2
    

    Declaration

    Swift

    public func / (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL arithmetic division.

    // 2 / width
    2 / Column("width")
    

    Declaration

    Swift

    public func / (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic division.

    // width / height
    Column("width") / Column("height")
    

    Declaration

    Swift

    public func / (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic addition.

    // width + 2
    Column("width") + 2
    

    Declaration

    Swift

    public func + (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL arithmetic addition.

    // 2 + width
    2 + Column("width")
    

    Declaration

    Swift

    public func + (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic addition.

    // width + height
    Column("width") + Column("height")
    

    Declaration

    Swift

    public func + (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • A negated SQL arithmetic expression.

    // -width
    -Column("width")
    

    Declaration

    Swift

    public prefix func - (value: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic substraction.

    // width - 2
    Column("width") - 2
    

    Declaration

    Swift

    public func - (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • An SQL arithmetic substraction.

    // 2 - width
    2 - Column("width")
    

    Declaration

    Swift

    public func - (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • An SQL arithmetic substraction.

    // width - height
    Column("width") - Column("height")
    

    Declaration

    Swift

    public func - (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression

Logical Operators (AND, OR, NOT)

  • A logical SQL expression with the AND SQL operator.

    // favorite AND 0
    Column("favorite") && false
    

    Declaration

    Swift

    public func && (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • A logical SQL expression with the AND SQL operator.

    // 0 AND favorite
    false && Column("favorite")
    

    Declaration

    Swift

    public func && (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • A logical SQL expression with the AND SQL operator.

    // email IS NOT NULL AND favorite
    Column("email") != nil && Column("favorite")
    

    Declaration

    Swift

    public func && (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • A logical SQL expression with the OR SQL operator.

    // favorite OR 1
    Column("favorite") || true
    

    Declaration

    Swift

    public func || (lhs: SQLSpecificExpressible, rhs: SQLExpressible) -> SQLExpression
  • A logical SQL expression with the OR SQL operator.

    // 0 OR favorite
    true || Column("favorite")
    

    Declaration

    Swift

    public func || (lhs: SQLExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • A logical SQL expression with the OR SQL operator.

    // email IS NULL OR hidden
    Column("email") == nil || Column("hidden")
    

    Declaration

    Swift

    public func || (lhs: SQLSpecificExpressible, rhs: SQLSpecificExpressible) -> SQLExpression
  • A negated logical SQL expression with the NOT SQL operator.

    // NOT hidden
    !Column("hidden")
    

    Some expressions may be negated with specific SQL operators:

    // id NOT BETWEEN 1 AND 10
    !((1...10).contains(Column("id")))
    

    Declaration

    Swift

    public prefix func ! (value: SQLSpecificExpressible) -> SQLExpression

Public

  • Return as many question marks separated with commas as the count argument.

    databaseQuestionMarks(count: 3) // "?,?,?"
    

    Declaration

    Swift

    @inlinable
    public func databaseQuestionMarks(count: Int) -> String