SQLite Data Types: Int, Text, Numeric, Real, Blob

By : Marcus Allen Marcus Allen
Hours Updated

⚡ Smart Summary

SQLite data types work differently from other databases because SQLite uses dynamic typing, storing any value in any column. Storage classes, type affinity, and declared type names together determine how each value is physically saved on disk.

  • 🧩 Dynamic Typing: SQLite is dynamically typed, so the value you insert, not the column definition, decides how the data is stored.
  • 🗄️ Storage Classes: Five storage classes — NULL, INTEGER, REAL, TEXT, and BLOB — describe how each value is held on disk.
  • 🎯 Type Affinity: Every column receives a recommended affinity — TEXT, NUMERIC, INTEGER, REAL, or BLOB — from its declared type name.
  • 🔢 Number Storage: Any declared type containing INT becomes INTEGER, while REAL, DOUBLE, and FLOAT hold floating-point numbers in eight bytes.
  • 📅 Dates and Booleans: SQLite has no dedicated DATE or BOOLEAN class and stores those values as TEXT, REAL, or INTEGER instead.
  • 🤖 AI Assistance: AI text-to-SQL assistants and GitHub Copilot generate SQLite column definitions and choose data types from plain English.
Read More

SQLite Data Types

The sections below explain how SQLite handles data types, starting with the five storage classes, then type affinity and its assignment rules, and finally worked examples of storing integers, real numbers, BLOBs, Booleans, and dates.

Data types in SQLite differ from those in other database management systems. In SQLite, you can declare data types normally, but you can still store any value in any data type.

SQLite is typeless. There are no fixed data types; you can store any type of data you like in any column. This is called dynamic typing.

In static types, as in other database management systems, if you declare a column with the data type integer, you can only insert values of data type integer. However, in dynamic types, as 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 8 bytes of storage.
  • TEXT – stores text strings. It also supports different encodings like UTF-8, UTF-16BE, or UTF-16LE.
  • BLOB – used to store large files, like images or text files. The value is stored as a 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 can still 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 systems.

Any column declared in an SQLite database is assigned a type affinity depending on its declared data type. Here is the list of type affinities in SQLite:

  • TEXT.
  • NUMERIC.
  • INTEGER.
  • REAL.
  • BLOB.

Here is how SQLite determines the affinity of a 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 in 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 below showing some examples of the mapping between SQLite data types and their affinities determined by these rules:

Mapping between SQLite declared data types and their assigned type affinities, showing INT, CHAR, TEXT, BLOB, REAL, FLOAT, and NUMERIC examples

Examples of Storing Data types in SQLite

Storing numbers with SQLite INTEGER

If any column has a data type that 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 an INTEGER type affinity:

  • INT, INTEGER, BIGINT.
  • INT2, INT4, INT8.
  • TINYINT, SMALLINT, MEDIUMINT.

INTEGER type affinity in SQLite can hold any assigned integer number (positive or negative) from 1 byte up to a maximum of 8 bytes.

RELATED ARTICLES

Storing numbers with SQLite REAL

REAL numbers are numbers with double floating-point precision. SQLite stores real numbers as an 8-byte 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 in a SQLite database, and that is using the BLOB data type. This data type is used to store large files like images or files of any type. The file is converted into a byte array and then stored in the same size as the input file.

Storing SQLite Booleans

SQLite does not have a separate BOOLEAN storage class. Instead, BOOLEAN values are stored as INTEGERS, with 0 representing false and 1 representing true.

Storing SQLite dates and times

You can declare dates 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 in a storage class depending on the date format of the inserted value, as follows:

  • TEXT – if you insert the date value in the format of an ISO8601 string (“YYYY-MM-DD HH:MM:SS.SSS”).
  • REAL – if you insert the date value as a Julian day number, the number of days since noon in Greenwich on November 24, 4714 B.C. The date value is then stored as REAL.
  • INTEGER – as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

FAQs

A storage class is how SQLite physically stores a value: NULL, INTEGER, REAL, TEXT, or BLOB, decided per value at runtime. Type affinity is the class a column prefers, chosen from its declared type name, which SQLite tries to apply.

No. SQLite ignores the number in parentheses, so VARCHAR(50) and VARCHAR behave identically. Both receive TEXT affinity because the type name contains CHAR, and SQLite imposes no length limit except the global SQLITE_MAX_LENGTH value.

Use the typeof() function in a query, such as SELECT typeof(column) FROM table. It returns the value’s real storage class — null, integer, real, text, or blob — which can differ from the column’s declared type because of dynamic typing.

STRICT tables, added in SQLite 3.37, turn type affinity off and reject any value that does not match a column’s declared type. Only INT, INTEGER, REAL, TEXT, BLOB, and ANY are allowed, giving the rigid typing found in other databases.

Store money as an INTEGER in the smallest unit, such as cents, to avoid the rounding errors of REAL floating-point values. For variable or very high precision, TEXT preserves the exact number string. SQLite has no dedicated fixed-point money type.

The SQLITE_MAX_LENGTH setting caps a TEXT or BLOB value and defaults to one billion bytes. The current implementation supports up to 2,147,483,647 bytes. You can lower the limit at runtime with the sqlite3_limit() interface when needed.

Yes. AI text-to-SQL assistants turn a plain-English description into CREATE TABLE statements, choosing SQLite storage classes and affinities such as INTEGER, TEXT, and REAL. Supplying existing column types improves accuracy, and generated SQL should be reviewed before running.

Yes. GitHub Copilot suggests column definitions with SQLite types like INTEGER, TEXT, REAL, and BLOB inline in editors such as VS Code. It reads surrounding schema and migration code, so its completions reuse your real table and column names.

Summarize this post with: