Introduction

In this tutorial series, you will learn the basics for creating a new Swift on the Server application. For an interesting example, we have chosen a “Book Store” website, where people can search for books to buy and add them to their shopping carts. We will be using the SwiftKuery library for interfacing with the database. The following references could be very helpful:

Let’s get started!

Create a Swift project

You can create a new Swift project using the Swift package manager by calling:

$ swift package init

Add to your Package.swift file the following dependencies:

import PackageDescription

let package = Package(
    name: "bookstore",
	dependencies: [
		.Package(url: "https://github.com/IBM-Swift/HeliumLogger.git",       majorVersion: 1, minor: 1),
		.Package(url: "https://github.com/IBM-Swift/Kitura.git",             majorVersion: 1, minor: 2),
		.Package(url: "https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL", majorVersion: 0, minor: 2),
		.Package(url: "https://github.com/mxcl/PromiseKit",                  majorVersion: 4, minor: 0)
	]
)

Create the database schema

There are many database choices you can use when developing a Swift project, and many of them can be referenced by the TodoList project where we have over 8 examples using the most popular databases. This example will focus on persisting our data in a relational database. We will use SwiftKuery to help us build syntactically correct SQL queries. One benefit of using an abstraction layer for the SQL database, is that when you build queries for one SQL database, it will usually work for all of them. For example, if you’re currently using PostgreSQL- you can easily move to SQLite, or DB2 in the future.

Let’s assume PostgreSQL as your database as we begin to create the schema. You can use the ElephantSQL service in Bluemix in order to set up your PostgreSQL database.

The database will contain collection of very basically described books for this example. The book will have columns for the id, title, author, ISBN number, and year of publication:

CREATE TABLE books (
    book_id     SERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    author      TEXT NOT NULL,
    ISBN        TEXT NOT NULL,
    year        INTEGER NOT NULL
);

Since this is meant to be a very simple example, let’s just have a single text field for author. This could be a comma delimited list of authors. For a more mature example, you might have another table just for authors. Then define a many-to-many relationship mapping books to a group of authors. SwiftKuery supports many-to-many relationships, however this is outside of the scope of this tutorial.

In addition, store the list of registered users are in a simple table with the first and last name and the ID. This table could be extended to contain a hash of the password, email address, last sign in timestamp, etc.

 CREATE TABLE users (
	 user_id     SERIAL PRIMARY KEY, 
	 first_name  TEXT NOT NULL,
	 last_name   TEXT NOT NULL
 );

For each user, there is a shopping cart. This cart contains a list of books and also the quantity of the book (in case someone is buying a bunch as gifts for their friends):

 CREATE TABLE carts (
	 user_id     INTEGER NOT NULL REFERENCES users,
	 book_id     INTEGER NOT NULL REFERENCES books,
	 quantity    INTEGER NOT NULL
 );

The cart contains a foreign reference to the users and books table which prevents new items from being added to the cart where the book or the user does not exist anymore- or vice-versa.

For this example, let’s place a bunch of popular fantasy books in the database:

 INSERT INTO books (book_id, title, author, ISBN, year) 
 VALUES (1, 'A Game of Thrones', 'George R. R. Martin', '978-0553593716', 2003),
        (2, 'A Clash of Kings', 'George R. R. Martin', '978-0553593716', 2003),
        (3, 'A Storm of Swords', 'George R. R. Martin', '034554398X', 2003),
        (4, 'Harry Potter and the Sorcerer''s Stone', 'J.K. Rowling', '0439708184', 1999),
        (5, 'A Dance with Dragons', 'George R. R. Martin', '0553582011', 2013);

An item can be added to the cart as follows:

INSERT INTO users (user_id, first_name, last_name) 
VALUES (1, 'Hermione', 'Granger');
 
INSERT INTO carts (user_id, book_id, quantity) 
VALUES (1, 4, 2);

In order to make queries into the database faster and more efficient, we can add a few indices into the database. This can be useful as we search for books matching a title or an author.

CREATE INDEX idx_title_search ON books(title);
CREATE INDEX idx_author_search ON books(author);

Describing the Tables

Before SwiftKuery can build queries against tables in the database, it must have a description for the names of the tables and the columns inside of the table. You must have your tables inherit from the Table class for this to work properly. Your class must define what the tableName is, and have Columns defined for all of the properties you want your Select statements to fetch from.

For instance, the books table is described like this:

final class BooksTable : Table {
 
	 let tableName = "books"
 
	 let bookID =    Column("book_id")
	 let title =     Column("title")
	 let ISBN =      Column("isbn")
	 let year =      Column("year")
	 
 }

The carts table, similarly looks like this:

final class CartsTable: Table {

	 let tableName = "carts"
	 
	 let userID =    Column("user_id")
	 let bookID =    Column("book_id")
	 let quantity =  Column("quantity")
 
 }

You might be wondering how SwiftKuery knows how the Column names are discovered and used if they are simply just defined in the class. What happens automatically for you, is that the initializer for the Table class will use reflection in order to get the properties of the class. Any property that has the type Column is used for the query builder.

To keep everything together, create a new class called Database:

 public class Database {
	 
	 let queue = DispatchQueue(label: "com.bookstore.database", attributes: .concurrent)
	 
	 static let booksTable = BooksTable()
	 static let cartsTable = CartsTable()
 } 

This class Database contains static references to these BooksTable and CartsTable that you defined earlier. That will prevent you from having to initialize them each time you build a query that uses the tables. We create a Dispatch queue that will be used to help us execute various stages of calling the database in parallel. We will describe more of that later in the section about Promises.

We create a function that creates a new SQL connection:

private func createConnection() -> Connection {
		 return PostgreSQLConnection(host: Config.databaseHost, port: Config.databasePort,
		      options: [.userName(Config.userName),
			        .password(Config.password),
			        .databaseName(Config.databaseName)])
 }

Note that in this example, for every incoming request, we will create a new connection. After you are done with the request, you will call the Connection’s closeConnection method. As an alternative, you can implement automatic reconnect and connection pooling to have all requests go to a singleton connection object. However, that is out of scope for our tutorial.

It is a good idea to decouple the PostgreSQLConnection implementation from the SwiftKuery calls. This is because you might in the future want to change the implementation of the database you use. All connection types in SwiftKuery uses the Connection class.

Create new queries

Let’s now create a couple SwiftKuery executable statements.

To get all the books, it’s very straightforward. You can simply create a Select on a table with no additional predicates:

static func allBooks() -> Select {
    return Select(from: Database.booksTable) 
}

If you want to search for books that were written by an author, you can use this instead:

static func booksByAuthor(author: String) -> Select {
 
    return Select(from: Database.booksTable)
		  .where ( Database.booksTable.author == author )
		 
}

For a more complicated example, using “fuzzy matching”. You can have your users get a list of books that partially match a book title. We can use the “like” function to do a pattern match in the text. Order it alphabetically, and limit the results.

static func booksLike(title: String) -> Select {
    return Select(from: Database.booksTable)
                  .where( Database.booksTable.title.like("%"+title+"%"))
		  .order( by: .ASC(booksTable.title))
}

Finally, in order to get the book information back from a user’s shopping cart, you must do a join on the two tables. SwiftKuery makes this easy with the join and using methods:

static func booksInCart(userID: Int) -> Select {

    return Select(from: Database.cartsTable)
		 .where(Database.cartsTable.userID == userID)
	         .join(Database.booksTable)
		 .using(Database.cartsTable.bookID)
	
}

Query the books

Although you could create a different handler for when a user searches for all books, another for searches based on author, and another for fuzzy searches on title, and yet another for queries in the cart. We will create a very general purpose piece of code that handles all database for all book-based queries; all it needs is a Select statement and it will return a promise for a list of books.

func queryBooks(with selection: Select) -> Promise<[Book]> {
        
    let connection = createConnection()
        
    return firstly {
        connection.connect()
    }
    .then(on: queue) { result -> Promise<QueryResult> in
        selection.execute(connection)
    }
    .then(on: queue) { result -> ResultSet in
        guard let resultSet = result.asResultSet else { throw BookstoreError.noResult }
        return resultSet
    }.then(on: queue) { resultSet -> [Book] in
        let fields = resultToRows(resultSet: resultSet)
        return fields.flatMap( Book.init(fields:) )
    }.always(on: queue) {
        connection.closeConnection()
    }        
}

There is a lot going on here, so let’s step through it a bit. The function queryBooks returns a promise of a list of books. This promise serves as a handle for a future event that must be fulfilled or errored. The promise eventually will be resolved into either can error, or it can be a list of books. The important thing to note is that when you have a promise, you do not necessarily have the value of the promise immediately. The calls happen perhaps on another thread, and often must wait on some lengthy I/O before the result is resolved. Another interesting thing with promises is that you can return promises as values, or create various different promise handlers on the chain before returning them.

bookstorepromise

In this example, we first establish a connection, once that is established, the select statement can be invoked, then a query result will come back. Here, we can transform that query result into a list of books. We will always have the promise close the connection whether the sequence led to a successful list of books or an error.

We discuss the transformation from the SQL ResultSet into a list of books next:

let fields = resultToRows(resultSet: resultSet)
return fields.flatMap( Book.init(fields:) )

SQL to structures

Your application logic will most likely use Swift structures, classes, or enums for every complex data element. Define a new structure for a Book that has the following properties matching the books table:

struct Book {
	 
    let id: Int
    let title: String
    let author: String
    let ISBN: String
    let year: Int
    let quantity: Int?
	 
}

Notice that the quantity is optional. It does not exist in the books table- it exists in the carts table. This is because a query into the database in the catalog would have a nil value quantity since that is not relevant. However, when the Book is returned in the cart, it will contain a quantity.

We must now have some transformation that turns a ResultSet from SwiftKuery into a list of books. Let’s extend the Book structure to have an initializer that makes a list of String to Any pairs and optionally returns a new book or not. We can define this capability with a protocol:

public typealias Fields = [String: Any]
 
public protocol FieldMappable {
    init?( fields: Fields ) 
}

The Book is now extended with the FieldMappable protocol, and the initializer is then defined as follows:

extension Book: FieldMappable {
	 
    init?(fields: [String: Any]) {
		 
	if let fieldID = fields["book_id"] {
		 id = Int(fieldID as! String)!
        } else {
	         return nil
        }
		 
        title = fields["title"] as! String
		 
        ISBN = fields["isbn"] as! String
		 
        if let fieldYear = fields["year"] {
            year = Int(fieldYear as! String)!
        } else {
            return nil
        }
		 
        if let fieldAuthor = fields["author"] {
            author = fieldAuthor as! String
        } else {
            return nil
        }
		 
        if let fieldQuantity = fields["quantity"] {
            quantity = Int(fieldQuantity as! String)!
        } else {
            quantity = nil
        }		 
    }
 }

Note, this can be augmented with more error checking in case the elements returned from the table do not match what you’re expecting. For this example, we use a lot of force unwrapping of the Optionals for the sake of brevity in this tutorial.

Now, recall that when you execute a select statement with SwiftKuery, you are returned a ResultSet. Inside of that, there are two arrays, one that contains an array of column titles and another that contains an array of Any for the values. This is complicated, so an example can be useful. For instance,

The titles array would contain:

[ "title": "author", "id", "year", "isbn"]

with the rows:

[ ["A Game of Thrones",    "George R. R. Martin", "1", "2003", "978-0553593716"],
  ["A Dance with Dragons", "George R. R. Martin", "5", "2013", "XXX"]]

What we want is simply a Dictionary containing just a String to Any pair. This can be accomplished with the following function:

func resultToRows(resultSet: ResultSet) -> [Fields] {
	 
	 let t = resultSet.rows.map { zip(resultSet.titles, $0) }
    
	 let y: [Fields] = t.map {
		 var dicts = [String: Any]()
		 
		 $0.forEach {
			 let (title, value) = $0
			 dicts[title] = value
		 }
		 
		 return dicts
	 }
	 
	 return y
	 
} 

What we do is for every row in the database, we can zip the arrays for the title with the arrays for the value. The result can then be iterated over to return a new dictionary where the title becomes the key into the hash. So then, why doesn’t the SwiftKuery library simply return a ‘[String: Any’] object to begin with?

This is because a select statement such as select * from books, carts does not necessarily guarantee uniqueness on the titles. This could happen if each table shares the same column id, for example. In our example we purposefully did not have 2 tables share the same name of the column to make this transformation possible.

Making SwiftKuery return Promises

Notice in the above example, making a connection returns a Promise of the connection, instead of through a callback function that you are used to using in the SwiftKuery library. For instance, instead of:

connection.connection() {
	if let error = error {
	    // do something
	} else {
	    selection.execute() { result in 
		 // callback with the result
            }
	}
    }

}

With Promises, you can use:

return firstly {
    connection.connect()
}

Once the connection has been made, the following promise can be chained:

.then(on: queue) { result -> Promise<QueryResult> in
    return selection.execute(connection)
}

Promises help improve the readability of the code, through eliminating nested callbacks, and simplifying how errors propagate through the chain.

SwiftKuery does not automatically support promises. It is designed to support different style of programming for asynchrony. Since developers might choose to use callbacks, promises, reactive and all the various and sundry libraries that support promises and reactive programming, it provides the base which are callbacks.

One of the great things about the Swift language is that you can extend the capability of an existing API through extensions. To extend the connection class to instead return a promise, you can use:

public extension Connection {

	 func connect() -> Promise<Void> {
		 return Promise { fulfill, reject in
			 self.connect() { error in
				 if let error = error {
					 reject(error)
				 } else {
					 fulfill()
				 }
			 }
		 }
	 }
	 
}

Also, a QueryResult can be returned as a Promise instead of a callback, through this extension to Query:

public extension Query {

    func execute(_ connection: Connection ) -> Promise<QueryResult> {
        return Promise { fulfill, reject in
            self.execute( connection) { result in
	        fulfill(result)
	    }
	}
   }

}

Tests

To get books that contain the word “Storm” in it, and check to see if the “Storm of Swords” appears, you can run the following test:

func test_getBooksLike() {
	 
    let e = expectation(description: "Get similar books")
    let database = Database()
    
    firstly {
        database.queryBooks(with: Database.booksLike(title: "Storm"))
    }.then { books in
        print("Returned books are \(books)")
        XCTAssertNotNil(books)
        XCTAssertEqual(books.count, 1)
	 
        let stormBook = books[0]
        XCTAssertEqual(stormBook.title, "A Storm of Swords")
        e.fulfill()
    }.catch { error in
        XCTFail()
    }

    waitForExpectations(timeout: 10) { error in }
	 
}

To get the books in the shopping cart for user 1, you can create the following test:

func test_getBooksInCart() {
 
    let e = expectation(description: "Books in cart")

    let database = Database()

    firstly {
        database.queryBooks(with: Database.booksInCart(userID: 1))
    }.then { books in
        print("Books in the cart 1 are: \(books)")
        XCTAssertNotNil(books)
        e.fulfill()
    }.catch { error in
        XCTFail()
    } 
    
    waitForExpectations(timeout: 10) { error in }
		 
}

What’s Next?

We have presented how to interface to a relational database for a typical bookstore application and how SwiftKuery can make it easy to build queries against the database. To make our code more readable in the presence of complicated asynchrony, we have presented how Promises can be helpful for improving the readability of your code and improving error handling.

Feel free to download the Bookstore full project to get you started.

You may be interested in…

Stay tuned for the next segment in the series where we define a REST API for the Bookstore.

4 Comments on "Creating a Bookstore application: Interface to SQL"

Join The Discussion

Your email address will not be published. Required fields are marked *