Migrating Supabase dev to production

Let's start with the problem I have first. I’ve been developing this piece of software using Lovable for a good amount of time. It’s an internal project that we use. Obviously, the backend is built using Supabase.

Now most Vibe Programmers would be able to relate to my problem here. Because either we don’t know anything about engineering or at least we are not from this stack. For me I’ve been working with Unity for last 7 years. And I hate JS with all my heart. I know why I hate it though. Anyway, the last time I worked on web technology personally was probably before my graduation. So basically, my syntax knowledge is as good as a grad level student regarding web technology. But well since AI boom, I can now work on web projects because I do understand code; it’s just a pain to work with the weird js paradigm. And not to mention writing SQL is a pain. Thanks to AI I don’t need to do that either. Sharing all these contexts so that those who are with more experience would be able to empathize to the problem.

Me Vibe Coding

So anyhow. I had deployed the project. Delivered it to the internal client. But now I’ve a problem. The client is expecting changes. But the client project backend (supabase project) is a different project than the one I have. Because obviously we wouldn’t be making changes to production like that. the v2 of the project introduced massive changes to the database. Like changing table columns, DB Functions, Triggers etc. And I had no clue as to how to even proceed to migrating all the changes without destroying existing data on production.

Me when Support Requested on AI generated Project

Have I mentioned that I’m super lazy? I will never do any manual labor if I know that there must be a better way. One way was to manually update all the SQL. However There had to be a better way. While looking into it I found that supabase has this sdk where I can do diff against 2 databases. with their sdk I can diff a localdb with the hosted version. But this wasn’t what I needed. I have my dev supabase project which is hosted by supabase and another production supabase project which is also hosted by supabase.
What I needed was to a way to diff 2 different supabase projects. Essentially Supabase is basically based on postgres database. And supabase provides connection strings to directly connect with the postgres database. So I thought maybe I can use that. But that means Supabase SDK is out of question.

So I did what I do best. Told ChatGPT bro that,

I have 2 Postgres databases. One is dev and another is production.
The Dev database has lots of changes that I need to bring on prodction what
can be done?

Now bro gave me a few ideas. However, the one I really liked was something similar to supabase diff. Basically it said there’s this tool

GitHub - djrobstep/migra: Like diff but for PostgreSQL schemas

This does exactly what I want.

migra magically figures out all the statements required to get from A to B.

And I wanted a magical tool that does it for me. But this is still too much work. I wanted something like I’ll put in the connection strings for two databases, and it will tell me which public tables, functions, triggers have changed and will generated the SQL to do the migration.

This time I went to Claude Bro. And after a few back and forth I got this powershell script which does it for me.

Basically, what it does is it guides me with a step by step process for me to generate the migration.sql file. Which I can run on my production supabase to bring my schema changes. [Sorry I forgot to take screenshots of the CLI Now the changes are already done so can’t show]

And honestly it worked way better than I expected. it wasn’t perfect. But it generated 98% of the SQL. I had to write one drop function SQL and that’s it.

Thanks to AI now I can become a Full Stack Web Dev.

Me as a Full Stack Dev

(At least can cover most cases)