SqlDelight ON CONFLICT DO UPDATE throws near "ON": syntax error (code 1 SQLITE_ERROR)

Question

I'm using SqlDelight in a KMM project. I'm having problems when using the INSERT ON CONFLICT DO UPDATE command.

After some investigation, I found that I needed to update my dialect version of SqlDelight to 3.25 which I did and it works fine, except it still crashes on Api 29 and below.

I tested it on a pixel 4 Api 29 emulator and on a physical device pixel 3 Api 28 which both crash.

On Pixel 2 Api 30 and above emulators works fine.

SqlDelight version: 1.5.3

dialect = "sqlite:3.25"

My insert statement looks like this

insertLocalPortfolioItem:
INSERT INTO localPortfolioList(name, symbol, pricePerShare, currency, cost, noOfShares, logo, availableFunds, orderAction)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(symbol) DO UPDATE SET noOfShares=excluded.noOfShares;

StackTrace:

android.database.sqlite.SQLiteException: near "ON": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO localPortfolioList(name, symbol, pricePerShare, currency, cost, noOfShares, logo, availableFunds, orderAction)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(symbol) DO UPDATE SET noOfShares=excluded.noOfShares

Any ideas please?

Answer 1

Your problem would be in the version of SQLite shipped with Android, upsert clauses appear in SQLite 3.24+. If you are using the SupportSQLiteDatabase APIs from AndroidX, you can use the SQLite build from Requery, which is a drop-in replacement:

https://github.com/requery/sqlite-android