Practical SQLite Workflows

Upsert Setting

Insert or Update

ON CONFLICT lets a workflow write a row whether the key is new or already present.

Program

Play the script to choose a theme value and update an existing setting through an upsert.

upsert_setting.sql 
CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);
INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
WITH params(wanted_theme) AS (VALUES ()) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;
SELECT key, value FROM settings ORDER BY key;

CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);
INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
WITH params(wanted_theme) AS (VALUES ()) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;
SELECT key, value FROM settings ORDER BY key;

CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);
INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
WITH params(wanted_theme) AS (VALUES ()) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;
SELECT key, value FROM settings ORDER BY key;
ON CONFLICT `ON CONFLICT(key)` handles the duplicate primary-key case.
excluded `excluded.value` is the value the insert tried to write.
idempotent workflow Running the same shape of write can create or update the target row.