Look before you leap.
-- Aesop's fable
1. Design DB relations on dbdiagram.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 to supabase > 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 contain CREATE 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 as Snaplet Seed
I'd like to share my seed.config.ts and seed.ts file for your reference.
⬇️seed.config.ts
// seed.config.ts
import { SeedPostgres } from "@snaplet/seed/adapter-postgres";
import { defineConfig } from "@snaplet/seed/config";
import postgres from "postgres";
export default defineConfig({
adapter: () => {
const client = postgres(
//// Local dev
"postgresql://postgres:postgres@127.0.0.1:54322/postgres",
//// Prod
// "postgresql://YOUR_PROD_DATABASE_URL/postgres",
);
return new SeedPostgres(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.ts
import { createSeedClient } from "@snaplet/seed";
import { v4 as uuidv4 } from "uuid";
import bcrypt from "bcrypt";
async function hashPassword(password: string): Promise<string> {
const saltRounds = 10; // You can adjust the salt rounds as needed
const salt = await bcrypt.genSalt(saltRounds);
const hashedPassword = await bcrypt.hash(password, salt);
return hashedPassword;
}
const DRY_RUN = false;
const USER_EMAIL = "my@user.com";
const USER_PASSWORD = "userpass";
const main = async () => {
const USER_ENCRYPTED_PASSWORD = await hashPassword(USER_PASSWORD);
const USER_ID = uuidv4();
const seed = await createSeedClient({ dryRun: DRY_RUN });
// Truncate all tables in the database
await seed.$resetDatabase();
// Create the user and its related data
await seed.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 you
invited_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 you
raw_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 you
phone: 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 you
identity_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 sure seed.config.ts contains your local DB link:
// seed.config.ts
...
adapter: () => {
const client = 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 to seed.sql
In my practical learning, dry-run to generate and pour SQL commands into seed.sql is not convenient due to this issue I mentioned before. So I let Snaplet Seed seed data directly into local DB. (I use dry-run only for debugging purpose sometime.)
Make sure seed.ts sets DRY_RUN to false:
// seed.ts
...
const DRY_RUN = false;
...
Run npx 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.
Run supabase db reset --linked --no-seed
6. (Optional) Seed data to remote DB
Prerequisites
1. Use prod DB link in seed.config.ts
// seed.config.ts
...
adapter: () => {
const client = postgres(
//// Local dev
// "postgresql://postgres:postgres@127.0.0.1:54322/postgres",
//// Prod
"postgresql://YOUR_PROD_DATABASE_URL/postgres",
...
2. Make sure seed.ts sets DRY_RUN to false
// seed.ts
...
const DRY_RUN = false;
...
Run npx tsx seed.ts to seed data into remote DB.
Top comments (0)