NULL, BLOBs, Text Encoding, Collations, and Cross-Language Boundaries

Learning outcomes

Types become operationally important at boundaries: JSON request to application, application object to driver, driver binding to SQLite, SQLite value back through the driver, and finally display in a terminal or UI. This lesson closes Chapter 4 by making those boundaries explicit, with special attention to NULL, binary data, text encoding, and text comparison rules.

NULL is a runtime value with SQL semantics—not an empty placeholder

From the prerequisite SQL course, recall that NULL represents missing/unknown information and participates in three-valued logic. In SQLite’s runtime model, NULL is also one of the five storage classes. Drivers normally map a host-language null-like value to SQL NULL, but your API contract must still distinguish “field absent,” “field present with null,” and “field present with an empty/zero value.”

SELECT typeof(NULL) AS null_type,       NULL IS NULL   AS is_null_test,       NULL = NULL    AS equality_result;DROP TABLE IF EXISTS optional_note;CREATE TABLE optional_note (    note_id INTEGER PRIMARY KEY,    comment TEXT) STRICT;INSERT INTO optional_note(comment) VALUES (NULL), (''), ('checked');SELECT note_id, quote(comment), typeof(comment)FROM optional_noteORDER BY note_id;

NULL IS NULL is true; NULL = NULL produces SQL NULL rather than true. The three rows then preserve three states: NULL, empty TEXT, and non-empty TEXT. Decide which states your application allows instead of treating them as interchangeable.

BLOB means bytes that SQLite does not interpret as text

A BLOB is an arbitrary byte sequence. In SQL source, hexadecimal BLOB literals use the form x'...' with an even number of hexadecimal digits. In applications, use the driver’s binary binding type rather than hex-encoding bytes into a text string unless textual encoding is actually part of the contract.

SELECT x'0001FF' AS payload,       typeof(x'0001FF') AS payload_type,       length(x'0001FF') AS byte_count,       hex(x'0001FF') AS printable_hex;DROP TABLE IF EXISTS binary_sample;CREATE TABLE binary_sample (    sample_id INTEGER PRIMARY KEY,    mime_type TEXT,    payload   BLOB NOT NULL) STRICT;INSERT INTO binary_sample(mime_type, payload)VALUES ('application/octet-stream', x'0001FF');SELECT sample_id, mime_type, length(payload), hex(payload)FROM binary_sample;

The sample payload is three bytes. hex() is useful for diagnostics because it creates readable hexadecimal TEXT without changing what is stored in the BLOB column.

Internal versus external binary data is a workload decision

SQLite can store large BLOBs, but “can” is not the same as “always should.” Keeping an attachment inside the database can make transactions and backups self-contained. Keeping large media externally can simplify streaming, CDN/object-storage integration, or lifecycle policies. Performance depends on object size, page size, filesystem, access pattern, cache behavior, and application architecture; do not repeat a universal size threshold as folklore.

Chapter 16 will cover reliable backup/recovery semantics. Here, the design rule is to measure the actual workload and preserve a consistent reference if content is external.

Database text encoding is not terminal display encoding

SQLite databases use one database text encoding—UTF-8, UTF-16 little-endian, or UTF-16 big-endian—for stored TEXT representation in the database file. PRAGMA encoding reports the main database’s encoding. Once a database is created, attempting to change its encoding with the setting form does not rewrite the database.

PRAGMA encoding;SELECT 'Tehran تهران' AS multilingual_text,       typeof('Tehran تهران') AS storage_class,       length('Tehran تهران') AS character_count;

If the database is valid but a terminal prints mojibake, the problem may be the terminal/code-page/font/output path rather than the SQLite database encoding. Conversely, a UTF-8 terminal does not prove a database was created with UTF-8. Keep the layers separate: database representation, driver/API string representation, and display environment.

Collation defines text comparison order, not Unicode normalization

A collating sequence tells SQLite how two TEXT values compare for ordering/equality contexts. SQLite has three built-in collations: BINARY, NOCASE, and RTRIM. The default is BINARY unless a column/expression specifies otherwise.

SELECT 'A' = 'a' COLLATE NOCASE AS ascii_case_equal,       'É' = 'é' COLLATE NOCASE AS non_ascii_case_equal,       'abc ' = 'abc' COLLATE RTRIM AS trailing_space_equal;

The expected results are 1, 0, and 1. NOCASE’s built-in behavior is intentionally limited to ASCII case folding. If your application needs locale-aware ordering, full Unicode case folding, or normalization-equivalent matching, register/use an appropriate application/extension collation and test it as part of your deployment contract.

Unicode normalization is a separate problem from collation

Unicode can represent visually similar text using different code-point sequences. SQLite’s built-in collations do not promise to normalize those sequences into one canonical form. That means “looks identical in the UI” is not a sufficient uniqueness or equality rule for international text.

-- These two strings can render similarly but are different code-point sequences:SELECT hex('é') AS precomposed_utf8,       hex('é') AS decomposed_utf8,       'é' = 'é' AS binary_equal,       'é' = 'é' COLLATE NOCASE AS nocase_equal;

On a normal UTF-8 connection, the hex sequences differ and the equality tests are false. If canonical equivalence matters—for usernames, tags, search keys, or deduplication—choose a normalization policy in the application or a tested Unicode-aware extension/collation. Do not assume BINARY or NOCASE silently provides it.

Host-language adapters translate application types into SQLite values

Every language binding needs a mapping from host types to SQLite’s small set of value types. The exact API differs, but a common primitive mapping looks like the table below. Dates, decimals, UUID objects, enums, and custom classes generally need an explicit representation choice rather than a portable built-in SQLite type.

import sqlite3con = sqlite3.connect(':memory:')con.execute('CREATE TABLE boundary(value)')values = [None, 42, 3.25, 'PUMP-007', sqlite3.Binary(b'\x10\x20')]for value in values:    con.execute('INSERT INTO boundary(value) VALUES (?)', (value,))print(con.execute(    'SELECT group_concat(typeof(value), ",") FROM boundary').fetchone()[0])con.close()

The expected class sequence is null,integer,real,text,blob. A production adapter should also define overflow/range behavior, text error handling, and conversion policy for logical types. Do not convert everything to strings because it is convenient.

Dates and custom host objects need explicit adapters

A host language may offer date, decimal, UUID, or enum classes that SQLite itself does not know. Some drivers provide convenience adapters; their defaults can vary by language and version. Treat those conveniences as driver behavior, not as SQLite storage semantics.

Explicit conversion makes cross-language behavior testable. A Node.js client, Python client, Java client, and mobile client can all agree on the SQLite representation even though their host-language classes are different.

Data-contract exercise: define one API write end to end

Suppose an API receives a FieldNotes measurement. Before writing SQL, define what each field means at every boundary. The table below is a reusable design-review format.

DROP TABLE IF EXISTS api_measurement;CREATE TABLE api_measurement (    measurement_id INTEGER PRIMARY KEY,    device_code    TEXT NOT NULL,    observed_at    TEXT NOT NULL,    reading        REAL,    is_valid       INTEGER NOT NULL CHECK (is_valid IN (0,1)),    attachment     BLOB,    trace_uuid     TEXT NOT NULL,    service_minor  INTEGER CHECK (service_minor IS NULL OR service_minor >= 0)) STRICT;INSERT INTO api_measurement(    device_code, observed_at, reading, is_valid,    attachment, trace_uuid, service_minor) VALUES (    'SENS-003', '2026-08-12T05:50:00Z', 21.75, 1,    x'89504E47', '550e8400-e29b-41d4-a716-446655440000', NULL);SELECT device_code,       typeof(observed_at), typeof(reading), typeof(is_valid),       typeof(attachment), typeof(trace_uuid), typeof(service_minor)FROM api_measurement;

Expected classes are TEXT, REAL, INTEGER, BLOB, TEXT, and NULL for the selected fields. The final exercise is to write the corresponding host-language binding types beside each column and add negative tests for wrong UUID form, invalid Boolean, oversize attachment, and ambiguous timestamp.

Failure patterns and safe corrections

Chapter 4 design review checklist

Reuse this checklist whenever a new field is added to the course database or to a production SQLite schema.

Summary and bridge to Chapter 5

Chapter 4 replaced the “SQLite has no types” myth with a layered model. Runtime values have five storage classes. Ordinary columns derive affinity from declared type names and may convert values. STRICT tables add per-table rigid type contracts while preserving intentional heterogeneity through ANY. Logical types such as dates, Booleans, exact money, and UUIDs require explicit representation choices. Finally, drivers, encodings, collations, and NULL/BLOB semantics determine whether that contract survives across language boundaries.

Chapter 5 builds on this foundation by making invalid database states difficult to store: NOT NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT, foreign keys, conflict handling, generated columns, and integrity auditing.

References

• SQLite — Datatypes In SQLite.
• SQLite — Binding Values To Prepared Statements.
• SQLite — PRAGMA encoding and other PRAGMAs.
• SQLite — Database File Format and built-in collations.
• SQLite — Internal Versus External BLOBs.
• SQLite — Implementation Limits.
• SQLite — STRICT Tables.
• SQLite — Release History.