Null values in SQLite are being converted to default values without error or default attribute

[msberk]

Bug Description

When using the non-macro query_as functions, and deriving FromRow, the code always sets default value for the returned value if it is null and the field in the struct is not an Option. This is surprising, I would expect an Err return if a column is null and trying to be forced into a struct without an Option. I do not see this documented. I know that these functions are not the "normal" usage (macros preferred for sqlx) but I would still expect them to work similar to serde_json if a field is not found and the struct field is not an Option, i.e. return an Err.

Minimal Reproduction

src/main.rs

use sqlx::SqlitePool;

#[derive(sqlx::FromRow)]
struct Todo {
    id: i64,
    description: String,
    done: bool,
}

#[derive(sqlx::FromRow)]
struct TodoOption {
    id: i64,
    description: String,
    done: Option<bool>,
}

#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
    let pool = SqlitePool::connect("sqlite::memory:").await?;

    // read migrations from the filesystem into a string
    let migration = include_str!("../migrations/todos.sql");

    // Run migration on start
    sqlx::query(&migration).execute(&pool).await?;

    println!();
    list_todos(&pool).await?;

    Ok(())
}


async fn list_todos(pool: &SqlitePool) -> anyhow::Result<()> {
    println!("Printing value of todo.done using Todo struct (no Option for nullable done column)");

    let query_str = r#"
SELECT id, description, done
FROM todos
ORDER BY id
        "#;

    let rec: Todo = sqlx::query_as(&query_str)
    .fetch_one(pool)
    .await?;

    println!("Done value: {:?}", rec.done);
    println!();

    println!("Printing value of todo.done using TodoOption struct (with Option for nullable done column)");

    let rec: TodoOption = sqlx::query_as(&query_str)
    .fetch_one(pool)
    .await?;

    println!("Done value: {:?}", rec.done);

    Ok(())
}

migrations/todo.sql

CREATE TABLE IF NOT EXISTS todos
(
    id          INTEGER PRIMARY KEY NOT NULL,
    description TEXT                NOT NULL,
    done        BOOLEAN             DEFAULT NULL
);

INSERT INTO todos (description) VALUES ('done is null');

cargo.toml

[package]
name = "sqlx_demo"
version = "0.1.0"
edition = "2021"

[dependencies]
anyhow = "1.0"
futures = "0.3"
sqlx = { version = "0.7.4", features = ["sqlite", "runtime-tokio-native-tls"] }
tokio = { version = "1.20.0", features = ["rt", "macros"]}

Output

Printing value of todo.done using Todo struct (no Option for nullable done column)
Done value: false

Printing value of todo.done using TodoOption struct (with Option for nullable done column)
Done value: None

Info

• SQLx version: 0.7.4
• SQLx features enabled: "sqlite", "runtime-tokio-native-tls"
• Database server and version: SQLite 3.43.2
• Operating system: Mac OS 14.4.1
• rustc --version: 1.77.2 (25ef9e3d8 2024-04-09)

[abonander]

This is actually a well defined type conversion according to SQLite (scroll down for the conversion table): https://www.sqlite.org/c3ref/column_blob.html

Normally, we don't explicitly implement type conversions, because that should be handled in the database itself. For the MySQL or Postgres driver, this would be an error. However, since the SQLite API functions implement this conversion internally, it's actually more work for us to prevent the conversion, so it was just omitted.

If you use the checked Row::get() or ::try_get() methods, this will panic or error, respectively, but the FromRow derive uses ::try_get_unchecked() to avoid an extra branch on decoding the value. This also allows conversion between types that are binary-compatible but not considered compatible in the API.

I'm not particularly married to any of these decisions, but there's a high likelihood that there are people out there who depend on the current behavior.

[msberk]

Oof, fair enough. I didn’t know this was inherent behavior of SQLite, and I think it really caught me off guard since the behavior is a significant departure from how most other Rust deserializations I have used work and figured since the SQLx macros disallowed the mismatch it was an unintended inconsistency.

I’m not going to fight to have this changed though I might ask that the docs are updated to specifically note that FromRow derivations when using SQLite will not Err if a NULL value on a required field is encountered. That would be helpful, I got spun around on this for a few hours.

Thanks for explaining what was going on so well.