SQLite Data Types

SQLite data types define how data is stored and handled in an SQLite database. Proper data typing allows SQLite to efficiently store data and process SQL operations.

It also prevents unexpected type conversions that could lead to data corruption or loss of precision. Understanding SQLite data types is key to building robust database-driven applications.

This article will provide an in-depth overview of SQLite data types, storage classes, type affinity, date/time handling, custom functions, type conversions, and best practices.

Also check: SQLite Syntax: A Complete Beginner’s Guide

Storage Classes in SQLite

SQLite supports five storage classes for holding data values:

• NULL: Represents missing or unknown data. NULL values take up very little space as they have no data associated.
• INTEGER: Signed integer values up to 8 bytes. Stored efficiently using 1, 2, 3, 4, 6 or 8 bytes based on the magnitude.
• REAL: Floating point values stored as 8-byte IEEE floating point numbers.
• TEXT: Character strings stored using database encoding (UTF-8, UTF-16BE or UTF-16-LE).
• BLOB: Binary Large OBjects stored exactly as input. Used for storing binary data like images, audio, etc.

Careful consideration of storage classes can optimize database disk usage and query performance.

Type Affinity

SQLite allows columns to hold values of any storage class. However, each column has an associated type affinity that determines how data is converted when stored in that column. SQLite has five built-in type affinities:

• TEXT: Used for any text or character data. Stored as TEXT storage class.
• NUMERIC: Used for integer or floating point values. Stores as INTEGER or REAL storage class.
• INTEGER: Used for integer values. Stores as INTEGER storage class.
• REAL: Used for floating point values. Stores as REAL storage class.
• NONE: No preference on data type conversion or storage.

Type affinity is also used to determine how data types get converted during operations like sorting and comparisons. For example, a column with REAL affinity will convert TEXT values to REAL when comparing.

SQLite vs. Other Database Engines

Unlike traditional SQL databases, SQLite does not enforce strict typing. Columns are not required to declare a specific data type and can flexibly store any data. SQLite will dynamically try to convert values to the type affinity of the target column whenever possible.

This provides flexibility but can result in unexpected type conversions if affinities are not considered carefully. Other databases like MySQL require explicit declaration of data types and enforce strict typing for inserts and updates.

Date and Time Datatype

SQLite does not have built-in date/time storage classes. Instead dates and times are stored as TEXT, REAL or INTEGER values:

• TEXT values store dates/times in a string format like ‘YYYY-MM-DD HH:MM:SS.SSS’.
• REAL values store dates/times as Julian day numbers, the number of days since noon on November 24, 4714 B.C.
• INTEGER values store Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

SQLite provides a comprehensive set of functions like date(), time(), datetime() and modifiers like strftime() to handle date/time conversions and formatting during queries.

Also read: SQLite Data Types: An In-Depth Guide

Application-Defined SQL Functions

SQLite allows creating custom SQL functions using C/C++ or built-in tools like sqlite3_create_function(). Custom functions can perform arbitrary operations on arguments of any type.

Custom functions help extend SQLite to support application-specific data types with no native representation, like arrays, networks, geometries, etc.

Also read: SQLite IF EXISTS: A Clear Guide to Conditional Statements

Type Conversion Between Storage Classes

SQLite supports implicit and explicit type conversions between storage classes:

• Implicit conversions happen automatically based on type affinity. For example, REAL values will get converted to INTEGER when inserting into an INTEGER column.
• Explicit conversions use functions like CAST() or sqlite3_column_int() to force a specific storage class. This overrides the implicit type affinity conversions.

Type conversions follow well-defined rules to prevent data corruption. Lossy conversions like REAL to INTEGER require explicit casts to make it obvious. Other dangerous conversions like INTEGER to TEXT are disallowed.

Best Practices and Considerations

Choosing appropriate data types and type affinities from the start is vital for building robust SQLite databases:

• Use TEXT/BLOB for textual or binary data, REAL for floating point data, and INTEGER for integer values.
• Set type affinities properly based on expected data. Mismatched affinities can lead to weird type conversions.
• Use EXPLICIT typing with CAST() when inserting/selecting columns with different affinities.
• Avoid frequent type conversions as they can hurt performance. Store data already typed for usage.
• Test type conversions thoroughly. SQLite typing is more flexible so unintended results may happen.

Examples and Code Snippets

Here are some examples of creating tables with various storage classes and type affinities:

-- Explicit typing 
CREATE TABLE items (
    name TEXT, 
    price REAL,
    quantity INTEGER
);

-- Flexible typing
CREATE TABLE flexdata (
   id INTEGER, 
   description, -- No datatype specified
   last_updated TEXT -- Default affinity is NUMERIC 
)

-- Datetime stored as TEXT
CREATE TABLE events (
    event_name TEXT,
    event_date TEXT 
);

INSERT INTO events VALUES (
    'Concert', 
    '2025-04-15 20:00:00' -- Date stored as TEXT
);

Type conversions during insert in SQLite:

-- Implicit type conversion 
INSERT INTO items(name, price, quantity) VALUES 
   ('Book', 29.95, 10); 
   
-- Explicit conversion  
INSERT INTO items(name, price, quantity) VALUES
  ('Book', CAST(29.95 as REAL), CAST(10 as INTEGER));

Date Handling in SQLite:

-- Date functions
SELECT date('now') AS current_date; 

-- Date formatting  
SELECT strftime('%Y-%m-%d', event_date) AS event_day 
FROM events;

What’s next?

Proper handling of data types is crucial for building robust SQLite databases. SQLite offers flexible typing and dynamic type conversions using storage classes and type affinity. Developers should carefully consider how data needs to be stored and retrieved when picking data types to prevent unexpected behavior.

Further documentation on SQLite data types and custom functions is available on sqlite.org.