Repository navigation
PostgreSQL Prisma
| PostgreSQL | MySQL | |
|---|---|---|
| DB | 객체 관계형 데이터베이스 | 관계형 데이터베이스 |
| Data Types | 모든 MySQL 타입 + geometric, network address, XML, arrays.. (비정형 데이터까지) | numeric, character, date, time.. |
| ACID | 항상 ACID 규격 준수 | Inno DB, NDB 클러스터 환경에서만 준수 |
| Indexes | 트리 인덱스 외에도 expression index, partial index, hash index 등 지원 | B-tree나 R-tree 인덱스 지원 |
| Performance | 빈번한 쓰기 연산에 대한 성능이 좋음 | 빈번한 조회 연산에 대해 성능이 좋음 |
| Concurrency Control(동시성 제어) | MVCC(Multiversion Concurrency Control)을 항상 지원한다. | MySQL은 락 기반 동시성 제어이며 InnoDB에서는 MVCC를 지원한다. |
객체 관계형 데이터베이스
PostgreSQL은 객체관계형(ORDBMS)로 객체지향 프로그래밍의 개념을 통합하여 설계가 가능하다.
| 특징 | RDBMS (MySQL) | ORDBMS (PostgreSQL) |
|---|---|---|
| 사용자 정의 타입 | ❌ 불가능 | ✅ CREATE TYPE 지원 |
| 복합 타입 | ❌ 원자값만 허용 | ✅ 중첩된 구조 가능 |
| 배열 타입 | ❌ 없음 | ✅ 네이티브 배열 |
| 테이블 상속 | ❌ 없음 | ✅ INHERITS 절 |
| 메서드/함수 | ❌ 제약 조건만 | ✅ 타입에 메서드 바인딩 |
-
1️⃣ 사용자 정의 타입 (User-Defined Types, UDT) 만약 관계형 데이터베이스(RDBMS)로
users와addresses를 설계한다면 다음과 같이 단일 값으로 테이블을 생성할 수 있다.CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE addresses ( address_id INT PRIMARY KEY, user_id INT FOREIGN KEY, street VARCHAR(100), city VARCHAR(50), zip_code VARCHAR(10) );
하지만 ORDBMS에서는
CREATE TYPE을 지원하며address_type이라는 타입을 생성해낼 수 있다.CREATE TYPE address_type AS ( street TEXT, city TEXT, zip_code VARCHAR(10) );
-
2️⃣ 중첩된 복합 타입 (Nested Composite Types) 생성된 사용자 정의 타입은 또 다른 타입 내의 필드로 중첩하거나 생성할 테이블의 필드로 활용이 가능하다.
CREATE TYPE person AS ( first_name TEXT, last_name TEXT, home_address address_type ); CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), home_address address_type, work_address address_type );
-
3️⃣ 배열 타입 (Native Arrays)
MySQL에서는 배열을 저장하기 위해 별도의 테이블을 생성해야 하지만,PostgreSQL에서는 배열을 직접 하나의 열에 저장이 가능하다.CREATE TABLE user_tags ( id INT PRIMARY KEY, skills TEXT[], -- 1차원 배열 scores NUMERIC[][] -- 2차원 배열 ); INSERT INTO user_tags VALUES (1, ARRAY['TypeScript', 'PostgreSQL', 'Redis'], ARRAY[[95, 88], [92, 87]]);
hstore이라는 타입을 통해key-value쌍으로도 저장이 가능하다고.. ㄷㄷ;CREATE EXTENSION IF NOT EXISTS hstore; ALTER TABLE Books ADD COLUMN Attributes hstore; UPDATE Books SET Attributes = 'edition => "1st", language => "English"'::hstore;
JSONJSONB타입을 지원하여 JSON 데이터 형식의 데이터도 저장이 가능하다.MySQL에서도 5.7.8 버전부터JSON타입이 추가되어 저장이 가능하며 이에 대한 성능 비교는 **MySQL Json 데이터 타입의 저장 구조와 성능 비교** 아티클에 잘 작성되어 있다.JSON: 텍스트 기반 저장JSONB: 이진 분해 저장- JSON이 파싱되면서 트리 구조로 분해되며 각 요소를 이진으로 인코딩하여 저장된다.
- 메모리를 통해 이진 데이터에 직접 접근하기 때문에 속도가 빠르다.
-
JSON보다 삽입은 느리지만 인덱싱과 읽기 성능이 빠르다.MySQL도JSON타입은 문자열이 아닌 바이너리로 저장되지만 인덱싱이 불가하다.
-
4️⃣ 테이블 상속 (Table Inheritance) 객체지향답게 테이블 상속 또한 가능하다.
-- 기본 직원 테이블 CREATE TABLE employee ( id INT PRIMARY KEY, name VARCHAR(100), salary NUMERIC ); -- 관리자는 직원의 모든 속성을 상속 CREATE TABLE manager INHERITS (employee) ( team_size INT, department VARCHAR(50) ); -- 인턴도 직원을 상속 CREATE TABLE intern INHERITS (employee) ( university VARCHAR(100), graduation_date DATE ); -- 모든 직원 조회 (manager와 intern 포함) SELECT * FROM ONLY employee; -- 기본 employee만 SELECT * FROM employee; -- 상속된 모든 서브타입 포함 -- 결과: employee, manager, intern의 모든 데이터가 통합 반환 -- 특정 타입만 조회 SELECT * FROM manager WHERE team_size > 5;
-
5️⃣ 메서드/함수 바인딩 사용자 저의 타입에 함수를 선언하여 연결이 가능하다.
CREATE TYPE complex_number AS ( real_part FLOAT, imaginary_part FLOAT ); -- 이 타입을 위한 함수 정의 CREATE FUNCTION magnitude(c complex_number) RETURNS FLOAT AS $$ SELECT sqrt(c.real_part ^ 2 + c.imaginary_part ^ 2); $$ LANGUAGE SQL; -- 타입처럼 메서드 호출 SELECT magnitude(complex_num) FROM complex_numbers;
❓
MySQLSpatial Function이랑 비슷한 역할을 하는 것 같은데, 그러면 MySQL에서도 메서드 지원하는 거 아닌가?
결론: 이러한 PostgreSQL의 특성들로 인해, 규모가 커질수록 더 안정적이고 최적화된 구조로 인해 장점을 가지며 만약 작은 프로젝트 혹은 쓰기 연산보다 빈번하게 조회 연산이 많다면 MySQL을 사용하는 것이 좋다.
하지만 그럼에도 PostgreSQL을 사용한 이유.
- Prisma PostgreSQL 서비스를 제공할 정도로 primsa ORM과 PostgreSQL의 호환성이 좋다고 생각
- 배열이나 객체를 그대로 저장해볼 수 있기에, 한 번 경험삼아 사용해보기 위해
Node.js 와 Typescript 환경에서 사용하는 DB Tool 들은 Productivity와 Control 면에서 트레이드 오프가 존재한다.
-
Raw SQL: Full Control, low Productivity- DB 연산에 대해 모든 Control이 가능하지만 그만큼 관리하기가 어렵다.
- 직접 작성하는 쿼리문에 대한 오버헤드가 발생할 가능성이 존재한다.
- 쿼리 결과에 대해 타입 안정성이 보장되지 않아, 잠재적인 타입 에러 가능성이 존재한다.
-
SQL query builders: High Control, medium productivity- SQL 쿼리를 추상화하는 도구이지만 여전히 SQL 문법에 대해 개발자가 인지하고 있어야 한다.
-
Traditional ORMs: Less Control, better productivity- 하나의 모델을 클래스로 추상화하여 DB에 매핑한다.
- 해당 클래스의 인스턴스 메서드를 통해 데이터를 읽고 쓸 수 있다.
- 데이터를 객체처럼 다루는 것 같지만 실제 내부 쿼리 연산에서 비효율성이 존재한다.(N+1 같은 문제)
이러한 점에 대해 Prisma는 Productivity와 Control의 균형을 잡는 것을 목표로 한다.
-
SQL 쿼리 작성 < 직관적인 API 데이터 요청
-
객체 기반의 사고 ⇒ 클래스 대신 쿼리를 사용하여 객체보다 쿼리 연산의 사고가 가능하게끔
-
타입 안정성 보장
-
개인적으로 Prisma의 타입 안정성이 가장 큰 강점이라고 생각함 (Type-Safety)
// typeORM const posts = await postRepository.find({ where: { published: true }, select: ["id", "title"], }); if (posts[0].content.length > 0) { // ❌ 런타임 에러! console.log("내용 있음"); } //Prisma const posts = await prisma.post.findMany({ where: { published: true }, select: { id: true, title: true, }, }); if (posts[0].content.length > 0) { // ✅ 컴파일 에러! console.log("내용 있음"); }
-
1️⃣ 일단 local에서 접속하기
-
만약 postgreSQL이 설치가 안되어있다면 설치해야한다.
-
설치가 되었다면 DB 서버 환경과 동일하게 설정하기 위해 세팅을 해준다(만약 로컬 환경을 자유롭게 하고 싶다면 패스)
CREATE USER web02_users WITH PASSWORD '****'; CREATE DATABASE web02 OWNER web02_users; ALTER USER web02_users WITH SUPERUSER; \q
-
그리고 해당 계정으로 접속이 가능한 지 확인해본다.
psql -U web02_users -d web02 -h localhost -W password? ****
-
.env에 DATABAE_URL이 잘 추가되었는 지 확인한다.DATABASE_URL = "postgresql://web02_users:****@localhost:5432/web02?schema=public";
-
npx prisma migrate deploy -
npx prisma generate -
그러면 아마 prisma를 통해 DB 연결될 듯?
-
-
2️⃣ 로컬에서 DB 서버에 접근하기 Private Subnet에 존재하는 DB 서버를 로컬에서 접근하려면 SSH Tunneling을 이용해야 한다.
SSH Tunneling: SSH 서버로 연결하여 터널을 생성하고 이를 통해 다른 서비스로 접근하는 방법
-
🌊 기본 Flow
- DB 서버는 Private이기 때문에 같은 VPC 내의 서버를 제외하고는 접근이 불가하다. 로컬 또한 당연히 접근이 불가하다.
- 하지만 로컬에서 Public Subnet으로는 접근이 가능하다.
- 그렇다면 Public Subnet이 터널 역할을 해준다면, 로컬에서 public server를 거쳐 DB 서버로 접근이 가능하다
- Local → Public Server → Private Server
-
⚙️ 기본 세팅
-
sudo vim /etc/postgresql/18/main/postgresql.conf- 접근을 허용할 주소를 기존
localhost→ ‘*’ 로 변경
listen_addresses = '*' # what IP address(es) to listen on;
- 접근을 허용할 주소를 기존
-
sudo vim /etc/postgresql/18/main/pg_hba.conf- 실제 접근할 IP는 여기서 관리해주며 bastion host의 IP만 접근이 가능하도록 설정
# IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 host all all 192.168.0.6/32 scram-sha-256
- Private Subnet의 Inbound 열어준다.
- 현재는 bastion host의 22번 포트만 열려있지만, 우리는 bastion host를 통해 DB도 접근할 것이니 5432번 포트도 추가해준다.
TCP 192.168.0.6/32 5432 TCP 192.168.0.6/32 22
-
-
💻 CLI를 통해 접속 (고급 기술)
-
SSH Tunneling 명령어는 다음과 같다.
ssh -i ./web02.pem -L 5433:192.168.8.6:5432 root@<public_ip> -N
-
-i: 개인 키 파일을 통해 접속을 시도한다 -
-L:-L [로컬포트]:[원격에서 접근할 대상 IP]:[대상 포트]- ⇒ 로컬 5433 포트를 통해 private server의 5432 포트에 접근할 것입니다.
-
-N: ssh를 통해 접속하여도 터널링만 유지한다. (쉘 제공 x)
-
-
여기서 성공적으로 연결이 된다면 해당 터미널은 응답없이 멈추게 된다. (
-N설정으로 터널링만 유지하기에) -
그렇다면 터미널을 하나 켜서 로컬에서 접속을 시도하면된다.
psql -h 127.0.0.1 -p 5433 -U web02_users -d web02
-
-h: 호스트 -
-p: 포츠 -
-U: 유저 -
-d: DB 명 - ⇒ 내 로컬에서 5433번 포트로 web02_users 계정으로 web02 DB에 접근할 거에요
-
-
성공한다면 패스워드 입력칸이 나오고 패스워드를 통해 접속하면
psql쉘이 열린다. -
로컬에서 생성한 테이블과 추가한 데이터가 DB 서버에서도 조회가 가능하다.
-
-
🧑💻 Prisma를 통해 접속 CLI를 통해 접속을 성공하였다면 Prisma를 통해 세팅하는 것은 식은 죽 먹기다.
- 기본적으로 SSH Tunneling이 유지되어 있어야 한다.
-
.env파일에 있는 DATABASE_URL의 port 번호를 5433으로만 변경해주면 된다. -
만약 변경된 스키마를 로컬에서 바로 DB 서버로 적용시키려면
npx prisma migrate deploy
-
-
3️⃣ Public Server에서 DB 서버 접근하기
postgresql://web02_users:****@192.168.8.6:5432/web02?schema=publicDB Server- Public Server의 private IP와 5432 포트를 Inbound 설정에서 허용
-
postgreSQL설정 파일에서 IPv4로 public server의 private IP를 허용Public Server - DB Server의 private IP + 5432 포트로 연결가능
schema.prisma 에서 정의한 각 model 들을 prisma 명령어를 통해 DB에 스키마를 적용함과 동시에 prisma client를 생성하여 작업 환경에서도 사용 가능하게 해준다.
-
npx prisma migrate dev --name init- 작성한 스키마를 바탕으로 연결된 DB에 테이블을 생성하고 migration 파일을 생성한다.
- /prisma/migrations에는 timestamp로 생성한 name의 sql 파일이 생성하며 이를 통해 스키마 변경을 추적할 수 있다.
-
npx prisma generate-
Prisma Client를 생성하는 명령어다.
-
Prisma Client를 생성하면 작업 환경에서 해당 스키마를 바탕으로 자동 완성, 타입 검사를 진행해준다.
-
-
만약에 스키마에 변경이 생긴다면 migration에 기록해야 한다.
-
npx prisma migrate dev --name 변경이름을 통해 스키마 변경을 migration에 저장해야 한다. - 그리고 해당 내용은 배포될 때 CD에서
npx prisma migrate deploy를 통해 DB 서버에 적용될 것이다.
-
- ❓ 왜 코문철인가요?
- 🧠 이 게임은 어떻게 태어났나요?
- 🛠️ 어떤 서비스가 제공되나요?
- ⚙️ 코문철을 지탱하는 기술
- 🏗️ 코문철의 든든한 설계