Swifty SQL With GRDB

In my previous post I explained why I chose to use SQLite and GRDB for my app ExerPlan, and in this post I’m going to share implementation details with some Swift code.

I wanted to use Structs to map to database tables. This way I could do things like:

var activity = Activity(activityID: nil, activityType: 1, activityDate: Date() )

activity.save()

I also wanted to be able to have my structs encapsulate functionality to provide data validation. Although I will use some protection at the database level to enforce certain things (primary keys, constraints, triggers) I also like to take action at the UI level before it even hits the database.

To facilitate this I chose to create two structs per database table; one struct would be a “pure” GRDB-focussed struct that would give me the persistence code for free, and the second struct would be more like the public-facing API that I would use throughout my code for actually interacting with the data in the database. The first struct would become a property of the second.

struct ActivityDB: Codable, MutablePersistableRecord, TableRecord
{

   // Let GRDB know the name of the database table, otherwise it will assume it is the same as the struct’s name
   static let databaseTableName = “Activity”

   // This is the primary key, but it’s optional
   // because until a row is Inserted there is no value
   var activityID: Int64?

   var activityType: Int64
   var activityDate: Date

   mutating func save()
   {
      // Save record here
      // GRDB handles Insert/Update for new/modified row
      // and returns the auto-generated ID if there is one
   }

   // This is where the auto-generated ID can be used
   mutating func didInsert(with rowID: Int64, for column: String?)
   {
      activityTypeID = rowID
   }
}

My second struct would have ActivityDB as a property, but allow for data validation that I didn’t want to have on the GRDB-focussed struct.

struct Activity
{
   var dataStore: ActivityDB

   var activityID: Int64?
   {
       get
       {
           return dataStore.activityID
       }
   }

   var activityType: Int64
   {
      get
      {
         return dataStore.activityType
      }

      set(newValue)
      {
         // Perform some validation logic here
         dataStore.activityType = newValue
      }
   }

   var activityDate: Date
   {
      get
      {
         return dataStore.activityDate
      }

      set(newValue)
      {
         // Perform some validation logic here
         dataStore.activityDate = newValue
      }
   }

   mutating func save()
   {
      dataStore.save()
   }
}

There are a few kinks to work out with this approach, notably I’m not sure if I need the two structs and could just use one. At this stage it’s working as I want so I’ll continue with it until/unless I hit any major issues.

The next step was to create some code that would allow the easy retrieving of rows. GRDB provides a number of methods to do this, but I wrap those up inside a big Data Access Layer or DAL class. The class consists of a number of static methods that return arrays of structs, or whatever else is needed from the database. The following code shows the basic structure of how I return an array of one type.

class DAL
{
   private static func returnArray<T>(fromRows rows: [Row], ofType: DatabaseTypeEnum) -> Array<T>
   {
      var list = [T]()

      for row in rows
      {
         switch ofType
         {
         case .ActivityTableView:
            list.append(ActivityTableView(dbRow: row) as! T)

         case .PlanTableView:
            list.append(PlanTableView(dbRow: row) as! T)

         }
      }

      return list

   }

   private static func doFetchAll(withSQL sql: String, returnType: DatabaseTypeEnum) -> Array<T>
   {
      let rows = try! dbQueue.read
      { db in
         try Row.fetchAll(dB , sql)
      }

      returnArray(fromRows: rows, ofType: returnType)
   }

   static func fetchAllActivities() -> [ActivityTableView]
   {
      let sql = “””
Select src.ActivityID, src.ActivityType, src.ActivityDate
From Activity src
Order By
   src.ActivityDate desc
“””

   return doFetchAll(withSQL: sql, returningType: .ActivityTableView)

   }

}

The DAL can easily be extended with new methods for retrieving data, and also for the retrieval of single values/rows rather than an array. It’s still a work in progress design and as I get further into the UI development and have to start using these methods I may end up making new design decisions.

Writing this article has been a useful exercise as it has forced me to consider my choices again, and even led to some refactoring as I wrote it. The next step is to progress with the UI prototype which will give all this code a really good workout.