Skip to content

How Do I Change My Database Schema Without Losing the Data That's Already There?

6 min read

Can you change your database schema without losing the data already in it? Yes, if you make the change additive and take a backup first. The danger is not migrations in general, it is a handful of specific moves (renaming a column, dropping one, adding a NOT NULL constraint) that silently delete or reject real rows. Once you have actual users, the wrong one-line change can wipe data you can never get back.

Why this happens

In development, your tables are empty or full of throwaway seed data, so any schema change "just works." You rename a column, the app still runs, nothing is lost because there was nothing to lose. The moment real users show up, the same change has to carry existing rows across, and that is where it breaks.

An AI agent makes this easy to trigger. You ask it to "rename name to full_name" or "make email required," and it produces a migration that does exactly that, cleanly, against an empty table. It has no idea you now have 200 signed-up users, and it will not warn you that recreating a column drops the old one, or that adding NOT NULL fails the instant one row has a blank value. It built what you asked, not the safety you did not think to ask for.

How to check

  1. Do you have real data? If your main tables have rows from anyone who is not you, treat every schema change as production. Check row counts in your Supabase or Firebase dashboard, or run SELECT count(*) FROM your_table;.
  2. Read the migration before you run it. Look for the words DROP COLUMN, DROP TABLE, RENAME, NOT NULL, ALTER COLUMN ... TYPE, and UNIQUE. These are the operations that can destroy or reject existing rows.
  3. Check for a "reset" habit. Some agent workflows regenerate the schema by dropping and recreating tables (Prisma's db push --force-reset, a drop-and-recreate seed script). On a database with users, that is a full wipe. If your deploy runs anything like it, stop.

The fix

  1. Back up first, every time. Take a snapshot before any schema change. Supabase, Firebase, Neon, and Railway all offer point-in-time or on-demand backups. If you cannot answer "where is my backup from five minutes ago," read do I need database backups before beta launch before you touch the schema.
  2. Add, never rename in place. To rename name to full_name: add the new column, copy the data across (UPDATE users SET full_name = name;), update the app to read the new column, and only drop the old one in a later, separate deploy once nothing reads it.
  3. Make new columns nullable or give them a default. Adding a required column to a table with existing rows fails or blanks them. Add it nullable, backfill values for the old rows, then add the NOT NULL constraint.
  4. Run it in a transaction where you can. Wrapping the migration in BEGIN / COMMIT means that if one statement fails, the whole change rolls back instead of leaving the table half-migrated.
  5. Test on a copy first. Restore your backup into a staging database, run the migration there, confirm the row counts match and the data looks right, then run it for real.

The trap to avoid

The common wrong fix is to "just let the ORM sync it." Running prisma db push, drizzle push, or an auto-migrate on a live database lets the tool decide how to reconcile the schema, and its idea of the shortest path is often to drop and recreate the column, taking your data with it. Convenient in development, quietly destructive in production. Use explicit, reviewed migration files against anything holding real user data.

Where this fits

A migration that drops a column is indistinguishable from a bug until someone's data is gone, and by then it is a support email you cannot answer. Getting beta-ready means knowing which of your pending schema changes are additive and which are destructive, and having a backup you have actually tested restoring. The free Readiness Report checks whether you have real data at risk and whether a recoverable backup exists; if the answer is no, that is also what how do I restore my database if I delete real user data is about. If you would rather have the safe migration path and the backups set up for you, that is the Finishing Pass.