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
24/// An open connection to a MySQL database.
25///
26/// # Examples
27///
28/// Load a set of rows from a local PostgreSQL database, and iterate over them.
29///
30/// ```no_run
31/// use spin_sdk::mysql::{Connection, Decode, ParameterValue};
32///
33/// # fn main() -> anyhow::Result<()> {
34/// # let min_age = 0;
35/// let db = Connection::open("mysql://root:my_password@localhost/mydb")?;
36///
37/// let query_result = db.query(
38///     "SELECT * FROM users WHERE age < ?",
39///     &[ParameterValue::Int32(20)]
40/// )?;
41///
42/// let name_index = query_result.columns.iter().position(|c| c.name == "name").unwrap();
43///
44/// for row in &query_result.rows {
45///     let name = String::decode(&row[name_index])?;
46///     println!("Found user {name}");
47/// }
48/// # Ok(())
49/// # }
50/// ```
51///
52/// Perform an aggregate (scalar) operation over a table. The result set
53/// contains a single column, with a single row.
54///
55/// ```no_run
56/// use spin_sdk::mysql::{Connection, Decode};
57///
58/// # fn main() -> anyhow::Result<()> {
59/// let db = Connection::open("mysql://root:my_password@localhost/mydb")?;
60///
61/// let query_result = db.query("SELECT COUNT(*) FROM users", &[])?;
62///
63/// assert_eq!(1, query_result.columns.len());
64/// assert_eq!("COUNT(*)", query_result.columns[0].name);
65/// assert_eq!(1, query_result.rows.len());
66///
67/// let count = i64::decode(&query_result.rows[0][0])?;
68/// # Ok(())
69/// # }
70/// ```
71///
72/// Delete rows from a MySQL table. This uses [Connection::execute()]
73/// instead of the `query` method.
74///
75/// ```no_run
76/// use spin_sdk::mysql::{Connection, ParameterValue};
77///
78/// # fn main() -> anyhow::Result<()> {
79/// let db = Connection::open("mysql://root:my_password@localhost/mydb")?;
80///
81/// let rows_affected = db.execute(
82///     "DELETE FROM users WHERE name = ?",
83///     &[ParameterValue::Str("Baldrick".to_owned())]
84/// )?;
85/// # Ok(())
86/// # }
87/// ```
88#[doc(inline)]
89pub use super::wit::v2::mysql::Connection;
90
91/// The result of a database query.
92///
93/// # Examples
94///
95/// Load a set of rows from a local PostgreSQL database, and iterate over them
96/// selecting one field from each. The columns collection allows you to find
97/// column indexes for column names; you can bypass this lookup if you name
98/// specific columns in the query.
99///
100/// ```no_run
101/// use spin_sdk::mysql::{Connection, Decode, ParameterValue};
102///
103/// # fn main() -> anyhow::Result<()> {
104/// # let min_age = 0;
105/// let db = Connection::open("mysql://root:my_password@localhost/mydb")?;
106///
107/// let query_result = db.query(
108///     "SELECT * FROM users WHERE age >= ?",
109///     &[ParameterValue::Int32(min_age)]
110/// )?;
111///
112/// let name_index = query_result.columns.iter().position(|c| c.name == "name").unwrap();
113///
114/// for row in &query_result.rows {
115///     let name = String::decode(&row[name_index])?;
116///     println!("Found user {name}");
117/// }
118/// # Ok(())
119/// # }
120/// ```
121#[doc(inline)]
122pub use super::wit::v2::mysql::RowSet;
123
124#[doc(inline)]
125pub use super::wit::v2::mysql::Error as MysqlError;
126
127#[doc(inline)]
128pub use super::wit::v2::rdbms_types::*;
129
130/// A MySQL error
131#[derive(Debug, thiserror::Error)]
132pub enum Error {
133    /// Failed to deserialize [`DbValue`]
134    #[error("error value decoding: {0}")]
135    Decode(String),
136    /// MySQL query failed with an error
137    #[error(transparent)]
138    MysqlError(#[from] MysqlError),
139}
140
141/// A type that can be decoded from the database.
142pub trait Decode: Sized {
143    /// Decode a new value of this type using a [`DbValue`].
144    fn decode(value: &DbValue) -> Result<Self, Error>;
145}
146
147impl<T> Decode for Option<T>
148where
149    T: Decode,
150{
151    fn decode(value: &DbValue) -> Result<Self, Error> {
152        match value {
153            DbValue::DbNull => Ok(None),
154            v => Ok(Some(T::decode(v)?)),
155        }
156    }
157}
158
159impl Decode for bool {
160    fn decode(value: &DbValue) -> Result<Self, Error> {
161        match value {
162            DbValue::Int8(0) => Ok(false),
163            DbValue::Int8(1) => Ok(true),
164            _ => Err(Error::Decode(format_decode_err(
165                "TINYINT(1), BOOLEAN",
166                value,
167            ))),
168        }
169    }
170}
171
172impl Decode for i8 {
173    fn decode(value: &DbValue) -> Result<Self, Error> {
174        match value {
175            DbValue::Int8(n) => Ok(*n),
176            _ => Err(Error::Decode(format_decode_err("TINYINT", value))),
177        }
178    }
179}
180
181impl Decode for i16 {
182    fn decode(value: &DbValue) -> Result<Self, Error> {
183        match value {
184            DbValue::Int16(n) => Ok(*n),
185            _ => Err(Error::Decode(format_decode_err("SMALLINT", value))),
186        }
187    }
188}
189
190impl Decode for i32 {
191    fn decode(value: &DbValue) -> Result<Self, Error> {
192        match value {
193            DbValue::Int32(n) => Ok(*n),
194            _ => Err(Error::Decode(format_decode_err("INT", value))),
195        }
196    }
197}
198
199impl Decode for i64 {
200    fn decode(value: &DbValue) -> Result<Self, Error> {
201        match value {
202            DbValue::Int64(n) => Ok(*n),
203            _ => Err(Error::Decode(format_decode_err("BIGINT", value))),
204        }
205    }
206}
207
208impl Decode for u8 {
209    fn decode(value: &DbValue) -> Result<Self, Error> {
210        match value {
211            DbValue::Uint8(n) => Ok(*n),
212            _ => Err(Error::Decode(format_decode_err("UNSIGNED TINYINT", value))),
213        }
214    }
215}
216
217impl Decode for u16 {
218    fn decode(value: &DbValue) -> Result<Self, Error> {
219        match value {
220            DbValue::Uint16(n) => Ok(*n),
221            _ => Err(Error::Decode(format_decode_err("UNSIGNED SMALLINT", value))),
222        }
223    }
224}
225
226impl Decode for u32 {
227    fn decode(value: &DbValue) -> Result<Self, Error> {
228        match value {
229            DbValue::Uint32(n) => Ok(*n),
230            _ => Err(Error::Decode(format_decode_err(
231                "UNISIGNED MEDIUMINT, UNSIGNED INT",
232                value,
233            ))),
234        }
235    }
236}
237
238impl Decode for u64 {
239    fn decode(value: &DbValue) -> Result<Self, Error> {
240        match value {
241            DbValue::Uint64(n) => Ok(*n),
242            _ => Err(Error::Decode(format_decode_err("UNSIGNED BIGINT", value))),
243        }
244    }
245}
246
247impl Decode for f32 {
248    fn decode(value: &DbValue) -> Result<Self, Error> {
249        match value {
250            DbValue::Floating32(n) => Ok(*n),
251            _ => Err(Error::Decode(format_decode_err("FLOAT", value))),
252        }
253    }
254}
255
256impl Decode for f64 {
257    fn decode(value: &DbValue) -> Result<Self, Error> {
258        match value {
259            DbValue::Floating64(n) => Ok(*n),
260            _ => Err(Error::Decode(format_decode_err("DOUBLE", value))),
261        }
262    }
263}
264
265impl Decode for Vec<u8> {
266    fn decode(value: &DbValue) -> Result<Self, Error> {
267        match value {
268            DbValue::Binary(n) => Ok(n.to_owned()),
269            _ => Err(Error::Decode(format_decode_err("BINARY, VARBINARY", value))),
270        }
271    }
272}
273
274impl Decode for String {
275    fn decode(value: &DbValue) -> Result<Self, Error> {
276        match value {
277            DbValue::Str(s) => Ok(s.to_owned()),
278            _ => Err(Error::Decode(format_decode_err(
279                "CHAR, VARCHAR, TEXT",
280                value,
281            ))),
282        }
283    }
284}
285
286fn format_decode_err(types: &str, value: &DbValue) -> String {
287    format!("Expected {} from the DB but got {:?}", types, value)
288}
289
290#[cfg(test)]
291mod tests {
292    use super::*;
293
294    #[test]
295    fn boolean() {
296        assert!(bool::decode(&DbValue::Int8(1)).unwrap());
297        assert!(bool::decode(&DbValue::Int8(3)).is_err());
298        assert!(bool::decode(&DbValue::Int32(0)).is_err());
299        assert!(Option::<bool>::decode(&DbValue::DbNull).unwrap().is_none());
300    }
301
302    #[test]
303    fn int8() {
304        assert_eq!(i8::decode(&DbValue::Int8(0)).unwrap(), 0);
305        assert!(i8::decode(&DbValue::Int32(0)).is_err());
306        assert!(Option::<i8>::decode(&DbValue::DbNull).unwrap().is_none());
307    }
308
309    #[test]
310    fn int16() {
311        assert_eq!(i16::decode(&DbValue::Int16(0)).unwrap(), 0);
312        assert!(i16::decode(&DbValue::Int32(0)).is_err());
313        assert!(Option::<i16>::decode(&DbValue::DbNull).unwrap().is_none());
314    }
315
316    #[test]
317    fn int32() {
318        assert_eq!(i32::decode(&DbValue::Int32(0)).unwrap(), 0);
319        assert!(i32::decode(&DbValue::Boolean(false)).is_err());
320        assert!(Option::<i32>::decode(&DbValue::DbNull).unwrap().is_none());
321    }
322
323    #[test]
324    fn int64() {
325        assert_eq!(i64::decode(&DbValue::Int64(0)).unwrap(), 0);
326        assert!(i64::decode(&DbValue::Boolean(false)).is_err());
327        assert!(Option::<i64>::decode(&DbValue::DbNull).unwrap().is_none());
328    }
329
330    #[test]
331    fn uint8() {
332        assert_eq!(u8::decode(&DbValue::Uint8(0)).unwrap(), 0);
333        assert!(u8::decode(&DbValue::Uint32(0)).is_err());
334        assert!(Option::<u16>::decode(&DbValue::DbNull).unwrap().is_none());
335    }
336
337    #[test]
338    fn uint16() {
339        assert_eq!(u16::decode(&DbValue::Uint16(0)).unwrap(), 0);
340        assert!(u16::decode(&DbValue::Uint32(0)).is_err());
341        assert!(Option::<u16>::decode(&DbValue::DbNull).unwrap().is_none());
342    }
343
344    #[test]
345    fn uint32() {
346        assert_eq!(u32::decode(&DbValue::Uint32(0)).unwrap(), 0);
347        assert!(u32::decode(&DbValue::Boolean(false)).is_err());
348        assert!(Option::<u32>::decode(&DbValue::DbNull).unwrap().is_none());
349    }
350
351    #[test]
352    fn uint64() {
353        assert_eq!(u64::decode(&DbValue::Uint64(0)).unwrap(), 0);
354        assert!(u64::decode(&DbValue::Boolean(false)).is_err());
355        assert!(Option::<u64>::decode(&DbValue::DbNull).unwrap().is_none());
356    }
357
358    #[test]
359    fn floating32() {
360        assert!(f32::decode(&DbValue::Floating32(0.0)).is_ok());
361        assert!(f32::decode(&DbValue::Boolean(false)).is_err());
362        assert!(Option::<f32>::decode(&DbValue::DbNull).unwrap().is_none());
363    }
364
365    #[test]
366    fn floating64() {
367        assert!(f64::decode(&DbValue::Floating64(0.0)).is_ok());
368        assert!(f64::decode(&DbValue::Boolean(false)).is_err());
369        assert!(Option::<f64>::decode(&DbValue::DbNull).unwrap().is_none());
370    }
371
372    #[test]
373    fn str() {
374        assert_eq!(
375            String::decode(&DbValue::Str(String::from("foo"))).unwrap(),
376            String::from("foo")
377        );
378
379        assert!(String::decode(&DbValue::Int32(0)).is_err());
380        assert!(Option::<String>::decode(&DbValue::DbNull)
381            .unwrap()
382            .is_none());
383    }
384}
spin_sdk/mysql.rs

spin_sdk/
mysql.rs
