Look before you leap.
-- Aesop's fable
1. Design DB relations ondbdiagram.io
Tinkering SQL code is hard, a website like dbdiagram.io makes my life easier.
⬆️ Export to Postgres after designing.
2. Move dbdiagram.io exported.sql file tosupabase > migrations folder
Rename it if you like.
Note: If your DB topology designed in dbdiagram.io involved tables that do not belong to 'public' schema, check the code in.sql file before using. Let's say it involved "users" in "auth" schema, the code would containCREATE TABLE "users" which is unnecessary.
3. Let local Supabase DB apply the migration.
supabase db reset --no-seed
4. Leverage the power of seeding tools such asSnaplet Seed
I'd like to share myseed.config.ts andseed.ts file for your reference.
⬇️seed.config.ts
// seed.config.tsimport{SeedPostgres}from"@snaplet/seed/adapter-postgres";import{defineConfig}from"@snaplet/seed/config";importpostgresfrom"postgres";exportdefaultdefineConfig({adapter:()=>{constclient=postgres(//// Local dev"postgresql://postgres:postgres@127.0.0.1:54322/postgres",//// Prod// "postgresql://YOUR_PROD_DATABASE_URL/postgres",);returnnewSeedPostgres(client);},select:[// We don't alter any extensions tables that might be owned by extensions"!*",// We want to alter all the tables under public schema"public*",// We also want to alter some of the tables under the auth schema"auth.users","auth.identities","auth.sessions",],});⬇️seed.ts (the code will create a user for testing, refactor it to suit your need.)
// seed.tsimport{createSeedClient}from"@snaplet/seed";import{v4asuuidv4}from"uuid";importbcryptfrom"bcrypt";asyncfunctionhashPassword(password:string):Promise<string>{constsaltRounds=10;// You can adjust the salt rounds as neededconstsalt=awaitbcrypt.genSalt(saltRounds);consthashedPassword=awaitbcrypt.hash(password,salt);returnhashedPassword;}constDRY_RUN=false;constUSER_EMAIL="my@user.com";constUSER_PASSWORD="userpass";constmain=async()=>{constUSER_ENCRYPTED_PASSWORD=awaithashPassword(USER_PASSWORD);constUSER_ID=uuidv4();constseed=awaitcreateSeedClient({dryRun:DRY_RUN});// Truncate all tables in the databaseawaitseed.$resetDatabase();// Create the user and its related dataawaitseed.auth_users([{id:USER_ID,instance_id:"00000000-0000-0000-0000-000000000000",aud:"authenticated",role:"authenticated",email:USER_EMAIL,encrypted_password:USER_ENCRYPTED_PASSWORD,// email_confirmed_at: "", // Snaplet will generate this for youinvited_at:null,confirmation_token:"",confirmation_sent_at:null,recovery_token:"",recovery_sent_at:null,email_change_token_new:"",email_change:"",email_change_sent_at:null,// last_sign_in_at: "", // Snaplet will generate this for youraw_app_meta_data:{"provider":"email","providers":["email"]},raw_user_meta_data:{"sub":USER_ID,"email":USER_EMAIL,"email_verified":false,"phone_verified":false,},is_super_admin:null,// created_at: "", // Snaplet will generate this for you// updated_at: "", // Snaplet will generate this for youphone:null,phone_confirmed_at:null,phone_change:"",phone_change_token:"",phone_change_sent_at:null,email_change_token_current:"",email_change_confirm_status:0,banned_until:null,reauthentication_token:"",reauthentication_sent_at:null,is_sso_user:false,deleted_at:null,is_anonymous:false,identities:[{// id: "", // Snaplet will generate this for youidentity_data:{"sub":USER_ID,"email":USER_EMAIL,"email_verified":false,"phone_verified":false,},provider:"email",provider_id:USER_ID,// If the provider is email or phone, the id is the user's id from the auth.users table. https://supabase.com/docs/guides/auth/identities#the-user-identity-object// last_sign_in_at: "", // Snaplet will generate this for you// created_at: "", // Snaplet will generate this for you// updated_at: "", // Snaplet will generate this for you}],// data related to user// ...},]);if(!DRY_RUN){console.log("Database seeded successfully!");}process.exit();};main();- 4.1 Let Snaplet Seed get to know the current status of your local DB
npx @snaplet/seed sync
Note: Make sureseed.config.ts contains your local DB link:
// seed.config.ts...adapter:()=>{constclient=postgres(//// Local dev"postgresql://postgres:postgres@127.0.0.1:54322/postgres",//// Prod// "postgresql://YOUR_PROD_DATABASE_URL/postgres",... - 4.2 Let Snaplet Seed seed the data into local DB, not dry-run toseed.sql
In my practical learning, dry-run to generate and pour SQL commands intoseed.sql is not convenient due tothis issue I mentioned before. So I let Snaplet Seed seed data directly into local DB. (I usedry-run only for debugging purpose sometime.)
Make sure seed.ts setsDRY_RUN tofalse:
// seed.ts...constDRY_RUN=false;...Runnpx tsx seed.ts to seed data into local DB.
5. Reset remote DB
Let's make it clear:
The topology designed on dbdiagram.io is the source of truth for local DB's structure.
The local DB's structure is the source of truth for remote db.
After local DB's structure is stable, it's time to sync the initial status of remote DB with local.
Runsupabase db reset --linked --no-seed
6. (Optional) Seed data to remote DB
Prerequisites
1. Use prod DB link inseed.config.ts
// seed.config.ts...adapter:()=>{constclient=postgres(//// Local dev// "postgresql://postgres:postgres@127.0.0.1:54322/postgres",//// Prod"postgresql://YOUR_PROD_DATABASE_URL/postgres",...2. Make sure seed.ts setsDRY_RUN tofalse
// seed.ts...constDRY_RUN=false;...Runnpx tsx seed.ts to seed data into remote DB.
Top comments(0)
For further actions, you may consider blocking this person and/orreporting abuse
