What datatype does SQLite supports?

SQLite supports multiple data types, which can be categorized as follows:

1. Text type (TEXT):

   • SQLite stores text data using UTF-8, UTF-16BE, or UTF-16LE encoding.
   • Examples: any string data, such as 'Hello, world!' or 'Username'.

2. Integer type (INTEGER):

   • This type is used to store integer values. SQLite dynamically adjusts the byte usage based on the integer's size, ranging from 1 to 8 bytes.
   • Examples: age 29, quantity 150.

3. Real type (REAL):

   • Used to store floating-point numbers, internally represented as 8-byte IEEE floating-point values.
   • Examples: price 19.99, weight 65.5.

4. Binary type (BLOB):

   • Binary Large Object (BLOB) is used to store binary data, such as images and files.
   • Examples: binary data for storing image files or audio files.

5. NULL:

   • Used to represent missing or null values.

SQLite's flexibility lies in its dynamic typing system. This means you can store any data type in a column, though you may specify a suggested type when defining the column; the system does not enforce this type. This flexibility is highly useful in scenarios where the initial data model is unclear or during rapid prototyping.

For example, when developing a simple user management system, you might create a table containing user information as follows:

sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    email TEXT,
    profile_picture BLOB
);

In this table, id, name, age, email, and profile_picture utilize the data types described above, enabling flexible storage of various necessary user information.