Generated Column Visibility and Type Inconsistencies in SQLite

Understanding Generated Column Visibility and Data Type Reporting

Generated columns in SQLite implement a powerful feature for deriving values through expressions while presenting unique challenges in schema introspection. Two critical behavioral anomalies emerge when working with these columns:

1. Absence from PRAGMA table_info Output: Generated columns do not appear in standard schema introspection tools like PRAGMA table_info('table_name'), requiring developers to use extended metadata queries via PRAGMA table_xinfo('table_name') to detect them.
2. Inconsistent Type Metadata in table_xinfo: The type column returned by PRAGMA table_xinfo displays erratic values depending on how columns were declared – including empty strings, fragments of the column definition syntax (e.g., "generated always"), or hybrid combinations of data types and syntax elements.

These behaviors stem from SQLite’s internal handling of column metadata and parsing logic for generated column definitions. Generated columns are flagged internally using the same storage mechanism that tracks hidden columns (e.g., rowid aliases), but with different bitmask values. The parser’s treatment of the text between the column name and the AS keyword in CREATE TABLE statements directly influences what appears in the type field during introspection.

The practical implications are significant: tools relying on table_info will fail to acknowledge generated columns, while ORMs or schema migration systems parsing table_xinfo may misinterpret column types due to non-standard metadata formatting. Developers working with generated columns must account for these idiosyncrasies when building schema-aware applications or debugging persistence layer issues.

Root Causes of Missing Generated Columns and Inconsistent Type Metadata

Three interconnected factors explain the observed behavior:

Metadata Storage Design for Generated Columns

SQLite uses the hidden flag in the table_xinfo output to indicate special column types. Standard columns have a hidden value of 0, while virtual generated columns use 2 and stored generated columns use 3. The PRAGMA table_info command filters out all columns where hidden != 0, which explains the absence of generated columns from its output. This design choice prioritizes backward compatibility but creates a disconnect between basic and extended schema introspection.

Syntax Parsing Ambiguities in Column Definitions

When parsing CREATE TABLE statements, SQLite captures all tokens between the column name and the AS keyword for generated columns as the declared type. For example:

CREATE TABLE t1 (
  a INT GENERATED ALWAYS AS (b*2) STORED,  -- type becomes "INT GENERATED ALWAYS"
  c TEXT AS (d||'suffix') VIRTUAL         -- type becomes "TEXT"
);

The parser does not validate whether these tokens constitute a legitimate data type. This allows free-form text (including SQL keywords) to appear in the type metadata, resulting in the inconsistent values seen in table_xinfo.

Historical Constraints in SQLite’s Type System

SQLite’s flexible type affinity system permits arbitrary type names, which normally allows compatibility with other SQL dialects. However, this flexibility becomes a liability with generated columns, as the parser doesn’t distinguish between actual data types and syntax fragments in column definitions. The lack of dedicated metadata fields for generation expressions exacerbates the issue, forcing the reuse of existing metadata storage slots.

Resolving Generated Column Metadata Issues and Ensuring Consistent Type Handling

Strategy 1: Comprehensive Schema Introspection

To reliably detect generated columns, replace all usage of PRAGMA table_info with PRAGMA table_xinfo and filter using the hidden column:

SELECT name, type, hidden FROM pragma_table_xinfo('table_name') 
WHERE hidden IN (2, 3); 

This returns all generated columns (2=VIRTUAL, 3=STORED) with their declared types. For applications requiring backward compatibility, combine this with table_info results to get a complete column list.

Strategy 2: Normalizing Type Metadata

When processing table_xinfo output, implement a type normalization layer that:

1. Checks if the type string contains "GENERATED ALWAYS" (case-insensitive)
2. Extracts the portion before "GENERATED" as the effective data type
3. Falls back to the full type string if no generation keywords are present

Example normalization code (Python):

def normalize_type(col_type):
    import re
    match = re.match(r'^(.+?)\s*GENERATED\s+ALWAYS.*$', col_type, re.IGNORECASE)
    return match.group(1).strip() if match else col_type

Strategy 3: Schema Definition Best Practices

Enforce strict syntax in generated column declarations to produce consistent type metadata:

-- Recommended format
CREATE TABLE products (
    price REAL CHECK (price > 0),
    tax_rate REAL,
    total_price REAL GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

-- Avoid ambiguous declarations like:
CREATE TABLE products (
    total_price GENERATED ALWAYS AS (...) VIRTUAL,  -- type becomes empty string
    tax INCLUDING DEFAULT AS (...) STORED           -- type becomes "INCLUDING DEFAULT"
);

Always include an explicit data type before the GENERATED keyword. This ensures the type metadata in table_xinfo contains only the intended data type.

Strategy 4: Direct Schema Parsing

For mission-critical applications, bypass the PRAGMA interfaces entirely and parse the CREATE TABLE statement from sqlite_master:

SELECT sql FROM sqlite_master 
WHERE type='table' AND name='table_name';

Parse the generated column definitions using a state machine that:

1. Identifies column boundaries
2. Detects the GENERATED ALWAYS clause
3. Extracts the explicit data type (if any) before the generation keywords
4. Captures the generation expression

This approach provides full control over type interpretation but requires robust SQL parsing capabilities.

Strategy 5: Version-Specific Workarounds

SQLite versions 3.31.0+ (2020-01-22) introduced generated columns. Earlier versions lack support entirely. For applications targeting multiple SQLite versions:

-- Detect generated column support
SELECT (SELECT count(*) FROM pragma_compile_options WHERE compile_options LIKE 'ENABLE_GENERATED_COLUMNS') > 0;

When generated columns are unavailable, either disable related features or implement them through triggers and conventional columns.

Strategy 6: ORM and Framework Integration

When using ORMs like SQLAlchemy or Django’s ORM:

1. Override schema introspection methods to use table_xinfo instead of table_info
2. Map the hidden column values to SQLAlchemy’s Computed construct
3. Register custom type decorators that handle the normalized type strings

Example SQLAlchemy mixin:

from sqlalchemy import event, inspect

class GeneratedColumnMixin:
    @classmethod
    def __declare_last__(cls):
        insp = inspect(cls.__table__)
        for col in insp.columns:
            if col.info.get('generated'):
                # Apply computed column configuration
                col.computed = sqlalchemy.Computed(...)

Strategy 7: Migration and Validation Tooling

Incorporate generated column checks into schema migration pipelines:

1. Verify that all generated columns appear in table_xinfo outputs
2. Assert that their type metadata matches expected patterns
3. Cross-reference generation expressions with application-level expectations

Example pytest validation:

def test_generated_columns_sanity(db_connection):
    cursor = db_connection.execute(
        "SELECT name, type, sql FROM pragma_table_xinfo('products') WHERE hidden > 1"
    )
    for col in cursor.fetchall():
        assert col['type'] == 'REAL', f"Unexpected type {col['type']} for generated column {col['name']}"
        assert 'GENERATED ALWAYS' in col['sql'], "Generation clause missing in SQL definition"

Final Recommendations

1. Prefer Stored Generated Columns when using SQLite 3.31.0+, as their storage behavior is more predictable than virtual columns.
2. Explicitly Declare Data Types for all generated columns to avoid empty type strings.
3. Audit Schema Introspection Code to handle the hidden=2/3 values and parsed type strings.
4. Validate Generation Expressions separately from type metadata, as the expressions don’t appear in PRAGMA outputs.

By combining these strategies, developers can reliably work with generated columns while mitigating the inconsistencies in SQLite’s metadata reporting. The key is to assume that table_info is incomplete for generated columns and that table_xinfo type strings require sanitization before use in type-sensitive operations.