Adding a Database to FoodTracker Server with Swift-Kuery

This tutorial builds upon a server and application created by following the FoodTrackerBackend tutorial. These instructions demonstrate how to add a PostgreSQL database to the FoodTracker server using Swift-Kuery and Swift-Kuery-PostgreSQL so data persists between server restarts.

Pre-Requisites:

This tutorial follows on from the FoodTracker Application and server created by following the FoodTrackerBackend tutorial. If you have completed the FoodTracker Backend there are no further pre-requisites.

If you have not completed the FoodTrackerBackend tutorial follow the steps below to get started:

Ensure you have Swift 4, Xcode 9.x and Kitura 2.x installed.
Ensure you have CocoaPods installed:

sudo gem install cocoapods

Open a terminal window and clone the FoodTracker application and Server:

git clone https://github.com/Andrew-Lees11/SwiftPersistanceTutorial.git

Switch to the "completedFoodBackend" branch:

cd SwiftPersistanceTutorial
git checkout completedFoodBackend

Use Cocoapods to install app dependencies:

cd FoodTrackerBackend/iOS/FoodTracker
pod install

Open the FoodTracker Application in Xcode

open FoodTracker.xcworkspace/

This Xcode workspace contains the food tracker mobile app, which can be run by clicking the play button.

Connecting A PostgreSQL Database

Creating a PostgreSQL Database

The Food Tracker application is taken from the Apple tutorial for building your first iOS application. In FoodTrackerBackend Tutorial, we created a server and connected it to the iOS application. This means created meals are posted to the server and a user can then view these meals on localhost:8080/meals. Since the meals are stored on the Server, if the server is restarted the meal data is lost. To solve this problem, we will start by creating a PostgreSQL database where the meals will be stored.

Install PostgreSQL:

brew install postgresql
brew services start postgresql

You should receive a message that either PostgreSQL has been started or the service is already running. This installation should have installed two applications we need, namely createdb and psql, which will be used as clients to your locally running PostgreSQL.

Create a database called FoodDatabase to store the data:

createdb FoodDatabase

Open the PostgreSQL command line for your database:

psql FoodDatabase

Create a table to contain your meals:

CREATE TABLE meals (
    name varchar(100) PRIMARY KEY,
    photo text NOT NULL,
    rating integer
);

Note Name has been designated the primary key, therefore every name must be unique.

View your table to ensure it has been created:

TABLE meals;

At this point it will be empty since we have not inserted anything.

Type \q and then press ENTER to quit psql.

Adding Swift-Kuery dependencies to your server

Swift-Kuery is a database abstraction layer, it works alongside a specific database library, such as Swift-Kuery-PostgreSQL, to allow a user to easily query a SQL database in Swift. These two libraries are added to our Package.swift file, so the Server can access them.

Open a new terminal window and go into your server Package.swift file

cd SwiftPersistanceTutorial/FoodTrackerBackend/FoodServer
open Package.swift

Add Swift-Kuery and Swift-Kuery-PostgreSQL packages.

.package(url: "https://github.com/IBM-Swift/Swift-Kuery.git", .upToNextMinor(from: "1.0.0")),
.package(url: "https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL.git", .upToNextMinor(from: "1.0.1")),

Change targets to include Swift-Kuery and Swift-Kuery-PostgreSQL.

.target(name: "FoodServer", dependencies: [ .target(name: "Application"), "Kitura" , "HeliumLogger", "SwiftKuery", "SwiftKueryPostgreSQL"]),
.target(name: "Application", dependencies: [ "Kitura", "Configuration", "CloudEnvironment","SwiftMetrics","Health", "SwiftKuery", "SwiftKueryPostgreSQL"]),

Generate your FoodServer Xcode project

Now we have added the dependencies to our Package.swift file we can generate our FoodServer Xcode project to make editing the code easier. The FoodServer is a pure Swift project and so the following steps could also be achieved by editing the .swift files.

Generate the server Xcode project:

cd SwiftPersistenceTutorial/FoodTrackerBackend//FoodServer/
swift package generate-xcodeproj
open FoodServer.xcodeproj/

Click on the "FoodServer-Package" text on the top-left of the toolbar and select "Edit scheme" from the dropdown menu.
In "Run" click on the "Executable" dropdown, select FoodServer and click Close.

Now when you press play, Xcode will start your FoodTracker server listening on port 8080. You can see this by going to http://localhost:8080/ which will show the default Kitura landing page.

Create a Meals Table class

To work with the meals table in the database Swift-Kuery requires a matching class. We will now create a Meals class to match the meals table we created earlier in PostgreSQL.

Open your Sources > Application > Meal.swift file
Add SwiftKuery and SwiftKueryPostgreSQL to the import statements:

import SwiftKuery
import SwiftKueryPostgreSQL

Create a class, which matches the meals table you created in the database:

public class Meals : Table {
    let tableName = "meals"
    let name = Column("name")
    let photo = Column("photo")
    let rating = Column("rating")
}

Open your Sources > Application > Application.swift file
Add SwiftKuery and SwiftKueryPostgreSQL to the import statements for Application.swift

import SwiftKuery
import SwiftKueryPostgreSQL

Inside the App class create a Meals table object by inserting the line:

let meals = Meals()

below the line let cloudEnv = CloudEnv()

Connecting to the PostgreSQL database

We will now connect to our server to the PostgreSQL database. This will allow us to send and receive information using queries.

Staying within your Application.swift file set up a connection by inserting:

let connection = PostgreSQLConnection(host: "localhost", port: 5432, options: [.databaseName("FoodDatabase")])

below the line let meals = Meals()

Inside your storeHandler and loadHandler functions create a connection to the database.

Paste the following code on the first line of both your loadHandler and storeHandler functions:

connection.connect() { error in
    if error != nil {return}
    else {
        // Build and execute your query here.
    }
}

Querying the PostgreSQL Database

Once you have connected to your database, the code to perform queries is handled by the Swift-Kuery library. This means the following code would be the same for any supported SQL database.

Handling an HTTP POST request

We are now going to add an insert query to our storeHandler. This will mean that when our server receives an HTTP POST request, it will take the received data and perform an SQL INSERT query to the database. This will store the data in the Meals table in the database.

Inside the storeHandler function create an insert query.

// Build and execute your query here.
let insertQuery = Insert(into: self.meals, values: [meal.name, String(describing: meal.photo), meal.rating])

Add the following code to execute the insert query below your declaration of insertQuery

connection.execute(query: insertQuery) { result in
    // Respond to the result here
}

Note After you execute the query you receive a result back containing the response from the database. Since we are performing an insert query this will only include whether the query was successful. For this tutorial, we assume the insert query was successful and ignore the returned value.

After the connection.execute block, respond to the client with the inserted meal to indicate success:

completion(meal, nil)

Your completed storeHandler function should now look as follows:

func storeHandler(meal: Meal, completion: (Meal?, RequestError?) -> Void ) -> Void {
    connection.connect() { error in
        if error != nil {return}
        else {
            // Build and execute your query here.
            let insertQuery = Insert(into: meals, values: [meal.name, String(describing: meal.photo), meal.rating])
            connection.execute(query: insertQuery) { result in
                // Respond to the result here
            }
            completion(meal, nil)
        }
    }
}

Now when you create a meal in the application, the server will make an insert call to the PostgreSQL database.

You can verify this by:

Starting the FoodTracker application in Xcode.
Creating a meal in the application.
Accessing your database: psql FoodDatabase
Viewing your meals table: TABLE meals;

This should produce a table with the name, encoded photo string and rating of your newly added meal.

Handling an HTTP GET request

We are going to add a select query to our loadHandler function. This will mean that when the server receives an HTTP GET request, it will perform an SQL SELECT query to get the meals from the database. This means the data the server returns to the client is taken from the database and will persist, even if the server is restarted.

Create a temporary mealstore inside your loadHander function. Paste the following code on the first line of the loadHander function.

var tempMealStore: [String: Meal] = [:]

Inside the loadHander function create an select query.

// Build and execute your query here.
let query = Select(from :meals)

This query will return everything from the "meals" table.

Add the following code to execute the select query below your declaration of selectQuery

connection.execute(query: selectQuery) { queryResult in
    // Handle your result here
}

Iterate through the rows returned by the database.

// Handle your result here
if let resultSet = queryResult.asResultSet {
    for row in resultSet.rows {
        // Process rows
    }
}

For each row, create a Meal object from the table and add it to your temporary mealstore:

// Process rows
guard let name = row[0], let nameString = name as? String else{return}
guard let photo = row[1], let photoString = photo as? String else{return}
guard let photoData = photoString.data(using: .utf8) else {return}
guard let rating = row[2], let ratingInt = Int(String(describing: rating)) else{return}
let currentMeal = Meal(name: nameString, photo: photoData, rating: ratingInt)
tempMealStore[nameString] = currentMeal

In this example, we have parsed the cells from each row to be the correct type to create a meal object. Note For this tutorial we will not be storing the photo data in the database, instead we will store a string description of the photo and then encode that string to data when creating the Meal object.

At the end of the loadHandler function, replace your old mealstore with your newly created tempMealStore and return this as your response to the GET request.

self.mealStore = tempMealStore
let returnMeals: [Meal] = self.mealStore.map({ $0.value })
completion(returnMeals, nil)

Your completed loadHander function should now look as follows:

func loadHandler(completion: ([Meal]?, RequestError?) -> Void ) -> Void {
    var tempMealStore: [String: Meal] = [:]
    connection.connect() { error in
        if error != nil {return}
        else {
            let selectQuery = Select(from :meals)
            connection.execute(query: selectQuery) { queryResult in
                if let resultSet = queryResult.asResultSet {
                    for row in resultSet.rows {
                        guard let name = row[0], let nameString = name as? String else{return}
                        guard let photo = row[1], let photoString = photo as? String else{return}
                        guard let photoData = photoString.data(using: .utf8) else {return}
                        guard let rating = row[2], let ratingInt = Int(String(describing: rating)) else{return}
                        let currentMeal = Meal(name: nameString, photo: photoData, rating: ratingInt)
                        tempMealStore[nameString] = currentMeal
                    }
                }
            }
        }
    }
    self.mealStore = tempMealStore
    let returnMeals: [Meal] = self.mealStore.map({ $0.value })
    completion(returnMeals, nil)
}

Now when you preform a GET call to your server it will lookup and return the values from your database. You can verify this by going to http://localhost:8080/meals, where you should see your meals. You can now restart your server and this data will persist, since it is stored within the database!

If you would like to view a complete ToDoList application with database persistence, which contains further examples of HTTP and SQL calls please see PersistentiOSKituraKit.

alexwishart/SwiftPersistenceTutorial