Seeking Guidance on Migrating MySQL/MariaDB Data to Supabase

Question

I find myself facing a challenge, and I'm reaching out for some guidance.

My current project involves a rebuild for a client, and we're considering adopting the Supabase stack. However, our existing MySQL database houses a substantial amount of data, and I'm a bit uncertain about the migration process.

I've already exported the data in a .sql file, which includes table creation, relations, and the data itself. While I've come across the Supabase documentation on migrating from MySQL here, I admit there are aspects that I'm struggling to grasp.

Has anyone here successfully migrated from MySQL/MariaDB to Supabase before? I would greatly appreciate any insights, tips, or resources that could help me navigate this process more smoothly. If you've encountered similar challenges or have valuable experience with Supabase migrations, your advice would be gold to me right now.

Thanks a bunch in advance!

Update Nov 14, 2023:

So i used my mac, instead of windows to be able to use the pgloader.

What i did was to install pgloader using brew install pgloader (since it automatically install dependencies).

So i have created a folder and set up a file config.load and use this:

load database
  from mysql://user:@host/source_db
  into postgres://postgres:[email protected]:XXXX/postgres
alter schema 'public' owner to 'postgres';
set wal_buffers = '64MB', max_wal_senders = 0, statement_timeout = 0, work_mem to '2GB';

mysql's password is null since it's from xampp mysql and it is default to null while on postgres i got it from my database settings on supabase. take note that this is online and not local setup supabase.

and i am encountering an error at Line 4

Note: I have moved this post from StackOverflow to here since it's related to a database, but I can't seem to tag Supabase here because I can't add tags due to the 300 reputation requirement. They told me to post it here, so please don't hate me for using the Postgres tag, but Supabase is built on Postgres, and this is the only tag I can use. I've also added the MySQL tag to increase visibility for my post.

Answer 1

to proceed what i did is to keep it simple like this:

    load database
      from mysql://user:pass@host/source_db
      into postgres://postgres:[email protected]:XXXX/postgres;

Notes

• mysql database name should be named as public
• postgres should be granted with permission before doing this, so open your supabase and go to sql query and run: (i got this code from supabase mod at discord named garyaustin)

grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;