node prisma AWS RDS 로컬 DB를 AWS RDS로 전환

node prisma AWS RDS 로컬 DB를 AWS RDS로 전환

영리치 0 97 07.16 14:52
CREATE TABLE `program` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`title` varchar(255) NOT NULL UNIQUE,
`poster_img_url` varchar(2000) NOT NULL,
`title_img_url` varchar(2000) NOT NULL,
`summary` varchar(2000),
`age_range` varchar(200) NULL,
`channel_id` int,
`release_date` DATE,
`count` int,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `channel` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`name` varchar(255) NOT NULL UNIQUE
);

CREATE TABLE `genre` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`genre` varchar(255) NOT NULL UNIQUE
);

CREATE TABLE `participants` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`name` varchar(255) UNIQUE,
`participant_type_id` int NOT NULL
);

CREATE TABLE `participant_type` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`type` varchar(255) NOT NULL UNIQUE
);

CREATE TABLE `genre_program` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`program_id` int NOT NULL,
`genre_id` int NOT NULL
);

CREATE TABLE `participants_program` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`program_id` int NOT NULL,
`participants_id` int NOT NULL
);

CREATE TABLE `user` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`email` varchar(255) UNIQUE NOT NULL,
`nickname` varchar(255),
`password` varchar(1000) NOT NULL,
`membership` boolean,
`social_login_id` int,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `social_login` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`social_media_name` varchar(255) NOT NULL UNIQUE
);

CREATE TABLE `episode` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`program_id` int NOT NULL,
`episode_num` int NOT NULL,
`img_url` varchar(2000) NOT NULL,
`video_url` varchar(2000) NOT NULL,
`summary` varchar(2000),
`release_date` DATE,
`running_time` int,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `interest` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,
`user_id` int NOT NULL ,
`program_id` int NOT NULL ,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `watching_history` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL ,
`user_id` int NOT NULL ,
`episode_id` int NOT NULL ,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE `popular_search_log` (
`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL ,
`program_id` int NOT NULL ,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE `watching_history` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) on DELETE CASCADE ;

ALTER TABLE `like` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) on DELETE CASCADE;

ALTER TABLE `like` ADD FOREIGN KEY (`program_id`) REFERENCES `program` (`id`) on DELETE CASCADE;

ALTER TABLE `episode` ADD FOREIGN KEY (`program_id`) REFERENCES `program` (`id`) on DELETE CASCADE;

ALTER TABLE `genre_program` ADD FOREIGN KEY (`program_id`) REFERENCES `program` (`id`) on DELETE CASCADE;

ALTER TABLE `participants_program` ADD FOREIGN KEY (`program_id`) REFERENCES `program` (`id`) on DELETE CASCADE;

ALTER TABLE `genre_program` ADD FOREIGN KEY (`genre_id`) REFERENCES `genre` (`id`) on DELETE CASCADE;

ALTER TABLE `watching_history` ADD FOREIGN KEY (`episode_id`) REFERENCES `episode` (`id`) on DELETE CASCADE;

ALTER TABLE `popular_search_log` ADD FOREIGN KEY (`program_id`) REFERENCES `program` (`id`) on DELETE CASCADE;

ALTER TABLE `user` ADD FOREIGN KEY (`social_login_id`) REFERENCES `social_login` (`id`) on DELETE CASCADE;

ALTER TABLE `participants` ADD FOREIGN KEY (`participant_type_id`) REFERENCES `participant_type` (`id`) on DELETE CASCADE;

ALTER TABLE `participants_program` ADD FOREIGN KEY (`participants_id`) REFERENCES `participants` (`id`) on DELETE CASCADE;

ALTER TABLE `program` ADD FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`) on DELETE CASCADE;





// .env

DATABASE_URL="mysql://admin:<RDS_비밀번호>@<DB_ENDPOINT>:3306/<database명>" 


// shell

npx prisma db push


npx prisma generate





DB가 alter 등을 통해 수정되고 그 다음 prisma가 수정되어야 할 경우는 아래 명령어 쓰면 됨


npx prisma db pull // MYSQL에 등록된 스키마를 자동으로 프리즈마 스키마에 등록하여 줌
npx prisma generate // DB Pull 후 유도 동작 (프리즈마에서 필요한 듯?) // DB pull 안하는 경우, 모델링 해주고 해줌. 


alter table watching_history add updated_at timestamp default current_timestamp on update current_timestamp;


예를 들어서 위의 구문을 table plus 등으로 DB 내에서 먼저 실행하고 그것을 프로젝트의 prisma에 반영하기 위해서라면 위의 pull을 해야함

Comments

Category
Magazine
훈남/훈녀
 
 
 
Facebook Twitter GooglePlus KakaoStory NaverBand