SQLite interview questions covering embedded databases, SQL syntax, indexes, transactions, file storage, mobile apps, and limitations.
SQLite is an embedded relational database engine that stores data in a single ordinary file. It does not require a separate database server process. It is widely used in mobile apps, desktop apps, browsers, local caches, edge devices, tests, and small-to-medium applications that need reliable local storage.
SQLite is embedded because the database engine runs inside the application process instead of as a separate server. The application links to the SQLite library and reads or writes the database file directly. This makes deployment simple but changes concurrency and operational tradeoffs.
A SQLite database is stored as one main file on disk, with temporary journal or WAL files depending on mode. This makes backup, copy, and deployment simple. The tradeoff is that file permissions, filesystem reliability, and local locking behavior become very important.
sqlite3 app.db
.tables
.schemaSQLite is a good choice for local-first apps, mobile storage, desktop software, prototypes, tests, embedded systems, read-heavy sites, and small services with modest write concurrency. It is also useful when operational simplicity matters more than distributed database features.
SQLite is not ideal for high write concurrency, many independent network clients writing at once, large multi-tenant server databases, complex role management, or workloads requiring built-in replication and server-side administration. It can scale surprisingly far, but its single-writer design must match the workload.
Manifest typing means the type is associated with the stored value, not strictly with the column. A column can have a type affinity, but SQLite may store values of different storage classes unless stricter rules are used. This gives flexibility but can surprise developers coming from stricter databases.
Type affinity is SQLite recommended storage preference for a column, such as INTEGER, TEXT, REAL, NUMERIC, or BLOB. SQLite tries to convert inserted values based on affinity, but it is more permissive than many relational databases.
STRICT tables enforce tighter type rules than normal SQLite tables. They are useful when you want SQLite simplicity but fewer surprises from flexible typing. Strict tables can make application bugs easier to catch early.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
age INTEGER
) STRICT;Most SQLite tables have a hidden rowid column that uniquely identifies rows. An INTEGER PRIMARY KEY column aliases the rowid. rowid can make lookups efficient, but relying on hidden rowid behavior without understanding it can cause portability and schema-design issues.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL
);A WITHOUT ROWID table stores rows using the declared primary key instead of a hidden rowid. It can be useful for tables with natural composite primary keys, but it is not automatically better. Test it for the specific schema and access pattern.
CREATE TABLE enrollments (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
PRIMARY KEY (student_id, course_id)
) WITHOUT ROWID;Indexes speed lookups, joins, ordering, and filtering by storing searchable structures separate from table data. They improve reads but cost storage and slow writes. Use EXPLAIN QUERY PLAN to verify whether SQLite uses an index.
CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@example.com';A covering index contains all columns needed by a query, so SQLite can answer from the index without reading the table rows. This can improve performance for read-heavy queries.
CREATE INDEX idx_orders_customer_date_total
ON orders(customer_id, created_at, total);
SELECT created_at, total
FROM orders
WHERE customer_id = 42;A partial index indexes only rows that satisfy a WHERE clause. It is useful for active rows, non-deleted rows, queued jobs, or status-specific queries. It keeps indexes smaller when only a subset is queried often.
CREATE INDEX idx_tasks_open
ON tasks(created_at)
WHERE status = 'open';SQLite supports foreign keys, but enforcement must be enabled with PRAGMA foreign_keys = ON for each connection unless the application or driver enables it. This is a common interview gotcha.
PRAGMA foreign_keys = ON;
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id)
);Transactions group statements so they commit or roll back together. They improve correctness and can greatly improve bulk insert performance because SQLite avoids committing each row separately.
BEGIN;
INSERT INTO logs(message) VALUES ('one');
INSERT INTO logs(message) VALUES ('two');
COMMIT;SQLite is ACID-compliant when used correctly. Atomicity and durability depend on journal mode, synchronous settings, filesystem behavior, and power-loss conditions. Interview answers should avoid saying "it is just a file" as if it has no transaction safety.
WAL, or Write-Ahead Logging, writes changes to a WAL file before checkpointing them into the main database. WAL allows readers and one writer to work more smoothly together and often improves concurrency. It creates extra -wal and -shm files.
PRAGMA journal_mode = WAL;Rollback journal mode stores original pages in a journal before modifying the database. If a crash happens, SQLite uses the journal to roll back incomplete changes. It is older and broadly compatible, but WAL is often better for concurrent readers.
SQLite allows many concurrent readers but only one writer at a time. Locking happens around the database file and journal/WAL behavior. Applications with frequent writes should use short transactions, busy timeouts, and careful connection handling.
The single-writer limitation means only one write transaction can commit changes at a time. This is fine for many local and moderate workloads, but can become a bottleneck for server apps with many concurrent writes.
busy_timeout tells SQLite how long to wait for a locked database before returning SQLITE_BUSY. It helps when short write transactions occasionally overlap. It does not fix long-running write locks or poor transaction design.
PRAGMA busy_timeout = 5000;Prepared statements compile SQL once and bind values safely. They improve performance for repeated statements and prevent SQL injection when parameters are used correctly. Never concatenate untrusted input into SQL strings.
SELECT * FROM users WHERE email = ?;Parameter binding passes values separately from SQL text. SQLite supports placeholders such as ?, ?1, :name, @name, and $name. Binding values lets SQLite treat user input as data, not executable SQL.
INSERT INTO users(email, name) VALUES (:email, :name);PRAGMA commands inspect or change SQLite database settings, such as foreign key enforcement, journal mode, synchronous mode, cache size, and integrity checks. Some PRAGMAs are connection-specific, so applications must set them consistently.
PRAGMA foreign_keys;
PRAGMA journal_mode;
PRAGMA integrity_check;VACUUM rebuilds the database file, removes free pages, and can reduce file size. It can require extra disk space and may take time for large databases. Use it thoughtfully rather than after every delete.
VACUUM;ANALYZE gathers statistics that help the query planner choose better plans. It is useful after large data changes or when indexes are added. Query planner quality depends partly on accurate statistics.
ANALYZE;EXPLAIN QUERY PLAN shows how SQLite intends to execute a query, including whether it scans a table or uses an index. It is the first tool to check when a query is unexpectedly slow.
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;FTS5 is SQLite full-text search extension. It creates virtual tables optimized for text search. It is useful for notes, messages, docs, product search, and local app search without a separate search server.
CREATE VIRTUAL TABLE docs USING fts5(title, body);
SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';SQLite JSON functions query and manipulate JSON text. They are useful for flexible attributes and app payloads. Use generated columns or expression indexes when JSON fields are queried frequently.
SELECT json_extract(profile, '$.email') AS email
FROM users
WHERE json_extract(profile, '$.active') = 1;An in-memory database stores data in RAM instead of a disk file. It is useful for tests, temporary processing, and small ephemeral datasets. Data disappears when the connection closes unless shared-cache patterns are used carefully.
sqlite3 :memory:ATTACH DATABASE lets one SQLite connection access multiple database files. It is useful for migrations, imports, exports, and separating logical datasets. Transactions across attached databases have limitations depending on journal mode and storage.
ATTACH DATABASE 'archive.db' AS archive;
SELECT * FROM archive.old_orders;The backup API copies a database safely while it may be in use. It is better than blindly copying a live database file, especially when WAL files may also be involved. Many language bindings expose this API.
In WAL mode, recent changes may live in the -wal file before checkpointing into the main database file. Copying only the .db file can produce an incomplete backup. Use the backup API, VACUUM INTO, or copy the correct set of files only when the database is safely closed or checkpointed.
VACUUM INTO 'backup.db';SQLite supports ALTER TABLE for common changes, but complex migrations often require creating a new table, copying data, dropping the old table, and renaming. Always run migrations in transactions when possible and keep a schema version.
PRAGMA user_version = 2;user_version is an integer stored in the database header. Applications use it to track schema version and decide which migrations to run. It is simple and useful for mobile and desktop apps.
SQLite is widely used in mobile apps for offline data, local caches, sync queues, user preferences, and structured app data. Mobile apps must handle migrations, encryption needs, background sync, corruption recovery, and storage limits.
Desktop apps use SQLite for local documents, settings, history, catalogs, embedded analytics, and offline-first storage. It avoids requiring users to install a separate database server and makes backup as simple as managing files.
Write contention occurs when multiple writers try to write at the same time. Long transactions, slow disk, chatty writes, and background sync can worsen it. Use short transactions, batching, WAL mode, and a single write queue when needed.
SQLite supports concurrent readers. In WAL mode, readers usually do not block a writer and a writer does not block readers in the same way as rollback journal mode. This makes WAL a common choice for read-heavy local apps.
Use reliable storage, avoid copying only the main file while WAL is active, keep SQLite updated, avoid unsafe filesystem/network mounts, use transactions, run integrity checks, and maintain backups. Most corruption comes from environment misuse rather than normal SQLite operation.
PRAGMA integrity_check;SQLite does not encrypt database files by default. Use platform encryption, SQLCipher, SQLite SEE, application-level encryption, or encrypted storage depending on requirements. Interview answers should mention key management, not only encryption library choice.
The query planner chooses how to execute SQL, including join order and index usage. It relies on schema, indexes, constraints, and statistics. Use EXPLAIN QUERY PLAN and ANALYZE to understand and improve choices.
Open connections when needed, close them cleanly, configure PRAGMAs consistently, avoid sharing one connection unsafely across threads, and keep write transactions short. Connection behavior depends on the application framework and language binding.
Generated columns compute values from expressions. They can be virtual or stored and can be indexed. They are useful for extracting JSON fields, normalized values, or derived data while keeping queries simple.
CREATE TABLE users (
profile TEXT,
email TEXT GENERATED ALWAYS AS (json_extract(profile, '$.email')) VIRTUAL
);
CREATE INDEX idx_users_email ON users(email);CHECK constraints enforce rules on column or row values. They are useful for data integrity, especially because SQLite typing is flexible. Use constraints to prevent invalid values from reaching application logic.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
price REAL NOT NULL CHECK (price >= 0)
);ON CONFLICT controls what happens when uniqueness or constraint conflicts occur. It supports patterns such as DO NOTHING and DO UPDATE for UPSERT behavior.
INSERT INTO users(email, name)
VALUES ('a@example.com', 'Asha')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;Common mistakes include inserting many rows without a transaction, missing indexes, using leading-wildcard LIKE searches, keeping write transactions open too long, using KEYS-like full scans in app code, ignoring EXPLAIN QUERY PLAN, and storing huge blobs without considering file growth.
Common mistakes include string-concatenated SQL, assuming local files need no protection, failing to encrypt sensitive mobile data, weak file permissions, trusting imported database files, and forgetting that backups may contain sensitive data too.
Common anti-patterns include using SQLite as a high-concurrency server database, disabling foreign keys accidentally, relying on loose typing when strict data matters, copying live WAL databases incorrectly, running VACUUM too often, and using one database file for unrelated high-write workloads.
A strong SQLite schema uses clear primary keys, foreign keys, constraints, useful indexes, and PRAGMA settings. Explain the access patterns, migration plan, and why SQLite concurrency fits the workload.
PRAGMA foreign_keys = ON;
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE notes (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
body TEXT,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_notes_user_updated ON notes(user_id, updated_at DESC);Explore 500+ free tutorials across 20+ languages and frameworks.