Storing Settings as JSON in SQLite and Updating One Key

Application settings are the most natural use of a JSON column. Each user has a handful of preferences, the set grows with every release, and a column per preference means a migration per release. SQLite is a good home for them, with a few details worth getting right at the start: a check constraint so the column can only hold an object, the right function for changing one key, the right function for merging a partial update, defaults handled on read, and what happens when two requests change the same row. This article builds a small settings table and exercises each of those.

The table

create table settings (
  user_id    integer primary key,
  prefs      text not null default '{}'
             check (json_valid(prefs) and json_type(prefs) = 'object'),
  updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) strict;

Three decisions here. The column is TEXT and not a blob, so the data is readable in any tool (the JSONB article covers when to use the binary form). It has a default of '{}', so a new row is an empty object and readers never meet NULL. And the check insists on a JSON object, not any JSON: json_valid alone would accept [1,2], 5 or "text", which break every $.key path. The check rejected all of these in my test, with CHECK constraint failed: json_valid(prefs) and json_type(prefs) = 'object':

• '[1,2]' (an array)

• 'not json'

• '' (the empty string, which is not JSON)

• an update that set the whole value to 5 with json_set(prefs, '$', 5)

and a NULL failed the NOT NULL. The check runs on updates too, so a buggy json_set that would corrupt the column fails instead of writing.

strict adds type checking for the other columns: inserting 'abc' as user_id raised datatype mismatch.

Changing one key

json_set(doc, path, value) replaces or adds the value at a path, and in SQLite it also creates missing parents:

update settings set prefs = json_set(prefs, '$.theme', 'light') where user_id = 2;
update settings set prefs = json_set(prefs, '$.notify.sms', json('true')) where user_id = 2;
update settings set prefs = json_set(prefs, '$.font.size', 14) where user_id = 1;

The third statement made {"font":{"size":14}} from an empty object: no font existed, and json_set built it. In PostgreSQL and MySQL the same call returns the document unchanged, so code ported between them behaves differently. The second statement shows the type rule: to store a JSON true, write json('true'). A bare 1 = 1 or the integer 1 stores 1, and the text 'true' stores the string "true". A reader that checks === true will care.

Other single-key edits: json_remove(prefs, '$.font') deletes a key ({} was the result for user 1 afterward), json_insert adds a key only if it is missing, and json_replace changes only an existing key. For a default that should not overwrite an existing value, json_insert is the right choice: json_insert(prefs, '$.theme', 'system') leaves a chosen theme alone.

Merging a partial update

When the application sends several changed settings at once, json_patch(target, patch) merges them following RFC 7396: keys in the patch override, nested objects merge, null deletes a key, and arrays are replaced as a whole. Combined with an upsert, one statement handles both "create" and "update":

insert into settings (user_id, prefs) values (3, '{"theme":"dark"}')
  on conflict (user_id) do update set prefs = json_patch(settings.prefs, excluded.prefs);

insert into settings (user_id, prefs) values (3, '{"lang":"fr","theme":null}')
  on conflict (user_id) do update set prefs = json_patch(settings.prefs, excluded.prefs);

After the second statement, user 3's preferences were {"lang":"fr"}: the lang was added and the theme, patched with null, was removed. Two more patches, {"notify":{"email":false}} and then {"notify":{"sms":true}}, left {"lang":"fr","notify":{"email":false,"sms":true}}: the nested object merged instead of being replaced. This is the behavior you want for settings forms. It has two consequences to remember.

You cannot store a JSON null through a patch. A null means "delete this key". If "explicitly unset" must be distinguishable from "never set", use json_set with json('null'), or a sentinel value, or a separate boolean.

Arrays are replaced, not merged. Patching {"recent":["a"]} over {"recent":["x","y"]} leaves ["a"]. If a setting is a list, send the whole new list.

json_patch in ON CONFLICT DO UPDATE refers to the existing row as settings.prefs and to the incoming values as excluded.prefs, and both are text, so there is no conversion to worry about.

Reading with defaults

Defaults belong in the reader, so that a new setting needs no data migration:

select user_id,
       coalesce(prefs ->> '$.theme', 'system')    as theme,
       coalesce(prefs ->> '$.notify.email', 1)    as email,
       coalesce(prefs ->> '$.font.size', 12) + 0  as size
from settings order by 1;

user_id  theme   email  size
1        system  1      14
2        light   1      12
3        system  0      12

->> returns an SQL value, NULL for a missing key, so coalesce supplies the default. Remember that a JSON false arrives as the integer 0, and coalesce treats 0 as a value, not as missing, so user 3's explicit false for email was kept. The + 0 on the size forces a number when the value might have been stored as text. Keep the defaults in one place in the application (a dictionary merged under the stored preferences) if there are many; reading each one with coalesce in SQL is fine for a few.

Counters, and when not to use JSON for them

An integer inside the document can be incremented:

update settings
set prefs = json_set(prefs, '$.visits', coalesce(prefs ->> '$.visits', 0) + 1)
where user_id = 1;

Run twice, it left {"visits":2} with json_type integer; unlike MySQL, SQLite did not turn the integer into a double. The statement is atomic in SQLite, since a write transaction locks the database, so two simultaneous increments do not lose one. But every increment rewrites the whole prefs text and fires the trigger, so a counter that changes on every request belongs in its own integer column or a separate table.

Concurrency and compare-and-swap

SQLite allows one writer at a time, so two UPDATE statements run one after the other, and a statement like json_set(prefs, ...) always applies to the latest committed value. That makes single-statement updates safe. The lost-update problem appears when the application reads the JSON, edits it in code and writes the whole value back: another writer's change in between is overwritten. Send the change, not the document. If the application really must read first (for example to show a diff), add the old value to the WHERE clause:

update settings set prefs = json_set(prefs, '$.theme', 'blue')
where user_id = 2 and prefs ->> '$.theme' = 'light';
select changes();

changes() returned 1 the first time and 0 the second, because the theme was no longer light. A zero means someone else got there first; re-read and retry. This costs nothing and needs no locks beyond the statement's own.

Keeping a modified time

SQLite has no ON UPDATE column. A trigger does it:

create trigger settings_touch after update of prefs on settings
begin
  update settings set updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now') where user_id = new.user_id;
end;

Because the trigger is declared after update of prefs, it fires only when that column is assigned, and its own update touches updated_at only, so it does not recurse. After my updates, each user's updated_at had a distinct timestamp. Store times as ISO 8601 text so that SQLite's date functions and sort order both work.

Schema versions

A settings document ages. Fields are renamed, defaults change, and old values need cleanup. Put a version in the document: {"v":2, ...} and write a migration that runs update settings set prefs = ... where prefs ->> '$.v' is not 2, as a single statement using json_set, json_remove and json_patch. Applying it in a transaction with a count check before commit is straightforward in SQLite, because DDL and DML share transactions. For validation beyond "is an object", extend the check with a few json_type tests on the critical keys, as in validating text as JSON with json_valid. The overall function reference is in JSON functions in SQLite.

When settings should not be one JSON blob

The settings are queried across users. "Which users have dark mode" is a scan unless the field is indexed (indexing JSON fields in SQLite with expression indexes); if that is a routine admin query, a column is simpler.

A setting has to be enforced. A foreign key, a CHECK (font_size between 8 and 72) or a NOT NULL is a column property. In a document you can approximate each with json_type or json_extract checks, at the price of readability.

Settings are large or frequently written. Each update rewrites the whole text. A document with a long history list does not belong here.

Many writers touch different settings of one user. Single-statement json_set updates are safe, and the one-writer model still serializes them; high write rates on one row are a bottleneck.

Other programs read the database. Anything that wants a typed column gets a text blob. A view with ->> extractions can present columns to those readers.

The relevant manual pages are json_patch, UPSERT and STRICT tables.