Docs / Getting started / Your schema & migrations

Your schema & migrations

A fresh project's D1 has only the auth tables. Your app's tables — posts, notes, whatever — are yours to create. There are three ways, ordered from quickest-to-click to most repeatable.

Shared D1. Your tables coexist with Flarelink's auth tables (user, account, verification, flarelink_config) in the same database. Don't reuse those names. Do foreign-key your tables to user(id) — the dashboard's Users admin cascade-deletes dependent rows when you remove a user, so use REFERENCES "user"(id) ON DELETE CASCADE.

1. The table editor (visual)

In the dashboard's Database view, click New table. Add columns, pick logical types (UUID, Boolean, JSON, DateTime, Email, URL), set primary key / not-null / unique. Flarelink compiles these to the right SQLite physical types + DEFAULT / CHECK constraints (e.g. UUID → TEXT DEFAULT (lower(hex(randomblob(16))))). Best for getting started and one-off changes.

2. The SQL console (full control)

The SQL editor runs arbitrary SQL against your D1 — same power as wrangler d1 execute, with multi-tab, saved snippets, and query history. Paste a CREATE TABLE and run it. D1 treats ;-separated statements as one atomic batch, so a multi-statement migration applies all-or-nothing.

CREATE TABLE posts (
  id        TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
  author_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  title     TEXT NOT NULL,
  body      TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_posts_author ON posts(author_id, created_at);

3. Repeatable migrations (keep SQL in your repo)

For anything beyond a throwaway, keep your schema as numbered .sql files in your repo. There's no flarelink migrate CLI yet, but the D1 is on your Cloudflare account — so you can apply files with Wrangler directly, the standard Cloudflare D1 migration path. It supports whole files and multi-statement batches (which the SDK does not — see the note below).

# wrangler.toml — bind your project's D1 (name + id are on the dashboard's
# deployment card under "resources")
[[d1_databases]]
binding = "DB"
database_name = "myapp-db"
database_id = "…"

# migrations/0001_posts.sql, 0002_comments.sql, …  (checked into your repo)
# apply one against your remote D1:
npx wrangler d1 execute myapp-db --remote --file=migrations/0001_posts.sql

# or use Wrangler's own migrations system (tracks what's applied):
npx wrangler d1 migrations apply myapp-db --remote

The SDK runs one statement per call. flarelink.sql\`…\` goes through the auth Worker's prepare().all() path, which executes a single statement — so it's great for runtime queries but not for applying multi-statement .sql files. Use Wrangler (above) or the dashboard SQL console (which runs ;-batches atomically) for schema work.

Altering tables

SQLite's ALTER TABLE is limited (no drop-column on older engines, no FK changes). The dashboard's Edit schema does the safe 12-step rebuild for you (create temp → copy → drop → rename, atomically) when you rename/reorder/retype/add/drop columns. For scripted migrations, do the same rebuild pattern in your .sql file. Foreign keys are supported by D1.

Once your tables exist, query them with the database SDK.

Something unclear or missing? [email protected] llms-full.txt ↗