Swiftpack.co - Package - SwifQL/SwifQL

Mihael Isaev

MIT License Swift 5.2 Github Actions Swift.Stream

This lib can be used either stand alone, or with frameworks like Vapor, Kitura, Perfect and others

We recommend to use it with our Bridges lib which is built on top of SwifQL and support all its flexibility

It supports PostgreSQL and MySQL. And it's not so hard to add other dialects πŸ™‚ just check SwifQL/Dialect folder

Please feel free to ask any questions in issues, and also you could find me in the Discord app as @iMike#3049 or even better just join #swifql channel on Vapor's Discord server πŸ™‚


If you haven't found some functions available out-of-the-box then please check files like SwifQLable+Select and others in Sources/SwifQL folder to ensure how easy it is to extend SwifQL to support anything you need πŸš€

And feel free to send pull requests with your awesome new extensions ❀️

Support SwifQL development by giving a ⭐️


With Vapor 4 + Bridges + PostgreSQL

.package(url: "https://github.com/vapor/vapor.git", from:"4.0.0-rc"),
.package(url: "https://github.com/SwifQL/VaporBridges.git", from:"1.0.0-rc"),
.package(url: "https://github.com/SwifQL/PostgresBridge.git", from:"1.0.0-rc"),
.target(name: "App", dependencies: [
    .product(name: "Vapor", package: "vapor"),
    .product(name: "VaporBridges", package: "VaporBridges"),
    .product(name: "PostgresBridge", package: "PostgresBridge")

With Vapor 4 + Bridges + MySQL

.package(url: "https://github.com/vapor/vapor.git", from:"4.0.0-rc"),
.package(url: "https://github.com/SwifQL/VaporBridges.git", from:"1.0.0-rc"),
.package(url: "https://github.com/SwifQL/MySQLBridge.git", from:"1.0.0-rc"),
.target(name: "App", dependencies: [
    .product(name: "Vapor", package: "vapor"),
    .product(name: "VaporBridges", package: "VaporBridges"),
    .product(name: "MySQLBridge", package: "MySQLBridge")


.package(url: "https://github.com/MihaelIsaev/SwifQL.git", from:"2.0.0-beta"),
.target(name: "App", dependencies: [
    .product(name: "SwifQL", package: "SwifQL"),

Pure on NIO2

.package(url: "https://github.com/MihaelIsaev/SwifQL.git", from:"2.0.0-beta"),
.package(url: "https://github.com/MihaelIsaev/SwifQLNIO.git", from:"2.0.0"),
.target(name: "App", dependencies: [
    .product(name: "SwifQL", package: "SwifQL"),
    .product(name: "SwifQLNIO", package: "SwifQLNIO"),

Pure on NIO1 (deprecated)

.package(url: "https://github.com/MihaelIsaev/SwifQL.git", from:"1.0.0"),
.package(url: "https://github.com/MihaelIsaev/SwifQLNIO.git", from:"1.0.0"),
.target(name: "App", dependencies: ["SwifQL", "SwifQLNIO"]),

With Vapor 3 + Fluent (deprecated)

.package(url: "https://github.com/MihaelIsaev/SwifQL.git", from:"1.0.0"),
.package(url: "https://github.com/MihaelIsaev/SwifQLVapor.git", from:"1.0.0"),
.target(name: "App", dependencies: ["Vapor", "SwifQL", "SwifQLVapor"]),


This lib gives an ability to build absolutely any SQL query from simplest to monster complex.

Example of simple query

SELECT * FROM "User" WHERE "email" = 'john.smith@gmail.com'

build it with pure SwifQL this way

SwifQL.select(User.table.*).from(User.table).where(\User.email == "john.smith@gmail.com")

or with SwifQL + Bridges

SwifQL.select(User.table.*).from(User.table).where(\User.$email == "john.smith@gmail.com")
// or shorter
User.select.where(\User.$email == "john.smith@gmail.com")



πŸ’‘ TIP: It is simpler and more powerful with Bridges

Of course you have to import the lib

import SwifQL

For v1 Your table models should be conformed to Tableable protocol

extension MyTable: Tableable {}

For v2 Your table models should be conformed to Table protocol

extension MyTable: Table {}

How to build query

Instead of writing Model.self you should write Model.table, cause without Vapor you should conform your models to Table, and with Vapor its Models are already conforms to Table.

let query = SwifQL.select(\User.email, \User.name, \User.role)

or with SwifQL + Bridges

let query = SwifQL.select(\User.$email, \User.$name, \User.$role)
// or shorter
User.select(\.$email, \.$name, \.$role).orderBy(.asc(\User.$name)).limit(10)

How to print raw query

There are two options

1. Get just plain query
let rawSQLString = query.prepare(.psql).plain

or when using SwifQLSelectBuilder() - see below

let rawSQLBuilderString = query.build().prepare(.psql).plain
2. Get object splitted into: formatted raw SQL string with $ symbols, and separated array with values
let splittedQuery = query.prepare(.psql).splitted
let formattedSQLQuery = splittedQuery.query // formatted raw SQL string with $ symbols instead of values
let values = splittedQuery.values // an array of [Encodable] values

Then just put it into your database driver somehow πŸ™‚ or use Bridges

How to execute query?

SwifQL is only about building queries. For execution you have to use your favourite database driver.

Below you can see an example for SwifQL + Vapor4 + Bridges + PostgreSQL

πŸ’‘ You can get connection on both Application and Request objects.

Example for Application object e.g. for configure.swift file

// Called before your application initializes.
public func configure(_ app: Application) throws {
    app.postgres.connection(to: .myDb1) { conn in
        SwifQL.select(User.table.*).from(User.table).execute(on: conn).all(decoding: User.self).flatMap { rows in
            print("yaaay it works and returned \(rows.count) rows!")
    }.whenComplete {
        switch $0 {
        case .success: print("query was successful")
        case .failure(let error): print("query failed: \(error)")

Example for Request object

func routes(_ app: Application) throws {
    app.get("users") { req -> EventLoopFuture<[User]> in
        req.postgres.connection(to: .myDb1) { conn in
            SwifQL.select(User.table.*).from(User.table).execute(on: conn).all(decoding: User.self)

πŸ’‘ In examples above we use .all(decoding: User.self) for decoding results, but we also can use .first(decoding: User.self).unwrap(or: Abort(.notFound)) to get only first row and unwrap it since it may be nil.

Insert Into

Single record

SQL example

INSERT INTO "User" ("email", "name") VALUES ('john@gmail.com', 'John Doe'), ('sam@gmail.com', 'Samuel Jackson')

SwifQL representation

SwifQL.insertInto(User.table, fields: \User.email, \User.name).values("john@gmail.com", "John Doe")

or with SwifQL + Bridges

User(email: "john@gmail.com", name: "John Doe").insert(on: conn)


SQL example

INSERT INTO "User" ("email", "name") VALUES ('john@gmail.com', 'John Doe'), ('sam@gmail.com', 'Samuel Jackson')

SwifQL representation

SwifQL.insertInto(User.table, fields: \User.email, \User.name).values(array: ["john@gmail.com", "John Doe"], ["sam@gmail.com", "Samuel Jackson"])

or with SwifQL + Bridges

let user1 = User(email: "hello@gmail.com", name: "John")
let user2 = User(email: "byebye@gmail.com", name: "Amily")
let user3 = User(email: "trololo@gmail.com", name: "Trololo")
[user1, user2, user3].batchInsert(on: conn)


For now there are only one implemented builder

Select builder

SwifQLSelectBuilder - by using it you could easily build a select query but in multiple lines without carying about ordering.

let builder = SwifQLSelectBuilder()
builder.where(\User.id == 1)
let query = builder.build()
return query.execute(on: req, as: .psql)
            .first(decoding: User.self)
            .unwrap(or: Abort(.notFound, reason: "User not found"))

So it will build query like: SELECT "User".* FROM "User" WHERE "User"."id" = 1 LIMIT 1.

As you can see you shouldn't worry about parts ordering, it will sort them the right way before building.

More builders

Feel free to make your own builders and send pull request with it here!

Also more conveniences are available in Bridges lib which is created on top of SwifQL and support all its flexibility

More query examples

Let's use SwifQLSelectBuilder for some next examples below, cause it's really convenient especially for complex queries.

  1. Let's imagine that you want to query count of users.
/// Just query
let query = SwifQL.select(Fn.count(\User.id) => "count").from(User.table)

/// Execution and decoding for Vapor
struct CountResult: Codable {
  let count: Int64
query.execute(on: req, as: .psql)
     .first(decoding: CountResult.self)
     .unwrap(or: Abort(.notFound)) // returns Future<CountResult>

Here you can see two interesting things: Fn.count() and => "count"

Fn is a collection of function builders, so just call Fn. and take a look at the functions list on autocompletion.

=> uses for two things: 1) to write alias through as 2) to cast values to some other types

// TBD: Expand list of examples


Use => operator for that, e.g.:

If you want to write SELECT "User"."email" as eml then do it like this SwifQL.select(\User.email => "eml")

Or if to speak about table name aliasing:

If you want to reach "User" as u then do it like this User.as("u")

And then keypaths will work like

let u = User.as("u")
let emailKeypath = u.email

Type casting

Use => operator for that, e.g.:

If you want to write SELECT "User"."email"::text then do it like this SwifQL.select(\User.email => .text)


| Infix operator | SQL equivalent | | ------- | -------------- | | > | > | | >= | >= | | < | < | | <= | <= | | == | = | | == nil | IS NULL | | != | != | | != nil | IS NOT NULL | | && | AND |

And also

|| is for OR

||> is for @>

<|| is for <@

Please feel free to add more predicates in Predicates.swift πŸ˜‰


Please feel free to take a look at Fn.Operator enum in Functions.swift


Please feel free to take a look at the list of function in Functions.swift

Postgres JSON Object

You could build JSON objects by using PostgresJsonObject

SQL example

jsonb_build_object('id', "User"."id", 'email', "User"."email")

SwifQL representation

PgJsonObject().field(key: "id", value: \User.id).field(key: "email", value: \User.email)

Postgres Array

You could build PostgreSQL arrays by using PostgresArray

SQL example


SwifQL representation

PgArray(emptyMode: .dollar)
PgArray(1, 2, 3)
PgArray(emptyMode: .dollar) => .uuidArray
PgArray() => .textArray

Nesting array of objects inside of query result

Consider such response object you want to achieve:

struct Book {
  let title: String
  let authors: [Author]

struct Author {
  let name: String

you have to build it with use of subquery to dump Authors in JSON array and then attach them to result query. This will allow you to get all Books with their respective Authors

This example uses Pivot table BookAuthor to join Books with their Authors

    let authors = SwifQL.select(Fn.coalesce(Fn.array_agg(Fn.to_jsonb(Author.table)), PgArray() => .jsonbArray))

    let query = SwifQLSelectBuilder()


    query.join(.left, BookAuthor.table, on: \Book.$id == \BookAuthor.$bookID)
    query.join(.left, Author.table, on: \Author.$id == \BookAuthor.$authorID)

    // then query.group(...) as required in your case


SQL example

COUNT("User"."id") FILTER (WHERE \User.isAdmin = TRUE) as "admins"

SwifQL representation

Fn.count(\User.id).filter(where: \User.isAdmin == true) => "admins"

CASE ... WHEN ... THEN ... END

SQL example

  WHEN "User"."email" IS NULL
  ELSE "User"."email"

SwifQL representation

Case.when(\User.email == nil).then(nil).else(\User.email).end
// or as many cases as needed


Yes, we really often use round brackets in our queries, e.g. in where clauses or in subqueries.

SwifQL provides you with | prefix and postfix operators which is representates ( and ).

So it's easy to wrap some part of query into brackets, e.g.: SQL example

"User.role" = 'admin' OR ("User.role" = 'user' AND "User"."age" >= 21)

SwifQL representation

let where = \User.role == .admin || |\User.role == .user && \User.age >= 21|


| SQL | SwiftQL | SwiftQL + Bridges | | ------- | -------------- | -------------- | | "User" | User.table | the same | | "User" as u | User.as("u") you could declare it as let u = User.as("u") | the same | | "User".* | User.table.* | the same | | u.* | u.* | the same | | "User"."email" | \User.email | \User.$email | | u."email" | u.email | u.$email | | "User"."jsonObject"->"jsonField" | \User.jsonObject.jsonField | only through full path for now | | "User"."jsonObject"->"jsonField" | Path.Table("User").column("jsonObject", "jsonField") | the same |


For now tests coverage is maybe around 70%. If you have timΠ΅ and interest please feel free to send pull requests with more tests.

You could find tests in Tests folder

How it works under the hood

SwifQL object needed just to start writing query, but it's just an empty object that conforms to SwifQLable.

You can build your query with everything which conforms to SwifQLable, because SwifQLable is that very piece which will be used for concatenation to build a query.

If you take a look at the lib's files you may realize that the most of files are just extensions to SwifQLable.

All available operators like select, from, where, and orderBy realized just as a function in SwifQLable extension and these functions always returns SwifQLable as a result. That's why you can write a query by calling SwifQL.select().from().where().orderBy() one by one. That's awesome cause it feels like writing a raw SQL, but it also gives you an ordering limitation, so if you write SwifQL.select().where().from() then you'll get wrong query as a result. But this limitation is resolved by using special builders, like SwifQLSelectBuilder (read about it later below).

So let's take a look how lib builds a simple SELECT "User".* FROM "User" WHERE "User"."email" = 'john.smith@gmail.com' query

First of all we should split query into the parts. Almost every word and punctuation here is a SwifQLable piece.

  • SELECT is Fn.Operator.select
  • is Fn.Operator.space
  • "User" is User.table
  • .* is postfix operator .*
  • is Fn.Operator.space
  • FROM is Fn.Operator.from
  • "User" is User.table
  • is Fn.Operator.space
  • WHERE is Fn.Operator.where
  • is Fn.Operator.space
  • "User"."email" is \User.email keypath
  • is Fn.Operator.space
  • == is infix operator ==
  • is Fn.Operator.space
  • 'john.smith@gmail.com' is SwifQLPartUnsafeValue (it means that this value should be passed as $1 to the database)

That's crazy, but awesome, right? πŸ˜„ But it's under the hood, so no worries! πŸ˜ƒ I just wanted to explain, that if you need something more than already provided then you'll be able to add needed operators/functions easily just by writing little extensions.

And also there is no overhead, it works pretty fast, but I'd love to hear if you know how to make it faster.

This way gives you almost absolute flexibility in building queries. More than that as lib support SQLDialect's it will build this query different way for PostgreSQL and MySQL, e.g.:

  • PostgreSQL: SELECT "User".* FROM "User" WHERE "User"."email" = 'john.smith@gmail.com'
  • MySQL: SELECT User.* FROM User WHERE User.email = 'john.smith@gmail.com'


Please feel free to contribute!


Stars: 206


Used By

Total: 0


⚑️Conform `Operator` to `SwifQLable` - 2020-06-01 21:18:07

So now you will be able to use operators right in query like this


Implement `KeypathEncodable` - 2020-05-09 00:24:47

In case if you use @Column names with underscores but variable names are in camelCase you may experience problems when while encoding fetched data you're getting names with underscores.

To solve this problem now we have KeyPathEncodable protocol.

Simply conform your table to KeyPathEncodable and it will always be encoded with property names the same as variable names declared in your model.


To get as-is

final class User: Table {
    var id: Int
    var firstName: String

this will be encoded as

    "id": 1,
    "first_name": "John"

To get with variable names

final class User: Table, KeyPathEncodable {
    var id: Int
    var firstName: String

this will be encoded as

    "id": 1,
    "firstName": "John"

Implement Column, Alias, Table, and builders - 2020-04-22 23:31:28

Breaking changes

SwifQLAlias renamed to TableAlias

Now we're able to build table models

final class User: Table {
    var id: UUID

    var email: String

and access table columns through keyPath like this \User.$id and \User.$email

New @Alias property wrapper

struct Result: Aliasable {
    @Alias("anything") var emailAddress: String
let query = SwifQL.select(\User.$email => \Result.$emailAddress).from(User.table)
// SELECT "User"."email" as "anything" FROM "User"

Implement schema support 🍾 - 2020-04-13 00:32:19

Breaking change

Aliases syntax has been changed


let u = User.as("u")


let u = User.as("u")


schema for postgres is the same as database for mysql.

How to declare some schema

struct Deleted: Schemable {
    static var schemaName: String { "deleted" }

or right into your model (example for Bridges)

final class DeletedUser: Table, Schemable {
    static var schemaName: String { "deleted" }
    static var tableName: String { "user" }
    var id: UUID

    var email: String

Usage examples

// if `User` model conforms to `Schemable` it will use `public` schema all the time
\User.$id // result: "public"."user"."id"

// otherwise it will be printed simply without any schema
\User.$id // result: "user"."id"

// `DeletedUser` model conforms to `Schemable` and has custom schema name
\DeletedUser.$id // result: "deleted"."user"."id"

// Alternatively we can wrap any model to any schema
User.inSchema("deleted").$id // result: "deleted"."user"."id"

// also we can use aliases with tables with schemas
DeletedUser.as("du") // result: "deleted"."user" as "du"
// or
User.inSchema("deleted").as("du") // result: "deleted"."user" as "du"

New alias features

let u = User.as("u")

// to reach \User.$id we now can simply call
u.$id // result: "u"."id"

// the same for aliases with schemas
// declare aliases easily
let hu = User.inSchema("hello").as("hu")
// or even
struct HelloSchema: Schemable {
    static var schemaName: String { "hello" }
let hu = User.inSchema(HelloSchema.self).as("hu")

// use alias as table
hu.table // result: "hello"."user" as "hu"

// or simply call its columns
hu.$id // result: "hu"."id"

Custom aliases for subqueries

let u = SwifQLAlias("u")
let subQueryWithAlias = |SwifQL.select(User.table.*).from(User.table)| => u
// result: (SELECT "hello"."user".* FROM "hello"."user") as "u"

// and then also use its paths easily
u.id // result: "u"."id"
// even with any fantasy columns
u.heeeeey // result: "u"."heeeeey"

Add UNION and WITH (#19) - 2020-01-17 07:34:49

union functionality

let table1 = Table("Table1")
let table2 = Table("Table2")
let table3 = Table("Table3")

let sql = Union(

will give

(SELECT "Table1".* FROM "Table1")
(SELECT "Table2".* FROM "Table2")
(SELECT "Table3".* FROM "Table3"

with support

let with = With(

will give

    "Table1" as (SELECT "Table2".* FROM "Table2")
SELECT "Table1".* 
FROM "Table1"

Thanks to @hiimtmac

Make OrderBy.Direction public (#18) - 2020-01-08 13:28:54

In case when you want to set order direction in runtime now you could do it this way

SwifQL.orderBy(.direction(.asc, \User.email))
SwifQL.orderBy(.direction(.desc, \User.email))

Implement `Column` and `Table` - 2020-01-06 23:35:36

SwifQL.select(Column("id")) // SELECT "id"

SwifQL.select(Table("Todo")) // SELECT "Todo"

SwifQL.select(Table("Todo").column("id")) // SELECT "Todo"."id"

Implement `generate_series` by @hiimtmac - 2020-01-06 19:03:50

Example 1

SwifQL.select(Fn.generate_series(1, 4)

will generate

SELECT generate_series(1, 4)

Example 2

SwifQL.select(Fn.generate_series("2019-10-01", "2019-10-04", "1 day"))

will generate

SELECT generate_series('2019-10-01', '2019-10-04', '1 day')

Example 3

SwifQL.select(Fn.generate_series("2019-10-01" => .date, "2019-10-04" => .date, "1 day"))

will generate

SELECT generate_series('2019-10-01', '2019-10-04', '1 day')

Example 4

let df = DateFormatter()
df.dateFormat = "yyyy-MM-dd HH:mm:ss"
df.timeZone = TimeZone(secondsFromGMT: 0)
SwifQL.select(Fn.generate_series(df.date(from: "2019-10-01 00:00:00")!, df.date(from: "2019-10-04 00:00:00")!, "1 day"))

will generate

        (TIMESTAMP 'EPOCH' + make_interval(secs => 1569888000.0)), 
        (TIMESTAMP 'EPOCH' + make_interval(secs => 1570147200.0)), 
        '1 day'

πŸŽ‰ Refactored Fn and CastType - 2019-10-12 08:04:42

Now we have an ability to add custom functions and cast types with extensions like this

// Adds custom cast type
extension CastType {
    static var dayOfWeekArray: CastType = .init("dayofweek[]")
// Adds custom function name
extension Fn.Name {
    static var helloWorld: Fn.Name { return .init("hello_world") }
// Adds function builder method
extension Fn {
    static func row_to_json(_ record: SwifQLable, pretty: Bool? = nil) -> SwifQLable {
        var parts: [SwifQLPart] = record.parts
        if let pretty = pretty {
            parts.append(o: .comma)
            parts.append(o: .space)
            parts.append(safe: pretty)
        return build(.row_to_json, body: parts)

BREAKING: reimplement CASE WHEN THEN ELSE END - 2019-09-13 10:15:31

Now build it this way

Case.when(\CarBrands.name == "BMW")
    .then("Crazy driver")
    .when(\CarBrands.name == "Tesla")
    .then("Fancy driver")
    .else("Normal driver")

or this

    .then("Crazy driver")
    .then("Fancy driver")
    .else("Normal driver")

Closes #8

Implement `Date` support - 2019-08-02 21:08:35

For postgres it takes timeIntervalFrom1970 and converts it into TIMESTAMP WITHOUT TIMEZONE Fox mysql it takes timeIntervalFrom1970 and converts it into TIMESTAMP by using FROM_UNIXTIME function

So now you can easily use dates in your queries like this

SwifQL.select(Date() => "currentTime")
SwifQL.select(User.table.*).from(User.table).where(\User.createdAt > Date(timeIntervalSinceNow: -86400))

Improve `FormattedKeyPath` - 2019-07-26 12:39:20

Now you also could build keypath manually like this CarBrands.mkp("id")

Support aliases in SELECT - 2019-07-24 07:21:44

Implement =>"aliasName" to have an ability to use aliases as select params

So if you want to use some alias in SELECT please write it as String with => prefix like this

SwifQL.select("hello", =>"aaa")
      .from(|SwifQL.select(\CarBrands.name).from(CarBrands.table))| => "aaa"

to reach

SELECT 'hello', "aaa" FROM (SELECT "CarBrands"."name" FROM "CarBrands") as "aaa"

Implement `FormattedKeyPath` - 2019-06-20 15:07:12

In complex situations you could use it to reach complex paths



Normally in Vapor it should work with


but device should conform to ReflectionDecodable for that...

The new workaround is to use FormattedKeyPath like this

FormattedKeyPath(User.self, "device", "model")

Implement `Tableable` protocol - 2019-03-24 16:50:19

For Vapor

Optionally conform your models to SwifQLTable or to just Tableable protocol to be sure that SwifQL takes the right table name. Without conforming it will use String(describing: Self.self) as a table name.

For pure swift

Please conform all your models to SwifQLTable

Implement ability to append something wrapped into square brackets - 2019-03-20 13:19:59

// (array_agg(to_jsonb("Attachment")))
let someArray = |Fn.array_agg(Fn.to_jsonb("Attachment"))|
// (array_agg(to_jsonb("Attachment")))[1]
let query = someArray[1]

Added Postgres time functions and `between` infix operator - 2019-03-08 00:08:14

So now you can build queries with time functions like this

// SELECT '2001-09-28'::date + 7::integer
SwifQL.select("2001-09-28" => .date + 7 => .integer)

or like this

// SELECT * FROM "User" WHERE "birthDay" BETWEEN now() AND now() + '24 hours'::interval
      .where(\User. birthDay <> Fn.now() && Fn.now() + "24 hours" => .interval)


0.7.2: Change `.join` syntax by adding `on` keyword - 2019-03-01 12:40:26

⚠️ This may broke your project, please change your joins from

.join(.left, Role.table, \User.role == \Role.id)


.join(.left, Role.table, on: \User.role == \Role.id)

This syntax just feels more natural.