SQL database abstraction layer
Swift-Kuery is a pluggable SQL database driver/SDK abstraction layer. Its main idea is to unify the APIs to the various relational databases, providing a Swifty yet SQL-like API.
While Swift-Kuery is not an Object-Relational Mapping (ORM), it provides a great basis to build an ORM. It is also useful if you don't want to commit to a specific database, allowing easy switch between databases.
Swift-Kuery is easy to learn, consumable framework that comes with a set of implemented plugins.
This example demonstrates how to execute an SQL query using Swift-Kuery and Swift-Kuery-PostgreSQL. It assumes there is a PostgreSQL server running on localhost:5432, that contains a table called Grades:
id | course | grade
------+-----------+-------
12345 | physics | 82
12345 | chemistry | 90
12345 | history | 98
78901 | history | 100
78901 | chemistry | 94
78901 | physics | 90
24680 | physics | 74
24680 | chemistry | 92
24680 | history | 90
First we import Swift-Kuery and Swift-Kuery-PostgreSQL:
import SwiftKuery
import SwiftKueryPostgreSQL
Now we create a Table
that corresponds to our Grades table in PostgreSQL - we set the table's name and its columns:
class Grades: Table {
let tableName = "Grades"
let id = Column("id")
let course = Column("course")
let grade = Column("grade")
}
let grades = Grades()
Next we create a pool of connections to PostgreSQL:
let pool = PostgreSQLConnection.createPool(host: "localhost", port: 5432, options: [.userName("username"), .password("password")], poolOptions: ConnectionPoolOptions(initialCapacity: 10, maxCapacity: 50, timeout: 10000)))
Every time we need a connection, we get it from the pool:
if let connection = pool.getConnection() {
// Build and execute your query here.
}
else {
print("Error: failed to get a connection.")
}
Now lets build the query. Suppose we want to retrieve the average grades for courses with average greater than 90, and sort the results by the average ascending. Here is the SQL query we need to build:
SELECT course, ROUND(AVG(grade), 1) AS "average" FROM grades
GROUP BY course
HAVING AVG(grade) > 90
ORDER BY AVG(grade) ASC
Note, that we also round the average grades and alias this column in the result as "average".
Here is how to create such query using Swift-Kuery:
let query = Select(grades.course, round(avg(grades.grade), to: 1).as("average"), from: grades)
.group(by: grades.course)
.having(avg(grades.grade) > 90)
.order(by: .ASC(avg(grades.grade)))
As you can see, it is very similar to the SQL query syntax.
Now we execute the created query on our PostgreSQL connection:
guard let connection = pool.getConnection() else {
// Error
}
connection.execute(query: query) { result: QueryResult in
if let resultSet = queryResult.asResultSet {
for title in resultSet.titles {
// The column names of the result.
}
for row in resultSet.rows {
for value in row {
...
}
}
}
else if let queryError = result.asError {
// Something went wrong.
}
}
The expected result is:
course average
chemistry 92.0
history 96.0
If your application executes some query multiple times with different parameters, you may want to improve the performance of the application by using a prepared statement for that query. I.e. send the query to the database in advance, and later use the returned handle to execute the query providing the parameters. This way the database server will process the query only once.
For example, suppose our application needs to retrieve the average grade for courses with an average above various values. We change our query to use a parameter instead of a preset value of 90:
let query = Select(grades.course, round(avg(grades.grade), to: 1).as("average"), from: grades)
.group(by: grades.course)
.having(avg(grades.grade) > Parameter())
.order(by: .ASC(avg(grades.grade)))
Now we prepare the statement:
do {
let preparedStatement = try connection.prepareStatement(query)
}
catch {
// Error.
}
Note: preparedStatement
is a plugin-specific handle for the prepared statement.
Now we can use this handle to execute the query with different parameters without creating it every time:
connection.execute(preparedStatement: preparedStatement, parameters: [70]) { result in
...
}
Use the release
function to free the prepared statement:
connection.release(preparedStatement: preparedStatement) { result in
...
}
Swift-Kuery enables you to create tables on the database server.
Let's rewrite our Grades
table by adding columns type and constraints:
class Grades: Table {
let tableName = "Grades"
let id = Column("id", Char.self, length: 6, primaryKey: true)
let course = Column("course", Varchar.self, length: 50)
let grade = Column("grade", Int16.self, check: "grade >= 0")
}
We can add a foreign key to Grades
that references a column in another table:
let grades = Grades().foreignKey(grades.course, references: courses.name)
And create a multi-column primary key (if not set in the column as for Grades.id
):
grades.primaryKey(grades.id, grades.course)
To create the table in the database, do:
grades.create(connection: connection) { result in
guard result.success else {
print("Failed to create table: \(result.asError?)")
}
...
}
You can manage indices with Swift-Kuery in the following way:
let index = Index("index", on: grades, columns: [grades.id, desc(grades.grade)])
index.create(connection: connection) { result in ... }
...
index.drop(connection: connection) { result in ... }
Swift-Kuery has a class Migration
to help with migrations between two versions of a table.
Suppose we have a table MyTable
in our application. The suggested usage is to keep versions of the table classes somewhere in the application code:
public class MyTable_v0: Table {
let a = Column("a", ...)
let b = Column("b", ...)
let tableName = "MyTable"
}
public class MyTable_v1: Table {
let b = Column("b", ...)
let c = Column("c", ...)
let tableName = "MyTable"
}
And use a typealias to refer to the current version of the table class in the application:
typealias MyTable = MyTable_v0
let t = MyTable()
let q = Select(from t)
...
The migration code from v0 to v1 should be something like this:
let t0 = MyTable_v0()
let t1 = MyTable_v1()
let migration0 = Migration(from: t0, to: t1, using: connection)
migration0.alterTableAdd(column: t1.c) { result in ... }
And raw alternations, if needed:
let dropColumnQuery = "ALTER TABLE " + t1.tableName + " DROP COLUMN " + t0.a.name
connection.execute(dropColumnQuery) { result in ... }
Let's see more examples of how to build and execute SQL queries using Swift-Kuery.
class T1 {
let tableName = "t1"
let a = Column("a")
let b = Column("b")
}
class T2 {
let tableName = "t2"
let c = Column("c")
let b = Column("b")
}
SELECT * FROM t1;
let t1 = T1()
let s = Select(from: t1)
guard let connection = pool.getConnection() else {
// Error
}
s.execute(connection) { queryResult in
if let resultSet = queryResult.asResultSet {
for title in resultSet.titles {
...
}
for row in resultSet.rows {
for value in row {
...
}
}
}
else if let queryError = result.asError {
...
}
}
SELECT a, b FROM t1
WHERE (a LIKE '%b' OR a = 'apple') AND b > 5
ORDER BY b ASC, a DESC
OFFSET 5;
...
let s = Select(t1.a, t1.b, from: t1)
.where((t1.a.like("b%") || t1.a == "apple") && t1.b > 5)
.order(by: .ASC(t1.b), .DESC(t1.a))
.offset(5)
connection.execute(query: s) { queryResult in
...
}
SELECT UCASE(a) AS name FROM t1
WHERE b >= 0
GROUP BY a
HAVING SUM(b) > 3
ORDER BY a DESC;
...
let s = Select(ucase(t1.a).as("name"), from: t1)
.where(t1.b >= 0)
.group(by: t1.a)
.having(sum(t1.b) > 3)
.order(by: .DESC(t1.a))
...
INSERT INTO t1
VALUES ('apple', 10), ('apricot', 3), ('banana', 17);
...
let i = Insert(into: t1, rows: [["apple", 10], ["apricot", 3], ["banana", 17]])
connection.execute(query: i) { queryResult in
if queryResult.success {
...
}
else if let queryError = result.asError {
...
}
}
INSERT INTO t1
VALUES ('apple', 10);
...
let i = Insert(into: t1, values: "apple", 10)
...
INSERT INTO t1 (a, b)
VALUES ('apricot', '3');
...
let i = Insert(into: t1, valueTuples: (t1.a, "apricot"), (t1.b, "3"))
...
INSERT INTO t1 (a, b)
VALUES ('apricot', '3');
...
let i = Insert(into: t1, columns: [t1.a, t1.b], values: ["apricot", 3])
...
UPDATE t1 SET a = 'peach', b = 2
WHERE a = 'banana';
...
let u = Update(t1, set: [(t1.a, "peach"), (t1.b, 2)])
.where(t1.a == "banana")
...
SELECT * FROM t1 AS left
LEFT JOIN t2 AS right
ON left.b = right.b;
let t1 = T1()
let t2 = T2()
let leftTable = t1.as("left")
let rightTable = t2.as("right")
let s2 = Select(from: leftTable)
.leftJoin(rightTable)
.on(leftTable.b == rightTable.b)
...
SELECT * FROM t1
JOIN t2
USING (b);
...
let s2 = Select(from: t1)
.join(t2)
.using(t1.b)
...
Note: Named parameters are supported for all databases, even for those that do not support named parameters (e.g. PostgreSQL).
INSERT INTO t1
VALUES (@0,@1);
let i = Insert(into: t1, values: Parameter(), Parameter())
connection.execute(query: i1, parameters: "banana", 28) { queryResult in
...
}
INSERT INTO t1
VALUES (@fruit,@number);
let i = Insert(into: t1, values: Parameter("fruit"), Parameter("number"))
connection.execute(query: i1, parameters: ["number" : 28, "fruit" : "banana"]) { queryResult in
...
}
It is possible to insert NULL values using parameters:
connection.execute(query: i1, parameters: ["number" : 28, "fruit" : nil]) { queryResult in
...
}
Raw query:
connection.execute("CREATE TABLE myTable (a varchar(40), b integer)") { queryResult in
...
}
SELECT LEFT(a, 2) as raw FROM t1
WHERE b >= 0
GROUP BY a
HAVING sum(b) > 3
ORDER BY a DESC;
...
let s = Select(RawField("LEFT(a, 2) as raw"), from: t1)
.where("b >= 0")
.group(by: t1.a)
.having("sum(b) > 3")
.order(by: .DESC(t1.a))
...
SELECT * FROM t1
WHERE b >= ANY (SELECT b FROM t2);
...
let s = Select(from: t1)
.where(t1.b >= any(Select(t2.b, from: t2)))
...
SELECT * FROM t1
WHERE NOT EXISTS (SELECT * FROM t2 WHERE b < 8);
...
let s = Select(from: t1)
.where(notExists(Select(from: t2).where(t2.b < 8)))
...
SELECT c FROM t2
GROUP BY c
HAVING SUM(b) NOT IN (SELECT b FROM t1 WHERE a = 'apple');
...
let s = Select(t2.c, from: t2)
.group(by: t2.c)
.having(sum(t2.b).notIn(Select(t1.b, from: t1).where(t1.a == "apple")))
...
This library is licensed under Apache 2.0. Full license text is available in LICENSE.