SQLite is probably the most widely deployed database in the world. It runs inside phones, browsers, desktop apps, IoT devices and countless tools. These SQLite interview questions and answers cover how it works, its data types, transactions, performance and when it is the right choice.
Basics
1. What is SQLite?
SQLite is an embedded, serverless, self-contained SQL database engine. The library runs inside the application, and the whole database lives in a single file. There is no separate server process to install or manage.
2. How is SQLite different from MySQL or PostgreSQL?
| SQLite | MySQL / PostgreSQL |
|---|---|
| Library inside the application | Separate server process |
| One file per database | Many files managed by the server |
| No user accounts or network access | Users, roles and network connections |
| Best for local, embedded or low-concurrency use | Best for many concurrent users |
3. Where is SQLite used?
Android and iOS apps, web browsers, desktop software, embedded devices, local caches, test suites and small websites. It is also a popular file format for exchanging structured data.
4. What are its main advantages?
Zero configuration, a tiny footprint, a single portable file, full ACID transactions and a well-tested codebase. It is also in the public domain.
Data types
5. How does SQLite handle data types?
SQLite uses dynamic typing. Values have a storage class: NULL, INTEGER, REAL, TEXT or BLOB. Columns have a type affinity, which is a preference rather than a strict rule. As a result, a column declared INTEGER can still store text in normal tables.
6. What are STRICT tables?
Since version 3.37, you can create tables with the STRICT keyword. SQLite then enforces column types and rejects values that do not fit.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL) STRICT;7. What is the rowid?
Most tables have a hidden 64-bit integer key called rowid. A column declared INTEGER PRIMARY KEY becomes an alias for it. Adding AUTOINCREMENT prevents reuse of old IDs, but it adds a little overhead, so use it only when needed.
Transactions and concurrency
8. Is SQLite ACID-compliant?
Yes. Transactions are atomic, consistent, isolated and durable, even after a crash or power failure.
9. How does SQLite handle concurrent access?
Many readers can work at once, but only one writer at a time. In the default rollback journal mode, a writer blocks readers during commit. In WAL (write-ahead logging) mode, readers and the writer can work at the same time:
PRAGMA journal_mode=WAL;10. How do you handle “database is locked” errors?
Keep transactions short, enable WAL mode, and set a busy timeout so connections wait instead of failing immediately, for example PRAGMA busy_timeout = 5000;.
Schema and queries
11. Are foreign keys enforced?
Not by default, for backward compatibility. Turn them on for each connection:
PRAGMA foreign_keys = ON;12. What can ALTER TABLE do?
It can rename tables and columns, add columns and, since version 3.35, drop columns. More complex changes need a new table, a data copy and a rename.
13. How do indexes work in SQLite?
Indexes are B-trees that speed up lookups and sorting. Use EXPLAIN QUERY PLAN to check whether a query uses an index.
14. What is VACUUM?
VACUUM rebuilds the database file to reclaim free space and reduce fragmentation.
Operations
15. How do you back up an SQLite database safely?
Use the online backup API, the .backup command in the sqlite3 shell, or VACUUM INTO 'backup.db'. Avoid copying the file while a write is in progress.
16. What are SQLite’s limits?
The maximum database size is very large (about 281 TB), but practical limits come from storage and write concurrency. It is not designed for many simultaneous writers over a network.
17. When should you not use SQLite?
Avoid it for high-concurrency write workloads, client-server applications with many remote users, or when you need fine-grained user permissions. In those cases, choose PostgreSQL or MySQL.
For other databases, see our PostgreSQL interview questions and Redis interview questions.
Keep preparing
Practise each answer out loud, and run the commands in a lab rather than memorising them. Interviewers often ask follow-up questions, so make sure you understand why each answer is true.
Sources
Preparing for an interview? Browse more questions in our interview preparation hub. For businesses, we also offer security services such as VAPT, server hardening and ISO 27001 readiness.
