SQLite query macros don't reject INSERTs that omit NOT NULL columns without defaults

[pascalporedda]

Summary

With sqlx 0.8.6 and SQLite, a compile-time checked sqlx::query! INSERT that omits a NOT NULL / no-DEFAULT column still compiles. SQLite then rejects it only at runtime with NOT NULL constraint failed.

Reproduction

Repo: https://github.com/pascalporedda/sqlx-sqlite-not-null-repro

Schema:

CREATE TABLE session_group (
    prop_a TEXT NOT NULL,
    prop_b INTEGER NOT NULL,
    prop_c TEXT NOT NULL
);

Query that compiles:

sqlx::query!(
    r#"
    INSERT INTO session_group (prop_a, prop_b)
    VALUES (?, ?)
    "#,
    "test1",
    123_i64,
)

Commands:

cargo check
cargo run --quiet

Observed runtime output:

full insert rows_affected = 1
partial insert runtime result = Err(
    Database(
        SqliteError {
            code: 1299,
            message: "NOT NULL constraint failed: session_group.prop_c",
        },
    ),
)
persisted rows = [
    "ok:1:present",
]
row count = 1

Expected

I expected the compile-time checked query macro to reject the INSERT because prop_c is omitted and is NOT NULL with no default.

Actual

The query compiles and only fails when executed.

Notes

I looked through the 0.8.6 sources locally and it appears the SQLite describe path provides bind parameter count and result-column metadata, but not target-table write completeness. If this is the intended limit of checking for SQLite, a documentation note would help. If not, this looks like a missing compile-time verification case.

[barry3406]

This is a fundamental limitation of how sqlx's compile-time checking works with SQLite. The macro uses EXPLAIN to analyze queries, which gives back bind parameter types and result column metadata, but SQLite's EXPLAIN doesn't provide information about which target columns a write statement touches vs. which ones it's missing.

For Postgres, the wire protocol's Describe message returns richer metadata that can catch some of these cases. SQLite's C API (sqlite3_column_* family) is designed around result sets, not write validation.

To actually catch this at compile time, sqlx would need to:

1. Parse the INSERT to extract the target table and column list
2. Query pragma table_info(...) for the target table
3. Cross-reference to find NOT NULL columns without defaults that are missing from the INSERT

That's doable but non-trivial — it means the macro needs to understand INSERT syntax beyond just handing the SQL to SQLite's EXPLAIN. It's essentially a new validation pass specific to write statements.

At minimum, a doc note on the SQLite section clarifying the scope of compile-time checking would save people from this surprise. Happy to put up a PR for that if the maintainers think it's the right call.