laterite_core/
query.rs

1//! Portable parameterised queries over `sqlx::Any`.
2//!
3//! `sea-query` builds the SQL and an ordered list of values for the running
4//! backend; [`bind_values`] binds those values to a `sqlx::Any` query. Only
5//! portable value kinds are supported (bool, integers, floats, text, bytes), so
6//! backend-specific types (timestamps, JSON) are represented as text or integers
7//! at the query boundary and converted in Rust.
8
9use sqlx::any::{AnyArguments, AnyRow};
10use sqlx::{Any, Encode, Row, Type};
11
12use crate::migration::DbBackend;
13use crate::Db;
14
15/// Row helpers for portably stored types.
16pub trait AnyRowExt {
17    /// Reads a boolean stored as a 0/1 integer (the portable representation, see
18    /// [`bind_values`]). The counterpart to binding a `bool`.
19    fn get_bool(&self, column: &str) -> Result<bool, sqlx::Error>;
20
21    /// Reads a text column as a `String`, portably. MySQL reports `TEXT` columns
22    /// as `BLOB` through `sqlx::Any`, so a plain `String` decode fails there; this
23    /// falls back to reading the bytes and decoding UTF-8. Use for every text read
24    /// so the same code works on every backend.
25    fn get_text(&self, column: &str) -> Result<String, sqlx::Error>;
26
27    /// The nullable counterpart to [`get_text`], for a `text` column that may be
28    /// `NULL`.
29    fn get_text_opt(&self, column: &str) -> Result<Option<String>, sqlx::Error>;
30}
31
32impl AnyRowExt for AnyRow {
33    fn get_bool(&self, column: &str) -> Result<bool, sqlx::Error> {
34        Ok(self.try_get::<i32, _>(column)? != 0)
35    }
36
37    fn get_text(&self, column: &str) -> Result<String, sqlx::Error> {
38        match self.try_get::<String, _>(column) {
39            Ok(s) => Ok(s),
40            Err(_) => decode_utf8(column, self.try_get::<Vec<u8>, _>(column)?),
41        }
42    }
43
44    fn get_text_opt(&self, column: &str) -> Result<Option<String>, sqlx::Error> {
45        match self.try_get::<Option<String>, _>(column) {
46            Ok(s) => Ok(s),
47            Err(_) => self
48                .try_get::<Option<Vec<u8>>, _>(column)?
49                .map(|b| decode_utf8(column, b))
50                .transpose(),
51        }
52    }
53}
54
55/// Decodes bytes read from a text-typed column (the MySQL `BLOB` fallback path)
56/// as UTF-8, surfacing a bad encoding as a column-decode error.
57fn decode_utf8(column: &str, bytes: Vec<u8>) -> Result<String, sqlx::Error> {
58    String::from_utf8(bytes).map_err(|e| sqlx::Error::ColumnDecode {
59        index: column.to_string(),
60        source: Box::new(e),
61    })
62}
63
64/// A portable "insert, ignore duplicates" conflict clause for `keys` (the unique
65/// or primary-key columns of the conflict). Use in place of
66/// `OnConflict::columns(keys).do_nothing()`: sea-query renders MySQL's
67/// `DO NOTHING` as invalid SQL (`ON DUPLICATE KEY IGNORE`), so this expresses the
68/// same intent as a no-op update of the first key column, which is valid on
69/// Postgres, MySQL, and SQLite alike.
70pub fn on_conflict_ignore<C>(keys: impl IntoIterator<Item = C>) -> sea_query::OnConflict
71where
72    C: sea_query::IntoIden,
73{
74    use sea_query::IntoIden;
75    let keys: Vec<sea_query::DynIden> = keys.into_iter().map(IntoIden::into_iden).collect();
76    let first = keys[0].clone();
77    sea_query::OnConflict::columns(keys)
78        .update_column(first)
79        .to_owned()
80}
81
82/// The SQL type to cast a column to when you need its value back as a string on
83/// any backend. Descriptor-driven screens cast every selected column to a string
84/// so a value of any type reads back uniformly through `sqlx::Any`. MySQL rejects
85/// `CAST(x AS text)` (it casts to `char`), while Postgres and SQLite use `text`.
86/// Use as `Expr::col(c).cast_as(sea_query::Alias::new(text_cast(backend)))`.
87pub fn text_cast(backend: DbBackend) -> &'static str {
88    match backend {
89        DbBackend::Mysql => "char",
90        DbBackend::Postgres | DbBackend::Sqlite => "text",
91    }
92}
93
94/// Renders a `sea-query` statement to `(sql, values)` for `backend`.
95///
96/// The statement is taken by value and dropped here, so no `sea-query` builder
97/// (which holds non-`Send` reference-counted identifiers) survives into the
98/// caller's `.await`. That keeps request handlers' futures `Send`, as the async
99/// runtime requires. Construct the statement, hand it here, then bind and run
100/// the returned owned `(sql, values)`.
101pub fn build<S>(backend: DbBackend, stmt: S) -> (String, sea_query::Values)
102where
103    S: sea_query::QueryStatementWriter,
104{
105    match backend {
106        DbBackend::Postgres => stmt.build(sea_query::PostgresQueryBuilder),
107        DbBackend::Mysql => stmt.build(sea_query::MysqlQueryBuilder),
108        DbBackend::Sqlite => stmt.build(sea_query::SqliteQueryBuilder),
109    }
110}
111
112/// Runs an insert and returns the generated auto-increment id, portably.
113///
114/// The id column is `bigint auto_increment` on every backend, but reading the
115/// new id back is not uniform: Postgres exposes no last-insert-id, so its
116/// statement is given a `RETURNING` clause and the id is read from the returned
117/// row; MySQL and SQLite report it through the driver after a plain execute.
118/// `id` names the auto-increment column (used only for the Postgres `RETURNING`).
119//
120// This is a synchronous function returning a future, not an `async fn`: an
121// `async fn` captures all its parameters into the future from the moment it is
122// created, so taking the non-`Send` `sea-query` builder by value would make the
123// future non-`Send` regardless of when the body drops it. Rendering the builder
124// to owned `Send` data first, then capturing only that into an `async move`
125// block, keeps the returned future `Send` as request handlers require.
126pub fn insert_returning_id<I>(
127    db: &Db,
128    stmt: sea_query::InsertStatement,
129    id: I,
130) -> impl std::future::Future<Output = Result<i64, sqlx::Error>> + Send + '_
131where
132    I: sea_query::IntoIden + 'static,
133{
134    let (sql, values, returning) = render_insert(db.backend, stmt, id);
135    async move {
136        if returning {
137            bind_values(sqlx::query(&sql), values)
138                .fetch_one(&db.pool)
139                .await?
140                .try_get::<i64, _>(0)
141        } else {
142            bind_values(sqlx::query(&sql), values)
143                .execute(&db.pool)
144                .await?
145                .last_insert_id()
146                .ok_or(sqlx::Error::RowNotFound)
147        }
148    }
149}
150
151/// Renders an insert to `(sql, values, use_returning)`. Postgres and SQLite get a
152/// `RETURNING` clause on `id` (read back with `fetch_one`); MySQL has no portable
153/// `RETURNING`, so it reports the id through the driver's last-insert-id instead.
154fn render_insert<I>(
155    backend: DbBackend,
156    mut stmt: sea_query::InsertStatement,
157    id: I,
158) -> (String, sea_query::Values, bool)
159where
160    I: sea_query::IntoIden + 'static,
161{
162    let returning = matches!(backend, DbBackend::Postgres | DbBackend::Sqlite);
163    if returning {
164        stmt.returning_col(id);
165    }
166    let (sql, values) = build(backend, stmt);
167    (sql, values, returning)
168}
169
170type AnyQuery<'q> = sqlx::query::Query<'q, Any, AnyArguments<'q>>;
171type AnyQueryAs<'q, O> = sqlx::query::QueryAs<'q, Any, O, AnyArguments<'q>>;
172
173fn bind_one<'q, T>(query: AnyQuery<'q>, value: T) -> AnyQuery<'q>
174where
175    T: 'q + Send + Encode<'q, Any> + Type<Any>,
176{
177    query.bind(value)
178}
179
180/// Binds `sea-query` values onto a `sqlx::Any` query, in order. Only portable
181/// value kinds occur here (the framework converts time/JSON to text before
182/// building), so an unsupported kind is a framework bug and panics.
183pub fn bind_values(mut query: AnyQuery<'_>, values: sea_query::Values) -> AnyQuery<'_> {
184    use sea_query::Value;
185    for value in values.0 {
186        query = match value {
187            // Booleans are stored as 0/1 integers everywhere (a SQLite `boolean`
188            // column is not decodable through `sqlx::Any`), so a `bool` value
189            // binds as an integer. Callers write `bool`; storage stays portable.
190            Value::Bool(v) => bind_one(query, v.map(i32::from)),
191            Value::TinyInt(v) => bind_one(query, v.map(i32::from)),
192            Value::SmallInt(v) => bind_one(query, v),
193            Value::Int(v) => bind_one(query, v),
194            Value::BigInt(v) => bind_one(query, v),
195            // `sqlx::Any` has no unsigned column types, so unsigned integers
196            // (sea-query emits these for LIMIT/OFFSET) bind as the next wider
197            // signed integer. The framework never stores unsigned values.
198            Value::TinyUnsigned(v) => bind_one(query, v.map(i32::from)),
199            Value::SmallUnsigned(v) => bind_one(query, v.map(i32::from)),
200            Value::Unsigned(v) => bind_one(query, v.map(i64::from)),
201            Value::BigUnsigned(v) => bind_one(query, v.map(|n| n as i64)),
202            Value::Float(v) => bind_one(query, v),
203            Value::Double(v) => bind_one(query, v),
204            Value::String(v) => bind_one(query, v.map(|b| *b)),
205            Value::Char(v) => bind_one(query, v.map(|c| c.to_string())),
206            Value::Bytes(v) => bind_one(query, v.map(|b| *b)),
207            // Defensive: unreachable under the framework's sea-query features, but
208            // guards against a richer `Value` if a backend feature is unified in.
209            #[allow(unreachable_patterns)]
210            other => panic!("unsupported portable bind value: {other:?}"),
211        };
212    }
213    query
214}
215
216/// Same as [`bind_values`], for a `query_as` mapping rows into `O`.
217pub fn bind_values_as<O>(
218    mut query: AnyQueryAs<'_, O>,
219    values: sea_query::Values,
220) -> AnyQueryAs<'_, O> {
221    use sea_query::Value;
222    for value in values.0 {
223        query = match value {
224            Value::Bool(v) => query.bind(v.map(i32::from)),
225            Value::TinyInt(v) => query.bind(v.map(i32::from)),
226            Value::SmallInt(v) => query.bind(v),
227            Value::Int(v) => query.bind(v),
228            Value::BigInt(v) => query.bind(v),
229            Value::TinyUnsigned(v) => query.bind(v.map(i32::from)),
230            Value::SmallUnsigned(v) => query.bind(v.map(i32::from)),
231            Value::Unsigned(v) => query.bind(v.map(i64::from)),
232            Value::BigUnsigned(v) => query.bind(v.map(|n| n as i64)),
233            Value::Float(v) => query.bind(v),
234            Value::Double(v) => query.bind(v),
235            Value::String(v) => query.bind(v.map(|b| *b)),
236            Value::Char(v) => query.bind(v.map(|c| c.to_string())),
237            Value::Bytes(v) => query.bind(v.map(|b| *b)),
238            #[allow(unreachable_patterns)]
239            other => panic!("unsupported portable bind value: {other:?}"),
240        };
241    }
242    query
243}
244
245#[cfg(test)]
246mod tests {
247    use super::*;
248    use sea_query::{Alias, Expr, Iden, Query};
249    use sqlx::AnyPool;
250
251    #[derive(Iden)]
252    enum Widget {
253        Table,
254        Id,
255        Label,
256        Qty,
257    }
258
259    async fn pool() -> AnyPool {
260        sqlx::any::install_default_drivers();
261        let pool = sqlx::any::AnyPoolOptions::new()
262            .max_connections(1)
263            .connect("sqlite::memory:")
264            .await
265            .unwrap();
266        sqlx::raw_sql(
267            "create table widget (id text primary key, label text not null, qty integer not null)",
268        )
269        .execute(&pool)
270        .await
271        .unwrap();
272        pool
273    }
274
275    #[tokio::test]
276    async fn binds_parameters_on_insert_and_select() {
277        let pool = pool().await;
278        let backend = DbBackend::Sqlite;
279
280        let insert = Query::insert()
281            .into_table(Widget::Table)
282            .columns([Widget::Id, Widget::Label, Widget::Qty])
283            .values_panic(["w-1".into(), "Sprocket".into(), 7.into()])
284            .to_owned();
285        let (sql, values) = build(backend, insert);
286        bind_values(sqlx::query(&sql), values)
287            .execute(&pool)
288            .await
289            .unwrap();
290
291        // Select the label back by a bound id parameter.
292        let select = Query::select()
293            .column(Widget::Label)
294            .from(Widget::Table)
295            .and_where(Expr::col(Widget::Id).eq("w-1"))
296            .to_owned();
297        let (sql, values) = build(backend, select);
298        let label: String = bind_values_as(sqlx::query_as::<_, (String,)>(&sql), values)
299            .fetch_one(&pool)
300            .await
301            .unwrap()
302            .0;
303        assert_eq!(label, "Sprocket");
304
305        // A cast-free count via a bound filter.
306        let count_stmt = Query::select()
307            .expr(Expr::col(Widget::Id).count())
308            .from(Widget::Table)
309            .and_where(Expr::col(Alias::new("qty")).eq(7))
310            .to_owned();
311        let (sql, values) = build(backend, count_stmt);
312        let count: i64 = bind_values_as(sqlx::query_as::<_, (i64,)>(&sql), values)
313            .fetch_one(&pool)
314            .await
315            .unwrap()
316            .0;
317        assert_eq!(count, 1);
318    }
319}
laterite_core/query.rs

laterite_core/
query.rs
