react-nativesqliteexpoandroid-sqlitetypeorm

Expo-sqlite syntax error on older android versions for correctly formatted errors for UPSERTS

For older versions of android < 10, upserts do not seem to be working. I am trying to run a simple upsert and constantly getting this syntax error when it gets converted into SQL: near "ON": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO dummy_table (name_id, date, dummy_value) VALUES ('abc123', 1717200000, 1212) ON CONFLICT (name_id, date) DO UPDATE SET dummy_value = excluded.dummy_value;

The query works on iOS and newer versions of android. Maybe upserts using the ON CONFLICT syntax isn't compatible for such versions? If so, is there maybe a workaround to that?

I am pretty much facing the exact thing this person is facing in this thread: https://github.com/expo/expo/issues/16776

Solution

  • From https://www.sqlite.org/lang_upsert.html

    UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04)

    Android R (version 11 API 30) is the earliest that supports UPSERT (SQLite version 3.28.0) previous versions of Android were officially at 3.22.0 or lower.

    as per:-

    https://developer.android.com/reference/android/database/sqlite/package-summary.html

    which includes:-

    Android API SQLite Version
LATEST      3.42.0
API 34      3.39
API 33      3.32
API 32      3.32
API 31      3.32
API 30      3.28
API 28      3.22
API 27      3.19
....

    The simplest get arounds would likely be to either

    1. detect the conflict when inserting and follow with an UPDATE, or
    2. if viable, attempt the update detect that no row was updated and then insert.