Seeking advice: What is the professional standard for DB migration workflows?

[vishalkanteppa]

Hi everyone,

My team is working to establish a robust and foolproof CI/CD workflow for our Supabase database migrations, and we'd love to get some advice and feedback from the community on best practices.

We have a setup with separate dev and prod projects. After some research, here is the workflow we're currently using:

Our Current Workflow

1. Schema Change: We make schema changes (e.g., add a table, create a policy) in the UI of our remote dev project.
2. Link Project: We ensure our local repo is linked to the dev project with supabase link --project-ref <dev-ref>.
3. Generate Migration: We create an empty migration file (supabase migration new <migration_file_name>) and then populate it by comparing our local state against the remote dev project (supabase db diff --linked --file <migration_file_name>).
4. Local Verification: We run supabase db reset to tear down and rebuild our local database, ensuring the full migration history is valid.
5. Deploy: After committing the new migration to Git, a GitHub Action automatically runs supabase db push to our prod project on merge to main.

While this process works, we're wondering if it meets professional standards, or if there are potential failure points we're not seeing.

• Is db diff always reliable? For complex changes, like modifying function logic or intricate policies, can we always trust the generated diff? Are there known edge cases where manual review and editing of the migration file is critical?
• Is there a better "code first" approach? Making changes in a UI and then pulling them down feels like it could lead to human error. Is the recommended practice for teams to write migration SQL files manually from the start and avoid making direct changes in the dashboard altogether?
• How do you handle data migrations? This workflow seems solid for schema, but what is the professional standard for data migrations (e.g., transforming data from one format to another before dropping a column)? Do you handle these in separate scripts outside the main migration flow?

Any advice, critique of our current process, or links to established best practices would be hugely appreciated.

Thanks in advance!

[erondpowell]

Surprised this has zero replies or comments!
It's such a massive docs gap to getting prod ready and there's so many moving parts.
@vishalkanteppa What did you end up doing for ur dev to prod workflow?

[jennypelotech]

Hey! Your post sparked a really interesting conversation among our engineers at Pelotech. We didn't land on one definitive answer (honestly, there are more tradeoffs here than clean solutions) but I ended up writing a blog post based on their discussion that touches on all three of your questions (db diff reliability, code-first approaches, and data migrations).

Hope it's useful: No Easy Answers: A Conversation About Professional DB Migration Workflows