express를 공부하면서 본격적으로 데이터베이스의 데이터를 접하는 횟수가 많아지고 있다.
현재 mySQL과 같은 RDBMS의 테이블 스키마와 sql 파일을 vscode 상에서 다루도록 도와주는 도구인 dbmate로 데이터 테이블을 생성하고 데이터를 관리하는 방법을 배우고 있다.
그중에서 dbmate를 사용하며 어떤 문제에 직면했고, 스스로 해결 방식에 대해 소개하고자 한다.
개요
우선, dbmate로 migration 파일 안에 테이블 sql을 생성해 데이터를 넣는 작업을 진행했다.
dbmate new [table_name]을 통해 테이블을 생성했고, express 모듈을 담은 app 변수에 http 메소드를 활용해 데이터를 집어넣었다.
하지만, 생성한 3개의 테이블 모두 테이블 사이를 연결하는 foreign key 를 만들어야 했지먄 그러지 않은 채 js파일만 채웠고, DQL (Data Query Language)을 사용해 데이터를 검색하는 과정에서 문제에 직면하고 말았다.
문제 1 - dbmate up 시점
첫 번째로 직면한 문제는 dbmate new 문법으로 만든 테이블 column 수정이었다.
이미 dbmate up을 통해 스키마가 생성되면, 해당 파일에 직접 접근해서 새로운 colunm을 입력하는 방식으로는 테이블을 수정할 수 없었다.
여기서 나는 두 가지 해결 방안을 생각했는데, db/migration 내 테이블을 모두 삭제하고 다시 만들거나, mysql 문법을 활용해 컬럼을 추가하는 방식이었고, 후자를 택했다.
왜냐하면, 비록 지금은 3개의 테이블 뿐이지만, 이러한 문제는 현직에서도 충분히 직면할 수 있을 것이라고 생각했기 때문에 db의 손상을 최소화 할 수 있는 방식을 택하고 싶었기 때문이다.
그래서 해당 테이블에 새로운 column을 부여하기 위해 mysql 문법인 ALTER를 활용해 dbmate new로 새로운 sql 파일을 만들었다.
하지만, sql이나 http 메소드를 활용한 터미널에서 내가 원하는 결과가 나오지 않으며 이 방법 역시 적용되지 않았다. 다만, db/schema.sql 파일 하단에 schema_migration이라는 테이블의 version이라는 column에 새로운 데이터가 추가된 것을 확인했다.
즉, dbmate up을 통해 새로운 테이블 데이터가 schema의 기록을 관리하는 schema_migration 테이블에 추가됐지만, 어떤 이유로 mysql 테이블에 영향을 주지 못한 것이다.
해결 & 복기
version이 제시한 힌트를 통해, 네 번째 테이블을 dbmate up 한 뒤에 create 문법을 사용해 column 생성을 진행했기 때문에 version에 시간 값만 입력되고 테이블을 수정하진 못했음을 알게 됐다.
그래서 mysql에서 version의 네번째 데이터를 삭제했고, users 테이블에 column을 성공적으로 입력할 수 있었다.
이를 통해 dbmate up을 통해 스키마를 생성 / 변경할 수 있지만, schema_migration 테이블 내 version column에는 중복된 데이터를 입력받을 수 없기 때문에 생성에 필요한 모든 입력을 완료한 후 dbmate up을 하면 된다는 것을 느꼇다....
그리고 중복된 데이터를 받지 못한다는 점에서 또 다른 방법을 알게 됐는데, 바로 새로 만든 sql 파일의 시간값만 변경해도 수정이 가능하다는 점이다. 하지만, 이 방법은 version의 데이터를 쓸데없이 늘리는 것 같아 스스로에게 권장할 필요는 없다고 느꼈다.
문제 2 - foreign key checks
두 번째 문제는 그렇게 추가한 column에 foreign key를 부여하기 위해 또 다시 dbmate up으로 새로운 파일을 만들었지만, 부모 테이블을 참조할 수 없었다.
ERROR 1826 (1452): Cannot add or update a child row: a foreign key constraint fails
새로 column이 추가된 상황에서 기존 데이터들을 사용하기 어렵다고 판단했고, 해당 테이블의 모든 데이터를 삭제하는 과정에서 TRUNCATE 명령어를 사용했지만,
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint...
라는 또 다른 에러에 직면했다.....
이러한 에러 상황에 놓인 건 세 번째 테이블이 두 테이블을 참조하고 있어 참조된 row를 삭제하기 위해선 세 번째 테이블의 데이터를 모두 삭제해야 했다.
해결 & 복기
하지만, 외래키가 존재하는 테이블의 데이터를 삭제할 때 위와 같은 에러가 발생하는 것이었다. 결국,
set FOREIGN_KEY_CHECKS = 0;를 통해 foreign key를 비활성화 한 뒤 세 번째 테이블을 truncate 했고,
set FOREIGN_KEY_CHECKS = 1;로 다시 활성화시켜 제대로 동작하게끔 했다.
하지만, 이러한 방법을 사용하면 데이터베이스에서 아주 중요한 기능을 잠시 끄는 것이기 때문에, 항시 활성화돼야 하는 db 시스템에 상당한 위험 부담을 줄 수 있어 현직에서는 자제하는 방식이라고 한다. 필시, 더 안전한 방법을 찾아봐야 할 것!!
참조
