Add automatic database migrations

[carlomigueldy]

Overview

I haven't read abour this in context of supabase but the idea is setting up a CI step that will update the database schema when we roll out updates to production. This so we don't have the pressure of manually running a script to keep the schema in sync. When I look for supabase migrations I ended up at their cli https://supabase.com/blog/2021/03/31/supabase-cli

From @JoviDeCroock

---

Migrations are like version control for your database, allowing your team to define and share the application's database schema definition. If you have ever had to tell a teammate to manually add a column to their local database schema after pulling in your changes from source control, you've faced the problem that database migrations solve. (Read more here)

I have worked with Laravel projects in the past and it's where I got introduced to database migrations, it was fairly easy to use it with just a few artisan commands to run e.g. php artisan make:migration update_users_table, php artisan migrate

PS: Thought I'd create an issue for this after you sent me a DM about it @JoviDeCroock

How it works

The current process of updating database tables, modifying columns, adding attributes to columns, etc is to manually run SQL statements inside Supabase or PostgreSQL client (e.g. PGAdmin). With that process we can't really keep track of when the attributes were modified into the tables or forget to run the SQL statements.

Typically how database migrations would work:

• Create a migration file with the current timestamp as its filename at the time it was created
• Inside the migration file, have a SQL statement that modifies the database (e.g. ALTER COLUMN ...)
• Run a terminal command to finally execute these migrations into the database (e.g. php artisan migrate)

Migrations are usually be generated from CLI tools.

References

There is an active pull request by @G3root here where it uses Prisma

• Prisma Migrations, it has support and a few resources with integrating to Supabase: https://www.prisma.io/docs/concepts/components/prisma-migrate
• An example from Laravel, this won't likely be used here in the project but just a reference: https://laravel.com/docs/8.x/migrations

[with-heart]

Could you please explain what "automatic database migrations" refers to here?

How do they work? What might we need to do to make them happen?

Is there a documentation page that might provide more context?

[JoviDeCroock]

I haven't read abour this in context of supabase but the idea is setting up a CI step that will update the database schema when we roll out updates to production. This so we don't have the pressure of manually running a script to keep the schema in sync. When I look for supabase migrations I ended up at their cli https://supabase.com/blog/2021/03/31/supabase-cli

[carlomigueldy]

Could you please explain what "automatic database migrations" refers to here? How do they work? What might we need to do to make them happen? Is there a documentation page that might provide more context?

Updated the description for this ticket to address these.

[carlomigueldy]

Just a question since I didn't completely catch it from Discord conversation I was a bit lost with a bunch of options thrown out, why would we defer from using Prisma? @JoviDeCroock

1. What's the concern main concerns of it on using it client side?
2. What's the concern main concerns of it on using it server side? (e.g. Serverless Functions)