~/blog/Storing_User_Posts_as_JSON_Array_in_PostgreSQL_with_Prisma_-_Claude-2024-10-31
Published on

Storing User Posts as JSON Array in PostgreSQL with Prisma

764 words4 min read
Authors
  • avatar
    Name
    Shortie
    Twitter

데이터베이스 설계에서 유연성과 성능 사이의 균형을 잡는 것은 항상 도전적인 과제입니다. 특히 사용자 게시글과 같이 구조가 동적으로 변할 수 있는 데이터를 다룰 때는 더욱 그렇죠. 오늘은 PostgreSQL과 Prisma를 사용하여 사용자의 게시글 정보를 JSON 배열 형태로 저장하고 효과적으로 조회하는 방법에 대해 알아보겠습니다. 이 방법은 데이터 모델의 유연성을 극대화하면서도 강력한 쿼리 기능을 제공합니다.

먼저, Prisma 스키마 설정부터 시작해봅시다. 사용자 정보와 함께 게시글을 JSON 배열로 저장하기 위한 스키마는 다음과 같습니다:

model User {
  id        Int      @id @default(autoincrement())
  username  String   @unique
  posts     Json[]   // JSON 배열 형태로 게시글 정보 저장
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

이 스키마에서 주목할 점은 posts 필드의 타입이 Json[]로 지정되어 있다는 것입니다. 이는 PostgreSQL에서 JSONB 타입의 배열로 저장되며, 각 사용자의 게시글 정보를 JSON 객체의 배열로 저장할 수 있게 해줍니다. 예를 들어, 다음과 같은 형태로 데이터를 저장할 수 있습니다:

[
  {
    "id": 1,
    "title": "첫 번째 게시글",
    "content": "내용...",
    "createdAt": "2023-08-10T12:00:00Z"
  },
  {
    "id": 2,
    "title": "두 번째 게시글",
    "content": "내용...",
    "createdAt": "2023-08-11T14:30:00Z"
  }
]

이러한 접근 방식의 장점은 데이터 구조의 유연성과 쿼리의 간소화입니다. 게시글의 구조를 쉽게 변경할 수 있고, 사용자와 관련된 모든 게시글 정보를 한 번의 쿼리로 가져올 수 있습니다. 하지만 개별 게시글에 대한 쿼리와 인덱싱이 어려울 수 있고, 대량의 데이터를 처리할 때 성능 이슈가 발생할 수 있다는 단점도 있습니다.

이제 PostgreSQL에 직접 접속하여 이러한 데이터를 조회하는 방법을 살펴보겠습니다. PostgreSQL은 JSON 데이터를 다루기 위한 다양한 연산자와 함수를 제공하는데, 이를 활용하면 복잡한 쿼리도 효과적으로 수행할 수 있습니다.

  1. 모든 사용자와 그들의 게시글 목록 조회:
SELECT id, username, posts
FROM "User";
  1. 특정 사용자의 모든 게시글 조회:
SELECT posts
FROM "User"
WHERE id = 1;
  1. JSON 배열의 특정 요소에 접근 (예: 첫 번째 게시글의 제목):
SELECT id, username, posts->0->>'title' AS first_post_title
FROM "User";
  1. JSON 배열 내 모든 게시글의 제목 조회 (결과가 행으로 펼쳐집니다):
SELECT id, username, jsonb_array_elements(posts)->>'title' AS post_title
FROM "User";
  1. 특정 제목을 가진 게시글이 있는 사용자 찾기:
SELECT id, username
FROM "User"
WHERE exists (
  SELECT 1
  FROM jsonb_array_elements(posts) AS post
  WHERE post->>'title' = '첫 번째 게시글'
);
  1. 게시글 수가 2개 이상인 사용자 찾기:
SELECT id, username, jsonb_array_length(posts) AS post_count
FROM "User"
WHERE jsonb_array_length(posts) >= 2;
  1. 특정 날짜 이후에 작성된 게시글이 있는 사용자 찾기:
SELECT id, username
FROM "User"
WHERE exists (
  SELECT 1
  FROM jsonb_array_elements(posts) AS post
  WHERE (post->>'createdAt')::timestamp > '2023-08-10'::timestamp
);

이러한 쿼리들을 사용할 때 주의할 점이 몇 가지 있습니다. 먼저, Prisma가 생성한 테이블 이름은 대문자로 시작할 수 있으므로 "User"와 같이 따옴표로 감싸주는 것이 좋습니다. 또한, JSON 데이터에 접근할 때는 -> (JSON 객체 반환) 또는 ->> (텍스트 반환) 연산자를 사용합니다. jsonb_array_elements 함수를 사용하면 JSON 배열의 각 요소를 개별 행으로 펼칠 수 있어 매우 유용합니다.

여기서 사용된 jsonb_array_elements와 같은 함수들은 PostgreSQL에서 제공하는 특별한 함수입니다. 이는 SQL 공식 표준은 아니지만, PostgreSQL의 강력한 JSON 지원 기능 중 하나입니다. 다른 데이터베이스 시스템에서는 이와 유사한 기능을 다른 이름의 함수로 제공할 수 있습니다. 예를 들어, MySQL에서는 JSON_TABLE 함수를, SQL Server에서는 OPENJSON 함수를 사용하여 비슷한 작업을 수행할 수 있습니다.

이러한 PostgreSQL 특화 함수를 사용하면 코드의 이식성이 줄어들 수 있다는 점을 유의해야 합니다. 다른 데이터베이스 시스템으로 마이그레이션할 계획이 있다면, 이 부분을 고려하여 설계해야 합니다. 또한, SQL:2016부터 도입된 SQL/JSON 경로 표현식에 대한 표준을 참고하면 좋습니다. 이는 JSON 데이터 처리를 위한 표준화된 방법을 제공하지만, 아직 모든 데이터베이스에서 완전히 구현되지는 않았습니다.

결론적으로, PostgreSQL과 Prisma를 사용하여 JSON 배열로 사용자의 게시글 정보를 저장하고 조회하는 방법은 데이터 모델의 유연성을 극대화하면서도 강력한 쿼리 기능을 제공합니다. 이 접근 방식은 특히 빠르게 변화하는 요구사항에 대응해야 하는 프로젝트나, 데이터 구조가 자주 변경될 수 있는 상황에서 유용합니다. 하지만 대규모 데이터셋을 다룰 때의 성능 이슈와 다른 데이터베이스 시스템과의 호환성 문제를 고려해야 합니다. 프로젝트의 요구사항과 향후 확장 계획을 잘 파악하고, 이러한 장단점을 균형있게 고려하여 데이터 모델을 설계하는 것이 중요합니다.