• Stars
    star
    148
  • Rank 249,931 (Top 5 %)
  • Language
    Swift
  • License
    MIT License
  • Created over 6 years ago
  • Updated over 4 years ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

๐Ÿ—ƒ Powerful and easy to use Swift Query Builder for Vapor 3.

Mihael Isaev

MIT License Swift 4.1 Swift.Stream


โš ๏ธ This lib is DEPRECATED โš ๏ธ please use SwifQL with Bridges

Quick Intro

struct PublicUser: Codable {
    var name: String
    var petName: String
    var petType: String
    var petToysQuantity: Int
}
try FQL()
    .select(all: User.self)
    .select(\Pet.name, as: "petName")
    .select(\PetType.name, as: "petType")
    .select(.count(\PetToy.id), as: "petToysQuantity")
    .from(User.self)
    .join(.left, Pet.self, where: \Pet.id == \User.idPet)
    .join(.left, PetType.self, where: \PetType.id == \Pet.idType)
    .join(.left, PetToy.self, where: \PetToy.idPet == \Pet.id)
    .groupBy(\User.id, \Pet.id, \PetType.id, \PetToy.id)
    .execute(on: conn)
    .decode(PublicUser.self) // -> Future<[PublicUser]> ๐Ÿ”ฅ๐Ÿ”ฅ๐Ÿ”ฅ

Intro

It's a swift lib that gives ability to build complex raw SQL-queries in a more easy way using KeyPaths. I call it FQL ๐Ÿ˜Ž

Built for Vapor3 and depends on Fluent package because it uses Model.reflectProperty(forKey:) method to decode KeyPaths.

Install through Swift Package Manager

Edit your Package.swift

//add this repo to dependencies
.package(url: "https://github.com/MihaelIsaev/FluentQuery.git", from: "0.4.30")
//and don't forget about targets
//"FluentQuery"

One more little intro

I love to write raw SQL queries because it gives ability to flexibly use all the power of database engine.

And Vapor's Fleunt allows you to do raw queries, but the biggest problem of raw queries is its hard to maintain them.

I faced with that problem and I started developing this lib to write raw SQL queries in swift-way by using KeyPaths.

And let's take a look what we have :)

How it works

First of all you need to import the lib

import FluentQuery

Then create FQL object, build your SQL query using methods described below and as first step just print it as a raw string

let query = FQL()
//some building
print("rawQuery: \(query)")

Several examples

1. Simple

// SELECT * FROM "User" WHERE age > 18
let fql = FQL().select(all: User.self)
               .from(User.self)
               .where(\User.age > 18)
               .execute(on: conn)
               .decode(User.self)

2. Simple with join

// SELECT u.*, r.name as region FROM "User" as u WHERE u.age > 18 LEFT JOIN "UserRegion" as r ON u.idRegion = r.id
let fql = FQL().select(all: User.self)
               .select(\UserRegion.name)
               .from(User.self)
               .where(\User.age > 18)
               .join(.left, UserRegion.self, where: \User.idRegion == \UserRegion.id)
               .execute(on: conn)
               .decode(UserWithRegion.self)

3. Medium ๐Ÿ™‚ with query into jsonB obejcts

// SELECT (SELECT to_jsonb(u)) as user, (SELECT to_jsonb(r)) as region FROM "User" as u WHERE u.age > 18 LEFT JOIN "UserRegion" as r ON u.idRegion = r.id
let fql = FQL().select(.row(User.self), as: "user")
               .select(.row(UserRegion.self), as: "region")
               .from(User.self)
               .where(\User.age > 18)
               .join(.left, UserRegion.self, where: \User.idRegion == \UserRegion.id)
               .execute(on: conn)
               .decode(UserWithRegion.self)
// in this case UserWithRegion struct will look like this
struct UserWithRegion: Codable {
    var user: User
    var region: UserRegion
}

4. Complex

Let's take a look how to use it with some example request

Imagine that you have a list of cars

So you have Car fluent model

final class Car: Model {
  var id: UUID?
  var year: String
  var color: String
  var engineCapacity: Double
  var idBrand: UUID
  var idModel: UUID
  var idBodyType: UUID
  var idEngineType: UUID
  var idGearboxType: UUID
}

and related models

final class Brand: Decodable {
  var id: UUID?
  var value: String
}
final class Model: Decodable {
  var id: UUID?
  var value: String
}
final class BodyType: Decodable {
  var id: UUID?
  var value: String
}
final class EngineType: Decodable {
  var id: UUID?
  var value: String
}
final class GearboxType: Decodable {
  var id: UUID?
  var value: String
}

ok, and you want to get every car as convenient codable model

struct PublicCar: Content {
  var id: UUID
  var year: String
  var color: String
  var engineCapacity: Double
  var brand: Brand
  var model: Model
  var bodyType: BodyType
  var engineType: EngineType
  var gearboxType: GearboxType
}

Here's example request code for that situation

func getListOfCars(_ req: Request) throws -> Future<[PublicCar]> {
  return req.requestPooledConnection(to: .psql).flatMap { conn -> EventLoopFuture<[PublicCar]> in
      defer { try? req.releasePooledConnection(conn, to: .psql) }
      return FQL()
        .select(distinct: \Car.id)
        .select(\Car.year, as: "year")
        .select(\Car.color, as: "color")
        .select(\Car.engineCapacity, as: "engineCapacity")
        .select(.row(Brand.self), as: "brand")
        .select(.row(Model.self), as: "model")
        .select(.row(BodyType.self), as: "bodyType")
        .select(.row(EngineType.self), as: "engineType")
        .select(.row(GearboxType.self), as: "gearboxType")
        .from(Car.self)
        .join(.left, Brand.self, where: \Brand.id == \Car.idBrand)
        .join(.left, Model.self, where: \Model.id == \Car.idModel)
        .join(.left, BodyType.self, where: \BodyType.id == \Car.idBodyType)
        .join(.left, EngineType.self, where: \EngineType.id == \Car.idEngineType)
        .join(.left, GearboxType.self, where: \GearboxType.id == \Car.idGearboxType)
        .groupBy(\Car.id, \Brand.id, \Model.id, \BodyType.id, \EngineType.id, \GearboxType.id)
        .orderBy(.asc(\Brand.value), .asc(\Model.value))
        .execute(on: conn)
        .decode(PublicCar.self)
  }
}

Hahah, that's cool right? ๐Ÿ˜ƒ

As you can see we've build complex query to get all depended values and decoded postgres raw response to our codable model.

BTW, this is a raw SQL equivalent
SELECT
DISTINCT c.id,
c.year,
c.color,
c."engineCapacity",
(SELECT toJsonb(brand)) as "brand",
(SELECT toJsonb(model)) as "model",
(SELECT toJsonb(bt)) as "bodyType",
(SELECT toJsonb(et)) as "engineType",
(SELECT toJsonb(gt)) as "gearboxType"
FROM "Cars" as c
LEFT JOIN "Brands" as brand ON c."idBrand" = brand.id
LEFT JOIN "Models" as model ON c."idModel" = model.id
LEFT JOIN "BodyTypes" as bt ON c."idBodyType" = bt.id
LEFT JOIN "EngineTypes" as et ON c."idEngineType" = et.id
LEFT JOIN "GearboxTypes" as gt ON c."idGearboxType" = gt.id
GROUP BY c.id, brand.id, model.id, bt.id, et.id, gt.id
ORDER BY brand.value ASC, model.value ASC

So why do you need to use this lib for your complex queries?

The reason #1 is KeyPaths!

If you will change your models in the future you'll have to remember where you used links to this model properties and rewrite them manually and if you forgot one you will get headache in production. But with KeyPaths you will be able to compile your project only while all links to the models properties are up to date. Even better, you will be able to use refactor functionality of Xcode! ๐Ÿ˜„

The reason #2 is if/else statements

With FQL's query builder you can use if/else wherever you need. And it's super convenient to compare with using if/else while createing raw query string. ๐Ÿ˜‰

The reason #3

It is faster than multiple consecutive requests

The reason #4

You can join on join on join on join on join on join ๐Ÿ˜๐Ÿ˜๐Ÿ˜

With this lib you can do real complex queries! ๐Ÿ”ฅ And you still flexible cause you can use if/else statements while building and even create two separate queries with the same basement using let separateQuery = FQL(copy: originalQuery) ๐Ÿ•บ

Methods

The list of the methods which FQL provide with

Select

These methods will add fields which will be used between SELECT and FROM

SELECT _here_some_fields_list_ FROM

So to add what you want to select call these methods one by one

Method SQL equivalent
.select("*") *
.select(all: Car.self) "Cars".*
.select(all: someAlias) "some_alias".*
.select(\Car.id) "Car".id
.select(someAlias.k(.id)) "some_alias".id
.select(distinct: \Car.id) DISTINCT "Car".id
.select(distinct: someAlias.k(.id)) DISTINCT "some_alias".id
.select(.count(\Car.id), as: "count") COUNT("Cars".id) as "count"
.select(.sum(\Car.value), as: "sum") SUM("Cars".value) as "sum"
.select(.average(\Car.value), as: "average") AVG("Cars".value) as "average"
.select(.min(\Car.value), as: "min") MIN("Cars".value) as "min"
.select(.max(\Car.value), as: "max") MAX("Cars".value) as "max"
.select(.extract(.day, .timestamp, \Car.createdAt), as: "creationDay") EXTRACT(DAY FROM "Cars".value) as "creationDay"
.select(.extract(.day, .interval, "40 days 1 minute"), as: "creationDay") EXTRACT(DAY FROM INTERVAL '40 days 1 minute') as "creationDay"
.select(by: .rowNumber, over: FQOver, as: "rowNumber") rowNumber() OVER (partition BY EXPRESSION ORDER BY SOMETHING) as "rowNumber"

BTW, read about aliases and FQOver below

Window functions

If you need to use window functions like rowNumber, rank, dense_rank, etc. like this

rowNumber() OVER(partition BY "Record".title, "Record".tag ORDER BY "Record".priority ASC) as "rowNumber"

(refer to: https://www.postgresql.org/docs/current/static/functions-window.html)

then you could build it like this

let fqo = FQOver(.partition)
            .by(\Record.title, \Record.tag)
            .orderBy(.asc(\Record.priority))

and then use it in your query like this

let FQL()
    .select(\Record.id)
    .select(by: .rowNumber, over: fqo, as: "rowNumber")
    .from(Record.self)

From

Method SQL equivalent
.from("Table") FROM "Table"
.from(raw: "Table") FROM Table
.from(Car.self) FROM "Cars" as "cars"
.from(someAlias) FROM "SomeAlias" as "someAlias"

Join

.join(FQJoinMode, Table, where: FQWhere)

enum FQJoinMode {
    case left, right, inner, outer
}

As Table you can put Car.self or someAlias

About FQWhere please read below

Where

.where(FQWhere)

You can write where predicate two ways

First is object oriented

FQWhere(predicate).and(predicate).or(predicate).and(FQWhere).or(FQWhere)

Second is predicate oriented

Example for AND statements

\User.email == "[email protected]" && \User.password == "qwerty" && \User.active == true

Example for OR statements

\User.email == "[email protected]" || \User.email == "[email protected]" || \User.email == "[email protected]"

Example for both AND and OR statements

\User.email == "[email protected]" && FQWhere(\User.role == .admin || \User.role == .staff)

What FQWhere() doing here? It groups OR statements into round brackets to achieve a AND (b OR c) sql code.

What predicate is?

It may be KeyPath operator KeyPath or KeyPath operator Value

KeyPath may be \Car.id or someAlias.k(\.id)

Value may be any value like int, string, uuid, array, or even something optional or nil

List of available operators you saw above in cheatsheet

Some examples

FQWhere(someAlias.k(\.deletedAt) == nil)
FQWhere(someAlias.k(\.id) == 12).and(\Car.color ~~ ["blue", "red", "white"])
FQWhere(\Car.year == "2018").and(\Brand.value !~ ["Chevrolet", "Toyota"])
FQWhere(\Car.year != "2005").and(someAlias.k(\.engineCapacity) > 1.6)
Where grouping example

if you need to group predicates like

"Cars"."engineCapacity" > 1.6 AND ("Brands".value LIKE '%YO%' OR "Brands".value LIKE '%ET')

then do it like this

FQWhere(\Car.engineCapacity > 1.6).and(FQWhere(\Brand.value ~~ "YO").or(\Brand.value ~= "ET"))
Cheatsheet
Operator SQL equivalent Description
== == / IS Equals
!= != / IS NOT Not equals
> > Greater than
< < Less than
>= >= Greater or equal
<= <= Less or equal
~~ IN () In array
!~ NOT IN () Not in array
~= LIKE '%str' Case sensitive text search
~~ LIKE '%str%'
=~ LIKE 'str%'
~% ILIKE '%str' Case insensitive text search
%% ILIKE '%str%'
%~ ILIKE 'str%'
!~= NOT LIKE '%str' Case sensitive text search where text not like string
!~~ NOT LIKE '%str%'
!=~ NOT LIKE 'str%'
!~% NOT ILIKE '%str' Case insensitive text search where text not like string
!%% NOT ILIKE '%str%'
!%~ NOT ILIKE 'str%'
~~~ @@ 'str' Full text search

Having

.having(FQWhere)

About FQWhere you already read above, but as having calls after data aggregation you may additionally filter your results using aggreagate functions such as SUM, COUNT, AVG, MIN, MAX

.having(FQWhere(.count(\Car.id) > 0))
//OR
.having(FQWhere(.count(someAlias.k(\.id)) > 0))
//and of course you an use .and().or().groupStart().groupEnd()

Group by

.groupBy(\Car.id, \Brand.id, \Model.id)

or

.groupBy(FQGroupBy(\Car.id).and(\Brand.id).and(\Model.id))

or

let groupBy = FQGroupBy(\Car.id)
groupBy.and(\Brand.id)
groupBy.and(\Model.id)
.groupBy(groupBy)

Order by

.orderBy(FQOrderBy(\Car.year, .asc).and(someAlias.k(\.name), .desc))

or

.orderBy(.asc(\Car.year), .desc(someAlias.k(\.name)))

Offset

Method SQL equivalent
.offset(0) OFFSET 0

Limit

Method SQL equivalent
.limit(30) LIMIT 30

JSON

You can build json on jsonb object by creating FQJSON instance

Instance SQL equivalent
FQJSON(.normal) build_json_object()
FQJSON(.binary) build_jsonb_object()

After creating instance you should fill it by calling .field(key, value) method like

FQJSON(.binary).field("brand", \Brand.value).field("model", someAlias.k(\.value))

as you may see it accepts keyPaths and aliased keypaths

but also it accept function as value, here's the list of available functions

Function SQL equivalent
row(Car.self) SELECT row_to_json("Cars")
row(someAlias) SELECT row_to_json("some_alias")
extractEpochFromTime(\Car.createdAt) extract(epoch from "Cars"."createdAt")
extractEpochFromTime(someAlias.k(.createdAt)) extract(epoch from "some_alias"."createdAt")
count(\Car.id) COUNT("Cars".id)
count(someAlias.k(.id)) COUNT("some_alias".id)
countWhere(\Car.id, FQWhere(\Car.year == "2012")) COUNT("Cars".id) filter (where "Cars".year == '2012')
countWhere(someAlias.k(.id), FQWhere(someAlias.k(.id) > 12)) COUNT("some_alias".id) filter (where "some_alias".id > 12)

Aliases

FQAlias<OriginalClass>(aliasKey) or OriginalClass.alias(aliasKey)

Also you can use static alias OriginalClass.alias if you need only one its variation

And you can generate random alias OriginalClass.randomAlias but keep in mind that every call to randomAlias generates new alias as it's computed property

What's that for?

When you write complex query you may have several joins or subqueries to the same table and you need to use aliases for that like "Cars" as c

Usage

So with FQL you can create aliases like this

//"CarBrand" as b
let aliasBrand = CarBrand.alias("b")
//"CarModel" as m
let aliasModel = CarModel.alias("m")
//"EngineType" as e
let aliasEngineType = EngineType.alias("e")

and you can use KeyPaths of original tables referenced to these aliases like this

aliasBrand.k(\.id)
aliasBrand.k(\.value)
aliasModel.k(\.id)
aliasModel.k(\.value)
aliasEngineType.k(\.id)
aliasEngineType.k(\.value)

Executing query

.execute(on: PostgreSQLConnection)

try FQL().select(all: User.self).execute(on: conn)

Decoding query

.decode(Decodable.Type, dateDecodingstrategy: JSONDecoder.DateDecodingStrategy?)

try FQL().select(all: User.self).execute(on: conn).decode(PublicUser.self)

Custom DateDecodingStrategy

By default date decoding strategy is yyyy-MM-dd'T'HH:mm:ss.SSS'Z' which is compatible with postgres timestamp

But you can specify custom DateDecodingStrategy like this

try FQL().select(all: User.self).execute(on: conn).decode(PublicUser.self, dateDecodingStrategy: .secondsSince1970)

or like this

let formatter = DateFormatter()
formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
try FQL().select(all: User.self).execute(on: conn).decode(PublicUser.self, dateDecodingStrategy: .formatted(formatter))

or if you have two or more columns with different date format in the same model then you could create your own date formatter like described in issue #3

Conslusion

I hope that it'll be useful for someone.

Feedback is really appreciated!

And don't hesitate to asking me questions, I'm ready to help in Vapor's discord chat find me by @iMike nickname.

More Repositories

1

UIKitPlus

๐Ÿฐ Declarative UIKit with LivePreview for iOS9+ (best alternative to SwiftUI)
Swift
593
star
2

FCM

โšก๏ธ PushNotifications through FireBase for Vapor 3 and 4.
Swift
120
star
3

VaporCron

โฒ Swift cron scheduler for Vapor
Swift
85
star
4

AwesomeWS

๐Ÿฌ An elegant way to use websockets with Vapor
Swift
44
star
5

wkhtmltopdf

๐Ÿ“– PDF render for Swift and Vapor 3.
Swift
38
star
6

SwiftPopTipView

๐Ÿ’ฌ Popups for iOS
Swift
37
star
7

SwifCron

โฑSimple pure swift cron expression parser
Swift
29
star
8

NIOCronScheduler

โŒš๏ธSwift cron scheduler based on Swift NIO (both v1 and v2)
Swift
24
star
9

UIKitPlusExample

An example project for UIKitPlus library ๐Ÿ‘
Swift
24
star
10

BeautifulButtons

โค๏ธ iOS5 beautiful buttons
Objective-C
21
star
11

SwifRootViewController

๐Ÿ€Useful root navigation view controller (by best practice)
Swift
15
star
12

Localizer

๐Ÿ‡ฎ๐Ÿ‡ธ๐Ÿ‡ฉ๐Ÿ‡ช๐Ÿ‡ฏ๐Ÿ‡ต๐Ÿ‡ฒ๐Ÿ‡ฝ Swift localization helper
Swift
14
star
13

MultiSelectSegmentedControl

A swift representation of yonat/MultiSelectSegmentedControl
Swift
12
star
14

MaskedEditTextWatcher

Custom TextWatcher for EditText to mask phone number on-the-fly even with automatic country mask detection
Java
11
star
15

ECSwiftSlidingViewController

Translated to Swift ECSlidingViewController
Swift
10
star
16

Vaporizer

โœˆ๏ธ Vapor 4 declarative wrapper
Swift
8
star
17

State

Swift @State
Swift
7
star
18

AlexoChat

A simple realtime chat written in Swift on Vapor 3
Swift
7
star
19

ChatInMacOSX

Chat for inmac.org
Objective-C
7
star
20

braintree_swift

๐Ÿ’ฐNot official Swift Braintree provider for Vapor 3
Swift
7
star
21

branch.io.spm

Branch.io iOS SDK with SPM support (cleaned from `framework` files)
Objective-C
4
star
22

RealmQuantityGetterBug

This project reproduce Realm getter/setter glitch
Objective-C
2
star
23

SwifQLNIO

Helper library for SwifQL and SwiftNIO
Swift
2
star
24

SignalAppUIKitPlus

Demo application for UIKitPlus
Swift
2
star
25

VaporAvtokod

Avtokod.ru provider for Vapor 3
Swift
2
star
26

Branch

Branch.io API wrapper for Vapor4
Swift
2
star
27

VKPlayer

Music player for vk.com
Objective-C
1
star
28

MihaelIsaev

1
star
29

VaporDocs

An alternative documentation ๐Ÿ™‚
1
star
30

SimpleStreamVideoPlayer

Swift
1
star
31

pwa.github.io

HTML
1
star
32

VaporAutostat

Server-side Swift library for autostat.ru https://www.autostat.ru/help/pricecalc/
Swift
1
star
33

BridgesSubqueryArrayExample

1
star
34

CoreDataTempContext

The swift class to get the temp copy of current NSManagedObjectContext, use it and merge it
1
star