Top 60 SQLite Interview Questions (2026)

SQLite is a self-contained, serverless SQL database engine that runs inside your application as a library and stores the whole database in a single file. There's no separate server process, no configuration, and nothing to install.

That's the key difference from MySQL or PostgreSQL, which run as separate servers you connect to over a network. SQLite reads and writes a local file directly, which makes it the default choice for embedded devices, mobile apps, browsers, and testing.

SQLite fits when the database lives on the same machine as the app: mobile and desktop apps, embedded devices, local caches, small-to-medium websites, testing, and as an application file format. The official docs even suggest it as a replacement for ad hoc data files.

It's a poor fit when you need many machines writing to one database over a network, very high write concurrency, or per-user access control on the server side. Those are jobs for PostgreSQL or MySQL.

Use CREATE TABLE with column names and their declared types. SQLite is relaxed about types, but you still declare them because they set each column's type affinity.

You can add constraints inline: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK. IF NOT EXISTS avoids an error if the table already exists.

SQLite stores every value in one of five storage classes: NULL, INTEGER, REAL (floating point), TEXT, and BLOB (raw bytes). That's it at the storage level.

You can write DECLARE columns as VARCHAR, DATETIME, BOOLEAN, and so on, but SQLite maps those names to one of the five storage classes through type affinity. There's no separate boolean or date type; booleans are 0 and 1, and dates are TEXT, REAL, or INTEGER by convention.

Type affinity is the recommended storage class SQLite tries to use for a column, based on its declared type. A column with TEXT affinity prefers to store values as text; a column with INTEGER affinity converts number-looking text to integers when it can.

The point is that SQLite doesn't strictly enforce the declared type. You can put a string in an INTEGER column and it'll accept it. Affinity guides conversion, not rejection, which surprises people coming from strict databases.

A primary key uniquely identifies each row and can't be null or duplicated. In SQLite, declaring a column INTEGER PRIMARY KEY does something extra: that column becomes an alias for the table's built-in rowid.

That alias makes lookups by that key very fast, because the rowid is how rows are physically stored. If you leave it out on insert, SQLite fills it in automatically with the next integer.

Every ordinary table has a hidden 64-bit integer column called rowid that uniquely identifies each row and determines how rows are stored on disk. You can refer to it as rowid, _rowid_, or oid.

If you declare a column as INTEGER PRIMARY KEY, it becomes an alias for the rowid instead of a separate column. Tables created WITHOUT ROWID don't have one; those use their declared primary key for storage instead.

SELECT picks columns, FROM names the table, WHERE filters rows, ORDER BY sorts, and LIMIT caps how many rows come back. This is standard SQL and works the same in SQLite.

SELECT * returns all columns, but naming columns explicitly is better in real code because it's clear and survives schema changes.

INSERT adds rows, UPDATE changes existing rows, DELETE removes them. UPDATE and DELETE take a WHERE clause to target specific rows; leaving WHERE off changes or deletes every row, which is a classic accident.

You can insert multiple rows in one statement with several value tuples, which is faster than many single inserts.

WHERE keeps only rows where its condition is true. You combine conditions with AND and OR, test ranges with BETWEEN, test membership with IN, and match text patterns with LIKE.

In LIKE, % matches any sequence of characters and _ matches a single character. LIKE is case-insensitive for ASCII text by default in SQLite.

ORDER BY sorts the result; ASC is ascending (the default) and DESC is descending. You can sort by several columns, and each can have its own direction.

LIMIT caps the number of rows returned, and OFFSET skips rows before returning, which is the basic pattern for pagination. Sorting always applies before the limit is taken.

Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MIN, and MAX. GROUP BY splits rows into groups first, then the aggregate runs once per group.

Filter groups with HAVING, not WHERE. WHERE runs before grouping on individual rows; HAVING runs after, on the aggregated results.

WHERE filters individual rows before any grouping happens, so it can't use aggregate functions. HAVING filters groups after GROUP BY has run, so it can test COUNT, SUM, and the like.

A quick rule: if the condition is about a single row's column, use WHERE. If it's about an aggregate over a group, use HAVING. Using both in one query is common and correct.

A JOIN combines rows from two tables based on a related column. An INNER JOIN returns only rows that have a match in both tables. A LEFT JOIN returns every row from the left table, filling in NULLs where the right table has no match.

SQLite supports INNER, LEFT (OUTER), and CROSS joins. It doesn't have a native RIGHT or FULL OUTER JOIN in older versions, though recent versions added them.

DISTINCT removes duplicate rows from a result, keeping one copy of each unique combination of the selected columns. It applies to the whole selected row, not a single column.

It's handy for finding the unique set of values in a column, but it isn't free: SQLite has to sort or hash the rows to detect duplicates, so on large results it costs time.

NULL means unknown or missing, not zero or empty string. Any arithmetic or comparison with NULL usually yields NULL, so x = NULL is never true. You test with IS NULL and IS NOT NULL instead.

Aggregate functions like COUNT(column) skip NULLs, while COUNT(*) counts every row. Use COALESCE or IFNULL to substitute a fallback value when a column might be NULL.

An index is a sorted data structure (a B-tree) that lets SQLite find rows by a column's value without scanning the whole table. Without one, a lookup on a non-key column reads every row.

You add an index to speed up WHERE filters, JOIN conditions, and ORDER BY on columns you query often. The cost is extra storage and slower writes, since every insert and update must maintain the index too.

A UNIQUE constraint says no two rows may share the same value in that column (or combination of columns). SQLite enforces it by creating an index behind the scenes and rejecting inserts that would duplicate.

NULLs are treated as distinct, so a UNIQUE column can hold multiple NULL values. A primary key is like UNIQUE plus NOT NULL.

DEFAULT supplies a value when an insert doesn't provide one for that column, so you don't have to specify every field. CHECK validates a condition on each row and rejects inserts or updates that fail it.

Together they push simple rules into the schema itself, so bad data can't get in regardless of which code path wrote it.

Run sqlite3 followed by a filename to open (or create) that database. Inside, you type SQL statements ending in a semicolon, and dot-commands that configure the shell, like .tables to list tables and .schema to see definitions.

.mode and .headers make output readable, .import loads a CSV, and .quit exits. The shell is the fastest way to practice, which is why interviewers assume you've touched it.

A foreign key links a column in one table to the primary key of another, so a row can reference a valid parent row. It's how you model relationships like an order belonging to a user.

SQLite defines foreign keys but doesn't enforce them by default, for backward compatibility. You turn enforcement on per connection with PRAGMA foreign_keys = ON, and you have to do it every time you connect.

Yes, SQLite is fully ACID: transactions are atomic, consistent, isolated, and durable even across crashes and power loss. You start one with BEGIN, commit with COMMIT, and undo with ROLLBACK. Everything between BEGIN and COMMIT either all happens or none of it does.

By default each statement runs in its own automatic transaction (autocommit). Wrapping many statements in one explicit transaction is also much faster, because SQLite writes to disk once at commit instead of per statement.

SQLite allows many readers at once but only one writer at a time across the entire database. A write takes a lock that blocks other writers until it commits. In the default rollback-journal mode, an active writer also blocks readers.

This is fine for most apps because writes are usually brief. It becomes a bottleneck only under heavy concurrent writes, which is when a client-server database fits better. WAL mode relaxes the reader-writer conflict.

The default rollback journal writes the original page contents to a separate journal file so a failed transaction can be undone; during a write, readers are blocked. WAL (Write-Ahead Logging) instead appends new changes to a WAL file and leaves the main database untouched until a checkpoint.

The payoff is concurrency: in WAL mode, readers see the last committed state and don't block the single writer, and the writer doesn't block readers. You enable it once with PRAGMA journal_mode = WAL, and it persists in the database file.

PRAGMA is SQLite's mechanism for reading and changing engine settings and internal state, things a normal SQL statement can't touch. Some PRAGMAs configure a connection, others query metadata.

The ones worth knowing: foreign_keys (turn enforcement on), journal_mode (switch to WAL), synchronous (durability versus speed), cache_size, table_info (inspect columns), and integrity_check (verify the file).

An index is a B-tree keyed on one or more columns, storing the sorted key plus the rowid to find the full row. SQLite's query planner uses an index when the WHERE, JOIN, or ORDER BY can be satisfied by walking the sorted keys instead of scanning every row.

It won't use an index if the column is wrapped in a function, if a leading wildcard defeats it (LIKE '%x'), or if the planner estimates a full scan is cheaper. EXPLAIN QUERY PLAN tells you which path it chose.

A composite index covers several columns in a defined order. SQLite can use it for queries that filter on a leftmost prefix of those columns: an index on (a, b, c) helps queries on a, on a and b, or all three, but not on b alone.

So order matters a lot. Put the most selective or most frequently filtered column first, and match the index order to how your queries actually filter and sort.

A covering index is one that includes every column a query needs, so SQLite answers the query from the index alone and never touches the table rows. That skips the extra step of looking up each row by rowid.

You get one by adding the queried columns to the index. It trades disk space for read speed and is a common tuning move for hot read paths.

A subquery is a SELECT nested inside another statement. It can appear in WHERE (often with IN or EXISTS), in the FROM clause as a derived table, or in the SELECT list to produce a single value per row.

Correlated subqueries reference the outer query and run once per outer row, which can be slow; a JOIN often does the same work faster. EXISTS is usually the right choice for 'does a matching row exist' checks.

A CTE is a named temporary result defined with WITH at the top of a query, then referenced by name in the main statement. It makes complex queries readable by naming intermediate steps instead of nesting subqueries.

SQLite also supports recursive CTEs (WITH RECURSIVE), which walk hierarchies or generate sequences by referencing themselves. That's how you traverse a tree or produce a range of numbers in pure SQL.

Yes, since version 3.25 (2018). Window functions compute a value across a set of rows related to the current row without collapsing them the way GROUP BY does. You use OVER with PARTITION BY and ORDER BY to define the window.

They cover ranking (ROW_NUMBER, RANK), running totals (SUM as a window), and lag/lead comparisons. They're the clean way to answer 'top N per group' questions.

SQLite supports UPSERT with ON CONFLICT ... DO UPDATE (since 3.24) and the older INSERT OR REPLACE. ON CONFLICT lets you insert a row, and if it collides with a unique constraint, update the existing row instead.

Prefer ON CONFLICT DO UPDATE for real upserts, because INSERT OR REPLACE deletes the conflicting row and re-inserts, which can fire triggers and reset the rowid unexpectedly.

A view is a saved SELECT that you query like a table. It stores no data itself; each query against it runs the underlying SELECT. Views simplify repeated complex queries and hide join logic.

Views in SQLite are read-only by default; you can't INSERT, UPDATE, or DELETE through one directly. To make a view writable, you attach INSTEAD OF triggers that translate the operation to the base tables.

A trigger is SQL that runs automatically in response to an INSERT, UPDATE, or DELETE on a table. You choose BEFORE, AFTER, or INSTEAD OF, and inside the trigger you can reference the affected row as NEW and OLD.

Common uses: maintaining an audit log, updating a denormalized count, or enforcing a rule that a CHECK can't express. Keep them simple, because logic hidden in triggers is easy to forget when debugging.

SQLite has no dedicated date or time type. You store dates as TEXT in ISO 8601 format ('2026-07-04 10:30:00'), as a REAL Julian day number, or as an INTEGER Unix timestamp. Then built-in functions do the math.

date(), time(), datetime(), julianday(), and strftime() parse and format these. Storing ISO 8601 text is the common choice because it sorts correctly as a string and stays readable.

ATTACH DATABASE connects another SQLite file to your current connection under a schema name, so you can query and join across both in one statement. DETACH disconnects it.

This is how SQLite does cross-database queries without a server: you reference tables as schema_name.table_name. It's useful for copying data between files or splitting a large database into pieces.

VACUUM rebuilds the database file to reclaim space left by deleted rows and to defragment pages. Over time, deletes and updates leave free pages inside the file that don't shrink it; VACUUM compacts everything into a fresh, smaller file.

It can also apply certain settings that only take effect on a rebuild. VACUUM needs free disk space (it writes a temporary copy) and takes an exclusive lock, so you run it during quiet periods.

Use parameterized queries (bind parameters) instead of building SQL strings with user input. You put placeholders like ? or :name in the SQL and pass the values separately, so the input can never be parsed as SQL.

Every SQLite binding library supports this. Concatenating user input into a query string is the classic injection hole; parameters close it and often run faster because the statement can be prepared once and reused.

Run EXPLAIN QUERY PLAN in front of the query. It shows whether SQLite scans the full table (SCAN) or uses an index (SEARCH ... USING INDEX), plus how it handles joins and sorting.

A SCAN on a large table in a hot query usually means a missing index. From there you add the right index, restructure the WHERE so the planner can use one, or run ANALYZE so the planner has fresh statistics.

Because a database is one file, copying it looks trivial, but copying while writes are in flight can produce a corrupt copy. The safe options are the .backup command in the sqlite3 shell, the online backup API, or VACUUM INTO, all of which take a consistent snapshot.

For a text backup, .dump writes the whole database as SQL statements you can replay. In WAL mode, remember the -wal file holds recent changes, so a plain file copy alone can miss them.

SQLite turns your SQL into a small program that a virtual machine runs. The stages are: tokenize and parse the SQL, run the query planner to pick indexes and join order, compile that plan into bytecode for the VDBE (the virtual database engine), then execute the bytecode which reads pages and returns rows.

Understanding these stages explains why preparing a statement once and reusing it is faster (parse and plan happen once) and why EXPLAIN shows bytecode while EXPLAIN QUERY PLAN shows the high-level access path.

A SQLite file is a sequence of fixed-size pages (default 4096 bytes). Page 1 holds the 100-byte header and the schema. Tables and indexes are each stored as a B-tree, and the file is essentially a set of these B-trees plus free-page and pointer-map bookkeeping.

The file format is stable and cross-platform: the same file works on any OS and CPU architecture, big-endian or little-endian. That stability is why SQLite files are used as a long-term application file format.

A normal table stores rows in a B-tree keyed by rowid, with the primary key (if not INTEGER) held in a separate index. A WITHOUT ROWID table stores rows directly in a B-tree keyed by the declared primary key, removing the extra rowid layer.

Use it when the primary key is not an integer and you look up by it often, or for tables with many small rows, since it saves the space and indirection of a separate rowid. The trade-off: no rowid, and the primary key must be explicitly provided.

A STRICT table (since 3.37) enforces the declared column types instead of using loose type affinity. In a STRICT table, a column declared INTEGER rejects a non-integer value with an error, matching what people expect from other databases.

They exist because type affinity's leniency causes real bugs: a typo puts text in a numeric column silently. STRICT tables restrict column types to INT, INTEGER, REAL, TEXT, BLOB, and ANY, and enforce them.

In rollback-journal mode a connection moves through lock levels: UNLOCKED, SHARED (readers hold this, many at once), RESERVED (a writer intends to write but readers continue), PENDING (writer waiting for readers to finish, blocks new readers), and EXCLUSIVE (writer has sole access to commit).

This escalation is why one writer can prepare a transaction while reads continue, but must reach EXCLUSIVE to actually write pages. WAL mode replaces most of this with a shared-memory index and a single write lock, which is why it's less contentious.

That error (SQLITE_BUSY) means another connection holds a lock your write needs. The first fix is a busy timeout so a blocked connection waits and retries instead of failing instantly: PRAGMA busy_timeout = 5000 or the C-level sqlite3_busy_timeout.

Beyond that, switch to WAL mode to cut reader-writer conflicts, keep write transactions short, avoid holding a read transaction open while trying to write on the same connection, and funnel writes through a single connection or a queue so they serialize cleanly.

In WAL mode, writes append to the -wal file and the main database isn't updated until a checkpoint copies those changes back. Checkpoints happen automatically when the WAL grows past a threshold (default 1000 pages), or you trigger them with PRAGMA wal_checkpoint.

It goes wrong when a long-running reader holds an old snapshot, blocking the checkpoint from advancing, so the WAL keeps growing unbounded. The fixes are ensuring readers finish promptly, tuning wal_autocheckpoint, and running periodic TRUNCATE checkpoints.

synchronous controls how aggressively SQLite calls fsync to flush writes to disk. FULL (the default in rollback mode) fsyncs enough to survive a power loss without corruption. NORMAL fsyncs less often, faster but with a small window where a power loss can lose the last transaction (though not corrupt the file in WAL mode). OFF skips fsync entirely, fastest and least safe.

In WAL mode, NORMAL is a common and reasonable choice: it's much faster and still can't corrupt the database, it can only lose the very latest commits on a hard crash. Picking the level is a durability-versus-throughput decision you should make deliberately.

The hard limits are generous: a database can reach 281 terabytes, rows and blobs go up to about 2 GB, and there's no practical row-count ceiling. The real limit is concurrency, not size. One writer at a time means write-heavy, multi-user workloads bottleneck long before you hit any size cap.

So SQLite scales fine for read-heavy sites and single-writer services with large data, and poorly for many concurrent writers. The answer to 'can it handle a big app' is 'depends on the write pattern, not the data size'.

Start from access pattern and write concurrency, not size. If the database is local to one process or one machine and writes are moderate or single-writer, SQLite removes an entire operational layer: no server to run, secure, back up, or scale. If many machines must write concurrently, or you need role-based network access and horizontal scaling, use PostgreSQL or MySQL.

Also weigh migration cost: SQLite is a fine default that you can outgrow, and libraries plus SQL are portable enough that starting on SQLite and moving later is a reasonable path for many products.

SQLite ships FTS5, a virtual-table extension for fast full-text search. You create an FTS5 table over the text columns, insert content into it, then query with MATCH, which uses an inverted index instead of scanning with LIKE.

FTS5 supports phrase and prefix queries, ranking with bm25(), and tokenizers for different languages. It's the right tool when LIKE '%term%' is too slow, since a leading wildcard can't use a normal index at all.

SQLite has built-in JSON functions (the JSON1 features, now part of the core). You store JSON as TEXT and use functions like json_extract, json_object, json_array, and the -> and ->> operators to read and build it. Recent versions add a binary JSONB format for faster processing.

This lets you keep semi-structured data in a column and query into it, and you can even index a computed JSON path with an expression index. It's not a document database, but it covers the common 'a few flexible fields' case well.

A generated column's value is computed from an expression over other columns, not stored directly by inserts. VIRTUAL generated columns compute on read and cost no storage; STORED ones are computed on write and saved, trading space for read speed.

They're useful for deriving a normalized value once (a lowercased email, a total, a JSON field pulled out) and then indexing it, so queries filter on the derived value with an index behind them.

Preparing a statement runs the expensive front end once: tokenize, parse, and compile to VDBE bytecode. Reusing that prepared statement with different bound values skips straight to execution, so a loop of thousands of inserts avoids re-planning every iteration.

The safety benefit (no injection) and the speed benefit come from the same mechanism: the SQL The technical sequence is fixed at prepare time and only the values change. Pair prepared statements with a single wrapping transaction for the biggest bulk-write speedup.

The common causes are external: a broken or lying fsync on the storage layer, copying the file mid-write, a filesystem that doesn't honor locks (some network filesystems), killing the process during a write with fsync disabled, or two processes writing through separate faulty locking. SQLite's own engine is very reliable; corruption almost always traces to the environment.

Detect it with PRAGMA integrity_check. Recover by exporting what you can with .dump or .recover into a fresh database, and prevent it with WAL mode, correct fsync, avoiding network filesystems for concurrent access, and safe backup methods rather than raw file copies.

Wrap the whole batch in one transaction so SQLite commits (and fsyncs) once instead of per row, which is the single biggest win. Use a prepared statement with bound parameters and executemany-style batching. Optionally set PRAGMA synchronous = NORMAL and journal_mode = WAL for the load.

Further gains: drop non-essential indexes before the load and recreate them after (building an index once beats maintaining it per insert), and raise the cache size. On a fresh empty database, these together turn minutes into seconds.

Open the special filename ':memory:' and the entire database lives in RAM, gone when the connection closes. It's very fast and ideal for unit tests, temporary computation, and caching, where persistence isn't needed.

The catch: it's per-connection by default, so two connections each get their own empty in-memory database. To share one in-memory database across connections in a process, use a named shared-cache URI. Tests love in-memory databases because each run starts clean with no file cleanup.

SQLite's ALTER TABLE is limited: it supports ADD COLUMN, RENAME TABLE, RENAME COLUMN, and DROP COLUMN, but not arbitrary changes like altering a column type or dropping a constraint. The official pattern for those is the twelve-step migration: create a new table with the desired shape, copy data over, drop the old, rename the new, all inside a transaction.

Track schema version with PRAGMA user_version (a free integer stored in the file) or a migrations table, and run migrations in order at startup. Tools like the migration systems in ORMs automate the table-rebuild dance.

SQLite is thread-safe and compiles by default in serialized mode, where it's safe to use across threads. But the safe pattern is one connection per thread rather than sharing a single connection across threads, because sharing serializes access and invites subtle bugs.

There are three threading modes: single-thread, multi-thread (a connection per thread, no sharing one connection), and serialized (full safety). Most bindings default to serialized. For real write concurrency you still hit the one-writer rule, so threads help reads more than writes.

SQLite source code is in the public domain, not merely open source. Anyone can use, modify, and ship it for any purpose, commercial or not, with no attribution or license file required. That removes legal friction entirely, which is a real reason it's embedded in so many products.

The project also maintains extremely high test coverage (millions of test cases, including full branch and MC/DC coverage) and long-term support commitments, which is why safety-sensitive and long-lived systems trust it. There's a paid extension and support offering, but the core is free.