GRDB provides SQL interpolation that safely embeds Swift values in SQL queries with compile-time syntax validation.
Overview
SQL interpolation combines the readability of raw SQL with the safety of parameter binding:
let name = "Arthur"
let minScore = 1000
// Safe interpolation
let players = try Player.fetchAll(db, sql: """
SELECT * FROM player
WHERE name = \(name) AND score > \(minScore)
""")
SQL interpolation validates SQL syntax at compile time and prevents SQL injection.
Basic Usage
Interpolating Values
let name = "Arthur"
try db.execute(sql: """
INSERT INTO player (name) VALUES (\(name))
""")
INSERT INTO player (name) VALUES (?)
Fetching with Interpolation
let minScore = 1000
let players = try Player.fetchAll(db, sql: """
SELECT * FROM player WHERE score > \(minScore)
""")
SQL Literals
Use SQL to create reusable SQL fragments:
let name = "Arthur"
let condition = SQL("name = \(name)")
let players = try Player.fetchAll(db, sql: """
SELECT * FROM player WHERE \(literal: condition)
""")
Combining Literals
let name = "Arthur"
let minScore = 1000
let nameCondition = SQL("name = \(name)")
let scoreCondition = SQL("score > \(minScore)")
let combined = SQL("WHERE \(literal: nameCondition) AND \(literal: scoreCondition)")
let sql: SQL = """
SELECT * FROM player \(literal: combined)
"""
let players = try Player.fetchAll(db, sql)
Interpolation Options
Default: Value Binding
Values are bound as parameters (default behavior):
let value = 42
SQL("score = \(value)") // Generates: score = ?
literal: Raw SQL
Insert raw SQL without binding:
let column = "score"
SQL("SELECT \(literal: column) FROM player")
// Generates: SELECT score FROM player
Only use literal: with trusted, non-user input to prevent SQL injection.
Column Names
let column = Column("score")
SQL("SELECT \(column) FROM player")
// Generates: SELECT score FROM player
Table Names
let table = "player"
SQL("SELECT * FROM \(literal: table)")
// Generates: SELECT * FROM player
Array Interpolation
IN Clauses
let ids = [1, 2, 3]
let players = try Player.fetchAll(db, sql: """
SELECT * FROM player WHERE id IN \(ids)
""")
// Generates: SELECT * FROM player WHERE id IN (?, ?, ?)
Multiple Values
let names = ["Arthur", "Barbara", "Craig"]
try db.execute(sql: """
INSERT INTO player (name) VALUES \(names)
""")
// Generates: INSERT INTO player (name) VALUES (?), (?), (?)
Expressions
Arithmetic
let bonus = 100
SQL("score + \(bonus)")
// Generates: score + ?
Comparisons
let threshold = 1000
SQL("score > \(threshold)")
SQL("score >= \(threshold)")
SQL("score = \(threshold)")
SQL("score != \(threshold)")
Logic
let active = true
let verified = true
SQL("active = \(active) AND verified = \(verified)")
NULL Values
let optionalName: String? = nil
SQL("name = \(optionalName)")
// Generates: name = NULL (or name IS NULL)
let name: String? = "Arthur"
SQL("name = \(name)")
// Generates: name = ?
Date and Time
let date = Date()
try db.execute(sql: """
INSERT INTO event (createdAt) VALUES (\(date))
""")
databaseDateEncodingStrategy.
JSON
struct Metadata: Codable {
var tags: [String]
}
let metadata = Metadata(tags: ["swift", "database"])
try db.execute(sql: """
INSERT INTO item (metadata) VALUES (\(metadata))
""")
Custom Types
Any DatabaseValueConvertible type can be interpolated:
enum Status: String, DatabaseValueConvertible {
case active, inactive
}
let status = Status.active
SQL("status = \(status)")
// Generates: status = ?
Building Dynamic Queries
Conditional Clauses
func fetchPlayers(
name: String?,
minScore: Int?,
db: Database
) throws -> [Player] {
var conditions: [SQL] = []
if let name {
conditions.append(SQL("name = \(name)"))
}
if let minScore {
conditions.append(SQL("score > \(minScore)"))
}
let whereClause = conditions.isEmpty
? SQL("")
: SQL("WHERE \(literal: conditions.joined(separator: " AND "))")
return try Player.fetchAll(db, sql: """
SELECT * FROM player \(literal: whereClause)
""")
}
Dynamic Sorting
enum SortOrder {
case name, score
var sql: SQL {
switch self {
case .name: return "ORDER BY name"
case .score: return "ORDER BY score DESC"
}
}
}
func fetchPlayers(sortedBy order: SortOrder, db: Database) throws -> [Player] {
try Player.fetchAll(db, sql: """
SELECT * FROM player \(literal: order.sql)
""")
}
Compile-Time Validation
SQL syntax errors are caught at compile time:
// ❌ Compile error: Expected expression
SQL("SELECT FROM player")
// ❌ Compile error: Expected table name
SQL("INSERT INTO (name) VALUES (?)")
// ✅ Valid SQL
SQL("SELECT * FROM player WHERE score > ?")
Debugging
Print SQL for debugging:
let name = "Arthur"
let sql: SQL = "SELECT * FROM player WHERE name = \(name)"
print(sql.sql) // SELECT * FROM player WHERE name = ?
print(sql.arguments) // ["Arthur"]
Best Practices
Use interpolation for values, literal: only for identifiers:let name = "Arthur" // User input - use interpolation
let column = "name" // Developer-controlled - use literal:
SQL("SELECT * FROM player WHERE \(literal: column) = \(name)")
Build complex queries from SQL fragments:let baseQuery = SQL("SELECT * FROM player")
let filter = SQL("WHERE score > \(1000)")
let order = SQL("ORDER BY score DESC")
let fullQuery = SQL("""
\(literal: baseQuery)
\(literal: filter)
\(literal: order)
""")
Never interpolate user input with literal::// ❌ DANGEROUS - SQL injection risk!
let userInput = request.parameter("name")
SQL("SELECT * FROM player WHERE name = \(literal: userInput)")
// ✅ SAFE - value is bound as parameter
SQL("SELECT * FROM player WHERE name = \(userInput)")
- Compile-time validation means no runtime parsing
- Parameters are bound efficiently using SQLite’s prepared statement API
- Reusable
SQL values can be cached
Limitations
SQL interpolation validates syntax but not semantics. Column and table names are not verified against your schema.
Some complex SQL features may require raw SQL strings or the query interface instead.
See Also
