Learn SQLite in Neovim: Data Types — INTEGER, REAL, TEXT & typeof() | Episode 4

Learn SQLite Data Types: INTEGER, REAL, TEXT & typeof()

SQLite uses a flexible typing system with five storage classes, but the most common are INTEGER for whole numbers, REAL for decimals, and TEXT for strings. This tutorial demonstrates how to create a product catalog table with these types, insert sample data, and use the typeof() function to inspect how SQLite actually stores each value.

Code

-- Create a table with different data types
CREATE TABLE products (
  id INTEGER PRIMARY KEY,      -- whole number, unique ID
  name TEXT,                  -- product name as string
  price REAL,                 -- price as decimal number
  quantity INTEGER,           -- stock quantity as whole number
  description TEXT            -- optional text description
);

-- Insert sample products with mixed data
INSERT INTO products (name, price, quantity, description) VALUES
  ('Coffee Mug', 7.99, 120, 'Ceramic mug'),
  ('Notebook', 2.49, 300, '200 pages'),
  ('Pen', 1.20, 500, 'Blue ink'),
  ('Water Bottle', 12.50, 80, '1 liter capacity'),
  ('Sticker', 'free', 1000, 'Promotional item');  -- text in price column (flexible typing)

-- Query all products with their actual storage types
SELECT
  id, typeof(id) AS id_type,
  name, typeof(name) AS name_type,
  price, typeof(price) AS price_type,
  quantity, typeof(quantity) AS quantity_type,
  description, typeof(description) AS description_type
FROM products;

Key Points

• SQLite’s main storage classes are INTEGER, REAL, TEXT, BLOB, and NULL, with flexible typing allowing any type in any column.
• The typeof() function reveals the actual storage class of each value, which may differ from the declared column type.
• INTEGER stores whole numbers and is often used for IDs or boolean values (1/0).
• REAL stores floating-point numbers suitable for prices or measurements.
• TEXT stores strings and can appear in any column, even those declared as numeric types.