Query bypassing SQLx compile-time checks for missing NOT NULL column

Background

I've recently added a new required column to my database but I'm noticing that my SQLx query is still compiling successfully despite not including this column. Here's what I've done:

1. Added a migration that adds a NOT NULL column to my table:

ALTER TABLE task
ADD COLUMN user_id uuid NOT NULL;

ALTER TABLE task
ADD CONSTRAINT fk_task_users FOREIGN KEY (user_id)
REFERENCES users(user_id);

2. Restarted my database to ensure changes are applied.

3. My query still compiles despite not including the new required column:

#[instrument]
pub async fn create_new_task(pool: &PgPool, title: &str, description: &str) -> Result<()> {
    sqlx::query!(
        r#"
        INSERT INTO task (title, description)
        VALUES ($1, $2)
        "#,
        title,
        description
    )
    .execute(pool)
    .await?;
    Ok(())
}

Expected Behavior

I expected SQLx to produce a compile-time error because my query doesn't include the new user_id column that's marked as NOT NULL in the database schema.

Actual Behavior

The code compiles successfully but fails at runtime with a PostgreSQL error about violating the not-null constraint.

Environment Details

• SQLx version: 0.8
• Database: PostgreSQL 14
• Rust version: 1.85
• Dependencies from Cargo.toml related to SQLx: sqlx = { version = "0.8.3", features = ["runtime-tokio-native-tls", "postgres", "macros", "uuid", "time"] }

Questions

1. Why isn't SQLx catching this missing required column at compile time?
2. Is there a configuration I need to enable to ensure these kinds of errors are caught during compilation?
3. Could this indicate an issue with how my database connection is set up for compile-time checking?

Was sort of counting on SQLx to catch these kinds of mistakes at compile time, maybe I'm missing something but I've already tried a bunch of things.

Things I've Tried

1. Restarted the database and ran all the migrations again
2. sqlx db setup
3. Restarted Rust-Analyzer
4. Also did a cargo sqlx prepare and tried it in offline mode after having fixed the query to not error

Query bypassing SQLx compile-time checks for missing NOT NULL column

Background

I've recently added a new required column to my database but I'm noticing that my SQLx query is still compiling successfully despite not including this column. Here's what I've done:

1. Added a migration that adds a NOT NULL column to my table:

ALTER TABLE task
ADD COLUMN user_id uuid NOT NULL;

ALTER TABLE task
ADD CONSTRAINT fk_task_users FOREIGN KEY (user_id)
REFERENCES users(user_id);

2. Restarted my database to ensure changes are applied.

3. My query still compiles despite not including the new required column:

#[instrument]
pub async fn create_new_task(pool: &PgPool, title: &str, description: &str) -> Result<()> {
    sqlx::query!(
        r#"
        INSERT INTO task (title, description)
        VALUES ($1, $2)
        "#,
        title,
        description
    )
    .execute(pool)
    .await?;
    Ok(())
}

Expected Behavior

I expected SQLx to produce a compile-time error because my query doesn't include the new user_id column that's marked as NOT NULL in the database schema.

Actual Behavior

The code compiles successfully but fails at runtime with a PostgreSQL error about violating the not-null constraint.

Environment Details

• SQLx version: 0.8
• Database: PostgreSQL 14
• Rust version: 1.85
• Dependencies from Cargo.toml related to SQLx: sqlx = { version = "0.8.3", features = ["runtime-tokio-native-tls", "postgres", "macros", "uuid", "time"] }

Questions

1. Why isn't SQLx catching this missing required column at compile time?
2. Is there a configuration I need to enable to ensure these kinds of errors are caught during compilation?
3. Could this indicate an issue with how my database connection is set up for compile-time checking?

Was sort of counting on SQLx to catch these kinds of mistakes at compile time, maybe I'm missing something but I've already tried a bunch of things.

Things I've Tried

1. Restarted the database and ran all the migrations again
2. sqlx db setup
3. Restarted Rust-Analyzer
4. Also did a cargo sqlx prepare and tried it in offline mode after having fixed the query to not error

• postgresql
• rust
• rust-sqlx

Share Follow asked Mar 3 at 22:06 BalaaaBalaaa 2111 silver badge11 bronze badge 2

• 1 "Restarted my database to ensure changes are applied." You do not need to do this. SQL tables are meant to be altered while the database continues to run. – Schwern Commented Mar 3 at 22:13
• Constraint checks are a SQL runtime error. See my revised answer. – Schwern Commented Mar 4 at 0:48

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Why isn't SQLx catching this missing required column at compile time?

Because it is a valid query. not null is a constraint, and constraints are not evaluated until runtime. The statement can be prepared and executed, the constraint error is raised while the statement is executing.

For example, consider the case where task has a trigger to add a user_id if none is given.

create or replace function add_user_id() 
returns trigger 
as $$
declare uuid uuid;
begin
  if NEW.user_id is null then
    uuid = gen_random_uuid();
    insert into users(user_id) values(uuid);
    NEW.user_id = uuid;
  end if;
  
  return NEW;
end $$
language plpgsql;

create trigger user_id_fill 
  before insert 
  on task 
  for each row 
  execute procedure add_user_id();

Now your query will execute.

Demonstration.