Data types in SQLite are different compared to other database management system. In SQLite, you can declare data types normally, but you still can store any value in any data type.
In this tutorial, you will learn-
SQLite is typed less. There are no data types, you can store any type of data you like in any column. This is called dynamic types.
In static types, like in other database management systems, if you declared a column with a data type integer, you can only insert values of data type integer. However, in dynamic types like in SQLite, the type of the column is determined by the value inserted. And then SQLite stores that value depending on its type.
SQLite Storage Classes
In SQLite there are different storage methods depending on the type of value, these different storage methods are called storage classes in SQLite.
The following are the storage classes available in SQLite:
- NULL – this storage class is used to store any NULL value.
- INTEGER – any numeric value is stored as a signed integer value (It can hold both positive and negative integer values). The INTEGER values in SQLite are stored in either 1, 2, 3, 4, 6, or 8 bytes of storage depending on the value of the number.
- REAL – this storage class is used to store the floating point values, and they are stored in an 8-bytes of storage.
- TEXT – stores text strings. It also supports different encoding like UTF-8, UTF-16 BE, or UTF-26LE.
- BLOB – used to store large files, like images or text files. The value is stored as byte array the same as the input value.
SQLite Affinity Type
Type affinity is the recommended type of data stored in a column. However, you still can store any type of data as you wish, these types are recommended not required.
These types were introduced in SQLite to maximize the compatibility between SQLite and other database management system.
Any column declared in an SQLite database is assigned a type affinity depending on it declared data type. Here the lift of type affinities in SQLite:
- TEXT.
- NUMERIC.
- INTEGER.
- REAL.
- BLOB.
Here's how SQLite determines the affinity of the column from its declared data type:
- INTEGER affinity is assigned if the declared type contains the string "INT".
- TEXT affinity is assigned, if the column contains on its data type one of the following strings "TEXT", "CHAR, or "CLOB". For example, the type VARCHAR will be assigned the TEXT affinity.
- BLOB affinity is assigned if the column has no type specified or the data type is a BLOB.
- REAL affinity is assigned if the type contains one of the following strings "DOUB", "REAL, or "FLOAT".
- NUMERIC affinity is assigned for any other data type.
There is also a table on the same page showing some examples for the mapping between SQLite data types and their affinities determined by these rules:
Examples of Storing Data types in SQLite:
Storing number with SQLite integer:
Any column of data type contains the "INT" word, it will be assigned an INTEGER type affinity. It will be stored in an INTEGER storage class.
All the following data types are assigned as an INTEGER type affinity:
- INT, INTEGER, BIGINT.
- INT2, INT4, INT8.
- TINYINT, SMALLINT, MEDIUM INT.
INTEGER type affinity in SQLite can hold any assigned integer number (positive or negative) from 1 byte to maximum 8 bytes.
Storing numbers with SQLite REAL:
REAL numbers are the number with double floating points precision. SQLite stored real numbers as 8 bytes' array. Here is the list of data types in SQLite that you can use to store REAL numbers:
- REAL.
- DOUBLE.
- DOUBLE PRECISION.
- FLOAT.
Storing large data with SQLite BLOB:
There is only one way to store large files into a SQLite database, and it is using the BLOB data type. This data type is used to store large files like images, files (from any type), etc. The file is converted into bytes array and then stored in the same size as the input file.
Storing SQLite Booleans:
SQLite doesn't have a separate BOOLEAN storage class. However, the BOOLEAN values are stored as INTEGERS with values 0 (as false) or 1 (as true).
Storing SQLite dates and times:
You can declare date or date times in SQLite using one of the following data types:
- DATE
- DATETIME
- TIMESTAMP
- TIME
Note that, there is no separate DATE or DATETIME storage class in SQLite. Instead, any values declared with one of the previous data types are stored on a storage class depending on the date format of the inserted value as following:
- TEXT – if you inserted the date value in the format of the ISO8601 string ("YYYY-MM-DD HH:MM:SS.SSS").
- REAL – if you inserted the date value in the Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. Then the date value would be stored as REAL.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Summary:
SQLite supports a broad range of data types. But, at the same time, it is very flexible regarding data types. You can put any value type in any data type. SQLite also introduced some new concepts in data types like type affinity and storage classes, unlike other database management systems.