GRDB 3

A toolkit for SQLite databases, with a focus on application development

Latest release: March 9, 2019 • version 3.7.0 • CHANGELOG • Migrating From GRDB 2 to GRDB 3

Requirements: iOS 8.0+ / macOS 10.9+ / watchOS 2.0+ • Swift 4.1+ / Xcode 9.3+

Contact:

• Release announcements and usage tips: follow @groue on Twitter.
• Report bugs in a Github issue. Make sure you check the existing issues first.
• A question? Looking for advice? Do you wonder how to contribute? Fancy a chat? Go to the GRDB forums, or open a Github issue.

What is this?

GRDB provides raw access to SQL and advanced SQLite features, because one sometimes enjoys a sharp tool. It has robust concurrency primitives, so that multi-threaded applications can efficiently use their databases. It grants your application models with persistence and fetching methods, so that you don't have to deal with SQL and raw database rows when you don't want to.

Compared to SQLite.swift or FMDB, GRDB can spare you a lot of glue code. Compared to Core Data or Realm, it can simplify your multi-threaded applications.

It comes with up-to-date documentation, general guides, and it is fast.

See Why Adopt GRDB? if you are looking for your favorite database library.

Features • Usage • Installation • Documentation • Demo Application • FAQ

Features

GRDB ships with:

• Access to raw SQL and SQLite
• Records: Fetching and persistence methods for your custom structs and class hierarchies.
• Query Interface: A swift way to avoid the SQL language.
• Associations: Relations and joins between record types.
• WAL Mode Support: Extra performance for multi-threaded applications.
• Migrations: Transform your database as your application evolves.
• Database Observation: Observe database changes and transactions.
• Full-Text Search
• Encryption
• Support for Custom SQLite Builds

Companion libraries that enhance and extend GRDB:

• RxGRDB: track database changes in a reactive way, with RxSwift.
• GRDBObjc: FMDB-compatible bindings to GRDB.

Usage

import GRDB

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

// Enhanced multithreading based on SQLite's WAL mode
let dbPool = try DatabasePool(path: "/path/to/database.sqlite")

try dbQueue.write { db in
    try db.execute("""
        CREATE TABLE place (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          favorite BOOLEAN NOT NULL DEFAULT 0,
          latitude DOUBLE NOT NULL,
          longitude DOUBLE NOT NULL)
        """)

    try db.execute("""
        INSERT INTO place (title, favorite, latitude, longitude)
        VALUES (?, ?, ?, ?)
        """, arguments: ["Paris", true, 48.85341, 2.3488])
    
    let parisId = db.lastInsertedRowID
}

try dbQueue.read { db in
    // Fetch database rows
    let rows = try Row.fetchCursor(db, "SELECT * FROM place")
    while let row = try rows.next() {
        let title: String = row["title"]
        let isFavorite: Bool = row["favorite"]
        let coordinate = CLLocationCoordinate2D(
            latitude: row["latitude"],
            longitude: row["longitude"])
    }
    
    // Fetch values
    let placeCount = try Int.fetchOne(db, "SELECT COUNT(*) FROM place")! // Int
    let placeTitles = try String.fetchAll(db, "SELECT title FROM place") // [String]
}

let placeCount = try dbQueue.read { db in
    try Int.fetchOne(db, "SELECT COUNT(*) FROM place")!
}

struct Place {
    var id: Int64?
    var title: String
    var isFavorite: Bool
    var coordinate: CLLocationCoordinate2D
}

// snip: turn Place into a "record" by adopting the protocols that
// provide fetching and persistence methods.

try dbQueue.write { db in
    // Create database table
    try db.create(table: "place") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("title", .text).notNull()
        t.column("favorite", .boolean).notNull().defaults(to: false)
        t.column("longitude", .double).notNull()
        t.column("latitude", .double).notNull()
    }
    
    var berlin = Place(
        id: nil,
        title: "Berlin",
        isFavorite: false,
        coordinate: CLLocationCoordinate2D(latitude: 52.52437, longitude: 13.41053))
    
    try berlin.insert(db)
    berlin.id // some value
    
    berlin.isFavorite = true
    try berlin.update(db)
}

try dbQueue.write { db in
    // Place?
    let paris = try Place.fetchOne(db, key: 1)
    
    // Place?
    let berlin = try Place.filter(Column("title") == "Berlin").fetchOne(db)
    
    // [Place]
    let favoritePlaces = try Place
        .filter(Column("favorite") == true)
        .order(Column("title"))
        .fetchAll(db)
    
    // Int
    let favoriteCount = try Place.filter(Column("favorite")).fetchCount(db)
    
    // SQL is always welcome
    let places = try Place.fetchAll(db, "SELECT * FROM place")
}

let request = Place.order(Column("title"))
try ValueObservation
    .trackingAll(request)
    .start(in: dbQueue) { places: [Place] in
        print("Places have changed.")
    }

Documentation

GRDB runs on top of SQLite: you should get familiar with the SQLite FAQ. For general and detailed information, jump to the SQLite Documentation.

Reference

• GRDB Reference (generated by Jazzy)

Getting Started

• Installation
• Demo Application
• Database Connections: Connect to SQLite databases

SQLite and SQL

• SQLite API: The low-level SQLite API • executing updates • fetch queries

Records and the Query Interface

• Records: Fetching and persistence methods for your custom structs and class hierarchies.
• Query Interface: A swift way to generate SQL • table creation • requests

Application Tools

• Migrations: Transform your database as your application evolves.
• Full-Text Search: Perform efficient and customizable full-text searches.
• Joined Queries Support: Consume complex joined queries.
• Database Changes Observation: Observe database changes and transactions.
• Encryption: Encrypt your database with SQLCipher.
• Backup: Dump the content of a database to another.

Good to Know

• Avoiding SQL Injection
• Error Handling
• Unicode
• Memory Management
• Data Protection
• Concurrency
• Performance

General Guides & Good Practices

• 💡 Good Practices for Designing Record Types
• 💡 Migrating From GRDB 2 to GRDB 3
• 💡 Issues tagged "best practices"
• ❓ Issues tagged "question"
• 📘 Why Adopt GRDB?
• 📘 How to build an iOS application with SQLite and GRDB.swift
• 📘 Four different ways to handle SQLite concurrency
• 📘 Unexpected SQLite with Swift

FAQ

Sample Code

Installation

The installation procedures below have GRDB use the version of SQLite that ships with the target operating system.

See Encryption for the installation procedure of GRDB with SQLCipher.

See Custom SQLite builds for the installation procedure of GRDB with a customized build of SQLite 3.25.2.

See Enabling FTS5 Support for the installation procedure of GRDB with support for the FTS5 full-text engine.

CocoaPods

CocoaPods is a dependency manager for Xcode projects. To use GRDB with CocoaPods (version 1.2 or higher), specify in your Podfile:

use_frameworks!
pod 'GRDB.swift'

Swift Package Manager

The Swift Package Manager automates the distribution of Swift code. To use GRDB with SPM, add a dependency to your Package.swift file:

let package = Package(
    dependencies: [
        .package(url: "https://github.com/groue/GRDB.swift.git", from: "3.7.0")
    ]
)

Note that Linux is not currently supported.

Carthage

Carthage can build GRDB frameworks, but it can also inexplicably fail. This installation method is thus unsupported.

If you decide to use Carthage despite this warning, and get any Carthage-related error, please open an issue in the Carthage repo, ask Stack Overflow, summon your local Xcode guru, or submit a pull request that has the make test_CarthageBuild command succeed 100% of the time (one time is not enough). See #262 for more information.

Manually

1. Download a copy of GRDB, or clone its repository and make sure you use the latest tagged version with the git checkout v3.7.0 command.

2. Embed the GRDB.xcodeproj project in your own project.

3. Add the GRDBOSX, GRDBiOS, or GRDBWatchOS target in the Target Dependencies section of the Build Phases tab of your application target (extension target for WatchOS).

4. Add the GRDB.framework from the targetted platform to the Embedded Binaries section of the General tab of your application target (extension target for WatchOS).

💡 Tip: see the Demo Application for an example of such integration.

Demo Application

The repository comes with a demo application that shows you:

• how to setup a database in an iOS app
• how to define a simple Codable Record
• how to track database changes with ValueObservation and FetchedRecordsController.

Database Connections

GRDB provides two classes for accessing SQLite databases: DatabaseQueue and DatabasePool:

import GRDB

// Pick one:
let dbQueue = try DatabaseQueue(path: "/path/to/database.sqlite")
let dbPool = try DatabasePool(path: "/path/to/database.sqlite")

The differences are:

• Database pools allow concurrent database accesses (this can improve the performance of multithreaded applications).
• Database pools open your SQLite database in the WAL mode (unless read-only).
• Database queues support in-memory databases.

If you are not sure, choose DatabaseQueue. You will always be able to switch to DatabasePool later.

• Database Queues
• Database Pools

Database Queues

Open a database queue with the path to a database file:

import GRDB

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

SQLite creates the database file if it does not already exist. The connection is closed when the database queue gets deallocated.

A database queue can be used from any thread. The write and read methods are synchronous, and block the current thread until your database statements are executed in a protected dispatch queue:

// Modify the database:
try dbQueue.write { db in
    try db.create(table: "place") { ... }
    try Place(...).insert(db)
}

// Read values:
try dbQueue.read { db in
    let places = try Place.fetchAll(db)
    let placeCount = try Place.fetchCount(db)
}

Database access methods can return values:

let placeCount = try dbQueue.read { db in
    try Place.fetchCount(db)
}

let newPlaceCount = try dbQueue.write { db -> Int in
    try Place(...).insert(db)
    return try Place.fetchCount(db)
}

A database queue serializes accesses to the database, which means that there is never more than one thread that uses the database.

• When you don't need to modify the database, prefer the read method. It prevents any modification to the database.

• The write method wraps your database statements in a transaction that commits if and only if no error occurs. On the first unhandled error, all changes are reverted, the whole transaction is rollbacked, and the error is rethrown.

  When precise transaction handling is required, see Transactions and Savepoints.

A database queue needs your application to follow rules in order to deliver its safety guarantees. Please refer to the Concurrency chapter.

💡 Tip: see the Demo Application for a sample code that sets up a database queue on iOS.

DatabaseQueue Configuration

var config = Configuration()
config.readonly = true
config.foreignKeysEnabled = true // Default is already true
config.trace = { print($0) }     // Prints all SQL statements
config.label = "MyDatabase"      // Useful when your app opens multiple databases

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

See Configuration for more details.

Database Pools

A database pool allows concurrent database accesses.

import GRDB
let dbPool = try DatabasePool(path: "/path/to/database.sqlite")

SQLite creates the database file if it does not already exist. The connection is closed when the database pool gets deallocated.

☝️ Note: unless read-only, a database pool opens your database in the SQLite "WAL mode". The WAL mode does not fit all situations. Please have a look at https://www.sqlite.org/wal.html.

A database pool can be used from any thread. The write and read methods are synchronous, and block the current thread until your database statements are executed in a protected dispatch queue:

// Modify the database:
try dbPool.write { db in
    try db.create(table: "place") { ... }
    try Place(...).insert(db)
}

// Read values:
try dbPool.read { db in
    let places = try Place.fetchAll(db)
    let placeCount = try Place.fetchCount(db)
}

Database access methods can return values:

let placeCount = try dbPool.read { db in
    try Place.fetchCount(db)
}

let newPlaceCount = try dbPool.write { db -> Int in
    try Place(...).insert(db)
    return try Place.fetchCount(db)
}

Database pools allow several threads to access the database at the same time:

• When you don't need to modify the database, prefer the read method, because several threads can perform reads in parallel.

  Reads are generally non-blocking, unless the maximum number of concurrent reads has been reached. In this case, a read has to wait for another read to complete. That maximum number can be configured.

• Reads are guaranteed an immutable view of the last committed state of the database, regardless of concurrent writes. This kind of isolation is called snapshot isolation.

• Unlike reads, writes are serialized. There is never more than a single thread that is writing into the database.

• The write method wraps your database statements in a transaction that commits if and only if no error occurs. On the first unhandled error, all changes are reverted, the whole transaction is rollbacked, and the error is rethrown.

  When precise transaction handling is required, see Transactions and Savepoints.

• Database pools can take snapshots of the database.

A database pool needs your application to follow rules in order to deliver its safety guarantees. See the Concurrency chapter for more details about database pools, how they differ from database queues, and advanced use cases.

💡 Tip: see the Demo Application for a sample code that sets up a database queue on iOS, and just replace DatabaseQueue with DatabasePool.

DatabasePool Configuration

var config = Configuration()
config.readonly = true
config.foreignKeysEnabled = true // Default is already true
config.trace = { print($0) }     // Prints all SQL statements
config.label = "MyDatabase"      // Useful when your app opens multiple databases
config.maximumReaderCount = 10   // The default is 5

let dbPool = try DatabasePool(
    path: "/path/to/database.sqlite",
    configuration: config)

See Configuration for more details.

Database pools are more memory-hungry than database queues. See Memory Management for more information.

SQLite API

In this section of the documentation, we will talk SQL. Jump to the query interface if SQL is not your cup of tea.

• Executing Updates
• Fetch Queries
  • Fetching Methods
  • Row Queries
  • Value Queries
• Values
  • Data
  • Date and DateComponents
  • NSNumber and NSDecimalNumber
  • Swift enums
  • Custom Value Types
• Transactions and Savepoints

Advanced topics:

• Prepared Statements
• Custom SQL Functions and Aggregates
• Database Schema Introspection
• Row Adapters
• Raw SQLite Pointers

Executing Updates

Once granted with a database connection, the execute method executes the SQL statements that do not return any database row, such as CREATE TABLE, INSERT, DELETE, ALTER, etc.

For example:

try dbQueue.write { db in
    try db.execute("""
        CREATE TABLE player (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            score INT)
        """)
    
    try db.execute(
        "INSERT INTO player (name, score) VALUES (?, ?)",
        arguments: ["Barbara", 1000])
    
    try db.execute(
        "UPDATE player SET score = :score WHERE id = :id",
        arguments: ["score": 1000, "id": 1])
    }
}

The ? and colon-prefixed keys like :score in the SQL query are the statements arguments. You pass arguments with arrays or dictionaries, as in the example above. See Values for more information on supported arguments types (Bool, Int, String, Date, Swift enums, etc.), and StatementArguments for a detailed documentation of SQLite arguments.

Never ever embed values directly in your SQL strings, and always use arguments instead. See Avoiding SQL Injection for more information:

// WRONG
let id = 123
let name = textField.text
try db.execute("UPDATE player SET name = '\(name)' WHERE id = \(id)")

// CORRECT
try db.execute(
    "UPDATE player SET name = :name WHERE id = :id",
    arguments: ["name": name, "id": id])

Join multiple statements with a semicolon:

try db.execute("""
    INSERT INTO player (name, score) VALUES (?, ?);
    INSERT INTO player (name, score) VALUES (?, ?)
    """, arguments: ["Arthur", 750, "Barbara", 1000])

When you want to make sure that a single statement is executed, use Prepared Statements.

After an INSERT statement, you can get the row ID of the inserted row:

try db.execute(
    "INSERT INTO player (name, score) VALUES (?, ?)",
    arguments: ["Arthur", 1000])
let playerId = db.lastInsertedRowID

Don't miss Records, that provide classic persistence methods:

let player = Player(name: "Arthur", score: 1000)
try player.insert(db)
let playerId = player.id

Fetch Queries

Database connections let you fetch database rows, plain values, and custom models aka "records".

Rows are the raw results of SQL queries:

try dbQueue.read { db in
    if let row = try Row.fetchOne(db, "SELECT * FROM wine WHERE id = ?", arguments: [1]) {
        let name: String = row["name"]
        let color: Color = row["color"]
        print(name, color)
    }
}

Values are the Bool, Int, String, Date, Swift enums, etc. stored in row columns:

try dbQueue.read { db in
    let urls = try URL.fetchCursor(db, "SELECT url FROM wine")
    while let url = try urls.next() {
        print(url)
    }
}

Records are your application objects that can initialize themselves from rows:

let wines = try dbQueue.read { db in
    try Wine.fetchAll(db, "SELECT * FROM wine")
}

• Fetching Methods and Cursors
• Row Queries
• Value Queries
• Records

Fetching Methods

Throughout GRDB, you can always fetch cursors, arrays, or single values of any fetchable type (database row, simple value, or custom record):

try Row.fetchCursor(...) // A Cursor of Row
try Row.fetchAll(...)    // [Row]
try Row.fetchOne(...)    // Row?

• fetchCursor returns a cursor over fetched values:

  let rows = try Row.fetchCursor(db, "SELECT ...") // A Cursor of Row

• fetchAll returns an array:

  let players = try Player.fetchAll(db, "SELECT ...") // [Player]

• fetchOne returns a single optional value, and consumes a single database row (if any).

  let count = try Int.fetchOne(db, "SELECT COUNT(*) ...") // Int?

Cursors

Whenever you consume several rows from the database, you can fetch an Array, or a Cursor.

The fetchAll() method returns a regular Swift array, that you iterate like all other arrays:

try dbQueue.read { db in
    // [Player]
    let players = try Player.fetchAll(db, "SELECT ...")
    for player in players {
        // use player
    }
}

Unlike arrays, cursors returned by fetchCursor() load their results step after step:

try dbQueue.read { db in
    // Cursor of Player
    let players = try Player.fetchCursor(db, "SELECT ...")
    while let player = try players.next() {
        // use player
    }
}

Both arrays and cursors can iterate over database results. How do you choose one or the other? Look at the differences:

• Cursors can not be used on any thread: you must consume a cursor on the dispatch queue it was created in. Particularly, don't extract a cursor out of a database access method:

  // Wrong
  let cursor = try dbQueue.read { db in
      try Player.fetchCursor(db, ...)
  }
  while let player = try cursor.next() { ... }

  Conversely, arrays may be consumed on any thread:

  // OK
  let array = try dbQueue.read { db in
      try Player.fetchAll(db, ...)
  }
  for player in array { ... }

• Cursors can be iterated only one time. Arrays can be iterated many times.

• Cursors iterate database results in a lazy fashion, and don't consume much memory. Arrays contain copies of database values, and may take a lot of memory when there are many fetched results.

• Cursors are granted with direct access to SQLite, unlike arrays that have to take the time to copy database values. If you look after extra performance, you may prefer cursors over arrays.

• Cursors adopt the Cursor protocol, which looks a lot like standard lazy sequences of Swift. As such, cursors come with many convenience methods: compactMap, contains, dropFirst, dropLast, drop(while:), enumerated, filter, first, flatMap, forEach, joined, joined(separator:), max, max(by:), min, min(by:), map, prefix, prefix(while:), reduce, reduce(into:), suffix:

  // Prints all Github links
  try URL
      .fetchCursor(db, "SELECT url FROM link")
      .filter { url in url.host == "github.com" }
      .forEach { url in print(url) }
  
  // An efficient cursor of coordinates:
  let locations = try Row.
      .fetchCursor(db, "SELECT latitude, longitude FROM place")
      .map { row in
          CLLocationCoordinate2D(latitude: row[0], longitude: row[1])
      }
  
  // Turn cursors into arrays or sets:
  let array = try Array(cursor)
  let set = try Set(cursor)

• Cursors are not Swift sequences. That's because Swift sequences can't handle iteration errors, when reading SQLite results may fail at any time. SQL functions may throw errors. On iOS, data protection may block access to the database file in the background. On macOS, your application users may mess with the file system.

• Cursors require a little care:

  • Don't modify the results during a cursor iteration:

    // Undefined behavior
    while let player = try players.next() {
        try db.execute("DELETE ...")
    }

  • Don't turn a cursor of Row into an array. You would not get the distinct rows you expect. To get a array of rows, use Row.fetchAll(...). Generally speaking, make sure you copy a row whenever you extract it from a cursor for later use: row.copy().

If you don't see, or don't care about the difference, use arrays. If you care about memory and performance, use cursors when appropriate.

Row Queries

• Fetching Rows
• Column Values
• DatabaseValue
• Rows as Dictionaries

Fetching Rows

Fetch cursors of rows, arrays, or single rows (see fetching methods):

try dbQueue.read { db in
    try Row.fetchCursor(db, "SELECT ...", arguments: ...) // A Cursor of Row
    try Row.fetchAll(db, "SELECT ...", arguments: ...)    // [Row]
    try Row.fetchOne(db, "SELECT ...", arguments: ...)    // Row?
    
    let rows = try Row.fetchCursor(db, "SELECT * FROM wine")
    while let row = try rows.next() {
        let name: String = row["name"]
        let color: Color = row["color"]
        print(name, color)
    }
}

let rows = try dbQueue.read { db in
    try Row.fetchAll(db, "SELECT * FROM player")
}

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

let rows = try Row.fetchAll(db,
    "SELECT * FROM player WHERE name = ?",
    arguments: ["Arthur"])

let rows = try Row.fetchAll(db,
    "SELECT * FROM player WHERE name = :name",
    arguments: ["name": "Arthur"])

See Values for more information on supported arguments types (Bool, Int, String, Date, Swift enums, etc.), and StatementArguments for a detailed documentation of SQLite arguments.

Unlike row arrays that contain copies of the database rows, row cursors are close to the SQLite metal, and require a little care:

☝️ Don't turn a cursor of Row into an array. You would not get the distinct rows you expect. To get a array of rows, use Row.fetchAll(...). Generally speaking, make sure you copy a row whenever you extract it from a cursor for later use: row.copy().

Column Values

Read column values by index or column name:

let name: String = row[0]      // 0 is the leftmost column
let name: String = row["name"] // Leftmost matching column - lookup is case-insensitive
let name: String = row[Column("name")] // Using query interface's Column

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

let name: String? = row["name"]

The row[] subscript returns the type you ask for. See Values for more information on supported value types:

let bookCount: Int     = row["bookCount"]
let bookCount64: Int64 = row["bookCount"]
let hasBooks: Bool     = row["bookCount"] // false when 0

let string: String     = row["date"]      // "2015-09-11 18:14:15.123"
let date: Date         = row["date"]      // Date
self.date = row["date"] // Depends on the type of the property.

You can also use the as type casting operator:

row[...] as Int
row[...] as Int?

⚠️ Warning: avoid the as! and as? operators:

if let int = row[...] as? Int { ... } // BAD - doesn't work
if let int = row[...] as Int? { ... } // GOOD

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

• Successful conversions include:

  • All numeric SQLite values to all numeric Swift types, and Bool (zero is the only false boolean).
  • Text SQLite values to Swift String.
  • Blob SQLite values to Foundation Data.

  See Values for more information on supported types (Bool, Int, String, Date, Swift enums, etc.)

• NULL returns nil.

  let row = try Row.fetchOne(db, "SELECT NULL")!
  row[0] as Int? // nil
  row[0] as Int  // fatal error: could not convert NULL to Int.

  There is one exception, though: the DatabaseValue type:

  row[0] as DatabaseValue // DatabaseValue.null

• Missing columns return nil.

  let row = try Row.fetchOne(db, "SELECT 'foo' AS foo")!
  row["missing"] as String? // nil
  row["missing"] as String  // fatal error: no such column: missing

  You can explicitly check for a column presence with the hasColumn method.

• Invalid conversions throw a fatal error.

  let row = try Row.fetchOne(db, "SELECT 'Mom’s birthday'")!
  row[0] as String // "Mom’s birthday"
  row[0] as Date?  // fatal error: could not convert "Mom’s birthday" to Date.
  row[0] as Date   // fatal error: could not convert "Mom’s birthday" to Date.
  
  let row = try Row.fetchOne(db, "SELECT 256")!
  row[0] as Int    // 256
  row[0] as UInt8? // fatal error: could not convert 256 to UInt8.
  row[0] as UInt8  // fatal error: could not convert 256 to UInt8.

  Those conversion fatal errors can be avoided with the DatabaseValue type:

  let row = try Row.fetchOne(db, "SELECT 'Mom’s birthday'")!
  let dbValue: DatabaseValue = row[0]
  if dbValue.isNull {
      // Handle NULL
  } else if let date = Date.fromDatabaseValue(dbValue) {
      // Handle valid date
  } else {
      // Handle invalid date
  }

  This extra verbosity is the consequence of having to deal with an untrusted database: you may consider fixing the content of your database instead. See Fatal Errors for more information.

• SQLite has a weak type system, and provides convenience conversions that can turn String to Int, Double to Blob, etc.

  GRDB will sometimes let those conversions go through:

  let rows = try Row.fetchCursor(db, "SELECT '20 small cigars'")
  while let row = try rows.next() {
      row[0] as Int   // 20
  }

  Don't freak out: those conversions did not prevent SQLite from becoming the immensely successful database engine you want to use. And GRDB adds safety checks described just above. You can also prevent those convenience conversions altogether by using the DatabaseValue type.

DatabaseValue

DatabaseValue is an intermediate type between SQLite and your values, which gives information about the raw value stored in the database.

You get DatabaseValue just like other value types:

let dbValue: DatabaseValue = row[0]
let dbValue: DatabaseValue? = row["name"] // nil if and only if column does not exist

// Check for NULL:
dbValue.isNull // Bool

// The stored value:
dbValue.storage.value // Int64, Double, String, Data, or nil

// All the five storage classes supported by SQLite:
switch dbValue.storage {
case .null:                 print("NULL")
case .int64(let int64):     print("Int64: \(int64)")
case .double(let double):   print("Double: \(double)")
case .string(let string):   print("String: \(string)")
case .blob(let data):       print("Data: \(data)")
}

You can extract regular values (Bool, Int, String, Date, Swift enums, etc.) from DatabaseValue with the DatabaseValueConvertible.fromDatabaseValue() method:

let dbValue: DatabaseValue = row["bookCount"]
let bookCount   = Int.fromDatabaseValue(dbValue)   // Int?
let bookCount64 = Int64.fromDatabaseValue(dbValue) // Int64?
let hasBooks    = Bool.fromDatabaseValue(dbValue)  // Bool?, false when 0

let dbValue: DatabaseValue = row["date"]
let string = String.fromDatabaseValue(dbValue)     // "2015-09-11 18:14:15.123"
let date   = Date.fromDatabaseValue(dbValue)       // Date?

fromDatabaseValue returns nil for invalid conversions:

let row = try Row.fetchOne(db, "SELECT 'Mom’s birthday'")!
let dbValue: DatabaseValue = row[0]
let string = String.fromDatabaseValue(dbValue) // "Mom’s birthday"
let int    = Int.fromDatabaseValue(dbValue)    // nil
let date   = Date.fromDatabaseValue(dbValue)   // nil

Rows as Dictionaries

Row adopts the standard RandomAccessCollection protocol, and can be seen as a dictionary of DatabaseValue:

// All the (columnName, dbValue) tuples, from left to right:
for (columnName, dbValue) in row {
    ...
}

You can build rows from dictionaries (standard Swift dictionaries and NSDictionary). See Values for more information on supported types:

let row: Row = ["name": "foo", "date": nil]
let row = Row(["name": "foo", "date": nil])
let row = Row(/* [AnyHashable: Any] */) // nil if invalid dictionary

Yet rows are not real dictionaries: they may contain duplicate columns:

let row = try Row.fetchOne(db, "SELECT 1 AS foo, 2 AS foo")!
row.columnNames    // ["foo", "foo"]
row.databaseValues // [1, 2]
row["foo"]         // 1 (leftmost matching column)
for (columnName, dbValue) in row { ... } // ("foo", 1), ("foo", 2)

When you build a dictionary from a row, you have to disambiguate identical columns, and choose how to present database values. For example:

• A [String: DatabaseValue] dictionary that keeps leftmost value in case of duplicated column name:

  let dict = Dictionary(row, uniquingKeysWith: { (left, _) in left })

• A [String: AnyObject] dictionary which keeps rightmost value in case of duplicated column name. This dictionary is identical to FMResultSet's resultDictionary from FMDB. It contains NSNull values for null columns, and can be shared with Objective-C:

  let dict = Dictionary(
      row.map { (column, dbValue) in
          (column, dbValue.storage.value as AnyObject)
      },
      uniquingKeysWith: { (_, right) in right })

• A [String: Any] dictionary that can feed, for example, JSONSerialization:

  let dict = Dictionary(
      row.map { (column, dbValue) in
          (column, dbValue.storage.value)
      },
      uniquingKeysWith: { (left, _) in left })

See the documentation of Dictionary.init(_:uniquingKeysWith:) for more information.

Value Queries

Instead of rows, you can directly fetch values. Like rows, fetch them as cursors, arrays, or single values (see fetching methods). Values are extracted from the leftmost column of the SQL queries:

try dbQueue.read { db in
    try Int.fetchCursor(db, "SELECT ...", arguments: ...) // A Cursor of Int
    try Int.fetchAll(db, "SELECT ...", arguments: ...)    // [Int]
    try Int.fetchOne(db, "SELECT ...", arguments: ...)    // Int?
    
    // When database may contain NULL:
    try Optional<Int>.fetchCursor(db, "SELECT ...", arguments: ...) // A Cursor of Int?
    try Optional<Int>.fetchAll(db, "SELECT ...", arguments: ...)    // [Int?]
}

let playerCount = try dbQueue.read { db in
    try Int.fetchOne(db, "SELECT COUNT(*) FROM player")!
}

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.

There are many supported value types (Bool, Int, String, Date, Swift enums, etc.). See Values for more information:

let count = try Int.fetchOne(db, "SELECT COUNT(*) FROM player")! // Int
let urls = try URL.fetchAll(db, "SELECT url FROM link")          // [URL]

Values

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

• Swift Standard Library: Bool, Double, Float, all signed and unsigned integer types, String, Swift enums.

• Foundation: Data, Date, DateComponents, NSNull, NSNumber, NSString, URL, UUID.

• CoreGraphics: CGFloat.

• DatabaseValue, the type which gives information about the raw value stored in the database.

• Full-Text Patterns: FTS3Pattern and FTS5Pattern.

• Generally speaking, all types that adopt the DatabaseValueConvertible protocol.

Values can be used as statement arguments:

let url: URL = ...
let verified: Bool = ...
try db.execute(
    "INSERT INTO link (url, verified) VALUES (?, ?)",
    arguments: [url, verified])

Values can be extracted from rows:

let rows = try Row.fetchCursor(db, "SELECT * FROM link")
while let row = try rows.next() {
    let url: URL = row["url"]
    let verified: Bool = row["verified"]
}

Values can be directly fetched:

let urls = try URL.fetchAll(db, "SELECT url FROM link")  // [URL]

Use values in Records:

struct Link: FetchableRecord {
    var url: URL
    var isVerified: Bool
    
    init(row: Row) {
        url = row["url"]
        isVerified = row["verified"]
    }
}

Use values in the query interface:

let url: URL = ...
let link = try Link.filter(Column("url") == url).fetchOne(db)

Data (and Memory Savings)

Data suits the BLOB SQLite columns. It can be stored and fetched from the database just like other values:

let rows = try Row.fetchCursor(db, "SELECT data, ...")
while let row = try rows.next() {
    let data: Data = row["data"]
}

At each step of the request iteration, the row[] subscript creates two copies of the database bytes: one fetched by SQLite, and another, stored in the Swift Data value.

You have the opportunity to save memory by not copying the data fetched by SQLite:

while let row = try rows.next() {
    let data = row.dataNoCopy(named: "data") // Data?
}

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

Date and DateComponents

Date and DateComponents can be stored and fetched from the database.

Here is how GRDB supports the various date formats supported by SQLite:

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

² GRDB 2+ interprets numerical values as timestamps that fuel Date(timeIntervalSince1970:). Previous GRDB versions used to interpret numbers as julian days. Julian days are still supported, with the Date(julianDay:) initializer.

Date

Date can be stored and fetched from the database just like other values:

try db.execute(
    "INSERT INTO player (creationDate, ...) VALUES (?, ...)",
    arguments: [Date(), ...])

let row = try Row.fetchOne(db, ...)!
let creationDate: Date = row["creationDate"]

Dates are stored using the format "YYYY-MM-DD HH:MM:SS.SSS" in the UTC time zone. It is precise to the millisecond.

☝️ Note: this format was chosen because it is the only format that is:

• Comparable (ORDER BY date works)
• Comparable with the SQLite keyword CURRENT_TIMESTAMP (WHERE date > CURRENT_TIMESTAMP works)
• Able to feed SQLite date & time functions
• Precise enough

When the default format does not fit your needs, customize date conversions. For example:

try db.execute(
    "INSERT INTO player (creationDate, ...) VALUES (?, ...)",
    arguments: [Date().timeIntervalSinceReferenceDate, ...])

let row = try Row.fetchOne(db, ...)!
let creationDate = Date(timeIntervalSinceReferenceDate: row["creationDate"])

See Codable Records for more date customization options.

DateComponents

DateComponents is indirectly supported, through 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.

DatabaseDateComponents can be stored and fetched from the database just like other values:

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

// Store "1973-09-18"
let dbComponents = DatabaseDateComponents(components, format: .YMD)
try db.execute(
    "INSERT INTO player (birthDate, ...) VALUES (?, ...)",
    arguments: [dbComponents, ...])

// Read "1973-09-18"
let row = try Row.fetchOne(db, "SELECT birthDate ...")!
let dbComponents: DatabaseDateComponents = row["birthDate"]
dbComponents.format         // .YMD (the actual format found in the database)
dbComponents.dateComponents // DateComponents

NSNumber and NSDecimalNumber

NSNumber can be stored and fetched from the database just like other values. Floating point NSNumbers are stored as Double. Integer and boolean, as Int64. Integers that don't fit Int64 won't be stored: you'll get a fatal error instead. Be cautious when an NSNumber contains an UInt64, for example.

NSDecimalNumber deserves a longer discussion:

SQLite has no support for decimal numbers. Given the table below, SQLite will actually store integers or doubles:

CREATE TABLE transfer (
    amount DECIMAL(10,5) -- will store integer or double, actually
)

This means that computations will not be exact:

try db.execute("INSERT INTO transfer (amount) VALUES (0.1)")
try db.execute("INSERT INTO transfer (amount) VALUES (0.2)")
let sum = try NSDecimalNumber.fetchOne(db, "SELECT SUM(amount) FROM transfer")!

// Yikes! 0.3000000000000000512
print(sum)

Don't blame SQLite or GRDB, and instead store your decimal numbers differently.

A classic technique is to store integers instead, since SQLite performs exact computations of integers. For example, don't store Euros, but store cents instead:

// Write
let amount = NSDecimalNumber(string: "0.10")
let integerAmount = amount.multiplying(byPowerOf10: 2).int64Value
try db.execute("INSERT INTO transfer (amount) VALUES (?)", arguments: [integerAmount])

// Read
let integerAmount = try Int64.fetchOne(db, "SELECT SUM(amount) FROM transfer")!
let amount = NSDecimalNumber(value: integerAmount).multiplying(byPowerOf10: -2) // 0.10

UUID

UUID can be stored and fetched from the database just like other values.

GRDB stores uuids as 16-bytes data blobs, and decodes them from both 16-bytes data blobs and strings such as "E621E1F8-C36C-495A-93FC-0C247A3E6E5F".

Swift Enums

Swift enums and generally all types that adopt the RawRepresentable protocol can be stored and fetched from the database just like their raw values:

enum Color : Int {
    case red, white, rose
}

enum Grape : String {
    case chardonnay, merlot, riesling
}

// Declare empty DatabaseValueConvertible adoption
extension Color : DatabaseValueConvertible { }
extension Grape : DatabaseValueConvertible { }

// Store
try db.execute(
    "INSERT INTO wine (grape, color) VALUES (?, ?)",
    arguments: [Grape.merlot, Color.red])

// Read
let rows = try Row.fetchCursor(db, "SELECT * FROM wine")
while let row = try rows.next() {
    let grape: Grape = row["grape"]
    let color: Color = row["color"]
}

When a database value does not match any enum case, you get a fatal error. This fatal error can be avoided with the DatabaseValue type:

let row = try Row.fetchOne(db, "SELECT 'syrah'")!

row[0] as String  // "syrah"
row[0] as Grape?  // fatal error: could not convert "syrah" to Grape.
row[0] as Grape   // fatal error: could not convert "syrah" to Grape.

let dbValue: DatabaseValue = row[0]
if dbValue.isNull {
    // Handle NULL
} else if let grape = Grape.fromDatabaseValue(dbValue) {
    // Handle valid grape
} else {
    // Handle unknown grape
}

Custom Value Types

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

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

All types that adopt this protocol can be used like all other values (Bool, Int, String, Date, Swift enums, etc.)

The databaseValue property returns DatabaseValue, a type that wraps the five values supported by SQLite: NULL, Int64, Double, String and Data. Since DatabaseValue has no public initializer, use DatabaseValue.null, or another type that already adopts the protocol: 1.databaseValue, "foo".databaseValue, etc. Conversion to DatabaseValue must not fail.

The fromDatabaseValue() factory method returns an instance of your custom type if the database value contains a suitable value. If the database value does not contain a suitable value, such as "foo" for Date, fromDatabaseValue must return nil (GRDB will interpret this nil result as a conversion error, and react accordingly).

Transactions and Savepoints

• Transactions and Safety
• Explicit Transactions
• Savepoints
• Transaction Kinds

Transactions and Safety

A transaction is a fundamental tool of SQLite that guarantees data consistency as well as proper isolation between application threads and database connections.

GRDB generally opens transactions for you, as a way to enforce its concurrency guarantees, and provide maximal security for both your application data and application logic:

// BEGIN TRANSACTION
// INSERT INTO credit ...
// INSERT INTO debit ...
// COMMIT
try dbQueue.write { db in
    try Credit(destinationAccout, amount).insert(db)
    try Debit(sourceAccount, amount).insert(db)
}

// BEGIN TRANSACTION
// INSERT INTO credit ...
// INSERT INTO debit ...
// COMMIT
try dbPool.write { db in
    try Credit(destinationAccout, amount).insert(db)
    try Debit(sourceAccount, amount).insert(db)
}

Yet you may need to exactly control when transactions take place:

Explicit Transactions

DatabaseQueue.inDatabase() and DatabasePool.writeWithoutTransaction() execute your database statements outside of any transaction:

// INSERT INTO credit ...
// INSERT INTO debit ...
try dbQueue.inDatabase { db in
    try Credit(destinationAccout, amount).insert(db)
    try Debit(sourceAccount, amount).insert(db)
}

// INSERT INTO credit ...
// INSERT INTO debit ...
try dbPool.writeWithoutTransaction { db in
    try Credit(destinationAccout, amount).insert(db)
    try Debit(sourceAccount, amount).insert(db)
}

Writing outside of any transaction is dangerous, for two reasons:

• In our credit/debit example, you may successfully insert a credit, but fail inserting the debit, and end up with unbalanced accounts (oops).

  // UNSAFE DATABASE INTEGRITY
  try dbQueue.inDatabase { db in // or dbPool.writeWithoutTransaction
      try Credit(destinationAccout, amount).insert(db) // may succeed
      try Debit(sourceAccount, amount).insert(db)      // may fail
  }

  Transactions avoid this kind of bug.

• Database pool concurrent reads can see an inconsistent state of the database:

  // UNSAFE CONCURRENCY
  try dbPool.writeWithoutTransaction { db in
      try Credit(destinationAccout, amount).insert(db)
      // <- Concurrent dbPool.read sees a partial db update here
      try Debit(sourceAccount, amount).insert(db)
  }

  Transactions avoid this kind of bug, too.

To open explicit transactions, use one of the Database.inTransaction, DatabaseQueue.inTransaction, or DatabasePool.writeInTransaction methods:

// BEGIN TRANSACTION
// INSERT INTO credit ...
// INSERT INTO debit ...
// COMMIT
try dbQueue.inDatabase { db in  // or dbPool.writeWithoutTransaction
    try db.inTransaction {
        try Credit(destinationAccout, amount).insert(db)
        try Debit(sourceAccount, amount).insert(db)
        return .commit
    }
}

// BEGIN TRANSACTION
// INSERT INTO credit ...
// INSERT INTO debit ...
// COMMIT
try dbQueue.inTransaction { db in  // or dbPool.writeInTransaction
    try Credit(destinationAccout, amount).insert(db)
    try Debit(sourceAccount, amount).insert(db)
    return .commit
}

If an error is thrown from the transaction block, the transaction is rollbacked and the error is rethrown by the inTransaction method. If you return .rollback instead of .commit, the transaction is also rollbacked, but no error is thrown.

You can also perform manual transaction management:

try dbQueue.inDatabase { db in  // or dbPool.writeWithoutTransaction
    try db.beginTransaction()
    ...
    try db.commit()
    
    try db.execute("BEGIN TRANSACTION")
    ...
    try db.execute("ROLLBACK")
}

Transactions can't be left opened unless you set the allowsUnsafeTransactions configuration flag:

// fatal error: A transaction has been left opened at the end of a database access
try dbQueue.inDatabase { db in
    try db.execute("BEGIN TRANSACTION")
    // <- no commit or rollback
}

You can ask if a transaction is currently opened:

func myCriticalMethod(_ db: Database) throws {
    precondition(db.isInsideTransaction, "This method requires a transaction")
    try ...
}

Yet, you have a better option than checking for transactions: critical database sections should use savepoints, described below:

func myCriticalMethod(_ db: Database) throws {
    try db.inSavepoint {
        // Here the database is guaranteed to be inside a transaction.
        try ...
    }
}

Savepoints

Statements grouped in a savepoint can be rollbacked without invalidating a whole transaction:

try dbQueue.write { db in
    // Makes sure both inserts succeed, or none:
    try db.inSavepoint {
        try Credit(destinationAccout, amount).insert(db)
        try Debit(sourceAccount, amount).insert(db)
        return .commit
    }
    
    // Other savepoints, etc...
}

If an error is thrown from the savepoint block, the savepoint is rollbacked and the error is rethrown by the inSavepoint method. If you return .rollback instead of .commit, the savepoint is also rollbacked, but no error is thrown.

Unlike transactions, savepoints can be nested. They implicitly open a transaction if no one was opened when the savepoint begins. As such, they behave just like nested transactions. Yet the database changes are only written to disk when the outermost transaction is committed:

try dbQueue.inDatabase { db in
    try db.inSavepoint {
        ...
        try db.inSavepoint {
            ...
            return .commit
        }
        ...
        return .commit  // writes changes to disk
    }
}

SQLite savepoints are more than nested transactions, though. For advanced uses, use SQLite savepoint documentation.

Transaction Kinds

SQLite supports three kinds of transactions: deferred (the default), immediate, and exclusive.

The transaction kind can be changed in the database configuration, or for each transaction:

// 1) Default configuration:
let dbQueue = try DatabaseQueue(path: "...")

// BEGIN DEFERED TRANSACTION ...
dbQueue.write { db in ... }

// BEGIN EXCLUSIVE TRANSACTION ...
dbQueue.inTransaction(.exclusive) { db in ... }

// 2) Customized default transaction kind:
var config = Configuration()
config.defaultTransactionKind = .immediate
let dbQueue = try DatabaseQueue(path: "...", configuration: config)

// BEGIN IMMEDIATE TRANSACTION ...
dbQueue.write { db in ... }

// BEGIN EXCLUSIVE TRANSACTION ...
dbQueue.inTransaction(.exclusive) { db in ... }

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 of prepared statements: select statements, and update statements:

try dbQueue.write { db in
    let updateSQL = "INSERT INTO player (name, score) VALUES (:name, :score)"
    let updateStatement = try db.makeUpdateStatement(updateSQL)
    
    let selectSQL = "SELECT * FROM player WHERE name = ?"
    let selectStatement = try db.makeSelectStatement(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 ExpressibleByArrayLiteral and ExpressibleByDictionaryLiteral protocols).

updateStatement.arguments = ["name": "Arthur", "score": 1000]
selectStatement.arguments = ["Arthur"]

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

try updateStatement.execute()

Select statements can be used wherever a raw SQL query string would fit (see fetch queries):

let rows = try Row.fetchCursor(selectStatement)    // A Cursor of Row
let players = try Player.fetchAll(selectStatement) // [Player]
let player = try Player.fetchOne(selectStatement)  // Player?

You can set the arguments at the moment of the statement execution:

try updateStatement.execute(arguments: ["name": "Arthur", "score": 1000])
let player = try Player.fetchOne(selectStatement, arguments: ["Arthur"])

☝️ Note: it is a programmer error to reuse a prepared statement that has failed: GRDB may crash if you do so.

See row queries, value queries, and Records for more information.

Prepared Statements Cache

When the same query will be used several times in the lifetime of your application, you may feel a natural desire to cache prepared statements.

Don't cache statements yourself.

☝️ Note: This is because you don't have the necessary tools. Statements are tied to specific SQLite connections and dispatch queues which you don't manage yourself, especially when you use database pools. A change in the database schema may, or may not invalidate a statement. On systems earlier than iOS 8.2 and OSX 10.10 that don't have the sqlite3_close_v2 function, SQLite connections won't close properly if statements have been kept alive.

Instead, use the cachedUpdateStatement and cachedSelectStatement methods. GRDB does all the hard caching and memory management stuff for you:

let updateStatement = try db.cachedUpdateStatement(sql)
let selectStatement = try db.cachedSelectStatement(sql)

Should a cached prepared statement throw an error, don't reuse it (it is a programmer error). Instead, reload it from the cache.

Custom SQL Functions and Aggregates

SQLite lets you define SQL functions and aggregates.

A custom SQL function or aggregate extends SQLite:

SELECT reverse(name) FROM player;   -- custom function
SELECT maxLength(name) FROM player; -- custom aggregate

• Custom SQL Functions
• Custom Aggregates

Custom SQL Functions

let reverse = DatabaseFunction("reverse", argumentCount: 1, pure: true) { (values: [DatabaseValue]) in
    // Extract string value, if any...
    guard let string = String.fromDatabaseValue(values[0]) else {
        return nil
    }
    // ... and return reversed string:
    return String(string.reversed())
}
dbQueue.add(function: reverse)   // Or dbPool.add(function: ...)

try dbQueue.read { db in
    // "oof"
    try String.fetchOne(db, "SELECT reverse('foo')")!
}

The function argument takes an array of DatabaseValue, and returns any valid value (Bool, Int, String, Date, Swift enums, etc.) The number of database values is guaranteed to be argumentCount.

SQLite has the opportunity to perform additional optimizations when functions are "pure", which means that their result only depends on their arguments. So make sure to set the pure argument to true when possible.

Functions can take a variable number of arguments:

When you don't provide any explicit argumentCount, the function can take any number of arguments:

let averageOf = DatabaseFunction("averageOf", pure: true) { (values: [DatabaseValue]) in
    let doubles = values.compactMap { Double.fromDatabaseValue($0) }
    return doubles.reduce(0, +) / Double(doubles.count)
}
dbQueue.add(function: averageOf)

try dbQueue.read { db in
    // 2.0
    try Double.fetchOne(db, "SELECT averageOf(1, 2, 3)")!
}

Functions can throw:

let sqrt = DatabaseFunction("sqrt", argumentCount: 1, pure: true) { (values: [DatabaseValue]) in
    guard let double = Double.fromDatabaseValue(values[0]) else {
        return nil
    }
    guard double >= 0 else {
        throw DatabaseError(message: "invalid negative number")
    }
    return sqrt(double)
}
dbQueue.add(function: sqrt)

// SQLite error 1 with statement `SELECT sqrt(-1)`: invalid negative number
try dbQueue.read { db in
    try Double.fetchOne(db, "SELECT sqrt(-1)")!
}

Use custom functions in the query interface:

// SELECT reverseString("name") FROM player
Player.select(reverseString.apply(nameColumn))

GRDB ships with built-in SQL functions that perform unicode-aware string transformations. See Unicode.

Custom Aggregates

Before registering a custom aggregate, you need to define a type that adopts the DatabaseAggregate protocol:

protocol DatabaseAggregate {
    // Initializes an aggregate
    init()
    
    // Called at each step of the aggregation
    mutating func step(_ dbValues: [DatabaseValue]) throws
    
    // Returns the final result
    func finalize() throws -> DatabaseValueConvertible?
}

For example:

struct MaxLength : DatabaseAggregate {
    var maxLength: Int = 0
    
    mutating func step(_ dbValues: [DatabaseValue]) {
        // At each step, extract string value, if any...
        guard let string = String.fromDatabaseValue(dbValues[0]) else {
            return
        }
        // ... and update the result
        let length = string.count
        if length > maxLength {
            maxLength = length
        }
    }
    
    func finalize() -> DatabaseValueConvertible? {
        return maxLength
    }
}

let maxLength = DatabaseFunction(
    "maxLength",
    argumentCount: 1,
    pure: true,
    aggregate: MaxLength.self)

dbQueue.add(function: maxLength)   // Or dbPool.add(function: ...)

try dbQueue.read { db in
    // Some Int
    try Int.fetchOne(db, "SELECT maxLength(name) FROM player")!
}

The step method of the aggregate takes an array of DatabaseValue. This array contains as many values as the argumentCount parameter (or any number of values, when argumentCount is omitted).

The finalize method of the aggregate returns the final aggregated value (Bool, Int, String, Date, Swift enums, etc.).

SQLite has the opportunity to perform additional optimizations when aggregates are "pure", which means that their result only depends on their inputs. So make sure to set the pure argument to true when possible.

Use custom aggregates in the query interface:

// SELECT maxLength("name") FROM player
let request = Player.select(maxLength.apply(nameColumn))
try Int.fetchOne(db, request) // Int?

Database Schema Introspection

GRDB comes with a set of schema introspection methods:

try dbQueue.read { db in
    // Bool, true if the table exists
    try db.tableExists("player")
    
    // [ColumnInfo], the columns in the table
    try db.columns(in: "player")
    
    // PrimaryKeyInfo
    try db.primaryKey("player")
    
    // [ForeignKeyInfo], the foreign keys defined on the table
    try db.foreignKeys(on: "player")
    
    // [IndexInfo], the indexes defined on the table
    try db.indexes(on: "player")
    
    // Bool, true if column(s) is a unique key (primary key or unique index)
    try db.table("player", hasUniqueKey: ["email"])
}

// Bool, true if argument is the name of an internal SQLite table
Database.isSQLiteInternalTable(...)

// Bool, true if argument is the name of an internal GRDB table
Database.isGRDBInternalTable(...)

Row Adapters

Row adapters let you present database rows in the way expected by the row consumers.

They basically help two incompatible row interfaces to work together. For example, a row consumer expects a column named "consumed", but the produced row has a column named "produced".

In this case, the ColumnMapping row adapter comes in handy:

// Fetch a 'produced' column, and consume a 'consumed' column:
let adapter = ColumnMapping(["consumed": "produced"])
let row = try Row.fetchOne(db, "SELECT 'Hello' AS produced", adapter: adapter)!
row["consumed"] // "Hello"
row["produced"] // nil

Row adapters are values that adopt the RowAdapter protocol. You can implement your own custom adapters (🔥 EXPERIMENTAL), or use one of the four built-in adapters, described below.

To see how row adapters can be used, see Joined Queries Support.

ColumnMapping

ColumnMapping renames columns. Build one with a dictionary whose keys are adapted column names, and values the column names in the raw row:

// [newName:"Hello"]
let adapter = ColumnMapping(["newName": "oldName"])
let row = try Row.fetchOne(db, "SELECT 'Hello' AS oldName", adapter: adapter)!

SuffixRowAdapter

SuffixRowAdapter hides the first columns in a row:

// [b:1 c:2]
let adapter = SuffixRowAdapter(fromIndex: 1)
let row = try Row.fetchOne(db, "SELECT 0 AS a, 1 AS b, 2 AS c", adapter: adapter)!

RangeRowAdapter

RangeRowAdapter only exposes a range of columns.

// [b:1]
let adapter = RangeRowAdapter(1..<2)
let row = try Row.fetchOne(db, "SELECT 0 AS a, 1 AS b, 2 AS c", adapter: adapter)!

EmptyRowAdapter

EmptyRowAdapter hides all columns.

let adapter = EmptyRowAdapter()
let row = try Row.fetchOne(db, "SELECT 0 AS a, 1 AS b, 2 AS c", adapter: adapter)!
row.isEmpty // true

This limit adapter may turn out useful in some narrow use cases. You'll be happy to find it when you need it.

ScopeAdapter

ScopeAdapter defines row scopes:

let adapter = ScopeAdapter([
    "left": RangeRowAdapter(0..<2),
    "right": RangeRowAdapter(2..<4)])
let row = try Row.fetchOne(db, "SELECT 0 AS a, 1 AS b, 2 AS c, 3 AS d", adapter: adapter)!

ScopeAdapter does not change the columns and values of the fetched row. Instead, it defines scopes, which you access through the Row.scopes property:

row                   // [a:0 b:1 c:2 d:3]
row.scopes["left"]    // [a:0 b:1]
row.scopes["right"]   // [c:2 d:3]
row.scopes["missing"] // nil

Scopes can be nested:

let adapter = ScopeAdapter([
    "left": ScopeAdapter([
        "left": RangeRowAdapter(0..<1),
        "right": RangeRowAdapter(1..<2)]),
    "right": ScopeAdapter([
        "left": RangeRowAdapter(2..<3),
        "right": RangeRowAdapter(3..<4)])
    ])
let row = try Row.fetchOne(db, "SELECT 0 AS a, 1 AS b, 2 AS c, 3 AS d", adapter: adapter)!

let leftRow = row.scopes["left"]!
leftRow.scopes["left"]  // [a:0]
leftRow.scopes["right"] // [b:1]

let rightRow = row.scopes["right"]!
rightRow.scopes["left"]  // [c:2]
rightRow.scopes["right"] // [d:3]

Any adapter can be extended with scopes:

let baseAdapter = RangeRowAdapter(0..<2)
let adapter = ScopeAdapter(base: baseAdapter, scopes: [
    "remainder": SuffixRowAdapter(fromIndex: 2)])
let row = try Row.fetchOne(db, "SELECT 0 AS a, 1 AS b, 2 AS c, 3 AS d", adapter: adapter)!

row // [a:0 b:1]
row.scopes["remainder"] // [c:2 d:3]

Raw SQLite Pointers

If not all SQLite APIs are exposed in GRDB, you can still use the SQLite C Interface and call SQLite C functions.

Those functions are embedded right into the GRDBCustom and GRCBCipher modules. For the "regular" GRDB framework: you'll need to import SQLite3, or CSQLite, depending on whether you use the Swift Package Manager or not:

#if SWIFT_PACKAGE
    import CSQLite // For Swift Package Manager
#else
    import SQLite3 // Otherwise
#endif

let sqliteVersion = String(cString: sqlite3_libversion())

Raw pointers to database connections and statements are available through the Database.sqliteConnection and Statement.sqliteStatement properties:

try dbQueue.read { db in
    // The raw pointer to a database connection:
    let sqliteConnection = db.sqliteConnection

    // The raw pointer to a statement:
    let statement = try db.makeSelectStatement("SELECT ...")
    let sqliteStatement = statement.sqliteStatement
}

☝️ Notes

• Those pointers are owned by GRDB: don't close connections or finalize statements created by GRDB.
• GRDB opens SQLite connections in the "multi-thread mode", which (oddly) means that they are not thread-safe. Make sure you touch raw databases and statements inside their dedicated dispatch queues.
• Use the raw SQLite C Interface at your own risk. GRDB won't prevent you from shooting yourself in the foot.

Before jumping in the low-level wagon, here is the list of all SQLite APIs used by GRDB:

• sqlite3_aggregate_context, sqlite3_create_function_v2, sqlite3_result_blob, sqlite3_result_double, sqlite3_result_error, sqlite3_result_error_code, sqlite3_result_int64, sqlite3_result_null, sqlite3_result_text, sqlite3_user_data, sqlite3_value_blob, sqlite3_value_bytes, sqlite3_value_double, sqlite3_value_int64, sqlite3_value_text, sqlite3_value_type: see Custom SQL Functions and Aggregates
• sqlite3_backup_finish, sqlite3_backup_init, sqlite3_backup_step: see Backup
• sqlite3_bind_blob, sqlite3_bind_double, sqlite3_bind_int64, sqlite3_bind_null, sqlite3_bind_parameter_count, sqlite3_bind_parameter_name, sqlite3_bind_text, sqlite3_clear_bindings, sqlite3_column_blob, sqlite3_column_bytes, sqlite3_column_count, sqlite3_column_double, sqlite3_column_int64, sqlite3_column_name, sqlite3_column_text, sqlite3_column_type, sqlite3_exec, sqlite3_finalize, sqlite3_prepare_v2, sqlite3_reset, sqlite3_step: see Executing Updates, Fetch Queries, Prepared Statements, Values
• sqlite3_busy_handler, sqlite3_busy_timeout: see Configuration.busyMode
• sqlite3_changes, sqlite3_total_changes: see Database.changesCount and Database.totalChangesCount
• sqlite3_close, sqlite3_close_v2, sqlite3_next_stmt, sqlite3_open_v2: see Database Connections
• sqlite3_commit_hook, sqlite3_rollback_hook, sqlite3_update_hook: see TransactionObserver Protocol, DatabaseRegionObservation, ValueObservation, FetchedRecordsController
• sqlite3_config: see Error Log
• sqlite3_create_collation_v2: see String Comparison
• sqlite3_db_release_memory: see Memory Management
• sqlite3_errcode, sqlite3_errmsg, sqlite3_errstr, sqlite3_extended_result_codes: see Error Handling
• sqlite3_key, sqlite3_rekey: see Encryption
• sqlite3_last_insert_rowid: see Executing Updates
• sqlite3_preupdate_count, sqlite3_preupdate_depth, sqlite3_preupdate_hook, sqlite3_preupdate_new, sqlite3_preupdate_old: see Support for SQLite Pre-Update Hooks
• sqlite3_set_authorizer: reserved by GRDB
• sqlite3_sql: see Statement.sql
• sqlite3_trace: see Configuration.trace
• sqlite3_wal_checkpoint_v2: see DatabasePool.checkpoint

Records

On top of the SQLite API, GRDB provides protocols and a class that help manipulating database rows as regular objects named "records":

try dbQueue.write { db in
    if var place = try Place.fetchOne(db, key: 1) {
        place.isFavorite = true
        try place.update(db)
    }
}

Of course, you need to open a database connection, and create database tables first.

To define your custom records, you subclass the ready-made Record class, or you extend your structs and classes with protocols that come with focused sets of features: fetching methods, persistence methods, record comparison...

Extending structs with record protocols is more "swifty". Subclassing the Record class is more "classic". You can choose either way. See some examples of record definitions, and the list of record methods for an overview.

☝️ Note: if you are familiar with Core Data's NSManagedObject or Realm's Object, you may experience a cultural shock: GRDB records are not uniqued, do not auto-update, and do not lazy-load. This is both a purpose, and a consequence of protocol-oriented programming. You should read How to build an iOS application with SQLite and GRDB.swift for a general introduction.

💡 Tip: after you have read this chapter, check the Good Practices for Designing Record Types Guide.

💡 Tip: see the Demo Application for a sample app that uses records.

Overview

• Inserting Records
• Fetching Records
• Updating Records
• Deleting Records
• Counting Records

Protocols and the Record Class

• Record Protocols Overview
• FetchableRecord Protocol
• TableRecord Protocol
• PersistableRecord Protocol
  • Persistence Methods
  • Customizing the Persistence Methods
• Codable Records
• Record Class
• Record Comparison
• Record Customization Options

Records in a Glance

• Examples of Record Definitions
• List of Record Methods

Inserting Records

To insert a record in the database, call the insert method:

let player = Player(name: "Arthur", email: "[email protected]")
try player.insert(db)

👉 insert is available for subclasses of the Record class, and types that adopt the PersistableRecord protocol.

Fetching Records

To fetch records from the database, call a fetching method:

let arthur = try Player.fetchOne(db,            // Player?
    "SELECT * FROM players WHERE name = ?",
    arguments: ["Arthur"])

let bestPlayers = try Player                    // [Player]
    .order(Column("score").desc)
    .limit(10)
    .fetchAll(db)
    
let spain = try Country.fetchOne(db, key: "ES") // Country?

👉 Fetching from raw SQL is available for subclasses of the Record class, and types that adopt the FetchableRecord protocol.

👉 Fetching without SQL, using the query interface, is available for subclasses of the Record class, and types that adopt both FetchableRecord and TableRecord protocol.

Updating Records

To update a record in the database, call the update method:

if let player = try Player.fetchOne(db, key: 1) 
    player.score = 1000
    try player.update(db)
}

It is possible to avoid useless updates:

if let player = try Player.fetchOne(db, key: 1) {
    // does not hit the database if score has not changed
    try player.updateChanges(db) {
        $0.score = 1000
    }
}

For batch updates, execute an SQL query:

try db.execute("UPDATE player SET synchronized = 1")

👉 update methods are available for subclasses of the Record class, and types that adopt the PersistableRecord protocol.

Deleting Records

To delete a record in the database, call the delete method:

if let player = try Player.fetchOne(db, key: 1) {
    try player.delete(db)
}

You can also delete by primary key, or any unique index:

try Player.deleteOne(db, key: 1)
try Player.deleteOne(db, key: ["email": "[email protected]"])
try Country.deleteAll(db, keys: ["FR", "US"])

For batch deletes, execute an SQL query, or see the query interface:

try Player
    .filter(Column("email") == nil)
    .deleteAll(db)

👉 delete methods are available for subclasses of the Record class, and types that adopt the PersistableRecord protocol.

Counting Records

To count records, call the fetchCount method:

let playerCount: Int = try Player.fetchCount(db)

let playerWithEmailCount: Int = try Player
    .filter(Column("email") == nil)
    .fetchCount(db)

👉 fetchCount is available for subclasses of the Record class, and types that adopt the TableRecord protocol.

Details follow:

• Record Protocols Overview
• FetchableRecord Protocol
• TableRecord Protocol
• PersistableRecord Protocol
• Codable Records
• Record Class
• Record Comparison
• Record Customization Options
• Examples of Record Definitions
• List of Record Methods

Record Protocols Overview

GRDB ships with three record protocols. Your own types will adopt one or several of them, according to the abilities you want to extend your types with.

• FetchableRecord is able to decode database rows.

  It is always possible to decode rows without this protocol:

  struct Place { ... }
  try dbQueue.read { db in
      let rows = try Row.fetchAll(db, "SELECT * FROM place")
      let places: [Place] = rows.map { row in
          return Place(
              id: row["id"],
              title: row["title"],
              coordinate: CLLocationCoordinate2D(
                  latitude: row["latitude"],
                  longitude: row["longitude"]))
          )
      }
  }

  But FetchableRecord lets you write code that is easier to read, and more efficient as well, both in terms of performance and memory usage:

  struct Place: FetchableRecord { ... }
  try dbQueue.read { db in
      let places = try Place.fetchAll(db, "SELECT * FROM place")
  }

  💡 Tip: FetchableRecord can derive its implementation from the standard Decodable protocol. See Codable Records for more information.

  FetchableRecord can decode database rows, but it is not able to build SQL requests for you. For that, you also need TableRecord:

• TableRecord is able to generate SQL queries:

  struct Place: TableRecord { ... }
  // SELECT * FROM place ORDER BY title
  let request = Place.order(Column("title"))

  When a type adopts both TableRecord and FetchableRecord, it can load from those requests:

  struct Place: TableRecord, FetchableRecord { ... }
  try dbQueue.read { db in
      let places = try Place.order(Column("title")).fetchAll(db)
      let paris = try Place.fetchOne(key: 1)
  }

• PersistableRecord is able to write: it can create, update, and delete rows in the database:

  struct Place : PersistableRecord { ... }
  try dbQueue.write { db in
      try Place.delete(db, key: 1)
      try Place(...).insert(db)
  }

  A persistable record can also compare itself against other records, and avoid useless database updates.

  💡 Tip: PersistableRecord can derive its implementation from the standard Encodable protocol. See Codable Records for more information.

FetchableRecord Protocol

The FetchableRecord protocol grants fetching methods to any type that can be built from a database row:

protocol FetchableRecord {
    /// Row initializer
    init(row: Row)
}

To use FetchableRecord, subclass the Record class, or adopt it explicitly. For example:

struct Place {
    var id: Int64?
    var title: String
    var coordinate: CLLocationCoordinate2D
}

extension Place : FetchableRecord {
    init(row: Row) {
        id = row["id"]
        title = row["title"]
        coordinate = CLLocationCoordinate2D(
            latitude: row["latitude"],
            longitude: row["longitude"])
    }
}

Rows also accept column enums:

extension Place : FetchableRecord {
    enum Columns: String, ColumnExpression {
        case id, title, latitude, longitude
    }
    
    init(row: Row) {
        id = row[Columns.id]
        title = row[Columns.title]
        coordinate = CLLocationCoordinate2D(
            latitude: row[Columns.latitude],
            longitude: row[Columns.longitude])
    }
}

See column values for more information about the row[] subscript.

When your record type adopts the standard Decodable protocol, you don't have to provide the implementation for init(row:). See Codable Records for more information:

// That's all
struct Player: Decodable, FetchableRecord {
    var id: Int64
    var name: String
    var score: Int
}

FetchableRecord allows adopting types to be fetched from SQL queries:

try Place.fetchCursor(db, "SELECT ...", arguments:...) // A Cursor of Place
try Place.fetchAll(db, "SELECT ...", arguments:...)    // [Place]
try Place.fetchOne(db, "SELECT ...", arguments:...)    // Place?

See fetching methods for information about the fetchCursor, fetchAll and fetchOne methods. See StatementArguments for more information about the query arguments.

☝️ Note: for performance reasons, the same row argument to init(row:) is reused during the iteration of a fetch query. If you want to keep the row for later use, make sure to store a copy: self.row = row.copy().

☝️ Note: The FetchableRecord.init(row:) initializer fits the needs of most applications. But some application are more demanding than others. When FetchableRecord does not exactly provide the support you need, have a look at the Beyond FetchableRecord chapter.

TableRecord Protocol

The TableRecord protocol generates SQL for you. To use TableRecord, subclass the Record class, or adopt it explicitly:

protocol TableRecord {
    static var databaseTableName: String { get }
    static var databaseSelection: [SQLSelectable] { get }
}

The databaseSelection type property is optional, and documented in the Columns Selected by a Request chapter.

The databaseTableName type property is the name of a database table. By default, it is derived from the type name:

struct Place: TableRecord { }
print(Place.databaseTableName) // prints "place"

For example:

• Place: place
• Country: country
• PostalAddress: postalAddress
• HTTPRequest: httpRequest
• TOEFL: toefl

You can still provide a custom table name:

struct Place: TableRecord {
    static let databaseTableName = "location"
}
print(Place.databaseTableName) // prints "location"

Subclasses of the Record class must always override their superclass's databaseTableName property:

class Place: Record {
    override class var databaseTableName: String {
        return "place"
    }
}
print(Place.databaseTableName) // prints "place"

When a type adopts both TableRecord and FetchableRecord, it can be fetched using the query interface:

// SELECT * FROM place WHERE name = 'Paris'
let paris = try Place.filter(nameColumn == "Paris").fetchOne(db)

TableRecord can also fetch records by primary key:

try Player.fetchOne(db, key: 1)              // Player?
try Player.fetchAll(db, keys: [1, 2, 3])     // [Player]

try Country.fetchOne(db, key: "FR")          // Country?
try Country.fetchAll(db, keys: ["FR", "US"]) // [Country]

When the table has no explicit primary key, GRDB uses the hidden "rowid" column:

// SELECT * FROM document WHERE rowid = 1
try Document.fetchOne(db, key: 1)            // Document?

For multiple-column primary keys and unique keys defined by unique indexes, provide a dictionary:

// SELECT * FROM citizenship WHERE citizenId = 1 AND countryCode = 'FR'
try Citizenship.fetchOne(db, key: ["citizenId": 1, "countryCode": "FR"]) // Citizenship?

PersistableRecord Protocol

GRDB provides two protocols that let adopting types create, update, and delete rows in the database:

protocol MutablePersistableRecord : TableRecord {
    /// Defines the values persisted in the database
    func encode(to container: inout PersistenceContainer)
    
    /// Optional method that lets your adopting type store its rowID upon
    /// successful insertion. Don't call it directly: it is called for you.
    mutating func didInsert(with rowID: Int64, for column: String?)
}

protocol PersistableRecord : MutablePersistableRecord {
    /// Non-mutating version of the optional didInsert(with:for:)
    func didInsert(with rowID: Int64, for column: String?)
}

Yes, two protocols instead of one. Both grant exactly the same advantages. Here is how you pick one or the other:

• If your type is a class, choose PersistableRecord. On top of that, implement didInsert(with:for:) if the database table has an auto-incremented primary key.

• If your type is a struct, and the database table has an auto-incremented primary key, choose MutablePersistableRecord, and implement didInsert(with:for:).

• Otherwise, choose PersistableRecord, and ignore didInsert(with:for:).

The encode(to:) method defines which values (Bool, Int, String, Date, Swift enums, etc.) are assigned to database columns.

The optional didInsert method lets the adopting type store its rowID after successful insertion, and is only useful for tables that have an auto-incremented primary key. It is called from a protected dispatch queue, and serialized with all database updates.

To use the persistable protocols, subclass the Record class, or adopt one of them explicitly. For example:

extension Place : MutablePersistableRecord {
    /// The values persisted in the database
    func encode(to container: inout PersistenceContainer) {
        container["id"] = id
        container["title"] = title
        container["latitude"] = coordinate.latitude
        container["longitude"] = coordinate.longitude
    }
    
    // Update id upon successful insertion:
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

var paris = Place(
    id: nil,
    title: "Paris",
    coordinate: CLLocationCoordinate2D(latitude: 48.8534100, longitude: 2.3488000))

try paris.insert(db)
paris.id   // some value

Persistence containers also accept column enums:

extension Place : MutablePersistableRecord {
    enum Columns: String, ColumnExpression {
        case id, title, latitude, longitude
    }
    
    func encode(to container: inout PersistenceContainer) {
        container[Columns.id] = id
        container[Columns.title] = title
        container[Columns.latitude] = coordinate.latitude
        container[Columns.longitude] = coordinate.longitude
    }
}

When your record type adopts the standard Encodable protocol, you don't have to provide the implementation for encode(to:). See Codable Records for more information:

// That's all
struct Player: Encodable, MutablePersistableRecord {
    var id: Int64?
    var name: String
    var score: Int
    
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

Persistence Methods

Record subclasses and types that adopt PersistableRecord are given default implementations for methods that insert, update, and delete:

// Instance methods
try place.save(db)                     // INSERT or UPDATE
try place.insert(db)                   // INSERT
try place.update(db)                   // UPDATE
try place.update(db, columns: ...)     // UPDATE
try place.updateChanges(db, from: ...) // Maybe UPDATE
try place.updateChanges(db) { ... }    // Maybe UPDATE
try place.updateChanges(db)            // Maybe UPDATE (Record class only)
try place.delete(db)                   // DELETE
try place.exists(db)

// Type methods
try Place.deleteAll(db)                    // DELETE
try Place.deleteAll(db, keys:...)          // DELETE
try Place.deleteOne(db, key:...)           // DELETE

• insert, update, save and delete can throw a DatabaseError.

• update and updateChanges can also throw a PersistenceError, should the update fail because there is no matching row in the database.

  When saving an object that may or may not already exist in the database, prefer the save method:

• save makes sure your values are stored in the database.

  It performs an UPDATE if the record has a non-null primary key, and then, if no row was modified, an INSERT. It directly perfoms an INSERT if the record has no primary key, or a null primary key.

  Despite the fact that it may execute two SQL statements, save behaves as an atomic operation: GRDB won't allow any concurrent thread to sneak in (see concurrency).

• delete returns whether a database row was deleted or not.

All primary keys are supported, including composite primary keys that span several columns, and the implicit rowid primary key.

Customizing the Persistence Methods

Your custom type may want to perform extra work when the persistence methods are invoked.

For example, it may want to have its UUID automatically set before inserting. Or it may want to validate its values before saving.

When you subclass Record, you simply have to override the customized method, and call super:

class Player : Record {
    var uuid: UUID?
    
    override func insert(_ db: Database) throws {
        if uuid == nil {
            uuid = UUID()
        }
        try super.insert(db)
    }
}

If you use the raw PersistableRecord protocol, use one of the special methods performInsert, performUpdate, performSave, performDelete, or performExists:

struct Link : PersistableRecord {
    var url: URL
    
    func insert(_ db: Database) throws {
        try validate()
        try performInsert(db)
    }
    
    func update(_ db: Database, columns: Set<String>) throws {
        try validate()
        try performUpdate(db, columns: columns)
    }
    
    func validate() throws {
        if url.host == nil {
            throw ValidationError("url must be absolute.")
        }
    }
}

☝️ Note: the special methods performInsert, performUpdate, etc. are reserved for your custom implementations. Do not use them elsewhere. Do not provide another implementation for those methods.

☝️ Note: it is recommended that you do not implement your own version of the save method. Its default implementation forwards the job to update or insert: these are the methods that may need customization, not save.

Codable Records

Record types that adopt an archival protocol (Codable, Encodable or Decodable) get free database support just by declaring conformance to the desired record protocols:

// Declare a record...
struct Player: Codable, FetchableRecord, PersistableRecord {
    var name: String
    var score: Int
}

// ...and there you go:
try dbQueue.write { db in
    try Player(name: "Arthur", score: 100).insert(db)
    let players = try Player.fetchAll(db)
}

Codable records encode and decode their properties according to their own implementation of the Encodable and Decodable protocols. Yet databases have specific requirements:

• Properties are always coded according to their preferred database representation, when they have one (all values that adopt the DatabaseValueConvertible protocol).
• You can customize the encoding and decoding of dates and uuids.
• Complex properties (arrays, dictionaries, nested structs, etc.) are stored as JSON.

For more information about Codable records, see:

• JSON Columns
• Date and UUID Coding Strategies
• The userInfo Dictionary
• Tip: Use Coding Keys as Columns

💡 Tip: see the Demo Application for a sample app that uses Codable records.

JSON Columns

When a Codable record contains a property that is not a simple value (Bool, Int, String, Date, Swift enums, etc.), that value is encoded and decoded as a JSON string. For example:

enum AchievementColor: String, Codable {
    case bronze, silver, gold
}

struct Achievement: Codable {
    var name: String
    var color: AchievementColor
}

struct Player: Codable, FetchableRecord, PersistableRecord {
    var name: String
    var score: Int
    var achievements: [Achievement] // stored in a JSON column
}

try! dbQueue.write { db in
    // INSERT INTO player (name, score, achievements)
    // VALUES (
    //   'Arthur',
    //   100,
    //   '[{"color":"gold","name":"Use Codable Records"}]')
    let achievement = Achievement(name: "Use Codable Records", color: .gold)
    let player = Player(name: "Arthur", score: 100, achievements: [achievement])
    try player.insert(db)
}

GRDB uses the standard JSONDecoder and JSONEncoder from Foundation. By default, Data values are handled with the .base64 strategy, Date with the .millisecondsSince1970 strategy, and non conforming floats with the .throw strategy.

You can customize the JSON format by implementing those methods:

protocol FetchableRecord {
    static func databaseJSONDecoder(for column: String) -> JSONDecoder
}

protocol MutablePersistableRecord {
    static func databaseJSONEncoder(for column: String) -> JSONEncoder
}

💡 Tip: Make sure you set the JSONEncoder sortedKeys option, available from iOS 11.0+, macOS 10.13+, and watchOS 4.0+. This option makes sure that the JSON output is stable. This stability is required for Record Comparison to work as expected, and database observation tools such as ValueObservation to accurately recognize changed records.

Date and UUID Coding Strategies

By default, Codable Records encode and decode their Date and UUID properties as described in the general Date and DateComponents and UUID chapters.

To sum up: dates encode themselves in the "YYYY-MM-DD HH:MM:SS.SSS" format, in the UTC time zone, and decode a variety of date formats and timestamps. UUIDs encode themselves as 16-bytes data blobs, and decode both 16-bytes data blobs and strings such as "E621E1F8-C36C-495A-93FC-0C247A3E6E5F".

Those behaviors can be overridden:

protocol FetchableRecord {
    static var databaseDateDecodingStrategy: DatabaseDateDecodingStrategy { get }
}

protocol MutablePersistableRecord {
    static var databaseDateEncodingStrategy: DatabaseDateEncodingStrategy { get }
    static var databaseUUIDEncodingStrategy: DatabaseUUIDEncodingStrategy { get }
}

See DatabaseDateDecodingStrategy, DatabaseDateEncodingStrategy, and DatabaseUUIDEncodingStrategy to learn about all available strategies.

☝️ Note: there is no customization of uuid decoding, because UUID can already decode all its encoded variants (16-bytes blobs, and uuid strings).

The userInfo Dictionary

Your Codable Records can be stored in the database, but they may also have other purposes. In this case, you may need to customize their implementations of Decodable.init(from:) and Encodable.encode(to:), depending on the context.

The standard way to provide such context is the userInfo dictionary. Implement those properties:

protocol FetchableRecord {
    static var databaseDecodingUserInfo: [CodingUserInfoKey: Any] { get }
}

protocol MutablePersistableRecord {
    static var databaseEncodingUserInfo: [CodingUserInfoKey: Any] { get }
}

For example, here is a Player type that customizes its decoding:

// A key that holds a decoder's name
let decoderName = CodingUserInfoKey(rawValue: "decoderName")!

struct Player: FetchableRecord, Decodable {
    init(from decoder: Decoder) throws {
        // Print the decoder name
        let decoderName = decoder.userInfo[decoderName] as? String
        print("Decoded from \(decoderName ?? "unknown decoder")")
        ...
    }
}

You can have a specific decoding from JSON...

// prints "Decoded from JSON"
let decoder = JSONDecoder()
decoder.userInfo = [decoderName: "JSON"]
let player = try decoder.decode(Player.self, from: jsonData)

... and another one from database rows:

extension Player: FetchableRecord {
    static let databaseDecodingUserInfo: [CodingUserInfoKey: Any] = [decoderName: "database row"]
}

// prints "Decoded from database row"
let player = try Player.fetchOne(db, ...)

☝️ Note: make sure the databaseDecodingUserInfo and databaseEncodingUserInfo properties are explicitly declared as [CodingUserInfoKey: Any]. If they are not, the Swift compiler may silently miss the protocol requirement, resulting in sticky empty userInfo.

Tip: Use Coding Keys as Columns

If you declare an explicit CodingKeys enum (what is this?), you can instruct GRDB to use those coding keys as database columns:

struct Player: Codable, FetchableRecord, PersistableRecord {
    var name: String
    var score: Int
    
    // Add ColumnExpression conformance
    private enum CodingKeys: String, CodingKey, ColumnExpression {
        case name, score
    }
}

This allows your record to build requests with the query interface:

extension Player {
    static func filter(name: String) -> QueryInterfaceRequest<Player> {
        return filter(CodingKeys.name == name)
    }
    
    static var maximumScore: QueryInterfaceRequest<Int> {
        return select(max(CodingKeys.score), as: Int.self)
    }
}

Those requests can both fetch...

// Fetch values
try dbQueue.read { db in
    // SELECT * FROM player WHERE name = 'Arthur'
    let arthur = try Player.filter(name: "Arthur").fetchOne(db) // Player?
    
    // SELECT MAX(score) FROM player
    let maxScore = try Player.maximumScore.fetchOne(db)         // Int?
}

... and feed database observation tools such as ValueObservation:

// Observe changes
try ValueObservation
    .trackingOne(Player.maximumScore)
    .start(in: dbQueue) { maxScore: Int? in
        print("The maximum score has changed")
    }

Record Class

Record is a class that is designed to be subclassed. It inherits its features from the FetchableRecord, TableRecord, and PersistableRecord protocols. On top of that, Record instances can compare against previous versions of themselves in order to avoid useless updates.

Record subclasses define their custom database relationship by overriding database methods. For example:

class Place: Record {
    var id: Int64?
    var title: String
    var isFavorite: Bool
    var coordinate: CLLocationCoordinate2D
    
    init(id: Int64?, title: String, isFavorite: Bool, coordinate: CLLocationCoordinate2D) {
        self.id = id
        self.title = title
        self.isFavorite = isFavorite
        self.coordinate = coordinate
        super.init()
    }
    
    /// The table name
    override class var databaseTableName: String {
        return "place"
    }
    
    /// The table columns
    enum Columns: String, ColumnExpression {
        case id, title, favorite, latitude, longitude
    }
    
    /// Creates a record from a database row
    required init(row: Row) {
        id = row[Columns.id]
        title = row[Columns.title]
        isFavorite = row[Columns.favorite]
        coordinate = CLLocationCoordinate2D(
            latitude: row[Columns.latitude],
            longitude: row[Columns.longitude])
        super.init(row: row)
    }
    
    /// The values persisted in the database
    override func encode(to container: inout PersistenceContainer) {
        container[Columns.id] = id
        container[Columns.title] = title
        container[Columns.favorite] = isFavorite
        container[Columns.latitude] = coordinate.latitude
        container[Columns.longitude] = coordinate.longitude
    }
    
    /// Update record ID after a successful insertion
    override func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

Record Comparison

Records that adopt a PersistableRecord protocol can compare against other records, or against previous versions of themselves.

This helps avoiding costly UPDATE statements when a record has not been edited.

• The updateChanges Methods
• The databaseEquals Method
• The databaseChanges and hasDatabaseChanges Methods

The updateChanges Methods

The updateChanges methods perform a database update of the changed columns only (and does nothing if record has no change).

• updateChanges(_:from:)

  This method lets you compare two records:

  if let oldPlayer = try Player.fetchOne(db, key: 42) {
      var newPlayer = oldPlayer
      newPlayer.score = 100
      if try newPlayer.updateChanges(db, from: oldPlayer) {
          print("player was modified, and updated in the database")
      } else {
          print("player was not modified, and database was not hit")
      }
  }

• updateChanges(_:with:)

  This method lets you update a record in place:

  if var player = try Player.fetchOne(db, key: 42) {
      let modified = player.updateChanges(db) {
          $0.score = 100
      }
      if modified {
          print("player was modified, and updated in the database")
      } else {
          print("player was not modified, and database was not hit")
      }
  }

• updateChanges(_:) (Record class only)

  Instances of the Record class are able to compare against themselves, and know if they have changes that have not been saved since the last fetch or saving:

  // Record class only
  if let player = try Player.fetchOne(db, key: 42) {
      player.score = 100
      if try player.updateChanges(db) {
          print("player was modified, and updated in the database")
      } else {
          print("player was not modified, and database was not hit")
      }
  }

The databaseEquals Method

This method returns whether two records have the same database representation:

let oldPlayer: Player = ...
var newPlayer: Player = ...
if newPlayer.databaseEquals(oldPlayer) == false {
    try newPlayer.save(db)
}

☝️ Note: The comparison is performed on the database representation of records. As long as your record type adopts a PersistableRecord protocol, you don't need to care about Equatable.

The databaseChanges and hasDatabaseChanges Methods

databaseChanges(from:) returns a dictionary of differences between two records:

let oldPlayer = Player(id: 1, name: "Arthur", score: 100)
let newPlayer = Player(id: 1, name: "Arthur", score: 1000)
for (column, oldValue) in newPlayer.databaseChanges(from: oldPlayer) {
    print("\(column) was \(oldValue)")
}
// prints "score was 100"

The Record class is able to compare against itself:

// Record class only
let player = Player(id: 1, name: "Arthur", score: 100)
try player.insert(db)
player.score = 1000
for (column, oldValue) in player.databaseChanges {
    print("\(column) was \(oldValue)")
}
// prints "score was 100"

Record instances also have a hasDatabaseChanges property:

// Record class only
player.score = 1000
if player.hasDatabaseChanges {
    try player.save(db)
}

Record.hasDatabaseChanges is false after a Record instance has been fetched or saved into the database. Subsequent modifications may set it, or not: hasDatabaseChanges is based on value comparison. Setting a property to the same value does not set the changed flag:

let player = Player(name: "Barbara", score: 750)
player.hasDatabaseChanges // true

try player.insert(db)
player.hasDatabaseChanges // false

player.name = "Barbara"
player.hasDatabaseChanges // false

player.score = 1000
player.hasDatabaseChanges // true
player.databaseChanges    // ["score": 750]

For an efficient algorithm which synchronizes the content of a database table with a JSON payload, check JSONSynchronization.playground.

Record Customization Options

GRDB records come with many default behaviors, that are designed to fit most situations. Many of those defaults can be customized for your specific needs:

• Customizing the Persistence Methods: define what happens when you call a persistance method such as player.insert(db)
• Conflict Resolution: Run INSERT OR REPLACE queries, and generally define what happens when a persistence method violates a unique index.
• The Implicit RowID Primary Key: all about the special rowid column.
• Columns Selected by a Request: define which columns are selected by requests such as Player.fetchAll(db).
• Beyond FetchableRecord: the FetchableRecord protocol is not the end of the story.

Codable Records have a few extra options:

• JSON Columns: control the format of JSON columns.
• Date and UUID Coding Strategies: control the format of Date and UUID properties in your Codable records.
• The userInfo Dictionary: adapt your Codable implementation for the database.

Conflict Resolution

Insertions and updates can create conflicts: for example, a query may attempt to insert a duplicate row that violates a unique index.

Those conflicts normally end with an error. Yet SQLite let you alter the default behavior, and handle conflicts with specific policies. For example, the INSERT OR REPLACE statement handles conflicts with the "replace" policy which replaces the conflicting row instead of throwing an error.

The five different policies are: abort (the default), replace, rollback, fail, and ignore.

SQLite let you specify conflict policies at two different places:

• In the definition of the database table:

  // CREATE TABLE player (
  //     id INTEGER PRIMARY KEY AUTOINCREMENT,
  //     email TEXT UNIQUE ON CONFLICT REPLACE
  // )
  try db.create(table: "player") { t in
      t.autoIncrementedPrimaryKey("id")
      t.column("email", .text).unique(onConflict: .replace) // <--
  }
  
  // Despite the unique index on email, both inserts succeed.
  // The second insert replaces the first row:
  try db.execute("INSERT INTO player (email) VALUES (?)", arguments: ["[email protected]"])
  try db.execute("INSERT INTO player (email) VALUES (?)", arguments: ["[email protected]"])

• In each modification query:

  // CREATE TABLE player (
  //     id INTEGER PRIMARY KEY AUTOINCREMENT,
  //     email TEXT UNIQUE
  // )
  try db.create(table: "player") { t in
      t.autoIncrementedPrimaryKey("id")
      t.column("email", .text).unique()
  }
  
  // Again, despite the unique index on email, both inserts succeed.
  try db.execute("INSERT OR REPLACE INTO player (email) VALUES (?)", arguments: ["[email protected]"])
  try db.execute("INSERT OR REPLACE INTO player (email) VALUES (?)", arguments: ["[email protected]"])

When you want to handle conflicts at the query level, specify a custom persistenceConflictPolicy in your type that adopts the PersistableRecord protocol. It will alter the INSERT and UPDATE queries run by the insert, update and save persistence methods:

protocol MutablePersistableRecord {
    /// The policy that handles SQLite conflicts when records are
    /// inserted or updated.
    ///
    /// This property is optional: its default value uses the ABORT
    /// policy for both insertions and updates, so that GRDB generate
    /// regular INSERT and UPDATE queries.
    static var persistenceConflictPolicy: PersistenceConflictPolicy { get }
}

struct Player : MutablePersistableRecord {
    static let persistenceConflictPolicy = PersistenceConflictPolicy(
        insert: .replace,
        update: .replace)
}

// INSERT OR REPLACE INTO player (...) VALUES (...)
try player.insert(db)

☝️ Note: the ignore policy does not play well at all with the didInsert method which notifies the rowID of inserted records. Choose your poison:

• if you specify the ignore policy in the database table definition, don't implement the didInsert method: it will be called with some random id in case of failed insert.
• if you specify the ignore policy at the query level, the didInsert method is never called.

☝️ Note: The replace policy may have to delete rows so that inserts and updates can succeed. Those deletions are not reported to transaction observers (this might change in a future release of SQLite).

The Implicit RowID Primary Key

All SQLite tables have a primary key. Even when the primary key is not explicit:

// No explicit primary key
try db.create(table: "event") { t in
    t.column("message", .text)
    t.column("date", .datetime)
}

// No way to define an explicit primary key
try db.create(virtualTable: "book", using: FTS4()) { t in
    t.column("title")
    t.column("author")
    t.column("body")
}

The implicit primary key is stored in the hidden column rowid. Hidden means that SELECT * does not select it, and yet it can be selected and queried: SELECT *, rowid ... WHERE rowid = 1.

Some GRDB methods will automatically use this hidden column when a table has no explicit primary key:

// SELECT * FROM event WHERE rowid = 1
let event = try Event.fetchOne(db, key: 1)

// DELETE FROM book WHERE rowid = 1
try Book.deleteOne(db, key: 1)

Exposing the RowID Column

By default, a record type that wraps a table without any explicit primary key doesn't know about the hidden rowid column.

Without primary key, records don't have any identity, and the persistence method can behave in undesired fashion: update() throws errors, save() always performs insertions and may break constraints, exists() is always false.

When SQLite won't let you provide an explicit primary key (as in full-text tables, for example), you may want to make your record type fully aware of the hidden rowid column:

1. Have the databaseSelection static property (from the TableRecord protocol) return the hidden rowid column:

   struct Event : TableRecord {
       static let databaseSelection: [SQLSelectable] = [AllColumns(), Column.rowID]
   }
   
   // When you subclass Record, you need an override:
   class Book : Record {
       override class var databaseSelection: [SQLSelectable] {
           return [AllColums(), Column.rowID]
       }
   }

   GRDB will then select the rowid column by default:

   // SELECT *, rowid FROM event
   let events = try Event.fetchAll(db)

2. Have init(row:) from the FetchableRecord protocol consume the "rowid" column:

   struct Event : FetchableRecord {
       var id: Int64?
       
       init(row: Row) {
           id = row[Column.rowID]
       }
   }

   Your fetched records will then know their ids:

   let event = try Event.fetchOne(db)!
   event.id // some value

3. Encode the rowid in encode(to:), and keep it in the didInsert(with:for:) method (both from the PersistableRecord and MutablePersistableRecord protocols):

   struct Event : MutablePersistableRecord {
       var id: Int64?
       
       func encode(to container: inout PersistenceContainer) {
           container[Column.rowID] = id
           container["message"] = message
           container["date"] = date
       }
       
       mutating func didInsert(with rowID: Int64, for column: String?) {
           id = rowID
       }
   }

   You will then be able to track your record ids, update them, or check for their existence:

   let event = Event(message: "foo", date: Date())
   
   // Insertion sets the record id:
   try event.insert(db)
   event.id // some value
   
   // Record can be updated:
   event.message = "bar"
   try event.update(db)
   
   // Record knows if it exists:
   event.exists(db) // true

Beyond FetchableRecord

Some GRDB users eventually discover that the FetchableRecord protocol does not fit all situations. Use cases that are not well handled by FetchableRecord include:

• Your application needs polymorphic row decoding: it decodes some type or another, depending on the values contained in a database row.

• Your application needs to decode rows with a context: each decoded value should be initialized with some extra value that does not come from the database.

• Your application needs a record type that supports untrusted databases, and may fail at decoding database rows (throw an error when a row contains invalid values).

Since those use cases are not well handled by FetchableRecord, don't try to implement them on top of this protocol: you'll just fight the framework.

Instead, please have a look at the CustomizedDecodingOfDatabaseRows playground. You'll run some sample code, and learn how to escape FetchableRecord when you need. And remember that leaving FetchableRecord will not deprive you of query interface requests and generally all SQL generation features of the TableRecord and PersistableRecord protocols.

Examples of Record Definitions

We will show below how to declare a record type for the following database table:

try dbQueue.write { db in
    try db.create(table: "place") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("title", .text).notNull()
        t.column("favorite", .boolean).notNull().defaults(to: false)
        t.column("longitude", .double).notNull()
        t.column("latitude", .double).notNull()
    }
}

Each one of the three examples below is correct. You will pick one or the other depending on your personal preferences and the requirements of your application:

struct Place: Codable {
    var id: Int64?
    var title: String
    var favorite: Bool
    var latitude: CLLocationDegrees
    var longitude: CLLocationDegrees
    
    var coordinate: CLLocationCoordinate2D {
        get {
            return CLLocationCoordinate2D(
                latitude: latitude,
                longitude: longitude)
        }
        set {
            latitude = newValue.latitude
            longitude = newValue.longitude
        }
    }
}

// SQL generation
extension Place: TableRecord { }

// Fetching methods
extension Place: FetchableRecord { }

// Persistence methods
extension Place: MutablePersistableRecord {
    /// Update record ID after a successful insertion
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

struct Place {
    var id: Int64?
    var title: String
    var isFavorite: Bool
    var coordinate: CLLocationCoordinate2D
}

// SQL generation
extension Place: TableRecord {
    /// The table columns
    enum Columns: String, ColumnExpression {
        case id, title, favorite, latitude, longitude
    }
}

// Fetching methods
extension Place: FetchableRecord {
    /// Creates a record from a database row
    init(row: Row) {
        id = row[Columns.id]
        title = row[Columns.title]
        isFavorite = row[Columns.favorite]
        coordinate = CLLocationCoordinate2D(
            latitude: row[Columns.latitude],
            longitude: row[Columns.longitude])
    }
}

// Persistence methods
extension Place: MutablePersistableRecord {
    /// The values persisted in the database
    func encode(to container: inout PersistenceContainer) {
        container[Columns.id] = id
        container[Columns.title] = title
        container[Columns.favorite] = isFavorite
        container[Columns.latitude] = coordinate.latitude
        container[Columns.longitude] = coordinate.longitude
    }
    
    /// Update record ID after a successful insertion
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

class Place: Record {
    var id: Int64?
    var title: String
    var isFavorite: Bool
    var coordinate: CLLocationCoordinate2D
    
    init(id: Int64?, title: String, isFavorite: Bool, coordinate: CLLocationCoordinate2D) {
        self.id = id
        self.title = title
        self.isFavorite = isFavorite
        self.coordinate = coordinate
        super.init()
    }
    
    /// The table name
    override class var databaseTableName: String {
        return "place"
    }
    
    /// The table columns
    enum Columns: String, ColumnExpression {
        case id, title, favorite, latitude, longitude
    }
    
    /// Creates a record from a database row
    required init(row: Row) {
        id = row[Columns.id]
        title = row[Columns.title]
        isFavorite = row[Columns.favorite]
        coordinate = CLLocationCoordinate2D(
            latitude: row[Columns.latitude],
            longitude: row[Columns.longitude])
        super.init(row: row)
    }
    
    /// The values persisted in the database
    override func encode(to container: inout PersistenceContainer) {
        container[Columns.id] = id
        container[Columns.title] = title
        container[Columns.favorite] = isFavorite
        container[Columns.latitude] = coordinate.latitude
        container[Columns.longitude] = coordinate.longitude
    }
    
    /// Update record ID after a successful insertion
    override func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

List of Record Methods

This is the list of record methods, along with their required protocols. The Record class adopts all these protocols, and adds a few extra methods.