Insert doesn't fail but doesn't insert for a specific table [SQLite]

[Goby56]

I've been at this problem all day and can't find what is wrong.

The problem involves two tables, a User table and a Session table. These are their schemas:

CREATE TABLE "User" (
    "id"    INTEGER NOT NULL UNIQUE,
    "username"  TEXT NOT NULL UNIQUE,
    "password_hash" TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE "Session" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "secret_hash" TEXT NOT NULL,
    "created_at" INTEGER NOT NULL,
    "user_id" INTEGER NOT NULL,
    FOREIGN KEY("user_id") REFERENCES "User"("id")
);

I can create a new user without problems but when trying to create a session it just doesn't work. The query I am using doesn't throw any errors but the SqliteQueryResult says there is one affected row. When inspecting the DB either using a program or in the terminal I can see a user was created but no session. What could be causing this?

Here is the code:

pub async fn create_session(pool: &SqlitePool, user_id: u32) -> Result<Option<(Session, String)>, sqlx::Error> {
    let now = OffsetDateTime::now_utc().unix_timestamp(); 
    let (id, secret) = match (gen_secure_random_str(), gen_secure_random_str()) {
        (Some(id), Some(secret)) => (id, secret),
        _ => return Ok(None)
    };
    let secret_hash = hex::encode(Sha256::digest(secret.clone()));

    let token = id.clone() + "." + &secret;

    let session = Session {
        id: id.clone(), secret_hash: secret_hash.clone(), created_at: now, user_id
    };
    
    let result = sqlx::query("
        INSERT INTO Session (id, secret_hash, created_at, user_id)
        VALUES (?, ?, ?, ?)").bind(id).bind(secret_hash).bind(now).bind(user_id)
        .execute(pool).await?; // No error is thrown
    println!("Tried inserting session with {} affected rows", result.rows_affected()); 
    // 1 affected row
    // But nothing is inserted in the database
    return Ok(Some((session, token)));
}

I've tried countless things and the worst part is that it did work at one point in time. I've even asked ChatGPT which gave me some stuff to try:

Database file mismatch

Problem: Maybe the database my application is using isn't the same as the one I am inspecting using external tools.

I highly doubt this is the case as the user table shows up perfectly fine but this is how I declare my database:

async fn init_database() -> Result<Pool<Sqlite>, sqlx::Error> {
    let database_url = env::var("DATABASE_URL").unwrap();

    let db_options = SqliteConnectOptions::from_str(&database_url)?
        .create_if_missing(true)
        .extension("backend/spellfix1");

    let pool = SqlitePool::connect_with(db_options).await?;

    sqlx::migrate!("./migrations").run(&pool).await?;

    return Ok(pool);
}

The environment variable is DATABASE_URL=sqlite://backend/db/db.sqlite and the relevant file structure is:

project/
├─ backend/
│  ├─ db/
│  │  ├─ db.sqlite
│  ├─ src/
│  │  ├─ main.rs
│  ├─ migrations/

Foreign key pragma not being set

ChatGPT did bring up a lot about PRAGMA foreign_keys = ON not being set but I know the user_id column in Session is referencing a valid user so I can't see why that would be the problem.

Nevertheless I tried setting it as a pragma during initialization but it doesn't seem to work. I've tried both enabling it as a connection option and setting it as a normal query before migration. When later checking if it is available using PRAGMA foreign_keys; it returns 0 but in the GUI tool it seems to be checked as enabled. So I don't now what is going on.

---

Help would greatly be appreaciated as I can't move forward with this project with out this and I've been stuck on it all day. There may be just something simple I am missing but I fail to see it. Please tell me if you need more to go on and I will provide additional info!

[Goby56]

.....

ok I found it. I was immediately removing the row as I had a if statement flipped to delete the session if it had expired. Basically, I was using a greater than equal instead of less than.

Sorry for opening.