API Reference

This page provides a quick reference to the various SQL functions and operators available in SQLite.swift. For detailed usage, please refer to the guides.

Core SQL Functions

SQLite.swift surfaces many of SQLite's core functions in a type-safe way.

  • abs(X): expression.absoluteValue
  • round(X, Y): expression.round(precision)
  • random(): Expression<Int>.random()
  • length(X): expression.length
  • lower(X): expression.lowercaseString
  • upper(X): expression.uppercaseString
  • ltrim(X, Y): expression.ltrim(characters)
  • rtrim(X, Y): expression.rtrim(characters)
  • trim(X, Y): expression.trim(characters)
  • replace(X, Y, Z): expression.replace(pattern, with: replacement)
  • substr(X, Y, Z): expression.substring(location, length:) or expression[range]
  • ifnull(X, Y): optionalExpression ?? fallbackExpression
  • cast(X AS T): cast(expression)

Aggregate Functions

These functions operate on a set of rows and return a single value.

  • avg(X): expression.average
  • count(X): expression.count
  • count(*): count(*) or query.count
  • max(X): expression.max
  • min(X): expression.min
  • sum(X): expression.sum
  • total(X): expression.total

Window Functions

Window functions perform calculations across a set of rows related to the current row.

  • row_number() OVER ...: rowNumber(orderBy: ...)
  • rank() OVER ...: rank(orderBy: ...)
  • dense_rank() OVER ...: denseRank(orderBy: ...)
  • percent_rank() OVER ...: percentRank(orderBy: ...)
  • cume_dist() OVER ...: cumeDist(orderBy: ...)
  • ntile(N) OVER ...: ntile(N, orderBy: ...)
  • lag(expr, offset, default) OVER ...: expression.lag(offset:default:orderBy:)
  • lead(expr, offset, default) OVER ...: expression.lead(offset:default:orderBy:)
  • first_value(expr) OVER ...: expression.firstValue(orderBy:)
  • last_value(expr) OVER ...: expression.lastValue(orderBy:)
  • nth_value(expr, N) OVER ...: expression.value(N, orderBy:)

Date & Time Functions

SQLite's date and time functions are available via the DateFunctions class or on Date and Expression<Date> objects.

  • date(...): DateFunctions.date(...) or dateExpression.date
  • time(...): DateFunctions.time(...) or dateExpression.time
  • datetime(...): DateFunctions.datetime(...) or dateExpression.datetime
  • julianday(...): DateFunctions.julianday(...) or dateExpression.julianday
  • strftime(...): DateFunctions.strftime(...)

Operators

Common SQL operators are overloaded in Swift.

Infix Operators

  • Comparison: ==, !=, >, >=, <, <=
  • Logical: && (AND), || (OR)
  • Arithmetic: +, -, *, /
  • Bitwise: %, <<, >>, &, |, ^
  • String Concatenation: +
  • Pattern Matching: ~= for ranges (BETWEEN)
  • Null Coalescing: ?? (ifnull)

Prefix Operators

  • Logical: ! (NOT)
  • Arithmetic: - (negation)
  • Bitwise: ~ (complement)

Custom Functions & Collations

  • Custom Functions: db.createFunction(...) allows you to register custom Swift code as a callable SQL function.
  • Custom Aggregations: db.createAggregation(...) allows you to register custom aggregate functions.
  • Custom Collations: db.createCollation(...) allows you to define custom string comparison logic for sorting and ordering.