### Documentation/DemoApps/GRDBDemo/README
GRDBDemo Application
====================
**GRDBDemo demonstrates how GRDB can fuel a SwiftUI application.**
> **Note**: This demo app is not a project template. Do not copy it as a starting point for your application. Instead, create a new project, choose a GRDB [installation method](../../../README.md#installation), and use the demo as an inspiration.
The topics covered in this demo are:
- How to setup a database in an iOS app.
- How to define a simple [Codable Record](../../../README.md#codable-records).
- How to track database changes and animate a SwiftUI List with [ValueObservation](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservation).
- How to apply the recommendations of [Recommended Practices for Designing Record Types](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/recordrecommendedpractices).
- How to feed SwiftUI previews with a transient database.
**Files of interest:**
- [GRDBDemoApp.swift](GRDBDemo/GRDBDemoApp.swift)
`GRDBDemoApp` feeds the SwiftUI app with a database, through the SwiftUI environment.
- [AppDatabase.swift](GRDBDemo/Database/AppDatabase.swift)
`AppDatabase` is the type that grants database access. It uses [DatabaseMigrator](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasemigrator) in order to setup the database schema, and provides methods that read and write.
`AppDatabase` is [tested](GRDBDemoTests/AppDatabaseTests.swift).
- [Persistence.swift](GRDBDemo/Database/Persistence.swift)
This file instantiates various `AppDatabase` for the various projects needs: one database on disk for the application, and in-memory databases for SwiftUI previews.
- [Player.swift](GRDBDemo/Database/Models/Player.swift)
`Player` is a [Record](../../../README.md#records) type, able to read and write in the database. It conforms to the standard Codable protocol in order to gain all advantages of [Codable Records](../../../README.md#codable-records).
- [PlayerListModel.swift](GRDBDemo/Views/PlayerListModel.swift)
`PlayerListModel` is an `@Observable` object that observes the database, displays always fresh values on screen, and performs actions.
`PlayerListModel` is [tested](GRDBDemoTests/PlayerListModelTests.swift).
- [PlayersNavigationView.swift](GRDBDemo/Views/PlayersNavigationView.swift)
`PlayersNavigationView` is the main navigation view of the application. It instantiates a `PlayerListModel` from the `AppDatabase` stored in the SwiftUI environment.
---
### Documentation/DemoApps/README
Demo Applications
=================
[GRDBDemo] demonstrates how GRDB can fuel a SwiftUI application.
See also the demo apps of the [GRDBQuery] package: they use the `@Query` property wrapper that helps SwiftUI views automatically update their content when the database changes.
[GRDBDemo]: GRDBDemo
[GRDBQuery]: https://github.com/groue/GRDBQuery
---
### Documentation/AppGroupContainers
Sharing a Database in an App Group Container
============================================
This guide [has moved](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasesharing).
---
### Documentation/AssociationsBasics
GRDB Associations
=================
- [Associations Benefits]
- [Required Protocols]
- [The Types of Associations]
- [BelongsTo]
- [HasMany]
- [HasOne]
- [HasManyThrough]
- [HasOneThrough]
- [Choosing Between BelongsTo and HasOne]
- [Self Joins]
- [Associations to Common Table Expressions]
- [Associations and the Database Schema]
- [Convention for Database Table Names]
- [Convention for the BelongsTo Association]
- [Convention for the HasOne Association]
- [Convention for the HasMany Association]
- [Foreign Keys]
- [Building Requests from Associations]
- [Requesting Associated Records]
- [Joining And Prefetching Associated Records]
- [Combining Associations]
- [Filtering Associations]
- [Sorting Associations]
- [Ordered Associations]
- [Columns Selected by an Association]
- [Further Refinements to Associations]
- [Table Aliases]
- [Refining Association Requests]
- [Fetching Values from Associations]
- [The Structure of a Joined Request]
- [Decoding a Joined Request with a Decodable Record]
- [Decoding a Joined Request with FetchableRecord]
- [Debugging Request Decoding]
- [Association Aggregates]
- [Available Association Aggregates]
- [Annotating a Request with Aggregates]
- [Filtering a Request with Aggregates]
- [Aggregate Operations]
- [Isolation of Multiple Aggregates]
- [DerivableRequest Protocol]
**[FAQ]**
- [How do I filter records and only keep those that are associated to another record?](../README.md#how-do-i-filter-records-and-only-keep-those-that-are-associated-to-another-record)
- [How do I filter records and only keep those that are NOT associated to another record?](../README.md#how-do-i-filter-records-and-only-keep-those-that-are-not-associated-to-another-record)
- [How do I select only one column of an associated record?](../README.md#how-do-i-select-only-one-column-of-an-associated-record)
**[Known Issues]**
## Associations Benefits
**An association is a connection between two [Record] types.**
Associations streamline common operations in your code, make them safer, and more efficient. For example, consider a library application that has two record types, author and book:
```swift
struct Author: Codable, Identifiable, FetchableRecord, PersistableRecord {
var id: Int64
var name: String
enum Columns {
static let id = Column(CodingKeys.id)
static let name = Column(CodingKeys.name)
}
}
struct Book: Codable, Identifiable, FetchableRecord, PersistableRecord {
var id: Int64
var authorId: Int64?
var title: String
enum Columns {
static let id = Column(CodingKeys.id)
static let authorId = Column(CodingKeys.authorId)
static let title = Column(CodingKeys.title)
}
}
```
Now, suppose we wanted to load all books from an existing author. We'd need to do something like this:
```swift
let author: Author = ...
let books = try Book
.filter { $0.authorId == author.id }
.fetchAll(db)
```
Or, loading all pairs of books along with their authors:
```swift
struct BookInfo {
var book: Book
var author: Author?
}
let books = try Book.fetchAll(db)
let bookInfos = books.map { book -> BookInfo in
let author = try Author.fetchOne(db, id: book.authorId)
return BookInfo(book: book, author: author)
}
```
With GRDB associations, we can streamline these operations (and others), by declaring the connections between books and authors. Here is how we define associations, and properties that access them:
```swift
extension Author {
static let books = hasMany(Book.self)
var books: QueryInterfaceRequest {
request(for: Author.books)
}
}
extension Book {
static let author = belongsTo(Author.self)
var author: QueryInterfaceRequest {
request(for: Book.author)
}
}
```
Loading all books from an existing author is now easier:
```swift
let books = try author.books.fetchAll(db)
```
As for loading all pairs of books and authors, it is not only easier, but also *much more efficient*:
```swift
struct BookInfo: Decodable, FetchableRecord {
let book: Book
let author: Author?
}
let request = Book.including(optional: Book.author)
let bookInfos = BookInfo.fetchAll(db, request)
```
Before we dive in, please remember that associations can not generate all possible SQL queries that involve several tables. You may also *prefer* writing SQL, and this is just OK, because your SQL skills are welcome. The [`splittingRowAdapters(columnCounts:)`](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/splittingrowadapters(columncounts:)) method can help you consume the rows fetched from joined queries, as in `SELECT book.*, author.* FROM ...`.
## Required Protocols
**Associations are available on types that adopt the necessary supporting protocols.**
Associations are based on the [TableRecord], [FetchableRecord], and [EncodableRecord] protocols:
- **[TableRecord]** is the protocol that lets you declare associations between record types:
```swift
extension Author: TableRecord {
static let books = hasMany(Book.self)
}
extension Book: TableRecord {
static let author = belongsTo(Author.self)
}
```
- **[FetchableRecord]** makes it possible to fetch records from the database:
```swift
extension Author: FetchableRecord { }
// Who's prolific?
let authors = try dbQueue.read { db in
try Author
.having(Author.books.count >= 20)
.fetchAll(db) // [Author]
}
```
FetchableRecord conformance can be derived from the standard Decodable protocol. See [Codable Records] for more information.
- **[EncodableRecord]** makes it possible to fetch associated records with the `request(for:)` method:
```swift
extension Book: EncodableRecord {
// The request for the author of a book.
var author: QueryInterfaceRequest {
request(for: Book.author)
}
}
// Who wrote this book?
let book: Book = ...
let author = try dbQueue.read { db in
try book.author.fetchOne(db) // Author?
}
```
A record type can conform to EncodableRecord via the [PersistableRecord] protocol. However, PersistableRecord also grants [persistence methods], the ones that are able to insert, update, and delete rows in the database. When you'd rather keep a record type read-only, and yet profit from associations, all you need is EncodableRecord.
EncodableRecord conformance can be derived from the standard Encodable protocol. See [Codable Records] for more information.
The Types of Associations
=========================
GRDB handles several types of associations:
- **BelongsTo**
- **HasMany**
- **HasOne**
- **HasManyThrough**
- **HasOneThrough**
- **Associations to common table expressions**
An association generally declares a link from a record type to another, as in "one book **belongs to** its author". It instructs GRDB to use the foreign keys declared in the database as support for Swift methods.
Each one of these associations is appropriate for a particular database situation.
Associations to [common table expressions] are specific enough and are documented in [Associations to Common Table Expressions].
- [BelongsTo]
- [HasMany]
- [HasOne]
- [HasManyThrough]
- [HasOneThrough]
- [Choosing Between BelongsTo and HasOne]
- [Self Joins]
## BelongsTo
The **BelongsTo** association sets up a one-to-one connection from a record type to another record type, such as each instance of the declaring record "belongs to" an instance of the other record.
For example, if your application includes authors and books, and each book is assigned its author, you'd declare the `Book.author` association as below:
```swift
struct Book: TableRecord {
static let author = belongsTo(Author.self)
...
}
struct Author: TableRecord {
...
}
```
The **BelongsTo** association between a book and its author needs that the database table for books has a column that points to the table for authors:
See [Convention for the BelongsTo Association] for some sample code that defines the database schema for such an association, and [Building Requests from Associations] in order to learn how to use it.
## HasMany
The **HasMany** association indicates a one-to-many connection between two record types, such as each instance of the declaring record "has many" instances of the other record. You'll often find this association on the other side of a **BelongsTo** association.
For example, if your application includes authors and books, and each author is assigned zero or more books, you'd declare the `Author.books` association as below:
```swift
struct Author: TableRecord {
static let books = hasMany(Book.self)
}
struct Book: TableRecord {
...
}
```
The **HasMany** association between an author and its books needs that the database table for books has a column that points to the table for authors:
See [Convention for the HasMany Association] for some sample code that defines the database schema for such an association, and [Building Requests from Associations] in order to learn how to use it.
## HasOne
The **HasOne** association, like BelongsTo, sets up a one-to-one connection from a record type to another record type, but with different semantics, and underlying database schema. It is usually used when an entity has been denormalized into two database tables.
For example, if your application has one database table for countries, and another for their demographic profiles, you'd declare the `Country.demographics` association as below:
```swift
struct Country: TableRecord {
static let demographics = hasOne(Demographics.self, key: "demographics")
...
}
struct Demographics: TableRecord {
...
}
```
The **HasOne** association between a country and its demographics needs that the database table for demographics has a column that points to the table for countries:
Note that this demographics example of HasOne association uses an explicit `"demographics"` key, unlike the BelongsTo and HasMany associations above. This key is necessary when you use a plural name for a one-to-one association. See [Convention for Database Table Names] for more information.
See [Convention for the HasOne Association] for some sample code that defines the database schema for such an association, and [Building Requests from Associations] in order to learn how to use it.
## HasManyThrough
The **HasManyThrough** association is often used to set up a many-to-many connection with another record. This association indicates that the declaring record can be matched with zero or more instances of another record by proceeding through a third record. For example, consider the practice of passport delivery. The relevant association declarations could look like this:
```swift
struct Country: TableRecord {
static let passports = hasMany(Passport.self)
static let citizens = hasMany(Citizen.self, through: passports, using: Passport.citizen)
...
}
struct Passport: TableRecord {
static let country = belongsTo(Country.self)
static let citizen = belongsTo(Citizen.self)
}
struct Citizen: TableRecord {
static let passports = hasMany(Passport.self)
static let countries = hasMany(Country.self, through: passports, using: Passport.country)
...
}
```
The **HasManyThrough** association is also useful for setting up "shortcuts" through nested associations. For example, if a document has many sections, and a section has many paragraphs, you may sometimes want to get a simple collection of all paragraphs in the document. You could set that up this way:
```swift
struct Document: TableRecord {
static let sections = hasMany(Section.self)
static let paragraphs = hasMany(Paragraph.self, through: sections, using: Section.paragraphs)
...
}
struct Section: TableRecord {
static let paragraphs = hasMany(Paragraph.self)
...
}
struct Paragraph: TableRecord {
...
}
```
As in the examples above, **HasManyThrough** association is always built from two other associations: the `through:` and `using:` arguments. Those associations can be any other association (BelongsTo, HasMany, HasManyThrough, etc). The above `Document.paragraphs` association can also be defined, in a much more explicit way, as below:
```swift
struct Document: TableRecord {
static let paragraphs = hasMany(
Paragraph.self,
through: Document.hasMany(Section.self),
using: Section.hasMany(Paragraph.self))
...
}
```
See [Building Requests from Associations] in order to learn how to use the HasManyThrough association.
## HasOneThrough
A **HasOneThrough** association sets up a one-to-one connection with another record. This association indicates that the declaring record can be matched with one instance of another record by proceeding through a third record. For example, if each book belongs to a library, and each library has one address, then one knows where the book should be returned to:
```swift
struct Book: TableRecord {
static let library = belongsTo(Library.self)
static let returnAddress = hasOne(Address.self, through: library, using: Library.address)
...
}
struct Library: TableRecord {
static let address = hasOne(Address.self)
...
}
struct Address: TableRecord {
...
}
```
As in the example above, **HasOneThrough** association is always built from two other associations: the `through:` and `using:` arguments. Those associations can be any other association to one (BelongsTo, HasOne, HasOneThrough). The above `Book.returnAddress` association can also be defined, in a much more explicit way, as below:
```swift
struct Book: TableRecord {
static let returnAddress = hasOne(
Address.self,
through: Book.belongsTo(Library.self),
using: Library.hasOne(Address.self))
...
}
```
See [Building Requests from Associations] in order to learn how to use the HasOneThrough association.
## Choosing Between BelongsTo and HasOne
When you want to set up a one-to-one relationship between two record types, you'll need to add a **BelongsTo** association to one, and a **HasOne** association to the other. How do you know which is which?
The distinction is in where you place the database foreign key. The record that points to the other one has the **BelongsTo** association. The other record has the **HasOne** association:
A country **has one** demographic profile, a demographic profile **belongs to** a country:
```swift
struct Country: TableRecord {
static let demographics = hasOne(Demographics.self)
...
}
struct Demographics: TableRecord {
static let country = belongsTo(Country.self)
...
}
```
## Self Joins
When designing your data model, you will sometimes find a record that should have a relation to itself. For example, you may want to store all employees in a single database table, but be able to trace relationships such as between manager and subordinates. This situation can be modeled with self-joining associations:
```swift
struct Employee {
static let subordinates = hasMany(Employee.self, key: "subordinates")
static let manager = belongsTo(Employee.self, key: "manager")
...
}
```
The matching [migration] would look like:
```swift
migrator.registerMigration("Employees") { db in
try db.create(table: "employee") { t in
t.autoIncrementedPrimaryKey("id")
t.belongsTo("manager", inTable: "employee", onDelete: .setNull)
t.column("name", .text)
}
}
```
Note that the associations on both sides of the self-join use a customized **[association key](#the-structure-of-a-joined-request)**. This helps consuming this association. For example:
```swift
struct EmployeeInfo: FetchableRecord, Decodable {
var employee: Employee
var manager: Employee?
var subordinates: Set
}
let request = Employee
.including(optional: Employee.manager)
.including(all: Employee.subordinates)
let employeeInfos: [EmployeeInfo] = try EmployeeInfo.fetchAll(db, request)
```
See [Fetching Values from Associations] for more information.
Associations and the Database Schema
====================================
**Associations are grounded in the database schema, the way database tables are defined.**
For example, a **[BelongsTo]** association between a book and its author needs that the database table for books has a column that points to the table for authors.
GRDB also comes with several *conventions* for defining your database schema.
Those conventions help associations be convenient and, generally, "just work". When you can't, or don't want to follow conventions, you will have to override the expected defaults in your Swift code.
- [Convention for Database Table Names]
- [Convention for the BelongsTo Association]
- [Convention for the HasMany Association]
- [Convention for the HasOne Association]
- [Foreign Keys]
## Convention for Database Table Names
**Database table names should be written in English, singular, and camelCased.**
Make them look like Swift identifiers: `book`, `author`, `postalAddress`.
If the database schema does not follow this convention, and has, for example, database tables which are named with underscores (`postal_address`), you can still use associations. But you need to help row consumption by naming your associations with a customized key:
```swift
// Setup for table names that does not follow the expected convention
struct PostalAddress: TableRecord {
// Customized table name
static let databaseTableName = "postal_address"
}
extension Author {
// Customized association key
static let postalAddress = belongsTo(PostalAddress.self, key: "postalAddress")
}
```
GRDB will automatically **pluralize** or **singularize** names in order to help you easily associate records.
For example, the Book and Author records will automatically feed properties named `books`, `author`, or `bookCount` in your decoded records, without any explicit configuration, as long as the names of the backing database tables are "book" and "author".
The GRDB pluralization mechanisms are very powerful, being capable of pluralizing and singularizing both regular and irregular words (it's directly inspired from the battle-tested [Ruby on Rails inflections](https://api.rubyonrails.org/classes/ActiveSupport/Inflector.html#method-i-pluralize)).
When using class names composed of two or more words, the table name should use the camelCase singular form:
| RecordType | Table Name | Derived identifiers |
| ---------- | ---------- | ------------------- |
| Book | book | `book`, `books`, `bookCount` |
| LineItem | lineItem | `lineItem`, `lineItems`, `lineItemPriceSum` |
| Mouse | mouse | `mouse`, `mice`, `maxMouseSize` |
| Person | person | `person`, `people`, `personCount` |
If your application relies on non-English names, GRDB may generate unexpected identifiers. If this happens, please [open an issue](https://github.com/groue/GRDB.swift/issues).
See [The Structure of a Joined Request] for more information.
## Convention for the BelongsTo Association
```swift
extension Book: TableRecord {
static let author = belongsTo(Author.self)
}
```
Here is the recommended [migration] for the **[BelongsTo]** association:
```swift
migrator.registerMigration("Books and Authors") { db in
try db.create(table: "author") { t in
t.autoIncrementedPrimaryKey("id") // (1)
t.column("name", .text)
}
try db.create(table: "book") { t in
t.autoIncrementedPrimaryKey("id")
t.belongsTo("author", onDelete: .cascade) // (2)
.notNull() // (3)
t.column("title", .text)
}
}
```
1. The `author` table has a primary key.
2. The `book.authorId` column is used to link a book to the author it belongs to. This column is indexed in order to ease the selection of an author's books. A foreign key is defined from `book.authorId` column to `authors.id`, so that SQLite guarantees that no book refers to a missing author. The `onDelete: .cascade` option has SQLite automatically delete all of an author's books when that author is deleted. See [Foreign Key Actions] for more information.
3. Make the `book.authorId` column not null if you want SQLite to guarantee that all books have an author.
The example above uses auto-incremented primary keys. But generally speaking, all primary keys are supported, including composite primary keys that span several columns.
Following this convention lets you write, for example:
```swift
struct Book: TableRecord {
static let author = belongsTo(Author.self)
}
struct Author: TableRecord {
}
```
If the database schema does not follow this convention, and does not define foreign keys between tables, you can still use **BelongsTo** associations. But your help is needed to define the missing foreign key:
```swift
struct Book: TableRecord {
static let author = belongsTo(Author.self, using: ForeignKey(...))
}
```
See [Foreign Keys] for more information.
## Convention for the HasMany Association
```swift
extension Author: TableRecord {
static let books = hasMany(Book.self)
}
```
Here is the recommended [migration] for the **[HasMany]** association:
```swift
migrator.registerMigration("Books and Authors") { db in
try db.create(table: "author") { t in
t.autoIncrementedPrimaryKey("id") // (1)
t.column("name", .text)
}
try db.create(table: "book") { t in
t.autoIncrementedPrimaryKey("id")
t.belongsTo("author", onDelete: .cascade) // (2)
.notNull() // (3)
t.column("title", .text)
}
}
```
1. The `author` table has a primary key.
2. The `book.authorId` column is used to link a book to the author it belongs to. This column is indexed in order to ease the selection of an author's books. A foreign key is defined from `book.authorId` column to `authors.id`, so that SQLite guarantees that no book refers to a missing author. The `onDelete: .cascade` option has SQLite automatically delete all of an author's books when that author is deleted. See [Foreign Key Actions] for more information.
3. Make the `book.authorId` column not null if you want SQLite to guarantee that all books have an author.
The example above uses auto-incremented primary keys. But generally speaking, all primary keys are supported, including composite primary keys that span several columns.
Following this convention lets you write, for example:
```swift
struct Book: TableRecord {
}
struct Author: TableRecord {
static let books = hasMany(Book.self)
}
```
If the database schema does not follow this convention, and does not define foreign keys between tables, you can still use **HasMany** associations. But your help is needed to define the missing foreign key:
```swift
struct Author: TableRecord {
static let books = hasMany(Book.self, using: ForeignKey(...))
}
```
See [Foreign Keys] for more information.
## Convention for the HasOne Association
```swift
extension Country: TableRecord {
static let demographics = hasOne(Demographics.self)
}
```
Here is the recommended [migration] for the **[HasOne]** association:
```swift
migrator.registerMigration("Countries") { db in
try db.create(table: "country") { t in
t.primaryKey("code", .text) // (1)
t.column("name", .text)
}
try db.create(table: "demographics") { t in
t.autoIncrementedPrimaryKey("id")
t.belongsTo("country", onDelete: .cascade) // (2)
.notNull() // (3)
.unique() // (4)
t.column("population", .integer)
t.column("density", .double)
}
}
```
1. The `country` table has a primary key.
2. The `demographics.countryCode` column is used to link a demographic profile to the country it belongs to. This column is indexed in order to ease the selection of the demographics of a country. A foreign key is defined from `demographics.countryCode` column to `country.code`, so that SQLite guarantees that no profile refers to a missing country. The `onDelete: .cascade` option has SQLite automatically delete a profile when its country is deleted. See [Foreign Key Actions] for more information.
3. Make the `demographics.countryCode` column not null if you want SQLite to guarantee that all profiles are linked to a country.
4. Create a unique index on the `demographics.countryCode` column in order to guarantee the unicity of any country's profile.
The example above uses a string primary key for the "country" table. But generally speaking, all primary keys are supported, including composite primary keys that span several columns.
Following this convention lets you write, for example:
```swift
struct Country: TableRecord {
static let demographics = hasOne(Demographics.self)
}
struct Demographics: TableRecord {
}
```
If the database schema does not follow this convention, and does not define foreign keys between tables, you can still use HasOne associations. But your help is needed to define the missing foreign key:
```swift
struct Country: TableRecord {
static let demographics = hasOne(Demographics.self, using: ForeignKey(...))
}
```
See [Foreign Keys] for more information.
## Foreign Keys
**Associations can automatically infer the foreign keys that define how two database tables are linked together.**
In the example below, the `book.authorId` column is automatically used to link a book to its author, because the database schema defines a foreign key between the book and author database tables (see [Convention for the BelongsTo Association]).
```swift
struct Book: TableRecord {
static let author = belongsTo(Author.self)
}
struct Author: TableRecord {
static let books = hasMany(Book.self)
}
```
> **Note**: Generally speaking, all foreign keys are supported, including composite keys that span several columns.
>
> **Warning**: SQLite voids foreign key constraints when one or more of a foreign key column is NULL (see [SQLite Foreign Key Support](https://www.sqlite.org/foreignkeys.html)). GRDB does not match foreign keys that involve a NULL value either.
Sometimes the database schema does not define any foreign key. And sometimes, there are *several* foreign keys from a table to another.
```swift
// The migration that has created the above schema
migrator.registerMigration("Library") { db in
try db.create(table: "person") { t in
t.autoIncrementedPrimaryKey("id")
t.column("name", .text)
}
try db.create(table: "book") { t in
t.autoIncrementedPrimaryKey("id")
t.belongsTo("author", inTable: "person")
t.belongsTo("translator", inTable: "person")
t.column("title", .text)
}
}
```
When this happens, associations can't be automatically inferred from the database schema. GRDB will complain with a fatal error such as "Ambiguous foreign key from book to person", or "Could not infer foreign key from book to person".
Your help is needed. You have to instruct GRDB which foreign key to use:
```swift
struct Book: TableRecord {
// Table columns
enum Columns {
static let authorId = Column("authorId")
static let translatorId = Column("translatorId")
}
// Define foreign keys
static let authorForeignKey = ForeignKey([Columns.authorId])
static let translatorForeignKey = ForeignKey([Columns.translatorId])
// Use foreign keys to define associations:
static let author = belongsTo(Person.self, using: authorForeignKey)
static let translator = belongsTo(Person.self, using: translatorForeignKey)
}
```
Foreign keys are always defined from the table that contains the columns at the origin of the foreign key. Person's symmetric **HasMany** associations reuse Book's foreign keys:
```swift
struct Person: TableRecord {
static let writtenBooks = hasMany(Book.self, using: Book.authorForeignKey)
static let translatedBooks = hasMany(Book.self, using: Book.translatorForeignKey)
}
```
When the destination table of a foreign key does not define any primary key, you need to provide the full definition of a foreign key:
```swift
struct Book: TableRecord {
static let authorForeignKey = ForeignKey([Columns.authorId], to: [Person.Columns.id])
static let author = belongsTo(Person.self, using: authorForeignKey)
}
```
Building Requests from Associations
===================================
**Once you have defined associations, you can define fetch request that involve several record types.**
Fetch requests do not visit the database until you fetch values from them. This will be covered in [Fetching Values from Associations]. But before you can fetch anything, you have to describe what you want to fetch. This is the topic of this chapter.
- [Requesting Associated Records]
- [Joining And Prefetching Associated Records]
- [Combining Associations]
- [Filtering Associations]
- [Sorting Associations]
- [Ordered Associations]
- [Columns Selected by an Association]
- [Further Refinements to Associations]
- [Table Aliases]
- [Refining Association Requests]
## Requesting Associated Records
**You can use associations to build requests for associated records.**
For example, given a `Book.author` **[BelongsTo]** association, you can build a request for the author of a book with the `request(for:)` method. In the example below, we return this request from the `Book.author` property:
```swift
struct Book: TableRecord, EncodableRecord {
/// The association from a book to is author
static let author = belongsTo(Author.self)
/// The request for the author of a book
var author: QueryInterfaceRequest {
request(for: Book.author)
}
}
```
You can now fetch the author of a book:
```swift
let book: Book = ...
let author = try book.author.fetchOne(db) // Author?
```
All other associations, **[HasOne]**, **[HasMany]**, **[HasOneThrough]**, and **[HasManyThrough]**, can also build requests for associated records. For example:
```swift
struct Author: TableRecord, EncodableRecord {
/// The association from an author to its books
static let books = hasMany(Book.self)
/// The request for the books of an author
var books: QueryInterfaceRequest {
request(for: Author.books)
}
}
let author: Author = ...
let books = try author.books.fetchAll(db) // [Book]
```
Requests for associated records can be filtered and ordered like all [query interface requests]:
```swift
let novels = try author
.books
.filter { $0.kind == BookKind.novel }
.order(\.publishDate.desc)
.fetchAll(db) // [Book]
```
## Joining And Prefetching Associated Records
You build requests that involve associations with one of the following "joining methods":
- Prefetch associated records:
- [`including(required:)`]
- [`including(optional:)`]
- [`including(all:)`]
- Prefetch only a few columns of an associated record:
- [`annotated(withRequired:)`]
- [`annotated(withOptional:)`]
- [`including(all:)`]
- Join associated records without prefetching:
- [`joining(required:)`]
- [`joining(optional:)`]
- [Choosing a Joining Method Given the Shape of the Decoded Type]
### `including(required:)`
For example, fetch books along with their author:
```swift
// SELECT book.*, author.*
// FROM book
// JOIN author ON author.id = book.authorId
let request = Book.including(required: Book.author)
```
This method accepts any association. It has the base record fetched along with one associated record, which is "included" in the fetched results. When the associated record does not exist, records are not present in the fetched results: the associated record is "required".
**To fetch results from such a request**, you define a dedicated record type:
```swift
// Fetch all books along with their author
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var author: Author // The required associated record
}
let bookInfos = try Book
.including(required: Book.author)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
**The CodingKey for the `BookInfo.author` property must match the association key of the `Book.author` association.** The association key is, by default, the name of the associated database table. This can be configured with the association `forKey(_:)` method:
```swift
struct Author: TableRecord {
static let databaseTableName = "writer"
}
struct Book: TableRecord {
// Replace the default "writer" association key with "author"
static let author = belongsTo(Author.self).forKey("author")
}
// Fetch all books along with their author
struct BookInfo: Decodable, FetchableRecord {
var book: Book
var author: Author // Matches the "author" association key
}
let bookInfos = try Book
.including(required: Book.author)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
**When you only need a few columns of the associated record**, you can define a partial type with one property per selected column of the associated record:
```swift
// Fetch all books along with the name and country of their author
struct BookInfo: Decodable, FetchableRecord {
struct PartialAuthor: Decodable {
var name: String
var country: String
}
var book: Book
var author: PartialAuthor
}
let bookInfos = try Book
.including(required: Book.author.select { [$0.name, $0.country] })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
But if you'd rather avoid this extra partial type, prefer [`annotated(withRequired:)`]:
```swift
// Fetch all books along with the country of their author
struct BookInfo: Decodable, FetchableRecord {
var book: Book
var country: String
}
let bookInfos = try Book
.annotated(withRequired: Book.author.select(\.country)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
### `including(optional:)`
For example, fetch books along with their eventual author:
```swift
// SELECT book.*, author.*
// FROM book
// LEFT JOIN author ON author.id = book.authorId
let request = Book.including(optional: Book.author)
```
This method accepts any association. It has the base record fetched along with one associated record, which is "included" in the fetched results. The associated record can be missing: it is "optional".
**To fetch results from such a request**, you define a dedicated record type:
```swift
// Fetch all books along with their eventual author
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var author: Author? // The optional associated record
}
let bookInfos = try Book
.including(optional: Book.author)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
**The CodingKey for the `BookInfo.author` property must match the association key of the `Book.author` association.** See [`including(required:)`] for more information.
**When you only need a few columns of the associated record**, you can define a partial type with one property per selected column of the associated record:
```swift
// Fetch all books along with the name and country of their eventual author
struct BookInfo: Decodable, FetchableRecord {
struct PartialAuthor: Decodable {
var name: String
var country: String
}
var book: Book
var author: PartialAuthor?
}
let bookInfos = try Book
.including(optional: Book.author.select { [$0.name, $0.country] })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
But if you'd rather avoid this extra partial type, prefer [`annotated(withOptional:)`]:
```swift
// Fetch all books along with the country of their eventual author
struct BookInfo: Decodable, FetchableRecord {
var book: Book
var country: String?
}
let bookInfos = try Book
.annotated(withOptional: Book.author.select(\.country))
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
### `including(all:)`
For example, fetch authors along with their books:
```swift
// SELECT author.* FROM author
// SELECT book.* FROM book WHERE authorId IN (...)
let request = Author.including(all: Author.books)
```
This method accepts any to-many association ([HasMany] or [HasManyThrough]). It has the base record fetched along with all its associated records, which are "included" in the fetched results.
**To fetch results from such a request**, you define a dedicated record type:
```swift
// Fetch all authors along with their books
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author // The base record
var books: [Book] // A collection of associated records
}
let authorInfos = try Author
.including(all: Author.books)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
The associated records can be stored into an Array as in the above example, but also a Set, and generally speaking any decodable Swift collection.
**The CodingKey for the `AuthorInfo.books` property must match the association key of the `Author.books` association.** The association key is, by default, the pluralized name of the associated database table. This can be configured with the association `forKey(_:)` method:
```swift
struct Book: TableRecord {
static let databaseTableName = "publication"
}
struct Author: TableRecord {
// Replace the default "publications" association key with "books"
static let books = hasMany(Book.self).forKey("books")
}
// Fetch all authors along with their books
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var books: [Book] // Matches the "books" association key
}
let authorInfos = try Author
.including(all: Author.books)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
**When you only need a few columns of the associated records**, you can define a partial type with one property per selected column of the associated record:
```swift
// Fetch all authors along with the titles and years of their books
struct AuthorInfo: Decodable, FetchableRecord {
struct PartialBook: Decodable {
var title: String
var year: Int
}
var author: Author
var books: [PartialBook]
}
let authorInfos = try Author
.including(all: Author.books.select { [$0.title, $0.year] })
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
When you need a single column of the associated records, avoid the extra partial type, and instead use the association `forKey(_:)` method in order to match the name of the decoded property:
```swift
// Fetch all authors along with the titles of their books
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var bookTitles: [String]
}
let authorInfos = try Author
.including(all: Author.books
.select(\.title)
.forKey("bookTitles"))
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
### `annotated(withRequired:)`
For example, fetch books along with the name and country of their author:
```swift
// SELECT book.*, author.name, author.country
// FROM book
// JOIN author ON author.id = book.authorId
let request = Book.annotated(withRequired: Book.author.select { [$0.name, $0.country] })
```
This method accepts any association. The base record is annotated with the selected columns of one associated record. When the associated record does not exist, records are not present in the fetched results: the associated record is "required".
**To fetch results from such a request**, you define a dedicated record type:
```swift
// Fetch all books along with the name and country of their author
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var name: String // A column of the required associated record
var country: String // A column of the required associated record
}
let bookInfos = try Book
.annotated(withRequired: Book.author.select { [$0.name, $0.country] })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
If the name of a column of the associated record is also the name of a column of the base record, rename the associated column with the column `forKey(_:)` method, and accordingly rename the property of the decoded record type:
```swift
// Fetch all books along with the name of their author
struct BookInfo: Decodable, FetchableRecord {
var book: Book
var authorName: String
}
let bookInfos = try Book
.annotated(withRequired: Book.author.select { $0.name.forKey("authorName") })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
### `annotated(withOptional:)`
For example, fetch books along with the name and country of their eventual author:
```swift
// SELECT book.*, author.name, author.country
// FROM book
// LEFT JOIN author ON author.id = book.authorId
let request = Book.annotated(withOptional: Book.author.select { [$0.name, $0.country] })
```
This method accepts any association. The base record is annotated with the selected columns of one associated record. When the associated record does not exist, the columns of the associated record are NULL: the associated record is "optional".
**To fetch results from such a request**, you define a dedicated record type:
```swift
// Fetch all books along with the name of their eventual author
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var name: String? // A column of the optional associated record
var country: String? // A column of the optional associated record
}
let bookInfos = try Book
.annotated(withOptional: Book.author.select { [$0.name, $0.country] })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
If the name of a column of the associated record is also the name of a column of the base record, rename the associated column with the column `forKey(_:)` method, and accordingly rename the property of the decoded record type:
```swift
// Fetch all books along with the name of their eventual author
struct BookInfo: Decodable, FetchableRecord {
var book: Book
var authorName: String?
}
let bookInfos = try Book
.annotated(withOptional: Book.author.select { $0.name.forKey("authorName") })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
### `joining(required:)`
For example, fetch books by French authors:
```swift
// SELECT book.*
// FROM book
// JOIN author ON author.id = book.authorId
// AND author.country = 'France'
let request = Book.joining(required: Book.author.filter { $0.country == "France" })
```
This method accepts any association. It has the base record "joined" with one associated record, which is not included in the fetched results. When the associated record does not exist, the base record is not present in the fetched results: the associated record is "required".
To fetch results from such a request, you do not need to define a dedicated record type:
```swift
// Fetch all books by French authors
let books = try Book
.joining(required: Book.author.filter { $0.country == "France" })
.fetchAll(db)
```
### `joining(optional:)`
```swift
// SELECT book.*
// FROM book
// LEFT JOIN author ON author.id = book.authorId
let request = Book.joining(optional: Book.author)
```
This method accepts any association. It has the base record "joined" with one associated record, which is not included in the fetched results. The associated record can be missing: it is "optional".
This method has no observable effect unless the associated record is used in a way or another. We'll see examples later in this documentation.
### Choosing a Joining Method Given the Shape of the Decoded Type
In the description of the [joining methods] above, we have seen that you need to define dedicated record types in order to prefetch associated records. Each joining method needs a dedicated record type that has a specific shape.
In this chapter, we take the reversed perspective. We list various shapes of decoded record types. When you find the type you want, you'll know the joining method you need.
> **Note**: If you don't find the type you want, chances are that you are fighting the framework, and should reconsider your position. Your escape hatch is the low-level apis described in [Decoding a Joined Request with FetchableRecord].
- [`including(required:)`]
```swift
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var author: Author // The associated record
}
let bookInfos = try Book
.including(required: Book.author)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
```swift
struct BookInfo: Decodable, FetchableRecord {
struct PartialAuthor: Decodable {
var name: String
var country: String
}
var book: Book // The base record
var author: PartialAuthor // The partial associated record
}
let bookInfos = try Book
.including(required: Book.author.select { [$0.name, $0.country] })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
- [`annotated(withRequired:)`]
```swift
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var authorName: String // A column of the associated record
var country: String // A column of the associated record
}
let bookInfos = try Book
.annotated(withRequired: Book.author.select {
[$0.name.forKey("authorName"), $0.country]
})
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
- [`including(optional:)`]
```swift
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var author: Author? // The eventual associated record
}
let bookInfos = try Book
.including(optional: Book.author)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
```swift
struct BookInfo: Decodable, FetchableRecord {
struct PartialAuthor: Decodable {
var name: String
var country: String
}
var book: Book // The base record
var author: PartialAuthor? // The eventual partial associated record
}
let bookInfos = try Book
.including(optional: Book.author.select { [$0.name, $0.country] })
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
- [`annotated(withOptional:)`]
```swift
struct BookInfo: Decodable, FetchableRecord {
var book: Book // The base record
var authorName: String? // A column of the eventual associated record
var country: String? // A column of the eventual associated record
}
let bookInfos = try Book
.annotated(withOptional: Book.author.select {
[$0.name.forKey("authorName"), $0.country]
})
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
- [`including(all:)`]
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author // The base record
var books: [Book] // A collection of associated records
}
let authorInfos = try Author
.including(all: Author.books)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
```swift
struct AuthorInfo: Decodable, FetchableRecord {
struct PartialBook: Decodable {
var title: String
var year: Int
}
var author: Author // The base record
var books: [PartialBook] // A collection of partial associated records
}
let authorInfos = try Author
.including(all: Author.books.select { [$0.title, $0.year] })
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author // The base record
var bookTitles: [String] // A collection of one column of the associated records
}
let authorInfos = try Author
.including(all: Author.books
.select(\.title)
.forKey("bookTitles"))
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
## Combining Associations
**Associations can be combined in order to build more complex requests.**
You can join several associations in parallel:
```swift
// SELECT book.*, person1.*, person2.*
// FROM book
// JOIN person person1 ON person1.id = book.authorId
// LEFT JOIN person person2 ON person2.id = book.translatorId
let request = Book
.including(required: Book.author)
.including(optional: Book.translator)
// This request can feed the following record:
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Person
var translator: Person?
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
The request above fetches all books, along with their author and eventual translator.
You can chain associations in order to jump from a record to another:
```swift
// SELECT book.*, person.*, country.*
// FROM book
// JOIN person ON person.id = book.authorId
// LEFT JOIN country ON country.code = person.countryCode
let request = Book
.including(required: Book.author
.including(optional: Person.country))
// This request can feed the following record:
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Author
var country: Country?
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
The request above fetches all books, along with their author, and their author's country.
When you chain associations, you can avoid fetching intermediate tables by replacing the `including` method with `joining`. The request below fetches all books, along with their author's country, but does not include the intermediate authors in the fetched results:
```swift
// SELECT book.*, country.*
// FROM book
// LEFT JOIN person ON person.id = book.authorId
// LEFT JOIN country ON country.code = person.countryCode
let request = Book
.joining(optional: Book.author
.including(optional: Person.country))
// This request can feed the following record:
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var country: Country?
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
**[HasOneThrough]** and **[HasManyThrough]** associations provide a shortcut for those requests that skip intermediate tables:
```swift
// SELECT book.*, country.*
// FROM book
// LEFT JOIN person ON person.id = book.authorId
// LEFT JOIN country ON country.code = person.countryCode
let request = Book.including(optional: Book.country)
// This request can feed the following record:
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var country: Country?
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
> **Warning**: you can not currently chain a required association behind an optional association:
>
> ```swift
> // Not implemented
> let request = Book
> .joining(optional: Book.author
> .including(required: Person.country))
> ```
>
> This code compiles, but you'll get a runtime fatal error "Not implemented: chaining a required association behind an optional association". Future versions of GRDB may allow such requests.
## Filtering Associations
**You can filter associated records.**
The `filter(_:)`, `filter(id:)`, `filter(ids:)`, `filter(key:)` and `filter(keys:)` methods, that you already know for [filtering simple requests](../README.md#requests), can filter associated records as well:
```swift
// SELECT book.*
// FROM book
// JOIN person ON person.id = book.authorId
// AND person.countryCode = 'FR'
let frenchAuthor = Book.author.filter { $0.countryCode == "FR" }
let request = Book.joining(required: frenchAuthor)
// This request feeds the Book record:
let books: [Book] = try request.fetchAll(db)
```
The request above fetches all books written by a French author.
The one below fetches all authors along with their novels and poems:
```swift
let request = Author
.including(all: Author.book
.filter { $0.kind == "novel" }
.forKey("novels"))
.including(all: Author.book
.filter { $0.kind == "poems" }
.forKey("poems"))
// This request can feed the following record:
struct AuthorInfo: FetchableRecord, Decodable {
var author: Author
var novels: [Book]
var poems: [Book]
}
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
**There are more filtering options:**
- Filtering on conditions that involve several tables.
- Filtering in the WHERE clause instead of the ON clause (can be useful when you are skilled enough in SQL to make the difference).
Those extra filtering options require **[Table Aliases]**, introduced below.
## Sorting Associations
**You can sort fetched results according to associated records.**
The `order()` method, that you already know for [sorting simple requests](../README.md#requests), can sort associated records as well:
```swift
// SELECT book.*, person.*
// FROM book
// JOIN person ON person.id = book.authorId
// ORDER BY person.name
let sortedAuthor = Book.author.order(\.name)
let request = Book.including(required: sortedAuthor)
```
When you sort both the base record and the associated record, the request is sorted on the base record first, and on the associated record next:
```swift
// SELECT book.*, person.*
// FROM book
// JOIN person ON person.id = book.authorId
// ORDER BY book.publishDate DESC, person.name
let sortedAuthor = Book.author.order(\.name)
let request = Book
.including(required: sortedAuthor)
.order(\.publishDate.desc)
```
**There are more sorting options:**
- Sorting on expressions that involve several tables.
- Changing the order of the sorting terms (such as sorting on author name first, and then publish date).
Those extra sorting options require **[Table Aliases]**, introduced below.
## Ordered Associations
By default, **[HasMany]** or **[HasManyThrough]** associations are unordered: the order of associated records is undefined unless [explicitly specified](#sorting-associations) on each request.
But you can build an ordering right into the definition of an association, so that it becomes the default ordering for this association. For example, let's model soccer teams and players, ordered by the number printed on their shirt.
Let's start with a **HasMany** association. Each player knows its position in its team:
```swift
struct Team: FetchableRecord, TableRecord {
var id: Int64
var name: String
}
struct Player: FetchableRecord, TableRecord {
var id: Int64
var teamId: Int64
var name: String
var position: Int
}
```
The `Team.players` association is ordered by position, so that all team players are loaded well-sorted by default:
```swift
extension Team {
static let players = hasMany(Player.self).order(\.position)
var players: QueryInterfaceRequest {
request(for: Team.players)
}
}
```
Things are very similar for **HasManyThrough** associations. Now each player knows its position in the teams it belongs to:
```swift
struct Team: FetchableRecord, TableRecord {
var id: Int64
var name: String
}
struct PlayerRole: FetchableRecord, TableRecord {
var teamId: Int64
var playerId: Int64
var position: Int
}
struct Player: FetchableRecord, TableRecord {
var id: Int64
var name: String
}
```
Again, the `Team.players` association is ordered by position, so that all team players are loaded well-sorted by default:
```swift
extension Team {
static let playerRoles = hasMany(PlayerRole.self).order(\.position)
static let players = hasMany(Player.self, through: playerRoles, using: PlayerRole.player)
var players: QueryInterfaceRequest {
request(for: Team.players)
}
}
extension PlayerRole {
static let player = belongsTo(Player.self)
}
```
In both cases, you can escape the default ordering when you need it:
```swift
struct TeamInfo: Decodable, FetchableRecord {
var team: Team
var players: [Player]
}
// Default ordering by position
let team: Team = ...
let players = try team.players.fetchAll(db)
let teamInfos = try Team
.including(all: Team.players)
.asRequest(of: TeamInfo.self)
.fetchAll(db)
// Custom ordering
let team: Team = ...
let players = try team.players.order(\.name).fetchAll(db)
let teamInfos = try Team
.including(all: Team.players.order(\.name))
.asRequest(of: TeamInfo.self)
.fetchAll(db)
```
## Columns Selected by an Association
By default, associated records, like all records, include all their columns:
```swift
// SELECT book.*, author.*
// FROM book
// JOIN author ON author.id = book.authorId
let request = Book.including(required: Book.author)
```
**The selection can be changed for each individual request, or for all requests including a given type.**
To specify the default selection for a given record type, see [Columns Selected by a Request](../README.md#columns-selected-by-a-request).
To specify the selection in a specific request, use the `select` method:
```swift
// SELECT book.*, author.id, author.name
// FROM book
// JOIN author ON author.id = book.authorId
let restrictedAuthor = Book.author.select { [$0.id, $0.name] }
let request = Book.including(required: restrictedAuthor)
```
In order to fetch from such requests of partial records, see the documentation of the [joining methods].
## Further Refinements to Associations
Associations support more refinements:
- `distinct`
Fetch all authors with the kinds of books they write (novels, poems, plays, etc):
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var bookKinds: Set
}
let distinctBookKinds = Author.books
.select(\.kind)
.distinct()
.forKey("bookKinds")
let authorInfos: [AuthorInfo] = try Author
.including(all: distinctBookKinds)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
- `group`, `having`
Fetch all authors with the year of their latest book for each kind (novels, poems, plays, etc):
```swift
struct BookKindInfo: Decodable {
var kind: Book.Kind
var maxYear: Int
}
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var bookKindInfos: [BookKindInfo]
}
let bookKindInfos = Author.books
.select { [$0.kind, max($0.year).forKey("maxYear")] }
.group(\.kind)
.forKey("bookKindInfos")
let authorInfos: [AuthorInfo] = try Author
.including(all: bookKindInfos)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
- [Association Aggregates]
Fetch all authors with their awarded books:
```swift
struct AuthorInfo: FetchableRecord, Decodable {
var author: Author
var awardedBooks: [Book]
}
let awardedBooks = Author.books
.having(Book.awards.isEmpty == false)
.forKey("awardedBooks")
let authorInfos: [AuthorInfo] = try Author
.including(all: awardedBooks)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
- [Common Table Expressions]
Association can use their own CTEs:
```swift
struct AuthorInfo: FetchableRecord, Decodable {
var author: Author
var specialBooks: [Book]
}
let specialCTE = CommonTableExpression(...)
let specialBooks = Author.books
.with(specialCTE)
... // use the CTE in the book association
.forKey("specialBooks")
let authorInfos = try Author
.including(all: specialBooks)
.asRequest(of: AuthorInfo.self)
.fetchAll(db)
```
> **Warning**: associations refined with `limit`, `distinct`, `group`, `having`, or association aggregates can only be used with `including(all:)`. You will get a fatal error if you use them with other joining methods: `including(required:)`, etc.
## Table Aliases
In all examples we have seen so far, all associated records are joined, included, filtered, and sorted independently. We could not filter them on conditions that involve several records, for example.
Let's say we look for posthumous books, published after their author has died. We need to compare a book publication date with an author eventual death date.
Let's first see a wrong way to do it:
```swift
// A wrong request:
// SELECT book.*
// FROM book
// JOIN person ON person.id = book.authorId
// WHERE book.publishDate >= book.deathDate
let request = Book
.joining(required: Book.author)
.filter { $0.publishDate >= $0.deathDate }
```
When executed, we'll get a DatabaseError of code 1, "no such column: book.deathDate".
That is because the "deathDate" column has been used for filtering books, when it is defined on the person database table.
To fix this error, we need a **table alias**:
```swift
let authorAlias = TableAlias()
```
We modify the `Book.author` association so that it uses this table alias, and we use the table alias to qualify author columns where needed:
```swift
// Swift 6.1
//
// > SELECT book.*
// > FROM book
// > JOIN person ON person.id = book.authorId
// > WHERE book.publishDate >= person.deathDate
let request = Book
.joining(required: Book.author.aliased(authorAlias))
.filter { $0.publishDate >= authorAlias.deathDate }
// Swift 6.0
//
// > SELECT book.*
// > FROM book
// > JOIN person ON person.id = book.authorId
// > WHERE book.publishDate >= person.deathDate
let request = Book
.joining(required: Book.author.aliased(authorAlias))
.filter { $0.publishDate >= authorAlias[Author.Columns.deathDate] }
```
Note that Swift 6.1 is required for the short syntac `alias.column`. From now on, we will only give examples in Swift 6.1.
**Table aliases** can also improve control over the ordering of request results. In the example below, we override the [default ordering](#sorting-associations) of associated records by sorting on author names first:
```swift
// SELECT book.*
// FROM book
// JOIN person ON person.id = book.authorId
// ORDER BY person.name, book.publishDate
let request = Book
.joining(required: Book.author.aliased(authorAlias))
.order { [authorAlias.name, $0.publishDate] }
```
**Table aliases** can be given a name. This name is guaranteed to be used as the table alias in the SQL query. This guarantee lets you write SQL snippets when you need it:
```swift
// SELECT b.*
// FROM book b
// JOIN person a ON a.id = b.authorId
// AND a.countryCode = 'FR'
// WHERE b.publishDate >= a.deathDate
let bookAlias = TableAlias(name: "b")
let authorAlias = TableAlias(name: "a")
let request = Book.aliased(bookAlias)
.joining(required: Book.author.aliased(authorAlias)
.filter(sql: "a.countryCode = ?", arguments: ["FR"]))
.filter(sql: "b.publishDate >= a.deathDate")
```
> **Note**: avoid reusing table aliases between several tables or requests, because you will get a fatal error:
>
> ```swift
> // Fatal error: A TableAlias most not be used to refer to multiple tables
> let alias = TableAlias()
> let books = Book.aliased(alias)...
> let people = Person.aliased(alias)...
> ```
>
> **Note**: you can't use the `including(all:)` method and use table aliases to filter the associated records on other records:
>
> ```swift
> // NOT IMPLEMENTED: loading all authors along with their posthumous books
> let authorAlias = TableAlias()
> let request = Author
> .aliased(authorAlias)
> .including(all: Author.books
> .filter { $0.publishDate >= authorAlias.deathDate })
> ```
## Refining Association Requests
When you join or include an association several times, with the same **[association key](#the-structure-of-a-joined-request)**, GRDB will apply the following rules:
- `including` wins over `joining`:
```swift
// Equivalent to Record.including(optional: association)
Record
.including(optional: association)
.joining(optional: association)
```
- `required` wins over `optional`:
```swift
// Equivalent to Record.including(required: association)
Record
.including(required: association)
.including(optional: association)
```
- All [filters](#filtering-associations) are applied:
```swift
// Equivalent to Record.including(required: association.filter(condition1 && condition2))
Record
.including(required: association.filter(condition1))
.including(optional: association.filter(condition1))
```
- The last [ordering](#sorting-associations) wins:
```swift
// Equivalent to Record.including(required: association.order(ordering2))
Record
.including(required: association.order(ordering1))
.including(optional: association.order(ordering2))
```
- The last [selection](#columns-selected-by-an-association) wins:
```swift
// Equivalent to Record.including(required: association.select(selection2))
Record
.including(required: association.select(selection1))
.including(optional: association.select(selection2))
```
**Those rules exist so that you can design fluent interfaces that build complex requests out of simple building blocks.**
For example, we can start by defining base requests as extensions to the [DerivableRequest Protocol]:
```swift
// Author requests
extension DerivableRequest {
/// Filters authors by country
func filter(country: String) -> Self {
filter { $0.country == country }
}
}
// Book requests
extension DerivableRequest {
/// Filters books by author country
func filter(authorCountry: String) -> Self {
joining(required: Book.author.filter(country: country))
}
/// Order books by author name and then book title
func orderedByAuthorNameAndYear() -> Self {
let authorAlias = TableAlias()
return self
.joining(optional: Book.author.aliased(authorAlias))
.order { [
authorAlias.name.collating(.localizedCaseInsensitiveCompare),
$0.year,
] }
}
}
```
And then compose those in a fluent style:
```swift
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Author
}
// SELECT book.*, author.*
// FROM book
// JOIN author ON author.id = book.authorId AND author.country = 'FR'
// ORDER BY author.name COLLATE ..., book.year
let bookInfos = try Book.all()
.filter(authorCountry: "FR")
.orderedByAuthorNameAndYear()
.including(required: Book.author)
.asRequest(of: BookInfo.self)
.fetchAll(db)
```
Remember that those refinement rules only apply when an association is joined or included several times, with the same **[association key](#the-structure-of-a-joined-request)**. Changing this key stops merging associations together. See [Isolation of Multiple Aggregates] for a longer discussion.
Fetching Values from Associations
=================================
We have seen in [Joining And Prefetching Associated Records] how to define requests that involve several records.
To consume those requests, you will generally define a record type that matches the structure of the request. You'll make it adopt the [FetchableRecord] protocol, so that it can decode database rows.
Often, you'll also make it adopt the standard Decodable protocol, because the compiler will generate the decoding code for you.
Each association included in the request can feed a property of the decoded record:
- `including(optional:)` feeds an optional property:
```swift
let request = Employee.including(optional: Employee.manager)
struct EmployeeInfo: FetchableRecord, Decodable {
var employee: Employee
var manager: Employee? // the optional associated manager
}
let employeeInfos: [EmployeeInfo] = try EmployeeInfo.fetchAll(db, request)
```
- `including(required:)` feeds an non-optional property:
```swift
let request = Book.including(required: Book.author)
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Author // the required associated author
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
- `including(all:)` feeds an Array or Set property:
```swift
let request = Author.including(all: Author.books)
struct AuthorInfo: FetchableRecord, Decodable {
var author: Author
var books: [Book] // all associated books
}
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
- [The Structure of a Joined Request]
- [Decoding a Joined Request with a Decodable Record]
- [Decoding a Joined Request with FetchableRecord]
- [Debugging Request Decoding]
- [Recommended Practices for Designing Record Types] - in this general guide about records, check out the "Associations" chapter.
## The Structure of a Joined Request
**Joined request defines a tree of associated records identified by "association keys".**
Below, author and cover image are both associated to book, and country is associated to author:
```swift
let request = Book
.including(required: Book.author
.including(optional: Author.country))
.including(optional: Book.coverImage)
```
This request builds the following **tree of association keys**:
Requests can feed record types whose property names match those association keys:
```swift
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Author
var country: Country?
var coverImage: CoverImage?
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
By default, **association keys** are the names of the database tables of associated records. Keys are automatically [singularized or pluralized](#convention-for-database-table-names), depending of the cardinality of the included association:
```swift
extension Author {
static let books = hasMany(Book.self)
}
Author.including(all: Author.books) // association key "books"
extension Book {
static let author = belongsTo(Author.self)
}
Book.including(required: Book.author) // association key "author"
```
Keys can be customized when the association is defined:
```swift
extension Employee {
static let manager = belongsTo(Employee.self, key: "manager")
}
Employee.including(optional: Employee.manager) // association key "manager"
```
Keys can also be customized with the `forKey` method:
```swift
extension Author {
static let novels = books
.filter { $0.kind == "novel" }
.forKey("novels")
}
Author.including(all: Author.novels) // association key "novels"
```
## Decoding a Joined Request with a Decodable Record
When **association keys** match the property names of a Decodable record, you get free decoding of joined requests into this record:
```swift
let request = Book
.including(required: Book.author
.including(optional: Author.country))
.including(optional: Book.coverImage)
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Author
var country: Country?
var coverImage: CoverImage?
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
We see that a hierarchical tree has been flattened in the `BookInfo` record.
But sometimes your decoded records will have better reflect the hierarchical structure of the request:
### Decoding a Hierarchical Decodable Record
Some requests are better decoded with a Decodable record that reflects the hierarchical structure of the request.
```swift
let request = Book
.including(optional: Book.coverImage)
.including(required: Book.author
.including(optional: Person.country))
.including(optional: Book.translator
.including(optional: Person.country))
```
This requests for all books, with their cover images, and their authors and translators. Those people are themselves decorated with their respective nationalities.
We plan to decode this request into is the following nested record:
```swift
struct BookInfo: FetchableRecord, Decodable {
struct PersonInfo: Decodable {
var person: Person
var country: Country?
}
var book: Book
var authorInfo: PersonInfo
var translatorInfo: PersonInfo?
var coverImage: CoverImage?
}
```
This request needs a little preparation: we need **association keys** that match the **coding keys** for the authorInfo and translatorInfo properties.
And who is the most able to know those coding keys? BookInfo itself, thanks to its `CodingKeys` enum that was automatically generated by the Swift compiler. We thus define the `BookInfo.all()` method that builds our request:
```swift
extension BookInfo {
static func all() -> QueryInterfaceRequest {
Book.including(optional: Book.coverImage)
.including(required: Book.author
.forKey(CodingKeys.authorInfo) // (1)
.including(optional: Person.country))
.including(optional: Book.translator
.forKey(CodingKeys.translatorInfo) // (1)
.including(optional: Person.country))
.asRequest(of: BookInfo.self) // (2)
}
}
let bookInfos = try BookInfo.all().fetchAll(db, request) // [BookInfo]
```
1. The `forKey(_:)` method changes the association key, so that the associated records can feed their target properties.
2. The `asRequest(of:)` method turns the request into a request of BookInfo. See [Custom Requests] for more information.
## Decoding a Joined Request with FetchableRecord
When [Decodable](#decoding-a-joined-request-with-a-decodable-record) records provides convenient decoding of joined rows, you may want a little more control over row decoding.
The `init(row:)` initializer of the [FetchableRecord] protocol is what you look after:
```swift
let request = Book
.including(required: Book.author
.including(optional: Author.country))
.including(optional: Book.coverImage)
struct BookInfo: FetchableRecord {
var book: Book
var author: Author
var country: Country?
var coverImage: CoverImage?
init(row: Row) throws {
book = try Book(row: row)
author = try row.decode(forKey: "author")
country = try row.decodeIfPresent(forKey: "country")
coverImage = try row.decodeIfPresent(forKey: "coverImage")
}
}
let bookInfos: [BookInfo] = try BookInfo.fetchAll(db, request)
```
When you extract a record from a row, GRDB looks up the tree of **association keys**:
```swift
let author = try row.decode(Author.self, forKey: "author")
```
If the key is not found, or only associated with columns that are all NULL, an optional record is decoded as nil:
```swift
let country = try row.decodeIfPresent(Country.self, forKey: "country")
```
You can also perform custom navigation in the tree by using *row scopes*. See [Row Adapters] for more information.
When you use the `include(all:)` method, you can decode an Array or a Set of records:
```swift
let request = Author.including(all: Author.books)
struct AuthorInfo: FetchableRecord {
var author: Author
var books: [Book]
init(row: Row) throws {
author = try Author(row: row)
books = try row.decode(forKey: "books")
}
}
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
## Debugging Request Decoding
When you have difficulties building a Decodable record that successfully decodes a joined request, we advise to temporarily decode raw database rows, and inspect them.
```swift
let request = Book
.including(required: Book.author
.including(optional: Author.country))
.including(optional: Book.coverImage)
.including(all: Book.prizes)
let rows = try Row.fetchAll(db, request)
print(rows[0].debugDescription)
// Prints:
// ▿ [id:1, authorId:2, title:"Moby-Dick"]
// unadapted: [id:1, authorId:2, title:"Moby-Dick", id:2, name:"Herman Melville", countryCode:"US", code:"US", name:"United States of America", id:NULL, imageId:NULL, path:NULL]
// - author: [id:2, name:"Herman Melville", countryCode:"US"]
// - country: [code:"US", name:"United States of America"]
// - coverImage: [id:NULL, imageId:NULL, path:NULL]
// + prizes: 3 rows
```
Watch in the row debugging description:
- the **association keys**: "person", "country", "coverImage" and "prizes" in our example
- associated rows that contain only null values ("coverImage", above).
The associated rows that contain only null values are easy to deal with: null rows loaded from optional associated records should be decoded into Swift optionals:
```swift
struct BookInfo: FetchableRecord, Decodable {
var book: Book
var author: Author // .including(required: Book.author)
var country: Country? // .including(optional: Author.country)
var coverImage: CoverImage? // .including(optional: Book.coverImage)
var prizes: [Prize] // .including(all: Book.prizes)
}
```
When the **association keys** don't match your expectations, change them (see [The Structure of a Joined Request]):
```swift
let request = Book
.including(optional: Book.author.forKey("writer")) // customized association key
let rows = try Row.fetchAll(db, request)
print(rows[0].debugDescription)
// Prints:
// ▿ [id:1, authorId:2, title:"Moby-Dick"]
// unadapted: [id:1, authorId:2, title:"Moby-Dick", id:2, name:"Herman Melville"]
// - writer: [id:2, name:"Herman Melville", countryCode:"US"]
```
## Association Aggregates
It is possible to fetch aggregated values from **[HasMany]** and **[HasManyThrough]** associations:
Counting associated records, fetching the minimum, maximum, average value of an associated record column, computing the sum of an associated record column, these are all aggregation operations.
When you need to compute aggregates **from a single record**, you use [regular aggregating methods] on [requests for associated records]. For example:
```swift
struct Author: TableRecord, EncodableRecord {
static let books = hasMany(Book.self)
var books: QueryInterfaceRequest {
request(for: Author.books)
}
}
let author: Author = ...
// The number of books by this author
let bookCount = try author.books.fetchCount(db) // Int
// The year of the most recent book by this author
let request = author.books.select { max($0.year) }
let maxBookYear = try Int.fetchOne(db, request) // Int?
```
When you need to compute aggregates **from several record**, in a single shot, you'll use an **association aggregate**. Those are the topic of this chapter.
For example, you'll use the `isEmpty` aggregate when you want, say, to fetch all authors who wrote no book at all, or some books:
```swift
let lazyAuthors = try Author
.having(Author.books.isEmpty)
.fetchAll(db) // [Author]
let productiveAuthors: [Author] = try Author
.having(Author.books.isEmpty == false)
.fetchAll(db) // [Author]
```
And you'll use the `count` aggregate in order to fetch all authors along with the number of books they wrote:
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var bookCount: Int
}
let request = Author.annotated(with: Author.books.count)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
for info in authorInfos {
print("\(info.author.name) wrote \(info.bookCount) book(s).")
}
```
### Available Association Aggregates
**[HasMany]** and **[HasManyThrough]** associations let you build the following association aggregates:
- `books.count`
- `books.isEmpty`
- `books.min(column)`
- `books.max(column)`
- `books.average(column)`
- `books.sum(column)`
- `books.total(column)`
### Annotating a Request with Aggregates
The `annotated(with:)` method appends aggregated values to the selected columns of a request. You can append as many aggregates values as needed, from one or several associations.
In order to access those values, you fetch records that have matching properties.
For example:
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var bookCount: Int
var maxBookYear: Int?
}
// SELECT author.*,
// COUNT(DISTINCT book.id) AS bookCount,
// MAX(book.year) AS maxBookYear,
// FROM author
// LEFT JOIN book ON book.authorId = author.id
// GROUP BY author.id
let request = Author.annotated(with:
Author.books.count,
Author.books.max(\.year))
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
for info in authorInfos {
print(info.author.name)
print("- number of books: \(info.bookCount)")
print("- last book published on: \(info.maxBookYear)")
}
```
As seen in the above example, aggregated values are given a **default name**, such as "bookCount" or "maxBookYear", which directly feeds the decoded records.
The default name is built from the aggregating method, the **[association key](#the-structure-of-a-joined-request)**, and the aggregated column name:
| Method | Association Key | Aggregated Column | Aggregate name |
| ------ | --------------- | ----------------- | -------------- |
| `Author.books.isEmpty`. | `books` | - | `hasNoBook` |
| `Author.books.count`. | `books` | - | `bookCount` |
| `Author.books.min(\.year)` | `books` | `year` | `minBookYear` |
| `Author.books.max(\.year)` | `books` | `year` | `maxBookYear` |
| `Author.books.average(\.price)` | `books` | `price` | `averageBookPrice` |
| `Author.books.sum(\.awards)` | `books` | `awards` | `bookAwardsSum` |
| `Author.books.total((\.awards))` | `books` | `awards` | `bookAwardsSum` ¹ |
¹ The default name of the `total` aggregate has a `Sum` suffix, just like the `sum` aggregate. Both compute sums, one with the `SUM` SQL function, the other with `TOTAL`. See [SQLite documentation](https://www.sqlite.org/lang_aggfunc.html#sumunc) for the difference between these aggregate functions.
Those default names are lost whenever an aggregate is modified (negated, added, multiplied, whatever).
You can name or rename aggregates with the `forKey` method:
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var numberOfBooks: Int
}
let numberOfBooks = Author.books.count.forKey("numberOfBooks") // <--
let request = Author.annotated(with: numberOfBooks)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var hasBooks: Bool
}
let hasBooks = (Author.books.isEmpty == false).forKey("hasBooks") // <--
let request = Author.annotated(with: hasBooks)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var workCount: Int
}
let workCount = (Author.books.count + Author.paintings.count).forKey("workCount") // <--
let request = Author.annotated(with: workCount)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
Coding keys are also accepted:
```swift
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var numberOfBooks: Int
static func all() -> QueryInterfaceRequest {
let numberOfBooks = Author.books.count.forKey(CodingKey.numberOfBooks) // <--
return Author
.annotated(with: numberOfBooks)
.asRequest(of: AuthorInfo.self)
}
}
let authorInfos: [AuthorInfo] = try AuthorInfo.all().fetchAll(db)
```
### Filtering a Request with Aggregates
The `having(_:)` method filters a request according to an aggregated value. You can append as many aggregate conditions as needed, from one or several associations.
- Authors who did not write any book:
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
GROUP BY author.id
HAVING COUNT(DISTINCT book.id) = 0
```
```swift
let request = Author.having(Author.books.isEmpty)
```
- Authors who wrote at least one book:
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
GROUP BY author.id
HAVING COUNT(DISTINCT book.id) > 0
```
```swift
let request = Author.having(Author.books.isEmpty == false)
```
- Authors who wrote at least two books:
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
GROUP BY author.id
HAVING COUNT(DISTINCT book.id) >= 2
```
```swift
let request = Author.having(Author.books.count >= 2)
```
- Authors who wrote at least one book after 2010:
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
GROUP BY author.id
HAVING MAX(book.year) >= 2010
```
```swift
let request = Author.having(Author.books.max(\.year) >= 2010)
```
- Authors who wrote at least one book of kind "novel":
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id AND book.kind = 'novel'
GROUP BY author.id
HAVING COUNT(DISTINCT book.id) > 0
```
```swift
let novels = Author.books.filter { $0.kind == "novel" }
let request = Author.having(novels.isEmpty == false)
```
- Authors who wrote more books than they made paintings:
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
LEFT JOIN painting ON painting.authorId = author.id
GROUP BY author.id
HAVING COUNT(DISTINCT book.id) > COUNT(DISTINCT painting.id)
```
```swift
let request = Author.having(Author.books.count > Author.paintings.count)
```
- Authors who wrote no book, but made at least one painting:
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
LEFT JOIN painting ON painting.authorId = author.id
GROUP BY author.id
HAVING ((COUNT(DISTINCT book.id) = 0) AND (COUNT(DISTINCT painting.id) > 0))
```
```swift
let request = Author.having(Author.books.isEmpty && !Author.paintings.isEmpty)
```
### Aggregate Operations
Aggregates can be modified and combined with Swift operators:
- Logical operators `&&`, `||` and `!`
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
LEFT JOIN painting ON painting.authorId = author.id
GROUP BY author.id
HAVING ((COUNT(DISTINCT book.id) = 0) AND (COUNT(DISTINCT painting.id) = 0))
```
```swift
let condition = Author.books.isEmpty && Author.paintings.isEmpty
let request = Author.having(condition)
```
- Comparison operators `<`, `<=`, `=`, `!=`, `>=`, `>`
SQL
```sql
SELECT author.*
FROM author
LEFT JOIN book ON book.authorId = author.id
GROUP BY author.id
HAVING MAX(book.year) >= 2010
```
```swift
let request = Author.having(Author.books.max(\.year) >= 2010)
```
- Arithmetic operators `+`, `-`, `*`, `/`
SQL
```sql
SELECT author.*,
(COUNT(DISTINCT book.id) +
COUNT(DISTINCT painting.id)) AS workCount
FROM author
LEFT JOIN book ON book.authorId = author.id
LEFT JOIN painting ON painting.authorId = author.id
GROUP BY author.id
```
```swift
let workCount = Author.books.count + Author.paintings.count)
let request = Author.annotated(with: workCount.forKey("workCount"))
```
- IFNULL operator `??`
SQL
```sql
SELECT "team".*, IFNULL(MIN("player"."score"), 0) AS "minPlayerScore"
FROM "team"
LEFT JOIN "player" ON ("player"."teamId" = "team"."id")
GROUP BY "team"."id"
```
```swift
let request = Team.annotated(with: Team.players.min(\.score) ?? 0)
```
- SQL functions `ABS`, `CAST`, and `LENGTH` are available as the `abs`, `cast`, and `length` Swift functions:
SQL
```sql
SELECT "team".*, ABS(MAX("player"."score"))
FROM "team"
LEFT JOIN "player" ON ("player"."teamId" = "team"."id")
GROUP BY "team"."id"
```
```swift
let request = Team.annotated(with: abs(Team.players.max(\.score)))
```
### Isolation of Multiple Aggregates
When you compute multiple aggregates, make sure they use as many distinct **[association keys](#the-structure-of-a-joined-request)** as there are distinct populations of associated records.
In the example below, we use compute two aggregates from the same association `Author.books`. Both aggregates are computed on the same population of associated records, and so we want them to share the same association key:
- Authors with the publishing year of their first and last book:
SQL
```sql
SELECT author.*,
MIN(book.year) AS minBookYear,
MAX(book.year) AS maxBookYea
FROM author
LEFT JOIN book ON book.authorId = author.id
GROUP BY author.id
```
```swift
struct Author: TableRecord {
static let books = hasMany(Book.self) // association key "books"
}
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var minBookYear: Int?
var maxBookYear: Int?
}
let request = Author.annotated(with:
Author.books.min(\.year), // association key "books"
Author.books.max(\.year)) // association key "books"
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
In this other example, the `Author.books` and `Author.paintings` have the distinct `book` and `painting` keys. They don't interfere, and provide the expected results:
- Authors with their number of books and paintings:
SQL
```sql
SELECT author.*,
(COUNT(DISTINCT book.id) + COUNT(DISTINCT painting.id)) AS workCount
FROM author
LEFT JOIN book ON book.authorId = author.id
LEFT JOIN painting ON painting.authorId = author.id
GROUP BY author.id
```
```swift
struct Author: TableRecord {
static let books = hasMany(Book.self) // association key "books"
static let paintings = hasMany(Painting.self) // association key "paintings"
}
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var workCount: Int
}
let aggregate = Author.books.count + // association key "books"
Author.paintings.count // association key "paintings"
let request = Author.annotated(with: aggregate.forKey("workCount"))
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
But in the following example, we use the same association `Author.books` twice, in order to compute aggregates on two distinct populations of associated books. We must provide explicit keys in order to make sure both aggregates are computed independently:
- Authors with their number of novels and theatre plays:
SQL
```sql
SELECT author.*,
COUNT(DISTINCT book1.id) AS novelCount,
COUNT(DISTINCT book2.id) AS theatrePlayCount
FROM author
LEFT JOIN book book1 ON book1.authorId = author.id AND book1.kind = 'novel'
LEFT JOIN book book2 ON book2.authorId = author.id AND book2.kind = 'theatrePlay'
GROUP BY author.id
```
```swift
struct Author: TableRecord {
static let books = hasMany(Book.self) // association key "books"
}
struct AuthorInfo: Decodable, FetchableRecord {
var author: Author
var novelCount: Int
var theatrePlayCount: Int
}
let novelCount = Author.books
.filter { $0.kind == "novel" }
.forKey("novels") // association key "novels"
.count
let theatrePlayCount = Author.books
.filter { $0.kind == "theatrePlay" }
.forKey("theatrePlays") // association key "theatrePlays"
.count
let request = Author.annotated(with: novelCount, theatrePlayCount)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
When one doesn't use distinct association keys for novels and theatre plays, GRDB will not count two distinct sets of associated books, and will not fetch the expected results:
SQL
```sql
SELECT author.*,
COUNT(DISTINCT book.id) AS novelCount,
COUNT(DISTINCT book.id) AS theatrePlayCount
FROM author
LEFT JOIN book ON book.authorId = author.id
AND (book.kind = 'novel' AND book.kind = 'theatrePlay')
GROUP BY author.id
```
```swift
// WRONG: not counting distinct sets of associated books
let novelCount = Author.books // association key "books"
.filter { $0.kind == "novel" }
.count
.forKey("novelCount")
let theatrePlayCount = Author.books // association key "books"
.filter { $0.kind == "theatrePlay" }
.count
.forKey("theatrePlayCount")
let request = Author.annotated(with: novelCount, theatrePlayCount)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
```
## DerivableRequest Protocol
The `DerivableRequest` protocol is adopted by both [query interface requests] such as `Author.all()` and associations such as `Book.author`. It is intended for you to use as a customization point when you want to extend the built-in GRDB apis.
For example, we may want to define `orderedByName()` and `filter(country:)` request methods that make our requests easier to read:
```swift
// Authors sorted by name
let request = Author.all().orderedByName()
// French authors ordered by name
let request = Author.all().filter(country: "FR").orderedByName()
// Spanish books
let request = Book.all().filter(country: "ES")
```
Those methods are defined on extensions to the `DerivableRequest` protocol:
```swift
extension DerivableRequest {
func filter(country: String) -> Self {
filter { $0.country == country }
}
func orderedByName() -> Self {
order { $0.name.collating(.localizedCaseInsensitiveCompare) }
}
}
extension DerivableRequest {
func filter(country: String) -> Self {
joining(required: Book.author.filter(country: country))
}
}
```
See [Recommended Practices for Designing Record Types] for more information.
## Known Issues
- **You can't chain a required association on an optional association:**
```swift
// NOT IMPLEMENTED
let request = Book
.joining(optional: Book.author
.including(required: Person.country))
```
This code compiles, but you'll get a runtime fatal error "Not implemented: chaining a required association behind an optional association". Future versions of GRDB may allow such requests.
- **You can't use the `including(all:)` method and use table aliases to filter the associated records on other records:**
```swift
// NOT IMPLEMENTED: loading all authors along with their posthumous books
let authorAlias = TableAlias()
let request = Author
.aliased(authorAlias)
.including(all: Author.books
.filter { $0.publishDate >= authorAlias.deathDate })
```
- **You can't use the `including(all:)` method with a [HasMany] and a [HasManyThrough] associations that share the same base association in the same request**:
```swift
// NOT IMPLEMENTED
let request = Country
.including(all: Country.passports)
.including(all: Country.citizens)
```
This code compiles, but you'll get a runtime fatal error "Not implemented: merging a direct association and an indirect one with including(all:)". Future versions of GRDB may allow such requests.
The workaround is to nest the most remote association:
```swift
// Workaround
let request = Country
.including(all: Country.passports
.including(required: Passport.citizen))
```
---
This documentation owns a lot to the [Active Record Associations](http://guides.rubyonrails.org/association_basics.html) guide, which is an immensely well-written introduction to database relations. Many thanks to the Rails team and contributors.
---
### LICENSE
**GRDB**
Copyright (C) 2015-2023 Gwendal Roué
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
**Ruby on Rails documentation**
Copyright (c) 2005-2018 David Heinemeier Hansson
Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:
The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
[Associations Benefits]: #associations-benefits
[Required Protocols]: #required-protocols
[BelongsTo]: #belongsto
[HasMany]: #hasmany
[HasOne]: #hasone
[HasManyThrough]: #hasmanythrough
[HasOneThrough]: #hasonethrough
[Choosing Between BelongsTo and HasOne]: #choosing-between-belongsto-and-hasone
[Self Joins]: #self-joins
[Ordered Associations]: #ordered-associations
[Further Refinements to Associations]: #further-refinements-to-associations
[The Types of Associations]: #the-types-of-associations
[FetchableRecord]: ../README.md#fetchablerecord-protocols
[migration]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/migrations
[Record]: ../README.md#records
[Foreign Key Actions]: https://sqlite.org/foreignkeys.html#fk_actions
[Associations and the Database Schema]: #associations-and-the-database-schema
[Convention for Database Table Names]: #convention-for-database-table-names
[Convention for the BelongsTo Association]: #convention-for-the-belongsto-association
[Convention for the HasOne Association]: #convention-for-the-hasone-association
[Convention for the HasMany Association]: #convention-for-the-hasmany-association
[Foreign Keys]: #foreign-keys
[Building Requests from Associations]: #building-requests-from-associations
[Fetching Values from Associations]: #fetching-values-from-associations
[Combining Associations]: #combining-associations
[Requesting Associated Records]: #requesting-associated-records
[requests for associated records]: #requesting-associated-records
[Joining And Prefetching Associated Records]: #joining-and-prefetching-associated-records
[joining methods]: #joining-and-prefetching-associated-records
[Filtering Associations]: #filtering-associations
[Sorting Associations]: #sorting-associations
[Columns Selected by an Association]: #columns-selected-by-an-association
[Table Aliases]: #table-aliases
[Refining Association Requests]: #refining-association-requests
[The Structure of a Joined Request]: #the-structure-of-a-joined-request
[Decoding a Joined Request with a Decodable Record]: #decoding-a-joined-request-with-a-decodable-record
[Decoding a Hierarchical Decodable Record]: #decoding-a-hierarchical-decodable-record
[Decoding a Joined Request with FetchableRecord]: #decoding-a-joined-request-with-fetchablerecord
[Debugging Request Decoding]: #debugging-request-decoding
[Custom Requests]: ../README.md#custom-requests
[Association Aggregates]: #association-aggregates
[Available Association Aggregates]: #available-association-aggregates
[Annotating a Request with Aggregates]: #annotating-a-request-with-aggregates
[Filtering a Request with Aggregates]: #filtering-a-request-with-aggregates
[Aggregate Operations]: #aggregate-operations
[Isolation of Multiple Aggregates]: #isolation-of-multiple-aggregates
[DerivableRequest Protocol]: #derivablerequest-protocol
[Known Issues]: #known-issues
[Row Adapters]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/rowadapter
[query interface requests]: ../README.md#requests
[TableRecord]: ../README.md#tablerecord-protocol
[Recommended Practices for Designing Record Types]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/recordrecommendedpractices
[regular aggregating methods]: ../README.md#fetching-aggregated-values
[EncodableRecord]: ../README.md#persistablerecord-protocol
[PersistableRecord]: ../README.md#persistablerecord-protocol
[Codable Records]: ../README.md#codable-records
[persistence methods]: ../README.md#persistence-methods
[database observation tools]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseobservation
[FAQ]: ../README.md#faq-associations
[common table expressions]: CommonTableExpressions.md
[Common Table Expressions]: CommonTableExpressions.md
[Associations to Common Table Expressions]: CommonTableExpressions.md#associations-to-common-table-expressions
[`including(required:)`]: #includingrequired
[`including(optional:)`]: #includingoptional
[`including(all:)`]: #includingall
[`annotated(withRequired:)`]: #annotatedwithrequired
[`annotated(withOptional:)`]: #annotatedwithoptional
[`joining(required:)`]: #joiningrequired
[`joining(optional:)`]: #joiningoptional
[Choosing a Joining Method Given the Shape of the Decoded Type]: #choosing-a-joining-method-given-the-shape-of-the-decoded-type
---
### Documentation/Combine
GRDB ❤️ Combine
===============
**On systems supporting the Combine framework, GRDB offers the ability to publish database values and events using Combine's publishers.**
- [Usage]
- [Demo Application]
- [Asynchronous Database Access]
- [Database Observation]
- [Combine and Data Consistency]: take care when you combine database publishers together
## Usage
To connect to the database, please refer to [Database Connections].
Asynchronously read from the database
This publisher reads a single value and delivers it.
```swift
// DatabasePublishers.Read<[Player]>
let players = dbQueue.readPublisher { db in
try Player.fetchAll(db)
}
```
Asynchronously write in the database
This publisher updates the database and delivers a single value.
```swift
// DatabasePublishers.Write
let write = dbQueue.writePublisher { db in
try Player(...).insert(db)
}
// DatabasePublishers.Write
let newPlayerCount = dbQueue.writePublisher { db -> Int in
try Player(...).insert(db)
return try Player.fetchCount(db)
}
```
Asynchronously migrate the database
This publisher migrates a database:
```swift
// DatabasePublishers.Migrate
let migrator: DatabaseMigrator = ...
let publisher = migrator.migratePublisher(dbQueue)
```
Observe changes in database values
This publisher delivers fresh values whenever the database changes:
```swift
// A publisher with output [Player] and failure Error
let publisher = ValueObservation
.tracking { db in try Player.fetchAll(db) }
.publisher(in: dbQueue)
// A publisher with output Int? and failure Error
let publisher = ValueObservation
.tracking { db in try Int.fetchOne(db, sql: "SELECT MAX(score) FROM player") }
.publisher(in: dbQueue)
```
Observe database transactions
This publisher delivers database connections whenever a database transaction has impacted an observed region:
```swift
// A publisher with output Database and failure Error
let publisher = DatabaseRegionObservation
.tracking(Player.all())
.publisher(in: dbQueue)
let cancellable = publisher.sink(
receiveCompletion: { completion in ... },
receiveValue: { (db: Database) in
print("Exclusive write access to the database after players have been impacted")
})
// A publisher with output Database and failure Error
let publisher = DatabaseRegionObservation
.tracking(SQLRequest(sql: "SELECT MAX(score) FROM player"))
.publisher(in: dbQueue)
let cancellable = publisher.sink(
receiveCompletion: { completion in ... },
receiveValue: { (db: Database) in
print("Exclusive write access to the database after maximum score has been impacted")
})
```
# Asynchronous Database Access
GRDB provide publishers that perform asynchronous database accesses:
- [`readPublisher(receiveOn:value:)`]
- [`writePublisher(receiveOn:updates:)`]
- [`writePublisher(receiveOn:updates:thenRead:)`]
- [`migratePublisher(_:receiveOn:)`]
#### `DatabaseReader.readPublisher(receiveOn:value:)`
This methods returns a publisher that completes after database values have been asynchronously fetched.
```swift
// DatabasePublishers.Read<[Player]>
let players = dbQueue.readPublisher { db in
try Player.fetchAll(db)
}
```
Any attempt at modifying the database completes subscriptions with an error.
When you use a [database queue] or a [database snapshot], the read has to wait for any eventual concurrent database access performed by this queue or snapshot to complete.
When you use a [database pool], reads are generally non-blocking, unless the maximum number of concurrent reads has been reached. In this case, a read has to wait for another read to complete. That maximum number can be [configured].
This publisher can be subscribed from any thread. A new database access starts on every subscription.
The fetched value is published on the main queue, unless you provide a specific [scheduler] to the `receiveOn` argument.
#### `DatabaseWriter.writePublisher(receiveOn:updates:)`
This method returns a publisher that completes after database updates have been successfully executed inside a database transaction.
```swift
// DatabasePublishers.Write
let write = dbQueue.writePublisher { db in
try Player(...).insert(db)
}
// DatabasePublishers.Write
let newPlayerCount = dbQueue.writePublisher { db -> Int in
try Player(...).insert(db)
return try Player.fetchCount(db)
}
```
This publisher can be subscribed from any thread. A new database access starts on every subscription.
It completes on the main queue, unless you provide a specific [scheduler] to the `receiveOn` argument.
When you use a [database pool], and your app executes some database updates followed by some slow fetches, you may profit from optimized scheduling with [`writePublisher(receiveOn:updates:thenRead:)`]. See below.
#### `DatabaseWriter.writePublisher(receiveOn:updates:thenRead:)`
This method returns a publisher that completes after database updates have been successfully executed inside a database transaction, and values have been subsequently fetched:
```swift
// DatabasePublishers.Write
let newPlayerCount = dbQueue.writePublisher(
updates: { db in try Player(...).insert(db) }
thenRead: { db, _ in try Player.fetchCount(db) })
}
```
It publishes exactly the same values as [`writePublisher(receiveOn:updates:)`]:
```swift
// DatabasePublishers.Write
let newPlayerCount = dbQueue.writePublisher { db -> Int in
try Player(...).insert(db)
return try Player.fetchCount(db)
}
```
The difference is that the last fetches are performed in the `thenRead` function. This function accepts two arguments: a readonly database connection, and the result of the `updates` function. This allows you to pass information from a function to the other (it is ignored in the sample code above).
When you use a [database pool], this method applies a scheduling optimization: the `thenRead` function sees the database in the state left by the `updates` function, and yet does not block any concurrent writes. This can reduce database write contention.
When you use a [database queue], the results are guaranteed to be identical, but no scheduling optimization is applied.
This publisher can be subscribed from any thread. A new database access starts on every subscription.
It completes on the main queue, unless you provide a specific [scheduler] to the `receiveOn` argument.
# Database Observation
Database Observation publishers are based on [ValueObservation] and [DatabaseRegionObservation]. Please refer to their documentation for more information. If your application needs change notifications that are not built as Combine publishers, check the general [Database Changes Observation] chapter.
- [`ValueObservation.publisher(in:scheduling:)`]
- [`SharedValueObservation.publisher()`]
- [`DatabaseRegionObservation.publisher(in:)`]
#### `ValueObservation.publisher(in:scheduling:)`
[ValueObservation] tracks changes in database values. You can turn it into a Combine publisher:
```swift
let observation = ValueObservation.tracking { db in
try Player.fetchAll(db)
}
// A publisher with output [Player] and failure Error
let publisher = observation.publisher(in: dbQueue)
```
This publisher has the same behavior as ValueObservation:
- It notifies an initial value before the eventual changes.
- It may coalesce subsequent changes into a single notification.
- It may notify consecutive identical values. You can filter out the undesired duplicates with the `removeDuplicates()` Combine operator, but we suggest you have a look at the [removeDuplicates()](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservation/removeduplicates()) GRDB operator also.
- It only completes when it is cancelled.
- By default, it notifies the initial value, as well as eventual changes and errors, on the main thread, asynchronously.
This can be configured with the `scheduling` argument. It does not accept a Combine scheduler, but a [ValueObservationScheduler](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservationscheduler).
For example, the `.immediate` scheduler makes sure the initial value is notified immediately when the publisher is subscribed. It can help your application update the user interface without having to wait for any asynchronous notifications:
```swift
// Immediate notification of the initial value
let cancellable = observation
.publisher(
in: dbQueue,
scheduling: .immediate) // <-
.sink(
receiveCompletion: { completion in ... },
receiveValue: { (players: [Player]) in print("Fresh players: \(players)") })
// <- here "fresh players" is already printed.
```
Note that the `.immediate` scheduler requires that the publisher is subscribed from the main thread. It raises a fatal error otherwise.
#### `SharedValueObservation.publisher()`
[SharedValueObservation] tracks changes in database values. You can turn it into a Combine publisher:
```swift
let sharedObservation = ValueObservation
.tracking { db in try Player.fetchAll(db) }
.shared(in: dbQueue)
// A publisher with output [Player] and failure Error
let publisher = sharedObservation.publisher()
```
This publisher has the same behavior as SharedValueObservation.
#### `DatabaseRegionObservation.publisher(in:)`
[DatabaseRegionObservation] notifies all transactions that impact a tracked database region. You can turn it into a Combine publisher:
```swift
let request = Player.all()
let observation = DatabaseRegionObservation.tracking(request)
// A publisher with output Database and failure Error
let publisher = observation.publisher(in: dbQueue)
```
This publisher can be created and subscribed from any thread. It delivers database connections in a "protected dispatch queue", serialized with all database updates. It only completes when a database error happens.
```swift
let request = Player.all()
let cancellable = DatabaseRegionObservation
.tracking(request)
.publisher(in: dbQueue)
.sink(
receiveCompletion: { completion in ... },
receiveValue: { (db: Database) in
print("Players have changed.")
})
try dbQueue.write { db in
try Player(name: "Arthur").insert(db)
try Player(name: "Barbara").insert(db)
}
// Prints "Players have changed."
try dbQueue.write { db in
try Player.deleteAll(db)
}
// Prints "Players have changed."
```
See [DatabaseRegionObservation] for more information.
## Combine and Data Consistency
When you compose database publishers together with Combine operators such as `combineLatest` or `zip`, you lose all guarantees of [data consistency](https://en.wikipedia.org/wiki/Consistency_(database_systems)).
This is because each database publisher is isolated from others: each one of them sees its own state of the database. Whenever some database change is interleaved between publisher operations, publishers will process or publish values that may not fit well together.
In other words, whenever you need to perform some database access or observation that depends on some database invariant, make sure you define one and only one database publisher instead of combining several publishers. This is how you will prevent eventual concurrent database writes from messing with your app, and introduce bugs.
To this end, remember that *all database publishers can perform several requests*.
In the example below, we are totally sure that the published `HallOfFame` values will never contain inconsistent values, because it is produced by one and only one publisher:
```swift
struct HallOfFame {
// Invariant: bestPlayers.count <= totalPlayerCount
var totalPlayerCount: Int
var bestPlayers: [Player]
}
// CORRECT: DATA CONSISTENCY GUARANTEED
let hallOfFamePublisher = ValueObservation
.tracking { db -> HallOfFame in
// 1st request
let totalPlayerCount = try Player.fetchCount(db)
// 2nd request
let bestPlayers = try Player
.order(\.score.desc)
.limit(10)
.fetchAll(db)
// 100% guaranteed
assert(bestPlayers.count <= totalPlayerCount)
// Merge results together
return HallOfFame(
totalPlayerCount: totalPlayerCount,
bestPlayers: bestPlayers)
}
.publisher(in: dbQueue)
```
Compare with the incorrect version that combines two database publishers together:
```swift
// OK
let totalPlayerCountPublisher = ValueObservation
.tracking(Player.fetchCount)
.publisher(in: dbQueue)
// OK
let bestPlayerPublisher = ValueObservation
.tracking(Player
.order(\.score.desc)
.limit(10)
.fetchAll)
.publisher(in: dbQueue)
// NOT OK: DATA CONSISTENCY NOT GUARANTEED
let hallOfFamePublisher = totalPlayerCountPublisher
.combineLatest(bestPlayerPublisher)
.map(HallOfFame.init(totalPlayerCount:bestPlayers))
let cancellable = hallOfFamePublisher.sink(
receiveCompletion: { completion in ... },
receiveValue: { hallOfFame in
// ASSERTION MAY FAIL if some players are deleted
// at the wrong time
assert(hallOfFame.bestPlayers.count <= hallOfFame.totalPlayerCount)
})
```
[Database Connections]: ../README.md#database-connections
[Usage]: #usage
[Asynchronous Database Access]: #asynchronous-database-access
[Combine]: https://developer.apple.com/documentation/combine
[Database Changes Observation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseobservation
[Database Observation]: #database-observation
[Combine and Data Consistency]: #combine-and-data-consistency
[DatabaseRegionObservation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseregionobservation
[Demo Application]: DemoApps/GRDBCombineDemo/README.md
[SQLite]: http://sqlite.org
[ValueObservation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservation
[SharedValueObservation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/sharedvalueobservation
[`DatabaseRegionObservation.publisher(in:)`]: #databaseregionobservationpublisherin
[`ValueObservation.publisher(in:scheduling:)`]: #valueobservationpublisherinscheduling
[`SharedValueObservation.publisher()`]: #sharedvalueobservationpublisher
[`readPublisher(receiveOn:value:)`]: #databasereaderreadpublisherreceiveonvalue
[`writePublisher(receiveOn:updates:)`]: #databasewriterwritepublisherreceiveonupdates
[`writePublisher(receiveOn:updates:thenRead:)`]: #databasewriterwritepublisherreceiveonupdatesthenread
[`migratePublisher(_:receiveOn:)`]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasemigrator/migratepublisher(_:receiveon:)
[configured]: ../README.md#databasepool-configuration
[database pool]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasepool
[database queue]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasequeue
[database snapshot]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasesnapshot
[scheduler]: https://developer.apple.com/documentation/combine/scheduler
---
### Documentation/CommonTableExpressions
Common Table Expressions
========================
[**:fire: EXPERIMENTAL**](../README.md#what-are-experimental-features)
---
**Common table expressions** (CTEs) can generally be seen as *SQL views that you define on the fly*.
A certain level of familiarity with SQL databases is helpful before you dive into this guide. The starting point is obviously the [SQLite documentation](https://sqlite.org/lang_with.html). Many CTE tutorials exist online as well, including [this good one](https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/).
In this guide, you will learn how to:
- [Define Common Table Expressions]
- [Embed Common Table Expressions in Requests]
- [Fetch Values From Common Table Expressions]
- Join CTEs with [Associations to Common Table Expressions]
> **Note**: most code examples will be trivial, and not very "useful". This is because the goal of this guide is to stay focused on the GRDB support for CTEs. Rich setup would just be distracting. So bring your own good ideas with you!
## Define Common Table Expressions
You will create a `CommonTableExpression` definition first. Choose a **name**, and a **request** that provides the content of the common table expression.
The CTE name is like a regular table name: pick one that does not conflict with the names of existing tables.
The CTE request can be provided as a [query interface request]:
```swift
// WITH playerName AS (SELECT name FROM player) ...
let playerNameCTE = CommonTableExpression(
named: "playerName",
request: Player.select(\.name))
```
You can feed a CTE with raw SQL as well (second and third examples use [SQL Interpolation]):
```swift
let name = "O'Brien"
// WITH playerName AS (SELECT 'O''Brien') ...
let playerNameCTE = CommonTableExpression(
named: "playerName",
sql: "SELECT ?", arguments: [name])
// WITH playerName AS (SELECT 'O''Brien') ...
let playerNameCTE = CommonTableExpression(
named: "playerName",
literal: "SELECT \(name)")
// WITH playerName AS (SELECT 'O''Brien') ...
let request = SQLRequest("SELECT \(name)")
let playerNameCTE = CommonTableExpression(
named: "playerName",
request: request)
```
All CTEs can be provided with explicit column names:
```swift
// WITH pair(a, b) AS (SELECT 1, 2) ...
let pairCTE = CommonTableExpression(
named: "pair",
columns: ["a", "b"],
sql: "SELECT 1, 2")
```
Recursive CTEs need the `recursive` flag. The example below selects all integers between 1 and 1000:
```swift
// WITH RECURSIVE counter(x) AS
// (VALUES(1) UNION ALL SELECT x+1 FROM counter WHERE x<1000)
let counterCTE = CommonTableExpression(
recursive: true,
named: "counter",
columns: ["x"],
sql: """
VALUES(1)
UNION ALL
SELECT x+1 FROM counter WHERE x<1000
""")
```
> **Note**: many recursive CTEs use the `UNION ALL` SQL operator. The query interface does not provide any Swift support for it, so you'll generally have to write SQL in your definitions of recursive CTEs.
## Embed Common Table Expressions in Requests
A typical SQLite query that uses a common table expression first *defines* the CTE and then *uses* the CTE by mentioning its table name. We'll see below Swift apis that match those two steps.
We will use the (simple) query below as a target. It is the query we'll want to generate in this chapter. It defines a CTE, and uses it in a subquery:
```sql
WITH playerName AS (SELECT 'O''Brien')
SELECT * FROM player
WHERE name = (SELECT * FROM playerName)
```
We first build a `CommonTableExpression`:
```swift
let name = "O'Brien"
let playerNameCTE = CommonTableExpression(
named: "playerName",
literal: "SELECT \(name)")
```
We can then embed the definition of the CTE in a [query interface request] by calling the `with(_:)` method:
```swift
// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player ...
let request = Player
.with(playerNameCTE)...
```
And we can then filter the `player` table with a subquery:
```swift
// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player
// WHERE name = (SELECT * FROM playerName)
let request = Player
.with(playerNameCTE)
.filter { $0.name == playerNameCTE.all() }
```
> **Note**: the `with(_:)` method can be called as many times as a there are common table expressions in your request.
>
> **Note**: the `with(_:)` method can be called at any time, as all request methods: `Player.with(...).filter(...).with(...)`.
>
> **Note**: the `with(_:)` method replaces any previously embedded CTE that has the same table name. This allows you to embed the same CTE several times if you feel like it.
>
> **Note**: the `CommonTableExpression.all()` method builds a regular [query interface request] for the content of the CTE (like `SELECT * FROM `, not to be mismatched with the request that was used to define the CTE). You can filter this request, sort it, etc, like all query interface requests:
>
> ```swift
> cte.all().select(...).filter(...).group(...).order(...)
> ```
Common table expressions can also be embedded in [SQLRequest] with [SQL Interpolation]:
```swift
// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player
// WHERE name = (SELECT * FROM playerName)
let request: SQLRequest = """
WITH \(definitionFor: playerNameCTE)
SELECT * FROM player
WHERE name = (SELECT * FROM \(playerNameCTE))
"""
// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player
// WHERE name = (SELECT * FROM playerName)
let request: SQLRequest = """
WITH \(definitionFor: playerNameCTE)
SELECT * FROM player
WHERE name = (\(playerNameCTE.all()))
"""
```
Common table expressions can also be used as subqueries, when you update or delete rows in the database:
```swift
// WITH playerName AS (SELECT 'O''Brien')
// UPDATE player SET name = (SELECT * FROM playerName)
try Player
.with(playerNameCTE)
.updateAll(db) { $0.name.set(to: playerNameCTE.all()) }
// WITH playerName AS (SELECT 'O''Brien')
// DELETE FROM player WHERE name = (SELECT * FROM playerName)
try Player
.with(playerNameCTE)
.filter { $0.name == playerNameCTE.all() }
.deleteAll(db)
```
## Fetch Values From Common Table Expressions
In the previous chapter, a common table expression was embedded as a subquery, with the `CommonTableExpression.all()` method.
`cte.all()` builds a regular [query interface request] that you can filter, sort, etc, like all query interface requests.
You can also fetch from `cte.all()`, as long as the request is given the definition of the CTE: `cte.all().with(cte)`. In SQL, this would give: `WITH cte AS (...) SELECT * FROM cte`:
This request, of type `QueryInterfaceRequest`, can fetch raw database [rows](../README.md#row-queries):
```swift
let cte = CommonTableExpression(...)
let request = cte.all().with(cte)
let rows = try request.fetchAll(db) // [Row]
```
In order to fetch something else, such as simple [values](../README.md#value-queries), or custom [records](../README.md#records), you have two possible options:
1. Use the `asRequest(of:)` method:
```swift
let cte = CommonTableExpression(...)
let request = cte.all().with(cte).asRequest(of: Player.self)
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~
let players = try request.fetchAll(db) // [Player]
```
2. Provide the fetched type to the cte itself:
```swift
let cte = CommonTableExpression(...)
// ~~~~~~~~
let request = cte.all().with(cte)
let players = try request.fetchAll(db) // [Player]
```
## Associations to Common Table Expressions
GRDB [associations] define "to-one" and "to-many" relationships between two database tables. Here we will define associations between regular tables and common table expressions.
We recommend familiarity with the "joining methods", described in [Joining And Prefetching Associated Records]:
```swift
// SELECT parent.* FROM parent LEFT JOIN child ON ...
Parent.joining(optional: childAssociation)
// SELECT parent.* FROM parent JOIN child ON ...
Parent.joining(required: childAssociation)
// SELECT parent.*, child.* FROM parent LEFT JOIN child ON ...
Parent.including(optional: childAssociation)
// SELECT parent.*, child.* FROM parent JOIN child ON ...
Parent.including(required: childAssociation)
```
> **Note**: common table expressions currently only define "to-one" associations, so the `including(all:)` joining method is unavailable.
CTE associations are generally built with the `association(to:on:)` method, which needs:
- The two sides of the association: a `CommonTableExpression` instance, and another CTE or a type that conforms to the [TableRecord] protocol.
- A function that returns the condition that joins the two sides of the association.
The condition function plays the same role as the **foreign key** that defines regular table [associations] such as **BelongsTo** or **HasMany**. It accepts two [TableAlias], from which you can build a joining expression:
For example:
```swift
// An association from LeftRecord to rightCTE
let rightCTE = ...
let association = LeftRecord.association(
to: rightCTE,
on: { left, right in
left[Column("x")] == right[Column("y")]
})
```
Now this association can be used with a joining method:
```swift
// WITH rightCTE AS (...)
// SELECT leftRecord.*, rightCTE.*
// FROM leftRecord
// JOIN rightCTE ON leftRecord.x = rightCTE.y
LeftRecord
.with(rightCTE)
.including(required: association)
```
### CTE Association Example: a Chat App
As an example, let's build the classical main screen of a chat application: a list of all latest messages from all conversations.
The database schema of the chat app contains a `chat` and a `message` table. The application defines the following records:
```swift
struct Chat: Codable, FetchableRecord, PersistableRecord {
var id: Int64
...
}
struct Message: Codable, FetchableRecord, PersistableRecord {
var chatId: Int64
var date: Date
...
enum Columns {
static let chatId = Column("chatId")
static let date = Column("date")
...
}
}
```
To feed the main app screen, we want to fetch a list of `ChatInfo` records:
```swift
struct ChatInfo: Decodable, FetchableRecord {
/// The chat
var chat: Chat
/// The latest chat message, if any
var latestMessage: Message?
}
```
The SQL request that we want to run is below. It uses an SQLite-specific [special processing](https://sqlite.org/lang_select.html) of `MAX()` that helps the selection of latest messages from all chats:
```sql
WITH latestMessage AS
(SELECT *, MAX(date) FROM message GROUP BY chatID)
SELECT chat.*, latestMessage.*
FROM chat
LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
ORDER BY latestMessage.date DESC
```
We start by defining the CTE request, which loads the latest messages of all chats:
```swift
// SELECT *, MAX(date) FROM message GROUP BY chatID
let latestMessageRequest = Message
.annotated { max($0.date) }
.group(\.chatId)
```
We can now define the CTE for the latest messages:
```swift
// WITH latestMessage AS
// (SELECT *, MAX(date) FROM message GROUP BY chatID)
let latestMessageCTE = CommonTableExpression(
named: "latestMessage",
request: latestMessageRequest)
```
The association from a chat to its latest message follows:
```swift
// ... JOIN latestMessage ON chat.id = latestMessage.chatID
let latestMessage = Chat.association(
to: latestMessageCTE,
on: { chat, latestMessage in
chat.id == latestMessage.chatId
})
.order(\.date.desc)
```
The final request can now be defined:
```swift
// WITH latestMessage AS
// (SELECT *, MAX(date) FROM message GROUP BY chatID)
// SELECT chat.*, latestMessage.*
// FROM chat
// LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
// ORDER BY latestMessage.date DESC
let request = Chat
.with(latestMessageCTE)
.including(optional: latestMessage)
.asRequest(of: ChatInfo.self)
```
And we can fetch the data that feeds our application screen:
```swift
let chatInfos: [ChatInfos] = try dbQueue.read { db in
try request.fetchAll(db)
}
```
> :bulb: **Tip**: the joining methods are generally type-safe: they won't allow you to join apples to oranges. This works when associations have a *precise* type. In this context, anonymous `CommonTableExpression` CTEs can work against type safety. When you want to define associations between several CTEs, and make sure the compiler will notice wrong uses of those associations, tag your common table expressions with an explicit type: `CommonTableExpression`.
>
> To do so, you can use an existing record type, or an ad-hoc enum. For example:
>
> ```swift
> enum CTE1 { }
> let cte1 = CommonTableExpression(...)
>
> enum CTE2 { }
> let cte2 = CommonTableExpression(...)
>
> let assoc1 = BaseRecord.association(to: cte1, on: ...) // from BaseRecord to CTE1
> let assoc2 = cte1.association(to: cte2, on: ...) // from CTE1 to CTE2
> let assoc3 = cte2.association(to: FarRecord.self, on: ...) // from CTE2 to FarRecord
>
> // WITH ...
> // SELECT base.* FROM base
> // JOIN cte1 ON ...
> // JOIN cte2 ON ...
> // JOIN far ON ...
> let request = BaseRecord
> .with(cte1).with(cte2)
> .joining(required: assoc1. // OK
> .joining(required: assoc2. // OK
> .joining(required: assoc3))) // OK
>
> // Compiler error
> let request = BaseRecord
> .joining(required: assoc2) // Not OK
> .joining(required: assoc3) // Not OK
> ```
[query interface request]: ../README.md#requests
[query interface requests]: ../README.md#requests
[SQLRequest]: ../README.md#custom-requests
[SQLiteral]: SQLInterpolation.md
[SQL Interpolation]: SQLInterpolation.md
[associations]: AssociationsBasics.md
[Joining And Prefetching Associated Records]: AssociationsBasics.md#joining-and-prefetching-associated-records
[Define Common Table Expressions]: #define-common-table-expressions
[Embed Common Table Expressions in Requests]: #embed-common-table-expressions-in-requests
[Fetch Values From Common Table Expressions]: #fetch-values-from-common-table-expressions
[Associations to Common Table Expressions]: #associations-to-common-table-expressions
[TableRecord]: ../README.md#tablerecord-protocol
[TableAlias]: AssociationsBasics.md#table-aliases
---
### Documentation/Concurrency
:twisted_rightwards_arrows: Concurrency
=======================================
This guide [has moved](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/concurrency).
---
### Documentation/CustomSQLiteBuilds
Custom SQLite Builds
====================
By default, GRDB uses the version of SQLite that ships with the target operating system.
**You can build GRDB with a custom build of [SQLite 3.47.2](https://www.sqlite.org/changes.html).**
A custom SQLite build can activate extra SQLite features, and extra GRDB features as well, such as support for the [FTS5 full-text search engine](../../../#full-text-search), and [SQLite Pre-Update Hooks](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/transactionobserver).
GRDB builds SQLite with [swiftlyfalling/SQLiteLib](https://github.com/swiftlyfalling/SQLiteLib), which uses the same SQLite configuration as the one used by Apple in its operating systems, and lets you add extra compilation options that leverage the features you need.
> Warning: The technique described here is not compatible with the Swift Package Manager (SPM). It will create [build issues](https://github.com/groue/GRDB.swift/issues/1709) with SPM companion librairies such as [GRDBQuery](https://github.com/groue/GRDBQuery) or [GRDBSnapshotTesting](https://github.com/groue/GRDBSnapshotTesting).
**To install GRDB with a custom SQLite build:**
1. Clone the GRDB git repository, checkout the latest tagged version:
```sh
cd [GRDB directory]
git checkout [latest tag]
git submodule update --init SQLiteCustom/src
```
2. Choose your [extra compilation options](https://www.sqlite.org/compile.html). For example, `SQLITE_ENABLE_FTS5`, `SQLITE_ENABLE_PREUPDATE_HOOK`.
It is recommended that you enable the `SQLITE_ENABLE_SNAPSHOT` option. It allows GRDB to optimize [ValueObservation](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservation) when you use a [Database Pool](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databasepool).
3. Create a folder named `GRDBCustomSQLite` somewhere in your project directory.
4. Create four files in the `GRDBCustomSQLite` folder:
- `SQLiteLib-USER.xcconfig`: this file sets the extra SQLite compilation flags.
```xcconfig
// As many -D options as there are custom SQLite compilation options
// Note: there is no space between -D and the option name.
CUSTOM_SQLLIBRARY_CFLAGS = -DSQLITE_ENABLE_SNAPSHOT -DSQLITE_ENABLE_FTS5
```
- `GRDBCustomSQLite-USER.xcconfig`: this file lets GRDB know about extra compilation flags, and enables extra GRDB APIs.
```xcconfig
// As many -D options as there are custom SQLite compilation options
// Note: there is one space between -D and the option name.
CUSTOM_OTHER_SWIFT_FLAGS = -D SQLITE_ENABLE_SNAPSHOT -D SQLITE_ENABLE_FTS5
```
- `GRDBCustomSQLite-USER.h`: this file lets your application know about extra compilation flags.
```c
// As many #define as there are custom SQLite compilation options
#define SQLITE_ENABLE_SNAPSHOT
#define SQLITE_ENABLE_FTS5
```
- `GRDBCustomSQLite-INSTALL.sh`: this file installs the three other files.
```
/* Detailed source-code truncated for AI context efficiency. */
```
Modify the top of `GRDBCustomSQLite-INSTALL.sh` file so that it contains correct paths.
5. Embed the `GRDBCustom.xcodeproj` project in your own project.
6. Add the `GRDBCustom` target in the **Target Dependencies** section of the **Build Phases** tab of your **application target**.
7. Add the `GRDBCustom.framework` from the targeted platform to the **Embedded Binaries** section of the **General** tab of your **application target**.
8. Add a Run Script phase for your target in the **Pre-actions** section of the **Build** tab of your **application scheme**:
```sh
source "${PROJECT_DIR}/GRDBCustomSQLite/GRDBCustomSQLite-INSTALL.sh"
```
The path should be the path to your `GRDBCustomSQLite-INSTALL.sh` file.
Select your application target in the "Provide build settings from" menu.
9. Check the "Shared" checkbox of your application scheme (this lets you commit the pre-action in your Version Control System).
10. If you have enabled "Hardened Runtime" for your target (**Build Settings**/**Signing**) then you may need to check **Disable Library Validation** under the **Hardened Runtime** section of the **Signing & Capabilities** tab.
(The build error without this exception is "Library not loaded ... different Team IDs")
Now you can use GRDB with your custom SQLite build:
```swift
import GRDB
let dbQueue = try DatabaseQueue(...)
```
---
### Documentation/FetchedRecordsController
# FetchedRecordsController
FetchedRecordsController has been removed in GRDB 5.
See [Database Observation](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseobservation) for other ways to observe the database.
---
### Documentation/FTS5Tokenizers
FTS5 Tokenizers
===============
**[FTS5](https://www.sqlite.org/fts5.html) is an extensible full-text search engine.**
GRDB lets you define your own custom FTS5 tokenizers, and extend SQLite built-in tokenizers. Possible use cases are:
- Have "fi" match the ligature "fi" (U+FB01)
- Have "first" match "1st"
- Have "Encyclopaedia" match "Encyclopædia"
- Have "Mueller" match "Müller", and "Grossmann" match "Großmann"
- Have "romaji" match "ローマ字", and "pinyin" match "拼音"
- Prevent "the" and other stop words from matching any document
**Table of Contents**
- [Tokenizers and Full-Text Search](#tokenizers-and-full-text-search)
- [The Tokenizer Protocols](#the-tokenizer-protocols)
- [Using a Custom Tokenizer](#using-a-custom-tokenizer)
- [FTS5Tokenizer](#fts5tokenizer)
- [FTS5CustomTokenizer](#fts5customtokenizer)
- [FTS5WrapperTokenizer](#fts5wrappertokenizer)
- [Choosing the Wrapped Tokenizer](#choosing-the-wrapped-tokenizer)
- [Example: Synonyms](#example-synonyms)
- [Example: Latin Script](#example-latin-script)
## Tokenizers and Full-Text Search
**A Tokenizer splits text into tokens**. For example, a tokenizer can split "SQLite is a database engine" into the five tokens "SQLite", "is", "a", "database", and "engine".
FTS5 use tokenizers to tokenize both indexed documents and search patterns. **A match between a document and a search pattern happens when both produce *identical* tokens.**
All SQLite [built-in tokenizers](https://www.sqlite.org/fts5.html#tokenizers) tokenize both "SQLite" and "sqlite" into the common lowercase token "sqlite". This is why they are case-insensitive. Generally speaking, different tokenizers achieve different matching by applying different transformations to the input text.
- The [ascii](https://www.sqlite.org/fts5.html#ascii_tokenizer) tokenizer turns all ASCII characters to lowercase. "SQLite is a database engine" gives "sqlite", "is", "a", "database", and "engine". The query "SQLITE DATABASE" will match, because its tokens "sqlite" and "database" are found in the document.
- The [unicode61](https://www.sqlite.org/fts5.html#unicode61_tokenizer) tokenizer remove diacritics from latin characters. Unlike the ascii tokenizer, it will match "Jérôme" with "Jerome", as both produce the same "jerome" token.
- The [porter](https://www.sqlite.org/fts5.html#porter_tokenizer) tokenizer turns English words into their root: "database engine" gives the "databas" and "engin" tokens. The query "database engines" will match, because it produces the same tokens.
However, built-in tokenizers don't match "first" with "1st", because they produce the different "first" and "1st" tokens.
Nor do they match "Grossmann" with "Großmann", because they produce the different "grossmann" and "großmann" tokens.
Custom tokenizers help dealing with these situations. We'll see how to match "Grossmann" and "Großmann" by tokenizing them into "grossmann" (see [latin script](#example-latin-script)). We'll also see how to have "first" and "1st" emit *synonym tokens*, so that they can match too (see [synonyms](#example-synonyms)).
## The Tokenizer Protocols
GRDB lets you use and define FTS5 tokenizers through three protocols:
- [FTS5Tokenizer](#fts5tokenizer): the protocol for all FTS5 tokenizers, including the [built-in tokenizers](https://www.sqlite.org/fts5.html#tokenizers) ascii, unicode61, and porter.
- [FTS5CustomTokenizer](#fts5customtokenizer): the low-level protocol that lets custom tokenizers use the raw [FTS5 C API](https://www.sqlite.org/fts5.html#custom_tokenizers).
- [FTS5WrapperTokenizer](#fts5wrappertokenizer): the high-level protocol for custom tokenizers that post-processes the tokens produced by another FTS5Tokenizer.
## Using a Custom Tokenizer
Once you have a custom tokenizer type that adopts [FTS5CustomTokenizer](#fts5customtokenizer) or [FTS5WrapperTokenizer](#fts5wrappertokenizer), it can fuel the FTS5 engine.
**Register the custom tokenizer into the database:**
```swift
class MyTokenizer : FTS5CustomTokenizer { ... }
var config = Configuration()
config.prepareDatabase { db in
db.add(tokenizer: MyTokenizer.self)
}
let dbQueue = try DatabaseQueue(path: dbPath, configuration: config)
```
**Create [full-text tables](../../../#create-fts5-virtual-tables) that use the custom tokenizer:**
```swift
try db.create(virtualTable: "documents", using: FTS5()) { t in
t.tokenizer = MyTokenizer.tokenizerDescriptor()
t.column("content")
}
```
The full-text table can be fed and queried in [a regular way](../../../#full-text-search):
```swift
try db.execute(sql: "INSERT INTO documents VALUES (?)", arguments: ["..."])
try Document(content: "...").insert(db)
let pattern = FTS5Pattern(matchingAnyTokenIn:"...")
let documents = try Document.matching(pattern).fetchAll(db)
```
## FTS5Tokenizer
**FTS5Tokenizer** is the protocol for all FTS5 tokenizers.
It only requires a tokenization method that matches the low-level `xTokenize` C function documented at https://www.sqlite.org/fts5.html#custom_tokenizers. We'll discuss it more when describing custom tokenizers.
```swift
typealias FTS5TokenCallback = @convention(c) (
_ context: UnsafeMutableRawPointer?,
_ flags: Int32,
_ pToken: UnsafePointer?,
_ nToken: Int32,
_ iStart: Int32,
_ iEnd: Int32) -> Int32
protocol FTS5Tokenizer : class {
func tokenize(
context: UnsafeMutableRawPointer?,
tokenization: FTS5Tokenization,
pText: UnsafePointer?,
nText: Int32,
tokenCallback: FTS5TokenCallback?) -> Int32
}
```
You can instantiate tokenizers, including [built-in tokenizers](https://www.sqlite.org/fts5.html#tokenizers), with the `Database.makeTokenizer()` method:
```swift
let unicode61 = try db.makeTokenizer(.unicode61()) // FTS5Tokenizer
```
## FTS5CustomTokenizer
**FTS5CustomTokenizer** is the low-level protocol for your custom tokenizers.
```swift
protocol FTS5CustomTokenizer : FTS5Tokenizer {
static var name: String { get }
init(db: Database, arguments: [String]) throws
}
```
Custom tokenizers have a name, like built-in tokenizers have a name. Don't use "ascii", "porter", or "unicode61" since they are already taken!
```swift
final class MyTokenizer : FTS5CustomTokenizer {
static let name = "custom"
}
```
SQLite instantiates tokenizers when it needs tokens. The arguments parameter of the `init(db:arguments:)` initializer is an array of strings, which your custom tokenizer can use for its own purposes. In the example below, the arguments will be `["arg1", "arg2"]`.
```swift
// CREATE VIRTUAL TABLE documents USING fts5(
// tokenize='custom arg1 arg2',
// authors, title, body
// )
try db.create(virtualTable: "documents", using: FTS5()) { t in
t.tokenizer = MyTokenizer.tokenizerDescriptor(arguments: ["arg1", "arg2"])
t.column("authors")
t.column("title")
t.column("body")
}
```
FTS5CustomTokenizer inherits from [FTS5Tokenizer](#fts5tokenizer), and performs tokenization with its `tokenize(context:tokenization:pText:nText:tokenCallback:)` method. This low-level method matches the `xTokenize` C function documented at https://www.sqlite.org/fts5.html#custom_tokenizers.
This method arguments are:
- `context`: An opaque pointer that is the first argument to the `tokenCallback` function
- `tokenization`: The reason why FTS5 is requesting tokenization.
- `pText`: The tokenized text bytes. May or may not be nul-terminated.
- `nText`: The number of bytes in the tokenized text.
- `tokenCallback`: The function to call for each found token. It matches the `xToken` callback at https://www.sqlite.org/fts5.html#custom_tokenizers:
- `context`: An opaque pointer
- `flags`: Flags that tell FTS5 how to register the token
- `pToken`: The token bytes. May or may not be nul-terminated.
- `nToken`: The number of bytes in the token
- `iStart`: Byte offset of token within input text
- `iEnd`: Byte offset of end of token within input text
Since tokenization is hard, and pointers to byte buffers uneasy to deal with, you may enjoy the [FTS5WrapperTokenizer](#fts5wrappertokenizer) protocol.
## FTS5WrapperTokenizer
**FTS5WrapperTokenizer** is the high-level protocol for your custom tokenizers. It provides a default implementation for the low-level `tokenize(context:tokenization:pText:nText:tokenCallback:)` method, so that the adopting type does not have to deal with raw byte buffers of the raw [FTS5 C API](https://www.sqlite.org/fts5.html#custom_tokenizers).
A FTS5WrapperTokenizer lets the hard tokenization job to another tokenizer, the "wrapped tokenizer", and post-processes the tokens produced by this wrapped tokenizer.
```swift
protocol FTS5WrapperTokenizer : FTS5CustomTokenizer {
var wrappedTokenizer: any FTS5Tokenizer { get }
func accept(
token: String,
flags: FTS5TokenFlags,
for tokenization: FTS5Tokenization,
tokenCallback: FTS5WrapperTokenCallback) throws
}
```
As all custom tokenizers, wrapper tokenizers must have a name:
```swift
final class MyTokenizer : FTS5WrapperTokenizer {
static let name = "custom"
}
```
The `wrappedTokenizer` property is the mandatory wrapped tokenizer. You instantiate it once, in the initializer:
```swift
final class MyTokenizer : FTS5WrapperTokenizer {
let wrappedTokenizer: any FTS5Tokenizer
init(db: Database, arguments: [String]) throws {
// Wrap the unicode61 tokenizer
wrappedTokenizer = try db.makeTokenizer(.unicode61())
}
}
```
Wrapper tokenizers have to implement the `accept(token:flags:for:tokenCallback:)` method.
For example, a tokenizer that simply passes tokens through gives:
```swift
final class MyTokenizer : FTS5WrapperTokenizer {
func accept(
token: String,
flags: FTS5TokenFlags,
for tokenization: FTS5Tokenization,
tokenCallback: FTS5WrapperTokenCallback) throws
{
// pass through
try tokenCallback(token, flags)
}
}
```
The token argument is a token produced by the wrapped tokenizer, ready to be ignored, modified, or multiplied into several [synonyms](#example-synonyms).
The tokenization parameter tells the reason why tokens are produced, if FTS5 is tokenizing a document, or a search pattern. Some tokenizers may produce different tokens depending on this parameter.
Finally, the tokenCallback is a function you call to output a custom token.
There are a two rules to observe when implementing the accept method:
1. Errors thrown by the tokenCallback function must not be caught (they notify that FTS5 requires the tokenization process to stop immediately).
2. The flags parameter should be given unmodified to the tokenCallback function along with the custom token, unless you union it with the `.colocated` flag when the tokenizer produces [synonyms](#example-synonyms).
### Choosing the Wrapped Tokenizer
The wrapped tokenizer can be hard-coded, or chosen at runtime.
For example, your custom tokenizer can wrap [unicode61](https://www.sqlite.org/fts5.html#unicode61_tokenizer), unless arguments say otherwise (in a fashion similar to the [porter](https://www.sqlite.org/fts5.html#porter_tokenizer) tokenizer):
```swift
final class MyTokenizer : FTS5WrapperTokenizer {
static let name = "custom"
let wrappedTokenizer: any FTS5Tokenizer
init(db: Database, arguments: [String]) throws {
if arguments.isEmpty {
wrappedTokenizer = try db.makeTokenizer(.unicode61())
} else {
let descriptor = FTS5TokenizerDescriptor(components: arguments)
wrappedTokenizer = try db.makeTokenizer(descriptor)
}
}
}
```
Arguments are provided when the virtual table is created:
```swift
// CREATE VIRTUAL TABLE documents USING fts5(
// tokenize='custom',
// content
// )
try db.create(virtualTable: "documents", using: FTS5()) { t in
// Wraps the default unicode61
t.tokenizer = MyTokenizer.tokenizerDescriptor()
t.column("content")
}
// CREATE VIRTUAL TABLE documents USING fts5(
// tokenize='custom ascii'
// content
// )
try db.create(virtualTable: "documents", using: FTS5()) { t in
// Wraps ascii
let ascii = FTS5TokenizerDescriptor.ascii()
t.tokenizer = MyTokenizer.tokenizerDescriptor(arguments: ascii.components)
t.column("content")
}
```
## Example: Synonyms
**FTS5 lets tokenizers produce synonyms**, so that, for example, "first" can match "1st".
The topic of synonyms is documented at https://www.sqlite.org/fts5.html#synonym_support, which describes several methods. You should carefully read this documentation, and pick the method you prefer.
In the example below, we'll pick method (3), and implement a tokenizer that adds multiple synonyms for a single term to the FTS index. Using this method, when tokenizing document text, the tokenizer provides multiple synonyms for each token. So that when a document such as "I won first place" is tokenized, entries are added to the FTS index for "i", "won", "first", "1st" and "place".
We'll also take care of the SQLite advice:
> When using methods (2) or (3), it is important that the tokenizer only provide synonyms when tokenizing document text or query text, not both. Doing so will not cause any errors, but is inefficient.
```swift
final class SynonymsTokenizer : FTS5WrapperTokenizer {
static let name = "synonyms"
let wrappedTokenizer: any FTS5Tokenizer
let synonyms: [Set] = [["first", "1st"]]
init(db: Database, arguments: [String]) throws {
wrappedTokenizer = try db.makeTokenizer(.unicode61())
}
func synonyms(for token: String) -> Set? {
synonyms.first { $0.contains(token) }
}
func accept(token: String, flags: FTS5TokenFlags, for tokenization: FTS5Tokenization, tokenCallback: FTS5WrapperTokenCallback) throws {
if tokenization.contains(.query) {
// Don't look for synonyms when tokenizing queries
try tokenCallback(token, flags)
return
}
guard let synonyms = synonyms(for: token) else {
// Token has no synonym
try tokenCallback(token, flags)
return
}
for (index, synonym) in synonyms.enumerated() {
// Notify each synonym, and set the colocated flag for all but the first
let synonymFlags = (index == 0) ? flags : flags.union(.colocated)
try tokenCallback(synonym, synonymFlags)
}
}
}
```
## Example: Latin Script
Languages that use the [latin script](https://en.wikipedia.org/wiki/Latin_script) offer a rich set of typographical, historical, and local features such as diacritics, ligatures, and dotless I: Großmann, fidélité (with the ligature "fi" U+FB01), Diyarbakır.
Full-text search in such a corpus often needs input tolerance, so that "encyclopaedia" can match "Encyclopædia", "Grossmann", "Großmann", and "Jerome", "Jérôme".
German has something specific in that both "Mueller" and "Muller" should match "Müller", when "Bauer" should not match "Baur" (only "ü" accepts both "u" and "ue"). A pull request that adds a chapter about German will be welcome.
A custom FTS5 tokenizer lets you provide fuzzy latin matching: after "Grossmann", "Großmann", and "GROSSMANN" have all been turned into "grossmann", they will all match together.
We'll wrap the built-in [unicode61](https://www.sqlite.org/fts5.html#unicode61_tokenizer) tokenizer (the one that knows how to split text on spaces and punctuations), and transform its tokens into their bare lowercase ascii form.
The tokenizer wrapping is provided by the [FTS5WrapperTokenizer](#fts5wrappertokenizer) protocol. The string transformation is provided by the [String.applyingTransform](https://developer.apple.com/reference/swift/string/1643133-applyingtransform) method:
```swift
final class LatinAsciiTokenizer : FTS5WrapperTokenizer {
static let name = "latinascii"
let wrappedTokenizer: any FTS5Tokenizer
init(db: Database, arguments: [String]) throws {
wrappedTokenizer = try db.makeTokenizer(.unicode61())
}
func accept(token: String, flags: FTS5TokenFlags, for tokenization: FTS5Tokenization, tokenCallback: FTS5WrapperTokenCallback) throws {
if let token = token.applyingTransform(StringTransform("Latin-ASCII; Lower"), reverse: false) {
try tokenCallback(token, flags)
}
}
}
```
Remember to register LatinAsciiTokenizer before using it:
```swift
dbQueue.add(tokenizer: LatinAsciiTokenizer.self) // or dbPool.add
dbQueue.inDatabase { db in
try db.create(virtualTable: "documents", using: FTS5()) { t in
t.tokenizer = LatinAsciiTokenizer.tokenizerDescriptor()
t.column("authors")
t.column("title")
t.column("body")
}
}
```
---
### Documentation/FullTextSearch
Full-Text Search
================
**Full-Text Search is an efficient way to search a corpus of textual documents.**
```swift
// Create full-text tables
try db.create(virtualTable: "book", using: FTS4()) { t in // or FTS3(), or FTS5()
t.column("author")
t.column("title")
t.column("body")
}
// Populate full-text table with records or SQL
try Book(...).insert(db)
try db.execute(
sql: "INSERT INTO book (author, title, body) VALUES (?, ?, ?)",
arguments: [...])
// Build search patterns
let pattern = FTS3Pattern(matchingPhrase: "Moby-Dick")
// Search with the query interface or SQL
let books = try Book.matching(pattern).fetchAll(db)
let books = try Book.fetchAll(db,
sql: "SELECT * FROM book WHERE book MATCH ?",
arguments: [pattern])
```
- **[Choosing the Full-Text Engine](#choosing-the-full-text-engine)**
- **Create Full-Text Virtual Tables**: [FTS3/4](#create-fts3-and-fts4-virtual-tables), [FTS5](#create-fts5-virtual-tables)
- **Choosing a Tokenizer**: [FTS3/4](#fts3-and-fts4-tokenizers), [FTS5](#fts5-tokenizers)
- **Tokenization**: [FTS3/4](#fts3-and-fts4-tokenization), [FTS5](#fts5-tokenization)
- **Search Patterns**: [FTS3/4](#fts3pattern), [FTS5](#fts5pattern)
- **Sorting by Relevance**: [FTS5](#fts5-sorting-by-relevance)
- **External Content Full-Text Tables**: [FTS4/5](#external-content-full-text-tables)
- **Full-Text Record**s: [FTS3/4/5](#full-text-records)
- **Unicode Full-Text Gotchas**: [FTS3/4/5](#unicode-full-text-gotchas). Unicorns don't exist.
- **Custom Tokenizers**: [FTS5](FTS5Tokenizers.md). Leverage extra full-text features such as synonyms or stop words. Avoid [unicode gotchas](#unicode-full-text-gotchas).
- **Sample Code**: [WWDC Companion](https://github.com/groue/WWDCCompanion), an iOS app that stores, displays, and lets the user search the WWDC 2016 sessions.
## Choosing the Full-Text Engine
**SQLite supports three full-text engines: [FTS3, FTS4](https://www.sqlite.org/fts3.html) and [FTS5](https://www.sqlite.org/fts5.html).**
Generally speaking, FTS5 is better than FTS4 which improves on FTS3. But this does not really tell which engine to choose for your application. Instead, make your choice depend on:
- **The full-text features needed by the application**:
| Full-Text Needs | FTS3 | FTS4 | FTS5 |
| -------------------------------------------------------------------------- | :--: | :--: | :--: |
| :question: Queries | | | |
| **Words searches** (documents that contain "database") | X | X | X |
| **Prefix searches** (documents that contain a word starting with "data") | X | X | X |
| **Phrases searches** (documents that contain the phrase "SQLite database") | X | X | X |
| **Boolean searches** (documents that contain "SQLite" or "database") | X | X | X |
| **Proximity search** (documents that contain "SQLite" near "database") | X | X | X |
| :scissors: Tokenization | | | |
| **Ascii case insensitivity** (have "DATABASE" match "database") | X | X | X |
| **Unicode case insensitivity** (have "ÉLÉGANCE" match "élégance") | X | X | X |
| **Latin diacritics insensitivity** (have "elegance" match "élégance") | X | X | X |
| **English Stemming** (have "frustration" match "frustrated") | X | X | X |
| **English Stemming and Ascii case insensitivity** | X | X | X |
| **English Stemming and Unicode case insensitivity** | | | X |
| **English Stemming and Latin diacritics insensitivity** | | | X |
| **Synonyms** (have "1st" match "first") | ¹ | ¹ | X ² |
| **Pinyin and Romaji** (have "romaji" match "ローマ字") | ¹ | ¹ | X ² |
| **Stop words** (don't index, and don't match words like "and" and "the") | ¹ | ¹ | X ² |
| **Spell checking** (have "alamaba" match "alabama") | ¹ | ¹ | ¹ |
| :bowtie: Other Features | | | |
| **Ranking** (sort results by relevance) | ¹ | ¹ | X |
| **Snippets** (display a few words around a match) | X | X | X |
¹ Requires extra setup, possibly hard to implement.
² Requires a [custom tokenizer](FTS5Tokenizers.md).
For a full feature list, read the SQLite documentation. Some missing features can be achieved with extra application code.
- **The speed versus disk space constraints.** Roughly speaking, FTS4 and FTS5 are faster than FTS3, but use more space. FTS4 only supports content compression.
- **The location of the indexed text in your database schema.** Only FTS4 and FTS5 support "contentless" and "external content" tables.
- See [FTS3 vs. FTS4](https://www.sqlite.org/fts3.html#differences_between_fts3_and_fts4) and [FTS5 vs. FTS3/4](https://www.sqlite.org/fts5.html#appendix_a) for more differences.
> **Note**: In case you were still wondering, it is recommended to read the SQLite documentation: [FTS3 & FTS4](https://www.sqlite.org/fts3.html) and [FTS5](https://www.sqlite.org/fts5.html).
## Create FTS3 and FTS4 Virtual Tables
**FTS3 and FTS4 full-text tables store and index textual content.**
Create tables with the `create(virtualTable:options:using:_:)` method:
```swift
// CREATE VIRTUAL TABLE document USING fts3(content)
try db.create(virtualTable: "document", using: FTS3()) { t in
t.column("content")
}
// CREATE VIRTUAL TABLE document USING fts4(content)
try db.create(virtualTable: "document", using: FTS4()) { t in
t.column("content")
}
```
**All columns in a full-text table contain text.** If you need to index a table that contains other kinds of values, you need an ["external content" full-text table](#external-content-full-text-tables).
You can specify a [tokenizer](#fts3-and-fts4-tokenizers):
```swift
// CREATE VIRTUAL TABLE book USING fts4(
// tokenize=porter,
// author,
// title,
// body
// )
try db.create(virtualTable: "book", using: FTS4()) { t in
t.tokenizer = .porter
t.column("author")
t.column("title")
t.column("body")
}
```
FTS4 supports [options](https://www.sqlite.org/fts3.html#fts4_options):
```swift
// CREATE VIRTUAL TABLE book USING fts4(
// content,
// uuid,
// content="",
// compress=zip,
// uncompress=unzip,
// prefix="2,4",
// notindexed=uuid,
// languageid=lid
// )
try db.create(virtualTable: "document", using: FTS4()) { t in
t.content = ""
t.compress = "zip"
t.uncompress = "unzip"
t.prefixes = [2, 4]
t.column("content")
t.column("uuid").notIndexed()
t.column("lid").asLanguageId()
}
```
The `content` option is involved in "contentless" and "external content" full-text tables. GRDB can help you defining full-text tables that automatically synchronize with their content table. See [External Content Full-Text Tables](#external-content-full-text-tables).
See [SQLite documentation](https://www.sqlite.org/fts3.html) for more information.
## FTS3 and FTS4 Tokenizers
**A tokenizer defines what "matching" means.** Depending on the tokenizer you choose, full-text searches won't return the same results.
SQLite ships with three built-in FTS3/4 tokenizers: `simple`, `porter` and `unicode61` that use different algorithms to match queries with indexed content:
```swift
try db.create(virtualTable: "book", using: FTS4()) { t in
// Pick one:
t.tokenizer = .simple // default
t.tokenizer = .porter
t.tokenizer = .unicode61(...)
}
```
See below some examples of matches:
| content | query | simple | porter | unicode61 |
| ----------- | ---------- | :----: | :----: | :-------: |
| Foo | Foo | X | X | X |
| Foo | FOO | X | X | X |
| Jérôme | Jérôme | X ¹ | X ¹ | X ¹ |
| Jérôme | JÉRÔME | | | X ¹ |
| Jérôme | Jerome | | | X ¹ |
| Database | Databases | | X | |
| Frustration | Frustrated | | X | |
¹ Don't miss [Unicode Full-Text Gotchas](#unicode-full-text-gotchas)
- **simple**
```swift
try db.create(virtualTable: "book", using: FTS4()) { t in
t.tokenizer = .simple // default
}
```
The default "simple" tokenizer is case-insensitive for ASCII characters. It matches "foo" with "FOO", but not "Jérôme" with "JÉRÔME".
It does not provide stemming, and won't match "databases" with "database".
It does not strip diacritics from latin script characters, and won't match "jérôme" with "jerome".
- **porter**
```swift
try db.create(virtualTable: "book", using: FTS4()) { t in
t.tokenizer = .porter
}
```
The "porter" tokenizer compares English words according to their roots: it matches "database" with "databases", and "frustration" with "frustrated".
It does not strip diacritics from latin script characters, and won't match "jérôme" with "jerome".
- **unicode61**
```swift
try db.create(virtualTable: "book", using: FTS4()) { t in
t.tokenizer = .unicode61()
t.tokenizer = .unicode61(diacritics: .keep)
}
```
The "unicode61" tokenizer is case-insensitive for unicode characters. It matches "Jérôme" with "JÉRÔME".
It strips diacritics from latin script characters by default, and matches "jérôme" with "jerome". This behavior can be disabled, as in the example above.
It does not provide stemming, and won't match "databases" with "database".
See [SQLite tokenizers](https://www.sqlite.org/fts3.html#tokenizer) for more information.
## FTS3 and FTS4 Tokenization
You can tokenize strings when needed:
```swift
// Default tokenization using the `simple` tokenizer:
FTS3.tokenize("SQLite database") // ["sqlite", "database"]
FTS3.tokenize("Gustave Doré") // ["gustave", "doré"])
// Tokenization with an explicit tokenizer:
FTS3.tokenize("SQLite database", withTokenizer: .porter) // ["sqlite", "databas"]
FTS3.tokenize("Gustave Doré", withTokenizer: .unicode61()) // ["gustave", "dore"])
```
## FTS3Pattern
**Full-text search in FTS3 and FTS4 tables is performed with search patterns:**
- `database` matches all documents that contain "database"
- `data*` matches all documents that contain a word starting with "data"
- `SQLite database` matches all documents that contain both "SQLite" and "database"
- `SQLite OR database` matches all documents that contain "SQLite" or "database"
- `"SQLite database"` matches all documents that contain the "SQLite database" phrase
**Not all search patterns are valid**: they must follow the [Full-Text Index Queries Grammar](https://www.sqlite.org/fts3.html#full_text_index_queries).
The FTS3Pattern type helps you validating patterns, and building valid patterns from untrusted strings (such as strings typed by users):
```swift
struct FTS3Pattern {
init(rawPattern: String) throws
init?(matchingAnyTokenIn string: String)
init?(matchingAllTokensIn string: String)
init?(matchingAllPrefixesIn string: String)
init?(matchingPhrase string: String)
}
```
The first initializer validates your raw patterns against the query grammar, and may throw a [DatabaseError](../README.md#databaseerror):
```swift
// OK: FTS3Pattern
let pattern = try FTS3Pattern(rawPattern: "sqlite AND database")
// DatabaseError: malformed MATCH expression: [AND]
let pattern = try FTS3Pattern(rawPattern: "AND")
```
The other initializers don't throw. They build a valid pattern from any string, **including strings provided by users of your application**. They let you find documents that match any given word, all given words or prefixes, or a full phrase, depending on the needs of your application:
```swift
let query = "SQLite database"
// Matches documents that contain "SQLite" or "database"
let pattern = FTS3Pattern(matchingAnyTokenIn: query)
// Matches documents that contain "SQLite" and "database"
let pattern = FTS3Pattern(matchingAllTokensIn: query)
// Matches documents that contain words that start with "SQLite" and words that start with "database"
let pattern = FTS3Pattern(matchingAllPrefixesIn: query)
// Matches documents that contain "SQLite database"
let pattern = FTS3Pattern(matchingPhrase: query)
```
They return nil when no pattern could be built from the input string:
```swift
let pattern = FTS3Pattern(matchingAnyTokenIn: "") // nil
let pattern = FTS3Pattern(matchingAnyTokenIn: "*") // nil
```
FTS3Pattern are regular [values](../README.md#values). You can use them as query [arguments](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/statementarguments):
```swift
let documents = try Document.fetchAll(db,
sql: "SELECT * FROM document WHERE content MATCH ?",
arguments: [pattern])
```
Use them in the [query interface](../README.md#the-query-interface):
```swift
// Search in all columns
let documents = try Document.matching(pattern).fetchAll(db)
// Search in a specific column:
let documents = try Document.filter { $0.content.match(pattern) }.fetchAll(db)
```
## Create FTS5 Virtual Tables
**FTS5 full-text tables store and index textual content.**
To use FTS5, you'll need a [custom SQLite build] that activates the `SQLITE_ENABLE_FTS5` compilation option.
Create FTS5 tables with the `create(virtualTable:options:using:_:)` method:
```swift
// CREATE VIRTUAL TABLE document USING fts5(content)
try db.create(virtualTable: "document", using: FTS5()) { t in
t.column("content")
}
```
**All columns in a full-text table contain text.** If you need to index a table that contains other kinds of values, you need an ["external content" full-text table](#external-content-full-text-tables).
You can specify a [tokenizer](#fts5-tokenizers):
```swift
// CREATE VIRTUAL TABLE book USING fts5(
// tokenize='porter',
// author,
// title,
// body
// )
try db.create(virtualTable: "book", using: FTS5()) { t in
t.tokenizer = .porter()
t.column("author")
t.column("title")
t.column("body")
}
```
FTS5 supports [options](https://www.sqlite.org/fts5.html#fts5_table_creation_and_initialization):
```swift
// CREATE VIRTUAL TABLE book USING fts5(
// content,
// uuid UNINDEXED,
// content='table',
// content_rowid='id',
// prefix='2 4',
// columnsize=0,
// detail=column
// )
try db.create(virtualTable: "document", using: FTS5()) { t in
t.column("content")
t.column("uuid").notIndexed()
t.content = "table"
t.contentRowID = "id"
t.prefixes = [2, 4]
t.columnSize = 0
t.detail = "column"
}
```
The `content` and `contentRowID` options are involved in "contentless" and "external content" full-text tables. GRDB can help you defining full-text tables that automatically synchronize with their content table. See [External Content Full-Text Tables](#external-content-full-text-tables).
See [SQLite documentation](https://www.sqlite.org/fts5.html) for more information.
## FTS5 Tokenizers
**A tokenizer defines what "matching" means.** Depending on the tokenizer you choose, full-text searches won't return the same results.
SQLite ships with three built-in FTS5 tokenizers: `ascii`, `porter` and `unicode61` that use different algorithms to match queries with indexed content.
```swift
try db.create(virtualTable: "book", using: FTS5()) { t in
// Pick one:
t.tokenizer = .unicode61() // default
t.tokenizer = .unicode61(...)
t.tokenizer = .ascii
t.tokenizer = .porter(...)
}
```
See below some examples of matches:
| content | query | ascii | unicode61 | porter on ascii | porter on unicode61 |
| ----------- | ---------- | :----: | :-------: | :-------------: | :-----------------: |
| Foo | Foo | X | X | X | X |
| Foo | FOO | X | X | X | X |
| Jérôme | Jérôme | X ¹ | X ¹ | X ¹ | X ¹ |
| Jérôme | JÉRÔME | | X ¹ | | X ¹ |
| Jérôme | Jerome | | X ¹ | | X ¹ |
| Database | Databases | | | X | X |
| Frustration | Frustrated | | | X | X |
¹ Don't miss [Unicode Full-Text Gotchas](#unicode-full-text-gotchas)
- **unicode61**
```swift
try db.create(virtualTable: "book", using: FTS5()) { t in
t.tokenizer = .unicode61()
t.tokenizer = .unicode61(diacritics: .keep)
}
```
The default "unicode61" tokenizer is case-insensitive for unicode characters. It matches "Jérôme" with "JÉRÔME".
It strips diacritics from latin script characters by default, and matches "jérôme" with "jerome". This behavior can be disabled, as in the example above.
It does not provide stemming, and won't match "databases" with "database".
- **ascii**
```swift
try db.create(virtualTable: "book", using: FTS5()) { t in
t.tokenizer = .ascii()
}
```
The "ascii" tokenizer is case-insensitive for ASCII characters. It matches "foo" with "FOO", but not "Jérôme" with "JÉRÔME".
It does not provide stemming, and won't match "databases" with "database".
It does not strip diacritics from latin script characters, and won't match "jérôme" with "jerome".
- **porter**
```swift
try db.create(virtualTable: "book", using: FTS5()) { t in
t.tokenizer = .porter() // porter wrapping unicode61 (the default)
t.tokenizer = .porter(.ascii()) // porter wrapping ascii
t.tokenizer = .porter(.unicode61(diacritics: .keep)) // porter wrapping unicode61 without diacritics stripping
}
```
The porter tokenizer is a wrapper tokenizer which compares English words according to their roots: it matches "database" with "databases", and "frustration" with "frustrated".
It strips diacritics from latin script characters if it wraps unicode61, and does not if it wraps ascii (see the example above).
See [SQLite tokenizers](https://www.sqlite.org/fts5.html#tokenizers) for more information, and [custom FTS5 tokenizers](FTS5Tokenizers.md) in order to add your own tokenizers.
## FTS5 Tokenization
You can tokenize strings when needed:
```swift
let ascii = try db.makeTokenizer(.ascii())
// Tokenize an FTS5 query
for (token, flags) in try ascii.tokenize(query: "SQLite database") {
print(token) // Prints "sqlite" then "database"
}
// Tokenize an FTS5 document
for (token, flags) in try ascii.tokenize(document: "SQLite database") {
print(token) // Prints "sqlite" then "database"
}
```
Some tokenizers may produce a different output when you tokenize a query or a document (see `FTS5_TOKENIZE_QUERY` and `FTS5_TOKENIZE_DOCUMENT` in https://www.sqlite.org/fts5.html#custom_tokenizers). You should generally use `tokenize(query:)` when you intend to tokenize a string in order to compose a [raw search pattern](#fts5pattern).
See the `FTS5_TOKEN_*` flags in https://www.sqlite.org/fts5.html#custom_tokenizers for more information about token flags. In particular, tokenizers that support synonyms may output multiple tokens for a single word, along with the `.colocated` flag.
## FTS5Pattern
**Full-text search in FTS5 tables is performed with search patterns:**
- `database` matches all documents that contain "database"
- `data*` matches all documents that contain a word starting with "data"
- `SQLite database` matches all documents that contain both "SQLite" and "database"
- `SQLite OR database` matches all documents that contain "SQLite" or "database"
- `"SQLite database"` matches all documents that contain the "SQLite database" phrase
**Not all search patterns are valid**: they must follow the [Full-Text Query Syntax](https://www.sqlite.org/fts5.html#full_text_query_syntax).
The FTS5Pattern type helps you validating patterns, and building valid patterns from untrusted strings (such as strings typed by users):
```swift
extension Database {
func makeFTS5Pattern(rawPattern: String, forTable table: String) throws -> FTS5Pattern
}
struct FTS5Pattern {
init?(matchingAnyTokenIn string: String)
init?(matchingAllTokensIn string: String)
init?(matchingAllPrefixesIn string: String)
init?(matchingPhrase string: String)
init?(matchingPrefixPhrase string: String)
}
```
The `Database.makeFTS5Pattern(rawPattern:forTable:)` method validates your raw patterns against the query grammar and the columns of the targeted table, and may throw a [DatabaseError](../README.md#databaseerror):
```swift
// OK: FTS5Pattern
try db.makeFTS5Pattern(rawPattern: "sqlite", forTable: "book")
// DatabaseError: syntax error near \"AND\"
try db.makeFTS5Pattern(rawPattern: "AND", forTable: "book")
// DatabaseError: no such column: missing
try db.makeFTS5Pattern(rawPattern: "missing: sqlite", forTable: "book")
```
The FTS5Pattern initializers don't throw. They build a valid pattern from any string, **including strings provided by users of your application**. They let you find documents that match all given words, any given word, or a full phrase, depending on the needs of your application:
```swift
let query = "SQLite database"
// Matches documents that contain "SQLite" or "database"
let pattern = FTS5Pattern(matchingAnyTokenIn: query)
// Matches documents that contain "SQLite" and "database"
let pattern = FTS5Pattern(matchingAllTokensIn: query)
// Matches documents that contain words that start with "SQLite" and words that start with "database"
let pattern = FTS5Pattern(matchingAllPrefixesIn: query)
// Matches documents that contain "SQLite database"
let pattern = FTS5Pattern(matchingPhrase: query)
// Matches documents that start with "SQLite database"
let pattern = FTS5Pattern(matchingPrefixPhrase: query)
```
They return nil when no pattern could be built from the input string:
```swift
let pattern = FTS5Pattern(matchingAnyTokenIn: "") // nil
let pattern = FTS5Pattern(matchingAnyTokenIn: "*") // nil
```
FTS5Pattern are regular [values](../README.md#values). You can use them as query [arguments](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/statementarguments):
```swift
let documents = try Document.fetchAll(db,
sql: "SELECT * FROM document WHERE document MATCH ?",
arguments: [pattern])
```
Use them in the [query interface](../README.md#the-query-interface):
```swift
// Search in all columns
let documents = try Document.matching(pattern).fetchAll(db)
// Search in a specific column:
let documents = try Document.filter { $0.content.match(pattern) }.fetchAll(db)
```
## FTS5: Sorting by Relevance
**FTS5 can sort results by relevance** (most to least relevant):
```swift
// SQL
let documents = try Document.fetchAll(db,
sql: "SELECT * FROM document WHERE document MATCH ? ORDER BY rank",
arguments: [pattern])
// Query Interface
let documents = try Document.matching(pattern).order(Column.rank).fetchAll(db)
```
For more information about the ranking algorithm, as well as extra options, read [Sorting by Auxiliary Function Results](https://www.sqlite.org/fts5.html#sorting_by_auxiliary_function_results)
GRDB does not provide any ranking for FTS3 and FTS4. See SQLite's [Search Application Tips](https://www.sqlite.org/fts3.html#appendix_a) if you really need it.
## External Content Full-Text Tables
**An external content table does not store the indexed text.** Instead, it indexes the text stored in another table.
This is very handy when you want to index a table that can not be declared as a full-text table (because it contains non-textual values, for example). You just have to define an external content full-text table that refers to the regular table.
The two tables must be kept up-to-date, so that the full-text index matches the content of the regular table. This synchronization happens automatically if you use the `synchronize(withTable:)` method in your full-text table definition:
```swift
// A regular table
try db.create(table: "book") { t in
t.column("author", .text)
t.column("title", .text)
t.column("content", .text)
...
}
// A full-text table synchronized with the regular table
try db.create(virtualTable: "book_ft", using: FTS4()) { t in // or FTS5()
t.synchronize(withTable: "book")
t.column("author")
t.column("title")
t.column("content")
}
```
The eventual content already present in the regular table is indexed, and every insert, update or delete that happens in the regular table is automatically applied to the full-text index.
For more information, see the SQLite documentation about external content tables: [FTS4](https://www.sqlite.org/fts3.html#_external_content_fts4_tables_), [FTS5](https://sqlite.org/fts5.html#external_content_tables).
See also [WWDC Companion](https://github.com/groue/WWDCCompanion), a sample app that uses external content tables to store, display, and let the user search the WWDC sessions.
### Deleting Synchronized Full-Text Tables
Synchronization of full-text tables with their content table happens by the mean of SQL triggers.
SQLite automatically deletes those triggers when the content (not full-text) table is dropped.
However, those triggers remain after the full-text table has been dropped. Unless they are dropped too, they will prevent future insertion, updates, and deletions in the content table, and the creation of a new full-text table.
To drop those triggers, use the `dropFTS4SynchronizationTriggers` or `dropFTS5SynchronizationTriggers` methods:
```swift
// Create tables
try db.create(table: "book") { t in
...
}
try db.create(virtualTable: "book_ft", using: FTS4()) { t in
t.synchronize(withTable: "book")
...
}
// Drop full-text table
try db.drop(table: "book_ft")
try db.dropFTS4SynchronizationTriggers(forTable: "book_ft")
```
> **Warning**: there was a bug in GRDB up to version 2.3.1 included, which created triggers with a wrong name. If it is possible that the full-text table was created by an old version of GRDB, then delete the synchronization triggers **twice**: once with the name of the deleted full-text table, and once with the name of the content table:
>
> ```swift
> // Drop full-text table
> try db.drop(table: "book_ft")
> try db.dropFTS4SynchronizationTriggers(forTable: "book_ft")
> try db.dropFTS4SynchronizationTriggers(forTable: "book") // Support for GRDB <= 2.3.1
> ```
### Querying External Content Full-Text Tables
When you need to perform a full-text search, and the external content table contains all the data you need, you can simply query the full-text table.
But if you need to load columns from the regular table, and in the same time perform a full-text search, then you will need to query both tables at the same time.
That is because SQLite will throw an error when you try to perform a full-text search on a regular table:
```swift
// SQLite error 1: unable to use function MATCH in the requested context
// SELECT * FROM book WHERE book MATCH '...'
let books = Book.matching(pattern).fetchAll(db)
```
The solution is to perform a joined request, using raw SQL:
```swift
let sql = """
SELECT book.*
FROM book
JOIN book_ft
ON book_ft.rowid = book.rowid
AND book_ft MATCH ?
"""
let books = Book.fetchAll(db, sql: sql, arguments: [pattern])
```
## Full-Text Records
**You can define [record](../README.md#records) types around the full-text virtual tables.**
The primary key of those tables is the hidden `rowid` column. If you need to fetch, delete, and update full-text records by primary key, you will have to expose this column to the record type. See [The Database Schema](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseschema) for more information.
## Unicode Full-Text Gotchas
The SQLite built-in tokenizers for [FTS3, FTS4](#fts3-and-fts4-tokenizers) and [FTS5](#fts5-tokenizers) are generally unicode-aware, with a few caveats, and limitations.
Generally speaking, matches may fail when content and query don't use the same [unicode normalization](http://unicode.org/reports/tr15/). SQLite actually exhibits inconsistent behavior in this regard.
For example, for "aimé" to match "aimé", they better have the same normalization: the NFC "aim\u{00E9}" form may not match its NFD "aime\u{0301}" equivalent. Most strings that you get from Swift, UIKit and Cocoa use NFC, so be careful with NFD inputs (such as strings from the HFS+ file system, or strings that you can't trust like network inputs). Use [String.precomposedStringWithCanonicalMapping](https://developer.apple.com/documentation/foundation/nsstring/1412645-precomposedstringwithcanonicalma) to turn a string into NFC.
Besides, if you want "fi" to match the ligature "fi" (U+FB01), then you need to normalize your indexed contents and inputs to NFKC or NFKD. Use [String.precomposedStringWithCompatibilityMapping](https://developer.apple.com/documentation/foundation/nsstring/1412625-precomposedstringwithcompatibili) to turn a string into NFKC.
Unicode normalization is not the end of the story, because it won't help "Encyclopaedia" match "Encyclopædia", "Mueller", "Müller", "Grossmann", "Großmann", or "Diyarbakır", "DIYARBAKIR". The [String.applyingTransform](https://developer.apple.com/documentation/foundation/nsstring/1407787-applyingtransform) method can help.
GRDB lets you write [custom FTS5 tokenizers](FTS5Tokenizers.md) that can transparently deal with all these issues. For FTS3 and FTS4, you'll need to pre-process your strings before injecting them in the full-text engine.
Happy indexing!
---
### Documentation/GoodPracticesForDesigningRecordTypes
Good Practices for Designing Record Types
=========================================
This guide [has moved](https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/recordrecommendedpractices).
---
### Documentation/GRDB5MigrationGuide
Migrating From GRDB 4 to GRDB 5
===============================
**This guide aims at helping you upgrading your applications from GRDB 4 to GRDB 5.**
- [Preparing the Migration to GRDB 5](#preparing-the-migration-to-grdb-5)
- [New requirements](#new-requirements)
- [Database Configuration](#database-configuration)
- [ValueObservation](#valueobservation)
- [Combine Integration](#combine-integration)
- [Other Changes](#other-changes)
## Preparing the Migration to GRDB 5
If you haven't made it yet, upgrade to the [latest GRDB 4 release](https://github.com/groue/GRDB.swift/tags) first, and fix any deprecation warning prior to the GRDB 5 upgrade.
GRDB 5 ships with fix-its that will suggest simple syntactic changes, and won't require you to think much.
Your attention will be needed, though, in the area of database observation.
## New requirements
GRDB requirements have been bumped:
- **Swift 5.3+** (was Swift 4.2+)
- **Xcode 12.0+** (was Xcode 10.0+)
- **iOS 11.0+** (was iOS 9.0+)
- **macOS 10.10+** (was macOS 10.9+)
- tvOS 9.0+ (unchanged)
- watchOS 2.0+ (unchanged)
## Database Configuration
The way to configure a database relies much more on the `Configuration.prepareDatabase(_:)` method:
```swift
// BEFORE: GRDB 4
var config = Configuration()
config.trace = { ... } // Tracing SQL statements
config.prepareDatabase = { db in // prepareDatabase was a property
... // Custom setup
}
let dbQueue = try DatabaseQueue(path: dbPath, configuration: config)
dbQueue.add(function: ...) // Custom SQL function
dbQueue.add(collation: ...) // Custom collation
dbQueue.add(tokenizer: ...) // Custom FTS5 tokenizer
// NEW: GRDB 5
var config = Configuration()
config.prepareDatabase { db in // prepareDatabase is now a method
db.trace { ... }
db.add(function: ...)
db.add(collation: ...)
db.add(tokenizer: ...)
...
}
let dbQueue = try DatabaseQueue(dbPath, configuration: config)
```
## ValueObservation
[ValueObservation] is the database observation tool that tracks changes in database values. It has quite changed in GRDB 5.
Those changes have the vanilla GRDB, its [Combine publishers], and [RxGRDB] offer a common API, and a common behavior. This greatly helps choosing or switching your preferred database observation technique. In previous versions of GRDB, the three companion libraries used to have subtle differences that were just opportunities for bugs.
In the end, this migration step might require some work. But it's for the benefit of all!
- [Creating ValueObservation](#creating-valueobservation)
- [Starting ValueObservation](#starting-valueobservation)
- [Runtime Behavior of ValueObservation](#runtime-behavior-of-valueobservation)
- [Removed ValueObservation Methods](#removed-valueobservation-methods)
### Creating ValueObservation
In GRDB 5, you *always* create a ValueObservation by providing a function that fetches the observed value:
```swift
// GRDB 5
let observation = ValueObservation.tracking { db in
/* fetch and return the observed value */
}
// For example, an observation of [Player], which tracks all players:
let observation = ValueObservation.tracking { db in
try Player.fetchAll(db)
}
// The same observation, using shorthand notation:
let observation = ValueObservation.tracking(Player.fetchAll)
```
Several methods that build observations were removed:
```swift
// BEFORE: GRDB 4
let observation = request.observationForCount()
let observation = request.observationForFirst()
let observation = request.observationForAll()
let observation = ValueObservation.tracking(value: someFetchFunction)
let observation = ValueObservation.tracking(..., fetch: { db in ... })
// NEW: GRDB 5
let observation = ValueObservation.tracking(request.fetchCount)
let observation = ValueObservation.tracking(request.fetchOne)
let observation = ValueObservation.tracking(request.fetchAll)
let observation = ValueObservation.tracking(someFetchFunction)
let observation = ValueObservation.tracking { db in ... }
```
Finally, ValueObservation used to let application define custom "reducers" based on a protocol name ValueReducer, which was removed in GRDB 5. See the [#731](https://github.com/groue/GRDB.swift/pull/731) conversation for a solution towards a replacement.
RxGRDB impact
```swift
// BEFORE: GRDB 4
request.rx.observeCount(in: dbQueue)
request.rx.observeFirst(in: dbQueue)
request.rx.observeAll(in: dbQueue)
// NEW: GRDB 5
ValueObservation.tracking(request.fetchCount).rx.observe(in: dbQueue)
ValueObservation.tracking(request.fetchOne).rx.observe(in: dbQueue)
ValueObservation.tracking(request.fetchAll).rx.observe(in: dbQueue)
```
### Starting ValueObservation
The `start` method which starts observing the database has changed as well.
```swift
// Start observing the database
let cancellable = observation.start(
in: dbQueue,
onError: { error in ... },
onChange: { value in print("fresh value: \(value)") })
```
1. The result of the `start` method is now a DatabaseCancellable which allows you to explicitly stop an observation:
```swift
// BEFORE: GRDB 4
let observer: TransactionObserver?
observer = observation.start(...)
observer = nil // Stop the observation
// NEW: GRDB 5
let cancellable: DatabaseCancellable
cancellable = observation.start(...)
cancellable.cancel() // Stop the observation
```
The returned DatabaseCancellable cancels itself when it gets deinitialized.
2. The `onError` handler of the `start` method is now mandatory:
```swift
// BEFORE: GRDB 4
do {
try observation.start(in: dbQueue) { value in
print("fresh value: \(value)")
}
} catch { ... }
// NEW: GRDB 5
observation.start(
in: dbQueue,
onError: { error in ... },
onChange: { value in print("fresh value: \(value)") })
```
### Runtime Behavior of ValueObservation
**The behavior of ValueObservation has changed**.
The changes can quite impact your application. We'll describe them below, as well as the strategies to restore the previous behavior when needed.
1. ValueObservation used to notify its initial value *immediately* when the observation starts. Now, it notifies fresh values on the main thread, *asynchronously*, by default.
This means that the parts of your application that rely on this immediate value to, say, set up their user interface, have to be modified. Otherwise, they may suffer from a brief flash of missing data, during the short amount of time between the beginning of the observation, and the asynchronous delivery of the initial value.
To be granted with an immediate, synchronous, delivery of the initial value, insert a `scheduling: .immediate` argument in the `start` method:
```swift
let observation = ValueObservation.tracking(Player.fetchAll)
let cancellable = observation.start(
in: dbQueue,
// Opt in for immediate notification of the initial value
scheduling: .immediate,
onError: { error in ... },
onChange: { [weak self] (players: [Player]) in
guard let self else { return }
self.updateView(players)
})
// <- Here the view has already been updated.
```
Note that the `.immediate` scheduling requires that the observation starts from the main thread. A fatal error is raised otherwise.
Combine impact
```swift
let observation = ValueObservation.tracking(Player.fetchAll)
let cancellable = observation
.publisher(
in: dbQueue,
// Opt in for immediate notification of the initial value
scheduling: .immediate)
.sink(...)
```
RxGRDB impact
```swift
let observation = ValueObservation.tracking(Player.fetchAll)
let disposable = observation
.rx.observe(
in: dbQueue,
// Opt in for immediate notification of the initial value
scheduling: .immediate)
.subscribe(...)
```
2. ValueObservation used to notify one fresh value for each and every database transaction that had an impact on the tracked value. Now, it may coalesce notifications. If your application relies on exactly one notification per transaction, use [DatabaseRegionObservation] instead.
3. Some value observations used to automatically remove duplicate values. This is no longer automatic. If your application relies on distinct consecutive values, use the [removeDuplicates] operator.
4. ValueObservation used to prevent a database connection (DatabaseQueue or DatabasePool) from closing. Now an observation just stops emitting any fresh value when the database connection closes.
5. ValueObservation used to be able to restart notifying fresh values after it has notified an error. Now an error marks the end of the observation.
6. ValueObservation used to have a `scheduling` property, which has been removed.
You can remove the explicit request to dispatch fresh values asynchronously on the main dispatch queue, because it is now the default behavior:
```swift
// BEFORE: GRDB 4
var observation = ValueObservation.tracking(...)
observation.scheduling = .async(onQueue: .main, startImmediately: true)
observation.start(in: dbQueue, onError: ..., onChange: ...)
// NEW: GRDB 5
let observation = ValueObservation.tracking(...)
observation.start(in: dbQueue, onError: ..., onChange: ...)
```
For other dispatch queues, use the `scheduling` parameter of the `start` method:
```swift
let queue: DispatchQueue = ...
// BEFORE: GRDB 4
var observation = ValueObservation.tracking(...)
observation.scheduling = .async(onQueue: queue, startImmediately: true)
observation.start(in: dbQueue, onError: ..., onChange: ...)
// NEW: GRDB 5
let observation = ValueObservation.tracking(...)
observation.start(in: dbQueue, scheduling: .async(onQueue: queue), onError: ..., onChange: ...)
```
The GRDB 4 `startImmediately` parameter is no longer supported: ValueObservation now always emits an initial value, without waiting for eventual changes. It is up to your application to ignore this initial value if it wants to.
### Removed ValueObservation Methods
1. ValueObservation used to have a `compactMap` method. This method has been removed without any replacement.
If your application uses Combine publishers or RxGRDB, then use the `compactMap` method from Combine or RxSwift instead.
2. ValueObservation used to have a `combine` method. This method has been removed without any replacement.
In your application, replace combined observations with a single observation:
```swift
struct HallOfFame {
var totalPlayerCount: Int
var bestPlayers: [Player]
}
// BEFORE: GRDB 4
let totalPlayerCountObservation = ValueObservation.tracking(Player.fetchCount)
let bestPlayersObservation = ValueObservation.tracking(Player
.limit(10)
.order(Column("score").desc)
.fetchAll)
let observation = ValueObservation
.combine(totalPlayerCountObservation, bestPlayersObservation)
.map(HallOfFame.init)
// NEW: GRDB 5
let observation = ValueObservation.tracking { db -> HallOfFame in
let totalPlayerCount = try Player.fetchCount(db)
let bestPlayers = try Player
.order(Column("score").desc)
.limit(10)
.fetchAll(db)
return HallOfFame(
totalPlayerCount: totalPlayerCount,
bestPlayers: bestPlayers)
}
```
As is previous versions of GRDB, do not use the `combineLatest` operators of Combine or RxSwift in order to combine several ValueObservation. You would lose all guarantees of [data consistency](https://en.wikipedia.org/wiki/Consistency_(database_systems)).
## Combine Integration
GRDB 4 had a companion library named GRDBCombine. Combine support is now embedded right into GRDB 5, and you have to remove any dependency on GRDBCombine.
GRDBCombine used to define a `fetchOnSubscription()` method of the ValueObservation subscriber. It has been removed. Replace it with `scheduling: .immediate` for the same effect (an initial value is notified immediately, synchronously, when the publisher is subscribed):
```swift
// BEFORE: GRDB 4 + GRDBCombine
let observation = ValueObservation.tracking { db in ... }
let publisher = observation
.publisher(in: dbQueue)
.fetchOnSubscription()
// NEW: GRDB 5
let observation = ValueObservation.tracking { db in ... }
let publisher = observation
.publisher(in: dbQueue, scheduling: .immediate)
```
## Other Changes
1. The `Configuration.trace` property has been removed. You know use the `Database.trace(options:_:)` method instead:
```swift
// BEFORE: GRDB 4
var config = Configuration()
config.trace = { print($0) }
let dbQueue = try DatabaseQueue(path: dbPath, configuration: config)
// NEW: GRDB 5
var config = Configuration()
config.prepareDatabase { db in
db.trace { print($0) }
}
let dbQueue = try DatabaseQueue(path: dbPath, configuration: config)
```
2. [Batch updates] used to rely of the `<-` operator. This operator has been removed. Use the `set(to:)` method instead:
```swift
// BEFORE: GRDB 4
try Player.updateAll(db, Column("score") <- 0)
// NEW: GRDB 5
try Player.updateAll(db, Column("score").set(to: 0))
```
> :question: This change avoids conflicts with other libraries that define the same operator.
3. [SQL Interpolation] does no longer wrap subqueries in parenthesis:
```swift
// BEFORE: GRDB 4
let maximumScore: SQLRequest = "SELECT MAX(score) FROM player"
let bestPlayers: SQLRequest = "SELECT * FROM player WHERE score = \(maximumScore)"
// NEW: GRDB 5
let maximumScore: SQLRequest = "SELECT MAX(score) FROM player"
let bestPlayers: SQLRequest = "SELECT * FROM player WHERE score = (\(maximumScore))"
// extra parenthesis required: ^ ^
```
> :question: This change makes it possible to concatenate subqueries with the UNION operator.
4. In order to extract raw SQL string from an [SQL literal], you now need a database connection:
```swift
// BEFORE: GRDB 4
let query: SQLLiteral = "UPDATE player SET name = \(name) WHERE id = \(id)"
print(query.sql) // prints "UPDATE player SET name = ? WHERE id = ?"
print(query.arguments) // prints ["O'Brien", 42]
// NEW: GRDB 5
let query: SQL = "UPDATE player SET name = \(name) WHERE id = \(id)"
let (sql, arguments) = try dbQueue.read { db in
try query.build(db)
}
print(sql) // prints "UPDATE player SET name = ? WHERE id = ?"
print(arguments) // prints ["O'Brien", 42]
```
5. In order to extract raw SQL string from a request ([SQLRequest] or [QueryInterfaceRequest]), you now need to call the `makePreparedRequest()` method:
```swift
// BEFORE: GRDB 4
try dbQueue.read { db in
let request = Player.filter(Column("name") == "O'Brien")
let sqlRequest = try SQLRequest(db, request: request)
print(sqlRequest.sql) // "SELECT * FROM player WHERE name = ?"
print(sqlRequest.arguments) // ["O'Brien"]
}
// NEW: GRDB 5
try dbQueue.read { db in
let request = Player.filter(Column("name") == "O'Brien")
let statement = try request.makePreparedRequest(db, forSingleResult: false).statement
print(statement.sql) // "SELECT * FROM player WHERE name = ?"
print(statement.arguments) // ["O'Brien"]
}
```
6. The `TableRecord.selectionSQL()` method is no longer available. When you need to embed the columns selected by a record type in an SQL request, you now have to use [SQL Interpolation]:
```swift
// BEFORE: GRDB 4
let sql = "SELECT \(Player.selectionSQL()) FROM player"
let players = try Player.fetchAll(db, sql: sql)
// NEW: GRDB 5
let request: SQLRequest = "SELECT \(columnsOf: Player.self) FROM player"
let players = try request.fetchAll(db)
```
7. [Custom SQL functions] are now [callable values](https://github.com/apple/swift-evolution/blob/master/proposals/0253-callable.md):
```swift
// BEFORE: GRDB 4
Player.select(myFunction.call(Column("name")))
// NEW: GRDB 5
Player.select(myFunction(Column("name")))
```
8. Defining custom `FetchRequest` types is no longer supported.
Refactor your app around [SQLRequest] and [QueryInterfaceRequest], which are supposed to fully address your needs.
9. The module name for [custom SQLite builds](CustomSQLiteBuilds.md) is now the plain `GRDB`:
```swift
// BEFORE: GRDB 4
import GRDBCustomSQLite
// NEW: GRDB 5
import GRDB
```
10. Importing the `GRDB` module grants access to the [SQLite C interface](https://www.sqlite.org/c3ref/intro.html). You don't need any longer to import the underlying SQLite library:
```swift
// BEFORE: GRDB 4
import CSQLite // When GRDB is included with the Swift Package Manager
import SQLCipher // When GRDB is linked to SQLCipher
import SQLite3 // When GRDB is linked to System SQLite
let sqliteVersion = String(cString: sqlite3_libversion())
// NEW: GRDB 5
import GRDB
let sqliteVersion = String(cString: sqlite3_libversion())
```
11. `FetchedRecordsController` was removed from GRDB 5. The [Database Observation] chapter describes the other ways to observe the database.
12. Defining custom `RowAdapter` types is no longer supported. A new [RenameColumnAdapter](../README.md#renamecolumnadapter) adapter makes it possible to process column names.
13. Many types and methods that support the query builder used to be publicly exposed and flagged as experimental. They are now private, or renamed with an underscore prefix, which means they are not for public use.
14. Explicit boolean tests `expression == true` and `expression == false` generate different SQL:
```swift
// GRDB 4: SELECT * FROM player WHERE isActive
// GRDB 5: SELECT * FROM player WHERE isActive = 1
Player.filter(Column("isActive") == true)
// GRDB 4: SELECT * FROM player WHERE NOT isActive
// GRDB 5: SELECT * FROM player WHERE isActive = 0
Player.filter(Column("isActive") == false)
// GRDB 4 & 5: SELECT * FROM player WHERE isActive
Player.filter(Column("isActive"))
// GRDB 4 & 5: SELECT * FROM player WHERE NOT isActive
Player.filter(!Column("isActive"))
```
This change is innocuous for database boolean values that are `0`, `1`, or `NULL`. However, it is a breaking change for all other database values.
[ValueObservation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservation
[DatabaseRegionObservation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseregionobservation
[RxGRDB]: https://github.com/RxSwiftCommunity/RxGRDB
[removeDuplicates]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/valueobservation/removeduplicates()
[Custom SQL functions]: ../README.md#custom-sql-functions
[Batch updates]: ../README.md#update-requests
[SQL Interpolation]: SQLInterpolation.md
[SQL literal]: SQLInterpolation.md#sql-literal
[SQLRequest]: ../README.md#custom-requests
[QueryInterfaceRequest]: ../README.md#requests
[Combine publishers]: Combine.md
[Database Observation]: https://swiftpackageindex.com/groue/GRDB.swift/documentation/grdb/databaseobservation
---