C#에서 SQLite를 업무 앱에 쓰기 ── WAL 모드·배타 제어·손상 대책·EF Core와의 구분

이전 기사 「Windows 앱의 데이터 저장 위치 고르기」에서, 늘어나는 업무 데이터·이력의 저장 위치는 SQLite가 1순위라고 썼습니다. 전제로 이어받을 결론을 한 줄로 말하면, 「위치는 사용자별이면 %LOCALAPPDATA%, 전체 사용자 공유면 %PROGRAMDATA%. 형식은 작은 설정이면 JSON, 늘어나는 업무 데이터·이력이면 SQLite. 비밀번호나 API 키만은 DPAPI로 따로 다룬다」입니다. 이 기사는 그중 「SQLite를 고른 다음」만 다루므로, 이전 글을 읽지 않았더라도 이 한 줄을 전제로 두면 충분합니다.

판단은 그걸로 끝나지만, 실제로 넣을 단계가 되면 다른 망설임이 나옵니다. 상담에서 자주 듣는 말은 「NuGet에서 SQLite로 검색하면 패키지가 여러 개 나와 무엇을 넣어야 할지 모르겠다」, 「동작은 하는데 가끔 database is locked가 난다. 재시도를 넣어 버티고 있는데 이게 맞는지」, 「백업은 DB 파일을 복사하기만 하면 되는지」 같은 목소리입니다.

어느 것이든 업무 앱에서 SQLite를 수년 운영한다면 반드시 지나가는 논점이며, 처음 설계에서 잡아 두면 나중에 고생하지 않는 부류입니다. 이 기사에서는 Microsoft.Data.Sqlite를 전제로, 라이브러리 선택, 연결 문자열과 풀링, WAL 모드의 구조, SQLITE_BUSY에 대한 대응, 타입 매핑의 함정, 손상 대책과 백업, 그리고 EF Core와의 구분까지, 설계 리뷰에서 매번 확인하는 항목을 한 바퀴 정리합니다.

1. 먼저 결론

한 줄로 말하면, 라이브러리는 Microsoft.Data.Sqlite, WAL 모드를 처음부터 켜고, 쓰기 경로를 하나로 모은다. 아래가 그 내역입니다.

• 신규 개발에서 쓸 라이브러리는 Microsoft.Data.Sqlite(또는 그 위에 올라가는 EF Core의 SQLite 프로바이더)가 기본입니다. System.Data.SQLite와는 연결 문자열도 세부 동작도 호환되지 않는 별개이므로, 웹상의 샘플이 어느 쪽 전제인지 항상 확인하세요.¹
• WAL 모드를 첫 릴리스부터 켭니다. 읽기와 쓰기의 병렬성이 올라가고, database is locked의 대부분이 사라집니다. 설정은 DB 파일 자체에 유지되지만, 네트워크 공유 위에서는 쓸 수 없습니다.²
• database is locked(SQLITE_BUSY)는 「오류 처리를 더하는」 대상이 아니라 설계를 다시 볼 신호입니다. Microsoft.Data.Sqlite는 타임아웃(기본 30초)까지 자동 재시도해 주지만³, 근본 대책은 쓰기 경로를 하나로 모으는 것입니다.
• 잘게 나뉜 INSERT를 대량으로 할 때는, 명시적 트랜잭션으로 묶기만 해도 두세 자릿수 빨라집니다. 「SQLite가 느리다」고 느끼면 먼저 커밋 단위를 의심하세요.
• SQLite의 타입은 실질적으로 INTEGER / REAL / TEXT / BLOB 네 가지이며, DateTime·Guid·decimal은 TEXT로 저장됩니다. 특히 decimal은 비교·정렬이 문자열 규칙이 되므로, 금액은 최소 통화 단위의 정수로 두는 것이 안전합니다.⁴
• 백업은 가동 중인 단순 파일 복사 금지입니다. VACUUM INTO 또는 Backup API(SqliteConnection.BackupDatabase)를 씁니다. -wal / -shm 파일을 손으로 지우는 것도 절대 금지입니다.⁵
• 순정 SQLite는 암호화를 지원하지 않습니다. 연결 문자열의 Password가 동작하는 것은 SQLCipher 계열 네이티브 라이브러리를 넣은 경우뿐이며⁶, 소량의 기밀 정보라면 DB 전체 암호화보다 DPAPI(Data Protection API. Windows가 OS 기능으로 제공하는 암호화 API로, 키를 앱이 쥐지 않아도 됩니다) 보호를 먼저 검토하세요. 자세한 내용은 「Windows 앱의 기밀 정보 저장 - DPAPI로 평문 설정을 피하기」에 적어 두었습니다.

그림의 실선은 항상 성립하는 관계, 점선은 조건이 붙는 관계입니다(성립 조건은 상세 페이지의 관계별 설명에 적혀 있습니다). 관계 전체 목록(총 27건, 근거와 확신도 포함)과 주요 개념의 정의는 지식 맵 상세 페이지에 정리되어 있습니다(일본어). 데이터: JSON-LD / Turtle

2. 라이브러리 선택 ── 이름은 비슷한데 내용은 다르다

.NET에서 SQLite를 쓰는 패키지는 여러 개이고, 이름이 헷갈리는 것이 첫 번째 걸림돌입니다. 정리하면 다음과 같습니다.

Microsoft.Data.Sqlite는 EF Core 팀이 유지하며, EF Core SQLite 프로바이더의 토대이기도 합니다.¹ NuGet 패키지에 네이티브 바이너리(SQLite 본체)가 포함되므로 클라이언트 PC로의 배포 작업은 필요 없고, x86/x64/ARM64 차이도 NuGet 쪽이 흡수합니다.

주의할 점은 System.Data.SQLite용 정보가 그대로 쓰이지 않는다는 것입니다. 오래 쓰인 만큼 웹상의 샘플은 System.Data.SQLite 전제가 대량으로 있고, 다음과 같은 비호환을 밟습니다.

• 연결 문자열에 호환성이 없습니다. Version=3이나 UseUTF16Encoding, 날짜 형식을 바꾸는 DateTimeFormat 같은 키워드는 Microsoft.Data.Sqlite에 존재하지 않으며, 지정하면 예외가 납니다. 키워드는 Data Source / Mode / Cache / Password / Foreign Keys / Default Timeout / Pooling 등 극소수입니다.⁶
• 타입 처리가 다릅니다. 예를 들어 Guid는 System.Data.SQLite 기본값에서는 BLOB, Microsoft.Data.Sqlite에서는 TEXT로 저장됩니다. 두 라이브러리로 같은 DB 파일을 읽고 쓰는 이행기에는 이 차이가 데이터 불일치로 나타납니다.

Microsoft.Data.Sqlite는 의도적으로 얇게 만들어져 있어, 독자 타입 변환이나 편의 기능이 없는 만큼 SQLite 공식 문서의 기술이 그대로 통합니다. 기존 앱이 System.Data.SQLite로 안정 가동 중이라면 무리하게 옮길 필요는 없지만, 신규 코드는 Microsoft.Data.Sqlite로 모으고, 이전한다면 위의 비호환을 이전 항목으로 먼저 뽑아 두는 것이 원칙입니다.

3. 연결과 연결 문자열 ── 풀링이 파일을 붙잡는다

연결 문자열의 기본형은 파일 경로뿐입니다. 실무에서 신경 쓰는 것은 Mode와 Pooling입니다.⁶

using Microsoft.Data.Sqlite;

var builder = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Mode = SqliteOpenMode.ReadWriteCreate  // 기본값. 없으면 만든다
};
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open();

• Mode: 기본값은 ReadWriteCreate(없으면 생성). 참조 전용 마스터 DB를 배포하는 경우에는 ReadOnly를 지정해 두면, 버그나 오조작에 의한 쓰기를 막을 수 있습니다.
• Cache: 보통은 기본값 그대로 둡니다. Cache=Shared는 WAL 모드와의 병용이 비권장이라고 문서에 명시되어 있으므로, WAL을 쓰는 이 기사의 방침에서는 자리가 없습니다.⁶
• Password: 지정하면 연결 직후에 PRAGMA key가 보내지지만, 표준 네이티브 라이브러리는 암호화를 지원하지 않으므로 아무 일도 일어나지 않습니다.⁶ DB 전체 암호화가 요건이라면 SQLCipher 계열 번들(SQLitePCLRaw.bundle_e_sqlcipher 등)로 바꿔 끼워야 하고, 그 암호화 키 보관에는 결국 DPAPI가 필요합니다. DPAPI(Data Protection API)는 Windows가 OS 기능으로 제공하는 암호화 API이며, System.Security.Cryptography.ProtectedData에서 씁니다. DataProtectionScope.CurrentUser를 지정해 암호화한 값은 그 사용자의 로그온 자격 정보에 묶이며, 원칙적으로 다른 사용자·다른 PC에서는 복호화할 수 없습니다. 「암호화 키 자체를 어디에 둘 것인가」라는 무한 회귀를 OS에 떠넘기는 도구로 이해하면 충분합니다(제 1장의 링크 대상에서 자세히 적었습니다).
• Default Timeout: 명령 타임아웃(기본 30초). 제 5장의 재시도 시간 상한이 됩니다.

한 가지 더, 비동기 API에 대해. SQLite 자체에 비동기 I/O가 없으므로 ExecuteNonQueryAsync 같은 async 메서드는 내부적으로는 동기 실행입니다.⁷ 「async로 바꿨으니 UI가 멈추지 않는다」는 성립하지 않으므로, 무거운 쿼리는 Task.Run 등으로 명시적으로 워커 스레드에 넘깁니다. async/await 판단 기준은 「C# async/await 실무 판단표」에서 정리한 대로입니다.

3.1 풀링의 함정 ── Close해도 파일은 열린 채

Microsoft.Data.Sqlite는 버전 6.0부터 연결 풀링이 기본으로 켜져 있습니다.⁶ Open마다 파일을 다시 열지 않아도 되는 이점의 한편으로, Close / Dispose해도 네이티브 연결은 풀에 남아 DB 파일 핸들을 계속 붙잡습니다. 그 결과 다음 같은 조작이 「파일이 사용 중입니다」로 실패합니다.

• 「데이터를 초기화한다」기능으로 DB 파일을 지우고 다시 만든다
• 백업에서 복원할 때 DB 파일을 교체한다
• 제거(uninstall)나 파일을 옮겨 두는 처리에서 DB 파일을 이동한다

대처는 파일 조작 직전에 풀을 비우는 것입니다.

// 이 연결 문자열에 해당하는 풀을 폐기하고, 파일 핸들을 해제한다
SqliteConnection.ClearPool(new SqliteConnection(connectionString));
File.Delete(dbPath);

WAL 모드(다음 장)라면 -wal / -shm 파일이 남아 있을 수 있으므로 함께 정리합니다. 앱 종료 시 모두 해제하려면 SqliteConnection.ClearAllPools(), 애초에 풀이 필요 없는 단발 도구라면 연결 문자열에서 Pooling=False로 두는 방법도 있습니다. 「Close했는데 지울 수 없다」는 SQLite 이전 후의 문의에서 자주 보이므로, DB 파일을 다루는 유틸리티 처리에는 처음부터 ClearPool을 넣어 두세요.

4. WAL 모드의 구조 ── 무엇이 일어나는지 알고 쓴다

이전 기사에서 「WAL 모드를 켠다」고만 썼으므로, 이번에는 구조까지 들어갑니다. 기본값인 롤백 저널 방식에서는 쓰기 중에 읽기가 막히며, database is locked의 주된 원인이 됩니다. WAL(Write-Ahead Logging) 방식에서는 변경을 DB 본체가 아니라 append 전용 로그 파일에 쓰므로, 읽기는 쓰기를 막지 않고, 쓰기도 읽기를 막지 않습니다.² UI 스레드가 이력을 표시하면서 백그라운드에서 계측값을 쓰는, 업무 앱의 전형 구성이 그대로 성립합니다.

WAL 모드로 바꾸면 DB 본체(체크포인트에서 확정된 내용) 옆에 파일 두 개가 나타납니다.

세 파일과 읽기·쓰기의 관계를 그림으로 그리면 다음과 같습니다.

flowchart TB
    W["쓰기 연결<br/>동시에 하나만"]
    R["읽기 연결<br/>몇 개든 동시에 열 수 있다"]
    WAL["app.db-wal（append 로그）<br/>커밋은 됐지만 본체에 미반영인 변경<br/>여기를 손으로 지우면 최근 데이터가 사라진다"]
    SHM["app.db-shm（wal-index）<br/>공유 메모리. WAL의 어디까지 읽으면 되는지를<br/>프로세스 사이에서 맞춘다"]
    DB["app.db（본체）<br/>체크포인트에서 확정된 내용"]
    CP["체크포인트<br/>기본값은 WAL이 1,000페이지（약 4MB）에서 자동 실행<br/>긴 읽기가 자리 잡으면 진행되지 않고 WAL이 비대해진다"]

    W -->|"변경을 이어 쓴다"| WAL
    R -->|"확정된 부분을 읽는다"| DB
    R -->|"미반영 커밋은 이쪽에서 읽는다"| WAL
    SHM -.->|"읽을 범위를 알려 준다"| R
    WAL --> CP
    CP -->|"본체로 복사해 넣고 WAL을 비운다"| DB

읽기가 본체와 WAL 양쪽을 보러 가므로, 쓰는 중에도 읽기가 멈추지 않습니다. 이것이 WAL의 효과입니다. 동시에 「본체만 복사해도 최근 데이터는 들어 있지 않다」, 「-shm은 공유 메모리라 네트워크 공유에서는 성립하지 않는다」는 제약도 이 그림에서 바로 읽힙니다.

잡아 둘 성질은 네 가지입니다.

• 체크포인트: -wal의 내용을 본체로 복사해 넣는 처리이며, 기본값에서는 WAL이 1,000페이지(약 4MB)에 도달하면 자동 실행됩니다.² 장시간 읽기 트랜잭션이 자리 잡으면 체크포인트가 진행되지 않고 -wal이 비대해지므로, 「읽기 연결을 연 채로 들고 다닌다」설계는 피하고, 쓸 때 열고 닫습니다(풀링 덕분에 다시 열기는 빠릅니다).
• 설정은 DB에 유지된다: PRAGMA journal_mode=WAL은 한 번 실행하면 DB 파일 자체에 기록되며, 이후 어느 연결로 열어도 WAL 그대로입니다.² 연결마다 발행할 필요는 없습니다.
• 쓰기는 여전히 동시에 하나: WAL이 올리는 것은 읽기·쓰기의 병렬성이며, 쓰기끼리는 여전히 배타입니다. 여기를 오해하고 「WAL로 바꿨으니 멀티스레드로 마음껏 쓴다」고 생각하면 제 5장의 SQLITE_BUSY를 만납니다.
• 네트워크 공유에서는 쓸 수 없다: wal-index가 공유 메모리를 전제로 하므로, 다른 머신의 프로세스 사이에서는 동작하지 않습니다.² 애초에 SQLite를 네트워크 공유에 두지 않는 것은 이전 기사와 같습니다.

운영 면의 주의로, -wal 파일에는 커밋은 됐지만 아직 본체에 반영되지 않은 트랜잭션이 들어 있습니다. 「본체 .db만 복사하면 된다」, 「-wal은 임시 파일이니 지워도 된다」는 둘 다 잘못이며, 최근 커밋이 사라지거나 최악에는 DB가 깨집니다.⁵ 이 점이 제 7장의 「백업은 파일 복사로는 안 된다」는 이야기로 바로 이어집니다.

4.1 설정이 적용됐는지 확인한다

WAL로 전환은 PRAGMA journal_mode=WAL을 한 번 실행하면 되지만, 「실행한 줄 알았는데 적용되지 않았다」를 막기 위해 설정은 반드시 다시 읽어 확인합니다. PRAGMA는 값을 지정하지 않고 쓰면 현재 값을 반환하므로, 확인은 몇 줄이면 됩니다.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA journal_mode";
var mode = (string)cmd.ExecuteScalar()!;      // WAL이 적용됐다면 "wal"
if (!string.Equals(mode, "wal", StringComparison.OrdinalIgnoreCase))
    logger.LogWarning("journal_mode가 여전히 {Mode}입니다", mode);

시작할 때 자가 진단으로 봐 둘 항목을 정리합니다.

중요한 것은 4열 「적용 방식」의 차이입니다. journal_mode는 DB 파일에 기록되므로 연결마다 발행할 필요는 없지만, 그 외는 연결을 열 때마다 정해지는 설정입니다. 「개발 머신에서는 됐는데 운영에서는 안 된다」는 상담은, 연결 문자열이나 네이티브 라이브러리 차이로 여기가 바뀌어 있는 경우가 흔합니다.

앱을 시작하지 않고 DB 파일만 조사하려면 SQLite 공식 커맨드라인 셸 sqlite3에서 같은 PRAGMA를 실행할 수 있습니다. 현장 조사 도구로 가지고 있으면 「이 DB가 정말 WAL인지」를 1분이면 확인할 수 있습니다.

5. 배타와 SQLITE_BUSY ── 쓰기를 하나로 모은다

SQLITE_BUSY(예외 메시지에서는 database is locked)는 다른 연결이 쓰기 잠금을 쥐고 있을 때 발생합니다. 먼저 알아 둘 점은, Microsoft.Data.Sqlite는 busy/locked 오류에 대해 명령 타임아웃(기본 30초)까지 자동으로 재시도한다는 것입니다.³ 즉 앱 쪽에서 catch한 뒤 슬립하고 다시 실행하는 자체 재시도는 보통 필요 없습니다. 그래도 타임아웃에 도달해 예외가 날아온다면 다음 중 하나입니다.

• 다른 연결(또는 다른 프로세스)이 30초를 넘는 긴 트랜잭션을 쥐고 있다
• 읽기로 시작한 트랜잭션 안에서 쓰기로 승격하려다 다른 쓰기와 충돌했다 ── 기다려도 해결되지 않아 즉시 실패합니다. 「읽고 나서 쓴다」처리는 처음부터 쓰기 트랜잭션으로 설계하는 것이 정석입니다
• 대량의 잘게 나뉜 쓰기가 여러 스레드에서 들어와 잠금 쟁탈이 되고 있다

어느 것이든 「재시도 횟수를 늘린다」로는 해결되지 않습니다. 대책은 트랜잭션을 짧게 두는 것과 쓰기 경로를 하나로 모으는 것입니다.

5.1 System.Threading.Channels로 쓰기 큐를 만든다

여러 스레드가 발생원이 되는 데이터(계측값, 조작 로그 등)는 각 스레드가 DB에 직접 쓰지 않고, 큐에 던져 전담 쓰기 루프가 처리하는 형태로 둡니다. .NET이라면 System.Threading.Channels를 그대로 쓸 수 있습니다.

using System.Globalization;
using System.Threading.Channels;
using Microsoft.Data.Sqlite;

public sealed record Measurement(string DeviceId, double Value, DateTime CreatedAtUtc);

public sealed class MeasurementWriter : IAsyncDisposable
{
    private readonly Channel<Measurement> _channel =
        Channel.CreateBounded<Measurement>(new BoundedChannelOptions(10_000)
        {
            FullMode = BoundedChannelFullMode.Wait  // 넘치면 생산 쪽을 기다리게 한다
        });
    private readonly string _connectionString;
    private readonly Task _loop;

    public MeasurementWriter(string connectionString)
    {
        _connectionString = connectionString;
        _loop = Task.Run(WriteLoop);
    }

    // 어느 스레드에서 호출해도 된다. DB에는 손대지 않는다. 쓰기 루프가
    // 죽어 있으면 ChannelClosedException으로 투입 쪽에도 바로 보인다
    public ValueTask EnqueueAsync(Measurement m) => _channel.Writer.WriteAsync(m);

    private async Task WriteLoop()
    {
        try
        {
            await WriteLoopCore();
        }
        catch (Exception ex)
        {
            // 디스크 풀 등으로 쓰기 역할이 죽은 것을 채널을 통해 투입 쪽에
            // 전한다. 이것을 빼먹으면 큐가 가득 찬 시점에 EnqueueAsync가
            // 영원히 기다리고, 아무도 장애를 알아채지 못한다
            _channel.Writer.TryComplete(ex);
            throw;
        }
    }

    private async Task WriteLoopCore()
    {
        using var conn = new SqliteConnection(_connectionString);
        conn.Open();
        var buffer = new List<Measurement>(500);
        while (await _channel.Reader.WaitToReadAsync())
        {
            // 쌓인 분을 최대 500건까지 회수해 1트랜잭션으로 쓴다
            buffer.Clear();
            while (buffer.Count < 500 && _channel.Reader.TryRead(out var m))
                buffer.Add(m);

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText =
                "INSERT INTO measurement (device_id, value, created_at) " +
                "VALUES ($device, $value, $at)";
            var pDevice = cmd.Parameters.Add("$device", SqliteType.Text);
            var pValue  = cmd.Parameters.Add("$value",  SqliteType.Real);
            var pAt     = cmd.Parameters.Add("$at",     SqliteType.Text);
            foreach (var m in buffer)
            {
                pDevice.Value = m.DeviceId;
                pValue.Value  = m.Value;
                // 컬처 기본의 달력·숫자에 영향받지 않도록 InvariantCulture를 명시
                pAt.Value     = m.CreatedAtUtc.ToString(
                    "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
    }

    public async ValueTask DisposeAsync()
    {
        // 쓰기 루프가 실패로 먼저 채널을 닫아도 예외로 만들지 않는다
        // (Complete()이면 이중 클로즈로 던지고, 원래 예외를 숨긴다)
        _channel.Writer.TryComplete();
        await _loop;  // 나머지를 다 쓴다. 루프가 죽어 있으면 원래 예외가 여기서 나온다
    }
}

이 형태로 두면 쓰기 충돌이 구조적으로 일어나지 않는 데다가, 큐에 쌓인 분을 자연스럽게 배치할 수 있어 다음 절의 고속화도 함께 손에 들어옵니다. 종료 시 DisposeAsync로 큐를 다 쓴 뒤에 닫는 점, FullMode로 가득 찼을 때의 동작을 명시적으로 정하는 점, 그리고 쓰기 루프 실패를 TryComplete(ex)로 투입 쪽에 전파하는 점(일원화한 쓰기 역할이 조용히 죽으면 장애가 큐 가득 참 때의 영구 대기로 나타납니다)도 업무 앱에서는 효과가 있습니다. 같은 「경로를 하나로 모은다」발상은 파일로 연동할 때의 배타 설계(「파일 연동과 잠금의 베스트 프랙티스」)와도 공통입니다.

5.2 트랜잭션으로 묶으면 속도가 자릿수 단위로 달라진다

SQLite는 커밋마다 스토리지에 동기 쓰기(fsync)를 하므로, 한 건씩 암시적 커밋으로 INSERT하면 SSD에서도 초당 수백〜수천 건, HDD라면 초당 수십 건에서 한계에 닿습니다. 같은 INSERT를 1,000건 단위의 명시적 트랜잭션(BeginTransaction으로 감싸고 마지막에 Commit)으로 묶기만 해도 초당 수만〜수십만 건 수준에 이릅니다. 튜닝이라고 부르기도 과한 3줄 변경으로 두세 자릿수가 달라집니다.

「CSV 가져오기에 20분 걸린다」, 「시작 시 데이터 이전이 끝나지 않는다」는 상담의 상당수는 이것이 원인이며, 트랜잭션화와 매개변수 재사용(앞 절 코드 형태)만으로 해결됩니다. 반대로 트랜잭션을 너무 길게 두면 이번에는 다른 쓰기를 기다리게 하므로, 「수백〜수천 건, 또는 수백 밀리초분」을 한 커밋으로 두는 정도가 실무적인 타협점입니다.

6. 타입 매핑의 함정 ── 네 개뿐인 타입에 무엇을 할당할 것인가

SQLite가 실제로 저장할 수 있는 타입은 INTEGER / REAL / TEXT / BLOB 네 가지뿐이며, .NET 타입은 이 중 하나에 할당됩니다. Microsoft.Data.Sqlite의 주요 매핑은 다음과 같습니다.⁴

TEXT로 떨어지는 세 가지가 주의 대상입니다.

• DateTime: TEXT의 ISO 8601 계열 형식은 형식과 타임존만 통일되어 있으면 문자열 정렬＝시각 정렬이 되므로, 실용상 문제는 없습니다. 반대로 말하면 UTC와 로컬 시각이 섞인 순간에 깨집니다. 「저장은 UTC, 표시에서 로컬로 변환」을 처음에 정하고 전 경로에서 지키는 것이 유일한 해이며, datetime('now') 같은 SQLite 함수가 반환하는 것도 UTC입니다. 한 가지 더, 직접 문자열화할 때는 ToString에 CultureInfo.InvariantCulture를 반드시 넘깁니다. 컬처 기본값 그대로면 일본력·불교력 등 그레고리력이 아닌 컬처에서 동작한 단말만 연도 표기가 바뀌어, 정렬도 읽기도 깨집니다(5.1의 코드 예 참조).
• Guid: 문자열로 저장되므로 조회는 문자열 일치입니다. 다른 도구나 라이브러리가 다른 표기(대소문자, BLOB 형식)로 쓰면 대조에 실패하므로, 여러 언어·도구에서 다룬다면 표기를 사양으로 명문화해 두세요.
• decimal: 가장 큰 함정입니다. REAL에서는 결손이 생기므로 TEXT로 저장되지만⁴, TEXT 열에 대한 WHERE amount > 1000 같은 비교는 SQLite의 타입 서열에서 TEXT는 항상 숫자보다 크기 때문에 의도대로 움직이지 않습니다. SUM 같은 집계도 내부에서 REAL 변환되어, decimal을 고른 이유인 정밀도가 사라집니다.

decimal의 실무 지침은 단순하며, 금액은 최소 통화 단위의 정수(INTEGER)로 두는 것입니다. 엔화라면 엔 단위의 long으로 저장하고 표시 시에 변환합니다. 정수라면 비교도 집계도 정확하고 빠르며, 타입의 함정이 사라집니다. 기존 스키마 사정으로 어쩔 수 없이 decimal 그대로 다룰 때는, 비교·집계를 SQL 쪽에서 하지 않고 읽어 낸 뒤 .NET 쪽에서 한다고 선을 그으세요.

덧붙여 CREATE TABLE에 쓰는 타입 이름은 「친화성(affinity)」힌트에 지나지 않으며, STRING 같은 독자 타입 이름은 암시적 변환 사고의 원인이 됩니다. 열 타입 이름도 INTEGER / REAL / TEXT / BLOB 네 가지만 쓰는 것이 공식 권장입니다.⁴

7. 운영 ── 깨뜨리지 않고, 깨져도 되돌린다

7.1 시작 시의 quick_check

SQLite는 트랜잭션으로 지켜지더라도, 디스크 장애나 잘못된 파일 조작에 의한 손상은 제로가 되지 않습니다. 깨진 DB로 계속 동작해 피해를 키우지 않도록, 시작 시에 정합성 검사를 넣어둡니다. 완전한 integrity_check는 큰 DB에서는 시간이 걸리므로, 일상은 경량판 quick_check로 충분합니다.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA quick_check";
var result = (string)cmd.ExecuteScalar()!;
if (result != "ok")
{
    // 깨진 DB에 이어 쓰지 않는다. 읽기 전용으로 축소하고 복원을 재촉한다
    logger.LogError("데이터베이스 손상을 감지했습니다: {Detail}", result);
    EnterReadOnlyMode(result);
}

손상을 검출했을 때 자동 복구·자동 롤백을 하지 않는(사용자 조작을 끼운다) 방침은 이전 기사의 6.2절에서 쓴 대로입니다.

7.2 백업 ── 왜 파일 복사로는 안 되는가

가동 중인 DB 파일의 단순 복사는 트랜잭션 도중 상태를 섞어 집어낼 수 있어, SQLite 공식이 손상 원인으로 명시합니다.⁵ WAL 모드라면 더 나아가, 본체만 복사하고 -wal(본체 미반영 커밋을 포함. 제 4장)을 남겨 두면 최근 데이터가 빠집니다.

올바른 방법은 두 가지이며, 둘 다 가동 중에 일관성 있는 스냅샷을 잡을 수 있습니다.

• VACUUM INTO: SQL 한 문으로 단편화를 해소한 최소 크기 복사본을 만듭니다.⁸ 코드 예는 이전 기사의 6.3절에 실었으므로 그쪽을 참조하세요.
• SqliteConnection.BackupDatabase: SQLite Backup API의 래퍼로, 연결 객체 사이에서 복사합니다.

using var source = new SqliteConnection($"Data Source={dbPath}");
using var target = new SqliteConnection($"Data Source={backupPath}");
source.Open();
target.Open();
source.BackupDatabase(target);  // 가동 중이어도 일관된 스냅샷이 된다

다만 BackupDatabase에는 주의점이 있습니다. Microsoft.Data.Sqlite의 현재 구현은 가능한 한 빨리 복사하고, 완료될 때까지 다른 연결의 쓰기를 막습니다.⁹ 큰 DB를 계측이나 사용자 조작이 이어지는 도중에 백업하면, 그 사이 쓰기가 SQLITE_BUSY나 화면 멈춤으로 나타납니다. 일상적인 세대 백업은 VACUUM INTO, BackupDatabase는 인메모리 DB와의 상호 복사나, 쓰기가 멈춘 시간대·유지보수 처리에서의 복제에 쓰는 구분이 안전합니다. 정기 실행에 작업 스케줄러를 쓴다면, 외부에서의 파일 복사가 아니라 앱 자신(또는 SQLite를 올바르게 여는 작은 도구)에 위의 백업을 실행시키는 형태로 두세요. 정기 실행 자체의 설계는 「작업 스케줄러로 정기 실행을 안전하게 운영하기」에서 쓴 대로입니다.

7.3 위치와 마이그레이션

DB 파일 위치는 %LOCALAPPDATA%\회사명\앱이름이 기본이고, 스키마 버전 관리는 PRAGMA user_version에 의한 시작 시 마이그레이션이 최소 구성입니다. 둘 다 이전 기사(3장과 6.1절)에서 코드와 함께 썼으므로 여기서는 반복하지 않습니다. 한 가지만 보완하면, 마이그레이션 실행 전에 7.2의 백업을 한 세대 잡아 두면 「마이그레이션에 실패해 시작 불능」이라는 최악 케이스에서의 복구가 단순 파일 교체가 됩니다.

8. EF Core를 써야 할 장면 ── ORM의 손익분기

여기까지 순수 Microsoft.Data.Sqlite로 썼지만, EF Core의 SQLite 프로바이더를 써야 할 장면도 분명합니다. 판단 축은 앱의 성격입니다.

EF Core를 고를 때는 SQLite 프로바이더 특유의 제약을 알아 둘 필요가 있습니다.¹⁰

• ALTER TABLE 제약에 의한 테이블 재구축: SQLite는 열의 타입 변경이나 삭제를 직접 지원하지 않으므로, AlterColumn이나 DropColumn을 포함한 마이그레이션은 「새 테이블 생성 → 데이터 복사 → 옛 테이블 삭제 → 이름 변경」이라는 재구축으로 실행됩니다. 데이터량이 많은 환경에서는 적용 시간과 디스크 사용량에 영향을 주므로, 큰 테이블의 스키마 변경은 계획적으로.
• 멱등 스크립트를 만들 수 없다: SQL Server처럼 if-then이 붙은 마이그레이션 스크립트는 생성하지 못합니다. 적용은 앱 시작 시의 dbContext.Database.Migrate()가 현실적입니다.
• decimal / DateTimeOffset 연산은 클라이언트 평가: 제 6장의 타입 사정은 EF Core에서도 사라지지 않습니다. 등호 이외의 비교나 정렬은 클라이언트 평가가 되므로, 금액을 정수 최소 단위로 두는 지침은 EF Core에서도 같습니다(값 변환기로 long에 변환해 저장할 수 있습니다).
• WAL은 기본으로 유효: EF Core가 만든 DB는 처음부터 WAL 모드이므로⁷, 제 4장의 설정은 필요 없습니다. 동작 이해는 계속 필요합니다.

덧붙여 EF Core를 채택한 경우에도, 리포지토리 계층의 단위 테스트에 SQLite 인메모리 DB를 쓰는 방법이 효과적입니다. 운영과 같은 프로바이더로 움직이므로 「목에서는 통과하는데 실제 DB에서는 실패한다」는 틈이 작아집니다. 테스트를 어느 계층에서 작성할지의 사고방식은 「유닛 테스트와 통합 테스트의 경계」를 참조하세요.

9. 정리

SQLite는 「넣기만 하면 30분, 올바르게 운영하려면 설계가 필요」한 라이브러리입니다. 그렇다고 필요한 설계는 정해져 있어, 이 기사 내용을 체크리스트로 만들면 다음 6점이 됩니다.

• 라이브러리는 Microsoft.Data.Sqlite(또는 그 위의 EF Core). System.Data.SQLite 전제 정보와 섞지 않는다
• WAL 모드를 첫 릴리스부터 켜고, -wal / -shm의 역할을 이해해 둔다
• 쓰기는 하나로 모으고(Channels의 쓰기 큐), 잘게 나뉜 INSERT는 트랜잭션으로 묶는다
• DateTime은 UTC로 통일, 금액은 최소 통화 단위의 정수. decimal을 TEXT 그대로 비교·집계하지 않는다
• 시작 시에 quick_check, 백업은 VACUUM INTO 또는 BackupDatabase. 가동 중 파일 복사는 하지 않는다
• DB 파일을 지우거나 교체하는 처리에는 SqliteConnection.ClearPool을 잊지 않는다

database is locked에 재시도를 겹쳐 버티고 있다, 백업을 파일 복사로 받고 있다, 같은 구성이 떠오른다면, 깨지기 전에 한 번 이 기사의 항목으로 점검해 보세요. 어느 것이든 고치는 일 자체는 작은 변경으로 끝납니다.

관련 기사

• Windows 앱의 데이터 저장 위치 고르기 ── SQLite / JSON / 레지스트리 / Access 판단표
• Windows 앱의 기밀 정보 저장 - DPAPI로 평문 설정을 피하기
• 작업 스케줄러로 정기 실행을 안전하게 운영하기
• C# async/await 실무 판단표 - Task.Run과 ConfigureAwait

관련 상담 영역

合同会社小村ソフト에서는 SQLite를 넣은 업무 앱의 설계 리뷰(배타 제어·백업·마이그레이션 설계)나, database is locked·데이터 손상·성능 저하 같은 가동 중 앱의 트러블 조사, Access 등 기존 데이터 스토어에서의 이전 지원을 다룹니다.

• 기술 상담·설계 리뷰
• Windows 앱 개발
• 기존 자산 활용·이전 지원
• 문의

참고 링크

관련 기사

같은 태그를 공유하는 최신 기사입니다. 더 가까운 주제로 지식을 넓힐 수 있습니다.

관련 토픽

이 기사와 가까운 토픽 페이지입니다. 기사를 출발점 삼아 관련 서비스와 다른 기사로 이어집니다.

이 주제와 연결되는 서비스

이 기사는 다음 서비스 페이지로 이어집니다. 가까운 입구부터 확인해 주세요.

자주 묻는 질문

이 기사 주제에 대해 상담 시 자주 나오는 질문을 모았습니다.

SQLite에서 「database is locked」가 나오는 이유는 무엇인가요?

SQLITE_BUSY는 다른 연결이 쓰기 잠금을 쥐고 있을 때 발생합니다. Microsoft.Data.Sqlite는 명령 타임아웃(기본 30초)까지 자동으로 재시도하므로, 자체 재시도는 보통 필요 없습니다. 그래도 예외가 난다면 30초를 넘는 긴 트랜잭션, 읽기에서 쓰기로의 승격 충돌, 여러 스레드의 잘게 나뉜 쓰기 경합이 원인이며, 재시도 횟수를 늘려도 해결되지 않습니다. 근본 대책은 트랜잭션을 짧게 두는 것과, System.Threading.Channels 같은 큐로 쓰기 경로를 하나로 모으는 것입니다. WAL 모드를 켜면 읽기·쓰기 사이의 차단 대부분도 사라집니다.

C#에서 SQLite를 쓰려면 어떤 라이브러리를 골라야 하나요?

신규 개발에서는 Microsoft가 유지하는 ADO.NET 프로바이더인 Microsoft.Data.Sqlite(또는 그 위에 올라가는 EF Core의 SQLite 프로바이더)가 기본입니다. NuGet 패키지에 네이티브 SQLite 본체가 포함되므로 별도 배포 작업은 필요 없습니다. 오래된 System.Data.SQLite와는 연결 문자열도 타입 처리도 호환되지 않는 별개이며, 예를 들어 Guid는 System.Data.SQLite 기본값에서는 BLOB, Microsoft.Data.Sqlite에서는 TEXT로 저장됩니다. 웹상의 샘플이 어느 쪽 전제인지 항상 확인하세요.

SQLite 백업은 파일 복사로 충분한가요?

가동 중인 단순 파일 복사는 금지입니다. 트랜잭션 도중 상태를 섞어 집어낼 수 있고, SQLite 공식도 손상 원인으로 명시합니다. WAL 모드에서는 -wal 파일에 본체에 아직 반영되지 않은 커밋이 들어 있으므로, 본체만 복사하면 최근 데이터가 빠집니다. 올바른 방법은 VACUUM INTO(SQL 한 문으로 일관성 있는 최소 크기 복사본을 만듦) 또는 SqliteConnection.BackupDatabase입니다. 다만 BackupDatabase는 완료될 때까지 다른 연결의 쓰기를 막으므로, 일상적인 세대 백업은 VACUUM INTO가 안전합니다.

SQLite의 대량 INSERT가 느린 이유는 무엇인가요?

커밋 단위가 원인인 경우가 거의 전부입니다. SQLite는 커밋마다 스토리지에 동기 쓰기(fsync)를 하므로, 한 건씩 암시적 커밋으로 INSERT하면 SSD에서도 초당 수백〜수천 건에서 한계에 닿습니다. 같은 INSERT를 1,000건 단위의 명시적 트랜잭션(BeginTransaction으로 감싸고 마지막에 Commit)으로 묶기만 해도 초당 수만〜수십만 건 수준에 이르며, 2〜3자리 빨라집니다. 반대로 트랜잭션을 너무 길게 두면 다른 쓰기를 기다리게 하므로, 수백〜수천 건 또는 수백 밀리초분을 한 커밋으로 두는 것이 실무적인 타협점입니다.

저자 프로필

기사 저자의 프로필 페이지입니다.

Go Komura

합동회사 코무라소프트 대표

Windows 소프트웨어 개발, 기술 상담, 장애 조사를 중심으로 재현이 어려운 장애 조사와 기존 자산이 남아 있는 프로젝트에 강점이 있습니다.