spin_sdk/
pg3.rs

1//! Postgres relational database storage for Spin 3.3 and earlier. Applications that do not require
2//! this backward compatibility should use the [`pg4`](crate::pg4) module instead.
3//!
4//! You can use the [`into()`](std::convert::Into) method to convert
5//! a Rust value into a [`ParameterValue`]. You can use the
6//! [`Decode`] trait to convert a [`DbValue`] to a suitable Rust type.
7//! The following table shows available conversions.
8//!
9//! # Types
10//!
11//! | Rust type               | WIT (db-value)                                | Postgres type(s)             |
12//! |-------------------------|-----------------------------------------------|----------------------------- |
13//! | `bool`                  | boolean(bool)                                 | BOOL                         |
14//! | `i16`                   | int16(s16)                                    | SMALLINT, SMALLSERIAL, INT2  |
15//! | `i32`                   | int32(s32)                                    | INT, SERIAL, INT4            |
16//! | `i64`                   | int64(s64)                                    | BIGINT, BIGSERIAL, INT8      |
17//! | `f32`                   | floating32(float32)                           | REAL, FLOAT4                 |
18//! | `f64`                   | floating64(float64)                           | DOUBLE PRECISION, FLOAT8     |
19//! | `String`                | str(string)                                   | VARCHAR, CHAR(N), TEXT       |
20//! | `Vec<u8>`               | binary(list\<u8\>)                            | BYTEA                        |
21//! | `chrono::NaiveDate`     | date(tuple<s32, u8, u8>)                      | DATE                         |
22//! | `chrono::NaiveTime`     | time(tuple<u8, u8, u8, u32>)                  | TIME                         |
23//! | `chrono::NaiveDateTime` | datetime(tuple<s32, u8, u8, u8, u8, u8, u32>) | TIMESTAMP                    |
24//! | `chrono::Duration`      | timestamp(s64)                                | BIGINT                       |
25
26/// An open connection to a PostgreSQL database.
27///
28/// # Examples
29///
30/// Load a set of rows from a local PostgreSQL database, and iterate over them.
31///
32/// ```no_run
33/// use spin_sdk::pg3::{Connection, Decode};
34///
35/// # fn main() -> anyhow::Result<()> {
36/// # let min_age = 0;
37/// let db = Connection::open("host=localhost user=postgres password=my_password dbname=mydb")?;
38///
39/// let query_result = db.query(
40///     "SELECT * FROM users WHERE age >= $1",
41///     &[min_age.into()]
42/// )?;
43///
44/// let name_index = query_result.columns.iter().position(|c| c.name == "name").unwrap();
45///
46/// for row in &query_result.rows {
47///     let name = String::decode(&row[name_index])?;
48///     println!("Found user {name}");
49/// }
50/// # Ok(())
51/// # }
52/// ```
53///
54/// Perform an aggregate (scalar) operation over a table. The result set
55/// contains a single column, with a single row.
56///
57/// ```no_run
58/// use spin_sdk::pg3::{Connection, Decode};
59///
60/// # fn main() -> anyhow::Result<()> {
61/// let db = Connection::open("host=localhost user=postgres password=my_password dbname=mydb")?;
62///
63/// let query_result = db.query("SELECT COUNT(*) FROM users", &[])?;
64///
65/// assert_eq!(1, query_result.columns.len());
66/// assert_eq!("count", query_result.columns[0].name);
67/// assert_eq!(1, query_result.rows.len());
68///
69/// let count = i64::decode(&query_result.rows[0][0])?;
70/// # Ok(())
71/// # }
72/// ```
73///
74/// Delete rows from a PostgreSQL table. This uses [Connection::execute()]
75/// instead of the `query` method.
76///
77/// ```no_run
78/// use spin_sdk::pg3::Connection;
79///
80/// # fn main() -> anyhow::Result<()> {
81/// let db = Connection::open("host=localhost user=postgres password=my_password dbname=mydb")?;
82///
83/// let rows_affected = db.execute(
84///     "DELETE FROM users WHERE name = $1",
85///     &["Baldrick".to_owned().into()]
86/// )?;
87/// # Ok(())
88/// # }
89/// ```
90#[doc(inline)]
91pub use super::wit::pg3::Connection;
92
93/// The result of a database query.
94///
95/// # Examples
96///
97/// Load a set of rows from a local PostgreSQL database, and iterate over them
98/// selecting one field from each. The columns collection allows you to find
99/// column indexes for column names; you can bypass this lookup if you name
100/// specific columns in the query.
101///
102/// ```no_run
103/// use spin_sdk::pg3::{Connection, Decode};
104///
105/// # fn main() -> anyhow::Result<()> {
106/// # let min_age = 0;
107/// let db = Connection::open("host=localhost user=postgres password=my_password dbname=mydb")?;
108///
109/// let query_result = db.query(
110///     "SELECT * FROM users WHERE age >= $1",
111///     &[min_age.into()]
112/// )?;
113///
114/// let name_index = query_result.columns.iter().position(|c| c.name == "name").unwrap();
115///
116/// for row in &query_result.rows {
117///     let name = String::decode(&row[name_index])?;
118///     println!("Found user {name}");
119/// }
120/// # Ok(())
121/// # }
122/// ```
123pub use super::wit::pg3::RowSet;
124
125#[doc(inline)]
126pub use super::wit::pg3::{Error as PgError, *};
127
128use chrono::{Datelike, Timelike};
129
130/// A Postgres error
131#[derive(Debug, thiserror::Error)]
132pub enum Error {
133    /// Failed to deserialize [`DbValue`]
134    #[error("error value decoding: {0}")]
135    Decode(String),
136    /// Postgres query failed with an error
137    #[error(transparent)]
138    PgError(#[from] PgError),
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::Boolean(boolean) => Ok(*boolean),
163            _ => Err(Error::Decode(format_decode_err("BOOL", value))),
164        }
165    }
166}
167
168impl Decode for i16 {
169    fn decode(value: &DbValue) -> Result<Self, Error> {
170        match value {
171            DbValue::Int16(n) => Ok(*n),
172            _ => Err(Error::Decode(format_decode_err("SMALLINT", value))),
173        }
174    }
175}
176
177impl Decode for i32 {
178    fn decode(value: &DbValue) -> Result<Self, Error> {
179        match value {
180            DbValue::Int32(n) => Ok(*n),
181            _ => Err(Error::Decode(format_decode_err("INT", value))),
182        }
183    }
184}
185
186impl Decode for i64 {
187    fn decode(value: &DbValue) -> Result<Self, Error> {
188        match value {
189            DbValue::Int64(n) => Ok(*n),
190            _ => Err(Error::Decode(format_decode_err("BIGINT", value))),
191        }
192    }
193}
194
195impl Decode for f32 {
196    fn decode(value: &DbValue) -> Result<Self, Error> {
197        match value {
198            DbValue::Floating32(n) => Ok(*n),
199            _ => Err(Error::Decode(format_decode_err("REAL", value))),
200        }
201    }
202}
203
204impl Decode for f64 {
205    fn decode(value: &DbValue) -> Result<Self, Error> {
206        match value {
207            DbValue::Floating64(n) => Ok(*n),
208            _ => Err(Error::Decode(format_decode_err("DOUBLE PRECISION", value))),
209        }
210    }
211}
212
213impl Decode for Vec<u8> {
214    fn decode(value: &DbValue) -> Result<Self, Error> {
215        match value {
216            DbValue::Binary(n) => Ok(n.to_owned()),
217            _ => Err(Error::Decode(format_decode_err("BYTEA", value))),
218        }
219    }
220}
221
222impl Decode for String {
223    fn decode(value: &DbValue) -> Result<Self, Error> {
224        match value {
225            DbValue::Str(s) => Ok(s.to_owned()),
226            _ => Err(Error::Decode(format_decode_err(
227                "CHAR, VARCHAR, TEXT",
228                value,
229            ))),
230        }
231    }
232}
233
234impl Decode for chrono::NaiveDate {
235    fn decode(value: &DbValue) -> Result<Self, Error> {
236        match value {
237            DbValue::Date((year, month, day)) => {
238                let naive_date =
239                    chrono::NaiveDate::from_ymd_opt(*year, (*month).into(), (*day).into())
240                        .ok_or_else(|| {
241                            Error::Decode(format!(
242                                "invalid date y={}, m={}, d={}",
243                                year, month, day
244                            ))
245                        })?;
246                Ok(naive_date)
247            }
248            _ => Err(Error::Decode(format_decode_err("DATE", value))),
249        }
250    }
251}
252
253impl Decode for chrono::NaiveTime {
254    fn decode(value: &DbValue) -> Result<Self, Error> {
255        match value {
256            DbValue::Time((hour, minute, second, nanosecond)) => {
257                let naive_time = chrono::NaiveTime::from_hms_nano_opt(
258                    (*hour).into(),
259                    (*minute).into(),
260                    (*second).into(),
261                    *nanosecond,
262                )
263                .ok_or_else(|| {
264                    Error::Decode(format!(
265                        "invalid time {}:{}:{}:{}",
266                        hour, minute, second, nanosecond
267                    ))
268                })?;
269                Ok(naive_time)
270            }
271            _ => Err(Error::Decode(format_decode_err("TIME", value))),
272        }
273    }
274}
275
276impl Decode for chrono::NaiveDateTime {
277    fn decode(value: &DbValue) -> Result<Self, Error> {
278        match value {
279            DbValue::Datetime((year, month, day, hour, minute, second, nanosecond)) => {
280                let naive_date =
281                    chrono::NaiveDate::from_ymd_opt(*year, (*month).into(), (*day).into())
282                        .ok_or_else(|| {
283                            Error::Decode(format!(
284                                "invalid date y={}, m={}, d={}",
285                                year, month, day
286                            ))
287                        })?;
288                let naive_time = chrono::NaiveTime::from_hms_nano_opt(
289                    (*hour).into(),
290                    (*minute).into(),
291                    (*second).into(),
292                    *nanosecond,
293                )
294                .ok_or_else(|| {
295                    Error::Decode(format!(
296                        "invalid time {}:{}:{}:{}",
297                        hour, minute, second, nanosecond
298                    ))
299                })?;
300                let dt = chrono::NaiveDateTime::new(naive_date, naive_time);
301                Ok(dt)
302            }
303            _ => Err(Error::Decode(format_decode_err("DATETIME", value))),
304        }
305    }
306}
307
308impl Decode for chrono::Duration {
309    fn decode(value: &DbValue) -> Result<Self, Error> {
310        match value {
311            DbValue::Timestamp(n) => Ok(chrono::Duration::seconds(*n)),
312            _ => Err(Error::Decode(format_decode_err("BIGINT", value))),
313        }
314    }
315}
316
317macro_rules! impl_parameter_value_conversions {
318    ($($ty:ty => $id:ident),*) => {
319        $(
320            impl From<$ty> for ParameterValue {
321                fn from(v: $ty) -> ParameterValue {
322                    ParameterValue::$id(v)
323                }
324            }
325        )*
326    };
327}
328
329impl_parameter_value_conversions! {
330    i8 => Int8,
331    i16 => Int16,
332    i32 => Int32,
333    i64 => Int64,
334    f32 => Floating32,
335    f64 => Floating64,
336    bool => Boolean,
337    String => Str,
338    Vec<u8> => Binary
339}
340
341impl From<chrono::NaiveDateTime> for ParameterValue {
342    fn from(v: chrono::NaiveDateTime) -> ParameterValue {
343        ParameterValue::Datetime((
344            v.year(),
345            v.month() as u8,
346            v.day() as u8,
347            v.hour() as u8,
348            v.minute() as u8,
349            v.second() as u8,
350            v.nanosecond(),
351        ))
352    }
353}
354
355impl From<chrono::NaiveTime> for ParameterValue {
356    fn from(v: chrono::NaiveTime) -> ParameterValue {
357        ParameterValue::Time((
358            v.hour() as u8,
359            v.minute() as u8,
360            v.second() as u8,
361            v.nanosecond(),
362        ))
363    }
364}
365
366impl From<chrono::NaiveDate> for ParameterValue {
367    fn from(v: chrono::NaiveDate) -> ParameterValue {
368        ParameterValue::Date((v.year(), v.month() as u8, v.day() as u8))
369    }
370}
371
372impl From<chrono::TimeDelta> for ParameterValue {
373    fn from(v: chrono::TimeDelta) -> ParameterValue {
374        ParameterValue::Timestamp(v.num_seconds())
375    }
376}
377
378impl<T: Into<ParameterValue>> From<Option<T>> for ParameterValue {
379    fn from(o: Option<T>) -> ParameterValue {
380        match o {
381            Some(v) => v.into(),
382            None => ParameterValue::DbNull,
383        }
384    }
385}
386
387fn format_decode_err(types: &str, value: &DbValue) -> String {
388    format!("Expected {} from the DB but got {:?}", types, value)
389}
390
391#[cfg(test)]
392mod tests {
393    use chrono::NaiveDateTime;
394
395    use super::*;
396
397    #[test]
398    fn boolean() {
399        assert!(bool::decode(&DbValue::Boolean(true)).unwrap());
400        assert!(bool::decode(&DbValue::Int32(0)).is_err());
401        assert!(Option::<bool>::decode(&DbValue::DbNull).unwrap().is_none());
402    }
403
404    #[test]
405    fn int16() {
406        assert_eq!(i16::decode(&DbValue::Int16(0)).unwrap(), 0);
407        assert!(i16::decode(&DbValue::Int32(0)).is_err());
408        assert!(Option::<i16>::decode(&DbValue::DbNull).unwrap().is_none());
409    }
410
411    #[test]
412    fn int32() {
413        assert_eq!(i32::decode(&DbValue::Int32(0)).unwrap(), 0);
414        assert!(i32::decode(&DbValue::Boolean(false)).is_err());
415        assert!(Option::<i32>::decode(&DbValue::DbNull).unwrap().is_none());
416    }
417
418    #[test]
419    fn int64() {
420        assert_eq!(i64::decode(&DbValue::Int64(0)).unwrap(), 0);
421        assert!(i64::decode(&DbValue::Boolean(false)).is_err());
422        assert!(Option::<i64>::decode(&DbValue::DbNull).unwrap().is_none());
423    }
424
425    #[test]
426    fn floating32() {
427        assert!(f32::decode(&DbValue::Floating32(0.0)).is_ok());
428        assert!(f32::decode(&DbValue::Boolean(false)).is_err());
429        assert!(Option::<f32>::decode(&DbValue::DbNull).unwrap().is_none());
430    }
431
432    #[test]
433    fn floating64() {
434        assert!(f64::decode(&DbValue::Floating64(0.0)).is_ok());
435        assert!(f64::decode(&DbValue::Boolean(false)).is_err());
436        assert!(Option::<f64>::decode(&DbValue::DbNull).unwrap().is_none());
437    }
438
439    #[test]
440    fn str() {
441        assert_eq!(
442            String::decode(&DbValue::Str(String::from("foo"))).unwrap(),
443            String::from("foo")
444        );
445
446        assert!(String::decode(&DbValue::Int32(0)).is_err());
447        assert!(Option::<String>::decode(&DbValue::DbNull)
448            .unwrap()
449            .is_none());
450    }
451
452    #[test]
453    fn binary() {
454        assert!(Vec::<u8>::decode(&DbValue::Binary(vec![0, 0])).is_ok());
455        assert!(Vec::<u8>::decode(&DbValue::Boolean(false)).is_err());
456        assert!(Option::<Vec<u8>>::decode(&DbValue::DbNull)
457            .unwrap()
458            .is_none());
459    }
460
461    #[test]
462    fn date() {
463        assert_eq!(
464            chrono::NaiveDate::decode(&DbValue::Date((1, 2, 4))).unwrap(),
465            chrono::NaiveDate::from_ymd_opt(1, 2, 4).unwrap()
466        );
467        assert_ne!(
468            chrono::NaiveDate::decode(&DbValue::Date((1, 2, 4))).unwrap(),
469            chrono::NaiveDate::from_ymd_opt(1, 2, 5).unwrap()
470        );
471        assert!(Option::<chrono::NaiveDate>::decode(&DbValue::DbNull)
472            .unwrap()
473            .is_none());
474    }
475
476    #[test]
477    fn time() {
478        assert_eq!(
479            chrono::NaiveTime::decode(&DbValue::Time((1, 2, 3, 4))).unwrap(),
480            chrono::NaiveTime::from_hms_nano_opt(1, 2, 3, 4).unwrap()
481        );
482        assert_ne!(
483            chrono::NaiveTime::decode(&DbValue::Time((1, 2, 3, 4))).unwrap(),
484            chrono::NaiveTime::from_hms_nano_opt(1, 2, 4, 5).unwrap()
485        );
486        assert!(Option::<chrono::NaiveTime>::decode(&DbValue::DbNull)
487            .unwrap()
488            .is_none());
489    }
490
491    #[test]
492    fn datetime() {
493        let date = chrono::NaiveDate::from_ymd_opt(1, 2, 3).unwrap();
494        let mut time = chrono::NaiveTime::from_hms_nano_opt(4, 5, 6, 7).unwrap();
495        assert_eq!(
496            chrono::NaiveDateTime::decode(&DbValue::Datetime((1, 2, 3, 4, 5, 6, 7))).unwrap(),
497            chrono::NaiveDateTime::new(date, time)
498        );
499
500        time = chrono::NaiveTime::from_hms_nano_opt(4, 5, 6, 8).unwrap();
501        assert_ne!(
502            NaiveDateTime::decode(&DbValue::Datetime((1, 2, 3, 4, 5, 6, 7))).unwrap(),
503            chrono::NaiveDateTime::new(date, time)
504        );
505        assert!(Option::<chrono::NaiveDateTime>::decode(&DbValue::DbNull)
506            .unwrap()
507            .is_none());
508    }
509
510    #[test]
511    fn timestamp() {
512        assert_eq!(
513            chrono::Duration::decode(&DbValue::Timestamp(1)).unwrap(),
514            chrono::Duration::seconds(1),
515        );
516        assert_ne!(
517            chrono::Duration::decode(&DbValue::Timestamp(2)).unwrap(),
518            chrono::Duration::seconds(1)
519        );
520        assert!(Option::<chrono::Duration>::decode(&DbValue::DbNull)
521            .unwrap()
522            .is_none());
523    }
524}
spin_sdk/pg3.rs

spin_sdk/
pg3.rs
