spin_sdk/
sqlite.rs

1use crate::wit_bindgen;
2
3#[doc(hidden)]
4/// Module containing wit bindgen generated code.
5///
6/// This is only meant for internal consumption.
7pub mod wit {
8    #![allow(missing_docs)]
9    use crate::wit_bindgen;
10
11    wit_bindgen::generate!({
12        runtime_path: "crate::wit_bindgen::rt",
13        world: "spin-sdk-sqlite",
14        path: "wit",
15        generate_all,
16    });
17
18    pub use spin::sqlite::sqlite;
19}
20
21#[doc(inline)]
22pub use wit::sqlite::{Error, Value};
23
24/// An open connection to a SQLite database.
25///
26/// [Connection::execute()] returns a tuple of `(columns, rows_stream, finish_future)`
27/// where rows are consumed from a stream and the finish future is awaited to check
28/// for errors.
29///
30/// # Examples
31///
32/// Open the default database, query rows, and iterate over the stream.
33///
34/// ```no_run
35/// # async fn run() -> anyhow::Result<()> {
36/// use spin_sdk::sqlite::{Connection, Value};
37///
38/// let min_age = 0;
39/// let db = Connection::open_default().await?;
40///
41/// let mut query_result = db.execute(
42///     "SELECT * FROM users WHERE age >= ?",
43///     [Value::Integer(min_age)],
44/// ).await?;
45///
46/// let name_idx = query_result.columns().iter().position(|c| c == "name").unwrap();
47///
48/// while let Some(row) = query_result.next().await {
49///     let name: &str = row.get(name_idx).unwrap();
50///     println!("Found user {name}");
51/// }
52///
53/// query_result.result().await?;
54/// # Ok(())
55/// # }
56/// ```
57///
58/// Perform an aggregate (scalar) operation over a named database.
59///
60/// ```no_run
61/// # async fn run() -> anyhow::Result<()> {
62/// use spin_sdk::sqlite::Connection;
63///
64/// let db = Connection::open("customer-data").await?;
65/// let mut query_result = db.execute("SELECT COUNT(*) FROM users", []).await?;
66///
67/// if let Some(row) = query_result.next().await {
68///     let count: i64 = row.get(0).unwrap();
69///     println!("Total users: {count}");
70/// }
71///
72/// query_result.result().await?;
73/// # Ok(())
74/// # }
75/// ```
76///
77/// Delete rows from a database. The row stream will be empty, but the finish
78/// future must still be awaited.
79///
80/// ```no_run
81/// # async fn run() -> anyhow::Result<()> {
82/// use spin_sdk::sqlite::{Connection, Value};
83///
84/// let min_age = 18;
85/// let db = Connection::open("customer-data").await?;
86/// let query_result = db.execute(
87///     "DELETE FROM users WHERE age < ?",
88///     [Value::Integer(min_age)],
89/// ).await?;
90///
91/// query_result.result().await?;
92/// # Ok(())
93/// # }
94/// ```
95pub struct Connection(wit::sqlite::Connection);
96
97impl Connection {
98    /// Open a connection to the default database
99    pub async fn open_default() -> Result<Self, Error> {
100        Self::open("default").await
101    }
102
103    /// Open a connection to a named database instance.
104    ///
105    /// If `database` is "default", the default instance is opened.
106    ///
107    /// `error::no-such-database` will be raised if the `name` is not recognized.
108    pub async fn open(database: impl AsRef<str>) -> Result<Self, Error> {
109        wit::sqlite::Connection::open_async(database.as_ref().to_string())
110            .await
111            .map(Connection)
112    }
113
114    /// Execute a statement returning back data if there is any
115    pub async fn execute(
116        &self,
117        statement: impl AsRef<str>,
118        parameters: impl IntoIterator<Item = Value>,
119    ) -> Result<QueryResult, Error> {
120        let (columns, rows, result) = self
121            .0
122            .execute_async(
123                statement.as_ref().to_string(),
124                parameters.into_iter().collect(),
125            )
126            .await?;
127        Ok(QueryResult {
128            columns,
129            rows,
130            result,
131        })
132    }
133
134    /// The SQLite rowid of the most recent successful INSERT on the connection, or 0 if
135    /// there has not yet been an INSERT on the connection.
136    pub async fn last_insert_rowid(&self) -> i64 {
137        self.0.last_insert_rowid_async().await
138    }
139
140    /// The number of rows modified, inserted or deleted by the most recently completed
141    /// INSERT, UPDATE or DELETE statement on the connection.
142    pub async fn changes(&self) -> u64 {
143        self.0.changes_async().await
144    }
145}
146
147/// The result of a [`Connection::execute`] operation.
148pub struct QueryResult {
149    columns: Vec<String>,
150    rows: wit_bindgen::StreamReader<RowResult>,
151    result: wit_bindgen::FutureReader<Result<(), Error>>,
152}
153
154impl QueryResult {
155    /// The columns in the query result.
156    pub fn columns(&self) -> &[String] {
157        &self.columns
158    }
159
160    /// Gets the next row in the result set.
161    ///
162    /// If this is `None`, there are no more rows available. You _must_
163    /// await [`QueryResult::result()`] to determine if all rows
164    /// were read successfully.
165    pub async fn next(&mut self) -> Option<RowResult> {
166        self.rows.next().await
167    }
168
169    /// Whether the query completed successfully or with an error.
170    pub async fn result(self) -> Result<(), Error> {
171        self.result.await
172    }
173
174    /// Collect all rows in the result set.
175    ///
176    /// This is provided for when the result set is small enough to fit in
177    /// memory and you do not require streaming behaviour.
178    pub async fn collect(self) -> Result<Vec<RowResult>, Error> {
179        let rows = self.rows.collect().await;
180        self.result.await?;
181        Ok(rows)
182    }
183
184    /// Extracts the underlying Wasm Component Model results of the query.
185    #[allow(clippy::type_complexity, reason = "that's what the inner bits are")]
186    pub fn into_inner(
187        self,
188    ) -> (
189        Vec<String>,
190        wit_bindgen::StreamReader<RowResult>,
191        wit_bindgen::FutureReader<Result<(), Error>>,
192    ) {
193        (self.columns, self.rows, self.result)
194    }
195}
196
197/// A single row from a SQLite query result.
198///
199/// `RowResult` provides index-based access to column values via [`RowResult::get()`].
200///
201/// # Examples
202///
203/// Consume rows from the async streaming API:
204///
205/// ```no_run
206/// # async fn run() -> anyhow::Result<()> {
207/// use spin_sdk::sqlite::{Connection, Value};
208///
209/// let db = Connection::open_default().await?;
210/// let mut query_result = db.execute(
211///     "SELECT name, age FROM users WHERE age >= ?",
212///     [Value::Integer(0)],
213/// ).await?;
214///
215/// let name_idx = query_result.columns().iter().position(|c| c == "name").unwrap();
216///
217/// while let Some(row) = query_result.next().await {
218///     let name: &str = row.get(name_idx).unwrap();
219///     println!("Found user {name}");
220/// }
221///
222/// query_result.result().await?;
223/// # Ok(())
224/// # }
225/// ```
226#[doc(inline)]
227pub use wit::sqlite::RowResult;
228
229impl RowResult {
230    /// Get a value by its column name. The value is converted to the target type.
231    ///
232    /// * SQLite integers are convertible to Rust integer types (i8, u8, i16, etc. including usize and isize) and bool.
233    /// * SQLite strings are convertible to Rust &str or &[u8] (encoded as UTF-8).
234    /// * SQLite reals are convertible to Rust f64.
235    /// * SQLite blobs are convertible to Rust &[u8] or &str (interpreted as UTF-8).
236    ///
237    /// To look up by name, you can use `QueryResult::rows()` or obtain the invoice from `QueryResult::columns`.
238    /// If you do not know the type of a value, access the underlying [Value] enum directly
239    /// via the [RowResult::values] field
240    ///
241    /// # Examples
242    ///
243    /// ```no_run
244    /// # async fn run() -> anyhow::Result<()> {
245    /// use spin_sdk::sqlite::{Connection, Value};
246    ///
247    /// let db = Connection::open_default().await?;
248    /// let mut query_result = db.execute(
249    ///     "SELECT name, age FROM users WHERE id = ?",
250    ///     [Value::Integer(0)],
251    /// ).await?;
252    ///
253    /// if let Some(row) = query_result.next().await {
254    ///     let name: &str = row.get(0).unwrap();
255    ///     let age: u16 = row.get(1).unwrap();
256    ///     println!("{name} is {age} years old");
257    /// }
258    ///
259    /// query_result.result().await?;
260    /// # Ok(())
261    /// # }
262    /// ```
263    pub fn get<'a, T: TryFrom<&'a Value>>(&'a self, index: usize) -> Option<T> {
264        self.values.get(index).and_then(|c| c.try_into().ok())
265    }
266}
267
268impl<'a> TryFrom<&'a Value> for bool {
269    type Error = ();
270
271    fn try_from(value: &'a Value) -> Result<Self, Self::Error> {
272        match value {
273            Value::Integer(i) => Ok(*i != 0),
274            _ => Err(()),
275        }
276    }
277}
278
279macro_rules! int_from_value {
280    ($($t:ty),*) => {
281        $(impl<'a> TryFrom<&'a Value> for $t {
282            type Error = ();
283
284            fn try_from(value: &'a Value) -> Result<Self, Self::Error> {
285                match value {
286                    Value::Integer(i) => (*i).try_into().map_err(|_| ()),
287                    _ => Err(()),
288                }
289            }
290        })*
291    };
292}
293
294int_from_value!(u8, u16, u32, u64, i8, i16, i32, i64, usize, isize);
295
296impl<'a> TryFrom<&'a Value> for f64 {
297    type Error = ();
298
299    fn try_from(value: &'a Value) -> Result<Self, Self::Error> {
300        match value {
301            Value::Real(f) => Ok(*f),
302            _ => Err(()),
303        }
304    }
305}
306
307impl<'a> TryFrom<&'a Value> for &'a str {
308    type Error = ();
309
310    fn try_from(value: &'a Value) -> Result<Self, Self::Error> {
311        match value {
312            Value::Text(s) => Ok(s.as_str()),
313            Value::Blob(b) => std::str::from_utf8(b).map_err(|_| ()),
314            _ => Err(()),
315        }
316    }
317}
318
319impl<'a> TryFrom<&'a Value> for &'a [u8] {
320    type Error = ();
321
322    fn try_from(value: &'a Value) -> Result<Self, Self::Error> {
323        match value {
324            Value::Blob(b) => Ok(b.as_slice()),
325            Value::Text(s) => Ok(s.as_bytes()),
326            _ => Err(()),
327        }
328    }
329}
330
331impl Value {
332    /// Creates a Text parameter.
333    pub fn text(value: impl Into<String>) -> Self {
334        Self::Text(value.into())
335    }
336
337    /// Creates an Integer parameter.
338    pub fn integer(value: impl Into<i64>) -> Self {
339        Self::Integer(value.into())
340    }
341
342    /// Creates a Real parameter.
343    pub fn real(value: impl Into<f64>) -> Self {
344        Self::Real(value.into())
345    }
346
347    /// Creates a Blob parameter.
348    pub fn blob(value: impl Into<Vec<u8>>) -> Self {
349        Self::Blob(value.into())
350    }
351}
352
353impl From<&str> for Value {
354    fn from(value: &str) -> Self {
355        Self::Text(value.into())
356    }
357}
358
359impl From<String> for Value {
360    fn from(value: String) -> Self {
361        Self::Text(value)
362    }
363}
364
365macro_rules! value_from_int {
366    ($($t:ty),*) => {
367        $(impl From<$t> for Value {
368            fn from(value: $t) -> Self {
369                Self::integer(value)
370            }
371        })*
372    };
373}
374
375value_from_int!(u8, u16, u32, i8, i16, i32, i64);
376
377impl From<f32> for Value {
378    fn from(value: f32) -> Self {
379        Self::Real(value.into())
380    }
381}
382
383impl From<f64> for Value {
384    fn from(value: f64) -> Self {
385        Self::Real(value)
386    }
387}
388
389impl From<&[u8]> for Value {
390    fn from(value: &[u8]) -> Self {
391        Self::Blob(value.into())
392    }
393}
394
395impl<const N: usize> From<[u8; N]> for Value {
396    fn from(value: [u8; N]) -> Self {
397        Self::Blob(value.into())
398    }
399}
400
401impl<const N: usize> From<&[u8; N]> for Value {
402    fn from(value: &[u8; N]) -> Self {
403        Self::Blob(value.into())
404    }
405}
406
407impl From<Vec<u8>> for Value {
408    fn from(value: Vec<u8>) -> Self {
409        Self::Blob(value)
410    }
411}
412
413impl<T: Into<Value>> From<Option<T>> for Value {
414    fn from(value: Option<T>) -> Self {
415        match value {
416            None => Value::Null,
417            Some(value) => value.into(),
418        }
419    }
420}
421
422impl PartialEq for Value {
423    fn eq(&self, other: &Self) -> bool {
424        match (self, other) {
425            (Self::Integer(l0), Self::Integer(r0)) => l0 == r0,
426            (Self::Real(l0), Self::Real(r0)) => l0 == r0,
427            (Self::Text(l0), Self::Text(r0)) => l0 == r0,
428            (Self::Blob(l0), Self::Blob(r0)) => l0 == r0,
429            _ => core::mem::discriminant(self) == core::mem::discriminant(other),
430        }
431    }
432}
433
434#[cfg(test)]
435mod test {
436    use super::*;
437
438    #[test]
439    fn value_conversions() {
440        let expected_text = Value::Text("a".to_string());
441        let expected_int = Value::Integer(123);
442        let expected_real = Value::Real(1234.5); // the test wants equality and FP stuff is notoriously inexact: use a value for testing that won't incur off-by-0.00000001 errors
443        let expected_real_int = Value::Real(123.0);
444        let expected_blob = Value::Blob(vec![1, 2, 3]);
445
446        assert_eq!(expected_text, Value::text("a"));
447        assert_eq!(expected_text, "a".into());
448        assert_eq!(expected_text, "a".to_string().into());
449
450        assert_eq!(expected_int, Value::integer(123u8));
451        assert_eq!(expected_int, Value::integer(123i16));
452        assert_eq!(expected_int, Value::integer(123u32));
453        assert_eq!(expected_int, Value::integer(123i64));
454        assert_eq!(expected_int, 123u8.into());
455        assert_eq!(expected_int, 123i16.into());
456        assert_eq!(expected_int, 123u32.into());
457        assert_eq!(expected_int, 123i64.into());
458
459        assert_eq!(expected_real, Value::real(1234.5f32));
460        assert_eq!(expected_real, Value::real(1234.5f64));
461        assert_eq!(expected_real, 1234.5f32.into());
462        assert_eq!(expected_real, 1234.5f64.into());
463        // named function allows passing integer to Real case (where `into()` would give you the Integer case)
464        assert_eq!(expected_real_int, Value::real(123u32));
465
466        assert_eq!(expected_blob, Value::blob([1, 2, 3]));
467        assert_eq!(expected_blob, Value::blob(vec![1, 2, 3]));
468        assert_eq!(expected_blob, (&[1, 2, 3]).into());
469        assert_eq!(expected_blob, ([1, 2, 3][..]).into());
470        assert_eq!(expected_blob, [1, 2, 3].into());
471        assert_eq!(expected_blob, (vec![1, 2, 3]).into());
472
473        assert_eq!(Value::Null, None::<i16>.into());
474        assert_eq!(expected_int, Some(123u32).into());
475    }
476}
spin_sdk/sqlite.rs

spin_sdk/
sqlite.rs
