In an earlier post I showed how to crate a data access layer
with SQLite.swift and Swift 1.2. In this
post I will be updating the previous post to use Swift 2 and SQLite.swift for
Swift 2. In both of these posts I describe the data access layer. In my new book Protocol-Oriented programming I take this example a step further by not only showing the data access layer but also showing how to integrate it with your application using the bridge design pattern.
SQLite is an open source, lightweight and cross platform
relational database however it does require good knowledge of SQL to use.
For me that is not much of a problem however it is always better if we can
avoid embedding SQL statements in our source code. This is where
frameworks like SQLite.swift come in. SQLite.swift is a type-safe, Swift language layer over SQLite3
that allows us to access an SQLite database in a pure Swift type
interface.
It is important, when designing an application, to design a good
data access layer between our application and its backend data storage.
While some may argue that is what Core Data is for, I am just not a big fan of
it especially if we are planning on porting our applications to other
platforms. In this blog post, I will show how we can use the SQLite.swift
framework to design a good data access layer for our applications written in
Swift 2.
We can download sqlite.swift for Swift 2 from it’s github repository.
There are good instructions on how to install it either though CocoaPods
or manually in our applications. Once we
have sqlite.swift setup within our application we will be ready to design our
data access layer.
In this blog post, we will mainly be discussing how to design
the data access layer. We will back up our design with a working example
but the focus of the post will be on the design and the code is there to
reinforce that design. You can find the
code for this post in this github repository.
Data Access Layer Introduction
Our data access layer will consist of three layers. The
bottom most, connection, layer will consist of one class named SQLiteDataStore which will contain the
connection handle for our SQLite database. This class will implement the
singleton pattern so all access to the database will go through one connection
handle.
The next layer will be a data helper layer that will contain one
class for each of our tables. These data helper classes will contain
methods to create, insert, delete and query a specific table. We will
want to create a protocol for the data helper classes to ensure that they
contain a minimum set of functionality like creating the table.
The final layer is the model layer. This layer will
contain tuples that model our database tables and will be used to write or
retrieve data from the data store.
In our example, we will have two tables. These are the Teams and the Players table. From our description above, we can probably
guess that this means we will need two data helper classes (TeamDataHelper
and PlayerDataHelper)
and two tuples (Team and Player). The data access layer
design will look like this:
Connection Layer
Lets begin by defining our errors that we can receive from our
data access layer. We will be using
Swift 2’s new error handling feature so our errors are defined in an enum like
this:
enum
DataAccessError: ErrorType {
case Datastore_Connection_Error
case Insert_Error
case Delete_Error
case Search_Error
case Nil_In_Data
}
We will see where we use these errors as we go though our code.
Now lets look at the code. Next we will look at the SQLiteDataStore class that will contain
the connection handle for our database. The SQLiteDataStore class will contain the following code:
import Foundation
import SQLite
class SQLiteDataStore {
static let sharedInstance
= SQLiteDataStore()
let BBDB: Connection?
private init() {
var path = "BaseballDB.sqlite"
if let dirs: [NSString] = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory,
NSSearchPathDomainMask.AllDomainsMask,
true) as [NSString] {
let dir = dirs[0]
path = dir.stringByAppendingPathComponent("BaseballDB.sqlite");
}
do {
BBDB = try Connection(path)
} catch _ {
BBDB = nil
}
}
func createTables() throws{
do {
try TeamDataHelper.createTable()
try PlayerDataHelper.createTable()
} catch {
throw DataAccessError.Datastore_Connection_Error
}
}
}
The SQLiteDataStore
class implements the singleton pattern so there is only one instance of the
class for the lifecycle of our application. We implement this pattern by
creating a private initializer which is accessed from a static constant named sharedInstance. Inside the initializer,
we set the path to the file that will contain our database and then create the BBDB Database connection
with that path.
The SQLiteDataStore
class contains a second method named createTables. The createTables
method calls the createTable methods from our data helper classes.
This method will let us create all of the tables from a single method.
Model Layer
Now lets look at the model layer. The first thing we need
to do in the model layer is to create an enumeration that will contain the
position that the players could play.
This Positions enum looks like
this.
enum Positions: String {
case Pitcher = "Pitcher"
case Catcher = "Catcher"
case FirstBase = "First
Base"
case SecondBase = "Second
Base"
case ThirdBase = "Third
Base"
case Shortstop = "Shortstop"
case LeftField = "Left
Field"
case CenterField = "Center
Field"
case RightField = "Right
field"
case DesignatedHitter = "Designated
Hitter"
}
Next we need to define the types that we use to model our
data. These types will be used to
transfer the data from our data access layer to the rest of our code. Since these types should be used exclusively
to transfer the data, using value types are definitely preferred. The reason why we should avoid using these
types in our business logic layer is they may change if we change the table
structure of our database and we do not want these changes to effect the code
in our business logic layer. With this
in mind we will use tuples to model our data as shown in the following code:
typealias Team = (
teamId: Int64?,
city: String?,
nickName: String?,
abbreviation: String?
)
typealias Player = (
playerId: Int64?,
firstName: String?,
lastName: String?,
number: Int?,
teamId: Int64?,
position: Positions?
)
Keep in mind that we should avoid using these types in our
business logic layer so we have the ability to change them without having to
change our business logic code. We
should have an adapter that will convert the data from these types into data
structures used by the business logic layer.
Data Helper Layer
Now lets look at the data helper layer. This layer will be
the bridge to our connection layer and will contain one data helper class per
table in our database. We will begin by creating a data helper protocol
that will define the set of methods that each data helper class needs to
implement. The DataHelperProtocol
protocol looks like this:
protocol
DataHelperProtocol {
typealias T
static func createTable() throws -> Void
static func insert(item: T) throws -> Int64
static func delete(item: T) throws -> Void
static func findAll() throws -> [T]?
}
Within this protocol the four methods that we are defining are:
createTable: Creates the table
insert: insets a row into the table
delete: deletes a row from the table
findAll: returns all rows in the table
Notice that we only define one method to query our data
stores. We do this because the methods to query each individual table
could be different therefore the method(s) needed to query the tables could be
different. We would need to evaluate the query methods needed for each
table on a table-by-table basis.
Now lets look at the TeamDataHelper
class that will confirm to the DataHelperProtocol.
This class will be used to read and write information to the Teams table of the SQLite data store.
import Foundation
import SQLite
class TeamDataHelper: DataHelperProtocol {
static let TABLE_NAME = "Teams"
static let table = Table(TABLE_NAME)
static let teamId = Expression<Int64>("teamid")
static let city = Expression<String>("city")
static let nickName = Expression<String>("nickname")
static let abbreviation = Expression<String>("abbreviation")
typealias T = Team
static func createTable() throws {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
do {
let _ = try DB.run( table.create(ifNotExists: true) {t in
t.column(teamId,
primaryKey: true)
t.column(city)
t.column(nickName)
t.column(abbreviation)
})
} catch _ {
// Error throw if table already exists
}
}
static func insert(item: T) throws -> Int64 {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
if (item.city != nil && item.nickName != nil &&
item.abbreviation != nil) {
let insert = table.insert(city <- item.city!,
nickName <- item.nickName!, abbreviation <- item.abbreviation!)
do {
let rowId = try DB.run(insert)
guard rowId > 0 else {
throw DataAccessError.Insert_Error
}
return rowId
} catch _ {
throw DataAccessError.Insert_Error
}
}
throw DataAccessError.Nil_In_Data
}
static func delete (item: T) throws -> Void {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
if let id = item.teamId {
let query = table.filter(teamId == id)
do {
let tmp = try DB.run(query.delete())
guard tmp == 1 else {
throw DataAccessError.Delete_Error
}
} catch _ {
throw DataAccessError.Delete_Error
}
}
}
static func find(id: Int64) throws -> T? {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
let query = table.filter(teamId == id)
let items = DB.prepare(query)
for item in items {
return Team(teamId: item[teamId] , city: item[city], nickName: item[nickName], abbreviation:
item[abbreviation])
}
return nil
}
static func findAll() throws -> [T]? {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
var retArray = [T]()
let items = DB.prepare(table)
for item in items {
retArray.append(Team(teamId:
item[teamId], city: item[city], nickName: item[nickName], abbreviation:
item[abbreviation]))
}
return retArray
}
}
We start the TeamDataHelper
class by setting the TABLE_NAME property. This property defines the name
for the table within our database. Next we define the table property which is the SQLite
table that contains our data. We use the
table property to access the Teams table with in our database. The next four properties (teamId, city,
nickname and abbreviation) define the name and type of each row
in the table.
Finally we implement each of the four methods defined in the DataHelperProtocol protocol plus one
extra method which will search the table by its unique identifier.
The PlayerDataHelper
class is similar to the TeamDataHelper
class except it is used to read and write to the Players table of the SQLite
data store. The PlayerDataHelper
class looks like this:
import Foundation
import SQLite
class PlayerDataHelper: DataHelperProtocol {
static let TABLE_NAME = "Players"
static let playerId = Expression<Int64>("playerid")
static let firstName = Expression<String>("firstName")
static let lastName = Expression<String>("lastName")
static let number = Expression<Int>("number")
static let teamId = Expression<Int64>("teamid")
static let position = Expression<String>("position")
static let table = Table(TABLE_NAME)
typealias T = Player
static func createTable() throws {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
do {
_ = try DB.run( table.create(ifNotExists: true) {t in
t.column(playerId,
primaryKey: true)
t.column(firstName)
t.column(lastName)
t.column(number)
t.column(teamId)
t.column(position)
})
} catch _ {
// Error thrown when table exists
}
}
static func insert(item: T) throws -> Int64 {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
if (item.firstName != nil && item.lastName != nil &&
item.teamId != nil && item.position != nil) {
let insert = table.insert(firstName <-
item.firstName!, number <- item.number!, lastName <- item.lastName!, teamId <- item.teamId!,
position <- item.position!.rawValue)
do {
let rowId = try DB.run(insert)
guard rowId >= 0 else {
throw DataAccessError.Insert_Error
}
return rowId
} catch _ {
throw DataAccessError.Insert_Error
}
}
throw DataAccessError.Nil_In_Data
}
static func delete (item: T) throws -> Void {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
if let id = item.playerId {
let query = table.filter(playerId == id)
do {
let tmp = try DB.run(query.delete())
guard tmp == 1 else {
throw DataAccessError.Delete_Error
}
} catch _ {
throw DataAccessError.Delete_Error
}
}
}
static func find(id: Int64) throws -> T? {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
let query = table.filter(playerId == id)
let items = DB.prepare(query)
for item in items {
return Player(playerId: item[playerId], firstName:
item[firstName], lastName: item[lastName], number: item[number], teamId: item[teamId], position: Positions(rawValue: item[position]))
}
return nil
}
static func findAll() throws -> [T]? {
guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
throw DataAccessError.Datastore_Connection_Error
}
var retArray = [T]()
let items = DB.prepare(table)
for item in items {
retArray.append(Player(playerId:
item[playerId], firstName:
item[firstName], lastName: item[lastName], number: item[number], teamId: item[teamId], position: Positions(rawValue: item[position])))
}
return retArray
}
}
Now lets look at how we would use this data access layer to read
and write information from our data store.
Using the Data Access Layer
To insert a row into a table, all we need to do is to create an
instance of the appropriate model type (Player or Team) and then pass
it to the appropriate data helper class (PlayerDataHelper or TeamDataHelper).
As an example, here is how we would create four rows in the Teams table:
do {
let bosId = try TeamDataHelper.insert(
Team(
teamId: 0,
city: "Boston",
nickName: "Red Sox",
abbreviation: "BOS"))
print(bosId)
} catch _{}
do {
let torId = try TeamDataHelper.insert(
Team(
teamId: 0,
city: "Toronto",
nickName: "Blue Jays",
abbreviation: "TOR"))
print(torId)
} catch _ {}
Here is how we would create three rows in the Players table:
let ortizId = try PlayerDataHelper.insert(
Player(
playerId: 0,
firstName: "David",
lastName: "Ortiz",
number: 34,
teamId: bosId,
position: Positions.DesignatedHitter
))
print(ortizId)
let bogeyId = try PlayerDataHelper.insert(
Player(
playerId: 0,
firstName: "Xander",
lastName: "Bogarts",
number: 2,
teamId: bosId,
position: Positions.Shortstop
))
print(bogeyId)
To query the database we would call the appropriate method with
the data helper class. The following code would return a list of all
teams in the Teams table and prints out the city and nickname for each team.
do {
if let teams = try TeamDataHelper.findAll() {
for team in teams {
print("\(team.city!) \(team.nickName!)")
}
}
} catch _ {}
Conclusion
In this blog post we showed how we could use sqlite.swift and
good development practices to create a data access layer that simplifies access
to the data and hides the complexity of the underlying data store. The
example shown gives us a good abstraction layer that separates the backend data
store from our main application.
The one thing that our example is missing is a way to query the
tables by different elements of the table. For example, we do not have a
way to query all players by team or position. There are a number of ways
that we could implement these queries depending on the complexity needed.
We could create a method for each type of query needed if there are not too
many or we could create a single method that will query by each element set in
the model object. How you implement these queries is up to you and really
depends on the complexity needed in your application.
