Kitura Logo

Working with Databases using the ORM

Introduction

Swift-Kuery-ORM is an ORM (Object Relational Mapping) library for Swift built on top of Swift-Kuery . Using it allows you to simplify the persistence of model objects in a database.

This guide steps you through connecting to a PostgreSQL database, creating your model, and persisting it using the ORM.

Adding Swift-Kuery-ORM and Swift-Kuery-PostgreSQL to your project

If you do not have an existing project, run kitura init in your terminal to generate a new project.

Edit your Package.swift adding Swift-Kuery-ORM and Swift-Kuery-PostgreSQL to your dependencies and application targets:

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

.target(name: "Application", dependencies: [ "SwiftKueryORM", "SwiftKueryPostgreSQL", ...

Regenerate your Xcode project:

swift package generate-xcodeproj

Open your Xcode project and go to Sources >> Application >> Application.swift

Add SwiftKueryORM and SwiftKueryPostgreSQL to your import statements:

import SwiftKueryORM
import SwiftKueryPostgreSQL

Creating Your PostgreSQL Database

You will create a locally running PostgreSQL Database that your server will connect to. We recommend you use Homebrew to set up PostgreSQL on your machine as follows:

brew install postgresql
brew services start postgresql
createdb school

Define Your Model

We are going to create a struct to represent the data we will store in the database. We then make it conform to Model (Which itself conforms to Codable) so it can be used with the ORM.

At the end of your Application.swift file, create struct representing a Grade:

struct Grade: Model {
  var course: String
  var grade: Int
}

Set up the Database connection

In the postInit function of your Application.swift file, create your database connection pool:

let pool = PostgreSQLConnection.createPool(host: "localhost", port: 5432, options: [.databaseName("school")], poolOptions: ConnectionPoolOptions(initialCapacity: 1, maxCapacity: 5, timeout: 10000))
Database.default = Database(pool)

Beneath your connection pool, create the database table that represents this struct:

do {
  try Grade.createTableSync()
} catch let error {
  // Error
}

To check that a database table called Grades has been created, run your Kitura server and then use psql from the command-line as follows:

psql school
SELECT * FROM "Grades";

This should print the column names of the Grades table with no data (i.e. no rows).

Persisting our Model in the database

We are going to create a set of Codable routes that store, retrieve and delete grades from the database.

At the bottom of your postInit function, define your routes:

router.get("/grades", handler: loadHandler)
router.get("/grades", handler: getOneHandler)
router.post("/grades", handler: postHandler)
router.delete("/grades", handler: deleteAllHandler)
router.delete("/grades", handler: deleteOneHandler)

After your postInit function, define your handlers:

func loadHandler(completion: @escaping ([Grade]?, RequestError?) -> Void ) {
    Grade.findAll(completion)
}
func getOneHandler(id: Int, completion: @escaping (Grade?, RequestError?) -> Void ) {
    Grade.find(id: id, completion)
}
func postHandler(grade: Grade, completion: @escaping (Grade?, RequestError?) -> Void ) {
    grade.save(completion)
}
func deleteAllHandler(completion: @escaping (RequestError?) -> Void ) {
    Grade.deleteAll(completion)
}
func deleteOneHandler(id: Int, completion: @escaping (RequestError?) -> Void ) {
    Grade.delete(id: id, completion)
}

Accessing the response on the server

Because the ORM API matches the Codable routes API, you can pass the completion straight to the Codable route response.
You can access your ORM response for your getOneHandler as follows:

func getOneHandler(id: Int, completion: @escaping (Grade?, RequestError?) -> Void ) {
    Grade.find(id: id) { grade, error in
        guard let grade = grade else {
            return completion(nil, error)
        }
        completion(grade, nil)
    }
}

Testing with OpenAPI

You can test these route using the OpenAPI interface:

1. Start your Kitura Server
2. Go to http://localhost:8080/openapi/ui
3. Using the interface, POST, GET and DELETE the Grade objects

You can also view your Grades table using PSQL:

1. Open the terminal and enter: psql school
2. View your Grades table: SELECT * FROM "Grades";
3. You should see your Grades.
4. Quit PSQL with: \q

Using Query Parameters with Swift-Kuery-ORM

You are now going to create a route that will filter the database results using Query Parameters. This will allow you to get only results you are interested in.

At the bottom of your application.swift file, Define your query:

struct Query: QueryParams {
  let course: String?
  let grade: InclusiveRange<Int>?
  let order: Ordering?
}

Add query parameters to your loadHandler:

func loadHandler(query: Query?, completion: @escaping([Grade]?, RequestError?) -> Void) {
    Grade.findAll(matching: query, completion)
}

The grades route can now accept query parameters to filter the response.
You can test this as follows:

1. Start your Kitura Server
2. Go to http://localhost:8080/openapi/ui
3. Using the interface, POST the following three courses:

course: Art, grade: 60
course: Physics, grade: 80
course: History, grade: 90

4. Go to http://localhost:8080/grades?course=Art
5. Go to http://localhost:8080/grades?grade=70,100&order=desc(grade)
The grades matching your query parameters should be shown. You can POST more Grades or change the query values to test this further.

Congratulations, you have just successfully connected a Kitura Server to a PostgreSQL database using the ORM!

Slack icon

NEED HELP?

MESSAGE US ON SLACK.