SQLite is a lightweight, self-contained database engine renowned for its simplicity and flexibility. One of its unique features is the way it handles data types through type affinity, which determines how SQLite associates values with storage classes.

Unlike many database systems that enforce strict type constraints, SQLite’s type affinity system is more flexible, accommodating a broader range of data.

What Is Type Affinity?

Type affinity refers to the set of rules that SQLite uses to determine how values of different types are stored in a column. Each column in a SQLite table has an affinity, which guides SQLite in deciding how to store and retrieve values for that column.

Unlike other relational database management systems (RDBMSs) such as MySQL or PostgreSQL, SQLite does not enforce strict data types (unless strict tables are used). Instead, SQLite uses a dynamic typing system, where values carry their data type with them rather than being strictly bound to the column’s data type.

Storage Classes in SQLite

SQLite categorizes all data into five storage classes:

1. NULL: Represents null values.
2. INTEGER: Stores signed integers.
3. REAL: Stores floating-point numbers.
4. TEXT: Stores text strings encoded in UTF-8, UTF-16BE, or UTF-16LE.
5. BLOB: Represents binary data, stored exactly as it is.

These storage classes underpin SQLite’s dynamic typing system and work in conjunction with type affinity.

How Type Affinity Works

When you define a table in SQLite, you assign a type to each column, such as INTEGER, TEXT, or BLOB. By default, instead of enforcing that the column can only store values of that type, SQLite applies type affinity to guide how values are interpreted and stored. The column’s affinity determines how SQLite attempts to convert values before storing them.

The Five Rules of Type Affinity

SQLite uses the following steps to determine a column’s affinity based on its declared type:

1. INTEGER Affinity: If the declared type contains the string “INT” then it is assigned INTEGER affinity.
2. TEXT Affinity: If the declared type of the column contains any of the strings “CHAR”, “CLOB”, or “TEXT” then that column has TEXT affinity.
3. BLOB Affinity: If the declared type for a column contains the string “BLOB” or if no type is specified then the column has affinity BLOB.
4. REAL Affinity: If the declared type for a column contains any of the strings “REAL”, “FLOA”, or “DOUB” then the column has REAL affinity.
5. NUMERIC Affinity: Otherwise, the affinity is NUMERIC.

The order is important. If a datatype matches more than one rule, only the first rule will apply.

Examples of Type Affinity Determination

Declared Type	Affinity	Explanation
VARCHAR(255)	TEXT	Contains “CHAR”.
INT	INTEGER	Contains “INT”.
NUMERIC	NUMERIC	All previous rules failed, and so the affinity is NUMERIC.
FLOAT	REAL	Contains “FLOA”.
	BLOB	No type has been specified, and so the affinity is BLOB.

Type Conversion When Comparing Values in SQLite

When using a comparison expression to compare values, SQLite may first try to convert values between the INTEGER, REAL, and/or TEXT storage classes.

SQLite uses the following rules to determine this:

• If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.
• If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand.
• Otherwise, no affinity is applied and both operands are compared as is.

Type affinity will only be applied if the conversion does not lose essential information.

Benefits of Type Affinity

SQLite’s type affinity can provide various benefits, such as:

• Flexibility: Type affinity allows SQLite to accept a wide variety of data formats, making it highly adaptable to different use cases and programming languages.
• Simplicity: The relaxed typing system reduces the need for strict schema enforcement, simplifying database design.
• Compatibility: SQLite’s approach to dynamic typing makes it compatible with data from various sources, especially in situations where strict typing would pose challenges.

Drawbacks of Type Affinity

There are also potential drawbacks of SQLite’s type affinity:

• Inconsistent Data: The relaxed enforcement can lead to unexpected data types in a column, such as strings in a column intended for numbers.
• Runtime Errors: Applications that assume strict typing might encounter errors or behave unpredictably when interacting with SQLite.
• Complex Queries: Queries involving mixed data types may require additional logic to handle unexpected formats.

Best Practices for Working with Type Affinity

• Choose Appropriate Affinities: Design your schema with the expected data types in mind to minimize unexpected conversions.
• Validate Input: Validate data at the application level to ensure consistency.
• Use Explicit Casting: When querying, use SQLite’s CAST() function to cast values to a specific type. You can also use TYPEOF() to check a value’s type.

Strict Tables in Relation to Type Affinity

In response to the challenges posed by its dynamic typing system, SQLite introduced strict tables in version 3.37.0. Strict tables enforce stricter type rules while preserving SQLite’s lightweight and flexible design. When a table is created with the STRICT keyword, the database engine requires values to adhere to the declared data type more rigorously.

How Strict Tables Work

Strict tables constrain data insertion and storage by enforcing a closer alignment between the declared column type and the actual storage class of values. This eliminates many of the ambiguities and inconsistencies that can arise from SQLite’s type affinity system.

For example:

• If a column is declared as INTEGER, only integer values can be stored in that column.
• If a column is declared as TEXT, only text strings are allowed.
• Attempting to insert a value that doesn’t match the column’s declared type will result in an error.

Creating a Strict Table

To create a strict table, include the STRICT keyword in the table definition:

CREATE TABLE account_info (
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    balance REAL DEFAULT 0.0
) STRICT;

Benefits of Strict Tables

• Improved Data Integrity: Strict tables ensure that the data adheres to the intended schema, reducing the risk of unexpected data types.
• Error Prevention: Applications interacting with strict tables are less likely to encounter runtime errors caused by inconsistent data.
• Enhanced Query Reliability: Queries on strict tables yield more predictable results, as data types are consistent with the schema.

Strict Tables vs. Traditional Tables

Feature	Traditional Tables	Strict Tables
Type Affinity	Applies flexible rules	Requires strict adherence
Data Validation	Limited enforcement	Strong enforcement
Usability	High flexibility	Improved integrity
Error Handling	May store unexpected types	Rejects mismatched types

Use Cases for Strict Tables

Strict tables are ideal for applications where data consistency and integrity are paramount, such as:

• Financial systems requiring precise numeric storage.
• Applications relying on strict schema validation.
• Scenarios where multiple developers interact with the database, reducing risks of unintended data insertion.

By combining strict tables with SQLite’s inherent simplicity, developers can leverage the best of both worlds: a lightweight database engine with enhanced data integrity for critical use cases.

Summary

SQLite’s type affinity system is a key feature that enables flexibility and adaptability in data management. By allowing dynamic typing and loosely enforcing column data types, SQLite provides a lightweight and versatile solution for a wide range of applications. However, this flexibility can sometimes lead to inconsistencies, requiring developers to carefully validate and manage their data.

To address scenarios where stricter type enforcement is needed, SQLite introduced strict tables, offering a way to enforce consistent data types while retaining SQLite’s simplicity. With strict tables, developers can ensure that their schemas are adhered to, preventing unexpected data types and enhancing the reliability of queries and applications.

By understanding the nuances of type affinity and leveraging features like strict tables, developers can make informed decisions about schema design and data handling.