Creating Tables

With storage classes and type affinity out of the way, it's time to actually build something. This lesson creates the first two real tables for RecipeBox — users and recipes — and covers the everyday CREATE TABLE syntax you'll use constantly for the rest of this track.

Basic CREATE TABLE Syntax

The shape is: a table name, then a comma-separated list of column names paired with a declared type (which, as covered last lesson, only sets that column's type affinity):

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

Creating the users Table

Every recipe in RecipeBox has one author, so users comes first:

CREATE TABLE users (
  id            INTEGER PRIMARY KEY,
  username      TEXT NOT NULL UNIQUE,
  email         TEXT NOT NULL UNIQUE,
  joined_on     TEXT NOT NULL DEFAULT (date('now'))
);

Notice joined_on is declared TEXT, not DATE — as covered last lesson, SQLite stores dates as ISO8601 text, and the date('now') function call supplies today's date automatically when a row is inserted without one.

Creating the recipes Table

Each recipe belongs to exactly one user (its author), referenced here by user_id — the actual FOREIGN KEY constraint and its enforcement gotcha are the whole subject of the next lesson, so it's left informal here:

CREATE TABLE recipes (
  id              INTEGER PRIMARY KEY,
  user_id         INTEGER NOT NULL,
  title           TEXT NOT NULL,
  description     TEXT,
  prep_minutes    INTEGER NOT NULL DEFAULT 0,
  servings        INTEGER NOT NULL DEFAULT 1,
  is_favorite     INTEGER NOT NULL DEFAULT 0,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

is_favorite is the BOOLEAN-as-INTEGER pattern from last lesson (0 or 1), and created_at uses datetime('now') to capture a full timestamp, not just a date.

CREATE TABLE IF NOT EXISTS

Running a plain CREATE TABLE against a table that already exists raises an error. Adding IF NOT EXISTS makes the statement a no-op instead when the table is already there — handy for setup scripts you might run more than once:

CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY,
  username      TEXT NOT NULL UNIQUE,
  email         TEXT NOT NULL UNIQUE,
  joined_on     TEXT NOT NULL DEFAULT (date('now'))
);

Viewing a Table's Schema

Once a table exists, the .schema dot-command in the sqlite3 CLI prints back the exact CREATE TABLE statement that defines it:

.schema recipes

CREATE TABLE recipes (
  id              INTEGER PRIMARY KEY,
  user_id         INTEGER NOT NULL,
  title           TEXT NOT NULL,
  description     TEXT,
  prep_minutes    INTEGER NOT NULL DEFAULT 0,
  servings        INTEGER NOT NULL DEFAULT 1,
  is_favorite     INTEGER NOT NULL DEFAULT 0,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

Running plain .schema with no table name prints the CREATE TABLE statement for every table in the database — useful for getting a full picture of RecipeBox as it grows.