A toolkit for SQLite databases, with a focus on application development
Proudly serving the community since 2015

Latest release: April 21, 2024 • version 6.27.0 • CHANGELOG • Migrating From GRDB 5 to GRDB 6

Requirements: iOS 11.0+ / macOS 10.13+ / tvOS 11.0+ / watchOS 4.0+ • SQLite 3.19.3+ • Swift 5.7+ / Xcode 14+

Contact:

• Release announcements and usage tips: follow @groue on Twitter, @groue@hachyderm.io on Mastodon.
• 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 GitHub discussions, or the GRDB forums.

Use this library to save your application’s permanent data into SQLite databases. It comes with built-in tools that address common needs:

• SQL Generation

  Enhance 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.

• Database Observation

  Get notifications when database values are modified.

• Robust Concurrency

  Multi-threaded applications can efficiently use their databases, including WAL databases that support concurrent reads and writes.

• Migrations

  Evolve the schema of your database as you ship new versions of your application.

• Leverage your SQLite skills

  Not all developers need advanced SQLite features. But when you do, GRDB is as sharp as you want it to be. Come with your SQL and SQLite skills, or learn new ones as you go!

Usage • Documentation • Installation • FAQ

import GRDB

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

// 2. Define the database schema
try dbQueue.write { db in
    try db.create(table: "player") { t in
        t.primaryKey("id", .text)
        t.column("name", .text).notNull()
        t.column("score", .integer).notNull()
    }
}

// 3. Define a record type
struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: String
    var name: String
    var score: Int
}

// 4. Write and read in the database
try dbQueue.write { db in
    try Player(id: "1", name: "Arthur", score: 100).insert(db)
    try Player(id: "2", name: "Barbara", score: 1000).insert(db)
}

let players: [Player] = try dbQueue.read { db in
    try Player.fetchAll(db)
}

try dbQueue.write { db in
    try db.execute(sql: """
        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(sql: """
        INSERT INTO place (title, favorite, latitude, longitude)
        VALUES (?, ?, ?, ?)
        """, arguments: ["Paris", true, 48.85341, 2.3488])
    
    let parisId = db.lastInsertedRowID
    
    // Avoid SQL injection with SQL interpolation
    try db.execute(literal: """
        INSERT INTO place (title, favorite, latitude, longitude)
        VALUES (\("King's Cross"), \(true), \(51.52151), \(-0.12763))
        """)
}

try dbQueue.read { db in
    // Fetch database rows
    let rows = try Row.fetchCursor(db, sql: "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, sql: "SELECT COUNT(*) FROM place")! // Int
    let placeTitles = try String.fetchAll(db, sql: "SELECT title FROM place") // [String]
}

let placeCount = try dbQueue.read { db in
    try Int.fetchOne(db, sql: "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.read { db in
    // Place
    let paris = try Place.find(db, id: 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, sql: "SELECT * FROM place")
}

// Define the observed value
let observation = ValueObservation.tracking { db in
    try Place.fetchAll(db)
}

// Start observation
let cancellable = observation.start(
    in: dbQueue,
    onError: { error in ... },
    onChange: { (places: [Place]) in print("Fresh places: \(places)") })

// Combine
let cancellable = observation.publisher(in: dbQueue).sink(
    receiveCompletion: { completion in ... },
    receiveValue: { (places: [Place]) in print("Fresh places: \(places)") })

// RxSwift
let disposable = observation.rx.observe(in: dbQueue).subscribe(
    onNext: { (places: [Place]) in print("Fresh places: \(places)") },
    onError: { error in ... })

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

• Demo Applications: Three flavors: vanilla UIKit, Combine + SwiftUI, and Async/Await + SwiftUI.
• FAQ

• 📖 GRDB Reference

• Installation
• Database Connections: Connect to SQLite databases

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

• Records: Fetching and persistence methods for your custom structs and class hierarchies
• Query Interface: A swift way to generate SQL • create tables • requests • associations between record types

• Migrations: Transform your database as your application evolves.
• Full-Text Search: Perform efficient and customizable full-text searches.
• Database Observation: Observe database changes and transactions.
• Encryption: Encrypt your database with SQLCipher.
• Backup: Dump the content of a database to another.
• Interrupt a Database: Abort any pending database operation.
• Sharing a Database: How to share an SQLite database between multiple processes - recommendations for App Group containers, App Extensions, App Sandbox, and file coordination.

• Concurrency: How to access databases in a multi-threaded application.
• Combine: Access and observe the database with Combine publishers.
• Avoiding SQL Injection
• Error Handling
• Unicode
• Memory Management
• Data Protection
• 💡 Migrating From GRDB 5 to GRDB 6
• 💡 Why Adopt GRDB?
• 💡 Recommended Practices for Designing Record Types

• GRDBQuery: Access and observe the database from your SwiftUI views.
• GRDBSnapshotTesting: Test your database.

FAQ

Sample Code

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.

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

pod 'GRDB.swift'

GRDB can be installed as a framework, or a static library.

The Swift Package Manager automates the distribution of Swift code. To use GRDB with SPM, add a dependency to https://github.com/groue/GRDB.swift.git

GRDB offers two libraries, GRDB and GRDB-dynamic. Pick only one. When in doubt, prefer GRDB. The GRDB-dynamic library can reveal useful if you are going to link it with multiple targets within your app and only wish to link to a shared, dynamic framework once. See How to link a Swift Package as dynamic for more information.

Note: Linux is not currently supported.

Warning: Due to an Xcode bug, you will get "No such module 'CSQLite'" errors when you want to embed the GRDB package in other targets than the main application (watch extensions, for example). UI and Unit testing targets are OK, though. See #642 for more information.

Carthage is unsupported. For some context about this decision, see #433.

1. Download a copy of GRDB, or clone its repository and make sure you checkout the latest tagged version.

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

3. Add the GRDB target in the Target Dependencies section of the Build Phases tab of your application target (extension target for WatchOS).

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

💡 Tip: see the Demo Applications for examples of such integration.

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.

For more information and tips when opening connections, see Database Connections.

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, NSDecimalNumber, and Decimal
  • Swift enums
  • DatabaseValueConvertible: the protocol for custom value types
• Transactions and Savepoints
• SQL Interpolation

Advanced topics:

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

Once granted with a database connection, the execute(sql:arguments:) 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(sql: """
        CREATE TABLE player (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            score INT)
        """)
    
    try db.execute(
        sql: "INSERT INTO player (name, score) VALUES (?, ?)",
        arguments: ["Barbara", 1000])
    
    try db.execute(
        sql: "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.

You can also embed query arguments right into your SQL queries, with execute(literal:), as in the example below. See SQL Interpolation for more details.

try dbQueue.write { db in
    let name = "O'Brien"
    let score = 550
    try db.execute(literal: """
        INSERT INTO player (name, score) VALUES (\(name), \(score))
        """)
}

Never ever embed values directly in your raw SQL strings. See Avoiding SQL Injection for more information:

// WRONG: don't embed values in raw SQL strings
let id = 123
let name = textField.text
try db.execute(
    sql: "UPDATE player SET name = '\(name)' WHERE id = \(id)")

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

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

// CORRECT: use SQL Interpolation
try db.execute(
    literal: "UPDATE player SET name = \(name) WHERE id = \(id)")

Join multiple statements with a semicolon:

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

try db.execute(literal: """
    INSERT INTO player (name, score) VALUES (\("Arthur"), \(750));
    INSERT INTO player (name, score) VALUES (\("Barbara"), \(1000));
    """)

When you want to make sure that a single statement is executed, use a prepared Statement.

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

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

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

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

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, sql: "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, sql: "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, sql: "SELECT * FROM wine")
}

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

Throughout GRDB, you can always fetch cursors, arrays, sets, 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.fetchSet(...)    // Set<Row>
try Row.fetchOne(...)    // Row?

• fetchCursor returns a cursor over fetched values:

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

• fetchAll returns an array:

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

• fetchSet returns a set:

  let names = try String.fetchSet(db, sql: "SELECT ...") // Set<String>

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

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

All those fetching methods require an SQL string that contains a single SQL statement. When you want to fetch from multiple statements joined with a semicolon, iterate the multiple prepared statements found in the SQL string.

📖 Cursor

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

The fetchAll() and fetchSet() methods return regular Swift array and sets, that you iterate like all other arrays and sets:

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

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

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

• 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 and sets 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 and sets can be iterated many times.

• Cursors iterate database results in a lazy fashion, and don't consume much memory. Arrays and sets 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 and sets that have to take the time to copy database values. If you look after extra performance, you may prefer cursors.

• Cursors can feed Swift collections.

  You will most of the time use fetchAll or fetchSet when you want an array or a set. For more specific needs, you may prefer one of the initializers below. All of them accept an extra optional minimumCapacity argument which helps optimizing your app when you have an idea of the number of elements in a cursor (the built-in fetchAll and fetchSet do not perform such an optimization).

  Arrays and all types conforming to RangeReplaceableCollection:

  // [String]
  let cursor = try String.fetchCursor(db, ...)
  let array = try Array(cursor)

  Sets:

  // Set<Int>
  let cursor = try Int.fetchCursor(db, ...)
  let set = try Set(cursor)

  Dictionaries:

  // [Int64: [Player]]
  let cursor = try Player.fetchCursor(db)
  let dictionary = try Dictionary(grouping: cursor, by: { $0.teamID })
  
  // [Int64: Player]
  let cursor = try Player.fetchCursor(db).map { ($0.id, $0) }
  let dictionary = try Dictionary(uniqueKeysWithValues: cursor)

• 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, sql: "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, sql: "SELECT latitude, longitude FROM place")
      .map { row in
          CLLocationCoordinate2D(latitude: row[0], longitude: row[1])
      }

• Cursors are not Swift sequences. That's because Swift sequences can't handle iteration errors, when reading SQLite results may fail at any time.

• 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(sql: "DELETE ...")
    }

  • Don't turn a cursor of Row into an array or a set. You would not get the distinct rows you expect. To get a array of rows, use Row.fetchAll(...). To get a set of rows, use Row.fetchSet(...). 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.

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

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

try dbQueue.read { db in
    try Row.fetchCursor(db, sql: "SELECT ...", arguments: ...) // A Cursor of Row
    try Row.fetchAll(db, sql: "SELECT ...", arguments: ...)    // [Row]
    try Row.fetchSet(db, sql: "SELECT ...", arguments: ...)    // Set<Row>
    try Row.fetchOne(db, sql: "SELECT ...", arguments: ...)    // Row?
    
    let rows = try Row.fetchCursor(db, sql: "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, sql: "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,
    sql: "SELECT * FROM player WHERE name = ?",
    arguments: ["Arthur"])

let rows = try Row.fetchAll(db,
    sql: "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:

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

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, sql: "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, sql: "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, sql: "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, sql: "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, sql: "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, sql: "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 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, sql: "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

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, sql: "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.

📖 DatabaseValueConvertible

Instead of rows, you can directly fetch values. There are many supported value types (Bool, Int, String, Date, Swift enums, etc.).

Like rows, fetch values as cursors, arrays, sets, 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, sql: "SELECT ...", arguments: ...) // A Cursor of Int
    try Int.fetchAll(db, sql: "SELECT ...", arguments: ...)    // [Int]
    try Int.fetchSet(db, sql: "SELECT ...", arguments: ...)    // Set<Int>
    try Int.fetchOne(db, sql: "SELECT ...", arguments: ...)    // Int?
    
    let maxScore = try Int.fetchOne(db, sql: "SELECT MAX(score) FROM player") // Int?
    let names = try String.fetchAll(db, sql: "SELECT name FROM player")       // [String]
}

Int.fetchOne returns nil in two cases: either the SELECT statement yielded no row, or one row with a NULL value:

// No row:
try Int.fetchOne(db, sql: "SELECT 42 WHERE FALSE") // nil

// One row with a NULL value:
try Int.fetchOne(db, sql: "SELECT NULL")           // nil

// One row with a non-NULL value:
try Int.fetchOne(db, sql: "SELECT 42")             // 42

For requests which may contain NULL, fetch optionals:

try dbQueue.read { db in
    try Optional<Int>.fetchCursor(db, sql: "SELECT ...", arguments: ...) // A Cursor of Int?
    try Optional<Int>.fetchAll(db, sql: "SELECT ...", arguments: ...)    // [Int?]
    try Optional<Int>.fetchSet(db, sql: "SELECT ...", arguments: ...)    // Set<Int?>
}

💡 Tip: One advanced use case, when you fetch one value, is to distinguish the cases of a statement that yields no row, or one row with a NULL value. To do so, use Optional<Int>.fetchOne, which returns a double optional Int??:

// No row:
try Optional<Int>.fetchOne(db, sql: "SELECT 42 WHERE FALSE") // .none
// One row with a NULL value:
try Optional<Int>.fetchOne(db, sql: "SELECT NULL")           // .some(.none)
// One row with a non-NULL value:
try Optional<Int>.fetchOne(db, sql: "SELECT 42")             // .some(.some(42))

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

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, Decimal, 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(
    sql: "INSERT INTO link (url, verified) VALUES (?, ?)",
    arguments: [url, verified])

Values can be extracted from rows:

let rows = try Row.fetchCursor(db, sql: "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, sql: "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 suits the BLOB SQLite columns. It can be stored and fetched from the database just like other values:

let rows = try Row.fetchCursor(db, sql: "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() {
    try row.withUnsafeData(name: "data") { (data: Data?) in
        ...
    }
}

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 can be stored and fetched from the database.

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

¹ Missing components are assumed to be zero. Dates are stored and read in the UTC time zone, unless the format is followed by a timezone indicator ⁽²⁾.

² This format may be optionally followed by a timezone indicator of the form [+-]HH:MM or just Z.

³ 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.

Warning: the range of valid years in the SQLite date formats is 0000-9999. You will need to pick another date format when your application needs to process years outside of this range. See the following chapters.

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

try db.execute(
    sql: "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

Warning: the range of valid years in the SQLite date format is 0000-9999. You will experience problems with years outside of this range, such as decoding errors, or invalid date computations with SQLite date & time functions.

Some applications may prefer another date format:

• Some may prefer ISO-8601, with a T separator.
• Some may prefer ISO-8601, with a time zone.
• Some may need to store years beyond the 0000-9999 range.
• Some may need sub-millisecond precision.
• Some may need exact Date roundtrip.
• Etc.

You should think twice before choosing a different date format:

• ISO-8601 is about exchange and communication, when SQLite is about storage and data manipulation. Sharing the same representation in your database and in JSON files only provides a superficial convenience, and should be the least of your priorities. Don't store dates as ISO-8601 without understanding what you lose. For example, ISO-8601 time zones forbid database-level date comparison.
• Sub-millisecond precision and exact Date roundtrip are not as obvious needs as it seems at first sight. Dates generally don't precisely roundtrip as soon as they leave your application anyway, because the other systems your app communicates with use their own date representation (the Android version of your app, the server your application is talking to, etc.) On top of that, Date comparison is at least as hard and nasty as floating point comparison.

The customization of date format is explicit. For example:

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

if let row = try Row.fetchOne(db, ...) {
    let timeInterval: TimeInterval = row["creationDate"]
    let creationDate = Date(timeIntervalSinceReferenceDate: timeInterval)
}

See also Codable Records for more date customization options, and DatabaseValueConvertible if you want to define a Date-wrapping type with customized database representation.

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.

Warning: the range of valid years is 0000-9999. You will experience problems with years outside of this range, such as decoding errors, or invalid date computations with SQLite date & time functions. See Date for more information.

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(
    sql: "INSERT INTO player (birthDate, ...) VALUES (?, ...)",
    arguments: [dbComponents, ...])

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

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

Here is how GRDB supports the various data types supported by SQLite:

• All three types can decode database integers and doubles:

  let number = try NSNumber.fetchOne(db, sql: "SELECT 10")            // NSNumber
  let number = try NSDecimalNumber.fetchOne(db, sql: "SELECT 1.23")   // NSDecimalNumber
  let number = try Decimal.fetchOne(db, sql: "SELECT -100")           // Decimal

• All three types decode database strings as decimal numbers:

  let number = try NSNumber.fetchOne(db, sql: "SELECT '10'")          // NSDecimalNumber (sic)
  let number = try NSDecimalNumber.fetchOne(db, sql: "SELECT '1.23'") // NSDecimalNumber
  let number = try Decimal.fetchOne(db, sql: "SELECT '-100'")         // Decimal

• NSNumber and NSDecimalNumber send 64-bit signed integers and doubles in the database:

  // INSERT INTO transfer VALUES (10)
  try db.execute(sql: "INSERT INTO transfer VALUES (?)", arguments: [NSNumber(value: 10)])
  
  // INSERT INTO transfer VALUES (10.0)
  try db.execute(sql: "INSERT INTO transfer VALUES (?)", arguments: [NSNumber(value: 10.0)])
  
  // INSERT INTO transfer VALUES (10)
  try db.execute(sql: "INSERT INTO transfer VALUES (?)", arguments: [NSDecimalNumber(string: "10.0")])
  
  // INSERT INTO transfer VALUES (10.5)
  try db.execute(sql: "INSERT INTO transfer VALUES (?)", arguments: [NSDecimalNumber(string: "10.5")])

  Warning: since SQLite does not support decimal numbers, sending a non-integer NSDecimalNumber can result in a loss of precision during the conversion to double.

  Instead of sending non-integer NSDecimalNumber to the database, you may prefer:

  • Send Decimal instead (those store decimal strings in the database).
  • Send integers instead (for example, store amounts of cents instead of amounts of Euros).

• Decimal sends decimal strings in the database:

  // INSERT INTO transfer VALUES ('10')
  try db.execute(sql: "INSERT INTO transfer VALUES (?)", arguments: [Decimal(10)])
  
  // INSERT INTO transfer VALUES ('10.5')
  try db.execute(sql: "INSERT INTO transfer VALUES (?)", arguments: [Decimal(string: "10.5")!])

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 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(
    sql: "INSERT INTO wine (grape, color) VALUES (?, ?)",
    arguments: [Grape.merlot, Color.red])

// Read
let rows = try Row.fetchCursor(db, sql: "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, sql: "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
}

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

📖 DatabaseFunction

A 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.

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())
}

You make a function available to a database connection through its configuration:

var config = Configuration()
config.prepareDatabase { db in
    db.add(function: reverse)
}
let dbQueue = try DatabaseQueue(path: dbPath, configuration: config)

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

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)
}
db.add(function: averageOf)

// 2.0
try Double.fetchOne(db, sql: "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)
}
db.add(function: sqrt)

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

Use custom functions in the query interface:

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

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

📖 DatabaseFunction, DatabaseAggregate

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? {
        maxLength
    }
}

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

Like custom SQL Functions, you make an aggregate function available to a database connection through its configuration:

var config = Configuration()
config.prepareDatabase { db in
    db.add(function: maxLength)
}
let dbQueue = try DatabaseQueue(path: dbPath, configuration: config)

try dbQueue.read { db in
    // Some Int
    try Int.fetchOne(db, sql: "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?

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(...)

For more information, see tableExists(_:) and related methods.

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 GRDB module, regardless of the underlying SQLite implementation (system SQLite, SQLCipher, or custom SQLite build):

import GRDB

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.makeStatement(sql: "SELECT ...")
    let sqliteStatement = statement.sqliteStatement
}

Note

• 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.

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, id: 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 Recommended Practices for Designing Record Types Guide.

💡 Tip: see the Demo Applications for sample apps 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
  • Persistence Methods and the RETURNING clause
  • Persistence Callbacks
• Identifiable Records
• Codable Records
• Record Class
• Record Comparison
• Record Customization Options
• Record Timestamps and Transaction Date

Records in a Glance

• Examples of Record Definitions
• List of Record Methods

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

let player = Player(name: "Arthur", email: "arthur@example.com")
try player.insert(db)

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

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

let arthur = try Player.fetchOne(db,            // Player?
    sql: "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, id: "ES")  // Country?
let italy = try Country.find(db, id: "IT")      // 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.

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

var player: Player = ...
player.score = 1000
try player.update(db)

It is possible to avoid useless updates:

// does not hit the database if score has not changed
try player.updateChanges(db) {
    $0.score = 1000
}

See the query interface for batch updates:

try Player
    .filter(Column("team") == "red")
    .updateAll(db, Column("score") += 1)

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

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

let player: Player = ...
try player.delete(db)

You can also delete by primary key, unique key, or perform batch deletes (see Delete Requests):

try Player.deleteOne(db, id: 1)
try Player.deleteOne(db, key: ["email": "arthur@example.com"])
try Country.deleteAll(db, ids: ["FR", "US"])
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. Batch deletes are available on the TableRecord protocol.

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
• Identifiable Records
• Codable Records
• Record Class
• Record Comparison
• Record Customization Options
• Examples of Record Definitions
• List of Record Methods

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.

  struct Place: FetchableRecord { ... }
  let places = try dbQueue.read { db in
      try Place.fetchAll(db, sql: "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 { ... }
  let placeCount = try dbQueue.read { db in
      // Generates and runs `SELECT COUNT(*) FROM place`
      try Place.fetchCount(db)
  }

  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(id: 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, id: 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

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

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

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, sql: "SELECT ...", arguments:...) // A Cursor of Place
try Place.fetchAll(db, sql: "SELECT ...", arguments:...)    // [Place]
try Place.fetchSet(db, sql: "SELECT ...", arguments:...)    // Set<Place>
try Place.fetchOne(db, sql: "SELECT ...", arguments:...)    // Place?

See fetching methods for information about the fetchCursor, fetchAll, fetchSet 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

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: [any 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 { "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 deal with primary and unique keys: see Fetching by Key and Testing for Record Existence.

📖 EncodableRecord, MutablePersistableRecord, PersistableRecord

GRDB record types can create, update, and delete rows in the database.

Those abilities are granted by three protocols:

// Defines how a record encodes itself into the database
protocol EncodableRecord {
    /// Defines the values persisted in the database
    func encode(to container: inout PersistenceContainer) throws
}

// Adds persistence methods
protocol MutablePersistableRecord: TableRecord, EncodableRecord {
    /// 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(_ inserted: InsertionSuccess)
}

// Adds immutability
protocol PersistableRecord: MutablePersistableRecord {
    /// Non-mutating version of the optional didInsert(_:)
    func didInsert(_ inserted: InsertionSuccess)
}

Yes, three protocols instead of one. Here is how you pick one or the other:

• If your type is a class, choose PersistableRecord. On top of that, implement didInsert(_:) 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(_:).

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

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 auto-incremented id upon successful insertion
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.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
    
    // Update auto-incremented id upon successful insertion
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

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

// INSERT
try place.insert(db)
let insertedPlace = try place.inserted(db) // non-mutating

// UPDATE
try place.update(db)
try place.update(db, columns: ["title"])

// Maybe UPDATE
try place.updateChanges(db, from: otherPlace)
try place.updateChanges(db) { $0.isFavorite = true }
try place.updateChanges(db) // Record class only

// INSERT or UPDATE
try place.save(db)
let savedPlace = place.saved(db) // non-mutating

// UPSERT
try place.upsert(db)
let insertedPlace = place.upsertAndFetch(db)

// DELETE
try place.delete(db)

// EXISTENCE CHECK
let exists = try place.exists(db)

See Upsert below for more information about upserts.

The TableRecord protocol comes with batch operations:

// UPDATE
try Place.updateAll(db, ...)

// DELETE
try Place.deleteAll(db)
try Place.deleteAll(db, ids:...)
try Place.deleteAll(db, keys:...)
try Place.deleteOne(db, id:...)
try Place.deleteOne(db, key:...)

For more information about batch updates, see Update Requests.

• All persistence methods can throw a DatabaseError.

• update and updateChanges throw RecordError if the database does not contain any row for the primary key of the record.

• 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 performs an INSERT if the record has no primary key, or a null primary key.

• delete and deleteOne returns whether a database row was deleted or not. deleteAll returns the number of deleted rows. updateAll returns the number of updated rows. updateChanges returns whether a database row was updated or not.

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

To customize persistence methods, you provide Persistence Callbacks, described below. Do not attempt at overriding the ready-made persistence methods.

UPSERT is an SQLite feature that causes an INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint (primary key or unique index).

Note: Upsert apis are available from SQLite 3.35.0+: iOS 15.0+, macOS 12.0+, tvOS 15.0+, watchOS 8.0+, or with a custom SQLite build or SQLCipher.

Note: With regard to persistence callbacks, an upsert behaves exactly like an insert. In particular: the aroundInsert(_:) and didInsert(_:) callbacks reports the rowid of the inserted or updated row; willUpdate, aroundUdate, didUdate are not called.

PersistableRecord provides three upsert methods:

• upsert(_:)

  Inserts or updates a record.

  The upsert behavior is triggered by a violation of any uniqueness constraint on the table (primary key or unique index). In case of conflict, all columns but the primary key are overwritten with the inserted values:

  struct Player: Encodable, PersistableRecord {
      var id: Int64
      var name: String
      var score: Int
  }
  
  // INSERT INTO player (id, name, score)
  // VALUES (1, 'Arthur', 1000)
  // ON CONFLICT DO UPDATE SET
  //   name = excluded.name,
  //   score = excluded.score
  let player = Player(id: 1, name: "Arthur", score: 1000)
  try player.upsert(db)

• upsertAndFetch(_:onConflict:doUpdate:) (requires FetchableRecord conformance)

  Inserts or updates a record, and returns the upserted record.

  The onConflict and doUpdate arguments let you further control the upsert behavior. Make sure you check the SQLite UPSERT documentation for detailed information.

  • onConflict: the "conflict target" is the array of columns in the uniqueness constraint (primary key or unique index) that triggers the upsert.

    If empty (the default), all uniqueness constraint are considered.

  • doUpdate: a closure that returns columns assignments to perform in case of conflict. Other columns are overwritten with the inserted values.

    By default, all inserted columns but the primary key and the conflict target are overwritten.

  In the example below, we upsert the new vocabulary word "jovial". It is inserted if that word is not already in the dictionary. Otherwise, count is incremented, isTainted is not overwritten, and kind is overwritten:

  // CREATE TABLE vocabulary(
  //   word TEXT NOT NULL PRIMARY KEY,
  //   kind TEXT NOT NULL,
  //   isTainted BOOLEAN DEFAULT 0,
  //   count INT DEFAULT 1))
  struct Vocabulary: Encodable, PersistableRecord {
      var word: String
      var kind: String
      var isTainted: Bool
  }
  
  // INSERT INTO vocabulary(word, kind, isTainted)
  // VALUES('jovial', 'adjective', 0)
  // ON CONFLICT(word) DO UPDATE SET \
  //   count = count + 1,   -- on conflict, count is incremented
  //   kind = excluded.kind -- on conflict, kind is overwritten
  // RETURNING *
  let vocabulary = Vocabulary(word: "jovial", kind: "adjective", isTainted: false)
  let upserted = try vocabulary.upsertAndFetch(
      db, onConflict: ["word"],
      doUpdate: { _ in
          [Column("count") += 1,            // on conflict, count is incremented
           Column("isTainted").noOverwrite] // on conflict, isTainted is NOT overwritten
      })

  The doUpdate closure accepts an excluded TableAlias argument that refers to the inserted values that trigger the conflict. You can use it to specify an explicit overwrite, or to perform a computation. In the next example, the upsert keeps the maximum date in case of conflict:

  // INSERT INTO message(id, text, date)
  // VALUES(...)
  // ON CONFLICT DO UPDATE SET \
  //   text = excluded.text,
  //   date = MAX(date, excluded.date)
  // RETURNING *
  let upserted = try message.upsertAndFetch(doUpdate: { excluded in
      // keep the maximum date in case of conflict
      [Column("date").set(to: max(Column("date"), excluded["date"]))]
  })

• upsertAndFetch(_:as:onConflict:doUpdate:) (does not require FetchableRecord conformance)

  This method is identical to upsertAndFetch(_:onConflict:doUpdate:) described above, but you can provide a distinct FetchableRecord record type as a result, in order to specify the returned columns.

SQLite is able to return values from a inserted, updated, or deleted row, with the RETURNING clause.

Note: Support for the RETURNING clause is available from SQLite 3.35.0+: iOS 15.0+, macOS 12.0+, tvOS 15.0+, watchOS 8.0+, or with a custom SQLite build or SQLCipher.

The RETURNING clause helps dealing with database features such as auto-incremented ids, default values, and generated columns. You can, for example, insert a few columns and fetch the default or generated ones in one step.

GRDB uses the RETURNING clause in all persistence methods that contain AndFetch in their name.

For example, given a database table with an auto-incremented primary key and a default score:

try dbQueue.write { db in
    try db.execute(sql: """
        CREATE TABLE player(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          score INTEGER NOT NULL DEFAULT 1000)
        """)
}

You can define a record type with full database information, and another partial record type that deals with a subset of columns:

// A player with full database information
struct Player: Codable, PersistableRecord, FetchableRecord {
    var id: Int64
    var name: String
    var score: Int
}

// A partial player
struct PartialPlayer: Encodable, PersistableRecord {
    static let databaseTableName = "player"
    var name: String
}

And now you can get a full player by inserting a partial one:

try dbQueue.write { db in
    let partialPlayer = PartialPlayer(name: "Alice")
    
    // INSERT INTO player (name) VALUES ('Alice') RETURNING *
    if let player = try partialPlayer.insertAndFetch(db, as: Player.self) {
        print(player.id)    // The inserted id
        print(player.name)  // The inserted name
        print(player.score) // The default score
    }
}

For extra precision, you can select only the columns you need, and fetch the desired value from the provided prepared Statement:

try dbQueue.write { db in
    let partialPlayer = PartialPlayer(name: "Alice")
    
    // INSERT INTO player (name) VALUES ('Alice') RETURNING score
    let score = try partialPlayer.insertAndFetch(db, selection: [Column("score")]) { statement in
        try Int.fetchOne(statement)
    }
    print(score) // Prints 1000, the default score
}

There are other similar persistence methods, such as upsertAndFetch, saveAndFetch, updateAndFetch, updateChangesAndFetch, etc. They all behave like upsert, save, update, updateChanges, except that they return saved values. For example:

// Save and return the saved player
let savedPlayer = try player.saveAndFetch(db)

See Persistence Methods, Upsert, and updateChanges methods for more information.

Batch operations can return updated or deleted values:

Warning: Make sure you check the documentation of the RETURNING clause, which describes important limitations and caveats for batch operations.

let request = Player.filter(...)...

// Fetch all deleted players
// DELETE FROM player RETURNING *
let deletedPlayers = try request.deleteAndFetchAll(db) // [Player]

// Fetch a selection of columns from the deleted rows
// DELETE FROM player RETURNING name
let statement = try request.deleteAndFetchStatement(db, selection: [Column("name")])
let deletedNames = try String.fetchSet(statement)

// Fetch all updated players
// UPDATE player SET score = score + 10 RETURNING *
let updatedPlayers = try request.updateAndFetchAll(db, [Column("score") += 10]) // [Player]

// Fetch a selection of columns from the updated rows
// UPDATE player SET score = score + 10 RETURNING score
let statement = try request.updateAndFetchStatement(
    db, [Column("score") += 10],
    select: [Column("score")])
let updatedScores = try Int.fetchAll(statement)

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

To this end, your record type can implement persistence callbacks. Callbacks are methods that get called at certain moments of a record's life cycle. With callbacks it is possible to write code that will run whenever an record is inserted, updated, or deleted.

In order to use a callback method, you need to provide its implementation. For example, a frequently used callback is didInsert, in the case of auto-incremented database ids:

struct Player: MutablePersistableRecord {
    var id: Int64?
    
    // Update auto-incremented id upon successful insertion
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

try dbQueue.write { db in
    var player = Player(id: nil, ...)
    try player.insert(db)
    print(player.id) // didInsert was called: prints some non-nil id
}

When you subclass the Record class, override the callback, and make sure you call super at some point of your implementation:

class Player: Record {
    var id: Int64?
    
    // Update auto-incremented id upon successful insertion
    func didInsert(_ inserted: InsertionSuccess) {
        super.didInsert(inserted)
        id = inserted.rowID
    }
}

Callbacks can also help implementing record validation:

struct Link: PersistableRecord {
    var url: URL
    
    func willSave(_ db: Database) throws {
        if url.host == nil {
            throw ValidationError("url must be absolute.")
        }
    }
}

try link.insert(db) // Calls the willSave callback
try link.update(db) // Calls the willSave callback
try link.save(db)   // Calls the willSave callback
try link.upsert(db) // Calls the willSave callback

Here is a list with all the available persistence callbacks, listed in the same order in which they will get called during the respective operations:

• Inserting a record (all record.insert and record.upsert methods)

  • willSave
  • aroundSave
  • willInsert
  • aroundInsert
  • didInsert
  • didSave

• Updating a record (all record.update methods)

  • willSave
  • aroundSave
  • willUpdate
  • aroundUpdate
  • didUpdate
  • didSave

• Deleting a record (only the record.delete(_:) method)

  • willDelete
  • aroundDelete
  • didDelete

For detailed information about each callback, check the reference.

In the MutablePersistableRecord protocol, willInsert and didInsert are mutating methods. In PersistableRecord, they are not mutating.

Note: The record.save(_:) method performs an UPDATE if the record has a non-null primary key, and then, if no row was modified, an INSERT. It directly performs an INSERT if the record has no primary key, or a null primary key. It triggers update and/or insert callbacks accordingly.

Warning: Callbacks are only invoked from persistence methods called on record instances. Callbacks are not invoked when you call a type method, perform a batch operations, or execute raw SQL.

Warning: When a did*** callback is invoked, do not assume that the change is actually persisted on disk, because the database may still be inside an uncommitted transaction. When you need to handle transaction completions, use the afterNextTransaction(onCommit:onRollback:). For example:

struct PictureFile: PersistableRecord {
    var path: String
    
    func willDelete(_ db: Database) {
        db.afterNextTransaction { _ in
            try? deleteFileOnDisk()
        }
    }
}

When a record type maps a table with a single-column primary key, it is recommended to have it adopt the standard Identifiable protocol.

struct Player: Identifiable, FetchableRecord, PersistableRecord {
    var id: Int64 // fulfills the Identifiable requirement
    var name: String
    var score: Int
}

When id has a database-compatible type (Int64, Int, String, UUID, ...), the Identifiable conformance unlocks type-safe record and request methods:

let player = try Player.find(db, id: 1)               // Player
let player = try Player.fetchOne(db, id: 1)           // Player?
let players = try Player.fetchAll(db, ids: [1, 2, 3]) // [Player]
let players = try Player.fetchSet(db, ids: [1, 2, 3]) // Set<Player>

let request = Player.filter(id: 1)
let request = Player.filter(ids: [1, 2, 3])

try Player.deleteOne(db, id: 1)
try Player.deleteAll(db, ids: [1, 2, 3])