Skip to content

Latest commit

 

History

History
1102 lines (787 loc) · 35 KB

README.md

File metadata and controls

1102 lines (787 loc) · 35 KB
Raw

GRDB.swift

GRDB.swift is an SQLite toolkit for Swift 2, from the author of GRMustache.

It ships with a low-level database API, plus application-level tools.

July 30, 2015: GRDB.swift 0.7.0 is out. Release notes

Get release announcements and usage tips: follow @groue on Twitter.

Jump to:

Features

  • A low-level SQLite API that leverages the Swift 2 standard library.
  • No ORM, no smart query builder, no table introspection. Your SQL skills are welcome here.
  • A Model class that wraps result sets, eats your custom SQL queries for breakfast, and provides basic CRUD operations.
  • Swift type freedom: pick the right Swift type that fits your data. Use Int64 when needed, or stick with the convenient Int. Store and read NSDate or NSDateComponents. Declare Swift enums for discrete data types. Define your own database-convertible types.
  • Database Migrations

Usage

import GRDB

// Open database connection
let dbQueue = try DatabaseQueue(path: "/path/to/database.sqlite")

let redWinesCount = dbQueue.inDatabase { db in            // Int
    db.fetchOne(Int.self, "SELECT COUNT(*) FROM wines WHERE color = ?",
                arguments: [Color.Red])!
}

try dbQueue.inTransaction { db in
    try Person(name: "Arthur").insert(db)
    return .Commit
}

dbQueue.inDatabase { db in
    let persons = db.fetchAll(Person.self, "SELECT ...")  // [Person]
    for wine in db.fetch(Wine.self, "SELECT ...") {       // AnySequence<Wine>
        ...
    }
}

Installation

iOS7

To use GRDB.swift in a project targetting iOS7, you must include the source files directly in your project.

CocoaPods

CocoaPods is a dependency manager for Xcode projects.

To use GRDB.swift with Cocoapods, specify in your Podfile:

source 'https://github.com/CocoaPods/Specs.git'
use_frameworks!

pod 'GRDB.swift', '0.7.0'

Carthage

Carthage is another dependency manager for Xcode projects.

To use GRDB.swift with Carthage, specify in your Cartfile:

github "groue/GRDB.swift" == 0.7.0

Before running carthage update, select Xcode-beta as the active developer directory by running the following command:

sudo xcode-select  -s /Applications/Xcode-beta.app

Manually

Download a copy of GRDB.swift, embed the GRDB.xcodeproj project in your own project, and add the GRDBOSX or GRDBiOS target as a dependency of your own target.

Documentation

To fiddle with the library, open the GRDB.xcworkspace workspace: it contains a GRDB-enabled Playground at the top of the files list.

Reference

Guides

Database Queues

You access SQLite databases through thread-safe database queues (inspired by ccgus/fmdb):

let dbQueue = try DatabaseQueue(path: "/path/to/database.sqlite")

Configure databases:

let configuration = Configuration(
    foreignKeysEnabled: true,   // Default true
    readonly: false,            // Default false
    trace: Configuration.logSQL // An optional trace function.
                                // Configuration.logSQL logs all SQL statements.
)
let dbQueue = try DatabaseQueue(
    path: "/path/to/database.sqlite",
    configuration: configuration)

To open an in-memory database, don't provide any path:

let inMemoryDBQueue = DatabaseQueue()

Database connections get closed when the database queue gets deallocated.

To create tables, we recommend using migrations.

Transactions

Transactions wrap the queries that alter the database content:

try dbQueue.inTransaction { db in
    try db.execute(
        "INSERT INTO persons (name, age) VALUES (?, ?)",
        arguments: ["Arthur", 36])
    
    try db.execute(
        "INSERT INTO persons (name, age) VALUES (:name, :age)",
        arguments: ["name": "Barbara", "age": 37])
    
    return .Commit
}

A rollback statement is issued if an error is thrown from the transaction block.

Fetch Queries

Row Queries

Fetch lazy sequences of rows, arrays, or a single row:

dbQueue.inDatabase { db in
    db.fetchRows("SELECT ...", arguments: ...)     // AnySequence<Row>
    db.fetchAllRows("SELECT ...", arguments: ...)  // [Row]
    db.fetchOneRow("SELECT ...", arguments: ...)   // Row?
}

Arguments are optional arrays or dictionaries that fill the positional ? and named parameters like :name in the query. GRDB.swift only supports colon-prefixed named parameters, even though SQLite supports other syntaxes.

db.fetchRows("SELECT * FROM persons WHERE name = ?", arguments: ["Arthur"])
db.fetchRows("SELECT * FROM persons WHERE name = :name", arguments: ["name": "Arthur"])

Lazy sequences can not be consumed outside of a database queue, but arrays are OK:

let rows = dbQueue.inDatabase { db in
    return db.fetchAllRows("SELECT ...")          // [Row]
    return fetchRows("SELECT ...").filter { ... } // [Row]
}
for row in rows { ... } // OK

Read row values by index or column name:

let name: String? = row.value(atIndex: 0)
let name: String? = row.value(named: "name")

// Force unwrap when value is not NULL
let id: Int64 = row.value(named: "id")!

// Extract the desired Swift type from the column value:
let bookCount: Int = row.value(named: "bookCount")!
let bookCount64: Int64 = row.value(named: "bookCount")!
let hasBooks: Bool = row.value(named: "bookCount")!     // false when 0

WARNING: type casting requires a very careful use of the as operator (see rdar://problem/21676393):

row.value(named: "bookCount")! as Int   // OK: Int
row.value(named: "bookCount") as Int?   // OK: Int?
row.value(named: "bookCount") as! Int   // NO NO NO DON'T DO THAT!
row.value(named: "bookCount") as? Int   // NO NO NO DON'T DO THAT!

Rows as Dictionaries

The row.value(named:) and row.value(atIndex:) methods above require that you know the row structure: which columns are available, in which order.

When you process an unknown row, you will prefer thinking of it as a dictionary of DatabaseValue, an intermediate type between SQLite and your values:

// Test if the column `name` is present:
if let databaseValue = row["name"] {
    // Extract the desired Swift type from the database value:
    let name: String? = databaseValue.value()
}

You can also iterate all the tuples (columnName, databaseValue) in a row:

for (columnName, databaseValue) in row {
    ...
}

Value Queries

Instead of rows, you can directly fetch values, extracted from the first column of the resulting rows.

Like rows, values can be fetched as lazy sequences, arrays, or single value:

dbQueue.inDatabase { db in
    db.fetch(Int.self, "SELECT ...", arguments: ...)      // AnySequence<Int?>
    db.fetchAll(Int.self, "SELECT ...", arguments: ...)   // [Int?]
    db.fetchOne(Int.self, "SELECT ...", arguments: ...)   // Int?
}

Lazy sequences can not be consumed outside of a database queue, but arrays are OK:

let names = dbQueue.inDatabase { db in
    return db.fetchAll(String.self, "SELECT name ...")             // [String?]
    return db.fetch(String.self, "SELECT name ...").filter { ... } // [String?]
}
for name in names { ... } // OK

Sequences and arrays contain optional values. When you are sure that all results are not NULL, unwrap the optionals with the bang ! operator:

// names is [String]
let names = dbQueue.inDatabase { db in
    db.fetchAll(String.self, "SELECT name FROM persons").map { $0! }
}

The db.fetchOne(type:sql:arguments:) function returns an optional value which is nil in two cases: either the SELECT statement yielded no row, or one row with a NULL value. If this ambiguity does not fit your need, use db.fetchOneRow.

Values

The library ships with built-in support for Bool, Int, Int32, Int64, Double, String, Blob, NSDate, NSDateComponents, and Swift enums.

Custom types are supported as well through the DatabaseValueConvertible protocol.

NSDate and NSDateComponents

NSDate and NSDateComponents can be stored and fetched from the database.

Here is the support provided by GRDB.swift for the various date formats supported by SQLite:

SQLite format NSDate NSDateComponents
YYYY-MM-DD Read ¹ Read/Write
YYYY-MM-DD HH:MM Read ¹ Read/Write
YYYY-MM-DD HH:MM:SS Read ¹ Read/Write
YYYY-MM-DD HH:MM:SS.SSS Read/Write ¹ Read/Write
YYYY-MM-DDTHH:MM Read ¹ Read
YYYY-MM-DDTHH:MM:SS Read ¹ Read
YYYY-MM-DDTHH:MM:SS.SSS Read ¹ Read
HH:MM Read/Write
HH:MM:SS Read/Write
HH:MM:SS.SSS Read/Write
Julian Day Number Read ²
now

¹ NSDates are stored and read in the UTC time zone. Missing components are assumed to be zero.

² See https://en.wikipedia.org/wiki/Julian_day

NSDate

Support for NSDate is given by the DatabaseDate helper type.

DatabaseDate stores dates using the format "yyyy-MM-dd HH:mm:ss.SSS" in the UTC time zone.

The storage format of DatabaseDate is lexically comparable with SQLite's CURRENT_TIMESTAMP, which means that your ORDER BY clauses will behave as expected.

Of course, if this format does not fit your needs, feel free to create your own helper type: the DatabaseValueConvertible protocol is there to help you store dates as ISO-8601 strings, timestamp numbers, etc. We provide sample code for storing dates as timestamps below.

Declare DATETIME columns in your tables:

try db.execute(
    "CREATE TABLE persons (" +
    "birthDate DATETIME, " +
    "creationDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP " +
    "...)")

Store NSDate into the database:

let birthDate = NSDate()
try db.execute("INSERT INTO persons (birthDate, ...) " +
                            "VALUES (?, ...)",
                         arguments: [DatabaseDate(birthDate), ...])

Extract NSDate from the database:

let row = db.fetchOneRow("SELECT birthDate, ...")!
let date = (row.value(named: "birthDate") as DatabaseDate?)?.date    // NSDate?

db.fetch(DatabaseDate.self, "SELECT ...")       // AnySequence<DatabaseDate?>
db.fetchAll(DatabaseDate.self, "SELECT ...")    // [DatabaseDate?]
db.fetchOne(DatabaseDate.self, "SELECT ...")    // DatabaseDate?

Use NSDate in a Row Model:

class Person : RowModel {
    var birthDate: NSDate?

    override var storedDatabaseDictionary: [String: DatabaseValueConvertible?] {
        return ["birthDate": DatabaseDate(birthDate), ...]
    }

    override func setDatabaseValue(dbv: DatabaseValue, forColumn column: String) {
        switch column {
        case "birthDate": birthDate = (dbv.value() as DatabaseDate?)?.date
        case ...
        default: super.setDatabaseValue(dbv, forColumn: column)
        }
    }
}

NSDateComponents

Support for NSDateComponents is given by the DatabaseDateComponents helper type.

DatabaseDateComponents reads date components from all date formats supported by SQLite, and stores them in the format of your choice, from HH:MM to YYYY-MM-DD HH:MM:SS.SSS.

Store NSDateComponents into the database:

let components = NSDateComponents()
components.year = 1973
components.month = 9
components.day = 18

// The .YMD format stores "1973-09-18" in the database.
let dbComponents = DatabaseDateComponents(components, format: .YMD)
try db.execute("INSERT INTO persons (birthDate, ...) " +
                            "VALUES (?, ...)",
                         arguments: [dbComponents, ...])

Extract NSDateComponents from the database:

let row = db.fetchOneRow("SELECT birthDate, ...")!
let dbComponents = row.value(named: "birthDate")! as DatabaseDateComponents
dbComponents.format         // .YMD (the actual format found in the database)
dbComponents.dateComponents // NSDateComponents

db.fetch(DatabaseDateComponents.self, "SELECT ...")    // AnySequence<DatabaseDateComponents?>
db.fetchAll(DatabaseDateComponents.self, "SELECT ...") // [DatabaseDateComponents?]
db.fetchOne(DatabaseDateComponents.self, "SELECT ...") // DatabaseDateComponents?

Use NSDateComponents in a Row Model:

class Person : RowModel {
    var birthDateComponents: NSDateComponents?

    override var storedDatabaseDictionary: [String: DatabaseValueConvertible?] {
        // Store birth date as YYYY-MM-DD:
        let dbComponents = DatabaseDateComponents(
            birthDateComponents,
            format: .YMD)
        return ["birthDate": dbComponents, ...]
    }

    override func setDatabaseValue(dbv: DatabaseValue, forColumn column: String) {
        switch column {
        case "birthDate":
            let dbComponents: DatabaseDateComponents? = dbv.value()
            birthDateComponents = dbComponents?.dateComponents
        case ...
        default: super.setDatabaseValue(dbv, forColumn: column)
        }
    }
}

Swift Enums

Swift enums get full support from GRDB.swift as long as their raw values are Int or String.

Given those two enums:

enum Color : Int {
    case Red
    case White
    case Rose
}

enum Grape : String {
    case Chardonnay
    case Merlot
    case Riesling
}

Simply add those two lines:

extension Color : DatabaseIntRepresentable { }
extension Grape : DatabaseStringRepresentable { }

And both types gain database powers:

// Store:
try db.execute("INSERT INTO wines (grape, color) VALUES (?, ?)",
               arguments: [Grape.Merlot, Color.Red])

// Extract from row:
for rows in db.fetchRows("SELECT * FROM wines") {
    let grape: Grape? = row.value(named: "grape")
    let color: Color? = row.value(named: "color")
}

// Direct fetch:
db.fetch(Color.self, "SELECT ...", arguments: ...)    // AnySequence<Color?>
db.fetchAll(Color.self, "SELECT ...", arguments: ...) // [Color?]
db.fetchOne(Color.self, "SELECT ...", arguments: ...) // Color?

Custom Types

Conversion to and from the database is based on the DatabaseValueConvertible protocol:

public protocol DatabaseValueConvertible {
    /// Returns a value that can be stored in the database.
    var databaseValue: DatabaseValue { get }
    
    /// Create an instance initialized to `databaseValue`.
    init?(databaseValue: DatabaseValue)
}

All types that adopt this protocol can be used wherever the built-in types Int, String, etc. are used. without any limitation or caveat.

Unfortunately not all types can adopt this protocol: Swift won't allow non-final classes to adopt DatabaseValueConvertible, and this prevents all our NSObject fellows to enter the game.

As an example, let's write an alternative to the built-in DatabaseDate, and store dates as timestamps. DatabaseTimestamp applies all the best practices for a great GRDB.swift integration:

struct DatabaseTimestamp: DatabaseValueConvertible {
    
    // NSDate conversion
    //
    // We consistently use the Swift nil to represent the database NULL: the
    // date property is a non-optional NSDate, and the NSDate initializer is
    // failable:
    
    /// The represented date
    let date: NSDate
    
    /// Creates a DatabaseTimestamp from an NSDate.
    /// The result is nil if and only if *date* is nil.
    init?(_ date: NSDate?) {
        guard let date = date else {
            return nil
        }
        self.date = date
    }
    
    
    // DatabaseValueConvertible adoption
    
    /// Returns a value that can be stored in the database.
    var databaseValue: DatabaseValue {
        return .Real(date.timeIntervalSince1970)
    }
    
    /// Create an instance initialized to `databaseValue`.
    init?(databaseValue: DatabaseValue) {
        // Double itself adopts DatabaseValueConvertible. So let's avoid
        // handling the raw DatabaseValue, and use built-in Double conversion:
        guard let timeInterval = Double(databaseValue: databaseValue) else {
            return nil
        }
        self.init(NSDate(timeIntervalSince1970: timeInterval))
    }
}

As a DatabaseValueConvertible adopter, DatabaseTimestamp can be stored and fetched from the database just like simple types Int and String:

// Store NSDate
let date = NSDate()
try db.execute("INSERT INTO persons (date, ...) " +
                            "VALUES (?, ...)",
                         arguments: [DatabaseTimestamp(date), ...])

// Extract NSDate from row:
for rows in db.fetchRows("SELECT ...") {
    let date = (row.value(named: "date") as DatabaseTimestamp?)?.date
}

// Direct fetch:
db.fetch(DatabaseTimestamp.self, "SELECT ...")    // AnySequence<DatabaseTimestamp?>
db.fetchAll(DatabaseTimestamp.self, "SELECT ...") // [DatabaseTimestamp?]
db.fetchOne(DatabaseTimestamp.self, "SELECT ...") // DatabaseTimestamp?

Value Extraction in Details

SQLite has a funny way to manage values. It is "funny" because it is a rather long read: https://www.sqlite.org/datatype3.html.

The interested reader should know that GRDB.swift does not use SQLite built-in casting features when extracting values. Instead, it performs its own conversions, based on the storage class of database values:

Storage class Bool Int ³ Int32 Int64 Double String ³ Blob
NULL - - - - - - -
INTEGER Bool ¹ Int ² Int32 ² Int64 Double - -
REAL Bool ¹ Int ² Int32 ² Int64 ² Double - -
TEXT - - - - - String -
BLOB - - - - - - Blob

¹ The only false numbers are 0 (integer) and 0.0 (real).

² You will get a fatal error if the value is too big for Int, Int32 or Int64.

³ Applies also to Int and String-based enums.

Your custom types can perform their own conversions to and from SQLite storage classes.

Prepared Statements

Prepared Statements can be reused.

Update statements:

try dbQueue.inTransaction { db in
    
    let sql = "INSERT INTO persons (name, age) VALUES (:name, :age)"
    let statement = try db.updateStatement(sql)
    
    let persons = [
        ["name": "Arthur", "age": 41],
        ["name": "Barbara"],
    ]
    
    for person in persons {
        let changes = try statement.execute(arguments: QueryArguments(person))
        changes.changedRowCount // The number of rows changed by the statement.
        changes.insertedRowID   // The inserted Row ID.
    }
    
    return .Commit
}

Select statements can fetch rows and values:

dbQueue.inDatabase { db in
    
    let statement = db.selectStatement("SELECT ...")
    
    statement.fetchRows(arguments: ...)          // AnySequence<Row>
    statement.fetchAllRows(arguments: ...)       // [Row]
    statement.fetchOneRow(arguments: ...)        // Row?
    
    statement.fetch(Int.self, arguments: ...)    // AnySequence<Int?>
    statement.fetchAll(Int.self, arguments: ...) // [Int?]
    statement.fetchOne(Int.self, arguments: ...) // Int?
}

Error Handling

No SQLite error goes unnoticed. Yet when such an error happens, some GRDB.swift functions throw a DatabaseError error, and some crash with a fatal error.

The rule is:

  • All methods that read data crash.
  • All methods that write data throw.

Rationale: we assume that all reading errors are either SQL errors that the developer should fix (a syntax error, a wrong column name), or external I/O errors that are beyond repair and better hidden behind a crash. Write errors may be relational errors (violated unique index, missing reference) and you may want to handle relational errors yourselves.

Please open an issue if you need fine tuning of select errors.

// fatal error:
// SQLite error 1 with statement `SELECT foo FROM bar`: no such table: bar
db.fetchAllRows("SELECT foo FROM bar")

do {
    try db.execute(
        "INSERT INTO pets (masterId, name) VALUES (?, ?)",
        arguments: [1, "Bobby"])
} catch let error as DatabaseError {
    // SQLite error 19 with statement `INSERT INTO pets (masterId, name)
    // VALUES (?, ?)` arguments [1, "Bobby"]: FOREIGN KEY constraint failed
    error.description
    
    // The SQLite result code: 19 (SQLITE_CONSTRAINT)
    error.code
    
    // The eventual SQLite message
    // "FOREIGN KEY constraint failed"
    error.message
    
    // The eventual erroneous SQL query
    // "INSERT INTO pets (masterId, name) VALUES (?, ?)"
    error.sql
}

See SQLite Result Codes.

Migrations

Migrations are a convenient way to alter your database schema over time in a consistent and easy way.

Migrations run in order, once and only once. When a user upgrades your application, only non-applied migration are run.

var migrator = DatabaseMigrator()

// v1.0 database
migrator.registerMigration("createPersons") { db in
    try db.execute(
        "CREATE TABLE persons (" +
        "id INTEGER PRIMARY KEY, " +
        "creationDate TEXT, " +
        "name TEXT NOT NULL)")
}

migrator.registerMigration("createBooks") { db in
    try db.execute(
        "CREATE TABLE books (" +
        "uuid TEXT PRIMARY KEY, " +
        "ownerID INTEGER NOT NULL " +
        "        REFERENCES persons(id) " +
        "        ON DELETE CASCADE ON UPDATE CASCADE, " +
        "title TEXT NOT NULL)")
}

// v2.0 database
migrator.registerMigration("AddAgeToPersons") { db in
    try db.execute("ALTER TABLE persons ADD COLUMN age INT")
}

try migrator.migrate(dbQueue)

If you have larger migrations, you might prefer to use Database.executeMultiStatement(). This method takes a SQL string containing multiple statements separated by semi-colons.

migrator.registerMigration("createBooks") { db in
    try db.executeMultiStatement(
        "CREATE TABLE persons (name TEXT NOT NULL);" +
        "INSERT INTO persons (name) VALUES ('Harry');" +
        "INSERT INTO persons (name) VALUES ('Ron');" +
        "INSERT INTO persons (name) VALUES ('Hermione');")
}

You might even store your migration scripts in a text file:

migrator.registerMigration("initialSchema") { (db) -> Void in
    if let filePath = NSBundle.mainBundle().pathForResource("dbMigration01", ofType: "txt") {
        let migrationSql = try String(contentsOfFile: filePath)
        let dbChanges = try db.executeMultiStatement(migrationSql)
        print("Rows affected: \(dbChanges.changedRowCount)")
    }
}

Row Models

RowModel is a class that wraps a table row, or the result of any query. It is designed to be subclassed.

Subclasses opt in RowModel features by overriding all or part of the core methods that define their relationship with the database:

Core Methods fetch insert update delete reload
setDatabaseValue(_:forColumn:) ✓ ¹ ✓
databaseTable ✓ ✓ ² ✓ ² ✓ ²
storedDatabaseDictionary ✓ ✓ ✓ ✓

¹ Insertion requires setDatabaseValue(_:forColumn:) when SQLite automatically generates row IDs.

² Update, delete & reload require a primary key.

Fetching Row Models

The Person subclass below will be our guinea pig. It declares properties for the persons table:

class Person : RowModel {
    var id: Int64!            // matches "id" not null column
    var age: Int?             // matches "age" column
    var name: String?         // matches "name" column
}

The setDatabaseValue(_:forColumn:) method assigns database values to properties:

class Person : RowModel {
    override func setDatabaseValue(dbv: DatabaseValue, forColumn column: String) {
        switch column {
        case "id":   id = dbv.value()    // Extract Int64!
        case "age":  age = dbv.value()   // Extract Int?
        case "name": name = dbv.value()  // Extract String?
        default:     super.setDatabaseValue(dbv, forColumn: column)
        }
    }
}

See Rows as Dictionaries for more information about the DatabaseValue type, and Values about the supported property types.

Now you can fetch lazy sequences of row models, arrays, or single instances:

dbQueue.inDatabase { db in
    // With custom SQL:
    db.fetch(Person.self, "SELECT ...", arguments:...)    // AnySequence<Person>
    db.fetchAll(Person.self, "SELECT ...", arguments:...) // [Person]
    db.fetchOne(Person.self, "SELECT ...", arguments:...) // Person?
    
    // With a key dictionary:
    db.fetchOne(Person.self, key: ["id": 123])            // Person?
}

The db.fetchOne(type:key:) method eats any key dictionary, and returns the first RowModel with matching values. Its result is undefined unless the dictionary is actually a key.

Lazy sequences can not be consumed outside of a database queue, but arrays are OK:

let persons = dbQueue.inDatabase { db in
    return db.fetchAll(Person.self, "SELECT ...")             // [Person]
    return db.fetch(Person.self, "SELECT ...").filter { ... } // [Person]
}
for person in persons { ... } // OK

Ad Hoc Subclasses

Swift makes it very easy to create small and private types. This is a wonderful opportunity to create ad hoc subclasses that provide support for custom queries with extra columns.

We think that this is the killer feature of GRDB.swift :bowtie:. For example:

class PersonsViewController: UITableViewController {
    
    // Private subclass of Person, with an extra `bookCount`:
    private class PersonViewModel : Person {
        var bookCount: Int!
        
        override func setDatabaseValue(dbv: DatabaseValue, forColumn column: String) {
            switch column {
            case "bookCount": bookCount = dbv.value()
            default: super.setDatabaseValue(dbv, forColumn: column)
            }
        }
    }
    
    var persons: [PersonViewModel]!
    
    override func viewWillAppear(animated: Bool) {
        super.viewWillAppear(animated)
        
        persons = dbQueue.inDatabase { db in
            db.fetchAll(PersonViewModel.self,
                "SELECT persons.*, COUNT(*) AS bookCount " +
                "FROM persons " +
                "JOIN books ON books.ownerID = persons.id " +
                "GROUP BY persons.id")
        }
        
        tableView.reloadData()
    }
    
    ...
}

Tables and Primary Keys

Declare a Table given its name and primary key in order to fetch row models by ID:

class Person : RowModel {
    override class var databaseTable: Table? {
        return Table(named: "persons", primaryKey: .RowID("id"))
    }
}

try dbQueue.inDatabase { db in
    // Fetch
    let person = db.fetchOne(Person.self, primaryKey: 123)  // Person?
}

There are three kinds of primary keys:

  • RowID: use it when you rely on automatically generated IDs in an INTEGER PRIMARY KEY column. Beware RowModel does not support the implicit ROWID column (see https://www.sqlite.org/autoinc.html for more information).

  • Column: for single-column primary keys that are not managed by SQLite.

  • Columns: for primary keys that span accross several columns.

RowModels with a multi-column primary key are not supported by Database.fetchOne(type:primaryKey:), which accepts a single value as a key. Instead, use Database.fetchOne(type:key:) that uses a dictionary.

Insert, Update and Delete

With one more method, you get the insert, update, delete methods, plus the convenience save and reload methods.

class Person : RowModel {
    // The values stored in the database:
    override var storedDatabaseDictionary: [String: DatabaseValueConvertible?] {
        return ["id": id, "name": name, "age": age]
    }
}

try dbQueue.inTransaction { db in
    
    // Insert
    let person = Person(name: "Arthur", age: 41)
    try person.insert(db)
    
    // Update
    person.age = 42
    try person.update(db)
    
    // Reload
    person.age = 666
    try person.reload(db)
    
    // Delete
    try person.delete(db)
    
    return .Commit
}

Models that declare a RowID primary key have their id automatically set after successful insertion (with the setDatabaseValue(_:forColumn:) method).

Other primary keys (single or multiple columns) are not managed by GRDB: you have to manage them yourself. You can for example override the insert primitive method, and make sure your primary key is set before calling super.insert.

Preventing Useless UPDATE Statements

The update() method always executes an UPDATE statement. When the row model has not been edited, this database access is generally useless.

Avoid it with the edited property, which returns whether the row model has changes that have not been saved:

let json = ...
try dbQueue.inTransaction { db in
    // Fetches or create a new person given its ID:
    let person = db.fetchOne(Person.self, primaryKey: json["id"]) ?? Person()
    
    // Apply json payload:
    person.updateFromJSON(json)
                 
    // Saves the person if it is edited (fetched then modified, or created):
    if person.edited {
        person.save(db) // inserts or updates
    }
    
    return .Commit
}

Note that edited is based on value comparison: setting a property to the same value does not set the edited flag.

RowModel Errors

RowModel methods can throw DatabaseError and also specific errors of type RowModelError:

  • RowModelError.RowModelNotFound: thrown by update and reload when the primary key does not match any row in the database.

Advice

RowModel is not a smart class. It is no replacement for Core Data. It does not provide any uniquing. It does not perform any SQL request behind your back. It has no knowledge of your database schema, and no notion of external references and model relationships.

Based on those facts, here are a few hints:

Autoincrement

For "autoincremented" ids, declare your id column as INTEGER PRIMARY KEY, and declare a RowID primary key:

CREATE TABLE persons {
    id INTEGER PRIMARY KEY,
    ...
}
class Person : RowModel {
    id: Int64!
    
    /// The table definition.
    override class var databaseTable: Table? {
        return Table(named: "persons", primaryKey: .RowID("id"))
    }
    
    /// The values that should be stored in the database.
    override var storedDatabaseDictionary: [String: DatabaseValueConvertible?] {
        return ["id": id, ...]
    }
    
    /// Updates `self` with a database value.
    override func setDatabaseValue(dbv: DatabaseValue, forColumn column: String) {
        switch column {
        case "id": id = dbv.value()
        case ...
        default:   super.setDatabaseValue(dbv, forColumn: column)
        }
    }
}

let person = Person(...)
person.id   // nil
try person.insert(db)
person.id   // some value

Default Values

Avoid default values in table declarations. RowModel doesn't know about them, and those default values won't be present in a row model after it has been inserted.

For example, avoid the table below:

CREATE TABLE persons (
    id INTEGER PRIMARY KEY,
    creationDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,   -- Avoid
    ...
)

Instead, override insert() and provide the default value there:

CREATE TABLE persons (
    id INTEGER PRIMARY KEY,
    creationDate DATETIME NOT NULL,   -- OK
    ...
)
class Person : RowModel {
    override func insert(db: Database) throws {
        if creationDate == nil {
            creationDate = NSDate()
        }
        try super.insert(db)
    }
}

INSERT OR REPLACE

RowModel does not provide any API which executes a INSERT OR REPLACE query. Instead, consider adding an ON CONFLICT clause to your table definition, and let the simple insert() method perform the eventual replacement:

CREATE TABLE persons (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE ON CONFLICT REPLACE,
    ...
)
let person = Person(name: "Arthur")
person.insert(db)   // Replace any existing person named "Arthur"

Thanks