Join my Laravel for REST API's course on Udemy 👀

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: INTEGER, REAL, TEXT, NULL and BLOB.

So all values you store in SQLite belong to one of these storage classes:

Storage class
INTEGERWhole 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.
REAL8-byte decimal floating point numbers
TEXTUnlimited storage of text characters (strings)
NULLNull values, representing missing or unknown information.
BLOBBinary 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: NUMERIC, INTEGER, REAL, TEXT and 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.