Guide to SQLite Data Types

SQLite is a popular, lightweight database system that stores data in a single file. Its simplicity is aided by its flexible, dynamic type system, which is quite different from the static, rigid types used in other database systems like MySQL, PostgreSQL, or SQL Server, which require you to specify data types when creating tables.

How Types Work in SQLite

In SQLite, you don't need to specify a data type for columns when creating a table. Instead, SQLite uses dynamic typing, which means it assigns a type to each inserted value based on its content. You can still specify data types when creating columns in SQLite tables, which act as a hint to tell the database what type of data to expect. However, SQLite will still store any type of data in any column, regardless of the declared type.

Datatypes/Storage Classes in SQLite

SQLite values in all columns (even those with declared types) may use any one of the following five storage classes:

• TEXT: Any string of text, including numbers, dates, and other types.
• INTEGER: Integer values.
• REAL: Floating-point values.
• BLOB: Binary large objects, such as images or other binary data.
• NULL: Value is a null (empty) value.

Type Affinities

SQLite doesn't use strict types but rather a concept called "type affinities." These are rules that SQLite uses to determine the type of a value stored in a column and which storage class to use for that value. The storage class is a more general concept that groups similar types.

Although there is a relationship between storage class and type affinity, affinity has to do with how SQLite interprets the data inserted or updated into a column. The type affinity provides guidance on the storage class used for a value in a column but it doesn't restrict the type of data you can store in that column.

The type affinities in SQLite are:

• TEXT: Any string of text, including numbers, dates, and other types.
• NUMERIC: Any number, including integers and floating-point values.
• INTEGER: Integer values.
• REAL: Floating-point values.
• BLOB: Binary large objects, such as images or other binary data. (Historically called NONE)

When you create a column, you can specify a type affinity to give SQLite a hint about the type of data you expect to store in that column. SQLite will then use this information to determine the storage class for the values in that column. However, SQLite will still allow you to store any type of data in any column, regardless of the declared type.

For example, let's say you created the following table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    score REAL
);

In this table, the id column is expected to store integer values, the name column is expected to store text values, and the score column is expected to store floating-point values. However, you could still insert a text value into the score column, and SQLite would store it as a text value without any issues.

INSERT INTO users (id, name, score) VALUES (1, 'Alice', 'unknown');
SELECT * FROM users WHERE id = 1;

This returns

1 | Alice | unknown

The type affinity declared for a column guides how SQLite interprets and stores values in that column. If you declare a column with a type affinity of INTEGER, SQLite will attempt to use the INTEGER storage class for values in that column when possible. On the other hand, if you use a TEXT affinity, SQLite will use the TEXT storage class for values in that column, even if the value could be stored as an integer.

SQLite's Type Affinity Rules

SQLite uses the following rules to determine the storage class for a value based on its type affinity:

1. INTEGER: Values stored as an integer if it can be converted to an integer without loss of information. If the value cannot be converted to an integer, SQLite will store it as a real value.

2. REAL: Values stored as a real value if it can be converted to a real value without loss of information. If the value cannot be converted to a real value, SQLite will store it as text.

3. TEXT: Values stored as a text value.

4. NUMERIC: SQLite converts and stores the value as an integer or real if possible.

5. BLOB: Values stored as a blob value. Note: Historically, this was called NONE, but this was confusing as it could imply 'no type affinity', so it was renamed to BLOB.

Pattern Matching When Creating Columns

It's worth noting that although SQLite only has five affinities (including BLOB, which was historically called NONE), it gives you considerable flexibility with what you call them, which can make SQLite more familiar to those familiar with other database systems. There are aliases for each of the storage classes that you can use when creating columns. These are case insensitive, and they follow this order:

• INTEGER: Any type with the string INT
  • e.g., (e.g. INT, INTEGER, SMALLINT)
• TEXT: Any type with the strings CHAR, CLOB, or TEXT
  • e.g., CHARACTER, VARCHAR, NCHAR, etc.
• BLOB: Any type with the string BLOB
• REAL: Any type with the strings REAL, FLOA, or DOUB
  • e.g., FLOAT, DOUBLE, etc.
• NUMERIC: Anything not matching the above

What types does SQLite not support?

SQLite is notable in that it does not support certain types that are common in other database systems. However, you can still store this sort of data in SQLite and work with it as you'd expect, but they will be stored as one of the five storage classes mentioned above.

The types SQLite does not support include:

• BOOLEAN/BIT
• DATE
• TIME
• DATETIME/TIMESTAMP

Working with Dates in SQLite

Despite SQLite's lack of a DATE type, you can still work with dates in SQLite. You can store dates as text, real, or integer values using ISO-8601 format, Unix timestamps, or Julian day numbers, for example. SQLite provides a number of built-in date and time functions to help you work with dates in your queries. For example, you can use the DATE and TIME functions to extract the date and time components from a text value or the strftime function to format dates in a specific way.

Generally, you'll want to use the human-readable ISO-8601 format for dates (e.g., 2024-09-01 01:48:00), as this is the most widely supported format and is easy to work with in SQLite.

Here are examples of how you can work with dates in SQLite:

-- ISO-8601 format, stored as TEXT (YYYY-MM-DD HH:MM:SS)
INSERT INTO events (name, date) VALUES ('Birthday', '2024-09-01');
INSERT INTO events (name, date) VALUES ('Appointment', '2024-09-01 01:48:00');
-- Unix timestamp, stored as INTEGER (Integer seconds since 1970-01-01)
INSERT INTO events (name, date) VALUES ('Meeting', 1723772054);
-- Julian day number, stored as REAL (Days since noon in Greenwich on November 24, 4714 BCE)
INSERT INTO events (name, date) VALUES ('Conference', 2459786.574074);

If you are using any one of these formats, SQLite provides a number of built-in date and time functions to help you work with dates in your queries. For example, you can use the DATE and TIME functions to extract the date and time components from a text value or the strftime function to format dates in a specific way. SQLite will use the storage class of the data to determine how to interpret and store the date values; values stored as REAL will be treated as Julian day numbers, INTEGER as Unix timestamps, and TEXT as ISO-8601 formatted strings.

Conclusion

You should now be familiar with both the storage classes (TEXT, INTEGER, REAL, BLOB, and NULL) and the type affinities (TEXT, NUMERIC, INTEGER, REAL, and BLOB) used in SQLite's dynamic type system, and the relationship between them. Remember that SQLite's type affinities are used to determine the storage class for a value in a column, but they don't restrict the type of data you can store in that column. You can store any type of data in any column, regardless of the declared type.