Fast and safe bulk inserts in SQLite: transactions, WAL and iOS

SQLite is everywhere: in iOS and Android apps, desktop tools, test suites, edge devices and plenty of .NET services. It's also one of the easiest databases to make look slow. Insert a few hundred thousand rows one statement at a time and an import that should take a second or two can stretch into minutes. Speed it up with the wrong PRAGMA and you may pay for that time with a corrupted file after the next power cut.

This article covers why bulk inserts are slow by default, the techniques that fix it, what the often-recommended "turbo" settings really cost, and how all of it applies on iOS with the SQLite C API, Core Data and SwiftData, with a .NET version alongside.

The default: every INSERT is its own transaction

If you don't open a transaction yourself, SQLite wraps each statement in its own. A durable commit means waiting for the storage layer to confirm the data is really written. The SQLite FAQ has explained this for years: SQLite can run a lot of INSERT statements per second, but only a few dozen transactions per second on spinning disks. A 2024 note on that FAQ entry says the numbers are far higher on today's SSDs, but the point stands. Grouping work into one transaction avoids paying the commit cost for every row.

The second cost is parsing. If you build a fresh SQL string for each row, for example with sprintf + sqlite3_exec, SQLite compiles that SQL into bytecode every time. You're also one quote character away from SQL injection.

Three techniques that make bulk inserts fast

Three techniques do most of the work:

1. One transaction around the batch. This is by far the biggest win.
2. Reuse a prepared statement. sqlite3_prepare_v2 once, then bind/step/reset in a loop.
3. Load first, index later. Every secondary index is another B-tree that has to be updated for each row. For a one-off import into an empty table, building the index at the end is usually cheaper.

Here's the pattern in Swift using the C API directly. It works the same on iOS and macOS:

import SQLite3

// Tells SQLite to copy the bound string (Swift strings are temporary)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

struct Stop { let routeId: String; let stopNo: Int; let time: String }

enum SQLiteError: Error { case failed(String) }

func check(_ rc: Int32, _ db: OpaquePointer?, expected: Int32 = SQLITE_OK) throws {
    guard rc == expected else {
        throw SQLiteError.failed(String(cString: sqlite3_errmsg(db)))
    }
}

func bulkInsert(_ db: OpaquePointer?, _ rows: [Stop]) throws {
    let sql = "INSERT INTO stops (route_id, stop_no, time) VALUES (?1, ?2, ?3)"
    var stmt: OpaquePointer?
    try check(sqlite3_prepare_v2(db, sql, -1, &stmt, nil), db)
    defer { sqlite3_finalize(stmt) }

    try check(sqlite3_exec(db, "BEGIN IMMEDIATE", nil, nil, nil), db)
    do {
        for row in rows {
            sqlite3_bind_text(stmt, 1, row.routeId, -1, SQLITE_TRANSIENT)
            sqlite3_bind_int64(stmt, 2, Int64(row.stopNo))
            sqlite3_bind_text(stmt, 3, row.time, -1, SQLITE_TRANSIENT)

            try check(sqlite3_step(stmt), db, expected: SQLITE_DONE)
            sqlite3_reset(stmt)
            sqlite3_clear_bindings(stmt)
        }
        try check(sqlite3_exec(db, "COMMIT", nil, nil, nil), db)
    } catch {
        sqlite3_exec(db, "ROLLBACK", nil, nil, nil)
        throw error
    }
}

Two details matter. SQLITE_TRANSIENT matters because the Swift string bridge only lives for the duration of the call, so SQLite has to copy it. BEGIN IMMEDIATE takes the write lock up front, so you don't hit a busy error halfway through the batch.

The .NET version with Microsoft.Data.Sqlite follows the same shape. Microsoft's own bulk insert page says the same thing: SQLite has no special bulk API, so use a transaction and reuse one parameterized command.

using Microsoft.Data.Sqlite;

static void BulkInsert(SqliteConnection connection, IEnumerable<Stop> rows)
{
    using var transaction = connection.BeginTransaction();

    using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText =
        "INSERT INTO stops (route_id, stop_no, time) VALUES ($route, $stop, $time)";

    var route = command.Parameters.Add("$route", SqliteType.Text);
    var stop  = command.Parameters.Add("$stop",  SqliteType.Integer);
    var time  = command.Parameters.Add("$time",  SqliteType.Text);

    foreach (var row in rows)
    {
        route.Value = row.RouteId;
        stop.Value  = row.StopNo;
        time.Value  = row.Time;
        command.ExecuteNonQuery();
    }

    transaction.Commit();
}

record Stop(string RouteId, int StopNo, string Time);

The cost of the "turbo" settings

PRAGMA synchronous=OFF and PRAGMA journal_mode=MEMORY are often recommended for bulk loads, and they do make writes faster. But the trade is bigger than "you might lose the last few rows". According to the PRAGMA documentation:

• With synchronous=OFF, an application crash is fine, but if the OS crashes or the device loses power before the data reaches storage, the database file might become corrupted.
• With journal_mode=MEMORY, the rollback journal lives in RAM, and if the application crashes in the middle of a transaction, the database file will very likely go corrupt.
• journal_mode=OFF is worse still: it removes atomic commit and rollback entirely.

On a desktop tool that rebuilds a cache file at startup, that might be acceptable. You can always regenerate the file. On a phone holding a user's notes, it isn't. iOS can kill your app at any moment, and batteries die. My rule: use these settings only on throwaway databases you can rebuild from scratch, and switch them back off afterwards.

Modern defaults: WAL + NORMAL

The setting I recommend as a default is:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

The semantics, straight from sqlite.org/wal.html and the PRAGMA docs:

• In WAL mode, readers don't block the writer and the writer doesn't block readers. The docs say this is "mostly true": there are obscure cases where you can still get SQLITE_BUSY, so keep a busy timeout.
• WAL with synchronous=NORMAL is safe from corruption and always consistent.
• Transactions are durable across application crashes regardless of the synchronous setting.
• What you give up: a transaction committed in WAL + NORMAL might roll back after a power loss or OS crash. You lose the most recent commits, but the database stays intact.
• journal_mode=WAL is persistent: set it once and the database file stays in WAL mode across reopens. synchronous is per connection, so set it every time you open one.

For most apps that's the right trade: no corruption, no lost data on an app crash, and a small window of recent commits at risk on power loss. If a commit truly must survive a power cut (a payment receipt, say), use synchronous=FULL for that connection.

The iOS angle

Most iOS developers never call sqlite3_* directly, but they're still using SQLite.

Core Data. The SQLite store has used WAL as its default journal mode since iOS 7 and OS X Mavericks, according to Apple's Technical Q&A QA1809. That's why you see -wal and -shm files next to your .sqlite file. The practical lesson from that note: never back up or move only the main file. Copy all three, or better, use NSPersistentStoreCoordinator's migration APIs.

For bulk imports, NSBatchInsertRequest (iOS 13+) writes straight to the persistent store without creating a managed object per row:

import CoreData

func importStops(_ stops: [StopDTO], container: NSPersistentContainer) async throws {
    let context = container.newBackgroundContext()
    try await context.perform {
        var index = 0
        let request = NSBatchInsertRequest(entityName: "Stop") { (row: NSMutableDictionary) -> Bool in
            guard index < stops.count else { return true }   // true = done
            let s = stops[index]
            row["routeId"] = s.routeId
            row["stopNo"] = s.stopNo
            row["time"] = s.time
            index += 1
            return false
        }
        request.resultType = .objectIDs
        let result = try context.execute(request) as? NSBatchInsertResult
        let ids = result?.result as? [NSManagedObjectID] ?? []
        NSManagedObjectContext.mergeChanges(
            fromRemoteContextSave: [NSInsertedObjectsKey: ids],
            into: [container.viewContext])
    }
}

The catch: it goes around the managed object context, so you don't get the usual save() machinery (relationships in particular are a weak spot for batch inserts), and your in-memory objects won't know about the new rows until you merge them, as above.

SwiftData. Apple's documentation describes the default store as using Core Data as its underlying storage, so you're still on SQLite underneath, with the same -wal and -shm files on disk. For large imports, the same rule applies: insert from a background ModelContext (a @ModelActor is the usual way) and save once per batch, not once per object.

GRDB. If you want SQL without writing C interop yourself, GRDB describes itself as a toolkit for SQLite databases focused on application development. Its DatabasePool opens the database in WAL mode, and its write blocks run inside a transaction, so the advice above becomes the default. Whether it's worth a dependency compared to Core Data or SwiftData depends on how much you want to write SQL yourself.

Common pitfalls

• A transaction per row in disguise. Calling context.save() or SaveChanges() inside the loop undoes the whole point.
• Huge single transactions on the main thread. One transaction is fast, but a 500k-row import still blocks. Do it in the background, and chunk it (say, a few thousand rows per transaction) if you need progress reporting or want to keep memory flat.
• Forgetting sqlite3_reset. The sqlite3_bind_* calls then return SQLITE_MISUSE, which hardly anyone checks. Modern SQLite resets automatically on the next step, so you can end up quietly inserting the previous row's values again.
• Copying only .sqlite. In WAL mode, committed data can still be sitting in the -wal file.
• Turbo settings that never get switched off. A pragma added "just for the import" can easily end up in production.

Checklist

• Batch inside one explicit transaction (BEGIN IMMEDIATE if there may be other writers).
• One prepared or parameterized statement, reused for every row.
• Secondary indexes created after the bulk load on fresh tables.
• journal_mode=WAL once, synchronous=NORMAL on every connection.
• synchronous=OFF / journal_mode=MEMORY only on throwaway databases you can rebuild.
• On iOS: background context, NSBatchInsertRequest for big imports, and back up all three store files together.

The fundamentals haven't changed: one transaction, one prepared statement, indexes last. What has changed is that we now have a safe default, WAL + NORMAL, that gets most of the speed without betting the database file on the battery.