rusqlite/types/
mod.rs

1//! Traits dealing with SQLite data types.
2//!
3//! SQLite uses a [dynamic type system](https://www.sqlite.org/datatype3.html). Implementations of
4//! the [`ToSql`] and [`FromSql`] traits are provided for the basic types that
5//! SQLite provides methods for:
6//!
7//! * Strings (`String` and `&str`)
8//! * Blobs (`Vec<u8>` and `&[u8]`)
9//! * Numbers
10//!
11//! The number situation is a little complicated due to the fact that all
12//! numbers in SQLite are stored as `INTEGER` (`i64`) or `REAL` (`f64`).
13//!
14//! [`ToSql`] and [`FromSql`] are implemented for all primitive number types.
15//! [`FromSql`] has different behaviour depending on the SQL and Rust types, and
16//! the value.
17//!
18//! * `INTEGER` to integer: returns an
19//!   [`Error::IntegralValueOutOfRange`](crate::Error::IntegralValueOutOfRange)
20//!   error if the value does not fit in the Rust type.
21//! * `REAL` to integer: always returns an
22//!   [`Error::InvalidColumnType`](crate::Error::InvalidColumnType) error.
23//! * `INTEGER` to float: casts using `as` operator. Never fails.
24//! * `REAL` to float: casts using `as` operator. Never fails.
25//!
26//! [`ToSql`] always succeeds except when storing a `u64` or `usize` value that
27//! cannot fit in an `INTEGER` (`i64`). Also note that SQLite ignores column
28//! types, so if you store an `i64` in a column with type `REAL` it will be
29//! stored as an `INTEGER`, not a `REAL` (unless the column is part of a
30//! [STRICT table](https://www.sqlite.org/stricttables.html)).
31//!
32//! If the `time` feature is enabled, implementations are
33//! provided for `time::OffsetDateTime` that use the RFC 3339 date/time format,
34//! `"%Y-%m-%dT%H:%M:%S.%fZ"`, to store time values as strings.  These values
35//! can be parsed by SQLite's builtin
36//! [datetime](https://www.sqlite.org/lang_datefunc.html) functions.  If you
37//! want different storage for datetimes, you can use a newtype.
38#![cfg_attr(
39    feature = "time",
40    doc = r##"
41For example, to store datetimes as `i64`s counting the number of seconds since
42the Unix epoch:
43
44```
45use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
46use rusqlite::Result;
47
48pub struct DateTimeSql(pub time::OffsetDateTime);
49
50impl FromSql for DateTimeSql {
51    fn column_result(value: ValueRef) -> FromSqlResult<Self> {
52        i64::column_result(value).and_then(|as_i64| {
53            time::OffsetDateTime::from_unix_timestamp(as_i64)
54            .map(|odt| DateTimeSql(odt))
55            .map_err(FromSqlError::other)
56        })
57    }
58}
59
60impl ToSql for DateTimeSql {
61    fn to_sql(&self) -> Result<ToSqlOutput> {
62        Ok(self.0.unix_timestamp().into())
63    }
64}
65```
66
67"##
68)]
69//! [`ToSql`] and [`FromSql`] are also implemented for `Option<T>` where `T`
70//! implements [`ToSql`] or [`FromSql`] for the cases where you want to know if
71//! a value was NULL (which gets translated to `None`).
72
73pub use self::from_sql::{FromSql, FromSqlError, FromSqlResult};
74pub use self::to_sql::{ToSql, ToSqlOutput};
75pub use self::value::Value;
76pub use self::value_ref::ValueRef;
77
78use std::fmt;
79
80#[cfg(feature = "chrono")]
81mod chrono;
82mod from_sql;
83#[cfg(feature = "jiff")]
84mod jiff;
85#[cfg(feature = "serde_json")]
86mod serde_json;
87#[cfg(feature = "time")]
88mod time;
89mod to_sql;
90#[cfg(feature = "url")]
91mod url;
92mod value;
93mod value_ref;
94
95/// Empty struct that can be used to fill in a query parameter as `NULL`.
96///
97/// ## Example
98///
99/// ```rust,no_run
100/// # use rusqlite::{Connection, Result};
101/// # use rusqlite::types::{Null};
102///
103/// fn insert_null(conn: &Connection) -> Result<usize> {
104///     conn.execute("INSERT INTO people (name) VALUES (?1)", [Null])
105/// }
106/// ```
107#[derive(Copy, Clone)]
108pub struct Null;
109
110/// SQLite data types.
111/// See [Fundamental Datatypes](https://sqlite.org/c3ref/c_blob.html).
112#[derive(Copy, Clone, Debug, PartialEq, Eq)]
113pub enum Type {
114    /// NULL
115    Null,
116    /// 64-bit signed integer
117    Integer,
118    /// 64-bit IEEE floating point number
119    Real,
120    /// String
121    Text,
122    /// BLOB
123    Blob,
124}
125
126impl fmt::Display for Type {
127    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
128        match *self {
129            Self::Null => f.pad("Null"),
130            Self::Integer => f.pad("Integer"),
131            Self::Real => f.pad("Real"),
132            Self::Text => f.pad("Text"),
133            Self::Blob => f.pad("Blob"),
134        }
135    }
136}
137
138#[cfg(test)]
139mod test {
140    use super::Value;
141    use crate::{params, Connection, Error, Result, Statement};
142    use std::ffi::{c_double, c_int};
143
144    fn checked_memory_handle() -> Result<Connection> {
145        let db = Connection::open_in_memory()?;
146        db.execute_batch("CREATE TABLE foo (b BLOB, t TEXT, i INTEGER, f FLOAT, n)")?;
147        Ok(db)
148    }
149
150    #[test]
151    fn test_blob() -> Result<()> {
152        let db = checked_memory_handle()?;
153
154        let v1234 = vec![1u8, 2, 3, 4];
155        db.execute("INSERT INTO foo(b) VALUES (?1)", [&v1234])?;
156
157        let v: Vec<u8> = db.one_column("SELECT b FROM foo", [])?;
158        assert_eq!(v, v1234);
159        Ok(())
160    }
161
162    #[test]
163    fn test_empty_blob() -> Result<()> {
164        let db = checked_memory_handle()?;
165
166        let empty = vec![];
167        db.execute("INSERT INTO foo(b) VALUES (?1)", [&empty])?;
168
169        let v: Vec<u8> = db.one_column("SELECT b FROM foo", [])?;
170        assert_eq!(v, empty);
171        Ok(())
172    }
173
174    #[test]
175    fn test_str() -> Result<()> {
176        let db = checked_memory_handle()?;
177
178        let s = "hello, world!";
179        db.execute("INSERT INTO foo(t) VALUES (?1)", [&s])?;
180
181        let from: String = db.one_column("SELECT t FROM foo", [])?;
182        assert_eq!(from, s);
183        Ok(())
184    }
185
186    #[test]
187    fn test_string() -> Result<()> {
188        let db = checked_memory_handle()?;
189
190        let s = "hello, world!";
191        db.execute("INSERT INTO foo(t) VALUES (?1)", [s.to_owned()])?;
192
193        let from: String = db.one_column("SELECT t FROM foo", [])?;
194        assert_eq!(from, s);
195        Ok(())
196    }
197
198    #[test]
199    fn test_value() -> Result<()> {
200        let db = checked_memory_handle()?;
201
202        db.execute("INSERT INTO foo(i) VALUES (?1)", [Value::Integer(10)])?;
203
204        assert_eq!(10, db.one_column::<i64, _>("SELECT i FROM foo", [])?);
205        Ok(())
206    }
207
208    #[test]
209    fn test_option() -> Result<()> {
210        let db = checked_memory_handle()?;
211
212        let s = "hello, world!";
213        let b = Some(vec![1u8, 2, 3, 4]);
214
215        db.execute("INSERT INTO foo(t) VALUES (?1)", [Some(s)])?;
216        db.execute("INSERT INTO foo(b) VALUES (?1)", [&b])?;
217
218        let mut stmt = db.prepare("SELECT t, b FROM foo ORDER BY ROWID ASC")?;
219        let mut rows = stmt.query([])?;
220
221        {
222            let row1 = rows.next()?.unwrap();
223            let s1: Option<String> = row1.get_unwrap(0);
224            let b1: Option<Vec<u8>> = row1.get_unwrap(1);
225            assert_eq!(s, s1.unwrap());
226            assert!(b1.is_none());
227        }
228
229        {
230            let row2 = rows.next()?.unwrap();
231            let s2: Option<String> = row2.get_unwrap(0);
232            let b2: Option<Vec<u8>> = row2.get_unwrap(1);
233            assert!(s2.is_none());
234            assert_eq!(b, b2);
235        }
236        Ok(())
237    }
238
239    #[test]
240    #[expect(clippy::cognitive_complexity)]
241    fn test_mismatched_types() -> Result<()> {
242        fn is_invalid_column_type(err: Error) -> bool {
243            matches!(err, Error::InvalidColumnType(..))
244        }
245
246        let db = checked_memory_handle()?;
247
248        db.execute(
249            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
250            [],
251        )?;
252
253        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
254        let mut rows = stmt.query([])?;
255
256        let row = rows.next()?.unwrap();
257
258        // check the correct types come back as expected
259        assert_eq!(vec![1, 2], row.get::<_, Vec<u8>>(0)?);
260        assert_eq!("text", row.get::<_, String>(1)?);
261        assert_eq!(1, row.get::<_, c_int>(2)?);
262        assert!((1.5 - row.get::<_, c_double>(3)?).abs() < f64::EPSILON);
263        assert_eq!(row.get::<_, Option<c_int>>(4)?, None);
264        assert_eq!(row.get::<_, Option<c_double>>(4)?, None);
265        assert_eq!(row.get::<_, Option<String>>(4)?, None);
266
267        // check some invalid types
268
269        // 0 is actually a blob (Vec<u8>)
270        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
271        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
272        assert!(is_invalid_column_type(row.get::<_, i64>(0).err().unwrap()));
273        assert!(is_invalid_column_type(
274            row.get::<_, c_double>(0).unwrap_err()
275        ));
276        assert!(is_invalid_column_type(row.get::<_, String>(0).unwrap_err()));
277        #[cfg(feature = "time")]
278        assert!(is_invalid_column_type(
279            row.get::<_, time::OffsetDateTime>(0).unwrap_err()
280        ));
281        assert!(is_invalid_column_type(
282            row.get::<_, Option<c_int>>(0).unwrap_err()
283        ));
284
285        // 1 is actually a text (String)
286        assert!(is_invalid_column_type(row.get::<_, c_int>(1).unwrap_err()));
287        assert!(is_invalid_column_type(row.get::<_, i64>(1).err().unwrap()));
288        assert!(is_invalid_column_type(
289            row.get::<_, c_double>(1).unwrap_err()
290        ));
291        assert!(is_invalid_column_type(
292            row.get::<_, Vec<u8>>(1).unwrap_err()
293        ));
294        assert!(is_invalid_column_type(
295            row.get::<_, Option<c_int>>(1).unwrap_err()
296        ));
297
298        // 2 is actually an integer
299        assert!(is_invalid_column_type(row.get::<_, String>(2).unwrap_err()));
300        assert!(is_invalid_column_type(
301            row.get::<_, Vec<u8>>(2).unwrap_err()
302        ));
303        assert!(is_invalid_column_type(
304            row.get::<_, Option<String>>(2).unwrap_err()
305        ));
306
307        // 3 is actually a float (c_double)
308        assert!(is_invalid_column_type(row.get::<_, c_int>(3).unwrap_err()));
309        assert!(is_invalid_column_type(row.get::<_, i64>(3).err().unwrap()));
310        assert!(is_invalid_column_type(row.get::<_, String>(3).unwrap_err()));
311        assert!(is_invalid_column_type(
312            row.get::<_, Vec<u8>>(3).unwrap_err()
313        ));
314        assert!(is_invalid_column_type(
315            row.get::<_, Option<c_int>>(3).unwrap_err()
316        ));
317
318        // 4 is actually NULL
319        assert!(is_invalid_column_type(row.get::<_, c_int>(4).unwrap_err()));
320        assert!(is_invalid_column_type(row.get::<_, i64>(4).err().unwrap()));
321        assert!(is_invalid_column_type(
322            row.get::<_, c_double>(4).unwrap_err()
323        ));
324        assert!(is_invalid_column_type(row.get::<_, String>(4).unwrap_err()));
325        assert!(is_invalid_column_type(
326            row.get::<_, Vec<u8>>(4).unwrap_err()
327        ));
328        #[cfg(feature = "time")]
329        assert!(is_invalid_column_type(
330            row.get::<_, time::OffsetDateTime>(4).unwrap_err()
331        ));
332        Ok(())
333    }
334
335    #[test]
336    fn test_dynamic_type() -> Result<()> {
337        use super::Value;
338        let db = checked_memory_handle()?;
339
340        db.execute(
341            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
342            [],
343        )?;
344
345        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
346        let mut rows = stmt.query([])?;
347
348        let row = rows.next()?.unwrap();
349        assert_eq!(Value::Blob(vec![1, 2]), row.get::<_, Value>(0)?);
350        assert_eq!(Value::Text(String::from("text")), row.get::<_, Value>(1)?);
351        assert_eq!(Value::Integer(1), row.get::<_, Value>(2)?);
352        match row.get::<_, Value>(3)? {
353            Value::Real(val) => assert!((1.5 - val).abs() < f64::EPSILON),
354            x => panic!("Invalid Value {x:?}"),
355        }
356        assert_eq!(Value::Null, row.get::<_, Value>(4)?);
357        Ok(())
358    }
359
360    macro_rules! test_conversion {
361        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect $expected_value:expr) => {
362            $db_etc.insert_statement.execute(params![$insert_value])?;
363            let res = $db_etc
364                .query_statement
365                .query_row([], |row| row.get::<_, $get_type>(0));
366            assert_eq!(res?, $expected_value);
367            $db_etc.delete_statement.execute([])?;
368        };
369        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_from_sql_error) => {
370            $db_etc.insert_statement.execute(params![$insert_value])?;
371            let res = $db_etc
372                .query_statement
373                .query_row([], |row| row.get::<_, $get_type>(0));
374            res.unwrap_err();
375            $db_etc.delete_statement.execute([])?;
376        };
377        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_to_sql_error) => {
378            $db_etc
379                .insert_statement
380                .execute(params![$insert_value])
381                .unwrap_err();
382        };
383    }
384
385    #[test]
386    #[expect(clippy::float_cmp)]
387    fn test_numeric_conversions() -> Result<()> {
388        // Test what happens when we store an f32 and retrieve an i32 etc.
389        let db = Connection::open_in_memory()?;
390        db.execute_batch("CREATE TABLE foo (x)")?;
391
392        // SQLite actually ignores the column types, so we just need to test
393        // different numeric values.
394
395        struct DbEtc<'conn> {
396            insert_statement: Statement<'conn>,
397            query_statement: Statement<'conn>,
398            delete_statement: Statement<'conn>,
399        }
400
401        let mut db_etc = DbEtc {
402            insert_statement: db.prepare("INSERT INTO foo VALUES (?1)")?,
403            query_statement: db.prepare("SELECT x FROM foo")?,
404            delete_statement: db.prepare("DELETE FROM foo")?,
405        };
406
407        // Basic non-converting test.
408        test_conversion!(db_etc, 0u8, u8, expect 0u8);
409
410        // In-range integral conversions.
411        test_conversion!(db_etc, 100u8, i8, expect 100i8);
412        test_conversion!(db_etc, 200u8, u8, expect 200u8);
413        test_conversion!(db_etc, 100u16, i8, expect 100i8);
414        test_conversion!(db_etc, 200u16, u8, expect 200u8);
415        test_conversion!(db_etc, u32::MAX, u64, expect u32::MAX as u64);
416        test_conversion!(db_etc, i64::MIN, i64, expect i64::MIN);
417        test_conversion!(db_etc, i64::MAX, i64, expect i64::MAX);
418        test_conversion!(db_etc, i64::MAX, u64, expect i64::MAX as u64);
419        test_conversion!(db_etc, 100usize, usize, expect 100usize);
420        test_conversion!(db_etc, 100u64, u64, expect 100u64);
421        test_conversion!(db_etc, i64::MAX as u64, u64, expect i64::MAX as u64);
422
423        // Out-of-range integral conversions.
424        test_conversion!(db_etc, 200u8, i8, expect_from_sql_error);
425        test_conversion!(db_etc, 400u16, i8, expect_from_sql_error);
426        test_conversion!(db_etc, 400u16, u8, expect_from_sql_error);
427        test_conversion!(db_etc, -1i8, u8, expect_from_sql_error);
428        test_conversion!(db_etc, i64::MIN, u64, expect_from_sql_error);
429        test_conversion!(db_etc, u64::MAX, i64, expect_to_sql_error);
430        test_conversion!(db_etc, u64::MAX, u64, expect_to_sql_error);
431        test_conversion!(db_etc, i64::MAX as u64 + 1, u64, expect_to_sql_error);
432
433        // FromSql integer to float, always works.
434        test_conversion!(db_etc, i64::MIN, f32, expect i64::MIN as f32);
435        test_conversion!(db_etc, i64::MAX, f32, expect i64::MAX as f32);
436        test_conversion!(db_etc, i64::MIN, f64, expect i64::MIN as f64);
437        test_conversion!(db_etc, i64::MAX, f64, expect i64::MAX as f64);
438
439        // FromSql float to int conversion, never works even if the actual value
440        // is an integer.
441        test_conversion!(db_etc, 0f64, i64, expect_from_sql_error);
442        Ok(())
443    }
444}
rusqlite/types/mod.rs

rusqlite/types/
mod.rs
