Supabase migrations ship safely when every change is reversible and tested first

Shipping a mobile app backend feels calm until the day a schema change locks your users out. On the web you can deploy, migrate, and roll forward in one pipeline. On mobile you cannot. Old app versions live on user phones for weeks, backgrounded sessions hold stale connections, and a column rename that looked harmless in staging can turn thousands of installed clients into crash reports. Supabase migrations give you a disciplined path through this, but only if you treat every migration as a production event with a rollback plan.

The stakes are plain. Your Postgres database is the one component every app version shares. Version 1.4 expects a username column, version 1.5 expects display_name, and both hit the same tables at the same time during a staged rollout. If your migration breaks either reader, no client-side fix can save you until users update, which they will not do on your schedule. The migration has to serve both.

Why mobile migrations fail differently than web deploys

Web deploys are atomic in practice. You ship the frontend and the backend together, flip the load balancer, and the old code stops running within minutes. Mobile deploys are staggered by nature. App review takes hours, staged rollouts take days, and a meaningful share of users updates weeks late or never. Your database must therefore be backward compatible with every app version still in the wild, not just the one you shipped today.

The second difference is connection behavior. Mobile clients drop connections constantly as they move between networks, and connection poolers queue retries that can pile up behind a migration lock. A migration that takes 200 milliseconds on your laptop can hold an ACCESS EXCLUSIVE lock for seconds under production load while hundreds of queued queries wait. Users experience that as a frozen app, then a timeout, then a one-star review.

The third difference is observability lag. Crash reports and support tickets arrive hours after the migration ran. By the time you notice the problem, the migration is baked into your schema history and the fix has to be a new forward migration, not a revert button. This is why the habits below matter more for mobile backends than for anything else you will ship.

Keep every migration small and reversible

The single highest-use rule is also the simplest. One migration does one thing. It adds a column, creates an index, or backfills a table. It never does all three. Small migrations are fast to apply, easy to review, and trivial to reason about when something goes wrong at 2am.

Reversibility means you write the undo before you need it. Every migration file should have a tested path back to the previous schema, whether that is a down migration, a compensating migration, or a documented manual step. If you cannot describe the rollback in one sentence, the migration is too big. Split it.

The Supabase CLI makes this workflow concrete. You generate a named migration, write the SQL, and apply it locally first:

supabase migration new add_display_name_to_profiles
supabase migration up
supabase db push --linked

Name migrations after what they do, not when you wrote them. add_display_name_to_profiles tells the next engineer everything. migration_42_final_v2 tells them nothing. Future you, debugging a failed deploy six months from now, will be grateful for the descriptive names.

Keep destructive operations out of routine migrations entirely. Dropping a column, changing a type, or adding a NOT NULL constraint without a default are all breaking changes for old clients. They belong in a planned multi-step sequence with client coordination, never in a Friday afternoon push. If your data safety habits are still forming, start with the row-level security baseline described in our RLS guide and layer migration discipline on top of it.

Expand then contract instead of renaming in place

Renames are the classic mobile migration trap. ALTER TABLE profiles RENAME COLUMN username TO display_name is a single statement that instantly breaks every installed client still reading username. The expand-contract pattern avoids this by stretching the change across two releases.

In the expand phase, you add the new column alongside the old one and dual-write from the client or from a trigger. Both columns exist, both are populated, and every app version keeps working:

-- Expand: add the new column without touching the old one.
alter table public.profiles
  add column if not exists display_name text;

-- Backfill from the old column in small batches.
update public.profiles
  set display_name = username
  where display_name is null
    and username is not null;

In the contract phase, shipped one or two app releases later after old versions have decayed, you stop writing the old column and eventually drop it. The drop itself is a separate migration with its own review, because drops are the one operation you cannot take back without a restore.

Type changes follow the same shape. Add the new-typed column, backfill with a cast, switch readers, then remove the original. It feels slower than a single ALTER COLUMN, and it is. It is also the only approach that survives contact with a real installed base. The extra release cycle is the price of zero downtime, and it is cheap compared to an emergency hotfix through app review.

Guard the migration with timeouts and checks

The most common production migration failure is not bad SQL. It is a good statement that waits forever for a lock. Under load, your ALTER TABLE queues behind long-running reads, the queued migration blocks every new query behind it, and the whole database appears to freeze. Two guards prevent this.

First, set a lock timeout so the migration fails fast instead of wedging the database. A statement that cannot acquire its lock within seconds should abort and let you retry during a quieter window, not hold the queue hostage:

-- Fail fast instead of wedging production behind a lock.
set lock_timeout = '5s';

alter table public.profiles
  add column if not exists avatar_url text;

Second, prefer CONCURRENTLY for index builds. A plain CREATE INDEX takes a write lock that blocks inserts and updates on the table. CREATE INDEX CONCURRENTLY builds without that lock at the cost of taking longer. On tables your mobile clients write to constantly, that tradeoff is always worth it:

-- Non-blocking index build for tables under constant mobile writes.
create index concurrently if not exists idx_profiles_display_name
  on public.profiles (display_name);

One caution. Concurrent index builds cannot run inside a transaction block, so they need their own migration file and cannot be combined with other statements. That is fine. It reinforces the one-thing-per-migration rule, and the Supabase migration runner handles single-statement files without complaint.

Test the migration path before production

Every migration should run against a production-like database before it touches production. The Supabase CLI gives you local parity for free, but local data is tiny and clean. Production data is large, messy, and full of edge cases your seed script never imagined. Bridge that gap deliberately.

At minimum, apply the migration to a staging project seeded with anonymized production data and measure how long each statement takes. A backfill that updates ten rows locally might scan ten million rows in production and hold a lock for minutes. If the staging run is slow, rewrite the migration into batched updates with pauses between batches:

-- Batched backfill: small writes with room for live traffic between them.
update public.profiles
  set display_name = username
  where id in (
    select id from public.profiles
    where display_name is null
      and username is not null
    limit 1000
  );

Run the batch in a loop until zero rows are affected, watching lock waits between rounds. Boring work, and exactly the work that keeps your migration under the lock timeout instead of over it.

Then test the app itself against the migrated staging schema with both the new build and the previous release build. Install the old binary from TestFlight or the Play internal track, point it at staging, and exercise the screens that touch the changed tables. If the old build crashes or shows blank data, your migration is not backward compatible and it is not ready. This two-binary check takes twenty minutes and catches the exact failure class that causes production incidents.

Wire the app to survive both schemas

Migration safety is half database work and half client work. The app should treat every column it reads as potentially absent and every write as potentially rejected by a constraint it does not know about yet. Defensive data access is not paranoia on mobile. It is the normal cost of supporting multiple schema generations at once.

In practice this means selecting explicit columns instead of select *, defaulting missing fields at the parsing layer, and never assuming a write succeeded without checking the response. A Supabase client query that names its columns keeps working when a new nullable column appears, while a star-select can surface unexpected nulls into code paths that never handled them:

// Explicit columns keep old builds stable when new fields appear.
const { data, error } = await supabase
  .from("profiles")
  .select("id, username, display_name, avatar_url")
  .eq("id", userId)
  .maybeSingle();

const displayName =
  data?.display_name ?? data?.username ?? "New member";

Feature-flag the client switch from old column to new column so you can revert app behavior without a binary update if the migration misbehaves. The flag check costs one line and buys you a kill switch that works at the speed of your flag provider instead of the speed of app review. Combined with expand-contract on the database side, you get independent rollback levers on both layers, which is what real zero-downtime operation looks like.

Roll back without panicking

Despite every precaution, a migration will eventually misbehave in production. What happens next should be a runbook, not an improvisation. Decide in advance who can approve a compensating migration, how you verify the problem is the migration and not the client, and at what error threshold you act.

For additive mistakes, the fix is usually a forward migration that drops or renames the new object. For data corruption from a bad backfill, the fix is a correcting backfill, ideally from a backup snapshot you verified before the migration window. For lock pileups, the fix is cancelling the migration query and retrying with tighter timeouts off-peak. None of these require creativity in the moment if you wrote them down beforehand.

The official Supabase migration workflow, including local development setup and the push sequence, is documented in the Supabase database migrations guide. Keep it bookmarked next to your runbook. When the alert fires, you want procedures, not archaeology.

One last habit closes the loop. After every migration, record what happened: how long it took, whether the lock timeout fired, whether old builds stayed healthy, and what you would do differently. Five lines in a shared doc, written while the memory is fresh. Over a year, those notes become the migration culture that lets a small team run a mobile backend with the confidence of a much larger one.

Sources

• Supabase docs: database migrations workflow and CLI commands.
• Internal: RLS baseline for mobile data safety on this blog.