Using Jiff with SQLx

[NuclEnergy]

Using Jiff with SQLx

This guide uses PostgreSQL TIMESTAMPTZ mapped to jiff::Timestamp as the example, and explains how to use Jiff with SQLx 0.9.

Key Idea

jiff-sqlx does not implement SQLx's Type, Encode, and Decode traits directly for jiff::Timestamp. Instead, it provides wrapper types, such as:

jiff_sqlx::Timestamp

That means there are usually two usage patterns:

• For SQLx macros such as query_as!, configure type overrides in sqlx.toml.
• For query_as() / FromRow, specify #[sqlx(try_from = "...")] on the field.

1. Add Dependencies

[dependencies]
sqlx = { version = "0.9.0", features = ["postgres", "macros", "sqlx-toml"] }
jiff = { version = "0.2.31", features = ["serde"] }
jiff-sqlx = { version = "0.2.0", features = ["postgres"] }
serde = { version = "1.0.228", features = ["derive"] }

Notes:

• sqlx/postgres and jiff-sqlx/postgres enable PostgreSQL support.
• sqlx/macros enables compile-time checked macros such as query! and query_as!.
• sqlx/sqlx-toml allows SQLx macros to read sqlx.toml.
• serde and jiff/serde are only needed if your structs need serialization or deserialization. They are not required for SQLx to decode timestamp values.

2. Create sqlx.toml

Create sqlx.toml next to the Cargo.toml of the crate that uses SQLx macros:

[macros.type-overrides]
'DATE' = 'jiff_sqlx::Date'
'TIME' = 'jiff_sqlx::Time'
'TIMESTAMP' = 'jiff_sqlx::DateTime'
'TIMESTAMPTZ' = 'jiff_sqlx::Timestamp'
# Note: only decoding is supported.
'INTERVAL' = 'jiff_sqlx::Span'

In a workspace, put sqlx.toml in the crate that invokes the SQLx macros, not necessarily at the workspace root.

SQLx macros read configuration from:

$CARGO_MANIFEST_DIR/sqlx.toml

3. Using query_as!

When using the query_as! macro, your struct field can use jiff::Timestamp directly:

struct User {
    id: i64,
    created_at: jiff::Timestamp,
}

let user = sqlx::query_as!(
    User,
    "SELECT id, created_at FROM users WHERE id = $1",
    user_id,
)
.fetch_one(&pool)
.await?;

Here, created_at is a PostgreSQL TIMESTAMPTZ column. Because sqlx.toml contains:

'TIMESTAMPTZ' = 'jiff_sqlx::Timestamp'

the SQLx macro decodes the column as jiff_sqlx::Timestamp, then calls .into() when assigning it to the jiff::Timestamp field.

Important: query_as! does not use FromRow. It generates struct literal initialization code, so this path does not require #[derive(sqlx::FromRow)].

4. Using query_as() / FromRow

If you use the non-macro form:

let user: User = sqlx::query_as("SELECT id, created_at FROM users WHERE id = $1")
    .bind(user_id)
    .fetch_one(&pool)
    .await?;

then SQLx uses FromRow. If the struct is written like this:

#[derive(sqlx::FromRow)]
struct User {
    id: i64,
    created_at: jiff::Timestamp,
}

you will usually get an error similar to:

the trait bound jiff::Timestamp: sqlx::Decode<'_, Postgres> is not satisfied

The reason is that Decode<Postgres> is implemented for jiff_sqlx::Timestamp, not directly for jiff::Timestamp.

The fix is to specify the intermediate decoded type on the field:

#[derive(sqlx::FromRow)]
struct User {
    id: i64,
    #[sqlx(try_from = "jiff_sqlx::Timestamp")]
    created_at: jiff::Timestamp,
}

This means:

1. SQLx decodes the database value as jiff_sqlx::Timestamp.
2. SQLx then calls TryFrom<jiff_sqlx::Timestamp> to convert it into the field type, jiff::Timestamp.

5. Why Does try_from Not Actually Fail?

jiff-sqlx already implements conversion from its wrapper type to the corresponding Jiff type:

impl From<jiff_sqlx::Timestamp> for jiff::Timestamp

The Rust standard library provides a blanket implementation for infallible conversions:

impl<T, U> TryFrom<U> for T
where
    U: Into<T>,
{
    type Error = Infallible;

    fn try_from(value: U) -> Result<Self, Self::Error> {
        Ok(U::into(value))
    }
}

So once jiff_sqlx::Timestamp: Into<jiff::Timestamp> is true, jiff::Timestamp automatically has:

TryFrom<jiff_sqlx::Timestamp, Error = Infallible>

In other words, #[sqlx(try_from = "jiff_sqlx::Timestamp")] is just the current interface exposed by SQLx's FromRow derive. Semantically, this timestamp conversion is not actually fallible.

6. Which One Should You Use?

If your query can be written as static SQL, prefer query_as!:

let user = sqlx::query_as!(
    User,
    "SELECT id, created_at FROM users WHERE id = $1",
    user_id,
)
.fetch_one(&pool)
.await?;

If you need dynamic SQL, or if you need to reuse FromRow, add try_from on the timestamp field:

#[derive(sqlx::FromRow)]
struct User {
    id: i64,
    #[sqlx(try_from = "jiff_sqlx::Timestamp")]
    created_at: jiff::Timestamp,
}

Both approaches have the same goal: let SQLx use jiff_sqlx::Timestamp for database encoding and decoding, while your application code continues to use jiff::Timestamp.