Module sqlx_sqlite::types
source · Expand description
Conversions between Rust and SQLite types.
§Types
Rust type | SQLite type(s) |
---|---|
bool | BOOLEAN |
i8 | INTEGER |
i16 | INTEGER |
i32 | INTEGER |
i64 | BIGINT, INT8 |
u8 | INTEGER |
u16 | INTEGER |
u32 | INTEGER |
f32 | REAL |
f64 | REAL |
&str , String | TEXT |
&[u8] , Vec<u8> | BLOB |
§Note: Unsigned Integers
The unsigned integer types u8
, u16
and u32
are implemented by zero-extending to the
next-larger signed type. So u8
becomes i16
, u16
becomes i32
, and u32
becomes i64
while still retaining their semantic values.
Similarly, decoding performs a checked truncation to ensure that overflow does not occur.
SQLite stores integers in a variable-width encoding and always handles them in memory as 64-bit signed values, so no space is wasted by this implicit widening.
However, there is no corresponding larger type for u64
in SQLite (it would require a i128
),
and so it is not supported. Bit-casting it to i64
or storing it as REAL
, BLOB
or TEXT
would change the semantics of the value in SQL and so violates the principle of least surprise.
§chrono
Requires the chrono
Cargo feature flag.
Rust type | Sqlite type(s) |
---|---|
chrono::NaiveDateTime | DATETIME (TEXT, INTEGER, REAL) |
chrono::DateTime<Utc> | DATETIME (TEXT, INTEGER, REAL) |
chrono::DateTime<Local> | DATETIME (TEXT, INTEGER, REAL) |
chrono::DateTime<FixedOffset> | DATETIME (TEXT, INTEGER, REAL) |
chrono::NaiveDate | DATE (TEXT only) |
chrono::NaiveTime | TIME (TEXT only) |
§NOTE: DATETIME
conversions
SQLite may represent DATETIME
values as one of three types: TEXT
, REAL
, or INTEGER
.
Which one is used is entirely up to you and how you store timestamps in your database.
The deserialization for NaiveDateTime
, DateTime<Utc>
and DateTime<Local>
infer the date
format from the type of the value they’re being decoded from:
- If
TEXT
, the format is assumed to be an ISO-8601 compatible datetime string. A number of possible formats are tried; seesqlx-sqlite/src/types/chrono.rs
for the current set of formats. - If
INTEGER
, it is expected to be the number of seconds since January 1, 1970 00:00 UTC, as if returned from theunixtime()
function (without thesubsec
modifier). - If
REAL
, it is expected to be the (possibly fractional) number of days since the Julian epoch, November 24, 4714 BCE 12:00 UTC, as if returned from thejulianday()
function.
These types will always encode to a datetime string, either
with (DateTime<Tz>
for any Tz: TimeZone
) or without (NaiveDateTime
) a timezone offset.
§time
Requires the time
Cargo feature flag.
Rust type | Sqlite type(s) |
---|---|
time::PrimitiveDateTime | DATETIME (TEXT, INTEGER) |
time::OffsetDateTime | DATETIME (TEXT, INTEGER) |
time::Date | DATE (TEXT only) |
time::Time | TIME (TEXT only) |
§NOTE: DATETIME
conversions
The behavior here is identical to the corresponding chrono
types, minus the support for REAL
values as Julian days (it’s just not implemented).
§uuid
Requires the uuid
Cargo feature flag.
Rust type | Sqlite type(s) |
---|---|
uuid::Uuid | BLOB, TEXT |
uuid::fmt::Hyphenated | TEXT |
uuid::fmt::Simple | TEXT |
§json
Requires the json
Cargo feature flag.
Rust type | Sqlite type(s) |
---|---|
[Json<T> ] | TEXT |
serde_json::JsonValue | TEXT |
&serde_json::value::RawValue | TEXT |
§Nullable
In addition, Option<T>
is supported where T
implements Type
. An Option<T>
represents
a potentially NULL
value from SQLite.
§Non-feature: NUMERIC
/ rust_decimal
/ bigdecimal
Support
Support for mapping rust_decimal::Decimal
and bigdecimal::BigDecimal
to SQLite has been
deliberately omitted because SQLite does not have native support for high-
or arbitrary-precision decimal arithmetic, and to pretend so otherwise would be a
significant misstep in API design.
The in-tree decimal.c
extension is unfortunately not included in the amalgamation,
which is used to build the bundled version of SQLite3 for libsqlite3-sys
(which we have
enabled by default for the simpler setup experience), otherwise we could support that.
The NUMERIC
type affinity, while seemingly designed for storing decimal values,
stores non-integer real numbers as double-precision IEEE-754 floating point,
i.e. REAL
in SQLite, f64
in Rust, double
in C/C++, etc.
Datatypes in SQLite: Type Affinity (accessed 2023/11/20):
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved.
With the SQLite3 interactive CLI, we can see that a higher-precision value (20 digits in this case) is rounded off:
sqlite> CREATE TABLE foo(bar NUMERIC);
sqlite> INSERT INTO foo(bar) VALUES('1.2345678901234567890');
sqlite> SELECT * FROM foo;
1.23456789012346
It appears the TEXT
storage class is only used if the value contains invalid characters
or extra whitespace.
Thus, the NUMERIC
type affinity is unsuitable for storage of high-precision decimal values
and should be avoided at all costs.
Support for rust_decimal
and bigdecimal
would only be a trap because users will naturally
want to use the NUMERIC
type affinity, and might otherwise encounter serious bugs caused by
rounding errors that they were deliberately avoiding when they chose an arbitrary-precision type
over a floating-point type in the first place.
Instead, you should only use a type affinity that SQLite will not attempt to convert implicitly,
such as TEXT
or BLOB
, and map values to/from SQLite as strings. You can do this easily
using the Text
adapter.