How to upsert multiple records at the same time?

[pedrommcarrasco]

I have an array of records that I need to insert or update efficiently. I couldn’t find a Batch Upsert option in the documentation — could you provide any guidance?

[groue]

Hello @pedrommcarrasco,

Write a loop, and make sure this loop is wrapped a single write transaction:

// CORRECT: one loop wrapped in a single transaction.
try dbQueue.write { db in
    for player in players {
        try player.upsert(db)
    }
}

// VERY BAD PERFORMANCE: one loop performs many transactions.
// This forces a lot of slow file-system synchronizations.
for player in players {
    try dbQueue.write { db in
        try player.upsert(db)
    }
}

If you are surprised, please check Many Small Queries Are Efficient In SQLite.

---

Now, if you are looking for the absolute best performance you can get with GRDB, then go down to the SQLite metal and use a prepared statement. The library does not provide any convenience api for building one¹, so you'll write something like:

// BEST PERFORMANCE
func upsertPlayers(_ db: Database, _ players: [Player]) throws {
    let statement = try db.cachedStatement(sql: """
        INSERT INTO player (id, name, score)
        VALUES (?, ?, ?)
        ON CONFLICT DO UPDATE SET
          -- one line per upserted column
          name = excluded.name,
          score = excluded.score
        """)
     
     for player in players {
         try statement.execute(arguments: [
             player.id,
             player.name,
             player.score,
         ])
     }
}

See UPSERT for more information.

Footnotes

1. An application should be able to do it from the schema inspection methods ↩

[pedrommcarrasco]

Love it - thank you!