CREATE TABLE

This is where you stop poking sample data and start designing the shape your UI will live on.

Minimal table

CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  website TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Comments for the code above:

• INTEGER PRIMARY KEY in SQLite doubles as the rowid - auto-increments when you omit id on insert
• NOT NULL - required
• DEFAULT (datetime('now')) - fill created_at if the insert doesn't provide it

Common constraints

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  company_id INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  CHECK (length(name) > 0)
);

Constraint	Meaning
PRIMARY KEY	unique identity
UNIQUE	no duplicate values (NULLs are special-cased)
NOT NULL	required
DEFAULT …	fill if omitted
CHECK (…)	custom rule
REFERENCES other(id)	foreign key (next chapter)

ALTER / DROP

ALTER TABLE contacts ADD COLUMN phone TEXT;

DROP TABLE contacts;  -- gone. forever. (unless you have backups)

SQLite's ALTER TABLE is more limited than Postgres - you can add columns, rename, etc., but big reshapes sometimes mean "new table + copy + swap". Migrations in frameworks hide that pain.

Naming habits I like

• tables: plural snake_case → contacts, deal_stages
• columns: snake_case → company_id, created_at
• boolean-ish: is_active, or timestamps like deleted_at for soft delete
• money: store cents as INTEGER or use REAL carefully - know your product rules

From TypeScript type → table

type Company = {
  id: number
  name: string
  website: string | null
  createdAt: string
}

CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  website TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Same fields. SQL just spells the nullability and defaults out loud.

Next: linking tables together - Keys & relations.