Simplify Sqlite Access from Swift

Using the SQLite C API has always been a pain. Now with Swift it’s practically unworkable. I think most people are using some kind of a wrapper library like FMDB. I took a few hours to evaluate various libraries available:

Out of these I liked GRDB the most. Here’s a quick tutorial on how to do the most common tasks in GRDB. We will assume a schema like this.

create table if not exists Employee (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    salary real not null,
    last_update datetime NOT NULL
);

Using GRDB

I use Cocoapod to add GRDB to my project. And then from any Swift file where you need to use GRDB add:

import GRDB

Opening a Connection

A connection is represented by a DatabasePool object. Here we connect to a file database.dat within the app’s Documents directory.

let paths = NSSearchPathForDirectoriesInDomains(
    .DocumentDirectory, .UserDomainMask, true);
let docsDir = paths[0];
let dbFile = docsDir + "/database.dat";

let db = try! DatabasePool(path: dbFile)

A connection is closed when the DatabasePool object gets destroyed.

Inserting a Row

This will add a new row to the Employee table.

try! db.execute(
  "INSERT INTO Employee (name, salary, last_update) VALUES (?, ?, ?)", 
  arguments: ["Daffy Duck", 24000.00, NSDate()])

Note how the Swift data types in arguments: match the SQLite data types.

Query

To query by primary key or any other clause that is expected to return a single row do this.

if let row = Row.fetchOne(db, 
  "select * from Employee where id=?", 
  arguments: [1]) {
    let name : String = row.value(named: "name")
    let lastUpdate : NSDate = row.value(named: "last_update")
    let salary : Double = row.value(named: "salary")

    print("Found employee: \(name) \(lastUpdate) \(salary)")
} else {
    print("Could not find employee with that ID.")
}

The Row.fetchOne() method returns an optional Row object. Using the options makes it super easy to tell if the query returned anything or not.

The Row class has different value(named:) methods that return different data types. Here the compiler has automatically picked the correct value(named:) based on the data type of the variable on the left hand side. Type inference has made things safer and easier.

When a query returns multiple rows, do this:

let rows = Row.fetchAll(db,
    "select * from Employee where salary > ?",
    arguments: [25000.00])

for row in rows {
    let name : String = row.value(named: "name")
    let lastUpdate : NSDate = row.value(named: "last_update")
    let salary : Double = row.value(named: "salary")

    print("Found employee: \(name) \(lastUpdate) \(salary)")
}

Updating a Row

Updates are done using the same db.execute() method that we have used to insert rows.

try! db.execute("update Employee set salary=? where id=?", 
  arguments: [40000.00, 4])

Deleting Rows

Deleting a single row using primary key:

try! db.execute(
  "delete from Employee where id = ?", arguments: [1])

Bulk delete is done the same way:

try! db.execute(
  "delete from Employee where salary > ?", 
  arguments: [25000.00])
Advertisements