Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 33 ยท Practice and Review

SQLite Interview Questions

26 SQLite interview questions with short answers: dynamic typing, rowid, foreign keys, locking, WAL, limits and how SQLite differs from PostgreSQL and MySQL.

SQLite interviews are a little different from general SQL interviews. The query language is mostly familiar, but interviewers like to probe what makes SQLite unusual: it has no server, it types values per value rather than per column, it keeps foreign keys off by default, and it lets only one connection write at a time. Knowing these facts and being able to explain why they exist is what separates "I have used SQLite" from "I understand SQLite".

This page has 26 questions grouped by topic, each with a short answer you can put in your own words. Try to answer aloud before you open a question. Where a question is easier to understand by running something, an editor follows it: predict the output first, then press Run. The answers link to the lessons that cover each topic in depth.

What interviewers probe

No server A library and one file.
Flexible types Values carry the type, not columns.
One writer Locking and WAL.
Trade-offs When SQLite fits and when it does not.

Be ready to explain the why, not only the what.

1 / 2

Basics and design

1. What does "serverless" mean for SQLite?

There is no separate database process to install, start or connect to over a network. SQLite is a C library that your program links in, and it reads and writes the database file directly. A query is a function call, not a network round trip, which makes it very fast for small and medium workloads and removes configuration and accounts. The price is that there is no network access control, and every program touching the file must be able to reach that file. See What Is SQLite?.

2. Where is the data stored?

In a single ordinary file (plus a temporary journal or -wal file next to it while writing). Tables, indexes, triggers and views all live inside that file, which is why copying it, emailing it or shipping it inside an app is easy. A database can also live entirely in memory (:memory:), which is how the live editors on this site work.

3. Name some places where SQLite is a good fit, and some where it is not.

Good fits: mobile and desktop apps, browsers, embedded devices, test databases, local caches, file formats, small to medium websites, and analysis of a single dataset on one machine. Poor fits: workloads with many machines writing at once, data that must be shared over a network between many clients, very high write concurrency, and databases that need fine-grained user permissions. Even a busy website can run on SQLite when writes are modest and the app runs on one server.

4. How is SQLite different from PostgreSQL and MySQL?
Topic SQLite PostgreSQL / MySQL
Architecture Library, one file Server process, network protocol
Typing Flexible per value (STRICT optional) Strict per column
Writers One at a time Many at once (row-level locking / MVCC)
Users and permissions None; file permissions only Roles and grants
Stored procedures None (triggers only) Yes
Setup and admin Nothing Install, configure, back up, tune
Scale One machine, up to hundreds of GB works well Replication, clustering

Neither is "better": SQLite is the simplest reliable choice when one process or machine owns the data, and the client-server systems win when many clients write concurrently over a network.

5. SQLite is described as "ACID". How does it achieve that with just a file?

Every change happens inside a transaction (an automatic one for a single statement). Before touching the main file, SQLite writes enough information to a rollback journal or a write-ahead log, flushes it to disk, and only then marks the transaction committed. After a crash or power loss, the next connection sees an unfinished journal or log and repairs the database to the last committed state. See Transactions.

Types and values

6. What is dynamic typing in SQLite?

The type belongs to the value, not the column. Every value is one of five storage classes: NULL, INTEGER, REAL, TEXT or BLOB. A column's declared type is only a hint called affinity: it says how SQLite should try to convert values being stored, but it does not forbid any value. You can see the class of any value with typeof(). See Dynamic Typing and Type Affinity.

7. What is type affinity, and how is it chosen?

SQLite looks at the declared type name and picks one of five affinities, in this order: a name containing INT gives INTEGER; one containing CHAR, CLOB or TEXT gives TEXT; BLOB or no type gives BLOB; REAL, FLOA or DOUB gives REAL; anything else gives NUMERIC. This is why VARCHAR(10) does not limit the length, and why BIGINT and INT behave identically. An INTEGER column converts '42' to the integer 42 but keeps 'forty-two' as text.

8. How do you make SQLite enforce column types?

Declare the table with STRICT (SQLite 3.37 and later). Then the allowed types are INT, INTEGER, REAL, TEXT, BLOB and ANY, and a value that cannot be converted without loss is rejected with an error. Combine it with NOT NULL and CHECK constraints for full control. See Constraints and STRICT Tables.

Predict the output of this query, then check the order SQLite uses when one column holds several storage classes:

SQL Code reading: sorting mixed storage classes

Press Run (or Ctrl + Enter) to see the result here.

The order is NULL, then numbers (integers and reals together, compared by value), then text, then blobs. That rule is part of why mixing types in one column gives surprising results.

9. What are the integer division and overflow rules?

Dividing two integers gives an integer (5 / 2 is 2), so make one operand real (5 / 2.0) to get 2.5. Integers are 64-bit; when an integer calculation would overflow, SQLite switches to a real number instead of raising an error, which loses precision. Text that looks like a number is converted in arithmetic ('5' + 0 is the integer 5).

SQL Code reading: arithmetic and comparison across types

Press Run (or Ctrl + Enter) to see the result here.

The two text values compare as text, so '10' < '9' is true (1). A number always sorts before text, so 10 < '9' is also true. The overflowing addition becomes a real number.

10. How do you store dates and booleans?

SQLite has no date or boolean type. Dates are stored as ISO-8601 text (2024-03-21, sorts correctly as text), as Julian day numbers (real) or as Unix timestamps (integer), and the functions date(), time(), datetime(), strftime(), julianday() and unixepoch() work with all three. Booleans are the integers 0 and 1; the keywords TRUE and FALSE are just aliases for 1 and 0. See Dates, Times and Booleans.

11. How does NULL behave?

As in standard SQL, NULL means "unknown", so NULL = NULL is not true. Use IS NULL and IS NOT NULL, or the null-safe IS and IS NOT operators, which treat two NULLs as equal. Aggregates ignore NULLs, sum() of no rows is NULL while total() is 0.0, and coalesce() supplies a fallback.

SQL Code reading: comparing with NULL

Press Run (or Ctrl + Enter) to see the result here.

The first value is NULL (unknown), not 0 and not 1. IS NULL gives 1, NULL IS 1 gives 0, and the empty sum() is NULL while total() gives 0.

Tables, keys and schema

12. What is the rowid?

Every ordinary table has a hidden 64-bit integer key called rowid that the table is physically organized by. Looking up a row by rowid is the fastest possible lookup. If you declare a column INTEGER PRIMARY KEY, that column is the rowid (an alias), so it costs no extra space. Only that exact spelling counts: INT PRIMARY KEY is a separate column and an ordinary index. See Tables, rowid and INTEGER PRIMARY KEY.

13. What does AUTOINCREMENT add to INTEGER PRIMARY KEY?

Without it, a new row gets max(rowid) + 1, so the id of a deleted last row can be reused. With AUTOINCREMENT, SQLite remembers the largest id ever used in the sqlite_sequence table and never reuses one. It costs an extra read and write per insert, so use it only when reusing ids would be harmful (for example when ids are exposed to users or other systems).

SQL Code reading: reusing ids

Press Run (or Ctrl + Enter) to see the result here.

The plain table reuses id 3 for the new row, so its largest id is 3. The AUTOINCREMENT table never reuses it and hands out 4.

14. What is a WITHOUT ROWID table?

A table stored as a B-tree keyed by its declared PRIMARY KEY instead of by a hidden rowid. It can be smaller and faster when the primary key is a text or composite key that you always look up by, because rows are stored once instead of once in the table and again in a separate index. It needs a primary key, has no rowid column and cannot use AUTOINCREMENT.

15. Are foreign keys enforced by default?

No. For historical compatibility, foreign key checking is off for each new connection and you switch it on with PRAGMA foreign_keys = ON. It must be done on every connection (it is not stored in the file), and it cannot be changed inside an open transaction. PRAGMA foreign_key_check lists violations already in the data. See Foreign Keys.

SQL Code reading: an orphan row slips in

Press Run (or Ctrl + Enter) to see the result here.

There is no parent with id 99, yet the insert succeeds and you get 1 orphan, because enforcement is off. After PRAGMA foreign_keys = ON; the same insert fails with FOREIGN KEY constraint failed.

16. What can ALTER TABLE do?

RENAME TABLE, RENAME COLUMN, ADD COLUMN and DROP COLUMN. Dropping fails if the column is part of a primary key, a unique constraint or an index, or is used by a view or trigger. Changing a column's type or constraints is not supported directly: you create a new table, copy the rows, drop the old table and rename the new one, inside a transaction. See Altering Schema Safely.

17. What is the "double-quoted string" quirk?

In standard SQL, double quotes mean an identifier and single quotes mean a string. If SQLite cannot find a column with that name, older settings quietly treat "hello" as the string 'hello', which hides typos in column names. Always write strings with single quotes and identifiers with double quotes, and many builds turn the lenient behavior off.

Querying features

18. What is the difference between LIKE and GLOB?

LIKE uses % and _ as wildcards and is case-insensitive for ASCII letters by default. GLOB uses *, ? and [...] character sets, follows Unix shell rules and is case-sensitive. Neither is a regular expression engine. See Querying in SQLite.

SQL Code reading: LIKE versus GLOB

Press Run (or Ctrl + Enter) to see the result here.

LIKE matches regardless of case (1), GLOB with the wrong case does not match (0), and the two shell-style patterns match (1 and 1).

19. How do upserts work?

INSERT ... ON CONFLICT (column) DO UPDATE SET ... inserts a row, or, when a unique or primary-key constraint is violated, updates the existing row instead. Inside the update, plain column names mean the stored row and excluded.column means the row you tried to insert. It is safer than INSERT OR REPLACE, which deletes the old row first. See UPSERT and RETURNING.

20. Which modern features does SQLite support?

Common table expressions including recursive ones, window functions (3.25), upsert (3.24), RETURNING (3.35), generated columns (3.31), UPDATE ... FROM (3.33), RIGHT and FULL OUTER JOIN (3.39), STRICT tables (3.37) and built-in JSON functions with -> and ->> (3.38, and compiled in by default). It still has no stored procedures, no ALTER COLUMN, and no user management. Features arrive with newer versions, so check sqlite_version() when something is missing.

21. How would you query JSON stored in a column?

Store the JSON as text, then use json_extract(col, '$.path') or the operators col -> '$.path' (returns JSON) and col ->> '$.path' (returns an SQL value). json_each() turns an array or object into rows, and json_group_array() builds an array from rows. To make a JSON field fast to filter, create an index on the expression, or add a generated column and index that. See Generated Columns and JSON.

Concurrency and durability

22. How does locking work in SQLite?

Any number of connections can read at the same time, but only one can write. In the default rollback-journal mode a writer takes escalating locks (shared, reserved, pending, exclusive), and the final exclusive lock blocks readers while changes are copied into the file. A connection that cannot get a lock gets SQLITE_BUSY ("database is locked") unless you set a busy timeout. See Concurrency, Locking and WAL.

23. What is WAL mode, and what is its trade-off?

With PRAGMA journal_mode = WAL, new changes are appended to a separate write-ahead log file and merged into the main file later (a checkpoint). Readers keep reading the last committed snapshot while a writer appends, so readers do not block the writer and the writer does not block readers. There is still only one writer at a time. The trade-offs: WAL needs shared memory so it does not work on network file systems, there are extra -wal and -shm files, and the log can grow if checkpoints are blocked by long-running readers. See PRAGMAs and Tuning.

24. A web app gets "database is locked" errors. What do you do?

First keep write transactions short and never hold one open while waiting for a network call or user input. Set a busy timeout (PRAGMA busy_timeout = 5000 or the driver's equivalent) so connections wait instead of failing at once. Switch to WAL mode. Start write transactions with BEGIN IMMEDIATE so the write lock is taken up front and cannot fail halfway through after a read. If writes still pile up, batch them or move to a client-server database.

25. Why is inserting many rows so much faster inside one transaction?

Outside an explicit transaction, every statement is its own transaction, and each commit forces the data to disk (an fsync), which is slow. Wrapping thousands of inserts in a single BEGIN ... COMMIT pays that cost once. It can turn minutes into a fraction of a second.

Limits

26. What are the practical limits of SQLite?

A database can reach about 281 terabytes, a single text or blob value defaults to at most 1 GB, a table can have up to 2,000 columns by default, a join can involve up to 64 tables, and a statement can have up to 32,766 bound parameters (in modern versions). Those limits are far beyond what most projects need. The real limit is usually concurrency: one writer at a time, and a single machine.

A one-statement upsert is the snippet interviewers ask for most often. Run it and explain each line:

SQL Code reading: counting words with an upsert

Press Run (or Ctrl + Enter) to see the result here.

The statement inserts sql once, then twice more hits the conflict and adds excluded.n (1) each time, giving lite 1 and sql 3. A multi-row insert with an upsert is processed row by row, so repeated keys inside the same statement accumulate.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A column is declared VARCHAR(5) and you insert 'abcdefgh'. What happens in a normal table?

    VARCHAR gives the column TEXT affinity, and SQLite does not enforce the length. Use a CHECK constraint with length() if you need a limit.
  2. 2. Which statement about foreign keys in SQLite is true?

    The setting is per connection and off by default, so an application must enable it every time it opens the database.
  3. 3. In WAL mode, which combination can happen at the same time?

    Readers use the last committed snapshot, so they do not block the single writer, and the writer does not block them.
  4. 4. Why use BEGIN IMMEDIATE for a transaction that reads then writes?

    A deferred transaction starts as a reader and may fail to upgrade to a writer later. IMMEDIATE grabs the write lock immediately, or fails right away.
  5. 5. What does INTEGER PRIMARY KEY give you that INT PRIMARY KEY does not?

    Only the exact type name INTEGER makes the column an alias for the rowid. INT PRIMARY KEY creates an ordinary column with a separate unique index.

Summary

  • SQLite is a serverless library with one database file; it trades network access and write concurrency for simplicity and speed.
  • Types belong to values; affinity only guides conversion, and STRICT tables enforce types.
  • INTEGER PRIMARY KEY is the rowid; AUTOINCREMENT prevents id reuse; WITHOUT ROWID keys the table by its primary key.
  • Foreign keys are off until you run PRAGMA foreign_keys = ON on each connection.
  • One writer at a time: use short transactions, busy_timeout, WAL and BEGIN IMMEDIATE; batch writes in a transaction.
  • Modern SQLite offers CTEs, windows, upsert, RETURNING, generated columns and JSON, but no stored procedures or users.

Next: SQLite Cheat Sheet, everything on one page.