PersistableRecord

Persistence callback called before the record is inserted.

Default implementation does nothing.

Persistence callback called upon successful insertion.

The default implementation does nothing.

You can provide a custom implementation in order to grab the auto-incremented id:

class Player: PersistableRecord {
    var id: Int64?
    var name: String?

    func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

Executes an INSERT statement.

Executes an INSERT ... RETURNING ... statement, and returns a new record built from the inserted row.

This method helps dealing with default column values and generated columns.

For example:

// A table with an auto-incremented primary key and a default value
try dbQueue.write { db in
    try db.execute(sql: """
        CREATE TABLE player(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT,
          score INTEGER DEFAULT 1000)
        """)
}

// A player with partial database information
struct PartialPlayer: PersistableRecord {
    static let databaseTableName = "player"
    var name: String
}

// A full player, with all database information
struct Player: TableRecord, FetchableRecord {
    var id: Int64
    var name: String
    var score: Int
}

// Insert a base player, get a full one
try dbQueue.write { db in
    let partialPlayer = PartialPlayer(name: "Alice")

    // INSERT INTO player (name) VALUES ('Alice') RETURNING *
    if let player = try partialPlayer.insertAndFetch(db, as: FullPlayer.self) {
        print(player.id)    // The inserted id
        print(player.name)  // The inserted name
        print(player.score) // The default score
    }
}

Executes an INSERT ... RETURNING ... statement, and returns the selected columns from the inserted row.

This method helps dealing with default column values and generated columns.

For example:

// A table with an auto-incremented primary key and a default value
try dbQueue.write { db in
    try db.execute(sql: """
        CREATE TABLE player(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT,
          score INTEGER DEFAULT 1000)
        """)
}

// A player with partial database information
struct PartialPlayer: PersistableRecord {
    static let databaseTableName = "player"
    var name: String
}

// Insert a base player, get the inserted score
try dbQueue.write { db in
    let partialPlayer = PartialPlayer(name: "Alice")

    // INSERT INTO player (name) VALUES ('Alice') RETURNING score
    let score = try partialPlayer.insertAndFetch(db, selection: [Column("score")]) { statement in
        try Int.fetchOne(statement)
    }
    print(score) // The inserted score
}

Executes an INSERT or UPDATE statement.

If the receiver has a non-nil primary key and a matching row in the database, this method performs an update.

Otherwise, performs an insert.

Executes an INSERT ... RETURNING ... or UPDATE ... RETURNING ... statement, and returns a new record built from the saved row.

If the receiver has a non-nil primary key and a matching row in the database, this method performs an update. Otherwise, it performs an insert.

Executes an INSERT ... RETURNING ... or UPDATE ... RETURNING ... statement, and returns the selected columns from the saved row.

If the receiver has a non-nil primary key and a matching row in the database, this method performs an update. Otherwise, it performs an insert.

Executes an INSERT ... ON CONFLICT DO UPDATE statement.

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
let player = Player(id: 1, name: "Arthur", score: 1000)
try player.upsert(db)

Executes an INSERT ... ON CONFLICT DO UPDATE ... RETURNING ... statement, and returns the upserted record.

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]
    })

Executes an INSERT ... ON CONFLICT DO UPDATE ... RETURNING ... statement, and returns the upserted record.

See upsertAndFetch(_:onConflict:doUpdate:) for more information about the conflictTarget and assignments parameters.