upsertAndFetch(_:onConflict:doUpdate:)

With default parameters (upsertAndFetch(db)), the upsert behavior is triggered by a violation of any uniqueness constraint on the table (primary key or unique index). In case of violation, all columns but the primary key are overwritten with the inserted values:

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

// INSERT INTO player (id, name, score)
// VALUES (1, 'Arthur', 1000)
// ON CONFLICT DO UPDATE SET
//   name = excluded.name,
//   score = excluded.score
// RETURNING *
let player = Player(id: 1, name: "Arthur", score: 1000)
let upsertedPlayer = try player.upsertAndFetch(db)

With conflictTarget and assignments arguments, you can further control the upsert behavior. Make sure you check https://www.sqlite.org/lang_UPSERT.html for detailed information.

The conflict target are the columns of the uniqueness constraint (primary key or unique index) that triggers the upsert. If empty, all uniqueness constraint are considered.

The assignments describe how to update columns in case of violation of a uniqueness constraint. In the next example, we insert the new vocabulary word “jovial” if that word is not already in the dictionary. If the word is already in the dictionary, it increments the counter, does not overwrite the tainted flag, and overwrites the remaining columns:

// CREATE TABLE vocabulary(
//   word TEXT PRIMARY KEY,
//   kind TEXT NOT NULL,
//   isTainted BOOLEAN DEFAULT 0,
//   count INT DEFAULT 1))
struct Vocabulary: Encodable, PersistableRecord {
    var word: String
    var kind: String
    var isTainted: Bool
}

// INSERT INTO vocabulary(word, kind, isTainted)
// VALUES('jovial', 'adjective', 0)
// ON CONFLICT(word) DO UPDATE SET \
//   count = count + 1,
//   kind = excluded.kind
// RETURNING *
let vocabulary = Vocabulary(word: "jovial", kind: "adjective", isTainted: false)
let upserted = try vocabulary.upsertAndFetch(
    db,
    onConflict: ["word"],
    doUpdate: { _ in
        [Column("count") += 1,
         Column("isTainted").noOverwrite]
    })