SQL, Prepared Statements, Rows, and Values
SQL is the fundamental language for accessing SQLite databases.
Overview
This section of the documentation focuses on low-level SQLite concepts: the SQL language, prepared statements, database rows and values.
If SQL is not your cup of tea, jump to Records and the Query Interface 🙂
SQL Support
GRDB has a wide support for SQL.
Once connected with one of the Database Connections, you can execute raw SQL statements:
try dbQueue.write { db in
try db.execute(sql: """
INSERT INTO player (name, score) VALUES (?, ?);
INSERT INTO player (name, score) VALUES (?, ?);
""", arguments: ["Arthur", 500, "Barbara", 1000])
}
Build a prepared Statement
and lazily iterate a DatabaseCursor
of Row
:
try dbQueue.read { db in
let sql = "SELECT id, score FROM player WHERE name = ?"
let statement = try db.makeStatement(sql: sql)
let rows = try Row.fetchCursor(statement, arguments: ["O'Brien"])
while let row = try rows.next() {
let id: Int64 = row[0]
let score: Int = row[1]
}
}
Leverage SQLRequest
and FetchableRecord
for defining streamlined apis with powerful SQL interpolation features:
struct Player: Decodable {
var id: Int64
var name: String
var score: Int
}
extension Player: FetchableRecord {
static func filter(name: String) -> SQLRequest<Player> {
"SELECT * FROM player WHERE name = \(name)"
}
static func maximumScore() -> SQLRequest<Int> {
"SELECT MAX(score) FROM player"
}
}
try dbQueue.read { db in
let players = try Player.filter(name: "O'Reilly").fetchAll(db) // [Player]
let maxScore = try Player.maximumScore().fetchOne(db) // Int?
}
For a more detailed overview, see SQLite API.
Fundamental Database Types
class Statement
A prepared statement.
class Row
A database row.
struct DatabaseValue
A value stored in a database table.
protocol DatabaseCursor<Element>
A cursor that lazily iterates the results of a prepared
Statement
.
SQL Literals and Requests
struct SQL
An SQL literal.
struct SQLRequest<RowDecoder>
An SQL request that can decode database rows.
func databaseQuestionMarks(count: Int
) -> String Return as many question marks separated with commas as the count argument.
Database Values
struct DatabaseDateComponents
A database value that holds date components.
protocol DatabaseValueConvertible
A type that can convert itself into and out of a database value.
protocol StatementColumnConvertible
A type that can decode itself from the low-level C interface to SQLite results.
Supporting Types
protocol Cursor<Element>
A type that supplies the values of some external resource, one at a time.
protocol FetchRequest<RowDecoder>
A type that fetches and decodes database rows.