SQLx Error Handling and Unique Violation Detection

Turn a PostgreSQL unique violation into a friendly conflict instead of a 500. Check is_unique_violation on the database error, read the constraint name and map foreign key errors too.

3 min read

Summarize in AI

Quick Summary

Match sqlx::Error::Database(db_err) and call db_err.is_unique_violation() to detect a duplicate key, is_foreign_key_violation() for a missing parent, and db_err.constraint() to know which rule fired. Convert these to domain errors such as EmailTaken, and map unknown database errors to a 500 without leaking their text.

Key takeaways

SQLx wraps server errors in sqlx::Error::Database, which exposes the SQLSTATE code, message and constraint name.
Relying on a unique constraint is safer than checking first with a SELECT, because the check races with concurrent inserts.
constraint() lets you tell which unique index failed when a table has several.
Other typed errors: RowNotFound for fetch_one, PoolTimedOut, and Io for connection failures.
In a PostgreSQL transaction, any error aborts it, so roll back before continuing.

Two people sign up with the same email at the same moment. Both pass the "is it taken" check, one insert wins, and the other gets a database error. If the code treats that as a generic failure, the user sees a 500 for what is an ordinary conflict. The fix is to let the constraint do the checking and translate the error. This post shows how. It belongs with SQLx explained and the web side of it in Axum error handling.

sqlx 0.8, PostgreSQL 17, Rust 1.90.

The schema

SQL

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    CONSTRAINT users_email_key UNIQUE (email)
);

Naming the constraint gives you a stable string to match on, instead of the generated default.

Detect and translate

rust

use sqlx::PgPool;

#[derive(Debug, thiserror::Error)]
pub enum CreateUserError {
    #[error("that email is already registered")]
    EmailTaken,
    #[error("database error")]
    Db(#[from] sqlx::Error),
}

pub async fn create_user(pool: &PgPool, email: &str) -> Result<i64, CreateUserError> {
    let result = sqlx::query_scalar::<_, i64>("INSERT INTO users (email) VALUES ($1) RETURNING id")
        .bind(email)
        .fetch_one(pool)
        .await;

    match result {
        Ok(id) => Ok(id),
        Err(sqlx::Error::Database(db))
            if db.is_unique_violation() && db.constraint() == Some("users_email_key") =>
        {
            Err(CreateUserError::EmailTaken)
        }
        Err(e) => Err(e.into()),
    }
}

The match guard checks both the kind and the constraint, so a different unique index still surfaces as a database error. The web handler maps EmailTaken to 409 Conflict. The Display text of the other branch is generic on purpose.

Why not check first

rust

// racy
if sqlx::query("SELECT 1 FROM users WHERE email = $1").bind(email).fetch_optional(pool).await?.is_some() { ... }

Between the SELECT and the INSERT, another request can insert the same value. The constraint is the only reliable arbiter. You can still do the cheap check for a nicer message, but you must handle the constraint error as well.

Other useful checks

Method	Meaning
`is_unique_violation()`	duplicate key
`is_foreign_key_violation()`	parent row missing, or child rows exist on delete
`is_check_violation()`	a `CHECK` constraint failed
`kind()`	the `ErrorKind` enum including `NotNullViolation`
`code()`	the raw SQLSTATE string, for rare cases

RowNotFound comes from fetch_one with no row, and is better avoided with fetch_optional, covered in mapping rows with query_as.

Inside a transaction

PostgreSQL marks the whole transaction as failed after the error. Either roll back and retry, or wrap the risky insert in a savepoint (let mut sp = tx.begin().await?;) so that dropping the savepoint rolls back only that part. See transactions.

Upserts as an alternative

INSERT ... ON CONFLICT (email) DO NOTHING RETURNING id returns no row when the key existed, with no error to catch. Use it when "already exists" is not a failure for the caller. If you need to know, the error path above is clearer.

Limits

Constraint names differ between databases and migration tools, so keep them in a constant next to the schema. MySQL and SQLite report unique violations too, but with different codes; the helper methods hide that. The trait is documented at DatabaseError.

Also inRust

Taggederrors sqlx