schema_migration.sql in SQL

The table rebuild: the only way to add a constraint SQLite cannot ALTER in.

-- Adding a NOT NULL column and a CHECK to a live table. SQLite cannot
-- ALTER either one in, so the table gets rebuilt around the data.

PRAGMA foreign_keys = OFF;

BEGIN;

CREATE TABLE payments_new (
    id INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE SET NULL,
    amount REAL NOT NULL CHECK (amount > 0),
    currency TEXT NOT NULL CHECK (LENGTH(currency) = 3),
    method TEXT NOT NULL DEFAULT 'card'
        CHECK (method IN ('card', 'transfer', 'credit')),
    paid_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO payments_new (id, order_id, amount, currency, method, paid_at)
SELECT
    id,
    order_id,
    amount,
    UPPER(COALESCE(NULLIF(TRIM(currency), ''), 'USD')),
    CASE
        WHEN method IN ('card', 'transfer', 'credit') THEN method
        ELSE 'card'
    END,
    COALESCE(paid_at, CURRENT_TIMESTAMP)
FROM payments
WHERE amount > 0;

DROP TABLE payments;

ALTER TABLE payments_new RENAME TO payments;

CREATE INDEX payments_by_order
    ON payments (order_id);

CREATE INDEX payments_by_day
    ON payments (paid_at);

PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys = ON;

SELECT
    method,
    currency,
    COUNT(*) AS payments,
    ROUND(SUM(amount), 2) AS collected
FROM payments
GROUP BY method, currency
ORDER BY collected DESC;

How it works

1. Foreign keys go off outside the transaction, then back on at the end.
2. Build the new table, copy the rows, drop the old one, rename into place.
3. foreign_key_check before COMMIT is what makes this safe to run.

Keywords and builtins used here

• ALTER
• AS
• BEGIN
• BY
• CASE
• CHECK
• COALESCE
• COMMIT
• COUNT
• CREATE
• CURRENT_TIMESTAMP
• DEFAULT
• DELETE
• DESC
• DROP
• ELSE
• END
• FROM
• GROUP
• IN
• INDEX
• INSERT
• INTEGER
• INTO
• KEY
• LENGTH
• NOT
• NULL
• NULLIF
• OFF
• ON
• ORDER
• PRIMARY
• REAL
• REFERENCES
• RENAME
• SELECT
• SET
• SUM
• TABLE
• TEXT
• THEN
• TO
• TRIM
• UPPER
• WHEN
• WHERE
• method

The run, in numbers

Lines

55

Characters to type

1210

Tokens

227

Three-star pace

100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 136 seconds.

Type this snippet

Step 1 of 2 in Encore, step 15 of 16 in Schema & constraints.

← Previous Next →