How to update or insert with SQLite.swift

Question

I want to update the column of a row if the row already exists, but if it doesn't exist yet then I want to insert a new row.

Related questions

This type of question is popular for SQL in general

• UPDATE if exists else INSERT in SQL Server 2008
• Solutions for INSERT OR UPDATE on SQL Server
• Insert into a MySQL table or update if exists

and SQLite in particular

• INSERT IF NOT EXISTS ELSE UPDATE?
• SQLite - UPSERT *not* INSERT or REPLACE
• SQLite UPSERT / UPDATE OR INSERT
• SQL: How to update or insert if doesn't exist?

Looking for SQLite.swift implementation

I'm trying to save development time by using the SQLite.swift wrapper for iOS development. I chose this framework because it was recommended on raywenderlich.com. I think it would be useful to have an example of the syntax for an update or insert.

Strategy

In this answer, Sam Saffron says:

If you are generally doing updates I would ..

1. Begin a transaction
2. Do the update
3. Check the rowcount
4. If it is 0 do the insert
5. Commit

If you are generally doing inserts I would

1. Begin a transaction
2. Try an insert
3. Check for primary key violation error
4. if we got an error do the update
5. Commit

This way you avoid the select and you are transactionally sound on Sqlite.

That makes sense to me, so in my answer below I am providing an example of the "generally doing updates".

Answer 1

In this example, the user dictionary stores words that are typed on a custom keyboard. If the word is already in the dictionary, then that word's frequency count is incremented by 1. But if the word hasn't been entered before, then a new row is inserted with a default frequency of 1.

The table was created with the following schema:

let userDictionary = Table("user_dictionary")
let wordId = Expression<Int64>("id")
let word = Expression<String>("word")
let frequency = Expression<Int64>("frequency")        

// ...

let _ = try db.run( userDictionary.create(ifNotExists: true) {t in
    t.column(wordId, primaryKey: true)
    t.column(word, unique: true)
    t.column(frequency, defaultValue: 1)
    })

Taken from the question, this is what we want to do:

1. Begin a transaction
2. Do the update
3. Check the rowcount
4. If it is 0 do the insert
5. Commit

Here is how the code would look.

let wordToUpdate = "hello"

// ...

// 1. wrap everything in a transaction
try db.transaction {

    // scope the update statement (any row in the word column that equals "hello")
    let filteredTable = userDictionary.filter(word == wordToUpdate)

    // 2. try to update
    if try db.run(filteredTable.update(frequency += 1)) > 0 { // 3. check the rowcount

        print("updated word frequency")

    } else { // update returned 0 because there was no match

        // 4. insert the word
        let rowid = try db.run(userDictionary.insert(word <- wordToUpdate))
        print("inserted id: \(rowid)")
    }
} // 5. if successful, transaction is commited

See the SQLite.swift documentation for more help.