d1-record

Schema changes ​

Change a table with a new migration. Wrangler applies each migration once, in order. The schema is regenerated after each change, so a Model’s attributes follow its table.

Adding a column ​

Generate the migration, then apply it:

sh
npx d1-record g migration AddBioToUsers bio:string
npx wrangler d1 migrations apply my-app-db --local
sql
ALTER TABLE users ADD COLUMN bio TEXT;

user.bio is there straight away, typed as string | null. A required column needs a default for the existing rows: 'role:string!=member'.

To deploy, apply the migration with --remote first, then deploy the code that uses the column. The code already deployed keeps working, since it only writes the attributes it knows.

Renaming a column ​

Generate an empty migration, and write the rename:

sh
npx d1-record g migration RenameBioToAbout
sql
ALTER TABLE users RENAME COLUMN bio TO about;

The attribute follows the column, so bio becomes about. Update the code that uses it, then apply the migration and deploy together, since the old code reads a column that’s gone.

TIP

Keeping the old attribute name for a renamed column is planned in #65.

Removing a column ​

Remove a column in the opposite order to adding one, so the code stops using it before it goes:

  1. Ignore the column in the Model, remove the code that uses it, and deploy:

    ts
    export class User extends ApplicationRecord("users", { bio: false }) {}

  2. Generate the migration, and apply it with --remote:

    sh
    npx d1-record g migration RemoveBioFromUsers bio

  3. Remove { bio: false }, now that the schema no longer has the column.

Remove a references column the same way, with RemoveUserFromPosts user:references. Its index is dropped first, then the column.

Changing a column’s type ​

SQLite can’t change a column’s type in place. Write a migration that builds a new table, copies the rows, and swaps the tables, as SQLite describes:

sql
PRAGMA defer_foreign_keys = true;

CREATE TABLE products_new (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
INSERT INTO products_new SELECT id, name, CAST(price_cents AS INTEGER), created_at, updated_at FROM products;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;
  • PRAGMA defer_foreign_keys = true lets the swap happen while other tables point at this one, as Cloudflare recommends.
  • Indexes are dropped with the old table, so recreate them after the swap.
  • The schema needs regenerating with npx d1-record schema, since you wrote this migration by hand.

TIP

Wrangler backs up the database before applying migrations to it.