Data types
SQLite only has four primitive data types: INTEGER, REAL, TEXT, and BLOB. APIs that return database values as an object will only ever return one of these four types. Additional .NET types are supported by Microsoft.Data.Sqlite, but values are ultimately coerced between these types and one of the four primitive types.
| .NET | SQLite | Remarks |
|---|---|---|
| Boolean | INTEGER | 0 or 1 |
| Byte | INTEGER | |
| Byte[] | BLOB | |
| Char | TEXT | UTF-8 |
| DateOnly | TEXT | yyyy-MM-dd |
| DateTime | TEXT | yyyy-MM-dd HH:mm:ss.FFFFFFF |
| DateTimeOffset | TEXT | yyyy-MM-dd HH:mm:ss.FFFFFFFzzz |
| Decimal | TEXT | 0.0########################### format. REAL would be lossy. |
| Double | REAL | |
| Guid | TEXT | 00000000-0000-0000-0000-000000000000 |
| Int16 | INTEGER | |
| Int32 | INTEGER | |
| Int64 | INTEGER | |
| SByte | INTEGER | |
| Single | REAL | |
| String | TEXT | UTF-8 |
| TimeOnly | TEXT | HH:mm:ss.fffffff |
| TimeSpan | TEXT | d.hh:mm:ss.fffffff |
| UInt16 | INTEGER | |
| UInt32 | INTEGER | |
| UInt64 | INTEGER | Large values overflow |
Alternative types
Some .NET types can be read from alternative SQLite types. Parameters can also be configured to use these alternative types. For more information, see Parameters.
| .NET | SQLite | Remarks |
|---|---|---|
| Char | INTEGER | UTF-16 |
| DateOnly | REAL | Julian day value |
| DateTime | REAL | Julian day value |
| DateTimeOffset | REAL | Julian day value |
| Guid | BLOB | |
| TimeOnly | REAL | In days |
| TimeSpan | REAL | In days |
For example, the following query reads a TimeSpan value from a REAL column in the result set.
command.CommandText =
@"
SELECT name, julianday(finished) - julianday(started) AS length
FROM task
WHERE finished IS NOT NULL
";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetString(0);
var length = reader.GetTimeSpan(1);
Console.WriteLine($"'{name}' took {length}.");
}
}
Column types
SQLite uses a dynamic type system where the type of a value is associated with the value itself and not the column where it's stored. You're free to use whatever column type name you want. Microsoft.Data.Sqlite won't apply any additional semantics to these names.
The column type name does have an impact on the type affinity. One common gotcha is that using a column type of STRING will try to convert values to INTEGER or REAL, which can lead to unexpected results. We recommend only using the four primitive SQLite type names: INTEGER, REAL, TEXT, and BLOB.
SQLite allows you to specify type facets like length, precision, and scale, but they are not enforced by the database engine. Your app is responsible for enforcing these.
