CREATE TABLE Basics

The skeleton statement

Every SQLite database is a collection of tables, and every table is created with CREATE TABLE. The shape is straightforward:

CREATE TABLE table_name (
  column_name TYPE [constraints],
  ...
);

What this gives you in SQLite is a real schema, but with one twist: SQLite uses type affinity rather than strict typing by default. The TYPE hint matters, but the engine will accept values of any type unless you explicitly opt in to STRICT mode (next lessons). For now, follow the convention — declare the type you mean and treat it as a contract you and your code keep.

Five core type names you'll use 95% of the time:

INTEGER — whole numbers, up to 8 bytes signed.
REAL — IEEE 754 doubles.
TEXT — UTF-8 strings.
BLOB — raw bytes.
NUMERIC — affinity that prefers integers but accepts decimals; rarely worth the ambiguity.

Principle: Even though SQLite is dynamically typed by default, write your CREATE TABLEs as if they were strict. Future you, future Pippa, future readers will thank you. Document intent in the schema, not in the code that uses it.

Code

A real, useful CREATE TABLE·sql

CREATE TABLE conversations (
  id          INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  brain       TEXT NOT NULL DEFAULT 'claude',
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at  TEXT NOT NULL DEFAULT (datetime('now')),
  archived    INTEGER NOT NULL DEFAULT 0   -- 0/1 boolean
);

Inspect what you created·sql

-- Two ways to read your own schema back
.schema conversations

SELECT name, sql FROM sqlite_schema WHERE type='table' AND name='conversations';

-- Per-column metadata, including PK and NOT NULL flags
SELECT cid, name, type, "notnull", dflt_value, pk
FROM   pragma_table_info('conversations');

Exercise

Design a messages table that pairs with conversations: it should have an id, a foreign key to conversations, a role ('user'|'assistant'|'system'), text content, a created_at timestamp, and an optional brain column. Write the CREATE TABLE statement, then inspect it with pragma_table_info to confirm every column is what you intended.

The skeleton statement

Code

External links

Exercise

Progress

Comments 0