spin_sdk/
mysql.rs

1//! MySQL relational database storage.
2//!
3//! You can use the [`Decode`] trait to convert a [`DbValue`] to a
4//! suitable Rust type. The following table shows available conversions.
5//!
6//! # Types
7//!
8//! | Rust type | WIT (db-value)      | MySQL type(s)           |
9//! |-----------|---------------------|-------------------------|
10//! | `bool`    | int8(s8)            | TINYINT(1), BOOLEAN     |
11//! | `i8`      | int8(s8)            | TINYINT                 |
12//! | `i16`     | int16(s16)          | SMALLINT                |
13//! | `i32`     | int32(s32)          | MEDIUM, INT             |
14//! | `i64`     | int64(s64)          | BIGINT                  |
15//! | `u8`      | uint8(u8)           | TINYINT UNSIGNED        |
16//! | `u16`     | uint16(u16)         | SMALLINT UNSIGNED       |
17//! | `u32`     | uint32(u32)         | INT UNSIGNED            |
18//! | `u64`     | uint64(u64)         | BIGINT UNSIGNED         |
19//! | `f32`     | floating32(float32) | FLOAT                   |
20//! | `f64`     | floating64(float64) | DOUBLE                  |
21//! | `String`  | str(string)         | VARCHAR, CHAR, TEXT     |
22//! | `Vec<u8>` | binary(list\<u8\>)  | VARBINARY, BINARY, BLOB |
23
24use crate::wit_bindgen;
25use std::sync::Arc;
26
27#[doc(hidden)]
28/// Module containing wit bindgen generated code.
29///
30/// This is only meant for internal consumption.
31pub mod wit {
32    #![allow(missing_docs)]
33    use crate::wit_bindgen;
34
35    wit_bindgen::generate!({
36        runtime_path: "crate::wit_bindgen::rt",
37        world: "spin-sdk-mysql-v3",
38        path: "wit",
39        generate_all,
40    });
41
42    pub use spin::mysql::mysql;
43}
44
45/// An open connection to a MySQL database.
46///
47/// # Examples
48///
49/// Load a set of rows from a local MySQL database, and iterate over them.
50///
51/// ```no_run
52/// use spin_sdk::mysql::{Connection, Decode, ParameterValue};
53///
54/// # async fn run() -> anyhow::Result<()> {
55/// # let min_age = 0;
56/// let db = Connection::open("mysql://root:my_password@localhost/mydb").await?;
57///
58/// let mut query_result = db.query(
59///     "SELECT * FROM users WHERE age >= ?",
60///     &[min_age.into()]
61/// ).await?;
62///
63/// while let Some(row) = query_result.next().await {
64///     let name = row.get::<String>("name").unwrap();
65///     println!("Found user {name}");
66/// }
67///
68/// query_result.result().await?;
69/// # Ok(())
70/// # }
71/// ```
72///
73/// Perform an aggregate (scalar) operation over a table. The result set
74/// contains a single column, with a single row.
75///
76/// ```no_run
77/// use spin_sdk::mysql::{Connection, Decode};
78///
79/// # async fn run() -> anyhow::Result<()> {
80/// let db = Connection::open("mysql://root:my_password@localhost/mydb").await?;
81///
82/// let mut query_result = db.query("SELECT COUNT(*) FROM users", &[]).await?;
83///
84/// assert_eq!(1, query_result.columns().len());
85/// assert_eq!("COUNT(*)", query_result.columns()[0].name);
86///
87/// let rows = query_result.collect().await?;
88///
89/// assert_eq!(1, rows.len());
90///
91/// let count = &rows[0][0];
92/// # Ok(())
93/// # }
94/// ```
95///
96/// Delete rows from a MySQL table. This uses [Connection::execute()]
97/// instead of the `query` method.
98///
99/// ```no_run
100/// use spin_sdk::mysql::{Connection, ParameterValue};
101///
102/// # async fn run() -> anyhow::Result<()> {
103/// let db = Connection::open("mysql://root:my_password@localhost/mydb").await?;
104///
105/// db.execute(
106///     "DELETE FROM users WHERE name = ?",
107///     &["Baldrick".to_owned().into()]
108/// ).await?;
109/// # Ok(())
110/// # }
111/// ```
112pub struct Connection(wit::mysql::Connection);
113
114impl Connection {
115    /// Open a connection to a MySQL database.
116    ///
117    /// The address may be in connection string form (`"host=... dbname=..."`)
118    /// or in URL form (`"mysql://<host>/<dbname>?..."`).
119    pub async fn open(address: impl Into<String>) -> Result<Self, Error> {
120        let inner = wit::mysql::Connection::open(address.into()).await?;
121        Ok(Self(inner))
122    }
123
124    /// Query the database.
125    ///
126    /// Use this function for queries that return rows (typically `SELECT` queries).
127    /// For side-effectful queries, see [`Connection::execute`].
128    pub async fn query(
129        &self,
130        statement: impl Into<String>,
131        params: impl Into<Vec<ParameterValue>>,
132    ) -> Result<QueryResult, Error> {
133        let (columns, rows, result) = self.0.query(statement.into(), params.into()).await?;
134        Ok(QueryResult {
135            columns: Arc::new(columns),
136            rows,
137            result,
138        })
139    }
140
141    /// Execute a command against the database.
142    ///
143    /// Use this function for side-effectful queries (such as `INSERT` or `DELETE` queries).
144    /// For queries that return row data, see [`Connection::query`].
145    pub async fn execute(
146        &self,
147        statement: impl Into<String>,
148        params: impl Into<Vec<ParameterValue>>,
149    ) -> Result<(), Error> {
150        self.0
151            .execute(statement.into(), params.into())
152            .await
153            .map_err(Error::MysqlError)
154    }
155}
156
157#[doc(inline)]
158pub use wit::mysql::Error as MysqlError;
159
160#[doc(inline)]
161pub use wit::mysql::{Column, DbDataType, DbValue, ParameterValue};
162
163/// The result of a [`Connection::query`] operation.
164pub struct QueryResult {
165    columns: Arc<Vec<Column>>,
166    rows: wit_bindgen::StreamReader<Vec<DbValue>>,
167    result: wit_bindgen::FutureReader<Result<(), MysqlError>>,
168}
169
170impl QueryResult {
171    /// The columns in the query result.
172    pub fn columns(&self) -> &[Column] {
173        &self.columns
174    }
175
176    /// Gets the next row in the result set.
177    ///
178    /// If this is `None`, there are no more rows available. You _must_
179    /// await [`QueryResult::result()`] to determine if all rows
180    /// were read successfully.
181    pub async fn next(&mut self) -> Option<Row> {
182        self.rows.next().await.map(|r| Row {
183            columns: self.columns.clone(),
184            result: r,
185        })
186    }
187
188    /// Whether the query completed successfully or with an error.
189    pub async fn result(self) -> Result<(), Error> {
190        self.result.await.map_err(Error::MysqlError)
191    }
192
193    /// Collect all rows in the result set.
194    ///
195    /// This is provided for when the result set is small enough to fit in
196    /// memory and you do not require streaming behaviour.
197    pub async fn collect(mut self) -> Result<Vec<Row>, Error> {
198        let mut rows = vec![];
199        while let Some(row) = self.next().await {
200            rows.push(row);
201        }
202        self.result.await.map_err(Error::MysqlError)?;
203        Ok(rows)
204    }
205
206    /// An asynchronous reader for the rows of the query result. Call
207    /// `.next().await` to iterate over the rows. When this returns `None`,
208    /// you have read all available rows. At this point you _must_ check
209    /// [`QueryResult::result()`] to determine if the read completed
210    /// successfully.
211    ///
212    /// This provides each row as a plain vector of database values.
213    /// [`QueryResult::next()`] provides a more ergonomic wrapper.
214    ///
215    /// To collect all rows into a vector, see [`QueryResult::collect`].
216    pub fn rows(&mut self) -> &mut wit_bindgen::StreamReader<Vec<DbValue>> {
217        &mut self.rows
218    }
219
220    /// Extracts the underlying Wasm Component Model results of the query.
221    #[allow(
222        clippy::type_complexity,
223        reason = "sorry clippy that's just what the inner bits are"
224    )]
225    pub fn into_inner(
226        self,
227    ) -> (
228        Vec<Column>,
229        wit_bindgen::StreamReader<Vec<DbValue>>,
230        wit_bindgen::FutureReader<Result<(), MysqlError>>,
231    ) {
232        ((*self.columns).clone(), self.rows, self.result)
233    }
234}
235
236/// A database row result.
237///
238/// There are two representations of a MySQL row in the SDK.  This type is useful for
239/// addressing elements by column name, and is obtained from the [QueryResult::next()] function.
240/// The [DbValue] vector representation is obtained from the [QueryResult::rows()] function, and provides
241/// index-based lookup or low-level access to row values via a vector.
242pub struct Row {
243    columns: Arc<Vec<wit::mysql::Column>>,
244    result: Vec<DbValue>,
245}
246
247impl Row {
248    /// Get a value by its column name. The value is converted to the target type as per the
249    /// conversion table shown in the module documentation.
250    ///
251    /// This function returns None for both no such column _and_ failed conversion. You should use
252    /// it only if you do not need to address errors (that is, if you know that conversion should
253    /// never fail). If your code does not know the type in advance, use the raw [QueryResult::rows()] function
254    /// instead of the [`QueryResult::next()`] or [`QueryResult::collect()`] wrappers to access
255    /// the underlying [DbValue] enum: this will allow you to
256    /// determine the type and process it accordingly.
257    ///
258    /// Additionally, this function performs a name lookup each time it is called. If you are iterating
259    /// over a large number of rows, it's more efficient to use column indexes, either calculated or
260    /// statically known from the column order in the SQL.
261    ///
262    /// # Examples
263    ///
264    /// ```no_run
265    /// use spin_sdk::mysql::{Connection, DbValue};
266    ///
267    /// # async fn run() -> anyhow::Result<()> {
268    /// # let user_id = 0;
269    /// let db = Connection::open("mysql://root:my_password@localhost/mydb").await?;
270    /// let mut query_result = db.query(
271    ///     "SELECT * FROM users WHERE id = ?",
272    ///     &[user_id.into()]
273    /// ).await?;
274    /// let user_row = query_result.next().await.unwrap();
275    ///
276    /// let name = user_row.get::<String>("name").unwrap();
277    /// let age = user_row.get::<i16>("age").unwrap();
278    /// # Ok(())
279    /// # }
280    /// ```
281    pub fn get<T: Decode>(&self, column: &str) -> Option<T> {
282        let i = self.columns.iter().position(|c| c.name == column)?;
283        let db_value = self.result.get(i)?;
284        Decode::decode(db_value).ok()
285    }
286}
287
288impl std::ops::Index<usize> for Row {
289    type Output = DbValue;
290
291    fn index(&self, index: usize) -> &Self::Output {
292        &self.result[index]
293    }
294}
295
296/// A MySQL error
297#[derive(Debug, thiserror::Error)]
298pub enum Error {
299    /// Failed to deserialize [`DbValue`]
300    #[error("error value decoding: {0}")]
301    Decode(String),
302    /// MySQL query failed with an error
303    #[error(transparent)]
304    MysqlError(#[from] MysqlError),
305}
306
307/// A type that can be decoded from the database.
308pub trait Decode: Sized {
309    /// Decode a new value of this type using a [`DbValue`].
310    fn decode(value: &DbValue) -> Result<Self, Error>;
311}
312
313impl<T> Decode for Option<T>
314where
315    T: Decode,
316{
317    fn decode(value: &DbValue) -> Result<Self, Error> {
318        match value {
319            DbValue::DbNull => Ok(None),
320            v => Ok(Some(T::decode(v)?)),
321        }
322    }
323}
324
325impl Decode for bool {
326    fn decode(value: &DbValue) -> Result<Self, Error> {
327        match value {
328            DbValue::Int8(0) => Ok(false),
329            DbValue::Int8(1) => Ok(true),
330            _ => Err(Error::Decode(format_decode_err(
331                "TINYINT(1), BOOLEAN",
332                value,
333            ))),
334        }
335    }
336}
337
338impl Decode for i8 {
339    fn decode(value: &DbValue) -> Result<Self, Error> {
340        match value {
341            DbValue::Int8(n) => Ok(*n),
342            _ => Err(Error::Decode(format_decode_err("TINYINT", value))),
343        }
344    }
345}
346
347impl Decode for i16 {
348    fn decode(value: &DbValue) -> Result<Self, Error> {
349        match value {
350            DbValue::Int16(n) => Ok(*n),
351            _ => Err(Error::Decode(format_decode_err("SMALLINT", value))),
352        }
353    }
354}
355
356impl Decode for i32 {
357    fn decode(value: &DbValue) -> Result<Self, Error> {
358        match value {
359            DbValue::Int32(n) => Ok(*n),
360            _ => Err(Error::Decode(format_decode_err("INT", value))),
361        }
362    }
363}
364
365impl Decode for i64 {
366    fn decode(value: &DbValue) -> Result<Self, Error> {
367        match value {
368            DbValue::Int64(n) => Ok(*n),
369            _ => Err(Error::Decode(format_decode_err("BIGINT", value))),
370        }
371    }
372}
373
374impl Decode for u8 {
375    fn decode(value: &DbValue) -> Result<Self, Error> {
376        match value {
377            DbValue::Uint8(n) => Ok(*n),
378            _ => Err(Error::Decode(format_decode_err("UNSIGNED TINYINT", value))),
379        }
380    }
381}
382
383impl Decode for u16 {
384    fn decode(value: &DbValue) -> Result<Self, Error> {
385        match value {
386            DbValue::Uint16(n) => Ok(*n),
387            _ => Err(Error::Decode(format_decode_err("UNSIGNED SMALLINT", value))),
388        }
389    }
390}
391
392impl Decode for u32 {
393    fn decode(value: &DbValue) -> Result<Self, Error> {
394        match value {
395            DbValue::Uint32(n) => Ok(*n),
396            _ => Err(Error::Decode(format_decode_err(
397                "UNISIGNED MEDIUMINT, UNSIGNED INT",
398                value,
399            ))),
400        }
401    }
402}
403
404impl Decode for u64 {
405    fn decode(value: &DbValue) -> Result<Self, Error> {
406        match value {
407            DbValue::Uint64(n) => Ok(*n),
408            _ => Err(Error::Decode(format_decode_err("UNSIGNED BIGINT", value))),
409        }
410    }
411}
412
413impl Decode for f32 {
414    fn decode(value: &DbValue) -> Result<Self, Error> {
415        match value {
416            DbValue::Floating32(n) => Ok(*n),
417            _ => Err(Error::Decode(format_decode_err("FLOAT", value))),
418        }
419    }
420}
421
422impl Decode for f64 {
423    fn decode(value: &DbValue) -> Result<Self, Error> {
424        match value {
425            DbValue::Floating64(n) => Ok(*n),
426            _ => Err(Error::Decode(format_decode_err("DOUBLE", value))),
427        }
428    }
429}
430
431impl Decode for Vec<u8> {
432    fn decode(value: &DbValue) -> Result<Self, Error> {
433        match value {
434            DbValue::Binary(n) => Ok(n.to_owned()),
435            _ => Err(Error::Decode(format_decode_err("BINARY, VARBINARY", value))),
436        }
437    }
438}
439
440impl Decode for String {
441    fn decode(value: &DbValue) -> Result<Self, Error> {
442        match value {
443            DbValue::Str(s) => Ok(s.to_owned()),
444            _ => Err(Error::Decode(format_decode_err(
445                "CHAR, VARCHAR, TEXT",
446                value,
447            ))),
448        }
449    }
450}
451
452macro_rules! impl_parameter_value_conversions {
453    ($($ty:ty => $id:ident),*) => {
454        $(
455            impl From<$ty> for ParameterValue {
456                fn from(v: $ty) -> ParameterValue {
457                    ParameterValue::$id(v)
458                }
459            }
460        )*
461    };
462}
463
464impl_parameter_value_conversions! {
465    i8 => Int8,
466    i16 => Int16,
467    i32 => Int32,
468    i64 => Int64,
469    f32 => Floating32,
470    f64 => Floating64,
471    bool => Boolean,
472    String => Str,
473    Vec<u8> => Binary
474}
475
476fn format_decode_err(types: &str, value: &DbValue) -> String {
477    format!("Expected {} from the DB but got {:?}", types, value)
478}
479
480#[cfg(test)]
481mod tests {
482    use super::*;
483
484    #[test]
485    fn boolean() {
486        assert!(bool::decode(&DbValue::Int8(1)).unwrap());
487        assert!(bool::decode(&DbValue::Int8(3)).is_err());
488        assert!(bool::decode(&DbValue::Int32(0)).is_err());
489        assert!(Option::<bool>::decode(&DbValue::DbNull).unwrap().is_none());
490    }
491
492    #[test]
493    fn int8() {
494        assert_eq!(i8::decode(&DbValue::Int8(0)).unwrap(), 0);
495        assert!(i8::decode(&DbValue::Int32(0)).is_err());
496        assert!(Option::<i8>::decode(&DbValue::DbNull).unwrap().is_none());
497    }
498
499    #[test]
500    fn int16() {
501        assert_eq!(i16::decode(&DbValue::Int16(0)).unwrap(), 0);
502        assert!(i16::decode(&DbValue::Int32(0)).is_err());
503        assert!(Option::<i16>::decode(&DbValue::DbNull).unwrap().is_none());
504    }
505
506    #[test]
507    fn int32() {
508        assert_eq!(i32::decode(&DbValue::Int32(0)).unwrap(), 0);
509        assert!(i32::decode(&DbValue::Boolean(false)).is_err());
510        assert!(Option::<i32>::decode(&DbValue::DbNull).unwrap().is_none());
511    }
512
513    #[test]
514    fn int64() {
515        assert_eq!(i64::decode(&DbValue::Int64(0)).unwrap(), 0);
516        assert!(i64::decode(&DbValue::Boolean(false)).is_err());
517        assert!(Option::<i64>::decode(&DbValue::DbNull).unwrap().is_none());
518    }
519
520    #[test]
521    fn uint8() {
522        assert_eq!(u8::decode(&DbValue::Uint8(0)).unwrap(), 0);
523        assert!(u8::decode(&DbValue::Uint32(0)).is_err());
524        assert!(Option::<u16>::decode(&DbValue::DbNull).unwrap().is_none());
525    }
526
527    #[test]
528    fn uint16() {
529        assert_eq!(u16::decode(&DbValue::Uint16(0)).unwrap(), 0);
530        assert!(u16::decode(&DbValue::Uint32(0)).is_err());
531        assert!(Option::<u16>::decode(&DbValue::DbNull).unwrap().is_none());
532    }
533
534    #[test]
535    fn uint32() {
536        assert_eq!(u32::decode(&DbValue::Uint32(0)).unwrap(), 0);
537        assert!(u32::decode(&DbValue::Boolean(false)).is_err());
538        assert!(Option::<u32>::decode(&DbValue::DbNull).unwrap().is_none());
539    }
540
541    #[test]
542    fn uint64() {
543        assert_eq!(u64::decode(&DbValue::Uint64(0)).unwrap(), 0);
544        assert!(u64::decode(&DbValue::Boolean(false)).is_err());
545        assert!(Option::<u64>::decode(&DbValue::DbNull).unwrap().is_none());
546    }
547
548    #[test]
549    fn floating32() {
550        assert!(f32::decode(&DbValue::Floating32(0.0)).is_ok());
551        assert!(f32::decode(&DbValue::Boolean(false)).is_err());
552        assert!(Option::<f32>::decode(&DbValue::DbNull).unwrap().is_none());
553    }
554
555    #[test]
556    fn floating64() {
557        assert!(f64::decode(&DbValue::Floating64(0.0)).is_ok());
558        assert!(f64::decode(&DbValue::Boolean(false)).is_err());
559        assert!(Option::<f64>::decode(&DbValue::DbNull).unwrap().is_none());
560    }
561
562    #[test]
563    fn str() {
564        assert_eq!(
565            String::decode(&DbValue::Str(String::from("foo"))).unwrap(),
566            String::from("foo")
567        );
568
569        assert!(String::decode(&DbValue::Int32(0)).is_err());
570        assert!(
571            Option::<String>::decode(&DbValue::DbNull)
572                .unwrap()
573                .is_none()
574        );
575    }
576}
spin_sdk/mysql.rs

spin_sdk/
mysql.rs
