SQLite 3 Data Types Cheatsheet

There are two definitions related to data types we need to know in SQLite 3:

Storage Classes used for a value.
Affinity Types used for a column.

1. SQLite Storage Classes

In SQLite, it’s quite flexible to store data with the dynamic type system. The data type of a value is associated with the value itself, not with its container. And then SQLite stores that value depending on its type.

The definition of the data type in SQLite is slightly more generic than in other relational database management systems. It uses the storage class with multiple different data types of different lengths.

Value is SQLite will be stored with one of these storage classes:

Name	Description
TEXT	A text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
INTEGER	A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL	A floating point value, stored as an 8-byte IEEE floating point number.
BLOB	A blob of data, stored exactly as it was input.
NULL	A NULL value.

Note: SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

2. SQLite Affinity Types

In SQLite, each column has a preferred storage class called its affinity type. It’s recommended for the column but not required. You can still store any type of data you like in any column because like I mentioned earlier, SQLite stores a value depending on its associated type, not the type of the column.

You can use the SQL syntax from other relational databases such as CHAR(size) and SQLite accepts them and converts to match with its data types (TEXT).

Each table column in an SQLite 3 database is assigned one of the following type affinities:

Name	Description	Equivalent
TEXT	This column stores all data using storage classes NULL, TEXT or BLOB.	CHAR(size) VARCHAR(size) TINYTEXT(size) TEXT(size) MEDIUMTEXT(size) LONGTEXT(size) NCHAR(size) NVARCHAR(size) CLOB(size)
INTEGER	Behaves the same as a column with NUMERIC affinity, with an exception in a CAST expression.	TINYINT SMALLINT MEDIUMINT INT INTEGER BIGINT INT2 INT4 INT8
NUMERIC	This column may contain values using all five storage classes.	NUMERIC DECIMAL BOOLEAN DATE DATETIME
REAL	Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation.	REAL DOUBLE DOUBLE PRECISION FLOAT
BLOB	A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.	NONE

Note:

If you try to specify the size, it will automatically be ignored by SQLite.

Looking for a good GUI tool to work with SQLite database? Try TablePlus.

TablePlus is a modern, native client with intuitive GUI tools to create, access, query & edit multiple databases: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Amazon Redshift, MariaDB, CockroachDB, Vertica, Cassandra, Oracle, and Redis.