[240721 TIL] 수파베이스 SQL로 테이블 생성하기

SQL로 테이블 만들기

최종프로젝트는 여지껏 진행해본 프로젝트에 비해 규모가 큽니다..
당연히 테이블 수도 많고 서로 연결된 테이블도 많습니다.

 

우리는 4일동안 기획회의를 진행하면서 DB 테이블 구조에 대한 고민을 정말 많이 했습니다.

백엔드 전문에게는 이정도 규모는 정말 작은 규모에 속하겠지만,

프론트엔드인 우리에게는 새로운 도전에 가까웠습니다.

 

그리하여, 노션에 표로 정리함과 동시에 drawsql 이라는
SQL schema 작성을 도와주는 사이트를 같이 이용했습니다.

 

이 사이트의 장점은, 시각적으로 보면서 작성한 SQL 을 SQL파일로 export 해준다는 것입니다.

 

 

대략 이런 식의 구조를 만들고 SQL 로 export 한 뒤 좀 더 다듬었습니다.
(두개의 테이블이 더 있지만, 해당 테이블은 팀원분께서 직접 만드신다고 하여 제외)

테이블 생성 SQL

create table
    "tripbookmarks" (
        "bookmark_id" uuid not null unique,
        "bookmark_created_at" timestamp with time zone not null,
        "bookmark_trip_id" uuid not null,
        "bookmark_buddy_id" uuid not null
    );

alter table "tripbookmarks"
add primary key ("bookmark_id");

create table
    "payments" (
        "payment_id" uuid not null unique,
        "payment_buddy_id" uuid not null,
        "payment_totalAmount" numeric not null,
        "payment_currency" text not null,
        "payment_status" text not null,
        "payment_approved_at" timestamp with time zone not null
    );

alter table "payments"
add primary key ("payment_id");

create table
    "storylikes" (
        "storylikes_id" uuid not null unique,
        "storylikes_created_at" timestamp with time zone not null,
        "storylikes_story_id" uuid not null,
        "storylikes_buddy_id" uuid not null
    );

alter table "storylikes"
add primary key ("storylikes_id");

create table
    "contract" (
        "contract_id" uuid not null unique,
        "contract_created_at" timestamp with time zone not null,
        "contract_trip_id" uuid not null,
        "contract_buddy_id" uuid not null,
        "contract_start_date" timestamp with time zone not null,
        "contract_end_date" timestamp with time zone not null,
        "contract_isPending" boolean not null,
        "contract_isLeader" boolean not null,
        "contract_isValidate" boolean not null,
        "contract_validate_date" timestamp with time zone not null
    );

alter table "contract"
add primary key ("contract_id");

create table
    "buddies" (
        "buddy_id" uuid not null unique,
        "buddy_created_at" timestamp with time zone not null,
        "buddy_nickname" text not null unique,
        "buddy_email" text not null unique,
        "buddy_temperature" float (53) not null,
        "buddy_isPro" boolean not null,
        "buddy_isOnBoarding" boolean not null,
        "buddy_following_counts" integer not null,
        "buddy_follower_counts" integer not null,
        "buddy_sex" text,
        "buddy_birth" timestamp with time zone,
        "buddy_preferred_theme1" text,
        "buddy_preferred_theme2" text,
        "buddy_preferred_theme3" text,
        "buddy_preferred_buddy1" text,
        "buddy_preferred_buddy2" text,
        "buddy_preferred_buddy3" text,
        "buddy_mbti" text,
        "buddy_region" text,
        "buddy_introduction" text,
        "buddy_login_id" text unique,
        "buddy_profile_pic" text
    );

alter table "buddies"
add primary key ("buddy_id");

create table
    "stories" (
        "story_id" uuid not null unique,
        "story_created_at" timestamp with time zone not null,
        "story_created_by" uuid not null,
        "story_media" text not null,
        "story_overlay" jsonb not null,
        "story_likes_counts" integer not null
    );

alter table "stories"
add primary key ("story_id");

create table
    "follow" (
        "follow_id" uuid not null unique,
        "follow_created_at" timestamp with time zone not null,
        "follow_following_id" uuid not null,
        "follow_follower_id" uuid not null
    );

alter table "follow"
add primary key ("follow_id");

create table
    "trips" (
        "trip_id" uuid not null unique,
        "trip_created_at" timestamp with time zone not null,
        "trip_title" text not null,
        "trip_content" text not null,
        "trip_master_id" uuid not null,
        "trip_max_buddies_counts" integer not null,
        "trip_start_date" timestamp with time zone not null,
        "trip_end_date" timestamp with time zone not null,
        "trip_final_destination" text not null,
        "trip_meet_location" text not null,
        "trip_theme1" text not null,
        "trip_theme2" text not null,
        "trip_theme3" text not null,
        "trip_wanted_buddies1" text not null,
        "trip_wanted_buddies2" text not null,
        "trip_wanted_buddies3" text not null,
        "trip_wanted_sex" text not null,
        "trip_start_age" integer not null,
        "trip_end_age" integer not null,
        "trip_thumbnail" text not null,
        "trip_isValidate" boolean not null
    );

alter table "trips"
add primary key ("trip_id");

alter table "follow"
add constraint "follow_follow_follower_id_foreign" foreign key ("follow_follower_id") references "buddies" ("buddy_id");

alter table "follow"
add constraint "follow_follow_following_id_foreign" foreign key ("follow_following_id") references "buddies" ("buddy_id");

alter table "payments"
add constraint "payments_payment_buddy_id_foreign" foreign key ("payment_buddy_id") references "buddies" ("buddy_id");

alter table "trips"
add constraint "trips_trip_id_foreign" foreign key ("trip_id") references "contract" ("contract_id");

alter table "storylikes"
add constraint "storylikes_storylikes_story_id_foreign" foreign key ("storylikes_story_id") references "stories" ("story_id");

alter table "storylikes"
add constraint "storylikes_storylikes_buddy_id_foreign" foreign key ("storylikes_buddy_id") references "buddies" ("buddy_id");

alter table "tripbookmarks"
add constraint "tripbookmarks_bookmark_buddy_id_foreign" foreign key ("bookmark_buddy_id") references "buddies" ("buddy_id");

alter table "tripbookmarks"
add constraint "tripbookmarks_bookmark_trip_id_foreign" foreign key ("bookmark_trip_id") references "trips" ("trip_id");

alter table "stories"
add constraint "stories_story_created_by_foreign" foreign key ("story_created_by") references "buddies" ("buddy_id");

alter table "contract"
add constraint "contract_contract_trip_id_foreign" foreign key ("contract_trip_id") references "trips" ("trip_id");

alter table "contract"
add constraint "contract_contract_buddy_id_foreign" foreign key ("contract_buddy_id") references "buddies" ("buddy_id");

 

SQL 의 생김새는 대략 위와 같습니다. 외래키 연결도 해줍니다.

 

supabase 에서 사용시 주의점은, timestamptz 는 꼭
timestamp with time zone not null, 이런식으로 써야 한다는 점입니다.

 

또한 SQL에 익숙치 않아서 작성하여 사용하지는 못했지만, CASCADE 설정도 가능합니다.