Sqlite syntax error with insertOnConflict

[sjoerdvisscher]

When running this code: insertOnConflict table (insertValues [tipRow]) anyConflict onConflictUpdateAll I get a runtime error:
SQLite3 returned ErrorError while attempting to perform prepare INSERT INTO "tip"("row_id", "row_slot", "row_block_id", "row_block_number") VALUES (?, ?, ?, ?) ON CONFLICT DO UPDATE SET "row_id" = "excluded"."row_id", "row_slot" = "excluded"."row_slot", "row_block_id" = "excluded"."row_block_id", "row_block_number" = "excluded"."row_block_number" near UPDATE: syntax error

If I execute this sql statement in the sqlite3 command line tool it works fine.

[kmicklas]

Do you have a minimal reproduction example?

[tp-woven]

Just ran into this too. This is my table declaration:

data ServiceMappingT f = ServiceMapping
  { _mappingServiceId :: PrimaryKey ServicesT f,
    _mappingClientId :: C f Types.ClientID
  }
  deriving (Generic, Beamable)

The insert:

  runInsert $
    insertOnConflict
      (<myTable>)
      ( insertValues
          [ ServiceMapping
              { _mappingServiceId = sId,
                _mappingClientId = c
              }
          ]
      )
      anyConflict
      onConflictUpdateAll

Table creation SQL (autoMigrate):

CREATE TABLE "service_mapping"("service_id__id" INTEGER NOT NULL , "client_id" VARCHAR NOT NULL , PRIMARY KEY("client_id"));

Insertion SQL:

INSERT INTO "service_mapping"("service_id__id", "client_id") VALUES (?, ?) ON CONFLICT  DO UPDATE SET "service_id__id" = "excluded"."service_id__id", "client_id" = "excluded"."client_id";

Results in:

SQLite3 returned ErrorError while attempting to perform prepare "INSERT INTO \"service_mapping\"(\"service_id__id\", \"client_id\") VALUES (?, ?) ON CONFLICT  DO UPDATE SET \"service_id__id\" = \"excluded\".\"service_id__id\", \"client_id\" = \"excluded\".\"client_id\"": near "UPDATE": syntax error

Also with onConflictUpdateSet:

INSERT INTO "service_mapping"("service_id__id", "client_id") VALUES (?, ?) ON CONFLICT  DO UPDATE SET "service_id__id" = ?, "client_id" = ?;
SQLite3 returned ErrorError while attempting to perform prepare "INSERT INTO \"service_mapping\"(\"service_id__id\", \"client_id\") VALUES (?, ?) ON CONFLICT  DO UPDATE SET \"service_id__id\" = ?, \"client_id\" = ?": near "UPDATE": syntax error

[tp-woven]

I (kinda) figured out the problem - ON CONFLICT DO UPDATE is only supported in SQLite v3.24.0 and later (see "4. History" section in SQLite docs). I did not specify any dependency versions in my project, getting the following dependency chain:

beam-sqlite 0.5.1.2
sqlite-simple 0.4.18.0
direct-sqlite 2.3.26

However, direct-sqlite 2.3.26 used an older version of SQLite. I added direct-sqlite >= 2.3.27, and that seems to have solved the issue (although I'm a Haskell noob, so there may be a better way to do this).