Available data types in SQLite databases
March 28, 2021 ‐ 2 min read
SQLite databases come with just five primitive data types, which are called storage classes. Which, to be fair, is enough to do the basics :). These five storage classes in SQLite are:
So all values you store in SQLite belong to one of these storage classes:
|Whole numbers both positive and negative. With a size of 1, 2, 3, 4, 6, or 8 bytes. SQLite manages the sizing automatically based on the value you store.
|8-byte decimal floating point numbers
|Unlimited storage of text characters (strings)
|Null values, representing missing or unknown information.
|Binary objects of unlimited size.
Typing in SQLite works a little different than most other relational database systems like MySQL and PostgreSQL. Where MySQL and PostgreSQL use static typing on a column basis, SQLite uses a dynamic typing approach where columns have just a type affinity. In SQLite the actual types are defined on cell, or value, basis instead of on a column basis.
You can thing of the "type affinity" of a column in SQLite as the recommended data type you store in that column. But do keep in mind that SQLite may cast data before storing based on a columns type affinity.
The "type affinities" that SQLite uses are:
NONE. As which storage class your data ends up in SQLite does depend the type affinity a column has. To learn more about the rules SQLite uses to cast data based on type affinities you can best read the SQLite docs.