Starting with Supabase Migrations in Existing Environments

[ianbaker615]

Hello Supabase Community,

I am trying to start using Supabase migrations to manage and track changes to databases across different environments (dev, staging, and prod). Up until now, all changes to the database tables have been made manually via the Supabase UI. I just created dev and staging as copies of prod about a week ago.

Objective:

• Start using migrations to make database changes from this point forward.
• Ensure smooth transition without errors when applying migrations.

Current Issues:

1. Baseline Migration:

• Created a baseline migration capturing the current state.
• Encountering errors such as ERROR: type "http_header" already exists (SQLSTATE 42710), indicating pre-existing objects are causing conflicts.
• AI keeps suggesting using DROP IF EXISTS in the migration file, but any baseline migration that I generate is thousands of lines long so it doesn't seem practical.

2. Applying New Migrations:

• Created small test migrations for subsequent changes.
• These migrations are also failing, often because they conflict with the existing state of objects in the database.

Questions:

1. How can I effectively create a baseline migration for my existing schema that won't fail due to pre-existing objects?
2. What is the best approach to ensure my environment databases (dev, staging, prod) are in sync and handle existing objects gracefully?
3. Any recommended practices or steps when transitioning from manual database changes to using migrations in Supabase?

I've attempted to follow typical migration practices and ensuring a consistent starting point, but I'm still running into issues. Any help, guidance, or shared experiences would be greatly appreciated.
Thank you!

[nicelion]

Hello fellow Ian.

Currently having the same issues.

Were you able to find a solution?