> ## Documentation Index > Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt > Use this file to discover all available pages before exploring further. # Supabase CLI: When and How (Single Source of Truth) > Version: 1.3.0 Last Updated: 2026-04-24 Purpose: Canonical guide so developers and AI agents know exactly when to use the Supabase CLI, how to run it, and how… **Version:** 1.3.0\ **Last Updated:** 2026-04-24\ **Purpose:** Canonical guide so developers and AI agents know exactly when to use the Supabase CLI, how to run it, and how to test migrations locally. Reduces "we keep running into this" confusion. ### Migration squash (2026-02-11) On 2026-02-11 the project **squashed 645 migrations** into two files: 1. **Baseline DDL:** `20260211182655_6aeefd1c-c5cc-4eaf-98b0-3e38abc7ba6a.sql` (full schema) 2. **DML companion:** `20260211222508_post_squash_dml.sql` (cron jobs, storage buckets, picklist seed data) Rollback point: git tag `pre-squash-v1`. After the squash, `npx supabase db reset` applies only these two migrations (much faster). See [After a migration squash](#after-a-migration-squash) for syncing with remote. > **For AI agents:** Migrations and local DB operations are **CLI-only**. Never use Supabase MCP to create or apply migrations. If you suggest `supabase start` or `supabase db reset`, first remind the user that the Supabase CLI must be installed and on PATH; if not, link to [Install the Supabase CLI](https://supabase.com/docs/guides/cli/getting-started#install-the-supabase-cli). *** ## When to Use CLI vs MCP | I need to… | Use | Do NOT use | | ---------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------- | | Create a migration file (DDL) | **Declarative:** edit `supabase/schemas/**/*.sql`, then **CLI** `npx supabase stop` and `npx supabase db diff -f ` (see [Declarative Schema Workflow](#declarative-schema-workflow-ddl--pg-toolbelt)). **Legacy:** `npx supabase migration new ` for caveat-only SQL. | MCP (no create-migration) | | Apply migrations locally | **CLI** `npx supabase start` then `npx supabase db reset` (or `supabase start` / `supabase db reset` if on PATH) | MCP | | Push migrations to remote (staging/production) | **CLI** `npx supabase link --project-ref ` then `npx supabase db push` (or `supabase` if on PATH) | MCP | | Generate TypeScript types | **CLI** `npx supabase gen types typescript --local` (or `supabase gen types` if on PATH) | MCP optional | | Start/stop local Supabase | **CLI** `npx supabase start` / `npx supabase stop` (or `supabase start` / `supabase stop` if on PATH) | MCP | | Read project URL, list branches, list tables | **MCP** (e.g. `mcp_supabase_get_project_url`, `mcp_supabase_list_migrations`) | — | | Search Supabase docs | **MCP** `mcp_supabase_search_docs` | — | **Rule of thumb:** If it changes schema or runs migrations → **CLI**. If it only reads or looks up docs → **MCP**. *** ## Ensure Supabase CLI Is Installed The Supabase CLI is **required** for local migration testing and for pushing to remotes. It is **not** bundled with the repo. If you prefer not to install globally, use `npx supabase` for all commands (see Running the CLI below). 1. **Install (optional):** Follow [Install the Supabase CLI](https://supabase.com/docs/guides/cli/getting-started#install-the-supabase-cli) (e.g. npm: `npm install -g supabase`, or Scoop/Homebrew on Windows/Mac). 2. **Verify:** From the project root run: ```bash theme={null} supabase --version ``` If the command is not found, the CLI is not on PATH or not installed. **For AI agents:** When suggesting any `supabase` command, if the user reports "command not found" or similar, suggest: run with `npx supabase` from the project root, or install the CLI and add to PATH; link to the official install guide above. **CLI version floor:** Use Supabase CLI **2.77.0 or newer** for branch and Edge Function deploys. Older versions or mismatched runtimes can cause: * Branch-action failures (e.g. lockfile parse errors). * Repeated deploy warnings: *"Specifying decorator through flags is no longer supported. Please use deno.json instead."* (fixed in [supabase/cli#4599](https://github.com/supabase/cli/pull/4599); CLI 2.77.0+ no longer passes deprecated Deno 2 flags). Check with `npx supabase --version`; update with `supabase update` if installed globally, or use `npx supabase` to get the version bundled with the project. Before bulk function deploy, run the [deploy preflight](#edge-functions-deploy-preflight) to validate CLI version. ### Running the CLI * **Option A (no install):** From the project root, run `npx supabase ` (e.g. `npx supabase start`, `npx supabase db reset`). Uses the CLI via npx; no global install or PATH change. * **Option B (install):** Install the CLI globally and add to PATH; then run `supabase ` (see install link). Prefer Option A if the team does not want global tools. *** ## Edge Functions: Lockfile and Deploy Compatibility Branch sync and Edge Function deploys run in Supabase’s cloud environment, which uses a Deno runtime that **does not support lockfile version 5** (generated by newer Deno 2.x). To avoid `Unsupported lockfile version '5'` and `Failed reading lockfile at '/app/supabase/functions/deno.lock'` during branch actions or `supabase functions deploy`: 1. **Lockfile policy:** The repo uses **no committed lockfile** for Edge Functions. `supabase/functions/deno.json` has `"lock": false` so the bundler does not require or parse a lockfile. Do **not** commit `supabase/functions/deno.lock`; if you regenerate it locally for development, add `supabase/functions/deno.lock` to `.gitignore` so it is not pushed. 2. **Preflight before branch sync or bulk deploy:** From project root run: ```bash theme={null} npx supabase --version ``` Ensure CLI is 2.77.0 or newer. Then confirm `supabase/functions/deno.json` contains `"lock": false` and that `supabase/functions/deno.lock` is either absent or gitignored. 3. **Deploying functions:** Link to the target project (branch or main), then deploy. For bulk deploy with parallel jobs use `--use-api --jobs 4`; if you hit rate limits (429), retry after a short delay or deploy in smaller batches. See [SUPABASE\_MULTI\_ENV\_SETUP.md](SUPABASE_MULTI_ENV_SETUP.md) for staging/production project refs and branch workflow. #### Edge Functions deploy preflight Before running `supabase functions deploy` (or bulk deploy), run the version check so deploys do not emit decorator deprecation warnings: ```bash theme={null} npm run supabase:deploy:preflight ``` Or from project root: `npx tsx scripts/supabase/deploy-preflight.ts`. The script exits with code 1 and prints upgrade instructions if the Supabase CLI is below the required version (2.77.0). See [scripts/supabase/deploy-preflight.ts](../../../scripts/supabase/deploy-preflight.ts). #### Expected output during db push and functions deploy These INFO lines are **expected** and not errors; no config change is required: * **"No buckets found. Try setting \[storage.buckets.name] in config.toml."** — The project creates storage buckets via migrations (e.g. `storage.buckets` INSERTs in `supabase/migrations/`), not via `config.toml`. You can ignore this. * **"Seeding data is disabled in config.toml."** — Remote (staging/production) seeding is intentionally disabled; local seeding uses `[db.seed]` in `config.toml` for `supabase db reset` only. If you see repeated **"Specifying decorator through flags is no longer supported. Please use deno.json instead."** during `supabase functions deploy`, your CLI is older than 2.77.0. Run `npm run supabase:deploy:preflight` and upgrade the CLI (e.g. `supabase update` or use `npx supabase`). *** ## Creating migrations (avoid duplicate timestamps) * **Prefer:** Use `npx supabase migration new ` (or `supabase migration new `) so the CLI assigns a unique timestamp. Do not create two migration files with the same timestamp; the DB tracks migrations by version (timestamp) and duplicate keys cause apply failures. * **If you must add a file manually:** Use a unique `YYYYMMDDHHMMSS` prefix; e.g. list files in `supabase/migrations/` and pick a timestamp that does not already exist. *** ## Local Migration Workflow (Before Committing) Use this sequence **before committing** new or changed migration files: 1. **Start local stack** (if not already running): ```bash theme={null} npx supabase start ``` (Or `supabase start` if CLI is on PATH. Requires Docker. First run may download images.) 2. **Apply all migrations to the local DB:** ```bash theme={null} npx supabase db reset ``` (Or `supabase db reset` if CLI is on PATH.) Base seeds run automatically (see `supabase/config.toml` \[db.seed]), creating test users for E2E. Use `supabase migration up` only if you already have a running local DB and want to apply just pending migrations. 3. **Validate migration file(s):** ```bash theme={null} npm run validate-migration -- --latest ``` Or: `npx ts-node scripts/database/validate-migration.ts --latest` 4. **Run RLS tests:** ```bash theme={null} npm run test:rls ``` 5. **Then** commit and push. Use `npx supabase db push` (or `supabase db push`) **only** when you intend to update a **linked** remote (staging/production), after local tests pass. If you see **"Remote migration versions not found in local migrations directory"** when running `supabase db push`, see [SUPABASE\_MIGRATION\_SYNC\_ERROR.md](../TROUBLESHOOTING_GUIDE.md#supabase-remote-migration-versions-not-found). *** ## Declarative Schema Workflow (DDL + pg-toolbelt) **Canonical DDL** for the product database lives under [`supabase/schemas/`](../../../supabase/schemas/) as version-controlled `.sql` (desired end state). **Migrations** under `supabase/migrations/` remain what Supabase applies in CI and remotes; for normal DDL they are **generated** with the Supabase CLI diff, not hand-written. **Assistive tooling:** [`@supabase/pg-delta`](https://www.npmjs.com/package/@supabase/pg-delta) / [`@supabase/pg-topo`](https://www.npmjs.com/package/@supabase/pg-topo) (from [pg-toolbelt](https://github.com/supabase/pg-toolbelt)) support export, optional apply-to-fresh-DB checks, and static ordering validation. They do **not** replace `supabase db diff` for migration generation. ### Typical DDL change sequence 1. **Edit** the appropriate `.sql` file(s) under `supabase/schemas/` (or add new files; see [DECLARATIVE\_SCHEMA\_GUIDE.md](DECLARATIVE_SCHEMA_GUIDE.md) for layout and caveats). 2. **Optional:** `npm run db:schemas:validate` — runs `@supabase/pg-topo` over the tree (skips if no `.sql` files yet). 3. **Stop** the local stack so the shadow DB used by diff is clean: ```bash theme={null} npx supabase stop ``` 4. **Generate** a migration from the drift between declared schema and the migration-applied baseline (per Supabase CLI): ```bash theme={null} npx supabase db diff -f ``` Convenience (same steps wired): `npm run db:schemas:diff -- -f ` 5. **Review** the new file in `supabase/migrations/` (diffs can be large; watch for unintended drops). 6. **Start** local stack and **apply** everything: ```bash theme={null} npx supabase start npx supabase db reset ``` 7. **Validate + test** as in [Local Migration Workflow](#local-migration-workflow-before-committing): `npm run validate-migration -- --latest`, `npm run test:rls`. ### Re-export schema from a running local DB After `npx supabase db reset` (or when refreshing the declarative tree from the DB catalog): ```bash theme={null} npm run db:schemas:export ``` Uses `pgdelta declarative export` with `--integration supabase`. Override URLs/paths with `SUPABASE_LOCAL_DB_URL`, `SUPABASE_SCHEMAS_DIR` if needed. ### Known diff caveats (hand-written migrations still required) Some changes are **not** reliably represented by schema diff alone (migra / CLI limitations). Keep these as **explicit versioned migrations** (see [MIGRATION\_LANES.md](MIGRATION_LANES.md) for DML lane rules), not only as declarative SQL: * DML (`INSERT` / `UPDATE` / `DELETE`) and one-off data repairs * View ownership / grants, `SECURITY INVOKER` on views, materialized views (and some view column type changes) * `ALTER POLICY` vs full policy recreate, column-level privileges, schema-level privileges * Comments (`COMMENT ON`), partitions, `ALTER PUBLICATION … ADD TABLE`, `CREATE DOMAIN`, duplicated `GRANT` noise from default privileges Full checklist: [DECLARATIVE\_SCHEMA\_GUIDE.md](DECLARATIVE_SCHEMA_GUIDE.md). ### Do not * Hand-edit generated DDL in `supabase/migrations/` for normal schema work (fix the declarative source and re-run `db diff`). * Run `supabase db diff` while `supabase start` is still running (stop first). *** ## Debug Test Failures Locally (No Remote) To debug test failures **without touching the remote** (no link, no push, no branches), use only the local Supabase stack. This avoids the "Remote migration versions not found in local migrations directory" error. 1. **Start the local Supabase stack** (requires Docker): ```bash theme={null} npx supabase start ``` Or `supabase start` if the CLI is on PATH. 2. **Apply all local migrations** to the local DB: ```bash theme={null} npx supabase db reset ``` This uses only files in `supabase/migrations/` and does not contact the remote. Base seeds (organizations, sites, departments, users) run automatically per `supabase/config.toml` `[db.seed]`, so test users (e.g. [admin@test-org-alpha.example](mailto:admin@test-org-alpha.example), password `password`) exist for E2E and RLS. 3. **Validate the latest migration** (optional): ```bash theme={null} npx ts-node scripts/database/validate-migration.ts --latest ``` Or add a script to `package.json`: `"validate-migration": "ts-node scripts/database/validate-migration.ts"` and run `npm run validate-migration -- --latest`. 4. **Run tests** (RLS, unit, integration, etc.): ```bash theme={null} npm run test:rls npm run test:unit npm run test:integration ``` Point the app at the local DB by using the env values printed after `supabase start` (e.g. `DB URL`, `Studio URL`). For RLS/unit/integration tests that use the test setup, ensure `tests/setup.ts` or your test env is configured to use the local Supabase URL and keys from `npx supabase status` if needed. 5. **Stop when done** (optional): ```bash theme={null} npx supabase stop ``` **Important:** Do not run `supabase link`, `supabase db push`, or `supabase branches` when you only want to run tests locally. Those commands compare with the remote and can trigger "Remote migration versions not found in local migrations directory" if your local migration filenames don’t match the remote’s migration history. *** ## Pushing to a Remote (Staging/Production) 1. Link to the target project (e.g. staging): ```bash theme={null} npx supabase link --project-ref ``` (Or `supabase link` if CLI is on PATH.) Refs: see [SUPABASE\_MULTI\_ENV\_SETUP.md](SUPABASE_MULTI_ENV_SETUP.md). 2. Push migrations: ```bash theme={null} npx supabase db push ``` (Or `supabase db push` if CLI is on PATH.) 3. If sync errors appear, resolve them using [SUPABASE\_MIGRATION\_SYNC\_ERROR.md](../TROUBLESHOOTING_GUIDE.md#supabase-remote-migration-versions-not-found) before pushing again. *** ## Connection Pooler Matrix Use this matrix when wiring DB URLs in app/runtime config: | Connection target | Typical hostname pattern | Prepared statement setting | Notes | | ------------------------------------- | ------------------------------- | -------------------------------------------------------------------- | ---------------------------------------------------------------------------- | | Direct Postgres | `db..supabase.co` | Allowed | Use for migrations, `db push`, and long-running admin operations. | | Supabase pooler (transaction mode) | `aws-*.pooler.supabase.com` | **Disable** (`prepared_statements=false` or client `pgbouncer=true`) | Required for pooled connections to avoid prepared statement protocol errors. | | Session pooler / managed runtime pool | provider-specific pool endpoint | Match provider guidance | If uncertain, default to transaction-pooler-safe settings. | Guardrail: PR CI scans `supabase/functions/**` for pooled-connection anti-patterns and rejects changes that add `prepare: true`, bare `SET` (instead of `SET LOCAL`), `LISTEN`, or `new pg.Pool(...)` without an explicit `max:` cap (unless explicitly bypassed by label). *** ## Pulling Dev drift into the repo (Lovable / MCP changes) When schema changes were applied to the Dev project (`zkgxozahyczcnzpwhbbf`) directly via Lovable or the Supabase MCP server, capture them as a real migration in `supabase/migrations/` instead of letting the repo lag. **One-shot from a laptop (Docker required):** ```bash theme={null} # .env.local must export SUPABASE_DEV_PROJECT_ID, SUPABASE_DB_PASSWORD, # and SUPABASE_ACCESS_TOKEN (use the Dev DB password and your personal token). npm run db:pull-from-dev -- --name add_widget_table ``` The script: 1. Runs the CLI version preflight. 2. `supabase link --project-ref $SUPABASE_DEV_PROJECT_ID --yes`. 3. `supabase db diff --linked -s public,storage,auth -f _` — emits a real migration when drift exists; exits cleanly when Dev is in sync. 4. `supabase db reset` to verify the captured migration applies locally. 5. Runs the same guards as CI (`db:schemas:lint`, `check-rls-initplan`, `check-fk-indexes`, `check-migration-lane`). 6. If `supabase/schemas/` is bootstrapped, runs `npm run db:schemas:export` so the declarative source of truth stays current (per [DECLARATIVE\_SCHEMA\_GUIDE.md](DECLARATIVE_SCHEMA_GUIDE.md)). Skipped with a console hint when the tree only has `README.md`. 7. Regenerates `src/integrations/supabase/types.ts` from the local DB. Types are written only after a successful `gen types` run, so a CLI failure won't leave the file truncated. Review the migration file, rename if the slug doesn't describe the change well, and commit it alongside any refreshed `supabase/schemas/**/*.sql` and the regenerated types file. **On a schedule:** [`supabase-sync-from-dev.yml`](../../../.github/workflows/supabase-sync-from-dev.yml) runs daily at 06:00 UTC and on `workflow_dispatch`. It performs the same flow on the runner and opens a PR to `dev` with the captured migration. ### Forward Push + Lineage Reconcile Ops * **Forward push gate (dev -> production PRs):** [`supabase-promote-pr.yml`](../../../.github/workflows/supabase-promote-pr.yml) now enforces lane checks plus promotion lineage parity (`scripts/database/check-promotion-lineage.ts`) before release PRs merge. * **Catalog parity monitor (prod reality drift):** [`db-prod-catalog-parity.yml`](../../../.github/workflows/db-prod-catalog-parity.yml) runs nightly and opens/updates an issue when committed prod catalog snapshots diverge. * **Lineage reconcile runbook (manual, approval-gated):** [`db-prod-lineage-reconcile.yml`](../../../.github/workflows/db-prod-lineage-reconcile.yml) can repair migration history to `applied` up to a cutoff timestamp for `prod` or `dev`. It requires the `db:approved-lineage-reconcile` label on the referenced PR and enforces a post-repair pending threshold via dry-run. * **Local mirror command:** `npm run db:pull-from-dev -- --name ` now auto-loads `.env.local` / `.env` when present, but still requires these vars: * `SUPABASE_DEV_PROJECT_ID` * `SUPABASE_DB_PASSWORD` (Dev DB password) * `SUPABASE_ACCESS_TOKEN` *** ## Quick Reference for Agents * **Creating migrations:** CLI only. `npx supabase migration new ` (or `supabase migration new ` if CLI on PATH). Use the CLI so the timestamp is unique; avoid manually adding a file with a duplicate timestamp. * **Testing migrations locally:** CLI only. Run `npx supabase start` (or `supabase start`) then `npx supabase db reset` (or `supabase db reset`) → `npm run validate-migration -- --latest` → `npm run test:rls`. * **Deploying migrations to remote:** CLI only. `npx supabase link` then `npx supabase db push` (or `supabase` if on PATH). * **Reading state / docs:** MCP is fine (list tables, list migrations, search docs). * **Edge Functions / branch deploy:** Keep `"lock": false` in `supabase/functions/deno.json`; do not commit `deno.lock` (see [Edge Functions: Lockfile and Deploy Compatibility](#edge-functions-lockfile-and-deploy-compatibility)). * **If user doesn’t have CLI:** Suggest running with `npx supabase` from the project root, or install the CLI and add to PATH; link to [Supabase CLI getting started](https://supabase.com/docs/guides/cli/getting-started#install-the-supabase-cli). * **Optional:** Add `supabase/seed.sql` to run seed data after `db reset`; see supabase/seeds/README.md. *** ## Optional: seed data after reset If `supabase/seed.sql` exists, it runs automatically after `supabase db reset` (or `npx supabase db reset`). The repo can include a `seed.sql` that includes the contents of `supabase/seeds/` (base then modules) so local resets load test data. See supabase/seeds/README.md for order and guidelines. *** ## After a migration squash When migrations have been squashed locally, the **remote** (staging/production) still has the old migration history (e.g. 645 applied versions). To sync: 1. **Option A – Repair remote history (recommended when you must keep the same project):** * Link: `npx supabase link --project-ref ` * Mark all **old** migration versions as reverted:\ `npx supabase migration repair --status reverted ` for each old timestamp. * Mark the **squashed baseline** as applied:\ `npx supabase migration repair --status applied 20260211182655` * Mark the **DML companion** as applied:\ `npx supabase migration repair --status applied 20260211222508` * Repeat for each remote (staging, then production). Scripting the repair loop for many timestamps is recommended; see Supabase docs for [migration repair](https://supabase.com/docs/reference/cli/supabase-migration-repair). 2. **Option B – New project / branch:** * Create a new Supabase project or branch and run `npx supabase db push` so only the two squashed migrations are applied. Then point the app at the new project/branch. 3. **Team coordination (after squash lands on main):** * Notify all developers: run `git pull` and then `npx supabase db reset` (only two migrations will apply). * Anyone with open feature branches that added migrations should rebase onto the new `main` and resolve conflicts by keeping only the new migration files (delete references to old migration filenames). * Optionally clean up local stashes or branches that reference the pre-squash migration set; the rollback point is git tag `pre-squash-v1` if needed. *** ## Going-forward: squash cadence and DML naming * **Squash cadence:** Plan to squash again every **major release** or roughly every **100 migrations**, whichever comes first, so `db reset` stays fast. * **DML in migrations:** When a migration contains **standalone** INSERT/UPDATE/DELETE (cron, storage buckets, seed data), name it with a hint so the next squash is easier to audit (e.g. `_seed_`, `_data_`, `_cron_` in the migration name). * **Periodic audit:** Run `scripts/migration/lint-migrations.ts` periodically to track migration count and size. *** ## Related Docs * [DECLARATIVE\_SCHEMA\_GUIDE.md](DECLARATIVE_SCHEMA_GUIDE.md) — Declarative `supabase/schemas/` + `supabase db diff` workflow * [MIGRATION\_LANES.md](MIGRATION_LANES.md) — Schema vs system-defaults migration lanes (CI) * [MCP\_USAGE.md](../MCP_USAGE.md) — MCP vs CLI overview and testing checklist * [SUPABASE\_MCP\_USAGE.md](../MCP_USAGE.md) — Full MCP/CLI decision matrix * [SUPABASE\_MIGRATION\_SYNC\_ERROR.md](../TROUBLESHOOTING_GUIDE.md#supabase-remote-migration-versions-not-found) — Fixing "Remote migration versions not found" * [SUPABASE\_MULTI\_ENV\_SETUP.md](SUPABASE_MULTI_ENV_SETUP.md) — Local, staging, production config