Latest release: September 16, 2016 • version 0.84.0 • CHANGELOG

Requirements: iOS 8.0+ / OSX 10.9+ / watchOS 2.0+ • Xcode 8+ • Swift 3

• Swift 2.2: use the version 0.80.2
• Swift 2.3: use the version 0.81.1

Follow @groue on Twitter for release announcements and usage tips.

Features • Usage • Installation • Documentation • FAQ

GRDB ships with a low-level SQLite API, and high-level tools that help dealing with databases:

• Records: fetching and persistence methods for your custom structs and class hierarchies
• Query Interface: a swift way to avoid the SQL language
• WAL Mode Support: that means extra performance for multi-threaded applications
• Migrations: transform your database as your application evolves
• Database Changes Observation: perform post-commit and post-rollback actions
• Fetched Records Controller: automated tracking of changes in a query results, and UITableView animations
• Encryption with SQLCipher (:warning: not currently supported with Swift 3)
• Support for custom SQLite builds

More than a set of tools that leverage SQLite abilities, GRDB is also:

• Safer: read the blog post Four different ways to handle SQLite concurrency
• Faster: see Comparing the Performances of Swift SQLite libraries
• Well documented & tested

For a general overview of how a protocol-oriented library impacts database accesses, have a look at How to build an iOS application with SQLite and GRDB.swift.

Open a connection to the database:

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

Execute SQL statements:

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

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

Fetch database rows and values:

dbQueue.inDatabase { db in
    for row in Row.fetch(db, "SELECT * FROM pointOfInterests") {
        let title: String = row.value(named: "title")
        let isFavorite: Bool = row.value(named: "favorite")
        let coordinate = CLLocationCoordinate2DMake(
            row.value(named: "latitude"),
            row.value(named: "longitude"))
    }

    let poiCount = Int.fetchOne(db, "SELECT COUNT(*) FROM pointOfInterests")! // Int
    let poiTitles = String.fetchAll(db, "SELECT title FROM pointOfInterests") // [String]
}

// Extraction
let poiCount = dbQueue.inDatabase { db in
    Int.fetchOne(db, "SELECT COUNT(*) FROM pointOfInterests")!
}

Insert and fetch records:

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

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

try dbQueue.inDatabase { db in
    var berlin = PointOfInterest(
        id: nil,
        title: "Berlin",
        isFavorite: false,
        coordinate: CLLocationCoordinate2DMake(52.52437, 13.41053))
    
    try berlin.insert(db)
    berlin.id // some value
    
    berlin.isFavorite = true
    try berlin.update(db)
    
    // Fetch [PointOfInterest] from SQL
    let pois = PointOfInterest.fetchAll(db, "SELECT * FROM pointOfInterests")
}

Avoid SQL with the query interface:

dbQueue.inDatabase { db in
    try db.create(table: "pointOfInterests") { t in
        t.column("id", .integer).primaryKey()
        t.column("title", .text).notNull()
        t.column("favorite", .boolean).notNull().defaults(to: false)
        t.column("longitude", .double).notNull()
        t.column("latitude", .double).notNull()
    }
    
    // PointOfInterest?
    let paris = PointOfInterest.fetchOne(db, key: 1)
    
    // PointOfInterest?
    let titleColumn = Column("title")
    let berlin = PointOfInterest.filter(titleColumn == "Berlin").fetchOne(db)
    
    // [PointOfInterest]
    let favoriteColumn = Column("favorite")
    let favoritePois = PointOfInterest
        .filter(favoriteColumn)
        .order(titleColumn)
        .fetchAll(db)
}

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 (on cocoadocs.org)

Getting Started

• Installation
• 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 • fetch requests

Application Tools

• Migrations: Transform your database as your application evolves.
• Database Changes Observation: Perform post-commit and post-rollback actions.
• FetchedRecordsController: Automatic database changes tracking, plus UITableView animations.
• Encryption: Encrypt your database with SQLCipher.
• Backup: Dump the content of a database to another.
• GRDB Extension Guide: When a feature is lacking, extend GRDB right from your application.

Good to Know

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

FAQ

Sample Code

GRDB requires Xcode 8 to be installed in the /Applications folder, with its regular name Xcode.

⚠️ Warning: SQLCipher is currently not available in Swift 3.

CocoaPods is a dependency manager for Xcode projects.

Swift 3 requires CocoaPods 1.1+, currently in beta. Install Cocoapods beta with the following command:

gem install cocoapods --pre

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

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

pod 'GRDB.swift'

Carthage is another dependency manager for Xcode projects.

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

github "groue/GRDB.swift"

1. Download a copy of GRDB.swift.
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.
4. Add the GRDB.framework from the targetted platform to the Embedded Binaries section of the General tab of your target.

See GRDBDemoiOS for an example of such integration.

By default, GRDB uses the SQLite library that ships with the operating system. You can build GRDB with custom SQLite sources and options, through swiftlyfalling/SQLiteLib. The current SQLite version is 3.14.1. See installation instructions.

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).
• Unless read-only, database pools open your SQLite database in the WAL mode.
• 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

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 inDatabase and inTransaction methods block the current thread until your database statements are executed in a protected dispatch queue. They safely serialize the database accesses:

// Execute database statements:
try dbQueue.inDatabase { db in
    try db.create(table: "pointOfInterests") { ... }
    try PointOfInterest(...).insert(db)
}

// Wrap database statements in a transaction:
try dbQueue.inTransaction { db in
    if let poi = PointOfInterest.fetchOne(db, key: 1) {
        try poi.delete(db)
    }
    return .commit
}

// Read values:
dbQueue.inDatabase { db in
    let pois = PointOfInterest.fetchAll(db)
    let poiCount = PointOfInterest.fetchCount(db)
}

// Extract a value from the database:
let poiCount = dbQueue.inDatabase { db in
    PointOfInterest.fetchCount(db)
}

Your application should create a single DatabaseQueue per database file. See, for example, DemoApps/GRDBDemoiOS/Database.swift for a sample code that properly sets up a single database queue that is available throughout the application.

If you do otherwise, you may well experience concurrency issues, and you don't want that. See Concurrency for more information.

Configure database queues:

var config = Configuration()
config.readonly = true
config.foreignKeysEnabled = true // Default is already true
config.trace = { print($0) }     // Prints all SQL statements
config.fileAttributes = [FileAttributeKey.protectionKey.rawValue: ...]  // Configure database protection

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

See Configuration for more details.

Database Queues are simple, but they prevent concurrent accesses: at every moment, there is no more than a single thread that is using the database.

A Database Pool can improve your application performance because it 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 read, write and writeInTransaction methods block the current thread until your database statements are executed in a protected dispatch queue. They safely isolate the database accesses:

// Execute database statements:
try dbPool.write { db in
    try db.create(table: "pointOfInterests") { ... }
    try PointOfInterest(...).insert(db)
}

// Wrap database statements in a transaction:
try dbPool.writeInTransaction { db in
    if let poi = PointOfInterest.fetchOne(db, key: 1) {
        try poi.delete(db)
    }
    return .commit
}

// Read values:
dbPool.read { db in
    let pois = PointOfInterest.fetchAll(db)
    let poiCount = PointOfInterest.fetchCount(db)
}

// Extract a value from the database:
let poiCount = dbPool.read { db in
    PointOfInterest.fetchCount(db)
}

Your application should create a single DatabasePool per database file.

If you do otherwise, you may well experience concurrency issues, and you don't want that. See Concurrency for more information.

Database pools allows 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.

• The total number of concurrent reads is limited. When the maximum number has been reached, a read waits for another read to complete. That maximum number can be configured (see below).

• Conversely, writes are serialized. They still can happen in parallel with reads, but GRDB makes sure that those parallel writes are not visible inside a read closure.

See Concurrency for more information.

Configure database pools:

var config = Configuration()
config.readonly = true
config.foreignKeysEnabled = true // Default is already true
config.trace = { print($0) }     // Prints all SQL statements
config.fileAttributes = [FileAttributeKey.protectionKey.rawValue: ...]  // Configure database protection
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.

In this section of the documentation, we will talk SQL only. Jump to the query interface if SQL if 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
• Transactions and Savepoints

Advanced topics:

• Custom Value Types
• Prepared Statements
• Custom SQL Functions
• Database Schema Introspection
• Row Adapters
• Raw SQLite Pointers

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 db.execute(
    "CREATE TABLE persons (" +
        "id INTEGER PRIMARY KEY," +
        "name TEXT NOT NULL," +
        "age INT" +
    ")")

try db.execute(
    "INSERT INTO persons (name, age) VALUES (:name, :age)",
    arguments: ["name": "Barbara", "age": 39])

// Join multiple statements with a semicolon:
try db.execute(
    "INSERT INTO persons (name, age) VALUES (?, ?); " +
    "INSERT INTO persons (name, age) VALUES (?, ?)",
    arguments: ["Arthur", 36, "Barbara", 39])

The ? and colon-prefixed keys like :name 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.).

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

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

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

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

let person = Person(name: "Arthur", age: 36)
try person.insert(db)
let personId = person.id

You can fetch database rows, plain values, and custom models aka "records".

Rows are the raw results of SQL queries:

if let row = Row.fetchOne(db, "SELECT * FROM wines WHERE id = ?", arguments: [1]) {
    let name: String = row.value(named: "name")
    let color: Color = row.value(named: "color")
    print(name, color)
}

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

for url in URL.fetch(db, "SELECT url FROM wines") {
    print(url)
}

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

let wines = Wine.fetchAll(db, "SELECT * FROM wines")

• Fetching Methods
• Row Queries
• Value Queries
• Records

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

Type.fetch(...)    // DatabaseSequence<Type>
Type.fetchAll(...) // [Type]
Type.fetchOne(...) // Type?

• fetch returns a sequence that is memory efficient, but must be consumed in a protected dispatch queue (you'll get a fatal error if you do otherwise).

  for row in Row.fetch(db, "SELECT ...") { // DatabaseSequence<Row>
      ...
  }

  Don't modify the database during a sequence iteration:

  // Undefined behavior
  for row in Row.fetch(db, "SELECT * FROM persons") {
      try db.execute("DELETE FROM persons ...")
  }

  A sequence fetches a new set of results each time it is iterated.

• fetchAll returns an array that can be consumed on any thread. It contains copies of database values, and can take a lot of memory:

  let persons = Person.fetchAll(db, "SELECT ...") // [Person]

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

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

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

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

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

for row in Row.fetch(db, "SELECT * FROM wines") {
    let name: String = row.value(named: "name")
    let color: Color = row.value(named: "color")
    print(name, color)
}

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

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

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

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

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

☝️ Don't turn a row sequence into an array with Array(rowSequence) or rowSequence.filter { ... }: you would not get the distinct rows you expect. To get an array, use Row.fetchAll(...).

☝️ Make sure you copy a row whenever you extract it from a sequence for later use: row.copy().

Read column values by index or column name:

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

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

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

The value function returns the type you ask for. See Values for more information on supported value types:

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

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

You can also use the as type casting operator:

row.value(...) as Int
row.value(...) as Int?

⚠️ Warning: avoid the as! and as? operators, because they misbehave in the context of type inference (see rdar://21676393):

if let int = row.value(...) as? Int { ... } // BAD - doesn't work
if let int = row.value(...) 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 = Row.fetchOne(db, "SELECT NULL")!
  row.value(atIndex: 0) as Int? // nil
  row.value(atIndex: 0) as Int  // fatal error: could not convert NULL to Int.

  There is one exception, though: the DatabaseValue type:

  row.value(atIndex: 0) as DatabaseValue // DatabaseValue.null

• Missing columns return nil.

  let row = Row.fetchOne(db, "SELECT 'foo' AS foo")!
  row.value(named: "missing") as String? // nil
  row.value(named: "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 = Row.fetchOne(db, "SELECT 'Mom’s birthday'")!
  row.value(atIndex: 0) as String // "Mom’s birthday"
  row.value(atIndex: 0) as Date?  // fatal error: could not convert "Mom’s birthday" to Date.
  row.value(atIndex: 0) as Date   // fatal error: could not convert "Mom’s birthday" to Date.

  This fatal error can be avoided with the DatabaseValueConvertible.fromDatabaseValue() method.

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

  GRDB will sometimes let those conversions go through:

  for row in Row.fetch(db, "SELECT '20 small cigars'") {
      row.value(atIndex: 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 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 dbv: DatabaseValue = row.value(atIndex: 0)
let dbv: DatabaseValue = row.value(named: "name")

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

// All the five storage classes supported by SQLite:
switch dbv.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 dbv: DatabaseValue = row.value(named: "bookCount")
let bookCount   = Int.fromDatabaseValue(dbv)   // Int?
let bookCount64 = Int64.fromDatabaseValue(dbv) // Int64?
let hasBooks    = Bool.fromDatabaseValue(dbv)  // Bool?, false when 0

let dbv: DatabaseValue = row.value(named: "date")
let string = String.fromDatabaseValue(dbv)     // "2015-09-11 18:14:15.123"
let date   = Date.fromDatabaseValue(dbv)       // Date?

fromDatabaseValue returns nil for invalid conversions:

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

This turns out useful when you have to process untrusted databases. Compare:

let date: Date? = row.value(atIndex: 0)  // fatal error: could not convert "Mom’s birthday" to Date.
let date = Date.fromDatabaseValue(row.value(atIndex: 0)) // nil

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

// All the (columnName, databaseValue) tuples, from left to right:
for (columnName, databaseValue) 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(nsDictionary) // nil if invalid NSDictionary

Yet rows are not real dictionaries: they are ordered, and may contain duplicate keys:

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

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

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

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

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 = Int.fetchOne(db, "SELECT COUNT(*) FROM persons")! // Int
let urls = URL.fetchAll(db, "SELECT url FROM links")          // [URL]

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

• Swift Standard Library: Bool, Double, Float, Int, Int32, Int64, 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.

• 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 links (url, verified) VALUES (?, ?)",
    arguments: [url, verified])

Values can be extracted from rows:

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

Values can be directly fetched:

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

Use values in Records:

class Link : Record {
    var url: URL
    var isVerified: Bool
    
    required init(row: Row) {
        url = row.value(named: "url")
        isVerified = row.value(named: "verified")
        super.init(row: row)
    }
    
    override var persistentDictionary: [String: DatabaseValueConvertible?] {
        return ["url": url, "verified": isVerified]
    }
}

Use values in the query interface:

let url: URL = ...
let link = Link.filter(urlColumn == url).fetchOne(db)

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

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

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

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

Compare with the anti-patterns below:

for row in Row.fetch(db, "SELECT data, ...") {
    // This data is copied:
    let data: Data = row.value(named: "data")
    
    // This data is copied:
    if let dbv: DatabaseValue = row.value(named: "data") {
        let data: Data = dbv.value()
    }
    
    // This data is copied:
    let copiedRow = row.copy()
    let data = copiedRow.dataNoCopy(named: "data")
}

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

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

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

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

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

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

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

let creationDate: Date = row.value(named: "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 NOW (WHERE date > NOW works)
• Able to feed SQLite date & time functions
• Precise enough

Yet this format may not fit your needs. For example, you may want to store dates as timestamps. In this case, store and load Doubles instead of Date, and perform the required conversions.

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 value types:

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

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

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 transfers (
    amount DECIMAL(10,5) -- will store integer or double, actually
)

This means that computations will not be exact:

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

// 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:

// Store
let amount = NSDecimalNumber(string: "0.1")                       // 0.1
let integerAmount = amount.multiplying(byPowerOf10: 2).int64Value // 100
try db.execute("INSERT INTO transfers (amount) VALUES (?)", arguments: [integerAmount])

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

UUID can be stored and fetched from the database just like other values. GRDB stores uuids as 16-bytes data blobs.

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

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

When a database value does not match any enum case, you get a fatal error. This fatal error can be avoided with the DatabaseValueConvertible.fromDatabaseValue() method:

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

row.value(atIndex: 0) as String  // "syrah"
row.value(atIndex: 0) as Grape?  // fatal error: could not convert "syrah" to Grape.
row.value(atIndex: 0) as Grape   // fatal error: could not convert "syrah" to Grape.
Grape.fromDatabaseValue(row.value(atIndex: 0))  // nil

The DatabaseQueue.inTransaction() and DatabasePool.writeInTransaction() methods open an SQLite transaction and run their closure argument in a protected dispatch queue. They block the current thread until your database statements are executed:

try dbQueue.inTransaction { db in
    let wine = Wine(color: .red, name: "Pomerol")
    try wine.insert(db)
    return .commit
}

If an error is thrown within the transaction body, the transaction is rollbacked and the error is rethrown by the inTransaction method. If you return .rollback from your closure, the transaction is also rollbacked, but no error is thrown.

If you want to insert a transaction between other database statements, you can use the Database.inTransaction() function:

try dbQueue.inDatabase { db in  // or dbPool.write { db in
    ...
    try db.inTransaction {
        ...
        return .commit
    }
    ...
}

You can ask a database 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 sections of your application should use savepoints, described below:

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

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

try dbQueue.inTransaction { db in
    try db.inSavepoint { 
        try db.execute("DELETE ...")
        try db.execute("INSERT ...") // need to rollback the delete above if this fails
        return .commit
    }
    
    // Other savepoints, etc...
    return .commit
}

If an error is thrown within the savepoint body, the savepoint is rollbacked and the error is rethrown by the inSavepoint method. If you return .rollback from your closure, the body 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 committed to disk when the outermost savepoint 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 savepoints uses, use SQL queries.

SQLite supports three kinds of transactions: deferred, immediate, and exclusive. GRDB defaults to immediate.

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

// A connection with default DEFERRED transactions:
var config = Configuration()
config.defaultTransactionKind = .deferred
let dbQueue = try DatabaseQueue(path: "...", configuration: config)

// Opens a DEFERRED transaction:
dbQueue.inTransaction { db in ... }

// Opens an EXCLUSIVE transaction:
dbQueue.inTransaction(.exclusive) { db in ... }

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

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

All types that adopt this protocol can be used like all other value types (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. DatabaseValue has no public initializer: to create one, use DatabaseValue.null, or another type that already adopts the protocol: 1.databaseValue, "foo".databaseValue, etc.

The fromDatabaseValue() factory method returns an instance of your custom type if the databaseValue contains a suitable value. If the databaseValue does not contain a suitable value, such as "foo" for Date, the method returns nil.

The GRDB Extension Guide contains sample code that has UIColor adopt DatabaseValueConvertible.

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.inDatabase { db in
    let updateSQL = "INSERT INTO persons (name, age) VALUES (:name, :age)"
    let updateStatement = try db.makeUpdateStatement(updateSQL)
    
    let selectSQL = "SELECT * FROM persons 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", "age": 41]
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):

for row in Row.fetch(selectStatement) { ... }
let persons = Person.fetchAll(selectStatement)
let person = Person.fetchOne(selectStatement)

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

try updateStatement.execute(arguments: ["name": "Arthur", "age": 41])
let person = Person.fetchOne(selectStatement, arguments: ["Arthur"])

☝️ Note: a prepared statement that has failed can not be reused.

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

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(updateSQL)
let selectStatement = try db.cachedSelectStatement(selectSQL)

SQLite lets you define SQL functions.

A custom SQL function extends SQLite. It can be used in raw SQL queries. And when SQLite needs to evaluate it, it calls your custom code.

let reverseString = DatabaseFunction(
    "reverseString",  // The name of the function
    argumentCount: 1, // Number of arguments
    pure: true,       // True means that the result only depends on input
    function: { (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.characters.reversed())
    })
dbQueue.add(function: reverseString)   // Or dbPool.add(function: ...)

dbQueue.inDatabase { db in
    // "oof"
    String.fetchOne(db, "SELECT reverseString('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.flatMap { Double.fromDatabaseValue($0) }
    return doubles.reduce(0, +) / Double(doubles.count)
}
dbQueue.add(function: averageOf)

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

Use custom functions in the query interface:

// SELECT reverseString("name") FROM persons
Person.select(reverseString.apply(nameColumn))

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

SQLite provides database schema introspection tools, such as the sqlite_master table, and the pragma table_info:

try db.create(table: "persons") { t in
    t.column("id", .integer).primaryKey()
    t.column("name", .text)
}

// <Row type:"table" name:"persons" tbl_name:"persons" rootpage:2
//      sql:"CREATE TABLE persons(id INTEGER PRIMARY KEY, name TEXT)">
for row in Row.fetch(db, "SELECT * FROM sqlite_master") {
    print(row)
}

// <Row cid:0 name:"id" type:"INTEGER" notnull:0 dflt_value:NULL pk:1>
// <Row cid:1 name:"name" type:"TEXT" notnull:0 dflt_value:NULL pk:0>
for row in Row.fetch(db, "PRAGMA table_info('persons')") {
    print(row)
}

GRDB provides four high-level methods as well:

db.tableExists("persons")    // Bool, true if the table exists
db.indexes(on: "persons")    // [IndexInfo], the indexes defined on the table
try db.table("persons", hasUniqueKey: ["email"]) // Bool, true if column(s) is a unique key
try db.primaryKey("persons") // PrimaryKeyInfo?

Primary key is nil when table has no primary key:

// CREATE TABLE items (name TEXT)
let itemPk = try db.primaryKey("items") // nil

Primary keys have one or several columns. Single-column primary keys may contain the auto-incremented row id:

// CREATE TABLE persons (
//   id INTEGER PRIMARY KEY,
//   name TEXT
// )
let personPk = try db.primaryKey("persons")!
personPk.columns     // ["id"]
personPk.rowIDColumn // "id"

// CREATE TABLE countries (
//   isoCode TEXT NOT NULL PRIMARY KEY
//   name TEXT
// )
let countryPk = db.primaryKey("countries")!
countryPk.columns     // ["isoCode"]
countryPk.rowIDColumn // nil

// CREATE TABLE citizenships (
//   personID INTEGER NOT NULL REFERENCES persons(id)
//   countryIsoCode TEXT NOT NULL REFERENCES countries(isoCode)
//   PRIMARY KEY (personID, countryIsoCode)
// )
let citizenshipsPk = db.primaryKey("citizenships")!
citizenshipsPk.columns     // ["personID", "countryIsoCode"]
citizenshipsPk.rowIDColumn // nil

Row adapters let you map column names for easier row consumption.

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

// An adapter that maps column 'consumed' to column 'produced':
let adapter = ColumnMapping(["consumed": "produced"])

// Fetch a column named 'produced', and apply adapter:
let row = Row.fetchOne(db, "SELECT 'Hello' AS produced", adapter: adapter)!

// The adapter in action:
row.value(named: "consumed") // "Hello"

Row adapters can also define row "scopes". Scopes help several consumers feed on a single row and can reveal useful with joined queries.

For example, let's build a query which loads books along with their author:

let sql = "SELECT books.id, books.title, " +
          "       books.authorID, persons.name AS authorName " +
          "FROM books " +
          "JOIN persons ON books.authorID = persons.id"

The author columns are "authorID" and "authorName". Let's say that we prefer to consume them as "id" and "name". For that we define a scope named "author":

let authorMapping = ColumnMapping(["id": "authorID", "name": "authorName"])
let adapter = ScopeAdapter(["author": authorMapping])

Use the Row.scoped(on:) method to access the "author" scope:

for row in Row.fetch(db, sql, adapter: adapter) {
    // The fetched row, without adaptation:
    row.value(named: "id")          // 1
    row.value(named: "title")       // Moby-Dick
    row.value(named: "authorID")    // 10
    row.value(named: "authorName")  // Melville
    
    // The "author" scope, with mapped columns:
    if let authorRow = row.scoped(on: "author") {
        authorRow.value(named: "id")    // 10
        authorRow.value(named: "name")  // Melville
    }
}

Tip: now that we have nice "id" and "name" columns, we can leverage RowConvertible types such as Record subclasses. For example, assuming the Book type consumes the "author" scope in its row initializer and builds a Person from it, the same row can be consumed by both the Book and Person types:

for book in Book.fetch(db, sql, adapter: adapter) {
    book.title        // Moby-Dick
    book.author?.name // Melville
}

And Person and Book can still be fetched without row adapters:

let books = Book.fetchAll(db, "SELECT * FROM books")
let persons = Person.fetchAll(db, "SELECT * FROM persons")

You can mix a main adapter with scopes:

let sql = "SELECT main.id AS mainID, main.name AS mainName, " +
          "       friend.id AS friendID, friend.name AS friendName, " +
          "FROM persons main " +
          "LEFT JOIN persons friend ON friend.id = main.bestFriendID"

let mainAdapter = ColumnMapping(["id": "mainID", "name": "mainName"])
let bestFriendAdapter = ColumnMapping(["id": "friendID", "name": "friendName"])
let adapter = mainAdapter.addingScopes(["bestFriend": bestFriendAdapter])

for row in Row.fetch(db, sql, adapter: adapter) {
    // The fetched row, adapted with mainAdapter:
    row.value(named: "id")   // 1
    row.value(named: "name") // Arthur
    
    // The "bestFriend" scope, with bestFriendAdapter:
    if let bestFriendRow = row.scoped(on: "bestFriend") {
        bestFriendRow.value(named: "id")    // 2
        bestFriendRow.value(named: "name")  // Barbara
    }
}

// Assuming Person.init(row:) consumes the "bestFriend" scope:
for person in Person.fetch(db, sql, adapter: adapter) {
    person.name             // Arthur
    person.bestFriend?.name // Barbara
}

For more information about row adapters, see the documentation of:

• RowAdapter: the protocol that lets you define your custom row adapters
• ColumnMapping: a row adapter that renames row columns
• SuffixRowAdapter: a row adapter that hides the first columns of a row
• ScopeAdapter: the row adapter that groups several adapters together to define scopes

Not all SQLite APIs are exposed in GRDB.

The Database.sqliteConnection and Statement.sqliteStatement properties provide the raw pointers that are suitable for SQLite C API:

try dbQueue.inDatabase { 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.
• SQLite connections are opened 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.

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

• Connections & statements, obviously.
• Errors (pervasive)
  • sqlite3_errmsg
  • sqlite3_errcode
• Inserted Row IDs (Database.lastInsertedRowID).
  • sqlite3_last_insert_rowid
• Changes count (Database.changesCount and Database.totalChangesCount).
  • sqlite3_changes
  • sqlite3_total_changes
• Custom SQL functions (see Custom SQL Functions)
  • sqlite3_create_function_v2
• Custom collations (see String Comparison)
  • sqlite3_create_collation_v2
• Busy mode (see Concurrency).
  • sqlite3_busy_handler
  • sqlite3_busy_timeout
• Update, commit and rollback hooks (see Database Changes Observation):
  • sqlite3_update_hook
  • sqlite3_commit_hook
  • sqlite3_rollback_hook
• Backup (see Backup):
  • sqlite3_backup_init
  • sqlite3_backup_step
  • sqlite3_backup_finish
• Authorizations callbacks are reserved by GRDB:
  • sqlite3_set_authorizer

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

if let poi = PointOfInterest.fetchOne(db, key: 1) {
    poi.isFavorite = true
    try poi.update(db)
}

Your custom structs and classes can adopt each protocol individually, and opt in to focused sets of features. Or you can subclass the Record class, and get the full toolkit in one go: fetching methods, persistence methods, and changes tracking.

☝️ 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, and do not auto-update. 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.

Overview

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

Protocols and the Record class

• RowConvertible Protocol
• TableMapping Protocol
• Persistable Protocol
  • Persistence Methods
  • Customizing the Persistence Methods
  • Conflict Resolution
• Record Class
  • Changes Tracking

To insert a record in the database, subclass the Record class or adopt the Persistable protocol, and call the insert method:

class Person : Record { ... }

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

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

Record subclasses and types that adopt the RowConvertible protocol can be fetched from the database:

class Person : Record { ... }
let persons = Person.fetchAll(db, "SELECT ...", arguments: ...)

Add the TableMapping protocol and you can stop writing SQL:

let persons = Person.filter(emailColumn != nil).order(nameColumn).fetchAll(db)
let person = Person.fetchOne(db, key: 1)
let person = Person.fetchOne(db, key: ["email": "[email protected]"])
let countries = Country.fetchAll(db, keys: ["FR", "US"])

To learn more about querying records, check the query interface.

Record subclasses and types that adopt the Persistable protocol can be updated in the database:

let person = Person.fetchOne(db, key: 1)!
person.name = "Arthur"
try person.update(db)

Record subclasses track changes:

let person = Person.fetchOne(db, key: 1)!
person.name = "Arthur"
if person.hasPersistentChangedValues {
    try person.update(db)
}

For batch updates, you have to execute an SQL query:

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

Record subclasses and types that adopt the Persistable protocol can be deleted from the database:

let person = Person.fetchOne(db, key: 1)!
try person.delete(db)

The TableMapping protocol gives you methods that delete according to primary key or any unique index:

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

For batch deletes, see the query interface:

try Person.filter(emailColumn == nil).deleteAll(db)

Record subclasses and types that adopt the TableMapping protocol can be counted:

let personWithEmailCount = Person.filter(emailColumn != nil).fetchCount(db)  // Int

You can now jump to:

• RowConvertible Protocol
• TableMapping Protocol
• Persistable Protocol
• Record Class
• The Query Interface

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

public protocol RowConvertible {
    /// Row initializer
    init(row: Row)
    
    /// Optional method which gives adopting types an opportunity to complete
    /// their initialization after being fetched. Do not call it directly.
    mutating func awakeFromFetch(row: Row)
}

To use RowConvertible, subclass the Record class, or adopt it explicitely. For example:

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

extension PointOfInterest : RowConvertible {
    init(row: Row) {
        id = row.value(named: "id")
        title = row.value(named: "title")
        coordinate = CLLocationCoordinate2DMake(
            row.value(named: "latitude"),
            row.value(named: "longitude"))
    }
}

See column values for more information about the row.value() method.

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

RowConvertible allows adopting types to be fetched from SQL queries:

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

See fetching methods for information about the fetch, fetchAll and fetchOne methods. See fetching rows for more information about the query arguments.

RowConvertible types usually consume rows by column name:

extension PointOfInterest : RowConvertible {
    init(row: Row) {
        id = row.value(named: "id")              // "id"
        title = row.value(named: "title")        // "title"
        coordinate = CLLocationCoordinate2DMake(
            row.value(named: "latitude"),        // "latitude"
            row.value(named: "longitude"))       // "longitude"
    }
}

Occasionnally, you'll want to write a complex SQL query that uses different column names. In this case, row adapters are there to help you mapping raw column names to the names expected by your RowConvertible types.

Adopt the TableMapping protocol on top of RowConvertible, and you are granted with the full query interface.

public protocol TableMapping {
    static var databaseTableName: String { get }
}

To use TableMapping, subclass the Record class, or adopt it explicitely. For example:

extension PointOfInterest : TableMapping {
    static let databaseTableName = "pointOfInterests"
}

Adopting types can be fetched without SQL, using the query interface:

let paris = PointOfInterest.filter(nameColumn == "Paris").fetchOne(db)

You can also fetch and delete records according to their primary key:

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

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

// Use a dictionary for composite primary keys:
Citizenship.fetchOne(db, key: ["personID": 1, "countryISOCode": "FR"]) // Citizenship?

// Delete records:
try Person.deleteOne(db, key: 1)
try Country.deleteAll(db, keys: ["FR", "US"])

When given dictionaries, fetchOne, deleteOne, fetchAll and deleteAll accept any set of columns that uniquely identify rows. These are the primary key columns, or any columns involved in a unique index:

// CREATE TABLE persons (
//   id INTEGER PRIMARY KEY, -- unique id
//   email TEXT UNIQUE,      -- unique email
//   name TEXT               -- not unique
// )
Person.fetchOne(db, key: ["id": 1])                       // Person?
Person.fetchOne(db, key: ["email": "[email protected]"]) // Person?
Person.fetchOne(db, key: ["name": "Arthur"]) // fatal error: table persons has no unique index on column name.

GRDB provides two protocols that let adopting types store themselves in the database:

public protocol MutablePersistable : TableMapping {
    /// The name of the database table (from TableMapping)
    static var databaseTableName: String { get }
    
    /// The values persisted in the database
    var persistentDictionary: [String: DatabaseValueConvertible?] { get }
    
    /// 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?)
}

public protocol Persistable : MutablePersistable {
    /// 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 struct that mutates on insertion, choose MutablePersistable.

  For example, your table has an INTEGER PRIMARY KEY and you want to store the inserted id on successful insertion. Or your table has a UUID primary key, and you want to automatically generate one on insertion.

• Otherwise, stick with Persistable. Particularly if your type is a class.

The persistentDictionary property returns a dictionary whose keys are column names, and values any DatabaseValueConvertible value (Bool, Int, String, Date, Swift enums, etc.) See Values for more information.

The optional didInsert method lets the adopting type store its rowID after successful insertion. If your table has an INTEGER PRIMARY KEY column, you are likely to define this method. Otherwise, you can safely ignore it. It is called from a protected dispatch queue, and serialized with all database updates.

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

extension PointOfInterest : MutablePersistable {
    
    /// The values persisted in the database
    var persistentDictionary: [String: DatabaseValueConvertible?] {
        return [
            "id": id,
            "title": title,
            "latitude": coordinate.latitude,
            "longitude": coordinate.longitude]
    }
    
    // Update id upon successful insertion:
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

var paris = PointOfInterest(
    id: nil,
    title: "Paris",
    coordinate: CLLocationCoordinate2DMake(48.8534100, 2.3488000))

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

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

try pointOfInterest.insert(db)               // INSERT
try pointOfInterest.update(db)               // UPDATE
try pointOfInterest.update(db, columns: ...) // UPDATE
try pointOfInterest.save(db)                 // Inserts or updates
try pointOfInterest.delete(db)               // DELETE
pointOfInterest.exists(db)                   // Bool

• insert, update, save and delete can throw a DatabaseError whenever an SQLite integrity check fails.

• update can also throw a PersistenceError of type recordNotFound, 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 primary keys that span several columns.

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 Person : Record {
    var uuid: UUID?
    
    override func insert(_ db: Database) throws {
        if uuid == nil {
            uuid = UUID()
        }
        try super.insert(db)
    }
}

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

struct Link : Persistable {
    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.

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:

• At the table level

  // CREATE TABLE persons (
  //     id INTEGER PRIMARY KEY,
  //     email TEXT UNIQUE ON CONFLICT REPLACE
  // )
  try db.create(table: "persons") { t in
      t.column("id", .integer).primaryKey()
      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 persons (email) VALUES (?)", arguments: ["[email protected]"])
  try db.execute("INSERT INTO persons (email) VALUES (?)", arguments: ["[email protected]"])

• At the query level:

  // CREATE TABLE persons (
  //     id INTEGER PRIMARY KEY,
  //     email TEXT UNIQUE
  // )
  try db.create(table: "persons") { t in
      t.column("id", .integer).primaryKey()
      t.column("email", .text)
  }
  
  // Again, despite the unique index on email, both inserts succeed.
  try db.execute("INSERT OR REPLACE INTO persons (email) VALUES (?)", arguments: ["[email protected]"])
  try db.execute("INSERT OR REPLACE INTO persons (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 MutablePersistable or Persistable protocol. It will alter the INSERT and UPDATE queries run by the insert, update and save persistence methods:

public protocol MutablePersistable {
    /// 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, and has GRDB generate regular
    /// INSERT and UPDATE queries.
    static var persistenceConflictPolicy: PersistenceConflictPolicy { get }
}

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

// INSERT OR REPLACE INTO persons (...) VALUES (...)
try person.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 at the table level, 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.

⚠️ Warning: ON CONFLICT REPLACE may 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).

Record is a class that is designed to be subclassed, and provides the full GRDB Record toolkit in one go:

• Fetching methods (from the RowConvertible protocol)
• Persistence methods (from the Persistable protocol)
• The query interface (from the TableMapping protocol)
• Changes tracking (unique to the Record class)

Record subclasses override the four core methods that define their relationship with the database:

class Record {
    /// The table name
    class var databaseTableName: String { get }
    
    /// Initialize from a database row
    required init(row: Row)
    
    /// The values persisted in the database
    var persistentDictionary: [String: DatabaseValueConvertible?]
    
    /// Optionally update record ID after a successful insertion
    func didInsert(with rowID: Int64, for column: String?)
}

For example, here is a fully functional Record subclass:

class PointOfInterest : Record {
    var id: Int64?
    var title: String
    var coordinate: CLLocationCoordinate2D
    
    /// The table name
    override class var databaseTableName: String {
        return "pointOfInterests"
    }
    
    /// Initialize from a database row
    required init(row: Row) {
        id = row.value(named: "id")
        title = row.value(named: "title")
        coordinate = CLLocationCoordinate2DMake(
            row.value(named: "latitude"),
            row.value(named: "longitude"))
        super.init(row: row)
    }
    
    /// The values persisted in the database
    override var persistentDictionary: [String: DatabaseValueConvertible?] {
        return [
            "id": id,
            "title": title,
            "latitude": coordinate.latitude,
            "longitude": coordinate.longitude]
    }
    
    /// Update record ID after a successful insertion
    override func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

Insert records (see persistence methods):

let poi = PointOfInterest(...)
try poi.insert(db)

Fetch records (see RowConvertible and the query interface):

// Using the query interface
let pois = PointOfInterest.order(titleColumn).fetchAll(db)

// By key
let poi = PointOfInterest.fetchOne(db, key: 1)

// Using SQL
let pois = PointOfInterest.fetchAll(db, "SELECT ...", arguments: ...)

Update records (see persistence methods):

let poi = PointOfInterest.fetchOne(db, key: 1)!
poi.coordinate = ...
try poi.update(db)

Delete records (see persistence methods):

let poi = PointOfInterest.fetchOne(db, key: 1)!
try poi.delete(db)

The Record class provides changes tracking.

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

Avoid it with the hasPersistentChangedValues property, which returns whether the record has changes that have not been saved:

// Saves the person if it has changes that have not been saved:
if person.hasPersistentChangedValues {
    try person.save(db)
}

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

let person = Person(name: "Barbara", age: 35)
person.hasPersistentChangedValues // true

try person.insert(db)
person.hasPersistentChangedValues // false

person.name = "Barbara"
person.hasPersistentChangedValues // false

person.age = 36
person.hasPersistentChangedValues // true
person.persistentChangedValues    // ["age": 35]

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

The query interface lets you write pure Swift instead of SQL:

// Update database schema
try db.create(table: "wines") { t in ... }

// Fetch
let wines = Wine.filter(origin == "Burgundy").order(price).fetchAll(db)

// Count
let count = Wine.filter(color == Color.red).fetchCount(db)

// Delete
try Wine.filter(corked == true).deleteAll(db)

Please bear in mind that the query interface can not generate all possible SQL queries. You may also prefer writing SQL, and this is just OK. From little snippets to full queries, your SQL skills are welcome:

try db.execute("CREATE TABLE wines (...)")
let count = Wine.filter(sql: "color = ?", arguments: [Color.red]).fetchCount(db)
let wines = Wine.fetchAll(db, "SELECT * FROM wines WHERE origin = ? ORDER BY price", arguments: ["Burgundy"])
try db.execute("DELETE FROM wines WHERE corked")

So don't miss the SQL API.

• Database Schema
• Requests
• Expressions
  • SQL Operators
  • SQL Functions
• Fetching from Requests
• Fetching by Primary Key
• Fetching Aggregated Values
• Delete Requests
• GRDB Extension Guide

Once granted with a database connection, you can setup your database schema without writing SQL:

• Create Tables
• Modify Tables
• Drop Tables
• Create Indexes

// CREATE TABLE pointOfInterests (
//   id INTEGER PRIMARY KEY,
//   title TEXT,
//   favorite BOOLEAN NOT NULL DEFAULT 0,
//   latitude DOUBLE NOT NULL,
//   longitude DOUBLE NOT NULL
// )
try db.create(table: "pointOfInterests") { t in
    t.column("id", .integer).primaryKey()
    t.column("title", .text)
    t.column("favorite", .boolean).notNull().defaults(to: false)
    t.column("longitude", .double).notNull()
    t.column("latitude", .double).notNull()
}

The create(table:) method covers nearly all SQLite table creation features.

Relevant SQLite documentation:

• CREATE TABLE
• Datatypes In SQLite Version 3
• SQLite Foreign Key Support
• ON CONFLICT
• The WITHOUT ROWID Optimization

Configure table creation:

// CREATE TABLE example ( ... )
try db.create(table: "example") { t in ... }
    
// CREATE TEMPORARY TABLE example IF NOT EXISTS (
try db.create(table: "example", temporary: true, ifNotExists: true) { t in

Add regular columns with their name and type (text, integer, double, numeric, boolean, blob, date and datetime) - see SQLite data types:

    // name TEXT,
    // creationDate DATETIME,
    t.column("name", .text)
    t.column("creationDate", .datetime)

Define not null columns, and set default values:

    // email TEXT NOT NULL,
    t.column("email", .text).notNull()
    
    // name TEXT NOT NULL DEFAULT 'Anonymous',
    t.column("name", .text).notNull().defaults(to: "Anonymous")

Use an individual column as primary, unique, or foreign key. When defining a foreign key, the referenced column is the primary key of the referenced table (unless you specify otherwise):

    // id INTEGER PRIMARY KEY,
    t.column("id", .integer).primaryKey()
    
    // email TEXT UNIQUE,
    t.column("email", .text).unique()
    
    // countryCode TEXT REFERENCES countries(code) ON DELETE CASCADE,
    t.column("countryCode", .text).references("countries", onDelete: .cascade)

Perform integrity checks on individual columns, and SQLite will only let conforming rows in. In the example below, the $0 closure variable is a column which lets you build any SQL expression.

    // name TEXT CHECK (LENGTH(name) > 0)
    // age INTEGER CHECK (age > 0)
    t.column("name", .text).check { length($0) > 0 }
    t.column("age", .integer).check(sql: "age > 0")

Other table constraints can involve several columns:

    // PRIMARY KEY (a, b),
    t.primaryKey(["a", "b"])
    
    // UNIQUE (a, b) ON CONFLICT REPLACE,
    t.uniqueKey(["a", "b"], onConfict: .replace)
    
    // FOREIGN KEY (a, b) REFERENCES parents(c, d),
    t.foreignKey(["a", "b"], references: "parent")
    
    // CHECK (a + b < 10),
    t.check(Column("a") + Column("b") < 10)
    
    // CHECK (a + b < 10)
    t.check(sql: "a + b < 10")
}

SQLite lets you rename tables, and add columns to existing tables:

// ALTER TABLE referers RENAME TO referrers
try db.rename(table: "referers", to: "referrers")

// ALTER TABLE persons ADD COLUMN url TEXT
try db.alter(table: "persons") { t in
    t.add(column: "url", .text)
}

☝️ Note: SQLite restricts the possible table alterations, and may require you to recreate dependent triggers or views. See the documentation of the ALTER TABLE for details. See Advanced Database Schema Changes for a way to lift restrictions.

Drop tables with the drop(table:) method:

try db.drop(table: "obsolete")

Create indexes with the create(index:) method:

// CREATE UNIQUE INDEX byEmail ON users(email)
try db.create(index: "byEmail", on: "users", columns: ["email"], unique: true)

Relevant SQLite documentation:

• CREATE INDEX
• Indexes On Expressions
• Partial Indexes

The query interface requests let you fetch values from the database:

let request = Person.filter(emailColumn != nil).order(nameColumn)
let persons = request.fetchAll(db)  // [Person]
let count = request.fetchCount(db)  // Int

All requests start from a type that adopts the TableMapping protocol, such as a Record subclass (see Records):

class Person : Record { ... }

Declare the table columns that you want to use for filtering, or sorting:

let idColumn = Column("id")
let nameColumn = Column("name")

You can now build requests with the following methods: all, select, distinct, filter, group, having, order, reversed, limit. All those methods return another request, which you can further refine by applying another method: Person.select(...).filter(...).order(...).

• all(): the request for all rows.

  // SELECT * FROM persons
  Person.all()

• select(expression, ...) defines the selected columns.

  // SELECT id, name FROM persons
  Person.select(idColumn, nameColumn)
  
  // SELECT MAX(age) AS maxAge FROM persons
  Person.select(max(ageColumn).aliased("maxAge"))

• distinct() performs uniquing:

  // SELECT DISTINCT name FROM persons
  Person.select(nameColumn).distinct()

• filter(expression) applies conditions.

  // SELECT * FROM persons WHERE id IN (1, 2, 3)
  Person.filter([1,2,3].contains(idColumn))
  
  // SELECT * FROM persons WHERE (name IS NOT NULL) AND (height > 1.75)
  Person.filter(nameColumn != nil && heightColumn > 1.75)

• group(expression, ...) groups rows.

  // SELECT name, MAX(age) FROM persons GROUP BY name
  Person
      .select(nameColumn, max(ageColumn))
      .group(nameColumn)

• having(expression) applies conditions on grouped rows.

  // SELECT name, MAX(age) FROM persons GROUP BY name HAVING MIN(age) >= 18
  Person
      .select(nameColumn, max(ageColumn))
      .group(nameColumn)
      .having(min(ageColumn) >= 18)

• order(ordering, ...) sorts.

  // SELECT * FROM persons ORDER BY name
  Person.order(nameColumn)
  
  // SELECT * FROM persons ORDER BY score DESC, name
  Person.order(scoreColumn.desc, nameColumn)

  Each order call clears any previous ordering:

  // SELECT * FROM persons ORDER BY name
  Person.order(scoreColumn).order(nameColumn)

• reversed() reverses the eventual orderings.

  // SELECT * FROM persons ORDER BY score ASC, name DESC
  Person.order(scoreColumn.desc, nameColumn).reversed()

  If no ordering was specified, the result is ordered by rowID in reverse order.

  // SELECT * FROM persons ORDER BY _rowid_ DESC
  Person.all().reversed()

• limit(limit, offset: offset) limits and pages results.

  // SELECT * FROM persons LIMIT 5
  Person.limit(5)
  
  // SELECT * FROM persons LIMIT 5 OFFSET 10
  Person.limit(5, offset: 10)

You can refine requests by chaining those methods:

// SELECT * FROM persons WHERE (email IS NOT NULL) ORDER BY name
Person.order(nameColumn).filter(emailColumn != nil)

The select, order, group, and limit methods ignore and replace previously applied selection, orderings, grouping, and limits. On the opposite, filter, and having methods extend the query:

Person                          // SELECT * FROM persons
    .filter(nameColumn != nil)  // WHERE (name IS NOT NULL)
    .filter(emailColumn != nil) //        AND (email IS NOT NULL)
    .order(nameColumn)          // - ignored -
    .order(ageColumn)           // ORDER BY age
    .limit(20, offset: 40)      // - ignored -
    .limit(10)                  // LIMIT 10

Raw SQL snippets are also accepted, with eventual arguments:

// SELECT DATE(creationDate), COUNT(*) FROM persons WHERE name = 'Arthur' GROUP BY date(creationDate)
Person
    .select(sql: "DATE(creationDate), COUNT(*)")
    .filter(sql: "name = ?", arguments: ["Arthur"])
    .group(sql: "DATE(creationDate)")

Feed requests with SQL expressions built from your Swift code:

• =, <>, <, <=, >, >=, IS, IS NOT

  Comparison operators are based on the Swift operators ==, !=, ===, !==, <, <=, >, >=:

  // SELECT * FROM persons WHERE (name = 'Arthur')
  Person.filter(nameColumn == "Arthur")
  
  // SELECT * FROM persons WHERE (name IS NULL)
  Person.filter(nameColumn == nil)
  
  // SELECT * FROM persons WHERE (age === 18)
  Person.filter(ageColumn === 18)
  
  // SELECT * FROM rectangles WHERE width < height
  Rectangle.filter(widthColumn < heightColumn)

  ☝️ Note: SQLite string comparison, by default, is case-sensitive and not Unicode-aware. See string comparison if you need more control.

• LIKE

  The SQLite LIKE operator is available as the like method:

  // SELECT * FROM persons WHERE (email LIKE '%@example.com')
  Person.filter(emailColumn.like("%@example.com"))

  ☝️ Note: the SQLite LIKE operator is case-unsensitive but not Unicode-aware. For example, the expression 'a' LIKE 'A' is true but 'æ' LIKE 'Æ' is false.

• *, /, +, -

  SQLite arithmetic operators are derived from their Swift equivalent:

  // SELECT ((temperature * 1.8) + 32) AS farenheit FROM persons
  Planet.select((temperatureColumn * 1.8 + 32).aliased("farenheit"))

  ☝️ Note: an expression like nameColumn + "rrr" will be interpreted by SQLite as a numerical addition (with funny results), not as a string concatenation.

• AND, OR, NOT

  The SQL logical operators are derived from the Swift &&, || and !:

  // SELECT * FROM persons WHERE ((NOT verified) OR (age < 18))
  Person.filter(!verifiedColumn || ageColumn < 18)

• BETWEEN, IN, IN (subquery), NOT IN, NOT IN (subquery)

  To check inclusion in a collection, call the contains method on any Swift sequence:

  // SELECT * FROM persons WHERE id IN (1, 2, 3)
  Person.filter([1, 2, 3].contains(idColumn))
  
  // SELECT * FROM persons WHERE id NOT IN (1, 2, 3)
  Person.filter(![1, 2, 3].contains(idColumn))
  
  // SELECT * FROM persons WHERE age BETWEEN 0 AND 17
  Person.filter((0..<18).contains(ageColumn))
  
  // SELECT * FROM persons WHERE age BETWEEN 0 AND 17
  Person.filter((0...17).contains(ageColumn))
  
  // SELECT * FROM persons WHERE name BETWEEN 'A' AND 'z'
  Person.filter(("A"..."z").contains(nameColumn))
  
  // SELECT * FROM persons WHERE (name >= 'A') AND (name < 'z')
  Person.filter(("A"..<"z").contains(nameColumn))

  ☝️ Note: SQLite string comparison, by default, is case-sensitive and not Unicode-aware. See string comparison if you need more control.

  To check inclusion in a subquery, call the contains method on another request:

  // SELECT * FROM events
  //  WHERE userId IN (SELECT id FROM persons WHERE verified)
  let verifiedUserIds = User.select(idColumn).filter(verifiedColumn)
  Event.filter(verifiedUserIds.contains(userIdColumn))

• EXISTS (subquery), NOT EXISTS (subquery)

  To check is a subquery would return any row, use the exists property on another request:

  // SELECT * FROM persons
  // WHERE EXISTS (SELECT * FROM books
  //                WHERE books.ownerId = persons.id)
  Person.filter(Book.filter(sql: "books.ownerId = persons.id").exists)