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.

wanted_theme
upsert_setting.sql
Replay: real traced execution (multi-file project)
CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);
INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
WITH params(wanted_theme) AS (VALUES ('dark')) 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 ('light')) 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 ('contrast')) 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;

  1. tables ← 1 row

    1CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
    values this step1 rowtables

  2. settings ← 2 rows

    1CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');3WITH params(wanted_theme) AS (VALUES ('dark')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;
    values this step2 rowssettings

  3. settings ← 2 rows

    2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');3WITH params(wanted_theme) AS (VALUES ('dark')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;4SELECT key, value FROM settings ORDER BY key;
    values this step2 rowssettings

  4. result ← 2 rows

    3WITH params(wanted_theme) AS (VALUES ('dark')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;4SELECT key, value FROM settings ORDER BY key;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
    values this step1 rowtables

  2. settings ← 2 rows

    1CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');3WITH params(wanted_theme) AS (VALUES ('light')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;
    values this step2 rowssettings

  3. settings ← 2 rows

    2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');3WITH params(wanted_theme) AS (VALUES ('light')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;4SELECT key, value FROM settings ORDER BY key;
    values this step2 rowssettings

  4. result ← 2 rows

    3WITH params(wanted_theme) AS (VALUES ('light')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;4SELECT key, value FROM settings ORDER BY key;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');
    values this step1 rowtables

  2. settings ← 2 rows

    1CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');3WITH params(wanted_theme) AS (VALUES ('contrast')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;
    values this step2 rowssettings

  3. settings ← 2 rows

    2INSERT INTO settings VALUES ('theme', 'light'), ('page_size', '20');3WITH params(wanted_theme) AS (VALUES ('contrast')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;4SELECT key, value FROM settings ORDER BY key;
    values this step2 rowssettings

  4. result ← 2 rows

    3WITH params(wanted_theme) AS (VALUES ('contrast')) INSERT INTO settings(key, value) VALUES ('theme', (SELECT wanted_theme FROM params)) ON CONFLICT(key) DO UPDATE SET value = excluded.value;4SELECT key, value FROM settings ORDER BY key;
    values this step2 rowsresult

Follow the Upsert

  1. settings starts with theme=light and page_size=20.
  2. wanted_theme is dark.
  3. The insert tries to write key theme.
  4. theme already exists, so ON CONFLICT updates its value.
  5. The final ordered rows are page_size 20 and theme dark. | wanted_theme | existing theme | final theme | final ordered rows | | --- | --- | --- | --- | | dark | light | dark | page_size 20; theme dark | | light | light | light | page_size 20; theme light | | contrast | light | contrast | page_size 20; theme contrast |
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.

Exercise: upsert_setting.sql

Reproduce page_size 20 and theme dark, then use wanted_theme light and contrast to predict the final theme row.