Spry Idempotent SQL Migrations

Idempotent SQL Migrations with Pure SQL + Spry

Use plain SQL, Common Table Expressions (CTEs), and Markdown cells to manage your database schema safely
— no frameworks, no dependencies, just Spry and SQLite.

Why Idempotent SQL Migrations?

The Problem

Traditional SQL migrations fail when re-run due to duplicate table, index, or column errors.

The Solution

Using CTEs with existence guards (WHERE NOT EXISTS) allows migrations to re-run safely.

Spry's Role

Captures SQL output in Markdown, making migrations readable, traceable, and replayable.

Markdown Notebook Spry Parser SQL Plan sqlite3 apply

What Makes Migrations "Idempotent"?

Idempotent means running the same migration multiple times produces the same result. It's safe, predictable, and self-documenting.

Traditional Migration	Idempotent Migration
`CREATE TABLE users;` ❌ Fails if exists	`CREATE TABLE IF NOT EXISTS users;` ✅ Safe to re-run
`ALTER TABLE ADD column;` ❌ Fails if column exists	`Check existence first with CTE` ✅ Only adds if missing
No built-in logging	✅ Logs APPLY or NOOP

Spry's Two-Pass Execution Pattern

Spry uses a powerful two-pass approach that gives you complete transparency and control:

First Pass: Generate

Spry executes Markdown sql cells, which output SQL strings. CTEs evaluate conditions and emit appropriate DDL.

Second Pass: Apply

The generated SQL is piped into sqlite3 to apply schema updates. You see exactly what runs.

spry run migrations.md | tee migrations/applied-$(date +%F-%H%M%S).sql | sqlite3 app.db

bash

Preview SQL before applying

See what will execute

Capture audit log

Save what was executed

Version control both

Generator and generated SQL

No black-box magic

Transparent execution

Example: Self-Describing Migration Notebook

Here's a complete working example showing how to create idempotent migrations in Markdown:


# User Table Migration

This notebook defines schema changes for our `user` table.  
All SQL is executed as emitted text using Spry's `sql` cell type.

## Create user table if it doesn't exist

```sql create-user-table --descr "Idempotently create the 'user' table"
WITH need AS (
  SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM sqlite_schema WHERE type='table' AND name='user'
  )
)
SELECT '-- APPLY  : create table user' FROM need
UNION ALL
SELECT 'CREATE TABLE IF NOT EXISTS user (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime(''%Y-%m-%dT%H:%M:%fZ'',''now''))
);' FROM need
UNION ALL
SELECT '-- NOOP   : create table user (already exists)'
WHERE NOT EXISTS (SELECT 1 FROM need);
```

## Add last_login column if missing

```sql add-last-login-column --dep create-user-table --descr "Idempotently add 'last_login' column"
WITH need AS (
  SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM pragma_table_info('user') WHERE name='last_login'
  )
)
SELECT '-- APPLY  : add column user.last_login' FROM need
UNION ALL
SELECT 'ALTER TABLE user ADD COLUMN last_login TEXT;' FROM need
UNION ALL
SELECT '-- NOOP   : add column user.last_login (already present)'
WHERE NOT EXISTS (SELECT 1 FROM need);
```

markdown

How it works: These sql fences emit strings that become valid SQL statements. The CTE pattern checks existence, then outputs either the DDL statement or a no-op comment.

The CTE Pattern Explained

Common Table Expressions (CTEs) provide a powerful way to conditionally generate SQL. Here are the three most common patterns:

Pattern 1: Table Creation

WITH need AS (
  SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM sqlite_schema WHERE type='table' AND name='table_name'
  )
)
SELECT 'CREATE TABLE table_name (...);' FROM need
UNION ALL
SELECT '-- NOOP: already exists' WHERE NOT EXISTS (SELECT 1 FROM need);

bash

Pattern 2: Column Addition

WITH need AS (
  SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM pragma_table_info('table_name') WHERE name='column_name'
  )
)
SELECT 'ALTER TABLE table_name ADD COLUMN column_name TYPE;' FROM need
UNION ALL
SELECT '-- NOOP: column exists' WHERE NOT EXISTS (SELECT 1 FROM need);

bash

Pattern 3: Index Creation

WITH need AS (
  SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM sqlite_schema WHERE type='index' AND name='idx_name'
  )
)
SELECT 'CREATE INDEX idx_name ON table_name (column);' FROM need
UNION ALL
SELECT '-- NOOP: index exists' WHERE NOT EXISTS (SELECT 1 FROM need);

bash

Avoids race conditions

Prevents "already exists" errors

Provides built-in logging

Complete Working Example

Here's a full end-to-end workflow showing how migrations work in practice:



#1. Create a new database

sqlite3 app.db "VACUUM;"

#2. Run your first migration (using the full graph)

spry run migrations.md | sqlite3 app.db

#3. View what was applied (First Run Output)

spry run migrations.md | head -20

#Output shows (Task: create-user-table):
#-- APPLY  : create table user
#CREATE TABLE IF NOT EXISTS user (...);

#Output shows (Task: add-last-login-column):
#-- APPLY  : add column user.last_login
#ALTER TABLE user ADD COLUMN last_login TEXT;

#4. Run again (idempotent check)

spry run migrations.md | sqlite3 app.db

#Output now shows:
#-- NOOP   : create table user (already exists)
#-- NOOP   : add column user.last_login (already present)

#5. Explicitly execute a single task (Demonstration)

spry task create-user-table migrations.md

#Output shows:
#-- NOOP   : create table user (already exists)

#6. Inspect schema

sqlite3 app.db ".schema user"

bash

Notice: The same migration runs twice with different results. First run applies changes, second run safely skips them. This is idempotency in action.

Why Pure SQL Over Migration Frameworks?

Traditional Framework

❌ Requires framework installation
❌ Learning curve for DSL/API
❌ Framework-specific conventions
❌ Migration state in separate table
❌ Vendor lock-in
❌ Black-box execution

Spry Idempotent SQL

✅ Just SQL + Markdown + Deno
✅ Standard SQL syntax
✅ Works with any SQLite database
✅ Self-documenting migrations
✅ No vendor lock-in
✅ Transparent execution
✅ Git is your version control
✅ Audit trail built-in

Spry ISM Philosophy

"Idempotent SQL Migrations is the new migration DSL"

Instead of inventing new DSLs or frameworks, we use plain SQL as code, embedded in Markdown, enriched by Spry's executable cells.

Core Principles

1. SQL as Code: No DSL abstraction, just pure SQL
2. Markdown as Documentation: Migrations are readable prose
3. Idempotency by Design: Every migration can safely re-run

Key Benefits

4. Transparency: Preview what will execute before applying
5. Git-Native: Version control is built-in
6. No Dependencies: Just Deno + SQLite

Real-World Use Cases

Local Development

Test schema changes safely

Production Deploys

Apply updates without downtime risk

Multi-Environment

Same migration works dev → staging → prod

Documentation

Migrations ARE the documentation

Compliance & Audit

Built-in logging of all changes

Team Collaboration

Git diffs show exact schema changes

Testing in CI

Migrations can be tested automatically

Disaster Recovery

Replay migrations to rebuild schema

Quick Start

Get started with idempotent SQL migrations in just a few steps:


# 1. Install Spry
curl -fsSL https://deno.land/install.sh | sh
curl -fsSL https://spry.md/init.sh | sh

# 2. Create your first task-based migration file (migrations.md)
# Note the explicit task name 'create-users-table'
cat > migrations.md << 'EOF'
# Initial Database Schema

```sql create-users-table --descr "Idempotently create users table"
-- Create users table idempotently using CTE
WITH need AS (
  SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM sqlite_schema WHERE type='table' AND name='users'
  )
)
SELECT 'CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);' FROM need;
```
EOF

# 3. Apply migration (spry run executes all tasks in the file)
spry run migrations.md | sqlite3 app.db

# 4. Verify
sqlite3 app.db ".tables"
# Output will show: users

# 5. Run again (Idempotency check)
spry run migrations.md
# Output will show: -- NOOP : create-users-table (already exists)

bash

Next steps: Try writing your own idempotent migrations in a Markdown notebook. See how the two-pass execution gives you full control and transparency.

Next Steps & Resources

Spry GitHub Repository

View source code and contribute

Spry Documentation

Learn more about Spry features

Try writing your own idempotent migrations in a Markdown notebook. See how the two-pass execution gives you full control and transparency.

Start Building Idempotent SQL Migrations with Spry

No frameworks, no magic—just SQL, CTEs, and Markdown