SQLite Data Type Mappings, Column Type Codes, and Unexpected BLOB Results

Core Concepts: SQLite Storage Classes, Type Affinities, and Column Type Reporting

SQLite’s approach to data types is fundamentally different from traditional rigidly-typed database systems. The system employs three distinct but interrelated concepts that often create confusion: storage classes, type affinities, and interface type codes. Storage classes represent the actual data format used to store values at the binary level. Type affinities represent column-level preferences for how values should be stored. Interface type codes are return values from API functions like sqlite3_column_type() that report on value representations during query execution.

The confusion arises because:

1. Storage classes (NULL, INTEGER, REAL, TEXT, BLOB) define how data is physically stored
2. Type affinities (TEXT, NUMERIC, INTEGER, REAL, BLOB) influence but don’t enforce storage class selection
3. C API constants (SQLITE_INTEGER=1, SQLITE_FLOAT=2, SQLITE_TEXT=3, SQLITE_BLOB=4, SQLITE_NULL=5) use different numbering than internal storage codes
4. Serial type codes in the database file format use completely different numeric identifiers

This complex interaction explains why users searching for a simple mapping table between these concepts will struggle. The system’s flexible typing allows multiple storage classes in a single column, while type affinities provide storage hints rather than constraints. When combined with SQLite’s automatic type conversions and the multiple numbering systems used in different layers (API vs storage format), this creates an environment where naive type code expectations often fail.

Common Misconceptions Leading to Unexpected Type Code Results

1. Assuming Column Affinities Enforce Storage Classes
Many developers expect that defining a column as INTEGER will guarantee all values use the INTEGER storage class. In reality:

• Affinity only influences storage class selection during insertion
• Updates can change storage classes without constraint
• CAST operations and expression results may use different storage classes
• UNION queries and VIEWs can combine multiple storage classes in single columns

2. Confusing API Return Codes With Storage Class Identifiers
The sqlite3_column_type() function returns C API constants (SQLITE_INTEGER=1, SQLITE_FLOAT=2, SQLITE_TEXT=3, SQLITE_BLOB=4, SQLITE_NULL=5), which:

• Don’t match the serial type codes used in the database file format
• Are different from the numbers used in type affinity system
• Report the current storage class of a value, not the column’s declared affinity

3. Misinterpreting Query Result Storage Classes
When a query contains expressions rather than direct column references:

• SQLite may convert values between storage classes during evaluation
• Temporary BLOB storage is common for complex expressions
• Type derivation follows different rules than column affinity
• UNION ALL operations use "affinityless" columns

4. Overlooking Type Conversion in Indexed Queries
Index lookups can trigger implicit conversions that change storage classes:

• Comparisons between different storage classes follow type conversion rules
• Indexed columns may return values in different formats than stored
• COLLATE clauses affect comparison but not storage class

5. Misunderstanding the Impact of First-Row Typing
When a column contains multiple storage classes:

• Some drivers/base libraries infer types from the first row
• sqlite3_column_type() returns the current row’s storage class
• Empty tables/columns may report ambiguous types

Comprehensive Diagnosis and Resolution Strategies

1. Establishing Ground Truth With typeof() and Storage Class Inspection
Execute diagnostic queries to reveal actual storage classes:

SELECT 
  column1, 
  typeof(column1) AS storage_class,
  column1 IS column1 AS is_unchanged
FROM my_table;

The IS operator check detects hidden type conversions – a TRUE result indicates no conversion occurred. For API-level inspection in C code:

int col_type = sqlite3_column_type(stmt, 0);
switch(col_type) {
  case SQLITE_INTEGER: /* 1 */ break;
  case SQLITE_FLOAT:   /* 2 */ break; 
  case SQLITE_TEXT:    /* 3 */ break;
  case SQLITE_BLOB:    /* 4 */ break;
  case SQLITE_NULL:    /* 5 */ break;
}

2. Resolving Persistent BLOB-Type Reports
When sqlite3_column_type() consistently returns BLOB (5):

Check 1: Expression Result Typing
Queries containing:

• JSON functions (json(), json_extract())
• Cryptographic functions (sha1(), encrypt())
• Complex math expressions
• Subqueries returning multiple storage classes

often return BLOB. Use explicit CAST() to convert:

SELECT CAST(sha1(data) AS TEXT) FROM records;

Check 2: Column Definition Ambiguity
Tables created without type affinities:

CREATE TABLE ambiguous (data); -- No affinity

will not influence storage class selection. Add explicit affinity:

ALTER TABLE ambiguous ADD COLUMN data_text TEXT;
UPDATE ambiguous SET data_text = CAST(data AS TEXT);

Check 3: Driver/Binding Type Handling
Some language bindings (Python, JavaScript) automatically convert BLOBs to other types. Force BLOB handling:

# Python example
conn = sqlite3.connect(":memory:")
conn.text_factory = bytes  # Disable text conversion

3. Mapping Between Storage Classes and Type Affinities
Create explicit conversion tables:

CREATE TABLE type_mapping (
  value ANY TYPE,
  declared_affinity TEXT,
  actual_storage TEXT GENERATED ALWAYS AS (typeof(value))
);

INSERT INTO type_mapping VALUES
  (123, 'INTEGER'),
  ('123', 'TEXT'),
  (123.45, 'REAL'),
  (x'1234', 'BLOB');

Query to see affinity vs storage relationships:

SELECT declared_affinity, actual_storage, count(*) 
FROM type_mapping
GROUP BY 1,2;

4. Handling Multi-Storage Class Columns
For columns containing multiple storage classes:

Strategy 1: Normalize Storage Classes

UPDATE inconsistent_data
SET number_column = 
  CASE WHEN typeof(number_column)='TEXT' 
       THEN CAST(number_column AS INTEGER)
       ELSE number_column
  END;

Strategy 2: Query-Time Handling

SELECT 
  column1,
  CASE typeof(column1)
    WHEN 'INTEGER' THEN column1
    WHEN 'TEXT' THEN CAST(column1 AS INTEGER)
    ELSE NULL
  END AS normalized_int
FROM flexible_columns;

5. Debugging Serial Type Codes in Database Files
To inspect low-level storage types (advanced):

# Use sqlite3_analyzer tool
sqlite3_analyzer database.db | grep 'Serial type'

Interpret serial type codes using SQLite’s file format specification:

• 0: NULL
• 1-6: Various integer formats (1=1-byte, 6=8-byte)
• 7: IEEE float (8 bytes)
• 8: Integer constant 0
• 9: Integer constant 1
• 10-12: Reserved
• N>12: BLOB (if even) or TEXT (if odd), with (N-13)/2 bytes

6. Forcing Type Stability in Queries
Use strict typing with CHECK constraints:

CREATE TABLE strict_types (
  id INTEGER PRIMARY KEY,
  sensor_reading REAL 
    CHECK (typeof(sensor_reading) = 'real')
);

For virtual columns with fixed types:

CREATE VIEW typed_view AS
SELECT 
  column1,
  CAST(column2 AS INTEGER) AS column2_int 
FROM source_table;

7. API-Specific Type Handling Patterns
In C code, implement comprehensive type checking:

sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, "SELECT data FROM table", -1, &stmt, 0);

while(sqlite3_step(stmt) == SQLITE_ROW) {
  switch(sqlite3_column_type(stmt, 0)) {
    case SQLITE_INTEGER:
      handle_int(sqlite3_column_int64(stmt,0));
      break;
    case SQLITE_FLOAT:
      handle_double(sqlite3_column_double(stmt,0));
      break;
    case SQLITE_TEXT:
      handle_text(sqlite3_column_text(stmt,0));
      break;
    case SQLITE_BLOB:
      handle_blob(sqlite3_column_blob(stmt,0),
                  sqlite3_column_bytes(stmt,0));
      break;
    case SQLITE_NULL:
      handle_null();
      break;
  }
}

8. Addressing Cross-Language Type Mapping Issues
Common language-specific solutions:

Python:

# Force TEXT/BLOB retention
conn = sqlite3.connect(':memory:')
conn.text_factory = str  # Disable UTF-8 conversion
conn.blob_factory = bytes

# Type detection
def typed_row_factory(cursor, row):
    return [ (col[0], cursor.description[i][0], row[i]) 
             for i, col in enumerate(cursor.description) ]
conn.row_factory = typed_row_factory

JavaScript (node-sqlite3):

db.each("SELECT data FROM table", (err, row) => {
  // Access type information
  this.get(0, (err, value) => {
    console.log('Type:', this.columnType(0));
  });
});

9. Performance Implications of Type Mismatches
Improper type handling causes:

• Index bypassing due to type conversions
• Storage bloat from non-optimal storage classes
• Query plan inefficiencies

Diagnose with EXPLAIN:

EXPLAIN QUERY PLAN
SELECT * FROM table WHERE CAST(column AS INTEGER) = 5;

Optimization strategies:

• Add redundant columns with proper types
• Use materialized views
• Create expression indexes

CREATE INDEX idx_cast_column ON table(CAST(column AS INTEGER));

10. Comprehensive Type Mapping Reference Table

11. Advanced Diagnostic Queries
Check affinity vs storage class distribution:

SELECT 
  name AS column_name,
  type AS declared_affinity,
  (SELECT typeof([name]) FROM [table] LIMIT 1) AS first_row_type,
  (SELECT COUNT(DISTINCT typeof([name])) FROM [table]) AS type_count
FROM pragma_table_info('[table]');

Identify columns with multiple storage classes:

SELECT 
  column_name,
  group_concat(storage_class) AS existing_types
FROM (
  SELECT 
    'my_column' AS column_name,
    typeof(my_column) AS storage_class
  FROM my_table
  GROUP BY storage_class
);

12. Permanent Schema Design Solutions
Implement type stability through generated columns:

CREATE TABLE stable_data (
  input_data ANY TYPE,
  as_int INTEGER GENERATED ALWAYS AS (CAST(input_data AS INTEGER)),
  as_text TEXT GENERATED ALWAYS AS (CAST(input_data AS TEXT)),
  storage_class TEXT GENERATED ALWAYS AS (typeof(input_data))
);

Use STRICT tables (SQLite 3.37+):

CREATE TABLE strict_types (
  id INTEGER PRIMARY KEY,
  data TEXT
) STRICT;

This comprehensive approach addresses the fundamental misunderstandings about SQLite’s type system while providing practical solutions for real-world type handling challenges. Developers should focus on managing storage class conversions rather than fighting the system’s inherent flexibility, using SQLite’s type affinity system as a guide rather than a constraint.