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(|err| FromSqlError::Other(Box::new(err)))
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")]
81#[cfg_attr(docsrs, doc(cfg(feature = "chrono")))]
82mod chrono;
83mod from_sql;
84#[cfg(feature = "jiff")]
85#[cfg_attr(docsrs, doc(cfg(feature = "jiff")))]
86mod jiff;
87#[cfg(feature = "serde_json")]
88#[cfg_attr(docsrs, doc(cfg(feature = "serde_json")))]
89mod serde_json;
90#[cfg(feature = "time")]
91#[cfg_attr(docsrs, doc(cfg(feature = "time")))]
92mod time;
93mod to_sql;
94#[cfg(feature = "url")]
95#[cfg_attr(docsrs, doc(cfg(feature = "url")))]
96mod url;
97mod value;
98mod value_ref;
99
100/// Empty struct that can be used to fill in a query parameter as `NULL`.
101///
102/// ## Example
103///
104/// ```rust,no_run
105/// # use rusqlite::{Connection, Result};
106/// # use rusqlite::types::{Null};
107///
108/// fn insert_null(conn: &Connection) -> Result<usize> {
109///     conn.execute("INSERT INTO people (name) VALUES (?1)", [Null])
110/// }
111/// ```
112#[derive(Copy, Clone)]
113pub struct Null;
114
115/// SQLite data types.
116/// See [Fundamental Datatypes](https://sqlite.org/c3ref/c_blob.html).
117#[derive(Copy, Clone, Debug, PartialEq, Eq)]
118pub enum Type {
119    /// NULL
120    Null,
121    /// 64-bit signed integer
122    Integer,
123    /// 64-bit IEEE floating point number
124    Real,
125    /// String
126    Text,
127    /// BLOB
128    Blob,
129}
130
131impl fmt::Display for Type {
132    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
133        match *self {
134            Self::Null => f.pad("Null"),
135            Self::Integer => f.pad("Integer"),
136            Self::Real => f.pad("Real"),
137            Self::Text => f.pad("Text"),
138            Self::Blob => f.pad("Blob"),
139        }
140    }
141}
142
143#[cfg(test)]
144mod test {
145    use super::Value;
146    use crate::{params, Connection, Error, Result, Statement};
147    use std::os::raw::{c_double, c_int};
148
149    fn checked_memory_handle() -> Result<Connection> {
150        let db = Connection::open_in_memory()?;
151        db.execute_batch("CREATE TABLE foo (b BLOB, t TEXT, i INTEGER, f FLOAT, n)")?;
152        Ok(db)
153    }
154
155    #[test]
156    fn test_blob() -> Result<()> {
157        let db = checked_memory_handle()?;
158
159        let v1234 = vec![1u8, 2, 3, 4];
160        db.execute("INSERT INTO foo(b) VALUES (?1)", [&v1234])?;
161
162        let v: Vec<u8> = db.one_column("SELECT b FROM foo")?;
163        assert_eq!(v, v1234);
164        Ok(())
165    }
166
167    #[test]
168    fn test_empty_blob() -> Result<()> {
169        let db = checked_memory_handle()?;
170
171        let empty = vec![];
172        db.execute("INSERT INTO foo(b) VALUES (?1)", [&empty])?;
173
174        let v: Vec<u8> = db.one_column("SELECT b FROM foo")?;
175        assert_eq!(v, empty);
176        Ok(())
177    }
178
179    #[test]
180    fn test_str() -> Result<()> {
181        let db = checked_memory_handle()?;
182
183        let s = "hello, world!";
184        db.execute("INSERT INTO foo(t) VALUES (?1)", [&s])?;
185
186        let from: String = db.one_column("SELECT t FROM foo")?;
187        assert_eq!(from, s);
188        Ok(())
189    }
190
191    #[test]
192    fn test_string() -> Result<()> {
193        let db = checked_memory_handle()?;
194
195        let s = "hello, world!";
196        db.execute("INSERT INTO foo(t) VALUES (?1)", [s.to_owned()])?;
197
198        let from: String = db.one_column("SELECT t FROM foo")?;
199        assert_eq!(from, s);
200        Ok(())
201    }
202
203    #[test]
204    fn test_value() -> Result<()> {
205        let db = checked_memory_handle()?;
206
207        db.execute("INSERT INTO foo(i) VALUES (?1)", [Value::Integer(10)])?;
208
209        assert_eq!(10i64, db.one_column::<i64>("SELECT i FROM foo")?);
210        Ok(())
211    }
212
213    #[test]
214    fn test_option() -> Result<()> {
215        let db = checked_memory_handle()?;
216
217        let s = "hello, world!";
218        let b = Some(vec![1u8, 2, 3, 4]);
219
220        db.execute("INSERT INTO foo(t) VALUES (?1)", [Some(s)])?;
221        db.execute("INSERT INTO foo(b) VALUES (?1)", [&b])?;
222
223        let mut stmt = db.prepare("SELECT t, b FROM foo ORDER BY ROWID ASC")?;
224        let mut rows = stmt.query([])?;
225
226        {
227            let row1 = rows.next()?.unwrap();
228            let s1: Option<String> = row1.get_unwrap(0);
229            let b1: Option<Vec<u8>> = row1.get_unwrap(1);
230            assert_eq!(s, s1.unwrap());
231            assert!(b1.is_none());
232        }
233
234        {
235            let row2 = rows.next()?.unwrap();
236            let s2: Option<String> = row2.get_unwrap(0);
237            let b2: Option<Vec<u8>> = row2.get_unwrap(1);
238            assert!(s2.is_none());
239            assert_eq!(b, b2);
240        }
241        Ok(())
242    }
243
244    #[test]
245    #[expect(clippy::cognitive_complexity)]
246    fn test_mismatched_types() -> Result<()> {
247        fn is_invalid_column_type(err: Error) -> bool {
248            matches!(err, Error::InvalidColumnType(..))
249        }
250
251        let db = checked_memory_handle()?;
252
253        db.execute(
254            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
255            [],
256        )?;
257
258        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
259        let mut rows = stmt.query([])?;
260
261        let row = rows.next()?.unwrap();
262
263        // check the correct types come back as expected
264        assert_eq!(vec![1, 2], row.get::<_, Vec<u8>>(0)?);
265        assert_eq!("text", row.get::<_, String>(1)?);
266        assert_eq!(1, row.get::<_, c_int>(2)?);
267        assert!((1.5 - row.get::<_, c_double>(3)?).abs() < f64::EPSILON);
268        assert_eq!(row.get::<_, Option<c_int>>(4)?, None);
269        assert_eq!(row.get::<_, Option<c_double>>(4)?, None);
270        assert_eq!(row.get::<_, Option<String>>(4)?, None);
271
272        // check some invalid types
273
274        // 0 is actually a blob (Vec<u8>)
275        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
276        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
277        assert!(is_invalid_column_type(row.get::<_, i64>(0).err().unwrap()));
278        assert!(is_invalid_column_type(
279            row.get::<_, c_double>(0).unwrap_err()
280        ));
281        assert!(is_invalid_column_type(row.get::<_, String>(0).unwrap_err()));
282        #[cfg(feature = "time")]
283        assert!(is_invalid_column_type(
284            row.get::<_, time::OffsetDateTime>(0).unwrap_err()
285        ));
286        assert!(is_invalid_column_type(
287            row.get::<_, Option<c_int>>(0).unwrap_err()
288        ));
289
290        // 1 is actually a text (String)
291        assert!(is_invalid_column_type(row.get::<_, c_int>(1).unwrap_err()));
292        assert!(is_invalid_column_type(row.get::<_, i64>(1).err().unwrap()));
293        assert!(is_invalid_column_type(
294            row.get::<_, c_double>(1).unwrap_err()
295        ));
296        assert!(is_invalid_column_type(
297            row.get::<_, Vec<u8>>(1).unwrap_err()
298        ));
299        assert!(is_invalid_column_type(
300            row.get::<_, Option<c_int>>(1).unwrap_err()
301        ));
302
303        // 2 is actually an integer
304        assert!(is_invalid_column_type(row.get::<_, String>(2).unwrap_err()));
305        assert!(is_invalid_column_type(
306            row.get::<_, Vec<u8>>(2).unwrap_err()
307        ));
308        assert!(is_invalid_column_type(
309            row.get::<_, Option<String>>(2).unwrap_err()
310        ));
311
312        // 3 is actually a float (c_double)
313        assert!(is_invalid_column_type(row.get::<_, c_int>(3).unwrap_err()));
314        assert!(is_invalid_column_type(row.get::<_, i64>(3).err().unwrap()));
315        assert!(is_invalid_column_type(row.get::<_, String>(3).unwrap_err()));
316        assert!(is_invalid_column_type(
317            row.get::<_, Vec<u8>>(3).unwrap_err()
318        ));
319        assert!(is_invalid_column_type(
320            row.get::<_, Option<c_int>>(3).unwrap_err()
321        ));
322
323        // 4 is actually NULL
324        assert!(is_invalid_column_type(row.get::<_, c_int>(4).unwrap_err()));
325        assert!(is_invalid_column_type(row.get::<_, i64>(4).err().unwrap()));
326        assert!(is_invalid_column_type(
327            row.get::<_, c_double>(4).unwrap_err()
328        ));
329        assert!(is_invalid_column_type(row.get::<_, String>(4).unwrap_err()));
330        assert!(is_invalid_column_type(
331            row.get::<_, Vec<u8>>(4).unwrap_err()
332        ));
333        #[cfg(feature = "time")]
334        assert!(is_invalid_column_type(
335            row.get::<_, time::OffsetDateTime>(4).unwrap_err()
336        ));
337        Ok(())
338    }
339
340    #[test]
341    fn test_dynamic_type() -> Result<()> {
342        use super::Value;
343        let db = checked_memory_handle()?;
344
345        db.execute(
346            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
347            [],
348        )?;
349
350        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
351        let mut rows = stmt.query([])?;
352
353        let row = rows.next()?.unwrap();
354        assert_eq!(Value::Blob(vec![1, 2]), row.get::<_, Value>(0)?);
355        assert_eq!(Value::Text(String::from("text")), row.get::<_, Value>(1)?);
356        assert_eq!(Value::Integer(1), row.get::<_, Value>(2)?);
357        match row.get::<_, Value>(3)? {
358            Value::Real(val) => assert!((1.5 - val).abs() < f64::EPSILON),
359            x => panic!("Invalid Value {x:?}"),
360        }
361        assert_eq!(Value::Null, row.get::<_, Value>(4)?);
362        Ok(())
363    }
364
365    macro_rules! test_conversion {
366        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect $expected_value:expr) => {
367            $db_etc.insert_statement.execute(params![$insert_value])?;
368            let res = $db_etc
369                .query_statement
370                .query_row([], |row| row.get::<_, $get_type>(0));
371            assert_eq!(res?, $expected_value);
372            $db_etc.delete_statement.execute([])?;
373        };
374        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_from_sql_error) => {
375            $db_etc.insert_statement.execute(params![$insert_value])?;
376            let res = $db_etc
377                .query_statement
378                .query_row([], |row| row.get::<_, $get_type>(0));
379            res.unwrap_err();
380            $db_etc.delete_statement.execute([])?;
381        };
382        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_to_sql_error) => {
383            $db_etc
384                .insert_statement
385                .execute(params![$insert_value])
386                .unwrap_err();
387        };
388    }
389
390    #[test]
391    #[expect(clippy::float_cmp)]
392    fn test_numeric_conversions() -> Result<()> {
393        // Test what happens when we store an f32 and retrieve an i32 etc.
394        let db = Connection::open_in_memory()?;
395        db.execute_batch("CREATE TABLE foo (x)")?;
396
397        // SQLite actually ignores the column types, so we just need to test
398        // different numeric values.
399
400        struct DbEtc<'conn> {
401            insert_statement: Statement<'conn>,
402            query_statement: Statement<'conn>,
403            delete_statement: Statement<'conn>,
404        }
405
406        let mut db_etc = DbEtc {
407            insert_statement: db.prepare("INSERT INTO foo VALUES (?1)")?,
408            query_statement: db.prepare("SELECT x FROM foo")?,
409            delete_statement: db.prepare("DELETE FROM foo")?,
410        };
411
412        // Basic non-converting test.
413        test_conversion!(db_etc, 0u8, u8, expect 0u8);
414
415        // In-range integral conversions.
416        test_conversion!(db_etc, 100u8, i8, expect 100i8);
417        test_conversion!(db_etc, 200u8, u8, expect 200u8);
418        test_conversion!(db_etc, 100u16, i8, expect 100i8);
419        test_conversion!(db_etc, 200u16, u8, expect 200u8);
420        test_conversion!(db_etc, u32::MAX, u64, expect u32::MAX as u64);
421        test_conversion!(db_etc, i64::MIN, i64, expect i64::MIN);
422        test_conversion!(db_etc, i64::MAX, i64, expect i64::MAX);
423        test_conversion!(db_etc, i64::MAX, u64, expect i64::MAX as u64);
424        test_conversion!(db_etc, 100usize, usize, expect 100usize);
425        test_conversion!(db_etc, 100u64, u64, expect 100u64);
426        test_conversion!(db_etc, i64::MAX as u64, u64, expect i64::MAX as u64);
427
428        // Out-of-range integral conversions.
429        test_conversion!(db_etc, 200u8, i8, expect_from_sql_error);
430        test_conversion!(db_etc, 400u16, i8, expect_from_sql_error);
431        test_conversion!(db_etc, 400u16, u8, expect_from_sql_error);
432        test_conversion!(db_etc, -1i8, u8, expect_from_sql_error);
433        test_conversion!(db_etc, i64::MIN, u64, expect_from_sql_error);
434        test_conversion!(db_etc, u64::MAX, i64, expect_to_sql_error);
435        test_conversion!(db_etc, u64::MAX, u64, expect_to_sql_error);
436        test_conversion!(db_etc, i64::MAX as u64 + 1, u64, expect_to_sql_error);
437
438        // FromSql integer to float, always works.
439        test_conversion!(db_etc, i64::MIN, f32, expect i64::MIN as f32);
440        test_conversion!(db_etc, i64::MAX, f32, expect i64::MAX as f32);
441        test_conversion!(db_etc, i64::MIN, f64, expect i64::MIN as f64);
442        test_conversion!(db_etc, i64::MAX, f64, expect i64::MAX as f64);
443
444        // FromSql float to int conversion, never works even if the actual value
445        // is an integer.
446        test_conversion!(db_etc, 0f64, i64, expect_from_sql_error);
447        Ok(())
448    }
449}