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    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
141    use wasm_bindgen_test::wasm_bindgen_test as test;
142
143    use super::Value;
144    use crate::{params, Connection, Error, Result, Statement};
145    use std::ffi::{c_double, c_int};
146
147    fn checked_memory_handle() -> Result<Connection> {
148        let db = Connection::open_in_memory()?;
149        db.execute_batch("CREATE TABLE foo (b BLOB, t TEXT, i INTEGER, f FLOAT, n)")?;
150        Ok(db)
151    }
152
153    #[test]
154    fn test_blob() -> Result<()> {
155        let db = checked_memory_handle()?;
156
157        let v1234 = vec![1u8, 2, 3, 4];
158        db.execute("INSERT INTO foo(b) VALUES (?1)", [&v1234])?;
159
160        let v: Vec<u8> = db.one_column("SELECT b FROM foo", [])?;
161        assert_eq!(v, v1234);
162        Ok(())
163    }
164
165    #[test]
166    fn test_empty_blob() -> Result<()> {
167        let db = checked_memory_handle()?;
168
169        let empty = vec![];
170        db.execute("INSERT INTO foo(b) VALUES (?1)", [&empty])?;
171
172        let v: Vec<u8> = db.one_column("SELECT b FROM foo", [])?;
173        assert_eq!(v, empty);
174        Ok(())
175    }
176
177    #[test]
178    fn test_str() -> Result<()> {
179        let db = checked_memory_handle()?;
180
181        let s = "hello, world!";
182        db.execute("INSERT INTO foo(t) VALUES (?1)", [&s])?;
183
184        let from: String = db.one_column("SELECT t FROM foo", [])?;
185        assert_eq!(from, s);
186        Ok(())
187    }
188
189    #[test]
190    fn test_string() -> Result<()> {
191        let db = checked_memory_handle()?;
192
193        let s = "hello, world!";
194        db.execute("INSERT INTO foo(t) VALUES (?1)", [s.to_owned()])?;
195
196        let from: String = db.one_column("SELECT t FROM foo", [])?;
197        assert_eq!(from, s);
198        Ok(())
199    }
200
201    #[test]
202    fn test_value() -> Result<()> {
203        let db = checked_memory_handle()?;
204
205        db.execute("INSERT INTO foo(i) VALUES (?1)", [Value::Integer(10)])?;
206
207        assert_eq!(10, db.one_column::<i64, _>("SELECT i FROM foo", [])?);
208        Ok(())
209    }
210
211    #[test]
212    fn test_option() -> Result<()> {
213        let db = checked_memory_handle()?;
214
215        let s = "hello, world!";
216        let b = Some(vec![1u8, 2, 3, 4]);
217
218        db.execute("INSERT INTO foo(t) VALUES (?1)", [Some(s)])?;
219        db.execute("INSERT INTO foo(b) VALUES (?1)", [&b])?;
220
221        let mut stmt = db.prepare("SELECT t, b FROM foo ORDER BY ROWID ASC")?;
222        let mut rows = stmt.query([])?;
223
224        {
225            let row1 = rows.next()?.unwrap();
226            let s1: Option<String> = row1.get_unwrap(0);
227            let b1: Option<Vec<u8>> = row1.get_unwrap(1);
228            assert_eq!(s, s1.unwrap());
229            assert!(b1.is_none());
230        }
231
232        {
233            let row2 = rows.next()?.unwrap();
234            let s2: Option<String> = row2.get_unwrap(0);
235            let b2: Option<Vec<u8>> = row2.get_unwrap(1);
236            assert!(s2.is_none());
237            assert_eq!(b, b2);
238        }
239        Ok(())
240    }
241
242    #[test]
243    #[expect(clippy::cognitive_complexity)]
244    fn test_mismatched_types() -> Result<()> {
245        fn is_invalid_column_type(err: Error) -> bool {
246            matches!(err, Error::InvalidColumnType(..))
247        }
248
249        let db = checked_memory_handle()?;
250
251        db.execute(
252            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
253            [],
254        )?;
255
256        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
257        let mut rows = stmt.query([])?;
258
259        let row = rows.next()?.unwrap();
260
261        // check the correct types come back as expected
262        assert_eq!(vec![1, 2], row.get::<_, Vec<u8>>(0)?);
263        assert_eq!("text", row.get::<_, String>(1)?);
264        assert_eq!(1, row.get::<_, c_int>(2)?);
265        assert!((1.5 - row.get::<_, c_double>(3)?).abs() < f64::EPSILON);
266        assert_eq!(row.get::<_, Option<c_int>>(4)?, None);
267        assert_eq!(row.get::<_, Option<c_double>>(4)?, None);
268        assert_eq!(row.get::<_, Option<String>>(4)?, None);
269
270        // check some invalid types
271
272        // 0 is actually a blob (Vec<u8>)
273        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
274        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
275        assert!(is_invalid_column_type(row.get::<_, i64>(0).err().unwrap()));
276        assert!(is_invalid_column_type(
277            row.get::<_, c_double>(0).unwrap_err()
278        ));
279        assert!(is_invalid_column_type(row.get::<_, String>(0).unwrap_err()));
280        #[cfg(feature = "time")]
281        assert!(is_invalid_column_type(
282            row.get::<_, time::OffsetDateTime>(0).unwrap_err()
283        ));
284        assert!(is_invalid_column_type(
285            row.get::<_, Option<c_int>>(0).unwrap_err()
286        ));
287
288        // 1 is actually a text (String)
289        assert!(is_invalid_column_type(row.get::<_, c_int>(1).unwrap_err()));
290        assert!(is_invalid_column_type(row.get::<_, i64>(1).err().unwrap()));
291        assert!(is_invalid_column_type(
292            row.get::<_, c_double>(1).unwrap_err()
293        ));
294        assert!(is_invalid_column_type(
295            row.get::<_, Vec<u8>>(1).unwrap_err()
296        ));
297        assert!(is_invalid_column_type(
298            row.get::<_, Option<c_int>>(1).unwrap_err()
299        ));
300
301        // 2 is actually an integer
302        assert!(is_invalid_column_type(row.get::<_, String>(2).unwrap_err()));
303        assert!(is_invalid_column_type(
304            row.get::<_, Vec<u8>>(2).unwrap_err()
305        ));
306        assert!(is_invalid_column_type(
307            row.get::<_, Option<String>>(2).unwrap_err()
308        ));
309
310        // 3 is actually a float (c_double)
311        assert!(is_invalid_column_type(row.get::<_, c_int>(3).unwrap_err()));
312        assert!(is_invalid_column_type(row.get::<_, i64>(3).err().unwrap()));
313        assert!(is_invalid_column_type(row.get::<_, String>(3).unwrap_err()));
314        assert!(is_invalid_column_type(
315            row.get::<_, Vec<u8>>(3).unwrap_err()
316        ));
317        assert!(is_invalid_column_type(
318            row.get::<_, Option<c_int>>(3).unwrap_err()
319        ));
320
321        // 4 is actually NULL
322        assert!(is_invalid_column_type(row.get::<_, c_int>(4).unwrap_err()));
323        assert!(is_invalid_column_type(row.get::<_, i64>(4).err().unwrap()));
324        assert!(is_invalid_column_type(
325            row.get::<_, c_double>(4).unwrap_err()
326        ));
327        assert!(is_invalid_column_type(row.get::<_, String>(4).unwrap_err()));
328        assert!(is_invalid_column_type(
329            row.get::<_, Vec<u8>>(4).unwrap_err()
330        ));
331        #[cfg(feature = "time")]
332        assert!(is_invalid_column_type(
333            row.get::<_, time::OffsetDateTime>(4).unwrap_err()
334        ));
335        Ok(())
336    }
337
338    #[test]
339    fn test_dynamic_type() -> Result<()> {
340        use super::Value;
341        let db = checked_memory_handle()?;
342
343        db.execute(
344            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
345            [],
346        )?;
347
348        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
349        let mut rows = stmt.query([])?;
350
351        let row = rows.next()?.unwrap();
352        assert_eq!(Value::Blob(vec![1, 2]), row.get::<_, Value>(0)?);
353        assert_eq!(Value::Text(String::from("text")), row.get::<_, Value>(1)?);
354        assert_eq!(Value::Integer(1), row.get::<_, Value>(2)?);
355        match row.get::<_, Value>(3)? {
356            Value::Real(val) => assert!((1.5 - val).abs() < f64::EPSILON),
357            x => panic!("Invalid Value {x:?}"),
358        }
359        assert_eq!(Value::Null, row.get::<_, Value>(4)?);
360        Ok(())
361    }
362
363    macro_rules! test_conversion {
364        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect $expected_value:expr) => {
365            $db_etc.insert_statement.execute(params![$insert_value])?;
366            let res = $db_etc
367                .query_statement
368                .query_row([], |row| row.get::<_, $get_type>(0));
369            assert_eq!(res?, $expected_value);
370            $db_etc.delete_statement.execute([])?;
371        };
372        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_from_sql_error) => {
373            $db_etc.insert_statement.execute(params![$insert_value])?;
374            let res = $db_etc
375                .query_statement
376                .query_row([], |row| row.get::<_, $get_type>(0));
377            res.unwrap_err();
378            $db_etc.delete_statement.execute([])?;
379        };
380        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_to_sql_error) => {
381            $db_etc
382                .insert_statement
383                .execute(params![$insert_value])
384                .unwrap_err();
385        };
386    }
387
388    #[test]
389    #[expect(clippy::float_cmp)]
390    fn test_numeric_conversions() -> Result<()> {
391        // Test what happens when we store a f32 and retrieve an i32 etc.
392        let db = Connection::open_in_memory()?;
393        db.execute_batch("CREATE TABLE foo (x)")?;
394
395        // SQLite actually ignores the column types, so we just need to test
396        // different numeric values.
397
398        struct DbEtc<'conn> {
399            insert_statement: Statement<'conn>,
400            query_statement: Statement<'conn>,
401            delete_statement: Statement<'conn>,
402        }
403
404        let mut db_etc = DbEtc {
405            insert_statement: db.prepare("INSERT INTO foo VALUES (?1)")?,
406            query_statement: db.prepare("SELECT x FROM foo")?,
407            delete_statement: db.prepare("DELETE FROM foo")?,
408        };
409
410        // Basic non-converting test.
411        test_conversion!(db_etc, 0u8, u8, expect 0u8);
412
413        // In-range integral conversions.
414        test_conversion!(db_etc, 100u8, i8, expect 100i8);
415        test_conversion!(db_etc, 200u8, u8, expect 200u8);
416        test_conversion!(db_etc, 100u16, i8, expect 100i8);
417        test_conversion!(db_etc, 200u16, u8, expect 200u8);
418        #[cfg(feature = "fallible_uint")]
419        test_conversion!(db_etc, u32::MAX, u64, expect u32::MAX as u64);
420
421        test_conversion!(db_etc, i64::MIN, i64, expect i64::MIN);
422        test_conversion!(db_etc, i64::MAX, i64, expect i64::MAX);
423        #[cfg(feature = "fallible_uint")]
424        test_conversion!(db_etc, i64::MAX, u64, expect i64::MAX as u64);
425        #[cfg(feature = "fallible_uint")]
426        test_conversion!(db_etc, 100usize, usize, expect 100usize);
427        #[cfg(feature = "fallible_uint")]
428        test_conversion!(db_etc, 100u64, u64, expect 100u64);
429        #[cfg(feature = "fallible_uint")]
430        test_conversion!(db_etc, i64::MAX as u64, u64, expect i64::MAX as u64);
431
432        // Out-of-range integral conversions.
433        test_conversion!(db_etc, 200u8, i8, expect_from_sql_error);
434        test_conversion!(db_etc, 400u16, i8, expect_from_sql_error);
435        test_conversion!(db_etc, 400u16, u8, expect_from_sql_error);
436        test_conversion!(db_etc, -1i8, u8, expect_from_sql_error);
437        #[cfg(feature = "fallible_uint")]
438        test_conversion!(db_etc, i64::MIN, u64, expect_from_sql_error);
439        #[cfg(feature = "fallible_uint")]
440        test_conversion!(db_etc, u64::MAX, i64, expect_to_sql_error);
441        #[cfg(feature = "fallible_uint")]
442        test_conversion!(db_etc, u64::MAX, u64, expect_to_sql_error);
443        #[cfg(feature = "fallible_uint")]
444        test_conversion!(db_etc, i64::MAX as u64 + 1, u64, expect_to_sql_error);
445
446        // FromSql integer to float, always works.
447        test_conversion!(db_etc, i64::MIN, f32, expect i64::MIN as f32);
448        test_conversion!(db_etc, i64::MAX, f32, expect i64::MAX as f32);
449        test_conversion!(db_etc, i64::MIN, f64, expect i64::MIN as f64);
450        test_conversion!(db_etc, i64::MAX, f64, expect i64::MAX as f64);
451
452        // FromSql float to int conversion, never works even if the actual value
453        // is an integer.
454        test_conversion!(db_etc, 0f64, i64, expect_from_sql_error);
455        Ok(())
456    }
457}
rusqlite/types/mod.rs

rusqlite/types/
mod.rs
