Trait Dialect 

pub trait Dialect:
    Send
    + Sync
    + 'static {
    const AUTO_PK: &'static str;
    const INSERT_IGNORE: &'static str;
    const CONFLICT_NOTHING: &'static str;
    const COLLATE_NOCASE: &'static str;
    const EPOCH_NOW: &'static str;
    const NOW: &'static str;
    const JSON_CAST: &'static str;
    const TIMESTAMPTZ_CAST: &'static str;
    const GREATEST_FN: &'static str;
    const LEAST_FN: &'static str;

    // Required methods
    fn ilike(col: &str) -> String;
    fn epoch_from_col(col: &str) -> String;
    fn select_as_text(col: &str) -> String;
    fn timestamptz_from_epoch(placeholder: &str) -> String;
}

SQL fragments that differ between database backends.

Implemented by zero-sized marker types (Sqlite, Postgres). All associated constants are &'static str for zero-cost usage.

Required Associated Constants

const AUTO_PK: &'static str

Auto-increment primary key DDL fragment.

SQLite: INTEGER PRIMARY KEY AUTOINCREMENT PostgreSQL: BIGSERIAL PRIMARY KEY

const INSERT_IGNORE: &'static str

INSERT OR IGNORE prefix for this backend.

SQLite: INSERT OR IGNORE PostgreSQL: INSERT (pair with CONFLICT_NOTHING suffix)

const CONFLICT_NOTHING: &'static str

Suffix for conflict-do-nothing semantics.

SQLite: empty string (handled by INSERT OR IGNORE prefix) PostgreSQL: ON CONFLICT DO NOTHING

const COLLATE_NOCASE: &'static str

Case-insensitive collation suffix for ORDER BY / WHERE clauses.

SQLite: COLLATE NOCASE PostgreSQL: empty string (use ILIKE or LOWER() instead)

const EPOCH_NOW: &'static str

Current epoch seconds expression.

SQLite: unixepoch('now') PostgreSQL: EXTRACT(EPOCH FROM NOW())::BIGINT

const NOW: &'static str

Current timestamp expression, for direct assignment into a TEXT/TIMESTAMPTZ updated_at-style column (as opposed to Self::EPOCH_NOW, which yields an integer).

SQLite: datetime('now') PostgreSQL: NOW()

const JSON_CAST: &'static str

Cast suffix for a bind parameter carrying pre-serialized JSON text, destined for a JSON-typed column.

sqlx sends string bind parameters as TEXT/VARCHAR. SQLite stores JSON columns as TEXT so no cast is needed. PostgreSQL stores them as JSONB, which requires an explicit cast from the bound TEXT value — otherwise the backend rejects the insert/update with “column is of type jsonb but expression is of type text”. Append this suffix directly after the ? placeholder for that bind position, e.g. format!("VALUES (?{json_cast})", json_cast = ...).

SQLite: empty string PostgreSQL: ::jsonb

const TIMESTAMPTZ_CAST: &'static str

Cast suffix for a bind parameter carrying a pre-formatted timestamp string, destined for a timestamp-typed column.

sqlx sends string bind parameters as TEXT/VARCHAR. SQLite stores timestamp columns as TEXT so no cast is needed. PostgreSQL stores them as TIMESTAMPTZ, which has no implicit cast from TEXT — binding a plain string fails with PgDatabaseError 42804 (“column is of type timestamptz but expression is of type text”). Append this suffix directly after the ? placeholder for that bind position, e.g. format!("VALUES (?{timestamptz_cast})", timestamptz_cast = ...).

SQLite: empty string PostgreSQL: ::timestamptz

const GREATEST_FN: &'static str

Scalar function name returning the greatest of two (or more) numeric arguments.

SQLite’s max(a, b, ...) is a scalar multi-argument function. PostgreSQL’s MAX() is exclusively an aggregate (requires GROUP BY) — the scalar equivalent is GREATEST(a, b, ...). Use this constant when building a two-argument “largest of” expression that must work as a plain scalar call, not an aggregate.

SQLite: MAX PostgreSQL: GREATEST

const LEAST_FN: &'static str

Scalar function name returning the least of two (or more) numeric arguments.

SQLite’s min(a, b, ...) is a scalar multi-argument function. PostgreSQL’s MIN() is exclusively an aggregate (requires GROUP BY) — the scalar equivalent is LEAST(a, b, ...). Use this constant when building a two-argument “smallest of” expression that must work as a plain scalar call, not an aggregate.

SQLite: MIN PostgreSQL: LEAST

Required Methods

fn ilike(col: &str) -> String

Case-insensitive comparison expression for a column.

SQLite: {col} COLLATE NOCASE PostgreSQL: LOWER({col})

fn epoch_from_col(col: &str) -> String

Epoch seconds expression for a timestamp column.

Wraps the column in the backend-specific function that converts a stored timestamp to a Unix epoch integer, coalescing NULL to 0.

SQLite: COALESCE(CAST(strftime('%s', {col}) AS INTEGER), 0) PostgreSQL: COALESCE(CAST(EXTRACT(EPOCH FROM {col}) AS BIGINT), 0)

fn select_as_text(col: &str) -> String

Project a non-TEXT column so it decodes into a plain String.

SQLite is dynamically typed and stores JSON/timestamp columns as TEXT, so the column already decodes into String directly — no cast needed. PostgreSQL stores the same logical data in natively-typed columns (JSONB, TIMESTAMPTZ, …); decoding those straight into String fails (sqlx’s String: Decode<Postgres> only covers TEXT-family OIDs), so the column must be cast to ::text in the SELECT list for call sites that only need the raw text representation (rather than a typed decode via sqlx::types::Json<T> or chrono::DateTime).

SQLite: {col} PostgreSQL: {col}::text

fn timestamptz_from_epoch(placeholder: &str) -> String

Timestamp expression for binding a Unix epoch-seconds value into a TEXT/TIMESTAMPTZ compacted_at-style column.

Wraps the given bind-parameter placeholder (e.g. ?, later rewritten to $N by crate::rewrite_placeholders) in the backend-specific conversion from Unix epoch seconds to the column’s native timestamp representation.

SQLite stores such columns as bare epoch-seconds TEXT, so the placeholder passes through unchanged. PostgreSQL stores them as TIMESTAMPTZ; unlike an ISO-8601 string, a bare epoch-seconds string has no valid timestamptz input syntax — '1735999999'::timestamptz fails to parse even with Self::TIMESTAMPTZ_CAST appended. The bound value must instead be routed through to_timestamp(), which both performs the epoch conversion and yields a TIMESTAMPTZ directly, so no additional cast is needed on the result.

The placeholder itself still needs an explicit ::double precision cast: callers typically bind a Rust String/&str (the value is formatted with format!("{secs}") before binding), which sqlx sends with the TEXT type OID. to_timestamp() has only to_timestamp(double precision) and to_timestamp(text, text) overloads — there is no implicit text -> double precision cast, so an unqualified to_timestamp({placeholder}) fails function-argument resolution with ERROR 42883: function to_timestamp(text) does not exist. The ::double precision cast on the placeholder is what makes the bound text resolve to the numeric overload.

SQLite: {placeholder} PostgreSQL: to_timestamp({placeholder}::double precision)

Dyn Compatibility

This trait is not dyn compatible.

In older versions of Rust, dyn compatibility was called "object safety".

Implementors

impl Dialect for Postgres

const AUTO_PK: &'static str = "BIGSERIAL PRIMARY KEY"

const INSERT_IGNORE: &'static str = "INSERT"

const CONFLICT_NOTHING: &'static str = "ON CONFLICT DO NOTHING"

const COLLATE_NOCASE: &'static str = ""

const EPOCH_NOW: &'static str = "EXTRACT(EPOCH FROM NOW())::BIGINT"

const NOW: &'static str = "NOW()"

const JSON_CAST: &'static str = "::jsonb"

const TIMESTAMPTZ_CAST: &'static str = "::timestamptz"

const GREATEST_FN: &'static str = "GREATEST"

const LEAST_FN: &'static str = "LEAST"

impl Dialect for Sqlite

const AUTO_PK: &'static str = "INTEGER PRIMARY KEY AUTOINCREMENT"

const INSERT_IGNORE: &'static str = "INSERT OR IGNORE"

const CONFLICT_NOTHING: &'static str = ""

const COLLATE_NOCASE: &'static str = "COLLATE NOCASE"

const EPOCH_NOW: &'static str = "unixepoch('now')"

const NOW: &'static str = "datetime('now')"

const JSON_CAST: &'static str = ""

const TIMESTAMPTZ_CAST: &'static str = ""

const GREATEST_FN: &'static str = "MAX"

const LEAST_FN: &'static str = "MIN"