[Spring] SQLite 사용기

개발새발 개발·2024년 9월 24일
DatabaseJavaPostgreSQLSpringbootmysqlsqlite
post-thumbnail

니콜라스의 채널에서 'DB고민 끝내드림' 이라는 영상을 보았다.
SQLite와 PostgreSQL에 대해 호기심이 생기게 되었고 먼저 스타트업에서 MySQL과 함께 많이 사용하는 SQLite에 대해 알아보려 한다.

📚각각의 Database들의 특징

📖MySQL

많은 기업, 사람들이 사용하는 많큼 문서화, 자료들이 많고 대부분의 프로그래밍 언어에서 지원한다.

읽기 전용 명령을 관리하는 데 선호된다.
대부분의 기능들을 지원하고, 초보자들이 사용하기에 좋다.

가장 유명한 database이기에 다음 DB로 빠르게 넘어가겠다!

📒PostgreSQL

MySQL보다 좀 더 전문적인 데이터베이스로, 더 많은 기능들이 존재한다

특징으로는 이러한 것들이 있다.

  • 확장자를 지원한다
  • 복잡한 쿼리에 탁월
  • 대용량 데이터 관리에 적합
  • Catalog 기반임으로 확장이 용이
  • NoSQL 및 다양한 데이터 형식 지원 (JSON, hstore, XML 포함해서 등등)
  • 높은 안정성과 신뢰성을 제공

가장 중요한 부분은 이것이다

🔎확장자를 지원한다

PostgreSQL에서는 extension이라는 기능을 제공한다. extension으로 외부 프로그램을 PostgreSQL에 연동하여 추가적인 기능들을 사용할 수 있다.
이를 통해 NoSQL, DB단위 테스트, JwtToken 생성 등등 훨씬 많은 기능들을 사용할 수 있다.

📑SQLite

많은 사람들이 잘 못 생각하고 있는 것이 있다. SQ'Lite'라는 이름을 보고 성능이 좋지 않고, 토이 프로젝트에나 사용하는 데이터베이스라 생각할 수 있는데 그것은 옳지 않다.
SQLite는 작다, 빠르다, 믿을 수 있다 는 의미이다.

특징으로는 이러한 것들이 있다.

  • 적은 데이터 타입
    NULL, INTEGER, REAL, TEXT, BLOB 이 5개의 유형만 존재하기에 편리하다.

  • SQLite는 하나의 데이터베이스를 하나의 파일로 관리한다. (임베디드 데이터베이스)
    SQLite는 그저 하나의 디스크 파일일 뿐이기에 데이터베이스를 복사하여 붙여넣거나, 이메일로 DB를 전송하거나, USB에 넣을 수 있으며 데이터베이스를 백업하고 복원하는 것이 간단하다.

  • DB를 유지 관리 해줄 필요가 없다.

  • 애플리케이션 코드가 있는 서버에 데이터 베이스가 있기에

    • 데이터를 가져오기 위해 서버에 요청을 할 필요가 없다
    • 여러 대의 서버가 있는 애플리케이션에는 적합하지 않다.
    • 더 빠르고 저렴하다.

Redit에서 찾은 SQLite 단점들😅

  • 별도의 BOOLEAN 데이터 유형 없음
  • 별도의 DATETIME 데이터 유형 없음
  • 기본적으로 외래 키 적용이 꺼져 있음
  • PRIMARY KEY는 때때로 NULL을 포함할 수 있음
  • 의심스러운 SQL은 오류나 경고 없이 허용된다.
  • AUTOINCREMENT는 MySQL과 동일하게 작동하지 않는다.

Spring 적용시키기

dependencies 추가

implementation 'org.xerial:sqlite-jdbc:3.46.1.0'

SQLite는 hibernate에서 지원해주지 않아서 직접 SQLDialect를 생성해줘야한다. 😢

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.type.StringType;

import java.sql.Types;

public class SQLDialect extends Dialect {
    public SQLDialect() {
        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.REAL, "real");
        registerColumnType(Types.DOUBLE, "double");
        registerColumnType(Types.NUMERIC, "numeric");
        registerColumnType(Types.DECIMAL, "decimal");
        registerColumnType(Types.CHAR, "char");
        registerColumnType(Types.VARCHAR, "varchar");
        registerColumnType(Types.LONGVARCHAR, "longvarchar");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "timestamp");
        registerColumnType(Types.BINARY, "blob");
        registerColumnType(Types.VARBINARY, "blob");
        registerColumnType(Types.LONGVARBINARY, "blob");
        // registerColumnType(Types.NULL, "null");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");

        registerFunction("concat", new VarArgsSQLFunction(StringType.INSTANCE, "", "||", ""));
        registerFunction("mod", new SQLFunctionTemplate(StringType.INSTANCE, "?1 % ?2"));
        registerFunction("substr", new StandardSQLFunction("substr", StringType.INSTANCE));
        registerFunction("substring", new StandardSQLFunction("substr", StringType.INSTANCE));
    }

    public boolean supportsIdentityColumns() {
        return true;
    }

    public boolean hasDataTypeInIdentityColumn() {
        return false; // As specify in NHibernate dialect
    }

    public String getIdentityColumnString() {
        // return "integer primary key autoincrement";
        return "integer";
    }

    public String getIdentitySelectString() {
        return "select last_insert_rowid()";
    }

    public boolean supportsLimit() {
        return true;
    }

    protected String getLimitString(String query, boolean hasOffset) {
        return new StringBuffer(query.length() + 20).append(query).append(hasOffset ? " limit ? offset ?" : " limit ?")
            .toString();
    }

    public boolean supportsTemporaryTables() {
        return true;
    }

    public String getCreateTemporaryTableString() {
        return "create temporary table if not exists";
    }

    public boolean dropTemporaryTableAfterUse() {
        return false;
    }

    public boolean supportsCurrentTimestampSelection() {
        return true;
    }

    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    public String getCurrentTimestampSelectString() {
        return "select current_timestamp";
    }

    public boolean supportsUnionAll() {
        return true;
    }

    public boolean hasAlterTable() {
        return false; // As specify in NHibernate dialect
    }

    public boolean dropConstraints() {
        return false;
    }

    public String getAddColumnString() {
        return "add column";
    }

    public String getForUpdateString() {
        return "";
    }

    public boolean supportsOuterJoinForUpdate() {
        return false;
    }

    public String getDropForeignKeyString() {
        throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
    }

    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable,
                                                   String[] primaryKey, boolean referencesPrimaryKey) {
        throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
    }

    public String getAddPrimaryKeyConstraintString(String constraintName) {
        throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
    }

    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    public boolean supportsCascadeDelete() {
        return false;
    }
}

yml 파일

spring:
  datasource:
    url: jdbc:sqlite:practice.db
    username: root
    password: 1234
    driver-class-name: org.sqlite.JDBC

  jpa:
    hibernate:
      ddl-auto: create
    database-platform: JavaProject.Sqlite.global.config.SQLDialect
  • jpa.database-platform 에는 방금 만들어줬던 SQLDialect의 경로를 넣어준다.

이제 자신있게 실행!.. 하면 안된다..

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: org.hibernate.dialect.identity.IdentityColumnSupportImpl does not support identity key generation

문제가 무엇인지 보았는데 Sqlite는 Mysql과 Autoincrement가 달라서 INDENTITY에서 다른 전략으로 변경해줘야한다.


이제 회원가입 API를 만들어보자 
@Service
@RequiredArgsConstructor
@Transactional
public class SignupService {

    private final UserRepository userRepository;
    private final PasswordEncoder passwordEncoder;
    private final JwtTokenProvider jwtTokenProvider;

    public TokenResponse execute(SignupRequest signupRequest) {

        if (userRepository.existsByAccountId(signupRequest.getAccountId())) {
            throw UserAlreadyExistException.EXCEPTION;
        }

        String password = passwordEncoder.encode(signupRequest.getPassword());

            userRepository.save(
                User.builder()
                    .accountId(signupRequest.getAccountId())
                    .email(signupRequest.getEmail())
                    .password(password)
                    .name(signupRequest.getName())
                    .role(Role.STUDENT)
                    .build()
            );


        return jwtTokenProvider.createToken(signupRequest.getAccountId());
    }
}

이런식으로 코드를 작성해줬다.

이제 postman을 실행 시키면??

또 에러가 뜬다..

[SQLITE_BUSY] The database file is locked (database is locked)

찾아보니 아이디를 검증하는 메서드 문제였다. SQLite는 원자성을 지키기 위해, 데이터베이스에 write작업이 발생할 때 File단위로 lock을 걸어서, lock을 걸린 상태에서는 현재 데이터베이스에 write/read 작업이 불가능 하다고 한다.

그렇다면 당연히 아이디를 검증하는 메서드를 삭제해주면 잘 작동은 하겠지만,
그렇다고 아이디 검증을 지울 수는 없는 일이다.

그 대신, 서비스 로직을 하나로 commit하지 않게 @Transactional을 제거해준다면 아래와 같이 DB에 스레드가 왔다갔다하며 잘 실행이 된다.

  1. 아이디 검증
  2. 유저 생성

회고

SQLite의 저장방식이 흥미로웠다.
MySQL과 많이 달라서 각각의 특징을 공부하며 코드를 작성하는게 재밌어서 기분 좋게 글을 작성 한 것 같다.

참고 👇👇

https://stackoverflow.com/questions/16113182/jpa-sqlite-no-such-table-sequence/16306718#16306718
https://www.reddit.com/r/programming/comments/vniiaw/sqlite_or_postgresql_its_complicated/
https://www.youtube.com/watch?v=ocZid4g4UpY
https://deep-jin.tistory.com/entry/Spring-Boot%EC%99%80-sqlite3-%EC%97%B0%EB%8F%99%ED%95%98%EA%B8%B0-Hibernate
https://makedotworld.tistory.com/55#google_vignette

profile
개발새발 개발
이것이 개발이다
이전 포스트

SES 샌드박스 계정 해지

0개의 댓글