To use sqlite.swift with Swift 2, see the Create a Data Access Layer with SQLite.Swift and Swift 2 post.
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.
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.
We can download sqlite.swift from it’s githubrepository. There are good instructions on how to install
it either though CocoaPods or manually in our applications here. 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 working example code but the focus of the post will be
on the design and the code is there to reinforce that design.
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 though 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 classes 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 model classes (Team and Player). The
classes required for our data access layer will look like this:
Connection Layer
Now lets look at the code.
We will begin with 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: Database
private init() {
var path = "BaseballDB.sqlite"
if let dirs : [String] = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.AllDomainsMask,
true) as? [String] {
let dir = dirs[0]
path = dir.stringByAppendingPathComponent("BaseballDB.sqlite");
println(path)
}
BBDB = Database(path)
}
func createTables() {
TeamDataHelper.createTable()
PlayerDataHelper.createTable()
}
}
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 initiator which is accessed from a static constant named sharedInstance. Inside the initiator, we set the path to the file that will contain our database and then create the BBDB Database instance with that path.
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 initiator which is accessed from a static constant named sharedInstance. Inside the initiator, we set the path to the file that will contain our database and then create the BBDB Database instance 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 classes in the model layer will mirror
the tables in the data store so we can use them to read from or write to the
data store. In our example we will have
two classes in the model layer, these are the Player
and Team classes. We will look at the Team
class first:
class Team {
var teamId: Int64?
var city: String?
var nickName: String?
var abbreviation: String?
init(teamId: Int64, city: String, nickName: String, abbreviation: String) {
self.teamId = teamId
self.city = city
self.nickName = nickName
self.abbreviation = abbreviation
}
}
}
The Team class contains four
properties and an init method that
will set the properties. The teamId property is designed to be the
unique identifier for the table while the rest of the properties are
information about the team.
Now lets see the Player
class:
class Player {
var playerId: Int64?
var firstName: String?
var lastName: String?
var number: Int?
var teamId: Int64?
var position: Positions?
init (playerId: Int64, firstName: String, lastName: String, number: Int, teamId: Int64, position: Positions?) {
self.playerId = playerId
self.firstName = firstName
self.lastName = lastName
self.number = number
self.teamId = teamId
self.position = position
}
}
}
The Player class has five
properties and an init method that
will set the properties. The playerId property is designed to be the
unique identifier for the table while the rest of the properties will be
information about the player. The final
property named position is of the
type Positions.
The Positions type is an enum that 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"
}
}
This enum is used to define the position that a player plays.
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()
-> Void
static func insert(item: T) -> Int64
static func delete(item: T) -> Void
static func findAll() ->
[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 teamId = Expression<Int64>("teamid")
static let city = Expression<String>("city")
static let nickName = Expression<String>("nickname")
static let abbreviation = Expression<String>("abbreviation")
static let table = SQLiteDataStore.sharedInstance.BBDB[TABLE_NAME]
typealias T = Team
static func createTable() {
let results = SQLiteDataStore.sharedInstance.BBDB.create(table: table,
ifNotExists: true) { t in
t.column(teamId,
primaryKey: true)
t.column(city)
t.column(nickName)
t.column(abbreviation)
}
}
static func insert(item: T) -> Int64 {
if (item.city != nil &&
item.nickName != nil &&
item.abbreviation != nil) {
if let results = table.insert(city <- item.city!, nickName <- item.nickName!, abbreviation <- item.abbreviation!).rowid {
return results
}
}
return -1
}
static func delete (item: T) -> Void {
if let id = item.teamId {
let query = table.filter(teamId == id)
query.delete()
}
}
static func find(id: Int64) -> T? {
let query = table.filter(teamId == id)
var results: T?
if let item = query.first {
results = Team(teamId:
item[teamId], city: item[city], nickName: item[nickName], abbreviation:
item[abbreviation])
}
return results
}
static func findAll() ->
[T]? {
var retArray = [T]()
for item in table {
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. The next four
properties (teamId, city, nickname and abbreviation)
define the name and type of each row in the table. We then set the table property which will be used to access the Team table within
the SQLite data store.
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 = SQLiteDataStore.sharedInstance.BBDB[TABLE_NAME]
typealias T = Player
static func createTable() {
let results = SQLiteDataStore.sharedInstance.BBDB.create(table: table,
ifNotExists: true) { t in
t.column(playerId,
primaryKey: true)
t.column(firstName)
t.column(lastName)
t.column(number)
t.column(teamId)
t.column(position)
}
}
static func insert(item: T) -> Int64 {
if (item.firstName != nil &&
item.lastName != nil &&
item.teamId != nil &&
item.position != nil) {
if let results = table.insert(firstName <- item.firstName!, number <- item.number!, lastName <- item.lastName!, teamId <- item.teamId!, position <- item.position!.rawValue).rowid {
return results
}
}
return -1
}
static func delete (item: T) -> Void {
if let id = item.playerId {
let query = table.filter(playerId == id)
query.delete()
}
}
static func find(id: Int64) -> T? {
let query = table.filter(playerId == id)
var results: T?
if let item = query.first {
results = Player(playerId:
item[playerId], firstName: item[firstName], lastName: item[lastName], number:
item[number], teamId: item[teamId], position: Positions(rawValue:
item[position]))
}
return results
}
static func findAll() ->
[T]? {
var retArray = [T]()
for item in table {
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
}
}
We start the PlayerDataHelper
class by setting the TABLE_NAME property. This property defines the name for the table
within our database. The next six properties
(playerId, firstName, lastName, number,
teamId and position) define the name and type of each row in the table. We then set the table property that will be used to access the Player table within the
SQLite data store.
Within these data helper classes we can implement any other query
methods that are needed for our application.
Using the Data Access Layer
Now lets see how we would use this data access layer to read
and write information to the data store.
To insert a row into a table, all we need to do is to create an instance
of the appropriate model class (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:
let bosId = TeamDataHelper.insert(Team(teamId: 0, city: "Boston", nickName: "Red
Sox", abbreviation: "BOS"))
let baltId = TeamDataHelper.insert(Team(teamId: 0, city: "Baltimore", nickName: "Orioles", abbreviation: "BAL"))
let tampId = TeamDataHelper.insert(Team(teamId: 0, city: "Tampa
Bay", nickName: "Rays", abbreviation: "TB"))
let torId = TeamDataHelper.insert(Team(teamId: 0, city: "Toronto", nickName: "Blue Jays", abbreviation: "TOR"))
let torId = TeamDataHelper.insert(Team(teamId: 0, city: "Toronto", nickName: "Blue Jays", abbreviation: "TOR"))
Here is how we would create three rows in the Players table:
let ortizId =
PlayerDataHelper.insert(Player(playerId: 0, firstName: "David",
lastName: "Ortiz", number: 34, teamId: bosId, position:
Positions.DesignatedHitter))
let napId =
PlayerDataHelper.insert(Player(playerId: 0, firstName: "Mike",
lastName: "Napoli", number: 12, teamId: bosId, position:
Positions.FirstBase))
let pedId = PlayerDataHelper.insert(Player(playerId: 0, firstName: "Dustin", lastName: "Pedroia", number: 15, teamId: bosId, position: Positions.SecondBase))
let pedId = PlayerDataHelper.insert(Player(playerId: 0, firstName: "Dustin", lastName: "Pedroia", number: 15, teamId: bosId, position: Positions.SecondBase))
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.
if let teams = TeamDataHelper.findAll() {
for team in teams {
println("\(team.city!) \(team.nickName!)")
}
}
}
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.
In this post I describe how to create a data access layer with SQLite.swift. In my new book titled Protocol Oriented Programming with Swift I show not only how to create this data access layer with SQLite.swift and Swift 2 but I also show how to use the Bridge pattern to integrate this layer with your application. You can also read about Protocol-Oriented programming in my POP and OOP blog post.
In this post I describe how to create a data access layer with SQLite.swift. In my new book titled Protocol Oriented Programming with Swift I show not only how to create this data access layer with SQLite.swift and Swift 2 but I also show how to use the Bridge pattern to integrate this layer with your application. You can also read about Protocol-Oriented programming in my POP and OOP blog post.
