Quick answer: Test SQLite JSON storage at five boundaries: document validity, null-versus-missing behavior, extracted SQL types, structured update values, and transaction read-back. A valid JSON string is only the first gate. Database expressions and application serializers can still change the meaning of values that reach an API client.
An array goes into an update helper and comes back as a quoted string. A missing property becomes indistinguishable from explicit null. These failures can survive a happy-path insert test because the document remains perfectly valid JSON.
The examples use Python 3.12.14 with SQLite 3.53.1, as reported by the tested connection. They cover JSON stored as text, not SQLite JSONB interoperability or performance. Our PostgreSQL and MySQL JSON guide covers those engines separately; do not assume their extraction or update semantics transfer unchanged to SQLite.
1. Does the storage boundary reject malformed and absent documents?
Create an explicit JSON validity constraint and test malformed text separately from SQL null. A text column does not validate JSON automatically, and a CHECK constraint is not a replacement for NOT NULL. Keep document syntax, root shape and required business fields as distinct checks so one passing gate cannot stand in for the others.
SQLite's CREATE TABLE documentation describes CHECK and NOT NULL constraints, including their different failure conditions. This example intentionally requires a non-null, syntactically valid JSON text. It does not yet require the document to be an object or to contain any particular field.
import sqlite3
import json
db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("""
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
payload TEXT NOT NULL CHECK (json_valid(payload))
)
""")
db.execute("INSERT INTO documents VALUES (?, ?)", (1, '{"name":"Maya"}'))
for bad in ['{"name":', None]:
try:
db.execute("INSERT INTO documents(payload) VALUES (?)", (bad,))
except sqlite3.IntegrityError:
pass
else:
raise AssertionError("invalid storage input accepted")
assert db.execute("SELECT count(*) FROM documents").fetchone()[0] == 1
Add an accepted fixture for every root type your API permits. If a route requires an object, valid strings, arrays and null must fail at the appropriate shape-validation gate. A database accepting those values under this syntax-only constraint is not a database bug; it means the contract needs another assertion or an application validator.
Use the JSON Schema overview to define a separate application shape contract when database syntax checks are insufficient.
Use parameter binding for payload values. Python's sqlite3 documentation explains its parameter placeholders. A quote inside a customer label should be data, not part of dynamically assembled SQL. Keep SQL construction concerns separate from whether the JSON text itself is syntactically valid.
Retain a negative test through the real insert path, including any framework model or repository wrapper. A standalone constraint test cannot prove that production writes target the same table or use the same migration. Include a schema check in deployment verification so a missing migration cannot quietly remove the intended storage gate.
Before adding a constraint to an existing dataset, inventory rows that would fail the new policy. Decide whether to reject, repair or quarantine each class of historical data, and preserve a reproducible migration fixture. A clean newly created test table cannot demonstrate that a production migration handles old malformed documents without dropping records or changing accepted API behavior unexpectedly.
2. Can the application distinguish missing paths from explicit JSON null?
Query both the extracted value and its JSON type when absence has different meaning from explicit null. A single-path json_extract result can collapse those states into SQL NULL. Include a present value as a control, and assert the exact application mapping so serializers do not turn an omitted property into a misleading explicit null.
The SQLite JSON function reference documents extraction and type inspection. A practical fixture compares the same path across two small documents. Do not infer the original document's intent solely from the Python value returned by the driver when the SQL expression has already discarded that distinction.
explicit = '{"note":null}'
missing = '{}'
for text in [explicit, missing]:
assert db.execute("SELECT json_extract(?, '$.note')", (text,)).fetchone()[0] is None
assert db.execute("SELECT json_type(?, '$.note')", (explicit,)).fetchone()[0] == 'null'
assert db.execute("SELECT json_type(?, '$.note')", (missing,)).fetchone()[0] is None
assert db.execute("SELECT json_type(?, '$.note')", ('{"note":"ok"}',)).fetchone()[0] == 'text'
Decide what each state means before writing the response mapper. For a profile field, absence might mean never supplied while null might mean deliberately cleared. For another endpoint, both may be equivalent. Either policy can be valid; the important test is that the application implements the chosen policy consistently.
Our JSON null versus undefined guide explains the related API distinction. SQLite's SQL NULL is another representation boundary, not JavaScript undefined. Avoid naming all three states empty in documentation, because that makes it difficult for client developers to predict updates and response fields.
Test a stored document and a constructed query result independently. A reporting query might intentionally coalesce missing values, while a write endpoint must preserve them. Reusing that reporting projection as the source for an update can erase the very distinction the original row retained. Keep the accepted behavior attached to the operation, not only the field name.
3. Do extracted scalar types match the API's declared types?
Assert both values and SQL types for fields extracted from JSON before building an API response. Single-path extraction can yield SQL scalars rather than JSON source text. A numeric string, a number and a boolean can therefore need different handling, even when the application driver represents some resulting values using the same host-language type.
Use a fixture that deliberately places similar-looking values beside each other. This makes accidental coercion visible without depending on a large production record. The assertions below check the tested connection and driver together rather than assuming that a printed query table reveals all type information.
sample = '{"number":7,"text":"7","enabled":true,"items":[1,2]}'
row = db.execute("""
SELECT json_extract(?, '$.number'),
typeof(json_extract(?, '$.number')),
json_extract(?, '$.text'),
typeof(json_extract(?, '$.text')),
json_extract(?, '$.enabled'),
json_type(?, '$.enabled')
""", (sample,) * 6).fetchone()
assert row == (7, 'integer', '7', 'text', 1, 'true')
array_text = db.execute("SELECT json_extract(?, '$.items')", (sample,)).fetchone()[0]
assert json.loads(array_text) == [1, 2]
The true value is especially useful. If the API promises a JSON boolean, passing the extracted integer straight to a generic response encoder can produce a number instead. Decide whether to preserve the original document, inspect JSON types, or apply an explicit schema-aware mapping. Test the actual response value, not just the database row.
Keep identifiers and numeric measurements separate. An identifier encoded as a string can have leading zeros that a numeric conversion removes. Add a fixture such as a product code with leading zeros when the domain uses one, and reject a helper that converts every numeric-looking string for convenience.
Review query changes as serialization changes. Replacing a whole-document fetch with a few extracted columns can alter output types even when the field names remain identical. Use an approved response fixture before and after the change. A successful SQL query proves that the expression ran, not that its output still satisfies the API contract.
Include a positive control that retrieves the original JSON document and parses it through the normal response path. Compare that response with the response assembled from extracted columns. If they differ, classify the difference as an intentional projection or a defect before release. This makes boolean conversion and missing-field policy visible to reviewers instead of treating every mismatch as harmless database formatting.
4. Does a structured update store an array rather than array-looking text?
Compare a plain text value argument with a JSON-producing value when updating a nested field. Array-looking SQL text can be inserted as a JSON string instead of a JSON array. Assert the resulting JSON type and decoded structure, then persist the intended expression and read the row back to confirm the stored representation.
SQLite distinguishes JSON arguments from ordinary value arguments in its JSON functions. The example makes that boundary explicit with json around the bound array text. Treat this conversion as a deliberate operation on already approved JSON, not permission to accept arbitrary application shapes without validation.
base = '{"tags":[]}'
array_input = '["new","sale"]'
quoted = db.execute("SELECT json_set(?, '$.tags', ?)", (base, array_input)).fetchone()[0]
structured = db.execute("SELECT json_set(?, '$.tags', json(?))", (base, array_input)).fetchone()[0]
assert json.loads(quoted)["tags"] == array_input
assert json.loads(structured)["tags"] == ["new", "sale"]
assert db.execute("SELECT json_type(?, '$.tags')", (structured,)).fetchone()[0] == 'array'
db.execute("UPDATE documents SET payload=json_set(payload, '$.tags', json(?)) WHERE id=?",
(array_input, 1))
stored = db.execute("SELECT payload FROM documents WHERE id=?", (1,)).fetchone()[0]
assert json.loads(stored) == {"name": "Maya", "tags": ["new", "sale"]}
Use JSON Compare to review the expected and retrieved documents when a migration or update helper changes. It reports structural differences while ignoring object-key order and whitespace. It does not execute SQL, verify transaction behavior or guarantee that an update expression targeted the intended row.
Test invalid array text and a valid JSON scalar separately. Parsing malformed text should fail, but a valid scalar can still be the wrong type for tags. If the endpoint promises an array of strings, validate both the root array and its elements before invoking the write helper. Successful json conversion is a syntax check, not that field's domain policy.
Keep the path fixed or validate any application-selected path against the endpoint's permitted update surface. Binding a value protects that value's SQL boundary; it does not decide which business fields a caller is authorized to modify. A storage test should not be described as a complete authorization check.
5. Does rollback preserve the previous document after a later write fails?
Exercise a multi-statement transaction where an early JSON update succeeds and a later statement violates a constraint. Roll back explicitly, then read the original row and verify it is unchanged. A statement error and an application transaction policy are different boundaries, so test the rollback path your actual connection code implements.
SQLite's transaction documentation describes transaction control. The test uses an explicit BEGIN and ROLLBACK with a connection configured for explicit control. It does not claim that every database error automatically undoes every prior statement in a transaction, or that the same behavior proves crash durability.
before = db.execute("SELECT payload FROM documents WHERE id=1").fetchone()[0]
db.execute("BEGIN")
try:
db.execute("UPDATE documents SET payload=json_set(payload, '$.name', ?) WHERE id=1",
('Changed',))
db.execute("INSERT INTO documents(payload) VALUES (?)", ('{bad}',))
except sqlite3.IntegrityError:
db.execute("ROLLBACK")
else:
raise AssertionError("expected transaction failure")
after = db.execute("SELECT payload FROM documents WHERE id=1").fetchone()[0]
assert json.loads(after) == json.loads(before)
assert not db.in_transaction
Run the same scenario through your repository or service layer. An exception handler that catches a failure and continues can commit an earlier update if it does not implement the intended rollback policy. Include a successful transaction as a positive control and read its result through the application's normal retrieval path.
Keep external side effects outside an assumed database rollback guarantee. If a service publishes a message before the transaction fails, rolling back the row does not retract that message. Add an application-level assertion around the notification or queue boundary when the operation uses one. The database fixture establishes row restoration only; the complete workflow needs evidence that its other observable effects follow the chosen commit policy too.
The in-memory fixture isolates local behavior. For a deployed application, add a file-backed persistence test, close and reopen the connection, and compare the document again. If multiple clients can update the same record, add a separate concurrency strategy and test it; this rollback example does not prevent lost updates by itself.
Wikipedia's SQLite background describes the database's embedded role. These five release tests focus on a narrower question: whether this connection and application preserve the JSON contract they promise. Keep the tested SQLite library version with the results, because the Python runtime name alone does not identify every database capability available in the deployment.