Daily-It

개발, AI, 인프라, 자동화와 일상 IT 제품 후기를 직접 써보며 정리하는 기술 블로그입니다.

What Is SQLite? Benefits, Limits, and Practical Use of a Serverless File-Based Database

Summary

SQLite is a file-based relational database that operates within the application process without running a separate server. Because of the word “light”, it is easy to misunderstand that it is a simple cache, but it is an actual SQL database with functions such as transactions, indexes, joins, and WAL, and is widely used in mobile apps, desktop apps, embedded equipment, test environments, and small web services. However, aspects such as simultaneous write processing, type rules, foreign key settings, and network file system must be approached differently from server-type DB.

Table of Contents

  1. First, What Is SQLite?
  2. When SQLite Fits Well and When to Avoid It
  3. Core operations: files, transactions, types, WAL
  4. A Basic SQLite Example You Can Run Now
  5. Practical Best Practices
  6. Common Misconceptions and Mistakes
  7. Failure Cases and Fixes
  8. Conclusion
  9. References

First, What Is SQLite?

SQLite sqlite3 The library is directly included in the application and operates. Embedded SQL database engineno see. Rather than connecting to a separate DB server process via TCP like MySQL or PostgreSQL, the application calls the SQLite library within the same process and the data is stored in a single database file.

The official documentation describes SQLite as a “self-contained, serverless, zero-configuration, transactional SQL database engine.” If we interpret this expression from a practical perspective, it is as follows.

Feature Meaning Practical Benefit
Serverless No separate DB server Low deployment, setup, and operation burden
Self-contained Most of the functionality is contained within small libraries Easy to put into mobile/desktop/CLI
Zero-configuration Little to no server setup after installation Great for testing, local development, and sample apps
Transactional ACID transaction support Although it is a file DB, atomic changes can be expected
Public domain public domain license Low burden of use in commercial/personal projects

previously organized Written by WatermelonDBSQLite also appears frequently as internal storage. There are many structures, such as WatermelonDB, Android Room, and iOS Core Data’s SQLite backend, where “users use libraries but leave actual storage to SQLite files.”

When SQLite Fits Well and When to Avoid It

The most important criterion when choosing SQLite is “Does the data flow around one application instance or one device?” SQLite is strong for local databases managed as a single file, and is not suitable for central DB roles where multiple servers handle high write loads simultaneously.

When SQLite is a good fit

  • Local storage for mobile apps
  • Save settings/state for desktop apps, Electron apps, and CLI tools
  • Local data recording of IoT and embedded devices
  • Test DB, demo app, local development environment
  • Small services with read-intensive or low write competition
  • When data needs to be moved as a single file, such as an application file format

In particular, data inside an app often needs to be able to continue to be read and written even when the network connection is lost. At this time, SQLite fits well with the offline-first structure because it has no server dependency and file backup is relatively simple.

When to look at SQLite carefully

  • Structure where multiple server instances want to write to the same DB file simultaneously
  • A central service that consistently experiences many write transactions per second.
  • An environment that requires DB server access control, user permissions, and network access management
  • An environment where you want to place database files on a network file system such as NFS/SMB.
  • Services where complex operational monitoring, replication, and failover are critical

This does not mean that SQLite is a “small and weak DB”. The problem is roles. It is powerful when used as a local file DB, but if you try to replace the central server DB role played by PostgreSQL or MySQL, limitations in locking, backup, operating permissions, and concurrency are quickly revealed.

Core operations: files, transactions, types, WAL

one database file

SQLite databases are usually .sqlite, .db It is saved as one file. Tables, indexes, triggers, and views are included in this file. So, a backup may seem like a simple file copy, but when copying a running DB file, transaction status and WAL files must also be considered.

If you need secure backup during operation, you can use SQLite CLI .backup It is better to use commands or the online backup API.

sqlite3 app.db ".backup 'backup-$(date +%Y%m%d).db'"

Transactions and Concurrency

SQLite supports transactions. By grouping multiple SQL statements into one unit of work, you can rollback if they fail prematurely.

BEGIN IMMEDIATE;

UPDATE accounts
SET balance = balance - 10000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 10000
WHERE id = 2;

COMMIT;

The important thing about concurrency is that Multiple reads are possible at the same time, but writing to the same DB file is basically centered on one writer at a time.The point is. Although WAL mode can reduce read and write conflicts, it does not provide infinite resolution for workloads with very high write contention.

WAL mode

Write-Ahead Logging (WAL) is a method of first logging changes to a separate WAL file. The official documentation explains that WAL can be faster in most scenarios, and provides better concurrency in a way that readers don’t block writers and writers don’t block readers.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;

The above settings are often used in app development, but are not necessarily the correct answer in all environments. WAL next to the database file app.db-wal, app.db-shm You can create files, but caution is required in network file systems or multi-process distribution structures.

Flexible type system and STRICT tables

SQLite is different from PostgreSQL or MySQL, where column types strongly restrict values. As expressed in the official document, SQLite has a type attached to the value itself. dynamic type systemUse . for example INTEGERColumns declared as may also contain different type values ​​depending on the situation.

This flexibility can sometimes be convenient, but in production, you may wonder, “Why is a string in a numeric column?” It can create the same bug. If you need stricter type checking, you can use STRICT You can consider the table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 0),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

It is safe to have the habit of turning on foreign keys for each connection.

SQLite supports foreign key constraints, but as of the official documentation, for reasons of backward compatibility, do not assume the default values ​​and use them on a per-connection basis. PRAGMA foreign_keys = ON;It is a safe habit to specify.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

Whenever you create a new SQLite connection in Node.js, Python, React Native, Android, etc., you need to ensure that this setting is applied. Even if you use an ORM or wrapper library, foreign keys may behave differently than expected if you do not check the actual connection options.

A Basic SQLite Example You Can Run Now

macOS and many Linux distributions have sqlite3 The CLI is already included or can be easily installed with a package manager.

sqlite3 blog.db

Let’s create a simple table and put some data into it.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

INSERT INTO notes (title, body)
VALUES ('SQLite 테스트', '서버 없이 파일 하나로 저장되는 로컬 DB 예제입니다.');

SELECT id, title, created_at
FROM notes
ORDER BY id DESC;

If you want to run it all at once from the CLI, you can use it like this:

sqlite3 blog.db <<'SQL'
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

INSERT INTO notes (title, body)
VALUES ('SQLite 테스트', '서버 없이 파일 하나로 저장되는 로컬 DB 예제입니다.');

SELECT id, title, created_at
FROM notes
ORDER BY id DESC;
SQL

In Python, the standard library sqlite3You can use it alone.

import sqlite3

with sqlite3.connect("blog.db") as conn:
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS notes (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        ) STRICT
    """)
    conn.execute(
        "INSERT INTO notes (title, body) VALUES (?, ?)",
        ("SQLite 테스트", "파라미터 바인딩으로 SQL 인젝션을 피합니다.")
    )
    rows = conn.execute("SELECT id, title FROM notes ORDER BY id DESC").fetchall()

print(rows)

The important part here is string formatting, rather than assembling SQL directly. ? The point is that parameters were used. Just because SQLite is a local DB does not mean that the risk of SQL injection disappears.

Practical Best Practices

1. Clear connection initialization SQL

SQLite has many per-connection settings. Gathering initialization SQL in one place when starting an app or creating a connection can reduce bugs due to differences in environments.

PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;

step, journal_modeis a setting saved in the DB file, and the results may vary depending on the execution environment, so it is better to check the actual return value as well.

PRAGMA journal_mode;

2. Keep write transactions short

In SQLite, long write transactions can cause other write operations to wait. Rather than including slow operations such as file downloads, network calls, and image processing within a transaction, it is recommended to bundle only the minimum section required for DB changes into a transaction.

3. Indexes are created based on query patterns

Index design is also important in SQLite. Consider indexes for columns that are frequently searched or sorted. EXPLAIN QUERY PLANCheck whether it is actually used or not.

CREATE INDEX IF NOT EXISTS idx_notes_created_at
ON notes (created_at DESC);

EXPLAIN QUERY PLAN
SELECT id, title
FROM notes
WHERE created_at >= '2026-01-01'
ORDER BY created_at DESC;

4. The team determines the date/time storage format.

SQLite does not have a separate, dedicated date type. Typically you choose one of the following: an ISO-8601 string, a Unix time integer, or a Julian day number. If the application does not set time zone rules together, confusion can arise in sorting, comparing, and displaying.

In practice, it is easy to understand how to store an ISO-8601 string based on UTC and convert it to the user’s time zone when displayed on the screen.

5. SQLite tools take precedence over file copying for backups

Doing a simple file copy while your app is using the DB may miss WAL files or checkpoint states. Backup during operation .backup Alternatively, take a look at the SQLite Online Backup API first.

sqlite3 app.db ".backup backup.db"
sqlite3 backup.db "PRAGMA integrity_check;"

Common Misconceptions and Mistakes

“SQLite is a toy DB for testing”

no. SQLite is used in a very wide range of applications, including browsers, mobile OSs, apps, and embedded devices. However, the purpose is different from the server-type DB. It is strong in local, embedded, read-oriented, and single file-oriented problems.

“If you turn on WAL, all concurrent write problems will be solved.”

WAL helps reduce reader and writer conflicts. However, there are still limitations in structures where write contention for the same DB file is high. You may need to serialize write operations to a queue, make transactions shorter, or reconsider whether you really need a central DB.

“AUTOINCREMENT must always be added to INTEGER PRIMARY KEY”

SQLite official documentation is AUTOINCREMENTcreates additional CPU, memory, disk, and I/O overhead and should be avoided unless strictly necessary. Most of the time INTEGER PRIMARY KEYJust that is enough. AUTOINCREMENTshould only be reviewed if you have a special need to avoid reusing previously used ROWIDs.

-- 대부분의 경우 충분합니다.
CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- 이전 ROWID 재사용 방지 요구가 있을 때만 신중히 검토합니다.
CREATE TABLE audit_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  message TEXT NOT NULL
);

“Because you declared a foreign key, it is automatically always checked”

Foreign key declaration and actual checking should be viewed separately. per connection PRAGMA foreign_keys = ON;, and also test to ensure that constraint violations are actually prevented.

Failure Cases and Fixes

Case 1. database is locked Error is repeated

symptoms: Intermittently in the app database is locked, SQLITE_BUSY An error occurs. This is especially common when multiple threads or processes are writing simultaneously.

main causes

  • Long write transactions are holding locks for a long time.
  • By connection busy_timeoutis too short or none
  • Multiple writers access the same DB file simultaneously
  • Perform slow operations such as network calls within a transaction
  • Using SQLite files on a network file system

How to check

PRAGMA journal_mode;
PRAGMA busy_timeout;
# macOS/Linux에서 DB 파일을 잡고 있는 프로세스 확인 예시
lsof app.db

solution direction

PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
  • Keep write transactions short.
  • Within an app, consider sequential processing with a write queue.
  • Do not place SQLite files on network shared folders.
  • If multiple servers need to share the same DB file, consider a server-type DB such as PostgreSQL/MySQL rather than SQLite.

Case 2. A foreign key is used, but incorrect data is entered.

symptoms: FOREIGN KEYis declared, but data without a parent row is inserted.

cause: You most likely do not have foreign key checking turned on in your SQLite connection. Based on the official documentation, the foreign key is PRAGMA foreign_keys = ON;It must be activated at runtime via and applied per connection.

confirmation command

PRAGMA foreign_keys;
PRAGMA foreign_key_check;

How to solve

PRAGMA foreign_keys = ON;

In your application code, run an initialization routine every time you create a connection. If you use an ORM, you need to check whether this PRAGMA is applied in the ORM’s SQLite connection options or event hook.

Case 3. A string is entered in a numeric column and the sorting is strange later.

symptoms: INTEGERIt is a column declared as a string value, and the sorting or comparison results are different from expectations.

cause: SQLite’s basic type system is flexible. You should not think that all type errors are strongly prevented just by column declaration.

confirmation command

SELECT id, age, typeof(age)
FROM users
WHERE typeof(age) <> 'integer';

How to solve

  • The new table is STRICT Review the table.
  • Add application validation at the input stage.
  • Existing data before migration typeof()Check with .
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 0)
) STRICT;

Case 4. I copied the backup file, but some data is missing after restoration.

symptoms: In operation .db I copied only the files, but the recent data is not visible in the restored DB.

cause: In WAL mode, the changes are -wal It may remain in the file. Simply copying only the running DB file may not result in a consistent backup.

How to solve

sqlite3 app.db ".backup backup.db"
sqlite3 backup.db "PRAGMA integrity_check;"

Alternatively, your application uses the SQLite Online Backup API. If file copying is absolutely necessary, you must clean up the DB connection and understand the checkpoint status before proceeding.

Conclusion

SQLite is a much more practical database than its description suggests: “a lightweight DB that uses one file without a server.” It significantly reduces installation and operational burden in situations where data must be close to the application, such as local apps, mobile apps, embedded devices, and test environments.

Conversely, if you need a central DB that handles many writes simultaneously from multiple servers, it is safer to choose a server-type DB such as PostgreSQL or MySQL rather than forcefully expanding SQLite. The key to using SQLite well is to accurately determine roles, initialize connection PRAGMA, short transactions, understand WAL, enable foreign keys, and organize backup methods early on.

References