[SQLite] 타입 친화성(Type Affinity)

평소에 SQLite는 테스트용으로만 사용하고 실제로는 잘 사용하지 않는데, 설치가 불필요한 로컬 RDB가 요구되는 프로젝트를 진행하게 되어 SQLite를 사용하기로 했다.

 

타입 종류

SQLite는 대표적인 RDBMS인 MySQL이나 PostgreSQL 등과 달리 데이터 타입이 매우 적다. 다음은 SQLite에서 제공하는 storage class이다. 

• TEXT : 데이터베이스 인코딩(UTF-8, UTF-16BE or UTF-16LE)을 사용하여 저장된 텍스트 문자열.
• INTEGER : 값 크기에 따라 0, 1, 2, 3, 4, 6, 8 바이트로 저장되는 정수(signed integer)
• REAL : 8 바이트 IEEE 부동 소수점 숫자로 저장되는 부동 소수점 값.
• BLOB : 입력 그대로 저장되는 데이터 
• NULL : NULL 값

문자열을 저장할 때 문자열을 지원하는 다양한 타입을 고민할 필요 없이 `TEXT`에 저장하면 된다. `BOOLEAN` 타입은 `INTEGER` 타입에 0(FALSE) 또는 1(TRUE) 값으로 저장하면 된다.

 

Note Spring Data JPA를 사용한다면 `Boolean` 타입 필드에 `@Column(columnDefinition="INTEGER")`을 적용해서 타입을 명시하자.

 

날짜 및 시간 

위에서 보이는 것처럼 날짜(date)나 시간(time) 관련 타입은 따로 제공하지 않기 때문에 `TEXT`, `REAL` 또는 `INTEGER`로 저장해야 한다. 

• TEXT : "YYYY-MM-DD HH:MM:SS.SSS" 
• REAL : 율리우스 일수로, 기원전 4714년 11월 24일 그리니치 정오 이후의 일수(예상 그레고리력 기준)
• INTEGER : 유닉스 시간으로, 1970년 1월 1일 00:00:00 UTC 이후의 초 수.

이렇게 저장된 데이터는 내장된 날짜/시간 함수를 사용해서 형식을 자유롭게 변환할 수 있다.

 

개인적으로 `LocalDateTime` 형식과 유사한 `TEXT` 타입을 선호한다. 위 포맷으로 저장하고 변환하는 유틸을 만들어두면 편리하다. 다만 포맷터가 있다 하더라도 `createdAt`, `modifiedAt` 등과 같이 날짜 시간 타입을 다룰 때 손이 더 가는 건 어쩔 수 없다.

 

다음은 이번에 프로젝트를 진행하면서 작성했던 코드로, 공통으로 사용하는 `createdAt`, `modifiedAt` 필드를 담고 있는 클래스이다. 

@Getter
@MappedSuperclass 
public abstract class BaseEntity {

    @Column(name = "created_at", updatable = false)
    protected String createdAt; // 이 클래스를 상속받는 다른 클래스에서 접근하기 위해서 protected로 선언

    @Column(name = "modified_at")
    private String modifiedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = CommonUtils.localDateTimeToString(LocalDateTime.now());
        modifiedAt = CommonUtils.localDateTimeToString(LocalDateTime.now());
    }

    @PreUpdate
    protected void onUpdate() {
        modifiedAt = CommonUtils.localDateTimeToString(LocalDateTime.now());
    }
}

처음에는 `LocalDateTime` 필드로 선언하고 `@Column` 애노테이션을 사용해서 `TEXT` 타입을 명시해줬는데 오히려 일이 복잡해져서 처음부터 `String` 타입으로 선언하고 따로 컬럼 삽입이나 변경 시 동작하는 메서드를 만들었다. 

또한 `@PrePersist`, `@PreUpdate`를 사용하기 때문에 JPA auditing 기능은 필요하지 않다.

 

Note SQLite에서 `DEFAULT CURRENT_TIMESTAMP`는 지원하지만, `ON UPDATE CURRENT_TIMESTAMP`는 지원하지 않는다.

 

타입 친화성

SQLite는 다른 데이터베이스와의 호환성을 위해 타입 친화성을 지원한다. 이는 다음을 의미한다.

• 컬럼을 SQLite에는 없는 `FLOAT`나 `TIMESTAMP` 등으로 선언할 수 있다. 
• 이렇게 저장된 컬럼을 SQLite는 내부적으로 위에서 소개한 5가지 storage class 중 하나로 매핑한다. 
• 따라서 네이티브 하지 않은 타입으로 선언된 컬럼들도 SQLite에서 기술적으로 유효하다. 

따라서 `Double` 타입 필드에 `@Column(columnDefinition="REAL")`을 사용해서 타입을 명시하지 않아도 실행했을 때 오류가 나지 않고 Hibernate가 만드는 쿼리를 확인해도 `REAL` 타입으로 그 컬럼이 선언되지 않는다.

 

Note Hibernate community dialect 의존성을 추가해도 SQLite에 있는 타입으로 변환되지 않는다.

 

이러한 타입 친화성 덕분에 오류가 나지는 않지만 그래도 컬럼을 명시하는 방향을 지향한다. 

 

 

🔖참고

• https://www.sqlite.org/datatype3.html