Schema & Migrations

Aerostack uses a numbered migration file system. Migrations run in order and are tracked to prevent re-runs.

Create a migration

migrations/0001_add_posts_table.sql

aerostack db migrate new add_posts_table

Write the migration

-- migrations/0001_add_posts_table.sql
CREATE TABLE IF NOT EXISTS posts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT,
  published INTEGER DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at);

Apply migrations

# Local development
aerostack db migrate apply

# Staging
aerostack db migrate apply --remote staging

# Production
aerostack db migrate apply --remote production

Check migration status

aerostack db migrate apply --dry-run

Common column patterns

-- Auto-ID with UUID (Workers-compatible)
id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),

-- Timestamps (Unix ms)
created_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
updated_at INTEGER,

-- Soft delete
deleted_at INTEGER,

-- JSON metadata
metadata TEXT DEFAULT '{}',

-- Boolean (SQLite has no boolean type)
is_active INTEGER DEFAULT 1,

Run schema queries from SDK

// Get all table names
const { results } = await sdk.db.query(
  "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
)

// Column info
const { results: cols } = await sdk.db.query(
  `PRAGMA table_info(posts)`
)