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 guide will demonstrate how to connect to a SQL database using one of the Swift-Kuery plugins and how to use this connection to send SQL queries to your database.


Step 1: Create the Kuery routes

We are going to create a new file in our project for the Kuery routes. The routes defined in this guide are examples of Codable routes. The equivalent definitions for raw routes can be found using the links below each code segment.

If you don't have a server, follow our Create a server guide.

Open your Application.swift file:

open Sources/Application/Application.swift

Inside the postInit() function add:

initializeKueryRoutes(app: self)

Create a new file called KueryRoutes.swift:

touch Sources/Application/Routes/KueryRoutes.swift

Open the KueryRoutes.swift file:

touch Sources/Application/Routes/KueryRoutes.swift

Inside this file we are going to create two routes. The first will use Swift-Kuery to save a Book into a database and the second will retrieve all of the saved books.

Add the following code to your KueryRoutes.swift file.

import KituraContracts
import LoggerAPI
import Foundation

func initializeKueryRoutes(app: App) {
    app.router.post("/kuery", handler: app.insertHandler)
    app.router.get("/kuery", handler: app.selectHandler)
}

extension App {
    // Create connection pool and initialize BookTable here

    func insertHandler(book: Book, completion: @escaping (Book?, RequestError?) -> Void) {
        // Handle POST here
    }

    func selectHandler(completion: @escaping ([Book]?, RequestError?) -> Void) {
        // Handle GET here
    }
}

See the above code as a raw route.

The routes in this guide are using the Book model from the routing guide, however you could use any Codable type.


Step 2: Install a database plugin

Kitura has built in support for PostgreSQL, MySQL and SQLite.

The API to use each plugin is the same but the installation steps differ slightly.

The algorithms are as follows:


Step 3: Create a class to represent the database table

To use SwiftKuery we need to create a class in Swift that matches our database table.

Create a new file called BookTable.swift:

touch Sources/Application/Models/BookTable.swift

Open the BookTable.swift file:

open Sources/Application/Models/BookTable.swift

Inside BookTable.swift, define your BookTable class:

import SwiftKuery

class BookTable: Table {
    let tableName = "BookTable"
    let id = Column("id", Int32.self, primaryKey: true)
    let title = Column("title", String.self)
    let price = Column("price", Float.self)
    let genre = Column("genre", String.self)
}

The BookTable class represents our Book model as an SQL table. It needs to inherit from Table and match the column names of the table we created in the database. We must also provide the table name as a property.

Return to the KueryRoutes.swift file. In the App extension, create an instance of this table:

static let bookTable = BookTable()

Now we're ready to start saving data to our database.


Step 4: Save data to the database

Now we're going to save a book that is posted to the '/kuery' route into our database.

Inside the handler for our POST route, we need to convert our book to an Array of Arrays of type Any. This converts the book that was sent to the route into the format required by Swift-Kuery. We need to use the Any type here as the database fields could be of any type.

let rows = [[book.id, book.title, book.price, book.genre]]
// Get connection from pool here

To talk to the database, the first thing we need to do is get a connection from the connection pool.

To do this we can use the getConnection method. We can add this into the handler for our POST route:

App.pool.getConnection() { connection, error in
    guard let connection = connection else {
        Log.error("Error connecting: \(error?.localizedDescription ?? "Unknown Error")")
        return completion(nil, .internalServerError)
    }
    // Write query and execute it here
}

See the above code as a raw route.

When we get a connection from the connection pool we need to confirm it's a valid connection.

If we don't get a valid connection, we will stop the execution and log an error to the console.

Now we can build the query for inserting data into a database.

Beneath our connection guard closure, add the following code:

let insertQuery = Insert(into: App.bookTable, rows: rows)

Once we have defined our insert query, we need to execute the query using the connection we took from the pool:

    connection.execute(query: insertQuery) { insertResult in
        guard insertResult.success else {
            Log.error("Error executing query: \(insertResult.asError?.localizedDescription ?? "Unknown Error")")
            return completion(nil, .internalServerError)
        }
        completion(book, nil)
    }

See the above code as a raw route.

That's it! We've setup our POST route to save data to a database. the completed handler for your POST route should now look as follows:

func insertHandler(book: Book, completion: @escaping (Book?, RequestError?) -> Void) {
    let rows = [[book.id, book.title, book.price, book.genre]]
    App.pool.getConnection() { connection, error in
        guard let connection = connection else {
            Log.error("Error connecting: \(error?.localizedDescription ?? "Unknown Error")")
            return completion(nil, .internalServerError)
        }
        let insertQuery = Insert(into: App.bookTable, rows: rows)
        connection.execute(query: insertQuery) { insertResult in
            guard insertResult.success else {
                Log.error("Error executing query: \(insertResult.asError?.localizedDescription ?? "Unknown Error")")
                return completion(nil, .internalServerError)
            }
            completion(book, nil)
        }
    }
}

See the above code as a raw route.

Next we can test our implementation.


Step 5 (Optional): Test saving a book to database

First we need to start our Kitura server.

Once the server is running run the following in a terminal:

curl -X POST \
      http://localhost:8080/kuery \
      -H 'content-type: application/json' \
      -d '{
      "id": 0,
      "title": "A Game of Thrones",
      "price": 14.99,
      "genre": "Fantasy"
  }'

This will make a POST request to the server and we should be returned our book in JSON format:

{"id":0,"title":"A Game of Thrones","price":14.99,"genre":"Fantasy"}

The following steps are for PostgreSQL. The commands will be different if you used a different database.

Then using the PostgreSQL CLI we can query the database to see if our data was saved.

Start the PostgreSQL CLI for our database by running the following in a terminal:

psql bookstoredb

In a terminal which is running the psql command, run the following:

    SELECT * FROM "BookTable";

You should see the following printed in the terminal:

 id |       title       | price |  genre
----+-------------------+-------+---------
 0  | A Game of Thrones | 14.99 | Fantasy
(1 row)

This shows our POST route is working as we'd expect.

A better way to test this, as we're writing Swift code, would be to query the database directly from our server instead of using Terminal.


Step 6: Retrieve data from the database

Now we're going to build a SELECT query that will query the database and return all the entries in our BookTable.

Inside the handler for our GET route, we need to get a connection from the connection pool:

App.pool.getConnection() { connection, error in
    guard let connection = connection else {
        Log.error("Error connecting: \(error?.localizedDescription ?? "Unknown Error")")
        return completion(nil, .internalServerError)
    }
    // Write query and execute it here
}

See the above code as a raw route.

Now we can build our SELECT query that will query the database for every entry in the "BookTable":

let selectQuery = Select(from: App.bookTable)

Like before we can now execute our query using connection.execute:

connection.execute(query: selectQuery) { selectResult in
    guard let resultSet = selectResult.asResultSet else {
        Log.error("Error connecting: \(selectResult.asError?.localizedDescription ?? "Unknown Error")")
        return completion(nil, .internalServerError)
    }
    // Iterate through result set here
}

See the above code as a raw route.

The query should return a set of results, in the code we use .asResultSet to check that the returned value is a valid result set, otherwise we log an error and return.

Next we need to iterate through our result set, converting each of the returned database table rows into a book:

var books = [Book]()
resultSet.forEach() { row, error in
    // Handle callback here
}

The forEach function will return either a row, an error or nil (in this case nil means that there are no more rows).

Inside the forEach callback, we need to handle these three cases:

guard let row = row else {
    if let error = error {
        Log.error("Error getting row: \(error)")
        return completion(nil, .internalServerError)
    } else {
        // All rows have been processed
        return completion(books, nil)
    }
}
// Convert row to book here

See the above code as a raw route.

When we get a row back from the database we need to convert it back into a Book model type.

After the guard closure, add the following code:

guard let idString = row[0] as? String,
      let id = Int(idString),
      let title = row[1] as? String,
      let price = row[2] as? Double,
      let genre = row[3] as? String
else {
      Log.error("Unable to decode book")
      let _ = response.send(status: .internalServerError)
      return next()
}
books.append(Book(id: id, title: title, price: price, genre: genre))

That's it! We've enabled our GET route to retrieve data from a database.

Our completed KueryRoutes.swift file should now look as follows:

import KituraContracts
import LoggerAPI
import Foundation
import SwiftKuery
import SwiftKueryPostgreSQL // This will be different if you did not use PostgreSQL

func initializeKueryRoutes(app: App) {
    app.router.post("/kuery", handler: app.insertHandler)
    app.router.get("/kuery", handler: app.selectHandler)
}

extension App {
    static let poolOptions = ConnectionPoolOptions(initialCapacity: 1, maxCapacity: 5)
    // The createPool() will be different if you used a plugin other than PostgreSQL
    static let pool = PostgreSQLConnection.createPool(host: "localhost", port: 5432, options: [.databaseName("bookstoredb")], poolOptions: poolOptions)
    static let bookTable = BookTable()

    func insertHandler(book: Book, completion: @escaping (Book?, RequestError?) -> Void) {
        let rows = [[book.id, book.title, book.price, book.genre]]
        App.pool.getConnection() { connection, error in
            guard let connection = connection else {
                Log.error("Error connecting: \(error?.localizedDescription ?? "Unknown Error")")
                return completion(nil, .internalServerError)
            }
            let insertQuery = Insert(into: App.bookTable, rows: rows)
            connection.execute(query: insertQuery) { insertResult in
                guard insertResult.success else {
                    Log.error("Error executing query: \(insertResult.asError?.localizedDescription ?? "Unknown Error")")
                    return completion(nil, .internalServerError)
                }
                completion(book, nil)
            }
        }
    }

    func selectHandler(completion: @escaping ([Book]?, RequestError?) -> Void) {
        App.pool.getConnection() { connection, error in
            guard let connection = connection else {
                Log.error("Error connecting: \(error?.localizedDescription ?? "Unknown Error")")
                return completion(nil, .internalServerError)
            }
            let selectQuery = Select(from: App.bookTable)
            connection.execute(query: selectQuery) { selectResult in
                guard let resultSet = selectResult.asResultSet else {
                    Log.error("Error connecting: \(selectResult.asError?.localizedDescription ?? "Unknown Error")")
                    return completion(nil, .internalServerError)
                }
                var books = [Book]()
                resultSet.forEach() { row, error in
                    guard let row = row else {
                        if let error = error {
                            Log.error("Error getting row: \(error)")
                            return completion(nil, .internalServerError)
                        } else {
                            // All rows have been processed
                            return completion(books, nil)
                        }
                    }
                    guard let idString = row[0] as? String,
                        let id = Int(idString),
                        let title = row[1] as? String,
                        let price = row[2] as? Double,
                        let genre = row[3] as? String
                    else {
                        Log.error("Unable to decode book")
                        return completion(nil, .internalServerError)
                    }
                    books.append(Book(id: id, title: title, price: price, genre: genre))
                }
            }
        }
    }
}

See the above code as a raw route.


Step 7 (Optional): Test retrieving the books from a database

If you followed "Step 5: Testing saving to database", then you will have a book in your database, which we can retrieve using the code we wrote in "Step 6: Retrieve data from the database".

To do this, start the server and navigate to: localhost:8080/kuery

This will call GET on the /kuery route and we will see the book we posted in Step 5 returned in JSON format. The book data persists even if we restart the Kitura server as it is now stored in a database.