Skip to content

Instantly share code, notes, and snippets.

@gwynne

gwynne/Model+SQLExpression.swift

Last active April 9, 2025 12:41
Show Gist options
  • Save gwynne/9bc9d04d44cf53dca529b526b7736324 to your computer and use it in GitHub Desktop.
Save gwynne/9bc9d04d44cf53dca529b526b7736324 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Model+SQLExpression.swift
import SQLKit
import FluentKit
/// Read each method's comments in the order they appear (from `fieldKey(for:)` through `sqlColumn(for:)`) for an
/// _EXTREMELY_ detailed breakdown of how the heck this all works.
extension Fields {
/// Returns the singular `FieldKey` corresponding to a specific property of the model.
///
/// Detailed operation:
///
/// 1. We must enforce that the caller pass a key path referring to a property of a Fluent model, and more
/// specifically a property which conforms to `QueryAddressableProperty`. Here's why:
/// 1. First and foremost, the property must conform to the very generically-named `Property` protocol,
/// meaning that it is an instance of one of the Fluent property wrappers. There is an important
/// distinction between "a property which uses one of the property wrappers" and "the property
/// wrapper instance itself"; that distinction is critical for this purpose. The latter is the backing
/// storage synthesized by the compiler whenever a wrapper is used. Fluent's property wrappers always
/// return this storage from the wrapper's `projectedValue` accessor, which means that
/// `model.$foo` - or in the case of a keypath, `\Model.$foo` - refers to that same storage. We need
/// this because it's how Fluent makes a property's `FieldKey` available, even to itself.
/// 2. The `QueryAddressableProperty` protocol indicates that the property may be "addressed" by a
/// query - in other words, the property represents, directly or indirectly, a `QueryableProperty`.
/// In turn, a `QueryableProperty` is a property which directly corresponds to exactly one column in
/// the actual database. A `@Field` is both queryable and query-addressable, because it directly
/// represents a single column. A `@Parent` is query-addressable, but _not_ queryable, because
/// while there is an underlying database column, that column is not the value of the property (the
/// actual parent model)- it's that model's ID. And `@Children`, `@Group`, and so forth are neither
/// queryable nor query-addressable; there is no single column in the database that corresponds to
/// these properties.
/// 2. We now have a keypath pointing to a property that we know can tell us how to refer to a single database
/// column by providing the apporpriate `FieldKey`. To actually _get_ that `FieldKey`, we first have to
/// create an empty instance of the model (a serious design flaw in how Fluent uses keypaths) so that we
/// can access the keypath and retrieve the instance of the property wrapper. The instance we want is _not_
/// (necessarily) the one referenced by the keypath - it's the one that property addresses via the
/// `QueryAddressableProperty` protocol - so we append the accessor defined by that protocol to the
/// keypath.
/// 3. Oops, this didn't yield a `FieldKey` - it yielded an _array_ of `FieldKey`s. This was a design choice
/// made early in Fluent 4's development that was intended to enable accessing properties nested within
/// JSON columns as if they were nested keypaths. This functionality, however, was never actually
/// implemented, and in practice, it is explicitly safe to assume that any array of `FieldKey`s returned
/// by any Fluent accessor always has exactly one element. As such, we subscript the array without
/// bothering to check whether it actually has only one element (or any at all).
///
/// > Note: By placing this method on `Fields` rather the more specific `Model`, we allow using it with, for
/// > example, the `IDValue` type of models which use `@CompositeID`, or the secondary structures referenced
/// > by `@Group` properties.
public static func fieldKey(for keypath: KeyPath<Self, some QueryAddressableProperty>) -> FieldKey {
Self()[keyPath: keypath.appending(path: \.queryablePath)][0]
}
/// Return an `SQLIdentifier` containing the _unqualified_ name of the column corresponding to a specific
/// property of the model.
///
/// Detailed operation (see `fieldKey(for:)` above for steps 1-3):
///
/// 4. A `FieldKey`'s `description` property is explicitly defined to be the canonical representation of the
/// column name in SQL, so we now have the correct column name addressed by the provided property. As a
/// bonus, for `@Parent` etc. properties, this works with a keypath such as `\MyModel.$parent` - despite
/// the fact that in Fluent's API, one would be required to write `\MyModel.$parent.$id`. This is a
/// limitation in Fluent's API caused by the fact that I didn't add `QueryAddressableProperty` to the
/// API until long after the original release of Fluent 4 and updating the query builder APIs appropriately
/// has never been considered worth the risk of problems.
///
/// > Note: By placing this method on `Fields` rather the more specific `Model`, we allow using it with, for
/// > example, the `IDValue` type of models which use `@CompositeID`, or the secondary structures referenced
/// > by `@Group` properties.
public static func sqlIdentifier(for keypath: KeyPath<Self, some QueryAddressableProperty>) -> SQLIdentifier {
.init(Self.fieldKey(for: keypath).description)
}
/// A convenience method for returning the result of ``fieldKey(for:)`` as a string.
///
/// As a general rule, users should try to avoid working with the string names of columns; the `FieldKey` - or
/// better yet, the appropriate `SQLExpression`s (such as returned by ``sqlIdentifier(for:)``) - are always to
/// be preferred when possible. However, we recognize that there are cases when this would result in excessive
/// code verbosity, such as when implementing an API which needs to ensure that a given input is a valid column.
///
/// > Note: Another reason for having this method is that it at least minimally insulates users from the
/// > rather unfortunate fact that the most correct way to get the string representation of a `FieldKey` is to
/// > request its `description` - a behavior which the stdlib would discourage if it could. It can't, because
/// > use of `description` is extremely widespread, but technically one is _supposed_ to invoke conformance to
/// > `CustomStringConvertible` by calling `String(describing:)`, although no one ever does. We don't do so
/// > here because `FieldKey.description` is not a proper `CustomStringConvertible` conformance in the first
/// > place, which makes calling it directly an (oxymoronically) incorrectly correct behavior.
public static func key(for keypath: KeyPath<Self, some QueryAddressableProperty>) -> String {
self.fieldKey(for: keypath).description
}
}
extension Schema {
/// Return an `SQLQualifiedTable` containing the fully qualified name of the table corresponding to this model.
///
/// Detailed operation:
///
/// - Fluent 4, though it did not have this feature at the time of its initial release, eventually gained support
/// (thanks to me) for models whose tables exist in different "spaces" (short for "namespaces". What Fluent calls
/// a "space" corresponds to a schema in PostgreSQL, a database in MySQL, or to an attached database in
/// SQLite - in all three cases the SQL syntax for referring to such a table is identical:
/// `other_space.some_table` (where both names are identifiers and should be quoted accordingly).
/// - Fluent 4 also eventually gained working support for model aliasing (this feature was supposedly included in
/// the initial release, but very few people - not zero, but very few - have ever actually used it in real code,
/// and it spent the first year or two of its existence being either partially or completely broken). Model
/// aliasing, in the form of the `ModelAlias` protocol, is intended to allow joining the same model multiple
/// times in a single query at the Fluent level, without using SQLKit (although in an SQLKit context, not only
/// would use of `ModelAlias` be pointless, it would be actively and significantly harmful for performance).
/// `ModelAlias` is the _only_ Fluent-provided type which conforms to `Schema` without also conforming to `Model`,
/// and the only proximate reason that the `Schema` protocol exists separately from `Model` in the first place.
/// - Thanks to the existence of spaces, we specify a Fluent model's table in SQLKit with `SQLQualifiedTable`, an
/// expression which serves the same purpose for table and space identifiers that `SQLColumn` does for column
/// and table identifiers. To specify a _fully_ qualified column in a space-qualified table, one thus uses an
/// `SQLColumn` whose table _is_ an `SQLQualifiedTable`, e.g.
/// `SQLColumn(column_name, table: SQLQualifiedTable(table_name, space: space_name))`.
/// - In order to fully respect `ModelAlias`es (just in case), when constructing an `SQLQualifiedTable` for a
/// Fluent model, we check whether the model (or more precisely, the `Schema`) has an `alias`. If it does, we
/// ignore the model's space (if any) and use the alias in place of the real table name (which, confusingly, is
/// specified by the `schema` property). Otherwise we use the values of the `schema` and `space` properties
/// as-is.
///
/// > Note: This level of support for even a very obscure and little-used Fluent feature - both by placing this
/// > method on `Schema` rather than `Model` and including the check for the presence of a model alias - is
/// > rather pedantic at best, and arguably unnecessary given that it will never matter to 99.9% of Fluent users
/// > even if these utilities were upstreamed to SQLKit in the first place. We do it anyway because 1) all of this
/// > is intended to have the additional purpose of at least somewhat documenting Fluent's behavior, 2) adding the
/// > feature support has no overhead at runtime (actual _use_ of `ModelAlias` with SQLKit is actively harmful, but
/// > simply _supporting_ its use is not), and 3) it is considered good practice in Swift to place utility
/// > extensions as close to the "root" of the protocol "inheritance" hierarchy as possible - thus we extend
/// > `Schema` rather than `Model`, in the same fashion that one would try to extend `Sequence` rather than
/// `> Collection` when possible.
public static var sqlTable: SQLKit.SQLQualifiedTable {
.init(
self.schemaOrAlias,
space: self.spaceIfNotAliased
)
}
/// Return an `SQLColumn` containing the _fully qualified_ name and table of the column corresponding to a
/// specific property of the model.
///
/// Detailed operation (see `fieldKey(for:)` and `sqlIdentifier(for:)` above for steps 1-4, and `sqlTable` above
/// for additional information):
///
/// 5. There are two kinds of column references in SQL syntax - qualified and unqualified. Many areas of SQL
/// syntax require _unqualified_ column names, such as the column list of an INSERT query, the left-hand side
/// of an assignment in an UPDATE query, the list of columns in a join's USING clause, or the column list of
/// an index or table constraint. However, most of the time SQL allows (and where ambiguity exists, requires)
/// _qualified_ column names, which specify the table to which the column belongs by full name or alias. Even
/// in cases where columns are not ambiguous, it is almost always preferable to fully qualify them regardless,
/// both for clarity and to guard against future changes in database structure which may introduce ambiguity
/// to a previously unambiguous reference. Thus we return an `SQLColumn` which specifies the `sqlTable` of the
/// model as the column's table, guaranteeing a fully-qualified reference. In places where an unqualified
/// reference is needed, `sqlIdentifier(for:)` should be used instead (if no table is specified, an
/// `SQLColumn` behaves identically to an `SQLIdentifier`).
///
/// > Note: As with `sqlTable`, by placing this method on `Schema` rather the more specific `Model`, we allow using
/// > it transparently with `ModelAlias` (as ill-advised as doing so may be).
public static func sqlColumn(for keypath: KeyPath<Self, some QueryAddressableProperty>) -> SQLColumn {
.init(
Self.sqlIdentifier(for: keypath),
table: Self.sqlTable
)
}
}
Raw
SQLAdditionalBinaryOperator.swift
import FluentKit
import SQLKit
/// An expression representing one of several additional binary operators not represented by `SQLBinaryOperator`.
public enum SQLAdditionalBinaryOperator: SQLExpression {
/// The `&` (bitwise `AND`) operator.
case AND
/// The `|` (bitwise `OR`) operator.
case OR
/// The bitwise `XOR` operator (`#` in PostgreSQL, `^` in MySQL).
case XOR
/// The `<<` (bitwise left shift) operator.
case leftShift
/// The `>>` (bitwise right shift) operator.
case rightShift
// See `SQLExpression.serialize(to:)`.
public func serialize(to serializer: inout SQLSerializer) {
switch self {
case .AND: serializer.write("&")
case .OR: serializer.write("|")
case .XOR:
switch serializer.dialect.name {
case "postgresql": serializer.write("#")
case "mysql": serializer.write("^")
default: break // SQLite has no XOR operator
}
case .leftShift: serializer.write("<<")
case .rightShift: serializer.write(">>")
}
}
}
extension SQLExpression {
/// Convenience method for creating an ``SQLAdditionalBinaryOperator`` for the `&` operator.
public static func AND() -> Self where Self == SQLAdditionalBinaryOperator {
.AND
}
/// Convenience method for creating an ``SQLAdditionalBinaryOperator`` for the `|` operator.
public static func OR() -> Self where Self == SQLAdditionalBinaryOperator {
.OR
}
/// Convenience method for creating an ``SQLAdditionalBinaryOperator`` for the `#/^` operator.
public static func XOR() -> Self where Self == SQLAdditionalBinaryOperator {
.XOR
}
/// Convenience method for creating an ``SQLAdditionalBinaryOperator`` for the `<<` operator.
public static func leftShift() -> Self where Self == SQLAdditionalBinaryOperator {
.leftShift
}
/// Convenience method for creating an ``SQLAdditionalBinaryOperator`` for the `>>` operator.
public static func rightShift() -> Self where Self == SQLAdditionalBinaryOperator {
.rightShift
}
}
extension Optional where Wrapped: SQLExpression {
public static func AND() -> Self where Self == SQLAdditionalBinaryOperator? { .some(.AND()) }
public static func OR() -> Self where Self == SQLAdditionalBinaryOperator? { .some(.OR()) }
public static func XOR() -> Self where Self == SQLAdditionalBinaryOperator? { .some(.XOR()) }
public static func leftShift() -> Self where Self == SQLAdditionalBinaryOperator? { .some(.leftShift()) }
public static func rightShift() -> Self where Self == SQLAdditionalBinaryOperator? { .some(.rightShift()) }
}
Raw
SQLBuilderProtocols+ModelKeypaths.swift
import FluentKit
import SQLKit
extension SQLPredicateBuilder {
/// Allow specifying a conjunctive `WHERE` condition using a Fluent model keypath as the left-hand operand.
@discardableResult
public func `where`(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some Encodable & Sendable
) -> Self {
self.where(keypath, op, .bind(rhs))
}
/// Allow specifying a conjunctive `WHERE` condition using two Fluent model keypaths, which may optionally refer
/// to different models, as the operands.
@discardableResult
public func `where`(
_ keypath1: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ keypath2: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self {
self.where(keypath1, op, .column(keypath2))
}
/// Allow specifying a conjunctive `WHERE` condition using a Fluent model keypath as the left-hand operand.
@discardableResult
public func `where`(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some SQLExpression
) -> Self {
self.where(.column(keypath), op, rhs)
}
/// Allow specifying a conjunctive `WHERE` condition using a Fluent model keypath as the left-hand operand.
/// This overload allows more convenient use of custom operators like `ILIKE`.
@discardableResult
public func `where`(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: some SQLExpression,
_ rhs: some SQLExpression
) -> Self {
self.where(.column(keypath), op, rhs)
}
/// Allow specifying a conjunctive `WHERE` condition using a Fluent model keypath having a boolean value as the
/// sole operand. This overload allows more convenient boolean testing.
@discardableResult
public func `where`<Prop: QueryAddressableProperty>(
_ keypath: KeyPath<some Schema, Prop>
) -> Self where Prop.Value == Bool {
self.where(.column(keypath))
}
/// Allow specifying an inclusively disjunctive `WHERE` condition using a Fluent model keypath as the
/// left-hand operand.
@discardableResult
public func orWhere(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some Encodable & Sendable
) -> Self {
self.orWhere(keypath, op, .bind(rhs))
}
/// Allow specifying an inclusively disjunctive `WHERE` condition using two Fluent model keypaths, which may
/// optionally refer to different models, as the operands.
@discardableResult
public func orWhere(
_ keypath1: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ keypath2: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self {
self.orWhere(keypath1, op, .column(keypath2))
}
/// Allow specifying an inclusively disjunctive `WHERE` condition using a Fluent model keypath as the
/// left-hand operand.
@discardableResult
public func orWhere(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some SQLExpression
) -> Self {
self.orWhere(.column(keypath), op, rhs)
}
/// Allow specifying an inclusively disjunctive `WHERE` condition using a Fluent model keypath as the
/// left-hand operand. This overload allows more convenient use of custom operators like `ILIKE`.
@discardableResult
public func orWhere(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: some SQLExpression,
_ rhs: some SQLExpression
) -> Self {
self.orWhere(.column(keypath), op, rhs)
}
/// Allow specifying an inclusively disjunctive `WHERE` condition using a Fluent model keypath having a boolean
/// value as the sole operand. This overload allows more convenient boolean testing.
@discardableResult
public func orWhere<Prop: QueryAddressableProperty>(
_ keypath: KeyPath<some Schema, Prop>
) -> Self where Prop.Value == Bool {
self.orWhere(.column(keypath))
}
}
extension SQLSecondaryPredicateBuilder {
/// Allow specifying a conjunctive `HAVING` condition using a Fluent model keypath as the left-hand operand.
@discardableResult
public func having(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some Encodable & Sendable
) -> Self {
self.having(keypath, op, .bind(rhs))
}
/// Allow specifying a conjunctive `HAVING` condition using two Fluent model keypaths, which may optionally refer
/// to different models, as the operands.
@discardableResult
public func having(
_ keypath1: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ keypath2: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self {
self.having(keypath1, op, .column(keypath2))
}
/// Allow specifying a conjunctive `HAVING` condition using a Fluent model keypath as the left-hand operand.
@discardableResult
public func having(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some SQLExpression
) -> Self {
self.having(.column(keypath), op, rhs)
}
/// Allow specifying a conjunctive `HAVING` condition using a Fluent model keypath as the left-hand operand.
/// This overload allows more convenient use of custom operators like `ILIKE`.
@discardableResult
public func having(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: some SQLExpression,
_ rhs: some SQLExpression
) -> Self {
self.having(.column(keypath), op, rhs)
}
/// Allow specifying a conjunctive `HAVING` condition using a Fluent model keypath having a boolean value as the
/// sole operand. This overload allows more convenient boolean testing.
@discardableResult
public func having<Prop: QueryAddressableProperty>(
_ keypath: KeyPath<some Schema, Prop>
) -> Self where Prop.Value == Bool {
self.having(.column(keypath))
}
/// Allow specifying an inclusively disjunctive `HAVING` condition using a Fluent model keypath as the
/// left-hand operand.
@discardableResult
public func orHaving(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some Encodable & Sendable
) -> Self {
self.orHaving(keypath, op, .bind(rhs))
}
/// Allow specifying an inclusively disjunctive `HAVING` condition using two Fluent model keypaths, which may
/// optionally refer to different models, as the operands.
@discardableResult
public func orHaving(
_ keypath1: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ keypath2: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self {
self.orHaving(keypath1, op, .column(keypath2))
}
/// Allow specifying an inclusively disjunctive `HAVING` condition using a Fluent model keypath as the
/// left-hand operand.
@discardableResult
public func orHaving(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some SQLExpression
) -> Self {
self.orHaving(.column(keypath), op, rhs)
}
/// Allow specifying an inclusively disjunctive `HAVING` condition using a Fluent model keypath as the
/// left-hand operand. This overload allows more convenient use of custom operators like `ILIKE`.
@discardableResult
public func orHaving(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: some SQLExpression,
_ rhs: some SQLExpression
) -> Self {
self.orHaving(.column(keypath), op, rhs)
}
/// Allow specifying an inclusively disjunctive `HAVING` condition using a Fluent model keypath having a boolean
/// value as the sole operand. This overload allows more convenient boolean testing.
@discardableResult
public func orHaving<Prop: QueryAddressableProperty>(
_ keypath: KeyPath<some Schema, Prop>
) -> Self where Prop.Value == Bool {
self.orHaving(.column(keypath))
}
}
extension SQLSubqueryClauseBuilder {
/// Allow specifying a table for a `FROM` clause using a Fluent model. Note that due to `FROM`'s place in query
/// syntax, the result of `Schema.sqlTable` is not suitable here. If an `alias` is provided, it takes precedence
/// over that specified by the model type (such as by a `ModelAlias`) and it will be the caller's responsibility
/// to deal with that override in other contexts that assume the model's alias is the one to use. (This will never
/// be a concern for 99.999999% of users.)
@discardableResult
public func from(_ type: any Schema.Type, as alias: String? = nil) -> Self {
if let alias = alias ?? type.alias {
self.from(SQLQualifiedTable(type.schema, space: type.space), as: .identifier(alias))
} else {
self.from(SQLQualifiedTable(type.schema, space: type.space))
}
}
/// Allow specifying a column in a `GROUP BY` clause using a Fluent model keypath.
@discardableResult
public func groupBy(_ keypath: KeyPath<some Schema, some QueryAddressableProperty>) -> Self {
self.groupBy(.column(keypath))
}
}
extension SQLJoinBuilder {
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
public func join(
_ table: String, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
on expression: some SQLExpression
) -> Self {
self.join(.alias(table, as: alias), method: method, on: expression)
}
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
public func join(
_ table: some SQLExpression, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
on expression: some SQLExpression
) -> Self {
self.join(.alias(table, as: alias), method: method, on: expression)
}
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
public func join(
_ table: String, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
on lhs: some SQLExpression, _ op: SQLBinaryOperator, _ rhs: some SQLExpression
) -> Self {
self.join(.alias(table, as: alias), method: method, on: lhs, op, rhs)
}
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
public func join(
_ table: some SQLExpression, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
on lhs: some SQLExpression, _ op: SQLBinaryOperator, _ rhs: some SQLExpression
) -> Self {
self.join(.alias(table, as: alias), method: method, on: lhs, op, rhs)
}
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
public func join(
_ table: some SQLExpression, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
using columns: some SQLExpression
) -> Self {
self.join(.alias(table, as: alias), method: method, using: columns)
}
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
public func join(
_ table: some SQLExpression, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
using col1: some SQLExpression, _ col2: some SQLExpression, _ cols: any SQLExpression...
) -> Self {
self.join(table, as: alias, method: method, using: [col1, col2] + cols)
}
/// Allow specifying aliases for joined tables directly in the join method.
@discardableResult
func join(
_ table: some SQLExpression, as alias: some StringProtocol,
method: SQLJoinMethod = .inner,
using columns: [any SQLExpression]
) -> Self {
self.join(table, as: alias, method: method, using: .group(.list(columns)))
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model and the join condition using two Fluent model
/// keypaths, which may optionally refer to different models, as the operands. The same caveats which apply to
/// `from(_:as:)` (see above) apply to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
on lhs: KeyPath<some Schema, some QueryAddressableProperty>, _ op: SQLBinaryOperator, _ rhs: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self {
self.join(type, as: alias, method: method, on: lhs, op, .column(rhs))
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model and the join condition using a Fluent model
/// keypath as the left-hand operand. The same caveats which apply to `from(_:as:)` (see above) apply
/// to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
on lhs: KeyPath<some Schema, some QueryAddressableProperty>, _ op: SQLBinaryOperator, _ rhs: some SQLExpression
) -> Self {
self.join(type, as: alias, method: method, on: .column(lhs), op, rhs)
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model. The same caveats which apply to
/// `from(_:as:)` (see above) apply to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
on left: some SQLExpression, _ op: SQLBinaryOperator, _ right: some SQLExpression
) -> Self {
self.join(type, as: alias, method: method, on: .expr(left, op, right))
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model. The same caveats which apply to
/// `from(_:as:)` (see above) apply to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
on expression: some SQLExpression
) -> Self {
if let alias = alias ?? type.alias {
self.join(SQLQualifiedTable(type.schema, space: type.space), as: alias, method: method, on: expression)
} else {
self.join(SQLQualifiedTable(type.schema, space: type.space), method: method, on: expression)
}
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model. The same caveats which apply to
/// `from(_:as:)` (see above) apply to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
using column1: some SQLExpression, _ column2: some SQLExpression, _ columns: any SQLExpression...
) -> Self {
self.join(type, as: alias, method: method, using: [column1, column2] + columns)
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model. The same caveats which apply to
/// `from(_:as:)` (see above) apply to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
using columns: [any SQLExpression]
) -> Self {
self.join(type, as: alias, method: method, using: .group(.list(columns)))
}
/// Allow specifying a table for a `JOIN` clause using a Fluent model. The same caveats which apply to
/// `from(_:as:)` (see above) apply to this method.
@discardableResult
public func join(
_ type: (some Schema).Type, as alias: String? = nil,
method: SQLJoinMethod = .inner,
using columns: some SQLExpression
) -> Self {
if let alias = alias ?? type.alias {
self.join(SQLQualifiedTable(type.schema, space: type.space), as: alias, method: method, using: columns)
} else {
self.join(SQLQualifiedTable(type.schema, space: type.space), method: method, using: columns)
}
}
}
extension SQLUnqualifiedColumnListBuilder {
/// Despite the name of the builder protocol, this method specifies a _fully qualified_ column using a column name
/// and table name in the `SQLColumn` parameter order.
@discardableResult
public func column(_ column: some StringProtocol, table: some StringProtocol) -> Self {
self.column(.identifier(column), table: .identifier(table))
}
/// Despite the name of the builder protocol, this method specifies a _fully qualified_ column using a column name
/// and table expression in the `SQLColumn` parameter order.
@discardableResult
public func column(_ column: some StringProtocol, table: some SQLExpression) -> Self {
self.column(.identifier(column), table: table)
}
/// Despite the name of the builder protocol, this method specifies a _fully qualified_ column using column and
/// table expressions in the `SQLColumn` parameter order.
@discardableResult
public func column(_ column: some SQLExpression, table: some SQLExpression) -> Self {
self.column(.column(column, table: table))
}
/// Despite the name of the builder protocol, this method specifies a _fully qualified_ column using a Fluent model
/// keypath. To specify an _unqualified_ column with a keypath, consider `.column(.identifier(\Model.$property))`.
/// See `Schema.sqlColumn(for:)`.
@discardableResult
public func column(_ keypath: KeyPath<some Schema, some QueryAddressableProperty>) -> Self {
self.column(.column(keypath))
}
/// Despite the name of the builder protocol, this method specifies a variable number of _fully qualified_ columns
/// using Fluent model keypaths. To specify _unqualified_ columns with keypaths, consider
/// `.column(.identifier(\Model.$property))`. See `Schema.sqlColumn(for:)`.
@discardableResult
public func columns<each S: Schema, each P: QueryAddressableProperty>(_ keypaths: repeat KeyPath<each S, each P>) -> Self {
repeat _ = self.column(each keypaths)
return self
}
}
/// > Note: `SQLInsertBuilder` is not an `SQLUnqualifiedColumnListBuilder`, although it should be. See `SQLInsertBuilder`'s
/// > documentation for details.
extension SQLInsertBuilder {
/// Allow specifying a set of unqualified column names for an insert builder using Fluent model keypaths. As with
/// all other `.columns()` methods of `SQLInsertBuilder`, this method _replaces_ all existing columns.
@discardableResult
public func columns<each S: Schema, each P: QueryAddressableProperty>(_ keypaths: repeat KeyPath<each S, each P>) -> Self {
// N.B.: Due to the nature of parameter packs, we must manage the builder's `SQLInsert` expression directly.
self.insert.columns = []
for keypath in repeat each keypaths {
self.insert.columns.append(.identifier(keypath))
}
return self
}
}
extension SQLAliasedColumnListBuilder {
/// Allow specifying a fully qualified column with an aliased name using a column name and table name in the
/// `SQLColumn` parameter order.
@discardableResult
public func column(_ column: some StringProtocol, table: some StringProtocol, as alias: some StringProtocol) -> Self {
self.column(.column(column, table: table), as: String(alias))
}
/// Allow specifying a fully qualified column with an aliased name using a column name and table expression in the
/// `SQLColumn` parameter order.
@discardableResult
public func column(_ column: some StringProtocol, table: some SQLExpression, as alias: some StringProtocol) -> Self {
self.column(.column(column, table: table), as: String(alias))
}
/// Allow specifying a fully qualified column with an aliased name using column and table expressions in the
/// `SQLColumn` parameter order.
@discardableResult
public func column(_ column: some SQLExpression, table: some SQLExpression, as alias: some StringProtocol) -> Self {
self.column(.column(column, table: table), as: String(alias))
}
/// Allow specifying a fully qualified column with an aliased name using a Fluent model keypath. See
/// `Schema.sqlColumn(for:)`.
@discardableResult
public func column(_ keypath: KeyPath<some Schema, some QueryAddressableProperty>, as alias: some StringProtocol) -> Self {
self.column(.column(keypath), as: String(alias))
}
}
extension SQLColumnUpdateBuilder {
/// Allow specifying a column to update using a Fluent model keypath. See `Fields.sqlIdentifier(for:)`.
@discardableResult
public func set(_ keypath: KeyPath<some Fields, some QueryAddressableProperty>, to bind: some Encodable & Sendable) -> Self {
self.set(.identifier(keypath), to: bind)
}
/// Allow specifying a column to update using a Fluent model keypath. See `Fields.sqlIdentifier(for:)`.
@discardableResult
public func set(_ keypath: KeyPath<some Fields, some QueryAddressableProperty>, to value: some SQLExpression) -> Self {
self.set(.identifier(keypath), to: value)
}
}
extension SQLConflictUpdateBuilder {
/// Allow specifying a column to use the excluded value of on conflict using a Fluent model keypath.
/// See `Fields.sqlIdentifier(for:)`.
@discardableResult
public func set(excludedValueOf keypath: KeyPath<some Fields, some QueryAddressableProperty>) -> Self {
self.set(excludedValueOf: .identifier(keypath))
}
}
extension SQLPartialResultBuilder {
/// Allow specifying a sorting column using a Fluent model keypath. See `Schema.sqlColumn(for:)`.
@discardableResult
public func orderBy(_ keypath: KeyPath<some Schema, some QueryAddressableProperty>, _ direction: SQLDirection = .ascending) -> Self {
self.orderBy(.column(keypath), direction)
}
}
extension SQLReturningBuilder {
/// Allow specifying columns in a `RETURNING` clause using Fluent model keypaths. See `Schema.sqlColumn(for:)`.
@discardableResult
public func returning<each S: Schema, each P: QueryAddressableProperty>(_ keypaths: repeat KeyPath<each S, each P>) -> SQLReturningResultBuilder<Self> {
var columns: [any SQLExpression] = []
repeat columns.append(.column(each keypaths))
return self.returning(columns)
}
}
extension SQLCommonTableExpressionBuilder {
/// Allow specifying a CTE's name using a Fluent model or model alias. The provided type may not specify a `space`.
@discardableResult
public func with(_ type: (some Schema).Type, columns: [String], as query: some SQLExpression) -> Self {
self.with(type.schemaOrAlias, columns: columns, as: query)
}
/// Allow specifying a CTE's name using a Fluent model or model alias. The provided type may not specify a `space`.
@discardableResult
public func with(recursive type: (some Schema).Type, columns: [String], as query: some SQLExpression) -> Self {
self.with(recursive: type.schemaOrAlias, columns: columns, as: query)
}
/// Allow specifying a CTE's name using a Fluent model or model alias. The provided type may not specify a `space`.
@discardableResult
public func with(_ type: (some Schema).Type, columns: [any SQLExpression] = [], as query: some SQLExpression) -> Self {
self.with(type.schemaOrAlias, columns: columns, as: query)
}
/// Allow specifying a CTE's name using a Fluent model or model alias. The provided type may not specify a `space`.
@discardableResult
public func with(recursive type: (some Schema).Type, columns: [any SQLExpression] = [], as query: some SQLExpression) -> Self {
self.with(recursive: type.schemaOrAlias, columns: columns, as: query)
}
}
extension SQLQueryFetcher {
/// Allow specifying a Fluent model keypath as a column name when decoding a single query fetcher result.
public func first<M: Schema, P: QueryAddressableProperty>(decodingColumn keypath: KeyPath<M, P>) async throws -> P.QueryablePropertyType.Value? {
try await self.first(decodingColumn: M.fieldKey(for: keypath).description, as: P.QueryablePropertyType.Value.self)
}
/// Allow specifying a Fluent model keypath as a column name when decoding query fetcher results.
public func all<M: Schema, P: QueryAddressableProperty>(decodingColumn keypath: KeyPath<M, P>) async throws -> [P.QueryablePropertyType.Value] {
try await self.all(decodingColumn: M.fieldKey(for: keypath).description, as: P.QueryablePropertyType.Value.self)
}
}
extension SQLCreateIndexBuilder {
/// Allow specifying the target table for a `CREATE INDEX` query using a Fluent model.
@discardableResult
public func on(_ type: (some Schema).Type) -> Self {
self.on(type.sqlTable)
}
}
extension SQLDropIndexBuilder {
/// Allow specifying the target table for a `DROP INDEX` query using a Fluent model.
@discardableResult
public func on(_ type: (some Schema).Type) -> Self {
self.on(type.sqlTable)
}
}
extension SQLDropTriggerBuilder {
/// Allow specifying the target table for a `DROP TRIGGER` query using a Fluent model.
@discardableResult
public func table(_ type: (some Schema).Type) -> Self {
self.table(type.sqlTable)
}
}
extension SQLDatabase {
/// Allow specifying a table for an `INSERT` query using a Fluent model.
public func insert(into model: (some Schema).Type) -> SQLInsertBuilder {
self.insert(into: model.sqlTable)
}
/// Allow specifying a table for an `UPDATE` query using a Fluent model.
public func update(_ model: (some Schema).Type) -> SQLUpdateBuilder {
self.update(model.sqlTable)
}
/// Allow specifying a table for a `DELETE` query using a Fluent model.
public func delete(from model: (some Schema).Type) -> SQLDeleteBuilder {
self.delete(from: model.sqlTable)
}
/// Mising SQLKit API for specifying a table for an `ALTER TABLE` with an arbitrary expression.
public func alter(table: any SQLExpression) -> SQLAlterTableBuilder {
.init(.init(name: table), on: self)
}
/// Allow specifying a table for an `ALTER TABLE` query using a Fluent model.
public func alter(table model: (some Schema).Type) -> SQLAlterTableBuilder {
self.alter(table: model.sqlTable)
}
/// Allow specifying a table for a `CREATE TRIGGER` query using a Fluent model.
public func create(trigger: String, table: (some Schema).Type, when: SQLCreateTrigger.WhenSpecifier, event: SQLCreateTrigger.EventSpecifier) -> SQLCreateTriggerBuilder {
self.create(trigger: .identifier(trigger), table: table.sqlTable, when: when, event: event)
}
/// Allow specifying a table for a `CREATE TRIGGER` query using a Fluent model.
public func create(trigger: any SQLExpression, table: (some Schema).Type, when: any SQLExpression, event: any SQLExpression) -> SQLCreateTriggerBuilder {
self.create(trigger: trigger, table: table.sqlTable, when: when, event: event)
}
/// Allow specifying a table for a `CREATE TABLE` query using a Fluent model.
public func create(table model: (some Schema).Type) -> SQLCreateTableBuilder {
self.create(table: model.sqlTable)
}
/// Allow specifying a table for a `DROP TABLE` query using a Fluent model.
public func drop(table model: (some Schema).Type) -> SQLDropTableBuilder {
self.drop(table: model.sqlTable)
}
}
Raw
SQLCaseExpression.swift
import SQLKit
/// Represents a `CASE [WHEN ... THEN ...] ... [ELSE ...] END` expression.
public struct SQLCaseExpression: SQLExpression {
/// The list of alternatives for the `CASE` expression, expressed as a set of "if condition"+"then result" pairs.
public let clauses: [(condition: any SQLExpression, result: any SQLExpression)]
/// The optional "default" alternative for the `CASE` expression.
public let alternativeResult: (any SQLExpression)?
/// Memberwise initializer.
///
/// - Parameters:
/// - clauses: The list of alternatives for the `CASE` expression.
/// - alternativeResult: The optional default alternative for the `CASE` expression.
public init(clauses: [(condition: any SQLExpression, result: any SQLExpression)], alternativeResult: (any SQLExpression)?) {
self.clauses = clauses
self.alternativeResult = alternativeResult
}
// See `SQLExpression.serialize(to:)`.
public func serialize(to serializer: inout SQLSerializer) {
serializer.statement {
$0.append("CASE")
for clause in self.clauses {
$0.append("WHEN", clause.condition)
$0.append("THEN", clause.result)
}
if let alternativeResult = self.alternativeResult {
$0.append("ELSE", alternativeResult)
}
$0.append("END")
}
}
}
extension SQLExpression {
/// Convenience method for creating an ``SQLCaseExpression`` with a single alternative and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression {
.init(clauses: [(condition1, result1)], alternativeResult: alternative)
}
/// Convenience method for creating an ``SQLCaseExpression`` with two alternatives and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
when condition2: some SQLExpression, then result2: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression {
.init(clauses: [(condition1, result1), (condition2, result2)], alternativeResult: alternative)
}
/// Convenience method for creating an ``SQLCaseExpression`` with three alternatives and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
when condition2: some SQLExpression, then result2: some SQLExpression,
when condition3: some SQLExpression, then result3: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression {
.init(clauses: [(condition1, result1), (condition2, result2), (condition3, result3)], alternativeResult: alternative)
}
/// Convenience method for creating an ``SQLCaseExpression`` with four alternatives and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
when condition2: some SQLExpression, then result2: some SQLExpression,
when condition3: some SQLExpression, then result3: some SQLExpression,
when condition4: some SQLExpression, then result4: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression {
.init(clauses: [(condition1, result1), (condition2, result2), (condition3, result3), (condition4, result4)], alternativeResult: alternative)
}
}
/// These overloads simply call through to the above sets of methods, wrapping the result in an Optional. This
/// allows using the static shorthand methods in optional contexts (such as a defaulted-`nil` parameter).
///
/// It is an unfortunate language limitation that so much duplication is necessary for this simple and common use case.
extension Optional where Wrapped: SQLExpression {
/// Convenience method for creating an optional ``SQLCaseExpression`` with a single alternative and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression, `else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression? {
.some(.case(when: condition1, then: result1, else: alternative))
}
/// Convenience method for creating an optional ``SQLCaseExpression`` with two alternatives and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
when condition2: some SQLExpression, then result2: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression? {
.some(.case(when: condition1, then: result1, when: condition2, then: result2, else: alternative))
}
/// Convenience method for creating an optional ``SQLCaseExpression`` with three alternatives and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
when condition2: some SQLExpression, then result2: some SQLExpression,
when condition3: some SQLExpression, then result3: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression? {
.some(.case(
when: condition1, then: result1,
when: condition2, then: result2,
when: condition3, then: condition3,
else: alternative
))
}
/// Convenience method for creating an optional ``SQLCaseExpression`` with four alternatives and an optional default.
public static func `case`(
when condition1: some SQLExpression, then result1: some SQLExpression,
when condition2: some SQLExpression, then result2: some SQLExpression,
when condition3: some SQLExpression, then result3: some SQLExpression,
when condition4: some SQLExpression, then result4: some SQLExpression,
`else` alternative: (some SQLExpression)? = nil
) -> Self where Self == SQLCaseExpression? {
.some(.case(
when: condition1, then: result1, when: condition2, then: result2,
when: condition3, then: condition3, when: condition4, then: condition4,
else: alternative
))
}
}
Raw
SQLDateValue.swift
import SQLKit
/// A database-independent expression of a date value representing "now".
public struct SQLUnderlyingCurrentTimestampValue: SQLExpression {
// See `SQLExpression.serialize(to:)`.
public func serialize(to serializer: inout SQLSerializer) {
switch serializer.dialect.name {
case "sqlite": SQLLiteral.string("now").serialize(to: &serializer) // For SQLite, write out the literal string 'now' (see below)
case "postgresql": SQLFunction("now").serialize(to: &serializer) // For Postgres, "current_timestamp" is a keyword, not a function, so use "now()" instead.
default: SQLFunction("current_timestamp").serialize(to: &serializer) // Everywhere else, just call the SQL standard function.
}
}
}
/// A wrapper for correctly referring to expressions which should be interpreted as datetime values.
///
/// This is useful because of the occasional database (**cough**SQLite**cough**) which doesn't do the right
/// thing when given such expressions as other databases do.
public struct SQLDateValue<E: SQLExpression>: SQLExpression {
/// Create a date value representing the current datetime.
public static func now() -> Self where E == SQLUnderlyingCurrentTimestampValue {
.init(.init())
}
/// The represented datetime value expression.
public let value: E
/// Create a datevalue expression from a value expression.
public init(_ value: E) {
self.value = value
}
// See `SQLExpression.serialize(to:)`.
public func serialize(to serializer: inout SQLSerializer) {
switch serializer.dialect.name {
case "sqlite": // For SQLite, explicitly convert the inputs to UNIX timestamps
SQLFunction("unixepoch", args: self.value).serialize(to: &serializer)
default: // With all other databases, this is just a passthrough.
self.value.serialize(to: &serializer)
}
}
}
extension SQLExpression {
/// A convenience method for creating an ``SQLDateValue`` expression representing the current datetime.
public static func now() -> Self where Self == SQLDateValue<SQLUnderlyingCurrentTimestampValue> {
.now()
}
}
extension Optional where Wrapped: SQLExpression {
/// A convenience method for creating an optional ``SQLDateValue`` expression representing the current datetime.
public static func now() -> Self where Self == SQLDateValue<SQLUnderlyingCurrentTimestampValue>? {
.some(.now())
}
}
Raw
SQLExpression+ModelKeypaths.swift
import FluentKit
import SQLKit
extension SQLExpression {
/// Allow using `.identifier(_:)` with Fluent model keypaths. See `Fields.sqlIdentifier(for:)`.
public static func identifier<M: Fields>(
_ keypath: KeyPath<M, some QueryAddressableProperty>
) -> Self where Self == SQLIdentifier {
M.sqlIdentifier(for: keypath)
}
/// Allow using `.column(_:)` with Fluent model keypaths. See `Schema.sqlColumn(for:)`.
public static func column<M: Schema>(
_ keypath: KeyPath<M, some QueryAddressableProperty>
) -> Self where Self == SQLColumn {
M.sqlColumn(for: keypath)
}
/// Allow specifying a unary inversion expression using a Fluent model keypath.
public static func not(
_ kp: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self where Self == SQLUnaryExpression {
.not(.column(kp))
}
/// Allow specifying a unary expression using a Fluent model keypath.
public static func expr(
_ op: SQLUnaryOperator,
_ kp: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self where Self == SQLUnaryExpression {
.expr(op, .column(kp))
}
/// Allow specifying a binary expression using two Fluent model keypaths, which may optionally refer
/// to different models.
public static func expr(
_ kp1: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ kp2: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self where Self == SQLBinaryExpression {
.expr(kp1, op, .column(kp2))
}
/// Allow specifying a binary expression using a Fluent model keypath as the left-hand operand.
public static func expr(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
_ op: SQLBinaryOperator,
_ rhs: some SQLExpression
) -> Self where Self == SQLBinaryExpression {
.expr(.column(keypath), op, rhs)
}
/// Allow specifying an alias using a Fluent model keypath.
public static func alias(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
as alias: some StringProtocol
) -> Self where Self == SQLAlias {
.alias(.column(keypath), as: alias)
}
/// Allow specifying an alias using a Fluent model keypath.
public static func alias(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
as alias: some SQLExpression
) -> Self where Self == SQLAlias {
.alias(.column(keypath), as: alias)
}
/// Allow specifying an argument to the `length()` SQL function using a Fluent model keypath.
public static func length(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>
) -> Self where Self == SQLFunction {
.length(.column(keypath))
}
/// Allow specifying an argument to the `count()` SQL function using a Fluent model keypath.
public static func count(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
distinct: Bool = false
) -> Self where Self == SQLFunction {
.count(.column(keypath), distinct: distinct)
}
/// Allow specifying an argument to the `substring()` SQL function using a Fluent model keypath.
public static func substring(
_ keypath: KeyPath<some Schema, some QueryAddressableProperty>,
from start: Int,
for length: Int
) -> Self where Self == SQLFunction {
.substring(.column(keypath), from: start, for: length)
}
}
extension SQLRow {
public func contains<F: Fields>(column keypath: KeyPath<F, some QueryAddressableProperty>) -> Bool {
self.contains(column: F.key(for: keypath))
}
public func decodeNil<F: Fields>(column keypath: KeyPath<F, some QueryAddressableProperty>) throws -> Bool {
try self.decodeNil(column: F.key(for: keypath))
}
public func decode<F: Fields, P: QueryAddressableProperty>(column keypath: KeyPath<F, P>) throws -> P.QueryablePropertyType.Value {
try self.decode(column: F.key(for: keypath), as: P.QueryablePropertyType.Value.self)
}
public func decode<each S: Schema, each P: QueryAddressableProperty>(columns keypaths: repeat KeyPath<each S, each P>) throws -> (repeat (each P).QueryablePropertyType.Value) {
(repeat try self.decode(column: each keypaths))
}
}
extension SQLQueryFetcher {
public func first<each S: Schema, each P: QueryAddressableProperty>(
decodingColumns keypaths: repeat KeyPath<each S, each P>
) async throws -> (repeat (each P).QueryablePropertyType.Value)? {
try await self.first().map { try $0.decode(columns: repeat each keypaths) }
}
public func all<each S: Schema, each P: QueryAddressableProperty>(
decodingColumns keypaths: repeat KeyPath<each S, each P>
) async throws -> [(repeat (each P).QueryablePropertyType.Value)] {
try await self.all().map { try $0.decode(columns: repeat each keypaths) }
}
}
Raw
SQLExpression+StaticShorthand.swift
import SQLKit
extension SQLExpression {
// MARK: - SQLBinaryExpression
public static func expr(
_ lhs: some SQLExpression,
_ op: SQLBinaryOperator,
_ rhs: some Encodable & Sendable
) -> Self where Self == SQLBinaryExpression {
.expr(lhs, op, .bind(rhs))
}
public static func expr(
_ lhs: some SQLExpression,
_ op: SQLBinaryOperator,
_ rhs: some SQLExpression
) -> Self where Self == SQLBinaryExpression {
.init(lhs, op, rhs)
}
/// Produces a left-nested binary expression joining each of the provided subexpressions with the given operator.
/// Due to the limtations of variadic generics, at least two subexpressions must be provided. Subexpressions may be
/// of any valid expression type.
///
/// Example:
///
/// ```swift
/// let allExprs: SQLBinaryExpression = .expr(
/// op: .and,
/// .expr(.identifier("foo"), .equal, "bar"),
/// .not(.literal(false)),
/// .function("bool_func"),
/// .expr(.identifier("baz"), .notLike, .literal("%bamf%"))
/// )
///
/// // Identical to:
/// let allExprs: SQLBinaryExpression = .expr(
/// .expr(
/// .expr(
/// .expr("foo", .equal, "bar"),
/// .and,
/// .not(.literal(false))
/// ),
/// .and,
/// .function("bool_func")
/// ),
/// .and,
/// .expr("baz", .notLike, .literal("%bamf%"))
/// )
///
/// // Serializes to (Postgres dialect):
/// // "foo" = $1 AND NOT false AND bool_func() AND "baz" NOT LIKE '%bamf%' ['bar'::text]
/// ```
///
public static func expr<each E: SQLExpression>(
op: SQLBinaryOperator,
_ expr1: some SQLExpression,
_ expr2: some SQLExpression,
_ exprs: repeat each E
) -> Self where Self == SQLBinaryExpression {
var finalExpr = SQLBinaryExpression(expr1, op, expr2)
for expr in repeat each exprs {
finalExpr = .expr(finalExpr, op, expr)
}
return finalExpr
}
// MARK: - SQLBind
public static func bind(_ value: some Encodable & Sendable) -> Self where Self == SQLBind {
.init(value)
}
public static func bindGroup(_ values: some Sequence<some Encodable & Sendable>) -> Self where Self == SQLGroupExpression {
.init(values.map(SQLBind.init(_:)))
}
// MARK: - SQLFunction
public static func function<each E: SQLExpression>(_ name: some StringProtocol, _ params: repeat each E) -> Self where Self == SQLFunction {
var args: [any SQLExpression] = []
repeat args.append(each params)
return .function(name, args)
}
public static func function(_ name: some StringProtocol, _ params: [any SQLExpression]) -> Self where Self == SQLFunction {
.init(String(name), args: params)
}
public static func length(_ arg: some SQLExpression) -> Self where Self == SQLFunction {
.function("length", [arg])
}
public static func concat<each E: SQLExpression>(_ params: repeat each E) -> Self where Self == SQLFunction {
.function("concat", repeat each params)
}
public static func concat(_ params: [any SQLExpression]) -> Self where Self == SQLFunction {
.function("concat", params)
}
public static func concat_ws<each E: SQLExpression>(separator: some StringProtocol, _ params: repeat each E) -> Self where Self == SQLFunction {
.function("concat_ws", .literal(separator), repeat each params)
}
public static func concat_ws(separator: some StringProtocol, _ params: [any SQLExpression]) -> Self where Self == SQLFunction {
.function("concat_ws", [.literal(separator)] + params)
}
public static func count(_ name: some StringProtocol, distinct: Bool = false) -> Self where Self == SQLFunction {
distinct ? .function("count", .clause(.unsafeRaw("distinct"), .identifier(name))) : .function("count", .identifier(name))
}
public static func count(_ name: some SQLExpression, distinct: Bool = false) -> Self where Self == SQLFunction {
distinct ? .function("count", .clause(.unsafeRaw("distinct"), name)) : .function("count", name)
}
public static func coalesce<each E: SQLExpression>(_ params: repeat each E) -> Self where Self == SQLFunction {
.function("coalesce", repeat each params)
}
public static func substring(_ column: some SQLExpression, from start: Int, for length: Int) -> Self where Self == SQLFunction {
.function("substring", column, .literal(start), .literal(length))
}
public static func sum(_ expr: some SQLExpression) -> Self where Self == SQLFunction {
.function("sum", expr)
}
public static func `if`(_ expr: some SQLExpression, then thenExpr: some SQLExpression, else elseExpr: some SQLExpression) -> Self where Self == SQLFunction {
.function("if", thenExpr, elseExpr)
}
// MARK: - SQLGroupExpression
public static func group(_ content: some SQLExpression) -> Self where Self == SQLGroupExpression {
.init(content)
}
// MARK: - SQLIdentifier
public static func identifier(_ str: some StringProtocol) -> Self where Self == SQLIdentifier {
.init(String(str))
}
// MARK: - SQLList
public static func list<each E: SQLExpression>(_ pieces: repeat each E) -> Self where Self == SQLList {
.list(repeat each pieces, separator: ", ")
}
public static func list(_ pieces: [any SQLExpression]) -> Self where Self == SQLList {
.list(pieces, separator: ", ")
}
public static func list<each E: SQLExpression>(_ pieces: repeat each E, separator: some StringProtocol) -> Self where Self == SQLList {
var args: [any SQLExpression] = []
repeat args.append(each pieces)
return .list(args, separator: separator)
}
public static func list(_ pieces: [any SQLExpression], separator: some StringProtocol) -> Self where Self == SQLList {
.init(pieces, separator: .unsafeRaw(separator))
}
public static func clause<each E: SQLExpression>(_ pieces: repeat each E) -> Self where Self == SQLList {
.list(repeat each pieces, separator: " ")
}
public static func clause(_ pieces: [any SQLExpression]) -> Self where Self == SQLList {
.list(pieces, separator: " ")
}
// MARK: - SQLLiteral
public static func literal(_ str: some StringProtocol) -> Self where Self == SQLLiteral {
.string(String(str))
}
public static func literal(_ str: (some StringProtocol)?) -> Self where Self == SQLLiteral {
str.map { .string(String($0)) } ?? .null
}
public static func literal(_ int: some FixedWidthInteger) -> Self where Self == SQLLiteral {
.numeric("\(int)")
}
public static func literal(_ int: (some FixedWidthInteger)?) -> Self where Self == SQLLiteral {
int.map { .numeric("\($0)") } ?? .null
}
public static func literal(_ real: some BinaryFloatingPoint) -> Self where Self == SQLLiteral {
.numeric("\(real)")
}
public static func literal(_ real: (some BinaryFloatingPoint)?) -> Self where Self == SQLLiteral {
real.map { .numeric("\($0)") } ?? .null
}
public static func literal(_ bool: Bool) -> Self where Self == SQLLiteral {
.boolean(bool)
}
public static func literal(_ bool: Bool?) -> Self where Self == SQLLiteral {
bool.map { .boolean($0) } ?? .null
}
public static func null() -> Self where Self == SQLLiteral {
.null
}
public static func all() -> Self where Self == SQLLiteral {
.all
}
public static func `default`() -> Self where Self == SQLLiteral {
.default
}
// MARK: - SQLRaw
public static func unsafeRaw(_ content: some StringProtocol) -> Self where Self == SQLRaw {
.init(String(content))
}
// MARK: - SQLAlias
public static func alias(_ column: some StringProtocol, as alias: some StringProtocol) -> Self where Self == SQLAlias {
.alias(.column(column), as: alias)
}
public static func alias(_ column: some SQLExpression, as alias: some StringProtocol) -> Self where Self == SQLAlias {
.alias(column, as: .identifier(alias))
}
public static func alias(_ column: some SQLExpression, as alias: some SQLExpression) -> Self where Self == SQLAlias {
.init(column, as: alias)
}
// MARK: - SQLColumn
public static func column(_ name: some StringProtocol) -> Self where Self == SQLColumn {
// N.B.: We need a separate overload for name-as-string-without-table here because a name specified as a
// string can have a semantically critical meaning (specifically "*" is handled as SQLLiteral.all) that is
// not relevant for a name specified as an expression; if we forward to the expression-based initializers,
// that distinction is lost. The extra overload is used instead of a defaulted parameter because
// `(some StringProtocol)? = nil` isn't linguistically valid.
.init(String(name))
}
public static func column(_ name: some StringProtocol, table: some StringProtocol) -> Self where Self == SQLColumn {
.init(String(name), table: String(table))
}
public static func column(_ name: some StringProtocol, table: some SQLExpression) -> Self where Self == SQLColumn {
.column(.identifier(name), table: table)
}
public static func column(_ name: some SQLExpression, table: some StringProtocol) -> Self where Self == SQLColumn {
.column(name, table: .identifier(table))
}
public static func column(_ name: some SQLExpression, table: (any SQLExpression)? = nil) -> Self where Self == SQLColumn {
.init(name, table: table)
}
// MARK: - SQLQualifiedTable
public static func table(_ name: some StringProtocol, space: (some StringProtocol)? = nil) -> Self where Self == SQLQualifiedTable {
.init(String(name), space: space.map { String($0) })
}
public static func table(_ name: some StringProtocol, space: some SQLExpression) -> Self where Self == SQLQualifiedTable {
.table(.identifier(name), space: space)
}
public static func table(_ name: some SQLExpression, space: (some StringProtocol)? = nil) -> Self where Self == SQLQualifiedTable {
.table(name, space: space.map { .identifier($0) })
}
public static func table(_ name: some SQLExpression, space: (any SQLExpression)? = nil) -> Self where Self == SQLQualifiedTable {
.init(name, space: space)
}
// MARK: - SQLSubquery
public static func subquery(_ build: (any SQLSubqueryClauseBuilder) throws -> any SQLSubqueryClauseBuilder) rethrows -> Self where Self == SQLSubquery {
// N.B.: We have to reimplement `SQLSubquery.select()` here instead of calling it because it returns `some SQLExpression` and we
// need a concrete type for this method to work.
let builder = SQLSubqueryBuilder()
_ = try build(builder)
return builder.query
}
}
/// These overloads simply call through to the above sets of methods, wrapping the result in an Optional. This
/// allows using the static shorthand methods in optional contexts (such as a defaulted-`nil` parameter).
///
/// It is an unfortunate language limitation that so much duplication is necessary for this simple and common use case.
extension Optional where Wrapped: SQLExpression {
// MARK: - SQLBinaryExpression
public static func expr(_ lhs: some SQLExpression, _ op: SQLBinaryOperator, _ rhs: some Encodable & Sendable) -> Self where Self == SQLBinaryExpression? { .some(.expr(lhs, op, rhs)) }
public static func expr(_ lhs: some SQLExpression, _ op: SQLBinaryOperator, _ rhs: some SQLExpression) -> Self where Self == SQLBinaryExpression? { .some(.expr(lhs, op, rhs)) }
public static func expr<each E: SQLExpression>(
op: SQLBinaryOperator, _ expr1: some SQLExpression, _ expr2: some SQLExpression, _ exprs: repeat each E
) -> Self where Self == SQLBinaryExpression? { .some(.expr(op: op, expr1, expr2, repeat each exprs)) }
// MARK: - SQLBind
public static func bind(_ value: some Encodable & Sendable) -> Self where Self == SQLBind? { .some(.bind(value)) }
public static func bindGroup(_ values: some Sequence<some Encodable & Sendable>) -> Self where Self == SQLGroupExpression? { .some(.bindGroup(values)) }
// MARK: - SQLFunction
public static func function<each E: SQLExpression>(_ name: some StringProtocol, _ params: repeat each E) -> Self where Self == SQLFunction? { .some(.function(name, repeat each params)) }
public static func function(_ name: some StringProtocol, _ params: [any SQLExpression]) -> Self where Self == SQLFunction? { .some(.function(name, params)) }
public static func length(_ arg: some SQLExpression) -> Self where Self == SQLFunction? { .some(.length(arg)) }
public static func concat<each E: SQLExpression>(_ params: repeat each E) -> Self where Self == SQLFunction? { .some(.concat(repeat each params)) }
public static func concat(_ params: [any SQLExpression]) -> Self where Self == SQLFunction? { .some(.concat(params)) }
public static func concat_ws<each E: SQLExpression>(separator: some StringProtocol, _ params: repeat each E) -> Self where Self == SQLFunction? { .some(.concat_ws(separator: separator, repeat each params)) }
public static func concat_ws(separator: some StringProtocol, _ params: [any SQLExpression]) -> Self where Self == SQLFunction? { .some(.concat_ws(separator: separator, params)) }
public static func count(_ name: some StringProtocol, distinct: Bool = false) -> Self where Self == SQLFunction? { .some(.count(name, distinct: distinct)) }
public static func count(_ name: some SQLExpression, distinct: Bool = false) -> Self where Self == SQLFunction? { .some(.count(name, distinct: distinct)) }
public static func coalesce<each E: SQLExpression>(_ params: repeat each E) -> Self where Self == SQLFunction? { .some(.coalesce(repeat each params)) }
public static func substring(_ column: some SQLExpression, from start: Int, for length: Int) -> Self where Self == SQLFunction? { .some(.substring(column, from: start, for: length)) }
public static func sum(_ expr: some SQLExpression) -> Self where Self == SQLFunction? { .some(.sum(expr)) }
public static func `if`(_ expr: some SQLExpression, then thenExpr: some SQLExpression, else elseExpr: some SQLExpression) -> Self where Self == SQLFunction? { .some(.if(expr, then: thenExpr, else: elseExpr)) }
// MARK: - SQLGroupExpression
public static func group(_ content: some SQLExpression) -> Self where Self == SQLGroupExpression? { .some(.group(content)) }
// MARK: - SQLIdentifier
public static func identifier(_ str: some StringProtocol) -> Self where Self == SQLIdentifier? { .some(.identifier(str)) }
// MARK: - SQLList
public static func list<each E: SQLExpression>(_ pieces: repeat each E) -> Self where Self == SQLList? { .some(.list(repeat each pieces)) }
public static func list(_ pieces: [any SQLExpression]) -> Self where Self == SQLList? { .some(.list(pieces)) }
public static func list<each E: SQLExpression>(_ pieces: repeat each E, separator: some StringProtocol) -> Self where Self == SQLList? { .some(.list(repeat each pieces, separator: separator)) }
public static func list(_ pieces: [any SQLExpression], separator: some StringProtocol) -> Self where Self == SQLList? { .some(.list(pieces, separator: separator)) }
public static func clause<each E: SQLExpression>(_ pieces: repeat each E) -> Self where Self == SQLList? { .some(.clause(repeat each pieces)) }
public static func clause(_ pieces: [any SQLExpression]) -> Self where Self == SQLList? { .some(.clause(pieces)) }
// MARK: - SQLLiteral
public static func literal(_ str: some StringProtocol) -> Self where Self == SQLLiteral? { .some(.literal(str)) }
public static func literal(_ str: (some StringProtocol)?) -> Self where Self == SQLLiteral? { .some(.literal(str)) }
public static func literal(_ int: some FixedWidthInteger) -> Self where Self == SQLLiteral? { .some(.literal(int)) }
public static func literal(_ int: (some FixedWidthInteger)?) -> Self where Self == SQLLiteral? { .some(.literal(int)) }
public static func literal(_ real: some BinaryFloatingPoint) -> Self where Self == SQLLiteral? { .some(.literal(real)) }
public static func literal(_ real: (some BinaryFloatingPoint)?) -> Self where Self == SQLLiteral? { .some(.literal(real)) }
public static func literal(_ bool: Bool) -> Self where Self == SQLLiteral? { .some(.literal(bool)) }
public static func literal(_ bool: Bool?) -> Self where Self == SQLLiteral? { .some(.literal(bool)) }
public static func null() -> Self where Self == SQLLiteral? { .some(.null()) }
public static func all() -> Self where Self == SQLLiteral? { .some(.all()) }
public static func `default`() -> Self where Self == SQLLiteral? { .some(.default()) }
// MARK: - SQLRaw
public static func unsafeRaw(_ content: some StringProtocol) -> Self where Self == SQLRaw? { .some(.unsafeRaw(content)) }
// MARK: - SQLAlias
public static func alias(_ column: some StringProtocol, as alias: some StringProtocol) -> Self where Self == SQLAlias? { .some(.alias(column, as: alias)) }
public static func alias(_ column: some SQLExpression, as alias: some StringProtocol) -> Self where Self == SQLAlias? { .some(.alias(column, as: alias)) }
public static func alias(_ column: some SQLExpression, as alias: some SQLExpression) -> Self where Self == SQLAlias? { .some(.alias(column, as: alias)) }
// MARK: - SQLColumn
public static func column(_ name: some StringProtocol) -> Self where Self == SQLColumn? { .some(.column(name)) }
public static func column(_ name: some StringProtocol, table: some StringProtocol) -> Self where Self == SQLColumn? { .some(.column(name, table: table)) }
public static func column(_ name: some StringProtocol, table: some SQLExpression) -> Self where Self == SQLColumn? { .some(.column(name, table: table)) }
public static func column(_ name: some SQLExpression, table: some StringProtocol) -> Self where Self == SQLColumn? { .some(.column(name, table: table)) }
public static func column(_ name: some SQLExpression, table: (any SQLExpression)? = nil) -> Self where Self == SQLColumn? { .some(.column(name, table: table)) }
// MARK: - SQLQualifiedTable
public static func table(_ name: some StringProtocol, space: (some StringProtocol)? = nil) -> Self where Self == SQLQualifiedTable? { .some(.table(name, space: space)) }
public static func table(_ name: some StringProtocol, space: some SQLExpression) -> Self where Self == SQLQualifiedTable? { .some(.table(name, space: space)) }
public static func table(_ name: some SQLExpression, space: (some StringProtocol)? = nil) -> Self where Self == SQLQualifiedTable? { .some(.table(name, space: space)) }
public static func table(_ name: some SQLExpression, space: (any SQLExpression)? = nil) -> Self where Self == SQLQualifiedTable? { .some(.table(name, space: space)) }
// MARK: - SQLSubquery
public static func subquery(_ build: (any SQLSubqueryClauseBuilder) throws -> any SQLSubqueryClauseBuilder) rethrows -> Self where Self == SQLSubquery? { try .some(.subquery(build)) }
}
Raw
SQLUnaryExpression.swift
import SQLKit
/// A fundamental syntactical expression - a unary operator and its single operand.
public struct SQLUnaryExpression: SQLExpression {
/// The unary operator.
public let op: any SQLExpression
/// The operand to which the operator applies.
public let operand: any SQLExpression
/// Create a new unary expression from components.
///
/// - Parameters:
/// - op: The operator.
/// - operand: The operand.
public init(op: some SQLExpression, operand: some SQLExpression) {
self.op = op
self.operand = operand
}
/// Create a unary expression from a predefined unary operator and an operand expression.
///
/// - Parameters:
/// - op: The operator.
/// - operand: The operand.
public init(_ op: SQLUnaryOperator, _ operand: some SQLExpression) {
self.init(op: op, operand: operand)
}
// See `SQLExpression.serialize(to:)`.
public func serialize(to serializer: inout SQLSerializer) {
serializer.statement {
$0.append(self.op, self.operand)
}
}
}
extension SQLExpression {
/// A convenience method for creating an ``SQLUnaryExpression`` from an operator and operand.
public static func expr(
_ op: SQLUnaryOperator,
_ operand: some SQLExpression
) -> Self where Self == SQLUnaryExpression {
.init(op, operand)
}
/// A convenience method for creating an ``SQLUnaryExpression`` for the `NOT` operator given an operand.
public static func not(_ operand: some SQLExpression) -> Self where Self == SQLUnaryExpression {
.expr(.not, operand)
}
}
extension Optional where Wrapped: SQLExpression {
/// A convenience method for creating an optional ``SQLUnaryExpression`` from an operator and operand.
public static func expr(
_ op: SQLUnaryOperator,
_ operand: some SQLExpression
) -> Self where Self == SQLUnaryExpression? {
.some(.expr(op, operand))
}
/// A convenience method for creating an optional ``SQLUnaryExpression`` for the `NOT` operator given an operand.
public static func not(_ operand: some SQLExpression) -> Self where Self == SQLUnaryExpression? {
.some(.not(operand))
}
}
Raw
SQLUnaryOperator.swift
import SQLKit
/// SQL unary expression operators.
public enum SQLUnaryOperator: SQLExpression {
/// Boolean inversion, or `NOT`.
case not
/// Arithmetic negation, or `-`.
case negate
/// Arithmetic positation, or `+` (no operation).
case plus
/// Bitwise inversion, or `~`.
case invert
/// Escape hatch for easily defining additional unary operators.
case custom(String)
// See `SQLExpression.serialize(to:)`.
@inlinable
public func serialize(to serializer: inout SQLSerializer) {
switch self {
case .not: serializer.write("NOT")
case .negate: serializer.write("-")
case .plus: serializer.write("+")
case .invert: serializer.write("~")
case .custom(let custom): serializer.write(custom)
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment