SQLite - Data Type

Hello, future database wizards! Today, we're diving into the fascinating world of SQLite data types. Don't worry if you're new to programming – I'll be your friendly guide on this journey, explaining everything step by step. So, grab a cup of coffee (or tea, if that's your thing), and let's get started!

SQLite Storage Classes

First things first, let's talk about SQLite storage classes. Think of these as the basic building blocks of data in SQLite. There are five main storage classes:

1. NULL
2. INTEGER
3. REAL
4. TEXT
5. BLOB

Let's break these down with some examples:

NULL

CREATE TABLE example (
    id INTEGER,
    name TEXT,
    age INTEGER
);

INSERT INTO example (id, name) VALUES (1, 'Alice');

In this example, we've created a table and inserted a row where the 'age' column is not specified. SQLite automatically assigns it a NULL value. NULL represents the absence of data – it's like saying, "We don't know Alice's age."

INTEGER

INSERT INTO example (id, name, age) VALUES (2, 'Bob', 30);

Here, we've inserted Bob's age as 30. SQLite stores this as an INTEGER. Integers are whole numbers without decimal points.

REAL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price REAL
);

INSERT INTO products (id, name, price) VALUES (1, 'Coffee', 3.99);

The price 3.99 is stored as a REAL value, which is SQLite's way of handling decimal numbers.

TEXT

INSERT INTO example (id, name, age) VALUES (3, 'Charlie', '25 years old');

Even though we're storing age, we've used TEXT here. SQLite will store "25 years old" as a string of characters.

BLOB

BLOB stands for Binary Large Object. It's used for storing binary data like images or files.

CREATE TABLE files (
    id INTEGER,
    name TEXT,
    content BLOB
);

-- Inserting a BLOB would typically be done in application code, not raw SQL

SQLite Affinity Type

Now, let's talk about type affinity. It's SQLite's way of being flexible with data types. Imagine it as SQLite trying its best to understand what type of data you want to store.

There are five type affinities in SQLite:

1. TEXT
2. NUMERIC
3. INTEGER
4. REAL
5. BLOB

Here's a fun table to show you how SQLite decides which affinity to use:

If the declared type contains...	The affinity is...
INT	INTEGER
CHAR, CLOB, TEXT	TEXT
BLOB	BLOB
REAL, FLOA, DOUB	REAL
Anything else	NUMERIC

Let's see this in action:

CREATE TABLE affinity_example (
    a TEXT,
    b NUMERIC,
    c INTEGER,
    d REAL,
    e BLOB
);

INSERT INTO affinity_example VALUES 
    ('123', '456', '789', '101112', x'ABCDEF');

SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM affinity_example;

This will output:

text, integer, integer, real, blob

Notice how '456' became an integer due to NUMERIC affinity!

SQLite Affinity and Type Names

SQLite is quite flexible when it comes to column type names. You can use standard SQL types, and SQLite will map them to its affinities. Here's a quick reference:

Type Name	Affinity
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8	INTEGER
CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB	TEXT
BLOB, no datatype specified	BLOB
REAL, DOUBLE, DOUBLE PRECISION, FLOAT	REAL
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME	NUMERIC

Boolean Datatype

SQLite doesn't have a separate Boolean type, but don't let that fool you! We can still work with true/false values. SQLite treats 0 as false and 1 as true.

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);

INSERT INTO tasks (description, completed) VALUES 
    ('Learn SQLite', 0),
    ('Master SQL', 1);

SELECT * FROM tasks WHERE completed = TRUE;

This will return the "Master SQL" task because its 'completed' value is 1 (true).

Date and Time Datatype

Last but not least, let's talk about dates and times. SQLite doesn't have a built-in date or time type, but it provides several date and time functions to work with these values.

We typically store dates and times as TEXT, INTEGER, or REAL:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT,
    date_text TEXT,
    date_int INTEGER,
    date_real REAL
);

INSERT INTO events (name, date_text, date_int, date_real) VALUES 
    ('SQLite Workshop', '2023-06-15', 1686787200, 1686787200.0);

SELECT name, 
       date_text, 
       datetime(date_int, 'unixepoch') as date_from_int,
       datetime(date_real, 'unixepoch') as date_from_real
FROM events;

This will output something like:

SQLite Workshop, 2023-06-15, 2023-06-15 00:00:00, 2023-06-15 00:00:00

And there you have it! We've covered the main data types in SQLite, from basic storage classes to the more nuanced concepts of type affinity. Remember, SQLite's flexibility is both its strength and something to be mindful of. Always test your data to ensure it's being stored and retrieved as you expect.

Practice makes perfect, so don't be afraid to experiment with these concepts. Create your own tables, insert some data, and see how SQLite handles different scenarios. Before you know it, you'll be an SQLite data type expert!

Happy coding, and may your databases always be normalized!

Credits: Image by storyset