Provides SQLite 3.46+ changes up to 3.51: json_pretty/unistr/jsonb functions, percentile aggregates, multi-arg iif/if, numeric underscores, date modifiers, new C APIs, DBCONFIG options. Load for recent SQLite work.
npx claudepluginhub nevaberry/nevaberry-plugins --plugin sqlite-knowledge-patchThis skill uses the workspace's default tool permissions.
Designs and optimizes AI agent action spaces, tool definitions, observation formats, error recovery, and context for higher task completion rates.
Implements structured self-debugging workflow for AI agent failures: capture errors, diagnose patterns like loops or context overflow, apply contained recoveries, and generate introspection reports.
Compares coding agents like Claude Code and Aider on custom YAML-defined codebase tasks using git worktrees, measuring pass rate, cost, time, and consistency.
Claude's baseline knowledge covers SQLite through 3.45. This skill provides changes from 3.46.0 (2024-05-23) onwards.
| Function | Version | Description |
|---|---|---|
json_pretty(X) / json_pretty(X,indent) | 3.46.0 | Pretty-print JSON with optional indent string |
unistr(X) | 3.50.0 | Convert string with \uXXXX Unicode escapes to characters |
unistr_quote(X) | 3.50.0 | Quote string using Unicode escape syntax |
jsonb_each(X) | 3.51.0 | Like json_each() but returns JSONB for array/object values |
jsonb_tree(X) | 3.51.0 | Like json_tree() but returns JSONB for array/object values |
median(X) | 3.47.0 | Median aggregate (CLI extension) |
percentile(X,P) | 3.47.0 | Percentile aggregate (CLI extension) |
percentile_cont(X,P) | 3.47.0 | Continuous percentile (CLI extension) |
percentile_disc(X,P) | 3.47.0 | Discrete percentile (CLI extension) |
-- 3.48.0: if() is an alias for iif()
-- 3.48.0: 2-argument form returns NULL when false
SELECT
if (x > 0, 'positive');
-- NULL when x <= 0
-- 3.49.0: accepts any number of args >= 2 (chained if/elseif)
SELECT
iif (x > 0, 'positive', x < 0, 'negative', 'zero');
SELECT
1_000_000;
-- 1000000
SELECT
3.141_592_653;
-- 3.141592653
SELECT
0xFF_FF;
-- 65535
-- New strftime format specifiers
SELECT strftime('%G', 'now'); -- ISO 8601 week-numbering year
SELECT strftime('%V', 'now'); -- ISO 8601 week number
-- Ceiling/floor modifiers for ambiguous month shifts
SELECT date('2024-01-31', '+1 month', 'ceiling'); -- 2024-03-01
SELECT date('2024-01-31', '+1 month', 'floor'); -- 2024-02-29
See references/new-apis.md for details.
sqlite3_setlk_timeout() (3.50.0) — separate timeout for blocking lockssqlite3_set_errmsg() (3.51.0) — extensions can set error messagessqlite3_db_status64() (3.51.0) — 64-bit version of sqlite3_db_status()sqlite3changeset_apply_v3() (3.51.0) — session extension// 3.49.0 — all default to ON
sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_ATTACH_CREATE, 0, 0); // prevent ATTACH creating new DBs
sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_ATTACH_WRITE, 0, 0); // make ATTACH read-only
sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_COMMENTS, 0, 0); // disallow SQL comments
-- Negative RHS on ->> accesses from the right of an array
SELECT '[10,20,30]' ->> -1; -- 30 (last element)
SELECT '[10,20,30]' ->> -2; -- 20 (second to last)
fts5_tokenizer_v2 API and locale=1 option for locale-aware tokenizerscontentless_unindexed=1 for contentless tables with persistent UNINDEXED columnsinsttoken config option and fts5_insttoken() function for prefix queriesgroup_concat() returns empty string (not NULL) for single empty string inputsqlite3_rsync — experimental tool for replicating SQLite databases%Q/%q printf with # flag converts control chars to unistr() escapesPRAGMA wal_checkpoint=NOOP — check if checkpoint is needed without doing onecarray and percentile extensions in amalgamation (compile with -DSQLITE_ENABLE_CARRAY / -DSQLITE_ENABLE_PERCENTILE)