Structures
The following structures are available globally.
-
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 association this way:
struct Author: TableRecord { ... } struct Book: TableRecord { static let author = belongsTo(Author.self) ... }
A BelongsTo associations should be supported by an SQLite foreign key.
Foreign keys are the recommended way to declare relationships between database tables because not only will SQLite guarantee the integrity of your data, but GRDB will be able to use those foreign keys to automatically configure your association.
You define the foreign key when you create database tables. For example:
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.column("authorId", .integer) // (2) .notNull() // (3) .indexed() // (4) .references("author", onDelete: .cascade) // (5) t.column("title", .text) }
- The author table has a primary key.
- The book.authorId column is used to link a book to the author it belongs to.
- Make the book.authorId column not null if you want SQLite to guarantee that all books have an author.
- Create an index on the book.authorId column in order to ease the selection of an author’s books.
- Create a foreign key 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 https://sqlite.org/foreignkeys.html#fk_actions for more information.
The example above uses auto-incremented primary keys. But generally speaking, all primary keys are supported.
If the database schema does not define foreign keys between tables, you can still use BelongsTo associations. But your help is needed to define the missing foreign key:
struct Book: FetchableRecord, TableRecord { static let author = belongsTo(Author.self, using: ForeignKey(...)) }
See ForeignKey for more information.
See moreDeclaration
Swift
public struct BelongsToAssociation<Origin, Destination>: Association
-
A column in a database table.
When you need to introduce your own column type, don’t wrap a Column. Instead, adopt the ColumnExpression protocol.
See https://github.com/groue/GRDB.swift#the-query-interface
See moreDeclaration
Swift
public struct Column: ColumnExpression
-
Configuration for a DatabaseQueue or DatabasePool.
See moreDeclaration
Swift
public struct Configuration
-
A column of a database table.
This type closely matches the information returned by the
table_info
pragma.> CREATE TABLE player ( id INTEGER PRIMARY KEY, firstName TEXT, lastName TEXT) > PRAGMA table_info("player") cid name type notnull dflt_value pk ---- ----- ------- -------- ---------- --- 0 id INTEGER 0 NULL 1 1 name TEXT 0 NULL 0 2 score INTEGER 0 NULL 0
See
See moreDatabase.columns(in:)
and https://www.sqlite.org/pragma.html#pragma_table_infoDeclaration
Swift
public struct ColumnInfo : FetchableRecord
-
Declaration
Swift
public struct IndexInfo
-
Primary keys are returned from the Database.primaryKey(_:) method.
When the table’s primary key is the rowid:
// CREATE TABLE item (name TEXT) let pk = try db.primaryKey("item") pk.columns // ["rowid"] pk.rowIDColumn // nil pk.isRowID // true // CREATE TABLE citizen ( // id INTEGER PRIMARY KEY, // name TEXT // ) let pk = try db.primaryKey("citizen")! pk.columns // ["id"] pk.rowIDColumn // "id" pk.isRowID // true
When the table’s primary key is not the rowid:
See more// CREATE TABLE country ( // isoCode TEXT NOT NULL PRIMARY KEY // name TEXT // ) let pk = db.primaryKey("country")! pk.columns // ["isoCode"] pk.rowIDColumn // nil pk.isRowID // false // CREATE TABLE citizenship ( // citizenID INTEGER NOT NULL REFERENCES citizen(id) // countryIsoCode TEXT NOT NULL REFERENCES country(isoCode) // PRIMARY KEY (citizenID, countryIsoCode) // ) let pk = db.primaryKey("citizenship")! pk.columns // ["citizenID", "countryIsoCode"] pk.rowIDColumn // nil pk.isRowID // false
Declaration
Swift
public struct PrimaryKeyInfo
-
You get foreign keys from table names, with the
See moreforeignKeys(on:)
method.Declaration
Swift
public struct ForeignKeyInfo
-
DatabaseDateComponents reads and stores DateComponents in the database.
See moreDeclaration
Swift
public struct DatabaseDateComponents : DatabaseValueConvertible, StatementColumnConvertible
-
A DatabaseMigrator registers and applies database migrations.
Migrations are named blocks of SQL statements that are guaranteed to be applied in order, once and only once.
When a user upgrades your application, only non-applied migration are run.
Usage:
See morevar migrator = DatabaseMigrator() // 1st migration migrator.registerMigration("createLibrary") { db in try db.create(table: "author") { t in t.autoIncrementedPrimaryKey("id") t.column("creationDate", .datetime) t.column("name", .text).notNull() } try db.create(table: "book") { t in t.autoIncrementedPrimaryKey("id") t.column("authorId", .integer) .notNull() .references("author", onDelete: .cascade) t.column("title", .text).notNull() } } // 2nd migration migrator.registerMigration("AddBirthYearToAuthors") { db in try db.alter(table: "author") { t t.add(column: "birthYear", .integer) } } // Migrations for future versions will be inserted here: // // // 3rd migration // migrator.registerMigration("...") { db in // ... // } try migrator.migrate(dbQueue)
Declaration
Swift
public struct DatabaseMigrator
-
DatabaseRegion defines a region in the database. DatabaseRegion is dedicated to help transaction observers recognize impactful database changes in their
observes(eventsOfKind:)
anddatabaseDidChange(with:)
methods.A database region is the union of any number of
table regions
, which can cover a full table, or the combination of columns and rows (identified by their rowids):|Table1 | |Table2 | |Table3 | |Table4 | |Table5 | |-------| |-------| |-------| |-------| |-------| |x|x|x|x| |x| | | | |x|x|x|x| |x|x| |x| | | | | | |x|x|x|x| |x| | | | | | | | | | | | | | | |x| | | |x|x|x|x| |x| | | | | | | | | |x|x| |x| | | | | | |x|x|x|x| |x| | | | | | | | | | | | | | | | | | |
You don’t create a database region directly. Instead, you use one of those methods:
SelectStatement.databaseRegion
:let statement = db.makeSelectStatement(
SELECT name, score FROM player
) print(statement.databaseRegion) // printsplayer(name,score)
FetchRequest.databaseRegion(_:)
let request = Player.filter(key: 1) try print(request.databaseRegion(db)) // prints
player(*)[1]
Database regions returned by requests can be more precise than regions returned by select statements. Especially, regions returned by statements don’t know about rowids:
See more// A plain statement let statement = db.makeSelectStatement("SELECT * FROM player WHERE id = 1") statement.databaseRegion // "player(*)" // A query interface request that executes the same statement: let request = Player.filter(key: 1) try request.databaseRegion(db) // "player(*)[1]"
Declaration
Swift
public struct DatabaseRegion: CustomStringConvertible, Equatable
-
DatabaseValue is the intermediate type between SQLite and your values.
See https://www.sqlite.org/datatype3.html
See moreDeclaration
Swift
public struct DatabaseValue: Hashable, CustomStringConvertible, DatabaseValueConvertible, SQLExpression
-
A section given by a FetchedRecordsController.
See moreDeclaration
Swift
public struct FetchedRecordsSectionInfo<Record: FetchableRecord>
-
An adapted request.
See moreDeclaration
Swift
public struct AdaptedFetchRequest<Base: FetchRequest> : FetchRequest
-
A type-erased FetchRequest.
An AnyFetchRequest forwards its operations to an underlying request, hiding its specifics.
See moreDeclaration
Swift
public struct AnyFetchRequest<T> : FetchRequest
-
A FetchRequest built from raw SQL.
See moreDeclaration
Swift
public struct SQLRequest<T> : FetchRequest
-
A ForeignKey helps building associations when GRDB can’t infer a foreign key from the database schema.
Sometimes the database schema does not define any foreign key between two tables. And sometimes, there are several foreign keys from a table to another:
| Table book | | Table person | | ------------ | | ------------ | | id | +-->• id | | authorId •---+ | name | | translatorId •---+ | title |
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
, orCould not infer foreign key from book to person
.Your help is needed. You have to instruct GRDB which foreign key to use:
struct Book: TableRecord { // Define foreign keys static let authorForeignKey = ForeignKey(["authorId"])) static let translatorForeignKey = ForeignKey(["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:
struct Person: TableRecord { static let writtenBooks = hasMany(Book.self, using: Book.authorForeignKey) static let translatedBooks = hasMany(Book.self, using: Book.translatorForeignKey) }
Foreign keys can also be defined from query interface columns:
struct Book: TableRecord { enum Columns: String, ColumnExpression { case id, title, authorId, translatorId } static let authorForeignKey = ForeignKey([Columns.authorId])) static let translatorForeignKey = ForeignKey([Columns.translatorId])) }
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:
See morestruct Book: TableRecord { static let authorForeignKey = ForeignKey(["authorId"], to: ["id"])) static let author = belongsTo(Person.self, using: authorForeignKey) }
Declaration
Swift
public struct ForeignKey
-
A QueryInterfaceRequest describes an SQL query.
See https://github.com/groue/GRDB.swift#the-query-interface
See moreDeclaration
Swift
public struct QueryInterfaceRequest<T>
-
FTS3 lets you define
fts3
virtual tables.
See more// CREATE VIRTUAL TABLE document USING fts3(content) try db.create(virtualTable: "document", using: FTS3()) { t in t.column("content") }
Declaration
Swift
public struct FTS3 : VirtualTableModule
-
A full text pattern that can query FTS3 and FTS4 virtual tables.
See moreDeclaration
Swift
public struct FTS3Pattern
-
An FTS3 tokenizer, suitable for FTS3 and FTS4 table definitions:
db.create(virtualTable: "book", using: FTS4()) { t in t.tokenizer = .simple // FTS3TokenizerDescriptor }
See https://www.sqlite.org/fts3.html#tokenizer
See moreDeclaration
Swift
public struct FTS3TokenizerDescriptor
-
FTS4 lets you define
fts4
virtual tables.// CREATE VIRTUAL TABLE document USING fts4(content) try db.create(virtualTable: "document", using: FTS4()) { t in t.column("content") }
See https://www.sqlite.org/fts3.html
See moreDeclaration
Swift
public struct FTS4 : VirtualTableModule
-
FTS5 lets you define
fts5
virtual tables.// CREATE VIRTUAL TABLE document USING fts5(content) try db.create(virtualTable: "document", using: FTS5()) { t in t.column("content") }
See https://www.sqlite.org/fts5.html
See more
-
A full text pattern that can query FTS5 virtual tables.
See more
-
The reason why FTS5 is requesting tokenization.
See https://www.sqlite.org/fts5.html#custom_tokenizers
See more
-
An FTS5 tokenizer, suitable for FTS5 table definitions:
db.create(virtualTable: "book", using: FTS5()) { t in t.tokenizer = .unicode61() // FTS5TokenizerDescriptor }
See https://www.sqlite.org/fts5.html#tokenizers
See more
-
Flags that tell SQLite how to register a token.
See https://www.sqlite.org/fts5.html#custom_tokenizers
See more
-
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.For example, if your application includes authors and books, and each author is assigned zero or more books, you’d declare the association this way:
struct Book: TableRecord { ... } struct Author: TableRecord { static let books = hasMany(Book.self) ... }
HasMany associations should be supported by an SQLite foreign key.
Foreign keys are the recommended way to declare relationships between database tables because not only will SQLite guarantee the integrity of your data, but GRDB will be able to use those foreign keys to automatically configure your association.
You define the foreign key when you create database tables. For example:
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.column("authorId", .integer) // (2) .notNull() // (3) .indexed() // (4) .references("author", onDelete: .cascade) // (5) t.column("title", .text) }
- The author table has a primary key.
- The book.authorId column is used to link a book to the author it belongs to.
- Make the book.authorId column not null if you want SQLite to guarantee that all books have an author.
- Create an index on the book.authorId column in order to ease the selection of an author’s books.
- Create a foreign key 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 https://sqlite.org/foreignkeys.html#fk_actions for more information.
The example above uses auto-incremented primary keys. But generally speaking, all primary keys are supported.
If the database schema does not define foreign keys between tables, you can still use HasMany associations. But your help is needed to define the missing foreign key:
struct Author: TableRecord { static let books = hasMany(Book.self, using: ForeignKey(...)) }
See ForeignKey for more information.
See moreDeclaration
Swift
public struct HasManyAssociation<Origin, Destination>: Association
-
The HasOne association indicates a one-to-one connection between two record types, such as each instance of the declaring record
has one
instances of the other record.For example, if your application has one database table for countries, and another for their demographic profiles, you’d declare the association this way:
struct Demographics: TableRecord { ... } struct Country: TableRecord { static let demographics = hasOne(Demographics.self) ... }
HasOne associations should be supported by an SQLite foreign key.
Foreign keys are the recommended way to declare relationships between database tables because not only will SQLite guarantee the integrity of your data, but GRDB will be able to use those foreign keys to automatically configure your association.
You define the foreign key when you create database tables. For example:
try db.create(table: "country") { t in t.column("code", .text).primaryKey() // (1) t.column("name", .text) } try db.create(table: "demographics") { t in t.autoIncrementedPrimaryKey("id") t.column("countryCode", .text) // (2) .notNull() // (3) .unique() // (4) .references("country", onDelete: .cascade) // (5) t.column("population", .integer) t.column("density", .double) }
- The country table has a primary key.
- The demographics.countryCode column is used to link a demographic profile to the country it belongs to.
- Make the demographics.countryCode column not null if you want SQLite to guarantee that all profiles are linked to a country.
- Create a unique index on the demographics.countryCode column in order to guarantee the unicity of any country’s profile.
- Create a foreign key 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 https://sqlite.org/foreignkeys.html#fk_actions for more information.
The example above uses a string primary for the country table. But generally speaking, all primary keys are supported.
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:
struct Country: FetchableRecord, TableRecord { static let demographics = hasOne(Demographics.self, using: ForeignKey(...)) }
See ForeignKey for more information.
See moreDeclaration
Swift
public struct HasOneAssociation<Origin, Destination> : Association
-
Use persistence containers in the
encode(to:)
method of your encodable records:
See morestruct Player : MutablePersistableRecord { var id: Int64? var name: String? func encode(to container: inout PersistenceContainer) { container["id"] = id container["name"] = name } }
Declaration
Swift
public struct PersistenceContainer
-
The MutablePersistableRecord protocol uses this type in order to handle SQLite conflicts when records are inserted or updated.
See
MutablePersistableRecord.persistenceConflictPolicy
.See https://www.sqlite.org/lang_conflict.html
See moreDeclaration
Swift
public struct PersistenceConflictPolicy
-
Indexes to (ColumnName, DatabaseValue) pairs in a database row.
Declaration
Swift
public struct RowIndex : Comparable, Strideable
-
EmptyRowAdapter is a row adapter that hides all columns.
See moreDeclaration
Swift
public struct EmptyRowAdapter: RowAdapter
-
ColumnMapping is a row adapter that maps column names.
See morelet adapter = ColumnMapping(["foo": "bar"]) let sql = "SELECT 'foo' AS foo, 'bar' AS bar, 'baz' AS baz" // [foo:"bar"] try Row.fetchOne(db, sql, adapter: adapter)
Declaration
Swift
public struct ColumnMapping : RowAdapter
-
SuffixRowAdapter is a row adapter that hides the first columns in a row.
See morelet adapter = SuffixRowAdapter(fromIndex: 2) let sql = "SELECT 1 AS foo, 2 AS bar, 3 AS baz" // [baz:3] try Row.fetchOne(db, sql, adapter: adapter)
Declaration
Swift
public struct SuffixRowAdapter : RowAdapter
-
RangeRowAdapter is a row adapter that only exposes a range of columns.
See morelet adapter = RangeRowAdapter(1..<3) let sql = "SELECT 1 AS foo, 2 AS bar, 3 AS baz, 4 as qux" // [bar:2 baz:3] try Row.fetchOne(db, sql, adapter: adapter)
Declaration
Swift
public struct RangeRowAdapter : RowAdapter
-
ScopeAdapter is a row adapter that lets you define scopes on rows.
See more// Two adapters let fooAdapter = ColumnMapping(["value": "foo"]) let barAdapter = ColumnMapping(["value": "bar"]) // Define scopes let adapter = ScopeAdapter([ "foo": fooAdapter, "bar": barAdapter]) // Fetch let sql = "SELECT 'foo' AS foo, 'bar' AS bar" let row = try Row.fetchOne(db, sql, adapter: adapter)! // Scoped rows: if let fooRow = row.scopes["foo"] { fooRow["value"] // "foo" } if let barRow = row.scopes["bar"] { barRow["value"] // "bar" }
Declaration
Swift
public struct ScopeAdapter : RowAdapter
-
SQLCollatedExpression taints an expression so that every derived expression is eventually evaluated using an SQLite collation.
You create one by calling the SQLSpecificExpressible.collating() method.
See morelet email: SQLCollatedExpression = Column("email").collating(.nocase) // SELECT * FROM player WHERE email = 'arthur@example.com' COLLATE NOCASE Player.filter(email == "arthur@example.com")
Declaration
Swift
public struct SQLCollatedExpression
-
SQLExpressionLiteral is an expression built from a raw SQL snippet.
SQLExpressionLiteral("1 + 2")
The SQL literal may contain
?
and colon-prefixed tokens:
See moreSQLExpressionLiteral("? + ?", arguments: [1, 2]) SQLExpressionLiteral(":one + :two", arguments: ["one": 1, "two": 2])
Declaration
Swift
public struct SQLExpressionLiteral : SQLExpression
-
Declaration
Swift
public struct SQLFunctionName : Hashable
-
SQLGenerationContext is responsible for preventing SQL injection and disambiguating table names when GRDB generates SQL queries.
Declaration
Swift
public struct SQLGenerationContext
-
AllColumns is the
*
inSELECT *
.You use AllColumns in your custom implementation of TableRecord.databaseSelection.
For example:
See morestruct Player : TableRecord { static var databaseTableName = "player" static let databaseSelection: [SQLSelectable] = [AllColumns(), Column.rowID] } // SELECT *, rowid FROM player let request = Player.all()
Declaration
Swift
public struct AllColumns
-
StatementArguments provide values to argument placeholders in raw SQL queries.
Placeholders can take several forms (see https://www.sqlite.org/lang_expr.html#varparam for more information):
?NNN
(e.g.?2
): the NNN-th argument (starts at 1)?
: the N-th argument, where N is one greater than the largest argument number already assigned:AAAA
(e.g.:name
): named argument$AAAA
(e.g.$name
): named argument
Positional Arguments
To fill question marks placeholders, feed StatementArguments with an array:
db.execute( "INSERT ... (?, ?)", arguments: StatementArguments(["Arthur", 41])) // Array literals are automatically converted: db.execute( "INSERT ... (?, ?)", arguments: ["Arthur", 41])
Named Arguments
To fill named arguments, feed StatementArguments with a dictionary:
db.execute( "INSERT ... (:name, :score)", arguments: StatementArguments(["name": "Arthur", "score": 41])) // Dictionary literals are automatically converted: db.execute( "INSERT ... (:name, :score)", arguments: ["name": "Arthur", "score": 41])
Concatenating Arguments
Several arguments can be concatenated and mixed with the
append(contentsOf:)
method and the+
,&+
,+=
operators:var arguments: StatementArguments = ["Arthur"] arguments += [41] db.execute("INSERT ... (?, ?)", arguments: arguments)
+
and+=
operators consider that overriding named arguments is a programmer error:var arguments: StatementArguments = ["name": "Arthur"] arguments += ["name": "Barbara"] // fatal error: already defined statement argument: name
&+
andappend(contentsOf:)
allow overriding named arguments:var arguments: StatementArguments = ["name": "Arthur"] arguments = arguments &+ ["name": "Barbara"] print(arguments) // Prints ["name": "Barbara"]
Mixed Arguments
When a statement consumes a mix of named and positional arguments, it prefers named arguments over positional ones. For example:
See morelet sql = "SELECT ?2 AS two, :foo AS foo, ?1 AS one, :foo AS foo2, :bar AS bar" let row = try Row.fetchOne(db, sql, arguments: [1, 2, "bar"] + ["foo": "foo"])! print(row) // Prints [two:2 foo:"foo" one:1 foo2:"foo" bar:"bar"]
Declaration
Swift
public struct StatementArguments: CustomStringConvertible, Equatable, ExpressibleByArrayLiteral, ExpressibleByDictionaryLiteral
-
A database event, notified to TransactionObserver.
See moreDeclaration
Swift
public struct DatabaseEvent