• Stars
    star
    426
  • Rank 101,874 (Top 3 %)
  • Language
    Swift
  • License
    Apache License 2.0
  • Created about 8 years ago
  • Updated about 3 years ago

Reviews

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

Repository Details

SQL database abstraction layer

Kitura

APIDoc Build Status - Master macOS Linux Apache 2 Slack Status

Swift-Kuery

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. This allows easy switching between databases and forms the basis for an Object-Relational Mapping (ORM) framework.

Swift-Kuery-ORM is an ORM, built on top of Swift-Kuery, which allows you to simplify the persistence of model objects with your server.

Swift-Kuery is an easy to learn, consumable framework that comes with a set of implemented plugins.

Table of Contents

Swift version

The latest version of Swift-Kuery requires Swift 4.0 or newer. You can download this version of the Swift binaries by following this link. Compatibility with other Swift versions is not guaranteed.

Usage

This example demonstrates how to execute an SQL query using Swift-Kuery with the Swift-Kuery-PostgreSQL plugin.

The starting point for this example is an existing Swift package. If you don't have one already, create and enter a directory named e.g. SwiftKueryExample. Now run the swift package's init command, to create an executable type, by running swift package init --type executable.

Creating A PostgreSQL Database

  1. Install PostgreSQL

Mac

brew install postgresql

Ubuntu Linux

sudo apt-get install postgresql postgresql-contrib

  1. Create a school database

    createdb school
    psql school
    
  2. Create a grades table

    CREATE TABLE "Grades" (
        id varchar(100) PRIMARY KEY,
        course text NOT NULL,
        grade integer
    );  
    

Update your Package.swift file

Add Swift-Kuery and your Kuery plugin, in this case Swift-Kuery-PostgreSQL, to the dependencies within your application's Package.swift file. Substitute "x.x.x" with the latest Swift-Kuery release and "y.y.y" with the latest plugin release.

dependencies: [
    ...
    // Add this line
    .package(url: "https://github.com/Kitura/Swift-Kuery.git", from: "x.x.x"),
    .package(url: "https://github.com/Kitura/Swift-Kuery-PostgreSQL.git", from: "y.y.y"),
  ],
  targets: [
    .target(
      name: ...
      // Add the module to your target(s)
      dependencies: [..., "SwiftKuery", "SwiftKueryPostgreSQL"]),
  ]

Executing SQL queries

Inside the main.swift file:

  1. Add SwiftKuery and SwiftKueryPostgreSQL to your import statements:
import SwiftKuery
import SwiftKueryPostgreSQL
  1. Create a Table class, which matches the grades table you created in the database:
class Grades: Table {
    let tableName = "Grades"
    let id = Column("id", Int32.self, primaryKey: true)
    let course = Column("course", String.self)
    let grade = Column("grade", Int32.self)
}
let grades = Grades()
  1. Create a pool of connections to PostgreSQL:
let pool = PostgreSQLConnection.createPool(host: "localhost", port: 5432, options: [.databaseName("school")], poolOptions: ConnectionPoolOptions(initialCapacity: 10, maxCapacity: 50))
  1. Create some example students:
let students: [[Any]] = [[0, "computing", 92], [1, "physics", 75], [2, "history", 83]]
  1. Connect to database and perform an SQL query:
pool.getConnection() { connection, error in
    guard let connection = connection else {
        guard let error = error else {
            return print("Unknown error")
        }
        return print("Error when getting connection from pool: \(error.localizedDescription)")
    }
    let insertQuery = Insert(into: grades, rows: students)
    connection.execute(query: insertQuery) { insertResult in
        connection.execute(query: Select(from: grades)) { selectResult in
            guard let resultSet = selectResult.asResultSet else {
                return print("No result set returned from query")
            }
            resultSet.forEach() { row, error in
                guard let row = row else {
                    guard let error = error else {
                        // Processed all results
                        return
                    }
                    // Handle error
                    return
                }
                guard row.count == 3 else {
                    // Expecting three elements per row
                    return print("Row has wrong number of elements. Expecting 3, returned: \(row.count)")
                }
                print("Student \(row[0] ?? ""), studying \(row[1] ?? ""), scored \(row[2] ?? "")")
            }
        }
    }
}
  1. If you were to run the application at this point it would execute immediately because the SwiftKuery API behaves asynchronously. In the case of this simple executable you can add a Dispatch Semaphore to force the application to wait for the asynchronous callbacks to complete:
// Add the following after the existing imports:
import Dispatch
let waitSemaphore = DispatchSemaphore(value: 0)

// Update the forEach callback to look like:
resultSet.forEach() { row, error in
    guard let row = row else {
        // Processed all results
        waitSemaphore.signal()
        return
    }
    print("Student \(row[0] ?? ""), studying \(row[1] ?? ""), scored \(row[2] ?? "")")
}

// Add the following line at the end of the main.swift file
waitSemaphore.wait()
  1. Save the main.swift file. Run swift build to build the executable.
  2. Run the executable .build/debug/<yourPackageName>.

This will print the id, course and grade for each student, which are queried from the database:

Student 0, studying computing, scored 92
Student 1, studying physics, scored 75
Student 2, studying history, scored 83

If you go to your database with psql school and enter TABLE grades; you can see that the table has been populated with the student data.

SQL Injection Prevention using Parameterization

Unsanitized data that is used in dynamic queries is one of the most common causes of SQL injection vulnerabilities. Parameterizing queries can help to prevent SQL injection attacks.

The following code is vulnerable to SQL injection if supplied_key1 or supplied_key2 contain untrusted data (that is, data which has not been validated):

let query = Select(from: confidential)
  .where(confidential.key1 == supplied_key1 || confidential.key2 == supplied_key2)

connection.execute(query: query) { queryResult in
  ...
}

To guard against SQL Injection attacks, use the following parameterized version of the code:

let query = Select(from: confidential)
  .where(confidential.key1 == Parameter() || confidential.key2 == Parameter())

connection.execute(query: query, parameters: supplied_key1, supplied_key2) { queryResult in
  ...
}

Prepared Statements

If your application executes the same (or similar) SQL statements repeatedly with different parameters you may improve the performance of the application by using a prepared statement. Prepared statements can reduce parsing time as the database parses and compiles the statement template only once and then stores the result but doesn’t execute it. Later, the application supplies values for the parameters of the statement template and the database executes the statement.

For example, suppose our application needs to retrieve the average grade for courses with an average above a given value; a value which we want to vary. Let’s change our query to use a parameter instead of a predefined value:

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, prepare the statement and execute as many times as required with different parameter values. Use the release function to free the prepared statement:

connection.prepareStatement(query) { result in
    guard let statement = result.asPreparedStatement else {
        // Handle error
        return
    }
    // Execute the statement
    connection.execute(preparedStatement: preparedStatement, parameters: [70]) { result in
        ...
        connection.execute(preparedStatement: preparedStatement, parameters: [25]) { result in
            ...
            connection.release(preparedStatement: preparedStatement) { result in
                ...
            }
        }
    }
}

Note: preparedStatement is a plugin-specific handle for the prepared statement.

Schema Management

Table creation

Swift-Kuery enables you to create tables on the database server.

Let's revisit the Grades table, which we used in our Example above:

class Grades: Table {
    let tableName = "Grades"
    let id = Column("id", Int32.self, primaryKey: true)
    let course = Column("course", String.self)
    let grade = Column("grade", Int32.self)
}

We will add a second table called courses:

class Courses: Table {
    let tableName = "Courses"
    let name = Column("name", String.self, primaryKey: true)
    let credit = Column("credit", Int32.self)
    let teacher = Column("teacher", String.self)
}
let courses = Courses()

We can add a foreign key to Grades that references a column in another table:

let grades = Grades().foreignKey(grades.course, references: courses.name)

Create a multi-column primary key (if not set in the column as for Grades.id)

grades.primaryKey(grades.id, grades.course)

Create the table in the database:

courses.create(connection: connection) { result in
     guard result.success else {
        print("Failed to create table: \(result.asError?)")
     }
    ...
}

Indices

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 ... }

Migration

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"
}

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 ... }

You can also execute raw alterations, if needed:

let dropColumnQuery = "ALTER TABLE " + t1.tableName + " DROP COLUMN " + t0.a.name
connection.execute(dropColumnQuery) { result in ... }

Query Examples

In the following section, we will provide an example SQL query and show you how to build and execute the same query in Swift using Swift-Kuery.

Classes used in the examples:

These examples we will use the following two tables:

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;

This query will select all results from the table. The example below shows how to execute this query including the boilerplate code:

let t1 = T1()

let query = Select(from: t1)

pool.getConnection() { connection, error in
    guard let connection = connection else {
        // Handle error
        return
    }
    query.execute(connection) { queryResult in
        guard let resultSet = queryResult.asResultSet else {
            // Handle error
            return
        }
        resultSet.getColumnTitles() { titles, error in
            guard let titles = titles else {
                // Handle error
                return
            }
            //Process titles
            resultSet.forEach() { row, error in
                guard let row = row else {
                    // Processed all results
                    return
                }
                // Process row
            }
        }
    }
}

The following examples show more complex queries, which can be substituted into the the above boilerplate.  

SELECT a, b FROM t1
WHERE (a LIKE '%b' OR a = 'apple') AND b > 5
ORDER BY b ASC, a DESC
OFFSET 5;

let query = 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)

 

SELECT UCASE(a) AS name FROM t1
WHERE b >= 0
GROUP BY a
HAVING SUM(b) > 3
ORDER BY a DESC;

let query = 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 query = Insert(into: t1, rows: [["apple", 10], ["apricot", 3], ["banana", 17]])

 

INSERT INTO t1
VALUES ('apple', 10);

let query = Insert(into: t1, values: "apple", 10)

 

INSERT INTO t1 (a, b)
VALUES ('apricot', '3');

let query = Insert(into: t1, valueTuples: (t1.a, "apricot"), (t1.b, "3"))

 

INSERT INTO t1 (a, b)
VALUES ('apricot', '3');

let query = Insert(into: t1, columns: [t1.a, t1.b], values: ["apricot", 3])

 

UPDATE t1 SET a = 'peach', b = 2
WHERE a = 'banana';

let query = 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 query = Select(from: leftTable)
  .leftJoin(rightTable)
  .on(leftTable.b == rightTable.b)

 

SELECT * FROM t1
JOIN t2
USING (b);

let query = Select(from: t1)
  .join(t2)
  .using(t1.b)

 

Queries with parameters:

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 query = Insert(into: t1, values: Parameter(), Parameter())

connection.execute(query: query, parameters: "banana", 28) { queryResult in
  // Process result
}

 

INSERT INTO t1
VALUES (@fruit,@number);

let query = Insert(into: t1, values: Parameter("fruit"), Parameter("number"))

connection.execute(query: query, parameters: ["number" : 28, "fruit" : "banana"]) { queryResult in
  // Process result
}

 

It is possible to insert NULL values using parameters:  

connection.execute(query: query, parameters: ["number" : 28, "fruit" : nil]) { queryResult in
  // Process result
}

 

Raw query:

connection.execute("CREATE TABLE myTable (a varchar(40), b integer)") {  queryResult in
  // Process result
}

 

SELECT LEFT(a, 2) as raw FROM t1
WHERE b >= 0 GROUP BY a
HAVING sum(b) > 3
ORDER BY a DESC;

let query = 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 query = 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 query = 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 query = Select(t2.c, from: t2)
    .group(by: t2.c)
    .having(sum(t2.b).notIn(Select(t1.b, from: t1).where(t1.a == "apple")))

List of plugins

API Documentation

For more information visit our API reference.

Community

We love to talk server-side Swift, and Kitura. Join our Slack to meet the team!

License

This library is licensed under Apache 2.0. Full license text is available in LICENSE.

More Repositories

1

Kitura

A Swift web framework and HTTP server.
Swift
7,628
star
2

BlueSocket

Socket framework for Swift using the Swift Package Manager. Works on iOS, macOS, and Linux.
Swift
1,407
star
3

Swift-JWT

JSON Web Tokens in Swift
Swift
559
star
4

Swift-SMTP

Swift SMTP client
Swift
261
star
5

Swift-Kuery-ORM

An ORM for Swift, built on Codable
Swift
212
star
6

BlueCryptor

Swift cross-platform crypto library using CommonCrypto/libcrypto
Swift
191
star
7

HeliumLogger

A lightweight logging framework for Swift
Swift
176
star
8

swift-html-entities

HTML5 spec-compliant character encoder/decoder for Swift
Swift
170
star
9

swift-ubuntu-docker

🚫 This repo is deprecated - please use the images here: https://hub.docker.com/_/swift
Vim Script
154
star
10

BlueRSA

RSA public/private key encryption, private key signing and public key verification in Swift using the Swift Package Manager. Works on iOS, macOS, and Linux (work in progress).
Swift
132
star
11

SwiftyRequest

SwiftyRequest is an HTTP networking library built for Swift.
Swift
110
star
12

Kitura-net

Kitura networking
Swift
104
star
13

BlueSSLService

SSL/TLS Add-in for BlueSocket using Secure Transport and OpenSSL
Swift
97
star
14

Kitura-redis

Swift Redis library
Swift
95
star
15

BlueECC

Elliptic-curve cryptography for Swift
Swift
94
star
16

BlueSignals

Generic Cross Platform Signal Handler
Swift
94
star
17

Configuration

Hierarchical configuration manager for Swift applications
Swift
81
star
18

Kitura-Sample

A sample application that shows how to use various features of Kitura
Swift
81
star
19

Kitura-WebSocket

WebSocket support for Kitura
Swift
68
star
20

OpenSSL

Swift modulemaps for libSSL and libcrypto
C
61
star
21

Swift-Kuery-PostgreSQL

PostgreSQL plugin for Swift-Kuery framework
Swift
61
star
22

SwiftKafka

Swift SDK for Apache Kafka
Swift
60
star
23

KituraKit

Swift client library for using Codable routes with Kitura
Swift
59
star
24

Kitura-CouchDB

CouchDB adapter for Kitura
Swift
51
star
25

CircuitBreaker

A Swift Circuit Breaker library – Improves application stability and reliability.
Swift
47
star
26

Kitura-Credentials

A pluggable framework for validating user credentials in a Swift server using Kitura
Swift
41
star
27

Kitura-NIO

A networking library for Kitura, based on SwiftNIO
Swift
38
star
28

Kitura-OpenAPI

OpenAPI support for Kitura
Swift
37
star
29

TypeDecoder

A Swift library to allow the runtime inspection of Swift language native and complex types.
Swift
37
star
30

SwiftKueryMySQL

MySQL plugin for Swift-Kuery framework
Swift
35
star
31

Package-Builder

Build and utility scripts used for continuous integration builds for Swift Package Manager projects on the Travis CI environment
Shell
35
star
32

CCurl

Modulemap for the libcurl library
Objective-C
31
star
33

Kitura-StencilTemplateEngine

Stencil templating for Kitura
Swift
27
star
34

Kitura-Markdown

Templating engine for Kitura that uses Markdown based templates
C
26
star
35

LoggerAPI

Logger protocol
Swift
26
star
36

kitura.dev

http://www.kitura.dev
JavaScript
26
star
37

Health

An application health library for Swift.
Swift
22
star
38

Kitura-Session

A pluggable framework for managing user sessions in a Swift server using Kitura
Swift
19
star
39

Kitura-WebSocket-NIO

A SwiftNIO based implementation of WebSocket for Kitura
Swift
18
star
40

CommonCrypto

CommonCrypto Module Map
Swift
18
star
41

FileKit

Swift
17
star
42

Kitura-TemplateEngine

Kitura Template Engine protocol
Swift
16
star
43

Swift-Kuery-SQLite

An SQLite plugin for the Swift-Kuery framework
Swift
16
star
44

Kitura-CredentialsHTTP

A plugin for the Kitura-Credentials framework that authenticates using HTTP Basic and Digest authentication
Swift
16
star
45

kitura-cli

⌨️ Kitura command-line interface
Go
14
star
46

KituraContracts

A library containing type definitions shared by client and server Kitura code.
Swift
13
star
47

CZlib

Module map for Zlib library
Swift
12
star
48

CloudEnvironment

Convenience Swift package for accessing environment variables, credentials.
Swift
12
star
49

Kitura-CredentialsFacebook

A plugin for the Kitura-Credentials framework that authenticates using the Facebook web login
Swift
10
star
50

Kitura-CORS

Kitura CORS middleware
Swift
10
star
51

Kitura-Cache

Kitura cache
Swift
10
star
52

Kitura-CredentialsGoogle

A plugin for the Kitura-Credentials framework that authenticates using the Google web login
Swift
9
star
53

Swift-cfenv

Easy access to Cloud Foundry application environment for Swift Packages.
Swift
9
star
54

Kitura-Compression

Kitura compression middleware
Swift
7
star
55

CEpoll

A modulemap file and include to help Swift code use epoll on Linux
Swift
6
star
56

Kitura-WebSocket-Client

A WebSocket client based on SwiftNIO
Swift
6
star
57

Kitura-CredentialsGitHub

A plugin for the Kitura-Credentials framework that authenticates using the GitHub web login
Swift
6
star
58

Kitura-MustacheTemplateEngine

Adapter of GRMustache Template Engine to Kitura Template Engine
Swift
5
star
59

CHTTPParser

Modulemap for the http-parser library
C
5
star
60

Kitura-WebSocket-Compression

A WebSocket compression library based on SwiftNIO
Swift
4
star
61

generator-swiftserver-projects

Autogenerated Kitura projects
Shell
4
star
62

Kitura-Session-Redis

Kitura-Session store using Redis as the backing store
Swift
4
star
63

Kitura-Benchmarks

Benchmarks for Kitura
Swift
3
star
64

homebrew-kitura

Homebrew tap
Ruby
3
star
65

Kitura-CredentialsJWT

A plugin for the Kitura-Credentials framework that supports JWT authentication.
Swift
3
star
66

ShellToolKit

Utility classes to help with common system/shell actions in Swift
Swift
3
star
67

anapistula

Simple standalone web server in swift
Swift
2
star
68

CLibpq

PostgreSQL wrapper
Swift
2
star
69

CMySQL

Swift
1
star
70

Kitura-CI

Repository to hold the testing scripts for some Kitura repositories
Shell
1
star
71

Maintainers

Files relevant to Kitura project maintainers
Swift
1
star
72

StarterWebServer

A starter web server that can be used as a template for a new project
Swift
1
star