Executing Updates

The execute method executes SQL statements that do not return database rows, such as CREATE TABLE, INSERT, DELETE, ALTER, etc.

Basic Usage

try dbQueue.write { db in
    try db.execute(sql: """
        CREATE TABLE player (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            score INT)
        """)
}

The execute Method

The Database class provides two execute methods:

public func execute(sql: String, arguments: StatementArguments = []) throws
public func execute(literal: SQL) throws

INSERT Operations

With Arguments Array
With Arguments Dictionary
With SQL Interpolation

try dbQueue.write { db in
    try db.execute(
        sql: "INSERT INTO player (name, score) VALUES (?, ?)",
        arguments: ["Barbara", 1000])
}

try dbQueue.write { db in
    try db.execute(
        sql: "INSERT INTO player (name, score) VALUES (:name, :score)",
        arguments: ["name": "Barbara", "score": 1000])
}

try dbQueue.write { db in
    let name = "O'Brien"
    let score = 550
    try db.execute(literal: """
        INSERT INTO player (name, score) VALUES (\(name), \(score))
        """)
}

Getting the Inserted Row ID

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

try dbQueue.write { db in
    try db.execute(
        sql: "INSERT INTO player (name, score) VALUES (?, ?)",
        arguments: ["Arthur", 1000])
    
    let playerId = db.lastInsertedRowID
    print("Inserted player with id: \(playerId)")
}

UPDATE Operations

try dbQueue.write { db in
    try db.execute(
        sql: "UPDATE player SET score = ? WHERE id = ?",
        arguments: [1000, 1])
}

Checking Affected Rows

try dbQueue.write { db in
    try db.execute(
        sql: "UPDATE player SET score = ? WHERE name = ?",
        arguments: [2000, "Arthur"])
    
    let changedRows = db.changesCount
    if changedRows == 0 {
        print("No player named Arthur found")
    }
}

DELETE Operations

Delete Specific Rows
Delete All Rows
With RETURNING

try dbQueue.write { db in
    try db.execute(
        sql: "DELETE FROM player WHERE score < ?",
        arguments: [500])
}

try dbQueue.write { db in
    try db.execute(sql: "DELETE FROM player")
}

try dbQueue.write { db in
    let deletedNames = try String.fetchAll(db, sql: """
        DELETE FROM player 
        WHERE score < 500 
        RETURNING name
        """)
    print("Deleted players: \(deletedNames.joined(separator: ", "))")
}

Multiple Statements

Join multiple statements with a semicolon:

try dbQueue.write { db in
    try db.execute(sql: """
        INSERT INTO player (name, score) VALUES (?, ?);
        INSERT INTO player (name, score) VALUES (?, ?);
        """, arguments: ["Arthur", 750, "Barbara", 1000])
}

When executing multiple statements, if any statement fails, the entire operation stops at that point. Consider using transactions for atomic operations.

SQL Interpolation

SQL Interpolation provides safe value embedding with automatic escaping:

try dbQueue.write { db in
    let name = "O'Brien"  // Contains single quote
    let score = 1000
    let active = true
    
    try db.execute(literal: """
        INSERT INTO player (name, score, active) 
        VALUES (\(name), \(score), \(active))
        """)
}

Identifiers in SQL Interpolation

Use \(bind:) for table and column names:

let tableName = "player"
let columnName = "score"

try db.execute(literal: """
    UPDATE \(bind: tableName) 
    SET \(bind: columnName) = \(score) 
    WHERE id = \(id)
    """)

Avoiding SQL Injection

Never embed values directly in raw SQL strings! This creates SQL injection vulnerabilities.

// DANGEROUS: SQL injection vulnerability!
let name = textField.text
try db.execute(sql: "UPDATE player SET name = '\(name)' WHERE id = 1")

Statement Arguments

GRDB supports many Swift types as statement arguments:

Basic Types

try db.execute(
    sql: "INSERT INTO data (bool, int, double, string) VALUES (?, ?, ?, ?)",
    arguments: [
        true,           // Bool
        42,             // Int
        3.14,           // Double
        "Hello"         // String
    ])

Date and Time

try db.execute(
    sql: "INSERT INTO event (date, time) VALUES (?, ?)",
    arguments: [
        Date(),                 // Date
        DateComponents(hour: 10, minute: 30)  // DateComponents
    ])

Data and UUID

try db.execute(
    sql: "INSERT INTO file (data, uuid) VALUES (?, ?)",
    arguments: [
        Data([0x01, 0x02, 0x03]),  // Data
        UUID()                      // UUID
    ])

Enums

enum Color: Int, DatabaseValueConvertible {
    case red, white, rose
}

try db.execute(
    sql: "INSERT INTO wine (color) VALUES (?)",
    arguments: [Color.red])

Optional Values

let optionalName: String? = nil
try db.execute(
    sql: "INSERT INTO player (name) VALUES (?)",
    arguments: [optionalName])  // Inserts NULL

Error Handling

do {
    try dbQueue.write { db in
        try db.execute(
            sql: "INSERT INTO player (name) VALUES (?)",
            arguments: ["Arthur"])
    }
} catch let error as DatabaseError {
    print("Database error: \(error.message ?? "")")
    print("SQL: \(error.sql ?? "")")
    print("Code: \(error.resultCode)")
} catch {
    print("Unexpected error: \(error)")
}

Common Patterns

Batch Inserts

try dbQueue.write { db in
    let players = [
        ("Arthur", 1000),
        ("Barbara", 2000),
        ("Charlie", 1500)
    ]
    
    let statement = try db.makeStatement(
        sql: "INSERT INTO player (name, score) VALUES (?, ?)")
    
    for (name, score) in players {
        try statement.execute(arguments: [name, score])
    }
}

Conditional Updates

try dbQueue.write { db in
    try db.execute(sql: """
        UPDATE player 
        SET score = score + 100 
        WHERE score < 1000
        """)
    
    let updatedCount = db.changesCount
    print("Updated \(updatedCount) players")
}

Upsert (INSERT OR REPLACE)

try dbQueue.write { db in
    try db.execute(sql: """
        INSERT INTO player (id, name, score) 
        VALUES (?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            name = excluded.name,
            score = excluded.score
        """, arguments: [1, "Arthur", 1500])
}

Using Records for Persistence

For most use cases, prefer using record types instead of raw SQL:

struct Player: Codable, PersistableRecord {
    var id: Int64?
    var name: String
    var score: Int
}

try dbQueue.write { db in
    // INSERT
    var player = Player(id: nil, name: "Arthur", score: 1000)
    try player.insert(db)
    
    // UPDATE
    player.score = 1500
    try player.update(db)
    
    // DELETE
    try player.delete(db)
}

See the Records documentation for more information.

Performance Tips

Prepared Statements
Transactions
Batch Operations

Reuse prepared statements for better performance:

try dbQueue.write { db in
    let statement = try db.makeStatement(
        sql: "INSERT INTO player (name, score) VALUES (?, ?)")
    
    for _ in 0..<1000 {
        try statement.execute(arguments: ["Player", 100])
    }
}

Wrap multiple updates in a transaction:

try dbQueue.write { db in
    try db.inTransaction {
        for i in 0..<1000 {
            try db.execute(
                sql: "INSERT INTO player (name) VALUES (?)",
                arguments: ["Player \(i)"])
        }
        return .commit
    }
}

Use batch operations when possible:

try dbQueue.write { db in
    // Delete multiple rows at once
    try db.execute(
        sql: "DELETE FROM player WHERE score < ?",
        arguments: [500])
}

Next Steps

Fetch Queries - Learn how to query and retrieve data
Transactions - Learn about transactions and savepoints
Records - Learn about record types for easier database access

Getting Started

Core Concepts

Records

Query Interface

Advanced Features

Guides

Basic Usage

The execute Method

INSERT Operations

Getting the Inserted Row ID

UPDATE Operations

Checking Affected Rows

DELETE Operations

Multiple Statements

SQL Interpolation

Identifiers in SQL Interpolation

Avoiding SQL Injection

Statement Arguments

Basic Types

Date and Time

Data and UUID

Enums

Optional Values

Error Handling

Common Patterns

Batch Inserts

Conditional Updates

Upsert (INSERT OR REPLACE)

Using Records for Persistence

Performance Tips

Next Steps

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Records

Query Interface

Advanced Features

Guides

​Basic Usage

​The execute Method

​INSERT Operations

​Getting the Inserted Row ID

​UPDATE Operations

​Checking Affected Rows

​DELETE Operations

​Multiple Statements

​SQL Interpolation

​Identifiers in SQL Interpolation

​Avoiding SQL Injection

​Statement Arguments

​Basic Types

​Date and Time

​Data and UUID

​Enums

​Optional Values

​Error Handling

​Common Patterns

​Batch Inserts

​Conditional Updates

​Upsert (INSERT OR REPLACE)

​Using Records for Persistence

​Performance Tips

​Next Steps

Build docs developers (and LLMs) love

Basic Usage

The execute Method

INSERT Operations

Getting the Inserted Row ID

UPDATE Operations

Checking Affected Rows

DELETE Operations

Multiple Statements

SQL Interpolation

Identifiers in SQL Interpolation

Avoiding SQL Injection

Statement Arguments

Basic Types

Date and Time

Data and UUID

Enums

Optional Values

Error Handling

Common Patterns

Batch Inserts

Conditional Updates

Upsert (INSERT OR REPLACE)

Using Records for Persistence

Performance Tips

Next Steps