<commit_before>GRDB.swift
==========

GRDB.swift is an [SQLite](https://www.sqlite.org) toolkit for Swift 2, from the author of [GRMustache](https://github.com/groue/GRMustache).

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

**November 4, 2015: GRDB.swift 0.27.0 is out** - [Release notes](CHANGELOG.md). Follow [@groue](http://twitter.com/groue) on Twitter for release announcements and usage tips.

Jump to:

- [Features](#features)
- [Requirements](#requirements)
- [Usage](#usage)
- [Benchmarks](#benchmarks)
- **[Installation](#installation)**
- **[Documentation](#documentation)**
- [Sample Code](#sample-code)


Features
--------

- **A low-level SQLite API** that leverages the Swift 2 standard library.
- **No smart query builder**. Your SQL skills are welcome here.
- **A Record 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**
- **Database changes observation hooks**


Requirements
------------

- iOS 7.0+ / OSX 10.9+
- Xcode 7


Usage
-----

**SQLite API**

```swift
import GRDB

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

try dbQueue.inDatabase { db in
    // Create tables
    try db.execute("CREATE TABLE wines (...)")
    
    // Insert
    let changes = try db.execute("INSERT INTO wines (color, name) VALUES (?, ?)",
        arguments: [Color.Red, "Pomerol"])
    let wineId = changes.insertedRowID
    print("Inserted wine id: \(wineId)")
    
    // Fetch rows
    for row in Row.fetch(db, "SELECT * FROM wines") {
        let name: String = row.value(named: "name")
        let color: Color = row.value(named: "color")
        print(name, color)
    }
    
    // Fetch values
    let redWineCount = Int.fetchOne(db,
        "SELECT COUNT(*) FROM wines WHERE color = ?",
        arguments: [Color.Red])!
}
```

**Using Records**

```swift
// Define Record subclass
class Wine : Record { ... }

try dbQueue.inDatabase { db in
    // Store
    let wine = Wine(color: .Red, name: "Pomerol")
    try wine.insert(db)
    print("Inserted wine id: \(wine.id)")
    
    // Fetch
    for wine in Wine.fetch(db, "SELECT * FROM wines") {
        print(wine.name, wine.color)
    }
}
```

**Users of [ccgus/fmdb](https://github.com/ccgus/fmdb)** will feel at ease with GRDB.swift. They may find GRDB to be easier when [fetching](#fetch-queries) data from the database. And they'll definitely be happy that [database errors](#error-handling) are handled in the Swift way.

**Users of [stephencelis/SQLite.swift](https://github.com/stephencelis/SQLite.swift)** may eventually find that a straightforward API around SQL is not a bad alternative.


Benchmarks
----------

GRDB.swift runs as fast as ccgus/fmdb, or faster.

For precise benchmarks, select the GRDBOSX scheme, run the tests in Release configuration, and check the results under the "Performance" tab in the Xcode Report Navigator.


Installation
------------

### iOS7

You can use GRDB.swift in a project targetting iOS7. See [GRDBDemoiOS7](DemoApps/GRDBDemoiOS7) for more information.


### CocoaPods

[CocoaPods](http://cocoapods.org/) is a dependency manager for Xcode projects.

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

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

pod 'GRDB.swift', '0.27.0'
```


### Carthage

[Carthage](https://github.com/Carthage/Carthage) is another dependency manager for Xcode projects.

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

```
github "groue/GRDB.swift" == 0.27.0
```


### Manually

1. Download a copy of GRDB.swift.
2. Embed the `GRDB.xcodeproj` project in your own project.
3. Add the `GRDBOSX` or `GRDBiOS` target in the **Target Dependencies** section of the **Build Phases** tab of your application target.
4. Add `GRDB.framework` to the **Embedded Binaries** section of the **General**  tab of your target.

See [GRDBDemoiOS](DemoApps/GRDBDemoiOS) for an example of such integration.


Documentation
=============

**Reference**

- [GRDB Reference](http://cocoadocs.org/docsets/GRDB.swift/0.27.0/index.html) on cocoadocs.org. Beware that it is incomplete: you may prefer reading the inline documentation right into the [source](https://github.com/groue/GRDB.swift/tree/master/GRDB).

**[SQLite API](#sqlite-api)**

- [SQLite Database](#sqlite-database)
- [Creating Tables](#creating-tables)
- [Inserting Rows](#inserting-rows)
- [Fetch Queries](#fetch-queries)
    - [Row Queries](#row-queries)
    - [Value Queries](#value-queries)
- [Values](#values)
    - [NSData](#nsdata-and-memory-savings)
    - [NSDate and NSDateComponents](#nsdate-and-nsdatecomponents)
    - [NSCoding](#nscoding)
    - [Swift enums](#swift-enums)
    - [Custom Value Types](#custom-value-types)
- [Prepared Statements](#prepared-statements)
- [Error Handling](#error-handling)
- [Transactions](#transactions)
- [Concurrency](#concurrency)
- [Raw SQLite Pointers](#raw-sqlite-pointers)

**[Application Tools](#application-tools)**

- [Migrations](#migrations): Transform your database as your application evolves.
- [Database Changes Observation](#database-changes-observation): A robust way to perform post-commit and post-rollback actions.
- [RowConvertible Protocol](#rowconvertible-protocol): Turn database rows into handy types, without sacrificing performance.
- [Records](#records): CRUD operations and changes tracking.


SQLite API
==========

## SQLite Database

You access SQLite databases through **thread-safe database queues** (inspired by [ccgus/fmdb](https://github.com/ccgus/fmdb)):

```swift
import GRDB

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

The database file is created if it does not already exist.

The connection is closed when the database queue gets deallocated.


**Configure** databases:

```swift
var config = Configuration()
config.foreignKeysEnabled = true // Default true
config.readonly = false          // Default false
config.trace = LogSQL            // The built-in LogSQL function logs all SQL statements with NSLog.

let dbQueue = try DatabaseQueue(
    path: "/path/to/database.sqlite",
    configuration: config)
```

See [Concurrency](#concurrency) for more details on database configuration.


The `inDatabase` and `inTransaction` methods perform your **database statements** in a dedicated, serial, queue:

```swift
// Extract values from the database:
let rows = dbQueue.inDatabase { db in
    Row.fetchAll(db, "SELECT ...")
}

// Execute database statements:
dbQueue.inDatabase { db in
    for person in Person.fetch(db, "SELECT...") {
        ...
    }
}

// Wrap database statements in a transaction:
try dbQueue.inTransaction { db in
    try db.execute("INSERT ...")
    try db.execute("DELETE FROM ...")
    return .Commit
}
```

See [Transactions](#transactions) for more information about GRDB transaction handling.


## Creating Tables

**To create a database table**, execute a [CREATE TABLE](https://www.sqlite.org/lang_createtable.html) statement:

```swift
try dbQueue.inDatabase { db in
    try db.execute(
        "CREATE TABLE persons (" +
            "id INTEGER PRIMARY KEY, " +
            "name TEXT NOT NULL, " +
            "email TEXT" +
        ")")
}
```

Database schemas usually evolve over time: you should probably create your tables with [migrations](#migrations).


## Inserting Rows

**To insert a database row**, execute an [INSERT](https://www.sqlite.org/lang_insert.html) statement:

```swift
try dbQueue.inDatabase { db in
    try db.execute("INSERT ...", arguments: ...)
}
```

Arguments are optional arrays or dictionaries that fill the positional `?` and colon-prefixed keys like `:name` in the query:

```swift
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": 39])
```

See [Values](#values) for more information on supported arguments types (Bool, Int, String, NSDate, Swift enums, etc.).

You extract the inserted Row ID from the result of the `execute` method:

```swift
let insertedRowID = try db.execute(
    "INSERT INTO persons (name, age) VALUES (?, ?)",
    arguments: ["Arthur", 36]).insertedRowID
```

Don't miss the [Record](#records) class, which helps inserting rows in the database:

```swift
let person = Person(name: "Arthur", age: 36)
try person.insert(db)
print("Inserted person id: \(person.id)")
```


## Fetch Queries

You can fetch **Rows**, **Values**, and **Records**:

```swift
dbQueue.inDatabase { db in
    Row.fetch(db, "SELECT ...", ...)       // DatabaseSequence<Row>
    Row.fetchAll(db, "SELECT ...", ...)    // [Row]
    Row.fetchOne(db, "SELECT ...", ...)    // Row?
    
    String.fetch(db, "SELECT ...", ...)    // DatabaseSequence<String>
    String.fetchAll(db, "SELECT ...", ...) // [String]
    String.fetchOne(db, "SELECT ...", ...) // String?
    
    Person.fetch(db, "SELECT ...", ...)    // DatabaseSequence<Person>
    Person.fetchAll(db, "SELECT ...", ...) // [Person]
    Person.fetchOne(db, "SELECT ...", ...) // Person?
    
    Person.fetch(db, keys: ...)            // DatabaseSequence<Person>
    Person.fetchAll(db, keys: ...)         // [Person]
    Person.fetchOne(db, key: ...)          // Person?
}
```

Most methods take a custom SQL query as an argument. If SQL is not your cup of tea, then maybe you are looking for a query builder. [stephencelis/SQLite.swift](https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#selecting-rows) is a pretty popular one.

- [Row Queries](#row-queries)
- [Value Queries](#value-queries)
- [RowConvertible Protocol](#rowconvertible-protocol) and [Records](#records)


### Row Queries

- [Fetching Rows](#fetching-rows)
- [Column Values](#column-values)
- [Rows as Dictionaries](#rows-as-dictionaries)
- [Convenience Rows](#convenience-rows)


#### Fetching Rows

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

```swift
dbQueue.inDatabase { db in
    Row.fetch(db, "SELECT ...", arguments: ...)     // DatabaseSequence<Row>
    Row.fetchAll(db, "SELECT ...", arguments: ...)  // [Row]
    Row.fetchOne(db, "SELECT ...", arguments: ...)  // Row?
}
```

Arguments are optional arrays or dictionaries that fill the positional `?` and colon-prefixed keys like `:name` in the query:

```swift
let rows = Row.fetch(db,
    "SELECT * FROM persons WHERE name = ?",
    arguments: ["Arthur"])

let rows = Row.fetch(db,
    "SELECT * FROM persons WHERE name = :name",
    arguments: ["name": "Arthur"])
```

See [Values](#values) for more information on supported arguments types (Bool, Int, String, NSDate, Swift enums, etc.).

Both `fetch` and `fetchAll` let you iterate the full list of fetched rows. The differences are:

- The array returned by `fetchAll` can take a lot of memory. Yet it can be iterated on any thread.
- The sequence returned by `fetch` only goes to the database as you iterate it, and is thus more memory efficient. The price for this efficiency is that the sequence must be iterated in the database queue (you'll get a fatal error if you do otherwise).
- The sequence returned by `fetch` will return a different set of results if the database has been modified between two sequence iterations.

Row sequences also grant the fastest access to the database. This performance advantage comes with extra precautions:

> :point_up: **Don't turn a row sequence into an array** with `Array(rowSequence)` or `rowSequence.filter { ... }`: you would not get the distinct rows you expect. To get an array, use `Row.fetchAll(...)`.
> 
> :point_up: **Make sure you copy a row** whenever you extract it from a sequence for later use: `row.copy()`. This does not apply to row arrays, which already contain independent copies of the database rows.


#### Column Values

**Read column values** by index or column name:

```swift
let name: String = row.value(atIndex: 0)    // 0 is the leftmost column
let name: String = row.value(named: "name")
```

Make sure to ask for an optional when the value may be NULL:

```swift
let name: String? = row.value(named: "name")
```

The `value` function returns the type you ask for:

```swift
let bookCount: Int     = row.value(named: "bookCount")
let bookCount64: Int64 = row.value(named: "bookCount")
let hasBooks: Bool     = row.value(named: "bookCount")  // false when 0
```

Did I say the type you ask for? See [Values](#values) for more information on supported value types:

```swift
let dateString: String = row.value(named: "date") // "2015-09-11 18:14:15.123"
let date: NSDate       = row.value(named: "date") // NSDate
self.date = row.value(named: "date") // Depends on the type of the property.
```

You can also use the `as` type casting operator:

```swift
row.value(...) as Int
row.value(...) as Int?
row.value(...) as Int!
```

> :warning: **Warning**: avoid the `as!` and `as?` operators (see [rdar://21676393](http://openradar.appspot.com/radar?id=4951414862249984)):
> 
> ```swift
> row.value(...) as! Int   // NO NO NO DON'T DO THAT!
> row.value(...) as? Int   // NO NO NO DON'T DO THAT!
> ```

Generally speaking, you can extract the type you need, *provided it can be converted from the underlying SQLite value*:

- **Successful conversions include:**
    
    - Numeric (integer and real) SQLite values to Swift Int, Int32, Int64, Double and Bool (zero is the only false boolean).
    - Text SQLite values to Swift String.
    - Blob SQLite values to NSData.
    
    See [Values](#values) for more information on supported types (NSDate, Swift enums, etc.).

- **Invalid conversions return nil.**

    ```swift
    row.value(named: "foo") as String  // "bar"
    row.value(named: "foo") as NSDate? // nil: can't convert "bar" to NSDate
    ```

- **GRDB crashes when you try to convert NULL to a non-optional value.**
    
    This behavior is notably different from SQLite C API, or from ccgus/fmdb, that both turn NULL to 0 when extracting an integer, for example.
    
- **The convenience conversions of SQLite, such as Blob to String or String to Integer are not guaranteed to apply.** You must not rely on them.


#### Rows as Dictionaries

You may prefer thinking of rows as dictionaries of `DatabaseValue`, an intermediate type between SQLite and your values:

```swift
// Test if the column `date` is present:
if let databaseValue = row["date"] {
    // Pick the type you need:
    let dateString: String = databaseValue.value() // "2015-09-11 18:14:15.123"
    let date: NSDate = databaseValue.value()       // NSDate
    self.date = databaseValue.value() // Depends on the type of the property.
}
```

Iterate all the tuples (columnName, databaseValue) in a row, from left to right:

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

Rows are not real dictionaries, though. They may contain duplicate keys:

```swift
let row = Row.fetchOne(db, "SELECT 1 AS foo, 2 AS foo")!
row.columnNames     // ["foo", "foo"]
row.databaseValues  // [1, 2]
row["foo"]          // 1 (the value for the leftmost column "foo")
for (columnName, databaseValue) in row { ... } // ("foo", 1), ("foo", 2)
```


Still, as a convenience, row can be converted to and from **NSDictionary** (in case of duplicate colum names, the leftmost value is returned):

```swift
row.toDictionary()  // NSDictionary
```


#### Convenience Rows

Rows is a fundamental type in GRDB, used by many other APIs.

From time to time, you'll want to build a custom one from scratch. Use the dictionary initializer:

```swift
Row(dictionary: ["name": "foo", "date": nil])
```

See [Values](#values) for more information on supported types.


### Value Queries

Instead of rows, you can directly fetch **values**. Like rows, fetch them as **sequences**, **arrays**, or **single** values. Values are extracted from the leftmost column of the SQL queries:

```swift
dbQueue.inDatabase { db in
    Int.fetch(db, "SELECT ...", arguments: ...)    // DatabaseSequence<Int>
    Int.fetchAll(db, "SELECT ...", arguments: ...) // [Int]
    Int.fetchOne(db, "SELECT ...", arguments: ...) // Int?

    // When database may contain NULL:
    Optional<Int>.fetch(db, "SELECT ...", arguments: ...)    // DatabaseSequence<Int?>
    Optional<Int>.fetchAll(db, "SELECT ...", arguments: ...) // [Int?]
}
```

For example:

```swift
dbQueue.inDatabase { db in
    // The number of persons with an email ending in @domain.com:
    let count = Int.fetchOne(db,
        "SELECT COUNT(*) FROM persons WHERE email LIKE ?",
        arguments: ["<EMAIL>"])!
    
    // The emails of people who own at least two pets:
    let emails = String.fetch(db,
        "SELECT persons.email " +
        "FROM persons " +
        "JOIN pets ON pets.masterId = persons.id " +
        "GROUP BY persons.id " +
        "HAVING COUNT(pets.id) >= 2")
}
```

Both `fetch` and `fetchAll` let you iterate the full list of fetched values. The differences are:

- The array returned by `fetchAll` can take a lot of memory. Yet it can be iterated on any thread.
- The sequence returned by `fetch` only goes to the database as you iterate it, and is thus more memory efficient. The price for this efficiency is that the sequence must be iterated in the database queue (you'll get a fatal error if you do otherwise).
- The sequence returned by `fetch` will return a different set of results if the database has been modified between two sequence iterations.

`fetchOne` returns an optional value which is nil in two cases: either the SELECT statement yielded no row, or one row with a NULL value.


## Values

GRDB ships with built-in support for the following value types:

- **Swift**:
    
    Bool, Double, Int, Int32, Int64, String, [Swift enums](#swift-enums).
    
- **Foundation**:
    
    [NSCoding](#nscoding), [NSData](#nsdata-and-memory-savings), [NSDate](#nsdate-and-nsdatecomponents), [NSDateComponents](#nsdate-and-nsdatecomponents), NSNull, NSNumber, NSString, NSURL.
    
- **CoreGraphics**:
    
    CGFloat.

All those types can be [stored](#inserting-rows):

```swift
let url: NSURL = ...
let verified: Bool = ...
try db.execute(
    "INSERT INTO urls (url, verified) VALUES (?, ?)",
    arguments: [url, verified])
```

They can be [extracted from rows](#column-values):

```swift
for row in Row.fetch(db, "SELECT * FROM urls") {
    let url: NSURL = row.value(named: "url")
    let verified: Bool = row.value(named: "verified")
}
```

They can be [directly fetched](#value-queries) from the database:

```swift
let urls = NSURL.fetchAll(db, "SELECT url FROM urls")  // [NSURL]
```

Your custom value types are supported as well, through the [DatabaseValueConvertible](#custom-value-types) protocol.


### NSData (and Memory Savings)

**NSData** suits the BLOB SQLite columns. It can be stored and fetched from the database just like other value types:

```swift
let row = Row.fetchOne(db, "SELECT data, ...")!
let data: NSData = row.value(named: "data")

NSData.fetch(db, "SELECT ...")       // DatabaseSequence<NSData>
NSData.fetchAll(db, "SELECT ...")    // [NSData]
NSData.fetchOne(db, "SELECT ...")    // NSData?
```

Yet, when extracting NSData from a row, **you have the opportunity to save memory by not copying the data fetched by SQLite**, using the `dataNoCopy()` method:

```swift
for row in Row.fetch(db, "SELECT data, ...") {
    let data = row.dataNoCopy(named: "data")     // NSData?

    // When the column `data` may not be there:
    if row.hasColumn("data") {
        let data = row.dataNoCopy(named: "data") // NSData?
    }
}
```

> :point_up: **Note**: The non-copied data does not live longer that the iteration step: make sure that you do not use it past this point.

Compare with the **anti-patterns** below:

```swift
for row in Row.fetch(db, "SELECT data, ...") {
    // Data is copied, row after row:
    let data: NSData = row.value(named: "data")
    
    // Data is copied, row after row:
    if let databaseValue = row["data"] {
        let data: NSData = databaseValue.value()
    }
}

// All rows have been copied in memory when the loop begins:
for row in Row.fetchAll(db, "SELECT data, ...") {
    // Too late to do the right thing:
    let data = row.dataNoCopy(named: "data")
}
```


### NSDate and NSDateComponents

[**NSDate**](#nsdate) and [**NSDateComponents**](#nsdatecomponents) can be stored and fetched from the database.

Here is the support provided by GRDB.swift for the various [date formats](https://www.sqlite.org/lang_datefunc.html) 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-DD**T**HH:MM         |     Read ¹   |       Read       |
| YYYY-MM-DD**T**HH:MM:SS      |     Read ¹   |       Read       |
| YYYY-MM-DD**T**HH: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

**GRDB stores NSDate using the format "yyyy-MM-dd HH:mm:ss.SSS" in the UTC time zone.**

> :point_up: **Note**: This format is lexically comparable with SQLite's CURRENT_TIMESTAMP, which means that your ORDER BY clauses will behave as expected.
>
> Yet, this format may not fit your needs. We provide below some sample code for [storing dates as timestamps](#custom-value-types). You can adapt it for your application.

Declare DATETIME columns in your tables:

```swift
try db.execute(
    "CREATE TABLE persons (" +
    "creationDate DATETIME, " +
    "...)")
```

Store NSDate into the database:

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

Extract NSDate from the database:

```swift
let row = Row.fetchOne(db, "SELECT creationDate, ...")!
let date: NSDate = row.value(named: "creationDate")

NSDate.fetch(db, "SELECT ...")       // DatabaseSequence<NSDate>
NSDate.fetchAll(db, "SELECT ...")    // [NSDate]
NSDate.fetchOne(db, "SELECT ...")    // NSDate?
```

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


#### NSDateComponents

NSDateComponents is indirectly supported, through the **DatabaseDateComponents** helper type.

DatabaseDateComponents reads date components from all [date formats supported by SQLite](https://www.sqlite.org/lang_datefunc.html), and stores them in the format of your choice, from HH:MM to YYYY-MM-DD HH:MM:SS.SSS.

Declare DATETIME columns in your tables:

```swift
try db.execute(
    "CREATE TABLE persons (" +
    "birthDate DATETIME, " +
    "...)")
```

Store NSDateComponents into the database:

```swift
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:

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

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

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


### NSCoding

NSCoding is indirectly supported, through the **DatabaseCoder** helper type.

DatabaseCoder reads and writes objects that adopt NSCoding into Blob columns.

For example, store NSArray into the database:

```swift
let ints = [1, 2, 3]
try db.execute(
    "INSERT INTO ... (ints, ...) VALUES (?, ...)",
    arguments: [DatabaseCoder(ints), ...])
```

Extract NSArray from the database:

```swift
let row = Row.fetchOne(db, "SELECT ints ...")!
let coder = row.value(named: "ints") as DatabaseCoder // DatabaseCoder
let array = coder.object as! NSArray                   // NSArray
let ints = array.map { $0 as! Int }                    // [Int]

DatabaseCoder.fetch(db, "SELECT ...")    // DatabaseSequence<DatabaseCoder>
DatabaseCoder.fetchAll(db, "SELECT ...") // [DatabaseCoder]
DatabaseCoder.fetchOne(db, "SELECT ...") // DatabaseCoder?
```

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


### Swift Enums

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

Given those two enums:

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

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

Simply add those two lines:

```swift
extension Color : DatabaseIntRepresentable { } // DatabaseInt32Representable for Int32, DatabaseInt64Representable for Int64
extension Grape : DatabaseStringRepresentable { }
```

And both types gain database powers:

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

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

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

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


### Custom Value Types

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

```swift
public protocol DatabaseValueConvertible {
    /// Returns a value that can be stored in the database.
    var databaseValue: DatabaseValue { get }
    
    /// Returns an instance initialized from databaseValue, if possible.
    static func fromDatabaseValue(databaseValue: DatabaseValue) -> Self?
}
```

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

The `databaseValue` property returns [DatabaseValue](GRDB/Core/DatabaseValue.swift), a type that wraps the five types supported by SQLite: NULL, Int64, Double, String and NSData.

The `fromDatabaseValue()` factory method returns an instance of your custom type, if the databaseValue contains a suitable value.

As an example, let's write an alternative to the built-in [NSDate](#nsdate-and-nsdatecomponents) behavior, and store dates as timestamps. Our sample DatabaseTimestamp type applies all the best practices for a great GRDB.swift integration:

```swift
struct DatabaseTimestamp: DatabaseValueConvertible {
    
    // NSDate conversion
    //
    // Value types should consistently use the Swift nil to represent the
    // database NULL: the date property is a non-optional NSDate.
    let date: NSDate
    
    // As a convenience, the NSDate initializer accepts an optional NSDate, and
    // is failable: 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 DatabaseValue(double: date.timeIntervalSince1970)
    }
    
    /// Returns an instance initialized from *databaseValue*, if possible.
    static func fromDatabaseValue(databaseValue: DatabaseValue) -> DatabaseTimestamp? {
        // Double itself adopts DatabaseValueConvertible:
        guard let timeInterval = Double.fromDatabaseValue(databaseValue) else {
            // No Double, no NSDate!
            return nil
        }
        return DatabaseTimestamp(NSDate(timeIntervalSince1970: timeInterval))
    }
}
```

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

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

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

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

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


## Prepared Statements

**Prepared Statements** let you prepare an SQL query and execute it later, several times if you need, with different arguments.

There are two kinds to prepared statements: **select statements**, and **update statements**:

```swift
try dbQueue.inTransaction { db in
    let updateSQL = "INSERT INTO persons (name, age) VALUES (:name, :age)"
    let updateStatement = db.updateStatement(updateSQL)
    
    let selectSQL = "SELECT * FROM persons WHERE name = ?"
    let selectStatement = db.selectStatement(selectSQL)
}
```

The `?` and colon-prefixed keys like `:name` in the SQL query are the statement arguments. You set them with arrays or dictionaries (arguments are actually of type StatementArguments, which happens to adopt the ArrayLiteralConvertible and DictionaryLiteralConvertible protocols).

```swift
// INSERT INTO persons (name, age) VALUES (:name, :age)
updateStatement.arguments = ["name": "Arthur", "age": 41]

// SELECT * FROM persons WHERE name = ?
selectStatement.arguments = ["Arthur"]
```

After arguments are set, you can execute the prepared statement:

```swift
let changes = try updateStatement.execute()
changes.changedRowCount // The number of rows changed by the statement.
changes.insertedRowID   // For INSERT statements, the inserted Row ID.

for row in Row.fetch(selectStatement) { ... }
for person in Person.fetch(selectStatement) { ... }
```

It is possible to set the arguments at the moment of the statement execution:

```swift
// INSERT INTO persons (name, age) VALUES (:name, :age)
try statement.execute(arguments: ["name": "Arthur", "age": 41])

// SELECT * FROM persons WHERE name = ?
let person = Person.fetchOne(selectStatement, arguments: ["Arthur"])
```

Select statements can be used wherever a raw SQL query would fit:

```swift
Row.fetch(statement, arguments: ...)       // DatabaseSequence<Row>
Row.fetchAll(statement, arguments: ...)    // [Row]
Row.fetchOne(statement, arguments: ...)    // Row?

String.fetch(statement, arguments: ...)    // DatabaseSequence<String>
String.fetchAll(statement, arguments: ...) // [String]
String.fetchOne(statement, arguments: ...) // String?

Person.fetch(statement, arguments: ...)    // DatabaseSequence<Person>
Person.fetchAll(statement, arguments: ...) // [Person]
Person.fetchOne(statement, arguments: ...) // Person?
```

See [Row Queries](#row-queries), [Value Queries](#value-queries), [RowConvertible](#rowconvertible-protocol), and [Records](#fetching-records) for more information.


## 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 *write* to the database throw.
- All other methods crash without notice (but with a detailed error message).

```swift
// fatal error:
// SQLite error 1 with statement `SELECT foo FROM bar`: no such table: bar
Row.fetchAll(db, "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](https://www.sqlite.org/rescode.html).



## Transactions

The `DatabaseQueue.inTransaction()` method opens a SQLite transaction:

```swift
try dbQueue.inTransaction { db in
    let wine = Wine(color: .Red, name: "Pomerol")
    try wine.insert(db)
    return .Commit
}
```

A ROLLBACK statement is issued if an error is thrown within the transaction block.

Otherwise, transactions are guaranteed to succeed, *provided there is a single DatabaseQueue connected to the database file*. See [Concurrency](#concurrency) for more information about concurrent database access.

If you want to insert a transaction between other database statements, and group those in a single block of code protected by the the database queue, you can use the Database.inTransaction() function:

```swift
try dbQueue.inDatabase { db in
    ...
    try db.inTransaction {
        ...
        return .Commit
    }
    ...
}
```


## Concurrency

**When your application has a single DatabaseQueue connected to the database file, it has no concurrency issue.** That is because all your database statements are executed in a single serial dispatch queue that is connected alone to the database.

**Things turn more complex as soon as there are several connections to a database file.**

SQLite concurrency management is fragmented. Documents of interest include:

- General discussion about isolation in SQLite: https://www.sqlite.org/isolation.html
- Types of locks and transactions: https://www.sqlite.org/lang_transaction.html
- WAL journal mode: https://www.sqlite.org/wal.html
- Busy handlers: https://www.sqlite.org/c3ref/busy_handler.html

By default, GRDB opens database in the **default journal mode**, uses **IMMEDIATE transactions**, and registers **no busy handler** of any kind.

See [Configuration](GRDB/Core/Configuration.swift) type and [DatabaseQueue.inTransaction()](GRDB/Core/DatabaseQueue.swift) method for more precise handling of transactions and eventual SQLITE_BUSY errors.


## Raw SQLite Pointers

Not all SQLite APIs are exposed in GRDB.

The `Database.sqliteConnection` and `Statement.sqliteStatement` properties provide the raw pointers that are suitable for [SQLite C API](https://www.sqlite.org/c3ref/funclist.html):

```swift
dbQueue.inDatabase { db in
    let sqliteConnection = db.sqliteConnection
    sqlite3_db_config(sqliteConnection, ...)
    
    let statement = db.selectStatement("SELECT ...")
    let sqliteStatement = statement.sqliteStatement
    sqlite3_step(sqliteStatement)
}
```

> :point_up: **Notes**
>
> - Those pointers are owned by GRDB: don't close connections or finalize statements created by GRDB.
> - SQLite connections are opened in the [Multi-thread mode](https://www.sqlite.org/threadsafe.html), which means that **they are not thread-safe**. Make sure you touch raw databases and statements inside the database queues.

Before jumping in the low-level wagon, here is a reminder of SQLite APIs supported by GRDB:

- Connections & statements, obviously.
- Errors (pervasive)
    - [sqlite3_errmsg](https://www.sqlite.org/c3ref/errcode.html)
- Inserted Row IDs (as the result of Database.execute()).
    - [sqlite3_last_insert_rowid](https://www.sqlite.org/c3ref/last_insert_rowid.html)
- Changes count (as the result of Database.execute()).
    - [sqlite3_changes](https://www.sqlite.org/c3ref/changes.html)
- Busy mode (see [Concurrency](#concurrency)).
    - [sqlite3_busy_handler](https://www.sqlite.org/c3ref/busy_handler.html)
    - [sqlite3_busy_timeout](https://www.sqlite.org/c3ref/busy_timeout.html)
- Update, commit and rollback hooks (see [Database Changes Observation](#database-changes-observation)):
    - [sqlite3_update_hook](https://www.sqlite.org/c3ref/update_hook.html)
    - [sqlite3_commit_hook](https://www.sqlite.org/c3ref/commit_hook.html)
    - [sqlite3_rollback_hook](https://www.sqlite.org/c3ref/commit_hook.html)


Application Tools
=================

On top of the SQLite API described above, GRDB provides a toolkit for applications. While none of those are mandatory, all of them help dealing with the database:

- [Migrations](#migrations): Transform your database as your application evolves.
- [Database Changes Observation](#database-changes-observation): A robust way to perform post-commit and post-rollback actions.
- [RowConvertible Protocol](#rowconvertible-protocol): Turn database rows into handy types, without sacrificing performance.
- [Records](#records): CRUD operations and changes tracking.


## 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 migrations are run.

```swift
var migrator = DatabaseMigrator()

// v1.0 database
migrator.registerMigration("createTables") { db in
    try db.execute("CREATE TABLE persons (...)")
    try db.execute("CREATE TABLE books (...)")
}

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

migrator.registerMigration("AddYearToBooks") { db in
    try db.execute("ALTER TABLE books ADD COLUMN year INT")
}

// (Insert migrations for future versions here)

try migrator.migrate(dbQueue)
```

**Each migration runs in a separate transaction.** Should one throw an error, its transaction is rollbacked, subsequent migrations do not run, and the error is eventually thrown by `migrator.migrate(dbQueue)`.

**The memory of applied migrations is stored in the database itself** (in a reserved table). When you are tuning your migrations, you may need to execute one several times. All you need then is to feed your application with a database file from a previous state.

You might use Database.executeMultiStatement(): this method takes a SQL string containing multiple statements separated by semi-colons:

```swift
migrator.registerMigration("createTables") { db in
    try db.executeMultiStatement(
        "CREATE TABLE persons (...);" +
        "CREATE TABLE books (...);" +
        "...")
}
```

You might even store your migrations as bundle resources:

```swift
// Execute migration01.sql, migration02.sql, etc.
NSBundle.mainBundle()
    .pathsForResourcesOfType("sql", inDirectory: "databaseMigrations")
    .sort()
    .forEach { path in
        let migrationName = (path as NSString).lastPathComponent
        migrator.registerMigration(migrationName) { db in
            let sql = try String(contentsOfFile: path, encoding: NSUTF8StringEncoding)
            try db.executeMultiStatement(sql)
        }
    }
```

### Advanced Database Schema Changes

SQLite does not support many schema changes, and won't let you drop a table column with "ALTER TABLE ... DROP COLUMN ...", for example.

Yet any kind of schema change is still possible. The SQLite documentation explains in detail how to do so: https://www.sqlite.org/lang_altertable.html#otheralter. This technique requires the temporary disabling of foreign key checks, and is supported by a specific  method of DatabaseMigrator:

```swift
migrator.registerMigrationWithoutForeignKeyChecks("dropTableColumn") { db in
    try db.execute("...")
}
```

While your migration code runs with disabled foreign key checks, those are re-enabled and checked at the end of the migration, regardless of eventual errors.


## Database Changes Observation

The TransactionObserverType protocol lets you **observe database changes**:

```swift
public protocol TransactionObserverType : class {
    // Notifies a database change:
    // - event.kind (insert, update, or delete)
    // - event.tableName
    // - event.rowID
    func databaseDidChangeWithEvent(event: DatabaseEvent)<commit_msg>README.md<commit_after>GRDB.swift
==========

GRDB.swift is an [SQLite](https://www.sqlite.org) toolkit for Swift 2, from the author of [GRMustache](https://github.com/groue/GRMustache).

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

**November 4, 2015: GRDB.swift 0.27.0 is out** - [Release notes](CHANGELOG.md). Follow [@groue](http://twitter.com/groue) on Twitter for release announcements and usage tips.

Jump to:

- [Features](#features)
- [Requirements](#requirements)
- [Usage](#usage)
- [Benchmarks](#benchmarks)
- **[Installation](#installation)**
- **[Documentation](#documentation)**
- [Sample Code](#sample-code)


Features
--------

- **A low-level SQLite API** that leverages the Swift 2 standard library.
- **No smart query builder**. Your SQL skills are welcome here.
- **A Record 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**
- **Database changes observation hooks**


Requirements
------------

- iOS 7.0+ / OSX 10.9+
- Xcode 7


Usage
-----

**SQLite API**

```swift
import GRDB

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

try dbQueue.inDatabase { db in
    // Create tables
    try db.execute("CREATE TABLE wines (...)")
    
    // Insert
    let changes = try db.execute("INSERT INTO wines (color, name) VALUES (?, ?)",
        arguments: [Color.Red, "Pomerol"])
    let wineId = changes.insertedRowID
    print("Inserted wine id: \(wineId)")
    
    // Fetch rows
    for row in Row.fetch(db, "SELECT * FROM wines") {
        let name: String = row.value(named: "name")
        let color: Color = row.value(named: "color")
        print(name, color)
    }
    
    // Fetch values
    let redWineCount = Int.fetchOne(db,
        "SELECT COUNT(*) FROM wines WHERE color = ?",
        arguments: [Color.Red])!
}
```

**Using Records**

```swift
// Define Record subclass
class Wine : Record { ... }

try dbQueue.inDatabase { db in
    // Store
    let wine = Wine(color: .Red, name: "Pomerol")
    try wine.insert(db)
    print("Inserted wine id: \(wine.id)")
    
    // Fetch
    for wine in Wine.fetch(db, "SELECT * FROM wines") {
        print(wine.name, wine.color)
    }
}
```

**Users of [ccgus/fmdb](https://github.com/ccgus/fmdb)** will feel at ease with GRDB.swift. They may find GRDB to be easier when [fetching](#fetch-queries) data from the database. And they'll definitely be happy that [database errors](#error-handling) are handled in the Swift way.

**Users of [stephencelis/SQLite.swift](https://github.com/stephencelis/SQLite.swift)** may eventually find that a straightforward API around SQL is not a bad alternative.


Benchmarks
----------

GRDB.swift runs as fast as ccgus/fmdb, or faster.

For precise benchmarks, select the GRDBOSX scheme, run the tests in Release configuration, and check the results under the "Performance" tab in the Xcode Report Navigator.


Installation
------------

### iOS7

You can use GRDB.swift in a project targetting iOS7. See [GRDBDemoiOS7](DemoApps/GRDBDemoiOS7) for more information.


### CocoaPods

[CocoaPods](http://cocoapods.org/) is a dependency manager for Xcode projects.

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

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

pod 'GRDB.swift', '0.27.0'
```


### Carthage

[Carthage](https://github.com/Carthage/Carthage) is another dependency manager for Xcode projects.

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

```
github "groue/GRDB.swift" == 0.27.0
```


### Manually

1. Download a copy of GRDB.swift.
2. Embed the `GRDB.xcodeproj` project in your own project.
3. Add the `GRDBOSX` or `GRDBiOS` target in the **Target Dependencies** section of the **Build Phases** tab of your application target.
4. Add `GRDB.framework` to the **Embedded Binaries** section of the **General**  tab of your target.

See [GRDBDemoiOS](DemoApps/GRDBDemoiOS) for an example of such integration.


Documentation
=============

**Reference**

- [GRDB Reference](http://cocoadocs.org/docsets/GRDB.swift/0.27.0/index.html) on cocoadocs.org. Beware that it is incomplete: you may prefer reading the inline documentation right into the [source](https://github.com/groue/GRDB.swift/tree/master/GRDB).

**[SQLite API](#sqlite-api)**

- [SQLite Database](#sqlite-database)
- [Creating Tables](#creating-tables)
- [Inserting Rows](#inserting-rows)
- [Fetch Queries](#fetch-queries)
    - [Row Queries](#row-queries)
    - [Value Queries](#value-queries)
- [Values](#values)
    - [NSData](#nsdata-and-memory-savings)
    - [NSDate and NSDateComponents](#nsdate-and-nsdatecomponents)
    - [NSCoding](#nscoding)
    - [Swift enums](#swift-enums)
    - [Custom Value Types](#custom-value-types)
- [Prepared Statements](#prepared-statements)
- [Error Handling](#error-handling)
- [Transactions](#transactions)
- [Concurrency](#concurrency)
- [Raw SQLite Pointers](#raw-sqlite-pointers)

**[Application Tools](#application-tools)**

- [Migrations](#migrations): Transform your database as your application evolves.
- [Database Changes Observation](#database-changes-observation): A robust way to perform post-commit and post-rollback actions.
- [RowConvertible Protocol](#rowconvertible-protocol): Turn database rows into handy types, without sacrificing performance.
- [Records](#records): CRUD operations and changes tracking.


SQLite API
==========

## SQLite Database

You access SQLite databases through **thread-safe database queues** (inspired by [ccgus/fmdb](https://github.com/ccgus/fmdb)):

```swift
import GRDB

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

The database file is created if it does not already exist.

The connection is closed when the database queue gets deallocated.


**Configure** databases:

```swift
var config = Configuration()
config.foreignKeysEnabled = true // Default true
config.readonly = false          // Default false
config.trace = LogSQL            // The built-in LogSQL function logs all SQL statements with NSLog.

let dbQueue = try DatabaseQueue(
    path: "/path/to/database.sqlite",
    configuration: config)
```

See [Concurrency](#concurrency) for more details on database configuration.


The `inDatabase` and `inTransaction` methods perform your **database statements** in a dedicated, serial, queue:

```swift
// Extract values from the database:
let rows = dbQueue.inDatabase { db in
    Row.fetchAll(db, "SELECT ...")
}

// Execute database statements:
dbQueue.inDatabase { db in
    for person in Person.fetch(db, "SELECT...") {
        ...
    }
}

// Wrap database statements in a transaction:
try dbQueue.inTransaction { db in
    try db.execute("INSERT ...")
    try db.execute("DELETE FROM ...")
    return .Commit
}
```

See [Transactions](#transactions) for more information about GRDB transaction handling.


## Creating Tables

**To create a database table**, execute a [CREATE TABLE](https://www.sqlite.org/lang_createtable.html) statement:

```swift
try dbQueue.inDatabase { db in
    try db.execute(
        "CREATE TABLE persons (" +
            "id INTEGER PRIMARY KEY, " +
            "name TEXT NOT NULL, " +
            "email TEXT" +
        ")")
}
```

Database schemas usually evolve over time: you should probably create your tables with [migrations](#migrations).


## Inserting Rows

**To insert a database row**, execute an [INSERT](https://www.sqlite.org/lang_insert.html) statement:

```swift
try dbQueue.inDatabase { db in
    try db.execute("INSERT ...", arguments: ...)
}
```

Arguments are optional arrays or dictionaries that fill the positional `?` and colon-prefixed keys like `:name` in the query:

```swift
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": 39])
```

See [Values](#values) for more information on supported arguments types (Bool, Int, String, NSDate, Swift enums, etc.).

You extract the inserted Row ID from the result of the `execute` method:

```swift
let insertedRowID = try db.execute(
    "INSERT INTO persons (name, age) VALUES (?, ?)",
    arguments: ["Arthur", 36]).insertedRowID
```

Don't miss the [Record](#records) class, which helps inserting rows in the database:

```swift
let person = Person(name: "Arthur", age: 36)
try person.insert(db)
print("Inserted person id: \(person.id)")
```


## Fetch Queries

You can fetch **Rows**, **Values**, and **Records**:

```swift
dbQueue.inDatabase { db in
    Row.fetch(db, "SELECT ...", ...)       // DatabaseSequence<Row>
    Row.fetchAll(db, "SELECT ...", ...)    // [Row]
    Row.fetchOne(db, "SELECT ...", ...)    // Row?
    
    String.fetch(db, "SELECT ...", ...)    // DatabaseSequence<String>
    String.fetchAll(db, "SELECT ...", ...) // [String]
    String.fetchOne(db, "SELECT ...", ...) // String?
    
    Person.fetch(db, "SELECT ...", ...)    // DatabaseSequence<Person>
    Person.fetchAll(db, "SELECT ...", ...) // [Person]
    Person.fetchOne(db, "SELECT ...", ...) // Person?
    
    Person.fetch(db, keys: ...)            // DatabaseSequence<Person>
    Person.fetchAll(db, keys: ...)         // [Person]
    Person.fetchOne(db, key: ...)          // Person?
}
```

Most methods take a custom SQL query as an argument. If SQL is not your cup of tea, then maybe you are looking for a query builder. [stephencelis/SQLite.swift](https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#selecting-rows) is a pretty popular one.

- [Row Queries](#row-queries)
- [Value Queries](#value-queries)
- [RowConvertible Protocol](#rowconvertible-protocol) and [Records](#records)


### Row Queries

- [Fetching Rows](#fetching-rows)
- [Column Values](#column-values)
- [Rows as Dictionaries](#rows-as-dictionaries)
- [Convenience Rows](#convenience-rows)


#### Fetching Rows

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

```swift
dbQueue.inDatabase { db in
    Row.fetch(db, "SELECT ...", arguments: ...)     // DatabaseSequence<Row>
    Row.fetchAll(db, "SELECT ...", arguments: ...)  // [Row]
    Row.fetchOne(db, "SELECT ...", arguments: ...)  // Row?
}
```

Arguments are optional arrays or dictionaries that fill the positional `?` and colon-prefixed keys like `:name` in the query:

```swift
let rows = Row.fetch(db,
    "SELECT * FROM persons WHERE name = ?",
    arguments: ["Arthur"])

let rows = Row.fetch(db,
    "SELECT * FROM persons WHERE name = :name",
    arguments: ["name": "Arthur"])
```

See [Values](#values) for more information on supported arguments types (Bool, Int, String, NSDate, Swift enums, etc.).

Both `fetch` and `fetchAll` let you iterate the full list of fetched rows. The differences are:

- The array returned by `fetchAll` can take a lot of memory. Yet it can be iterated on any thread.
- The sequence returned by `fetch` only goes to the database as you iterate it, and is thus more memory efficient. The price for this efficiency is that the sequence must be iterated in the database queue (you'll get a fatal error if you do otherwise).
- The sequence returned by `fetch` will return a different set of results if the database has been modified between two sequence iterations.

Row sequences also grant the fastest access to the database. This performance advantage comes with extra precautions:

> :point_up: **Don't turn a row sequence into an array** with `Array(rowSequence)` or `rowSequence.filter { ... }`: you would not get the distinct rows you expect. To get an array, use `Row.fetchAll(...)`.
> 
> :point_up: **Make sure you copy a row** whenever you extract it from a sequence for later use: `row.copy()`. This does not apply to row arrays, which already contain independent copies of the database rows.


#### Column Values

**Read column values** by index or column name:

```swift
let name: String = row.value(atIndex: 0)    // 0 is the leftmost column
let name: String = row.value(named: "name")
```

Make sure to ask for an optional when the value may be NULL:

```swift
let name: String? = row.value(named: "name")
```

The `value` function returns the type you ask for:

```swift
let bookCount: Int     = row.value(named: "bookCount")
let bookCount64: Int64 = row.value(named: "bookCount")
let hasBooks: Bool     = row.value(named: "bookCount")  // false when 0
```

Did I say the type you ask for? See [Values](#values) for more information on supported value types:

```swift
let dateString: String = row.value(named: "date") // "2015-09-11 18:14:15.123"
let date: NSDate       = row.value(named: "date") // NSDate
self.date = row.value(named: "date") // Depends on the type of the property.
```

You can also use the `as` type casting operator:

```swift
row.value(...) as Int
row.value(...) as Int?
row.value(...) as Int!
```

> :warning: **Warning**: avoid the `as!` and `as?` operators (see [rdar://21676393](http://openradar.appspot.com/radar?id=4951414862249984)):
> 
> ```swift
> row.value(...) as! Int   // NO NO NO DON'T DO THAT!
> row.value(...) as? Int   // NO NO NO DON'T DO THAT!
> ```

Generally speaking, you can extract the type you need, *provided it can be converted from the underlying SQLite value*:

- **Successful conversions include:**
    
    - Numeric (integer and real) SQLite values to Swift Int, Int32, Int64, Double and Bool (zero is the only false boolean).
    - Text SQLite values to Swift String.
    - Blob SQLite values to NSData.
    
    See [Values](#values) for more information on supported types (NSDate, Swift enums, etc.).

- **Invalid conversions return nil.**

    ```swift
    row.value(named: "foo") as String  // "bar"
    row.value(named: "foo") as NSDate? // nil: can't convert "bar" to NSDate
    ```

- **GRDB crashes when you try to convert NULL to a non-optional value.**
    
    This behavior is notably different from SQLite C API, or from ccgus/fmdb, that both turn NULL to 0 when extracting an integer, for example.
    
- **The convenience conversions of SQLite, such as Blob to String or String to Integer are not guaranteed to apply.** You must not rely on them.


#### Rows as Dictionaries

You may prefer thinking of rows as dictionaries of `DatabaseValue`, an intermediate type between SQLite and your values:

```swift
// Test if the column `date` is present:
if let databaseValue = row["date"] {
    // Pick the type you need:
    let dateString: String = databaseValue.value() // "2015-09-11 18:14:15.123"
    let date: NSDate = databaseValue.value()       // NSDate
    self.date = databaseValue.value() // Depends on the type of the property.
}
```

Iterate all the tuples (columnName, databaseValue) in a row, from left to right:

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

Rows are not real dictionaries, though. They may contain duplicate keys:

```swift
let row = Row.fetchOne(db, "SELECT 1 AS foo, 2 AS foo")!
row.columnNames     // ["foo", "foo"]
row.databaseValues  // [1, 2]
row["foo"]          // 1 (the value for the leftmost column "foo")
for (columnName, databaseValue) in row { ... } // ("foo", 1), ("foo", 2)
```


Still, as a convenience, row can be converted to and from **NSDictionary** (in case of duplicate colum names, the leftmost value is returned):

```swift
row.toDictionary()  // NSDictionary
```


#### Convenience Rows

Rows is a fundamental type in GRDB, used by many other APIs.

From time to time, you'll want to build a custom one from scratch. Use the dictionary initializer:

```swift
Row(dictionary: ["name": "foo", "date": nil])
```

See [Values](#values) for more information on supported types.


### Value Queries

Instead of rows, you can directly fetch **values**. Like rows, fetch them as **sequences**, **arrays**, or **single** values. Values are extracted from the leftmost column of the SQL queries:

```swift
dbQueue.inDatabase { db in
    Int.fetch(db, "SELECT ...", arguments: ...)    // DatabaseSequence<Int>
    Int.fetchAll(db, "SELECT ...", arguments: ...) // [Int]
    Int.fetchOne(db, "SELECT ...", arguments: ...) // Int?

    // When database may contain NULL:
    Optional<Int>.fetch(db, "SELECT ...", arguments: ...)    // DatabaseSequence<Int?>
    Optional<Int>.fetchAll(db, "SELECT ...", arguments: ...) // [Int?]
}
```

For example:

```swift
dbQueue.inDatabase { db in
    // The number of persons with an email ending in <EMAIL>:
    let count = Int.fetchOne(db,
        "SELECT COUNT(*) FROM persons WHERE email LIKE ?",
        arguments: ["<EMAIL>"])!
    
    // The emails of people who own at least two pets:
    let emails = String.fetch(db,
        "SELECT persons.email " +
        "FROM persons " +
        "JOIN pets ON pets.masterId = persons.id " +
        "GROUP BY persons.id " +
        "HAVING COUNT(pets.id) >= 2")
}
```

Both `fetch` and `fetchAll` let you iterate the full list of fetched values. The differences are:

- The array returned by `fetchAll` can take a lot of memory. Yet it can be iterated on any thread.
- The sequence returned by `fetch` only goes to the database as you iterate it, and is thus more memory efficient. The price for this efficiency is that the sequence must be iterated in the database queue (you'll get a fatal error if you do otherwise).
- The sequence returned by `fetch` will return a different set of results if the database has been modified between two sequence iterations.

`fetchOne` returns an optional value which is nil in two cases: either the SELECT statement yielded no row, or one row with a NULL value.


## Values

GRDB ships with built-in support for the following value types:

- **Swift**:
    
    Bool, Double, Int, Int32, Int64, String, [Swift enums](#swift-enums).
    
- **Foundation**:
    
    [NSCoding](#nscoding), [NSData](#nsdata-and-memory-savings), [NSDate](#nsdate-and-nsdatecomponents), [NSDateComponents](#nsdate-and-nsdatecomponents), NSNull, NSNumber, NSString, NSURL.
    
- **CoreGraphics**:
    
    CGFloat.

All those types can be [stored](#inserting-rows):

```swift
let url: NSURL = ...
let verified: Bool = ...
try db.execute(
    "INSERT INTO urls (url, verified) VALUES (?, ?)",
    arguments: [url, verified])
```

They can be [extracted from rows](#column-values):

```swift
for row in Row.fetch(db, "SELECT * FROM urls") {
    let url: NSURL = row.value(named: "url")
    let verified: Bool = row.value(named: "verified")
}
```

They can be [directly fetched](#value-queries) from the database:

```swift
let urls = NSURL.fetchAll(db, "SELECT url FROM urls")  // [NSURL]
```

Your custom value types are supported as well, through the [DatabaseValueConvertible](#custom-value-types) protocol.


### NSData (and Memory Savings)

**NSData** suits the BLOB SQLite columns. It can be stored and fetched from the database just like other value types:

```swift
let row = Row.fetchOne(db, "SELECT data, ...")!
let data: NSData = row.value(named: "data")

NSData.fetch(db, "SELECT ...")       // DatabaseSequence<NSData>
NSData.fetchAll(db, "SELECT ...")    // [NSData]
NSData.fetchOne(db, "SELECT ...")    // NSData?
```

Yet, when extracting NSData from a row, **you have the opportunity to save memory by not copying the data fetched by SQLite**, using the `dataNoCopy()` method:

```swift
for row in Row.fetch(db, "SELECT data, ...") {
    let data = row.dataNoCopy(named: "data")     // NSData?

    // When the column `data` may not be there:
    if row.hasColumn("data") {
        let data = row.dataNoCopy(named: "data") // NSData?
    }
}
```

> :point_up: **Note**: The non-copied data does not live longer that the iteration step: make sure that you do not use it past this point.

Compare with the **anti-patterns** below:

```swift
for row in Row.fetch(db, "SELECT data, ...") {
    // Data is copied, row after row:
    let data: NSData = row.value(named: "data")
    
    // Data is copied, row after row:
    if let databaseValue = row["data"] {
        let data: NSData = databaseValue.value()
    }
}

// All rows have been copied in memory when the loop begins:
for row in Row.fetchAll(db, "SELECT data, ...") {
    // Too late to do the right thing:
    let data = row.dataNoCopy(named: "data")
}
```


### NSDate and NSDateComponents

[**NSDate**](#nsdate) and [**NSDateComponents**](#nsdatecomponents) can be stored and fetched from the database.

Here is the support provided by GRDB.swift for the various [date formats](https://www.sqlite.org/lang_datefunc.html) 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-DD**T**HH:MM         |     Read ¹   |       Read       |
| YYYY-MM-DD**T**HH:MM:SS      |     Read ¹   |       Read       |
| YYYY-MM-DD**T**HH: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

**GRDB stores NSDate using the format "yyyy-MM-dd HH:mm:ss.SSS" in the UTC time zone.**

> :point_up: **Note**: This format is lexically comparable with SQLite's CURRENT_TIMESTAMP, which means that your ORDER BY clauses will behave as expected.
>
> Yet, this format may not fit your needs. We provide below some sample code for [storing dates as timestamps](#custom-value-types). You can adapt it for your application.

Declare DATETIME columns in your tables:

```swift
try db.execute(
    "CREATE TABLE persons (" +
    "creationDate DATETIME, " +
    "...)")
```

Store NSDate into the database:

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

Extract NSDate from the database:

```swift
let row = Row.fetchOne(db, "SELECT creationDate, ...")!
let date: NSDate = row.value(named: "creationDate")

NSDate.fetch(db, "SELECT ...")       // DatabaseSequence<NSDate>
NSDate.fetchAll(db, "SELECT ...")    // [NSDate]
NSDate.fetchOne(db, "SELECT ...")    // NSDate?
```

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


#### NSDateComponents

NSDateComponents is indirectly supported, through the **DatabaseDateComponents** helper type.

DatabaseDateComponents reads date components from all [date formats supported by SQLite](https://www.sqlite.org/lang_datefunc.html), and stores them in the format of your choice, from HH:MM to YYYY-MM-DD HH:MM:SS.SSS.

Declare DATETIME columns in your tables:

```swift
try db.execute(
    "CREATE TABLE persons (" +
    "birthDate DATETIME, " +
    "...)")
```

Store NSDateComponents into the database:

```swift
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:

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

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

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


### NSCoding

NSCoding is indirectly supported, through the **DatabaseCoder** helper type.

DatabaseCoder reads and writes objects that adopt NSCoding into Blob columns.

For example, store NSArray into the database:

```swift
let ints = [1, 2, 3]
try db.execute(
    "INSERT INTO ... (ints, ...) VALUES (?, ...)",
    arguments: [DatabaseCoder(ints), ...])
```

Extract NSArray from the database:

```swift
let row = Row.fetchOne(db, "SELECT ints ...")!
let coder = row.value(named: "ints") as DatabaseCoder // DatabaseCoder
let array = coder.object as! NSArray                   // NSArray
let ints = array.map { $0 as! Int }                    // [Int]

DatabaseCoder.fetch(db, "SELECT ...")    // DatabaseSequence<DatabaseCoder>
DatabaseCoder.fetchAll(db, "SELECT ...") // [DatabaseCoder]
DatabaseCoder.fetchOne(db, "SELECT ...") // DatabaseCoder?
```

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


### Swift Enums

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

Given those two enums:

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

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

Simply add those two lines:

```swift
extension Color : DatabaseIntRepresentable { } // DatabaseInt32Representable for Int32, DatabaseInt64Representable for Int64
extension Grape : DatabaseStringRepresentable { }
```

And both types gain database powers:

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

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

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

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


### Custom Value Types

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

```swift
public protocol DatabaseValueConvertible {
    /// Returns a value that can be stored in the database.
    var databaseValue: DatabaseValue { get }
    
    /// Returns an instance initialized from databaseValue, if possible.
    static func fromDatabaseValue(databaseValue: DatabaseValue) -> Self?
}
```

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

The `databaseValue` property returns [DatabaseValue](GRDB/Core/DatabaseValue.swift), a type that wraps the five types supported by SQLite: NULL, Int64, Double, String and NSData.

The `fromDatabaseValue()` factory method returns an instance of your custom type, if the databaseValue contains a suitable value.

As an example, let's write an alternative to the built-in [NSDate](#nsdate-and-nsdatecomponents) behavior, and store dates as timestamps. Our sample DatabaseTimestamp type applies all the best practices for a great GRDB.swift integration:

```swift
struct DatabaseTimestamp: DatabaseValueConvertible {
    
    // NSDate conversion
    //
    // Value types should consistently use the Swift nil to represent the
    // database NULL: the date property is a non-optional NSDate.
    let date: NSDate
    
    // As a convenience, the NSDate initializer accepts an optional NSDate, and
    // is failable: 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 DatabaseValue(double: date.timeIntervalSince1970)
    }
    
    /// Returns an instance initialized from *databaseValue*, if possible.
    static func fromDatabaseValue(databaseValue: DatabaseValue) -> DatabaseTimestamp? {
        // Double itself adopts DatabaseValueConvertible:
        guard let timeInterval = Double.fromDatabaseValue(databaseValue) else {
            // No Double, no NSDate!
            return nil
        }
        return DatabaseTimestamp(NSDate(timeIntervalSince1970: timeInterval))
    }
}
```

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

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

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

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

See [Column Values](#column-values) and [Value Queries](#value-queries) for more information.


## Prepared Statements

**Prepared Statements** let you prepare an SQL query and execute it later, several times if you need, with different arguments.

There are two kinds to prepared statements: **select statements**, and **update statements**:

```swift
try dbQueue.inTransaction { db in
    let updateSQL = "INSERT INTO persons (name, age) VALUES (:name, :age)"
    let updateStatement = db.updateStatement(updateSQL)
    
    let selectSQL = "SELECT * FROM persons WHERE name = ?"
    let selectStatement = db.selectStatement(selectSQL)
}
```

The `?` and colon-prefixed keys like `:name` in the SQL query are the statement arguments. You set them with arrays or dictionaries (arguments are actually of type StatementArguments, which happens to adopt the ArrayLiteralConvertible and DictionaryLiteralConvertible protocols).

```swift
// INSERT INTO persons (name, age) VALUES (:name, :age)
updateStatement.arguments = ["name": "Arthur", "age": 41]

// SELECT * FROM persons WHERE name = ?
selectStatement.arguments = ["Arthur"]
```

After arguments are set, you can execute the prepared statement:

```swift
let changes = try updateStatement.execute()
changes.changedRowCount // The number of rows changed by the statement.
changes.insertedRowID   // For INSERT statements, the inserted Row ID.

for row in Row.fetch(selectStatement) { ... }
for person in Person.fetch(selectStatement) { ... }
```

It is possible to set the arguments at the moment of the statement execution:

```swift
// INSERT INTO persons (name, age) VALUES (:name, :age)
try statement.execute(arguments: ["name": "Arthur", "age": 41])

// SELECT * FROM persons WHERE name = ?
let person = Person.fetchOne(selectStatement, arguments: ["Arthur"])
```

Select statements can be used wherever a raw SQL query would fit:

```swift
Row.fetch(statement, arguments: ...)       // DatabaseSequence<Row>
Row.fetchAll(statement, arguments: ...)    // [Row]
Row.fetchOne(statement, arguments: ...)    // Row?

String.fetch(statement, arguments: ...)    // DatabaseSequence<String>
String.fetchAll(statement, arguments: ...) // [String]
String.fetchOne(statement, arguments: ...) // String?

Person.fetch(statement, arguments: ...)    // DatabaseSequence<Person>
Person.fetchAll(statement, arguments: ...) // [Person]
Person.fetchOne(statement, arguments: ...) // Person?
```

See [Row Queries](#row-queries), [Value Queries](#value-queries), [RowConvertible](#rowconvertible-protocol), and [Records](#fetching-records) for more information.


## 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 *write* to the database throw.
- All other methods crash without notice (but with a detailed error message).

```swift
// fatal error:
// SQLite error 1 with statement `SELECT foo FROM bar`: no such table: bar
Row.fetchAll(db, "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](https://www.sqlite.org/rescode.html).



## Transactions

The `DatabaseQueue.inTransaction()` method opens a SQLite transaction:

```swift
try dbQueue.inTransaction { db in
    let wine = Wine(color: .Red, name: "Pomerol")
    try wine.insert(db)
    return .Commit
}
```

A ROLLBACK statement is issued if an error is thrown within the transaction block.

Otherwise, transactions are guaranteed to succeed, *provided there is a single DatabaseQueue connected to the database file*. See [Concurrency](#concurrency) for more information about concurrent database access.

If you want to insert a transaction between other database statements, and group those in a single block of code protected by the the database queue, you can use the Database.inTransaction() function:

```swift
try dbQueue.inDatabase { db in
    ...
    try db.inTransaction {
        ...
        return .Commit
    }
    ...
}
```


## Concurrency

**When your application has a single DatabaseQueue connected to the database file, it has no concurrency issue.** That is because all your database statements are executed in a single serial dispatch queue that is connected alone to the database.

**Things turn more complex as soon as there are several connections to a database file.**

SQLite concurrency management is fragmented. Documents of interest include:

- General discussion about isolation in SQLite: https://www.sqlite.org/isolation.html
- Types of locks and transactions: https://www.sqlite.org/lang_transaction.html
- WAL journal mode: https://www.sqlite.org/wal.html
- Busy handlers: https://www.sqlite.org/c3ref/busy_handler.html

By default, GRDB opens database in the **default journal mode**, uses **IMMEDIATE transactions**, and registers **no busy handler** of any kind.

See [Configuration](GRDB/Core/Configuration.swift) type and [DatabaseQueue.inTransaction()](GRDB/Core/DatabaseQueue.swift) method for more precise handling of transactions and eventual SQLITE_BUSY errors.


## Raw SQLite Pointers

Not all SQLite APIs are exposed in GRDB.

The `Database.sqliteConnection` and `Statement.sqliteStatement` properties provide the raw pointers that are suitable for [SQLite C API](https://www.sqlite.org/c3ref/funclist.html):

```swift
dbQueue.inDatabase { db in
    let sqliteConnection = db.sqliteConnection
    sqlite3_db_config(sqliteConnection, ...)
    
    let statement = db.selectStatement("SELECT ...")
    let sqliteStatement = statement.sqliteStatement
    sqlite3_step(sqliteStatement)
}
```

> :point_up: **Notes**
>
> - Those pointers are owned by GRDB: don't close connections or finalize statements created by GRDB.
> - SQLite connections are opened in the [Multi-thread mode](https://www.sqlite.org/threadsafe.html), which means that **they are not thread-safe**. Make sure you touch raw databases and statements inside the database queues.

Before jumping in the low-level wagon, here is a reminder of SQLite APIs supported by GRDB:

- Connections & statements, obviously.
- Errors (pervasive)
    - [sqlite3_errmsg](https://www.sqlite.org/c3ref/errcode.html)
- Inserted Row IDs (as the result of Database.execute()).
    - [sqlite3_last_insert_rowid](https://www.sqlite.org/c3ref/last_insert_rowid.html)
- Changes count (as the result of Database.execute()).
    - [sqlite3_changes](https://www.sqlite.org/c3ref/changes.html)
- Busy mode (see [Concurrency](#concurrency)).
    - [sqlite3_busy_handler](https://www.sqlite.org/c3ref/busy_handler.html)
    - [sqlite3_busy_timeout](https://www.sqlite.org/c3ref/busy_timeout.html)
- Update, commit and rollback hooks (see [Database Changes Observation](#database-changes-observation)):
    - [sqlite3_update_hook](https://www.sqlite.org/c3ref/update_hook.html)
    - [sqlite3_commit_hook](https://www.sqlite.org/c3ref/commit_hook.html)
    - [sqlite3_rollback_hook](https://www.sqlite.org/c3ref/commit_hook.html)


Application Tools
=================

On top of the SQLite API described above, GRDB provides a toolkit for applications. While none of those are mandatory, all of them help dealing with the database:

- [Migrations](#migrations): Transform your database as your application evolves.
- [Database Changes Observation](#database-changes-observation): A robust way to perform post-commit and post-rollback actions.
- [RowConvertible Protocol](#rowconvertible-protocol): Turn database rows into handy types, without sacrificing performance.
- [Records](#records): CRUD operations and changes tracking.


## 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 migrations are run.

```swift
var migrator = DatabaseMigrator()

// v1.0 database
migrator.registerMigration("createTables") { db in
    try db.execute("CREATE TABLE persons (...)")
    try db.execute("CREATE TABLE books (...)")
}

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

migrator.registerMigration("AddYearToBooks") { db in
    try db.execute("ALTER TABLE books ADD COLUMN year INT")
}

// (Insert migrations for future versions here)

try migrator.migrate(dbQueue)
```

**Each migration runs in a separate transaction.** Should one throw an error, its transaction is rollbacked, subsequent migrations do not run, and the error is eventually thrown by `migrator.migrate(dbQueue)`.

**The memory of applied migrations is stored in the database itself** (in a reserved table). When you are tuning your migrations, you may need to execute one several times. All you need then is to feed your application with a database file from a previous state.

You might use Database.executeMultiStatement(): this method takes a SQL string containing multiple statements separated by semi-colons:

```swift
migrator.registerMigration("createTables") { db in
    try db.executeMultiStatement(
        "CREATE TABLE persons (...);" +
        "CREATE TABLE books (...);" +
        "...")
}
```

You might even store your migrations as bundle resources:

```swift
// Execute migration01.sql, migration02.sql, etc.
NSBundle.mainBundle()
    .pathsForResourcesOfType("sql", inDirectory: "databaseMigrations")
    .sort()
    .forEach { path in
        let migrationName = (path as NSString).lastPathComponent
        migrator.registerMigration(migrationName) { db in
            let sql = try String(contentsOfFile: path, encoding: NSUTF8StringEncoding)
            try db.executeMultiStatement(sql)
        }
    }
```

### Advanced Database Schema Changes

SQLite does not support many schema changes, and won't let you drop a table column with "ALTER TABLE ... DROP COLUMN ...", for example.

Yet any kind of schema change is still possible. The SQLite documentation explains in detail how to do so: https://www.sqlite.org/lang_altertable.html#otheralter. This technique requires the temporary disabling of foreign key checks, and is supported by a specific  method of DatabaseMigrator:

```swift
migrator.registerMigrationWithoutForeignKeyChecks("AddNotNullCheckOnName") { db in
    try db.executeMultiStatement(
        "CREATE TABLE new_persons (id INTEGER PRIMARY KEY, name TEXT NOT NULL);" +
        "INSERT INTO new_persons SELECT id, name FROM persons;" +
        "DROP TABLE persons;" +
        "ALTER TABLE new_persons RENAME TO persons;")
}
```

While your migration code runs with disabled foreign key checks, those are re-enabled and checked at the end of the migration, regardless of eventual errors.


## Database Changes Observation

The TransactionObserverType protocol lets you **observe database changes**:

```swift
public protocol TransactionObserverType : class {
    // Notifies a database change:
    // - event.kind (insert, update, or delete)
    // - event.tableName
    // - event.rowID
    func databaseDidChangeWithEvent(event: DatabaseEvent)
{'fraction_non_alphanumeric': 0.1275835948490423, 'fraction_numerical': 0.005049958518197886, 'mean_word_length': 3.4669423706966, 'pattern_counts': {'":': 18, '<': 42, '<?xml version=': 0, '>': 82, 'https://': 60, 'lorem ipsum': 0, 'www.': 42, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4241996', 'n_tokens_mistral': 25262, 'n_tokens_neox': 23760, 'n_words': 8779}