Inserting a struct into an sqlite db using sqlx and rust

Question

I have a question regarding inserting a data structure into a db and I can't seem to find any documentation on.

I have a data structure

#[derive(FromRow, Getters, Default, Serialize, Deserialize, Debug)]
#[serde(crate = "rocket::serde")]
#[getset(get = "pub")]
pub struct RefreshKeys {
    id: i64,
    customer_id: i64,
    key: String,
    enabled: bool,
}

I want to insert this into a db with the same fields, called refresh_keys.

rocket_db_pools::sqlx::query_as::<_, RefreshKeys>(
  "INSERT INTO refresh_keys (id, customer_id, key, enabled)
   VALUES (?1, ?2, ?3, ?4)"
)
.fetch_one(&mut *db)
.await?

This sadly does not seem to work, i get the following error:

SqliteError { code: 1299, message: "NOT NULL constraint failed: refresh_keys.customer_id" }

I have tried for hours to find the relevant documentation, but I just can't find anything.

Thanks in advance!

Answer 1

The error message you're seeing seems to indicate that the data associated with the parameters is not being passed correctly (?1...?4), as this would result in the values being passed as NULLs.

• Not being very familiar with SQLite, my first thought was that your parameters should be named $1..$N, but it looks like SQLite does allow the ?NNN syntax per the documentation. So let's move on...

Next try looking closely at the query where you need to bind values to your parameters; I'm not seeing where you are passing the values to the query, for example, we could create a row of data & insert it into the database as follows. Note the bind() calls to bind the values from the data variable to the parameters in the query.

// Create new row to insert:
    let data = RefreshKeys {
        id: 1,
        customer_id: 1,
        key: "test".to_string(),
        enabled: true,
    };
// Now insert the row:
    let result = sqlx::query(
        "INSERT INTO refresh_keys (id, customer_id, key, enabled)
        VALUES ($1, $2, $3, $4)")
        .bind(data.id)
        .bind(data.customer_id)
        .bind(data.key)
        .bind(data.enabled)
        .execute(&pool).await;

Without a minimal working example, it's a bit difficult to help you further, but see if the below example helps out at all.

Working Minimal Example

Cargo.toml:

[dependencies]
sqlx = { version = "0.6.3", features = ["sqlite", "macros", "runtime-tokio-rustls"] }
tokio = { version = "1.27.0", features = ["macros"] }

src/main.rs:

use sqlx::{Sqlite, SqlitePool, migrate::MigrateDatabase};

#[derive(sqlx::FromRow)]
struct RefreshKeys {
    id: i32,
    customer_id: i64,
    key: String,
    enabled: bool,
}

const DATABASE_URL: &str = "sqlite://db.sqlite";

#[tokio::main]
async fn main() {

    // Create database if not exists:
    if !Sqlite::database_exists(DATABASE_URL).await.unwrap_or(false) {
        match Sqlite::create_database(DATABASE_URL).await {
            Ok(_) => println!("Database created"),
            Err(e) => println!("Error creating database: {}", e),
        }
    } else {
        println!("Database already exists");
    }

    // Connect to database:
    let pool = SqlitePool::connect(DATABASE_URL).await.unwrap();

    // Create table (would normally do this in ./migrations/*.sql):
    let result = sqlx::query("CREATE TABLE IF NOT EXISTS refresh_keys (
        id INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL,
        key TEXT NOT NULL,
        enabled BOOLEAN NOT NULL
    )").execute(&pool).await;

    match result {
        Ok(_) => println!("Table created"),
        Err(e) => println!("Error creating table: {}", e),
    }

    // Create new row to insert:
    let data = RefreshKeys {
        id: 1,
        customer_id: 1,
        key: "test".to_string(),
        enabled: true,
    };

    let result = sqlx::query(
        "INSERT INTO refresh_keys (id, customer_id, key, enabled)
        VALUES ($1, $2, $3, $4)")
        .bind(data.id)
        .bind(data.customer_id)
        .bind(data.key)
        .bind(data.enabled)
        .execute(&pool).await;

    match result {
        Ok(_) => println!("Row inserted"),
        Err(e) => println!("Error inserting row: {}", e),
    }

    // Select row in database:
    let keys = sqlx::query_as::<_, RefreshKeys>(
        "SELECT * FROM refresh_keys"
        ).fetch_all(&pool).await;

    let key_vec = match keys {
        Ok(keys) => keys,
        Err(e) => {
            println!("Error selecting row: {}", e);
            return;
        }
    };

    for key in key_vec {
        println!("id={}, customer_id={}, key={}, enabled={}",
                 key.id,
                 key.customer_id,
                 key.key,
                 key.enabled);
    }

}
:q