# Schema & Migrations

> Define and migrate your database schema with numbered migration files. Auto-tracked, non-destructive, and compatible with the Aerostack CLI.

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

## Create a migration

```bash
aerostack db migrate new add_posts_table
# Creates: migrations/0001_add_posts_table.sql
```

## Write the migration

```sql
-- 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

```bash
# Local development
aerostack db migrate apply

# Staging
aerostack db migrate apply --remote staging

# Production
aerostack db migrate apply --remote production
```

## Check migration status

```bash
aerostack db migrate apply --dry-run
```

## Common column patterns

```sql
-- 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

```ts
// 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)`
)
```