Question 1

Is SQLite suitable for production server-side apps in 2026?

Accepted Answer

Yes, for the right workloads. SQLite + WAL + Litestream is a popular stack for small-to-medium production apps (think under ~100 writes/sec, read-heavy, single-region). For multi-writer high-throughput SaaS, Postgres is still the boring correct choice. The 'edge SQLite' platforms (Cloudflare D1, Turso) make SQLite viable for new categories like globally-distributed read-heavy apps.

Question 2

How much does a developer working with SQLite earn in India?

Accepted Answer

₹5-16 LPA in 2026, depending heavily on the surrounding stack. Mobile developers (Android/iOS) using SQLite via Room or Core Data are at the lower end (₹5-12 LPA), edge/serverless backend roles using D1 or Turso command ₹12-22 LPA, and senior infra roles working on database internals or replication can go higher.

Question 3

Which companies in India use SQLite heavily?

Accepted Answer

Every mobile dev team in India uses SQLite implicitly via Room/Core Data — so PhonePe, Paytm, Razorpay, Swiggy, Zomato, CRED, Hotstar, Dream11 all rely on it. Newer fintech / wallet apps use offline-first SQLite explicitly. On the backend / edge side, teams using Cloudflare Workers or Turso are early adopters.

Question 4

Should I use SQLite or DuckDB for analytics?

Accepted Answer

DuckDB. They look similar (single-file, embedded, SQL) but they are optimised for different workloads — SQLite is row-oriented and tuned for OLTP (many small reads/writes), DuckDB is column-oriented and tuned for OLAP (big scans, aggregations). For 'fast ad-hoc queries over a CSV/Parquet file', DuckDB is dramatically faster.

Question 5

What's the maximum size of a SQLite database?

Accepted Answer

281 terabytes in theory. In practice, single SQLite files comfortably handle hundreds of GB. Performance starts to depend on disk IO above ~10GB if your working set doesn't fit in the page cache. Tune `cache_size` and use proper indexes — most 'SQLite is slow at scale' complaints turn out to be missing indexes or unprepared statements rather than fundamental size limits.

Question 6

Can I use SQLite from multiple programming languages on the same file?

Accepted Answer

Yes — the on-disk format is stable, well-documented, and platform-independent. A Python script can write to a file an iOS app reads, which a Go service can then back up. This is one of SQLite's biggest portability wins. Just make sure all clients agree on the journal mode (WAL or rollback-journal) — switching mid-flight can confuse things.

Question 7

What is SQLite and how is it different from databases like Postgres or MySQL?

Accepted Answer

SQLite is a self-contained, serverless, zero-configuration SQL database engine. The entire database — schema, data, indexes, triggers — lives in a single ordinary file on disk. Unlike Postgres or MySQL, there is no separate server process; SQLite is a library that your application links against and calls directly. That means there is no network round trip, no authentication layer, no user management, and no daemon to keep running. Your reads and writes are just function calls that operate on a file. This makes SQLite ideal for embedded use cases (mobile apps, browsers, IoT devices) and for any application where the data only ever needs to be accessed by a single process at a time. The trade-off: SQLite intentionally does not solve the problems that client/server databases solve. There is no high-concurrency parallel-write story, no built-in replication, no role-based permissions. For an iOS app storing local user data, this is irrelevant — the OS already isolates the file. For a multi-tenant SaaS handling thousands of concurrent writes, you would pick Postgres instead. Roughly: SQLite is to Postgres what an in-process cache is to Redis — same data shapes, completely different operational model.

Question 8

How do you create a database and a table in SQLite?

Accepted Answer

You do not 'create' a database the way you would in Postgres — opening a file path that does not exist creates an empty database file automatically. From there, you run standard `CREATE TABLE` DDL. The CLI (`sqlite3`) is shipped with most operating systems.

Question 9

What data types does SQLite support?

Accepted Answer

SQLite has only five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Notably, there is no native BOOLEAN, no DATE, no TIMESTAMP, no UUID, and no JSON type as a storage class. Booleans are stored as 0/1 INTEGERs. Dates are stored as TEXT (ISO-8601), REAL (Julian day), or INTEGER (Unix epoch) and you pick the convention. SQLite also uses 'type affinity' rather than strict typing — a column declared as `INTEGER` will happily store a TEXT value unless you opt into the STRICT mode (added in 3.37). This dynamic typing is one of the biggest surprises for developers coming from Postgres.

Question 10

What is type affinity in SQLite and why does it matter?

Accepted Answer

Type affinity is SQLite's loose typing system. A column has an 'affinity' (TEXT, NUMERIC, INTEGER, REAL, or BLOB) that influences but does not force the type of values you can insert. For example, a column declared `age INTEGER` will accept `INSERT INTO t (age) VALUES ('twenty')` without error — SQLite stores the string verbatim. This is fine for prototypes but a real bug magnet in production: you can write `WHERE age > 18` and the comparison silently returns nothing when `age` is a TEXT value like 'twenty'. The fix: declare the table with `STRICT` (SQLite 3.37+) which enforces declared types and raises an error on bad inserts.

Question 11

What is the INTEGER PRIMARY KEY in SQLite and why is it special?

Accepted Answer

Declaring a column `INTEGER PRIMARY KEY` makes it an alias for the table's built-in `rowid` — the internal 64-bit row identifier that every SQLite table has by default. This is faster than a normal primary key because no separate index is needed. If you also add `AUTOINCREMENT`, SQLite guarantees never to reuse a rowid even after deletes (it uses the `sqlite_sequence` table to track the high-water mark). Most ORM-generated schemas omit `AUTOINCREMENT` for speed, since reusing rowids is harmless for the vast majority of apps. The keyword `AUTOINCREMENT` in SQLite is also case-sensitive in older docs but accepted case-insensitively by the parser.

Question 12

How do transactions work in SQLite?

Accepted Answer

SQLite is fully ACID-compliant. You wrap statements with `BEGIN` / `COMMIT` (or `ROLLBACK` on error). If you don't explicitly open a transaction, every statement is implicitly its own transaction — which is correct but extremely slow for bulk inserts. Wrapping 10,000 inserts in a single transaction takes milliseconds; the same inserts without a transaction can take minutes because each one forces an fsync. This is the #1 SQLite performance gotcha and the most common 'SQLite is slow!' complaint on forums turns out to be exactly this. SQLite supports three transaction modes: DEFERRED (default — lock acquired lazily when first write happens), IMMEDIATE (write lock acquired right at BEGIN, avoids deadlock-like races), and EXCLUSIVE (locks the entire DB so no other readers/writers either). For concurrent-writer apps, BEGIN IMMEDIATE is strongly recommended to avoid the upgrade-deadlock pattern.

Question 13

How do you create and use indexes in SQLite?

Accepted Answer

Indexes are created the standard way (`CREATE INDEX ... ON table(col)`). SQLite's query planner is decent but smaller in scope than Postgres's — there is no parallel scan, no bitmap index scan, and no cost-based join reordering for complex queries. Use `EXPLAIN QUERY PLAN` to see which index a query is using. Partial indexes (`WHERE` clause on the index) and expression indexes are both supported and very useful: a partial index on `WHERE deleted_at IS NULL` is far smaller than a full index when most rows are deleted.

Question 14

How do you import and export data in SQLite?

Accepted Answer

The CLI supports `.import` for CSV and TSV files. For export, `.dump` produces a SQL script that recreates the entire database. Both work without leaving the `sqlite3` shell. For binary backups, use `VACUUM INTO 'backup.db'` or the C-level Online Backup API — these produce consistent snapshots even while the DB is being written.

Question 15

What is the .sqlite_master table?

Accepted Answer

`sqlite_master` (also exposed as `sqlite_schema` in modern versions) is the system catalog — a table SQLite maintains automatically that describes every other table, index, view, and trigger in the database. You can query it like a normal table to introspect a schema, find all tables containing a column, or list every index on a table. This is how ORMs and migration tools discover the current schema.

Question 16

How do you enforce foreign keys in SQLite?

Accepted Answer

Foreign keys exist in SQLite but are NOT enforced by default — declaring `REFERENCES other_table(id)` parses cleanly but ignores violations unless you turn enforcement on with `PRAGMA foreign_keys = ON`. This pragma is per-connection and resets on every new connection, so most application code runs it as the first statement after opening the DB. Forgetting this is one of the most common SQLite bugs — your schema looks correct but the constraints are silently disabled.

SQLite Interview Questions

What is SQLite and how is it different from databases like Postgres or MySQL?

How do you create a database and a table in SQLite?

What data types does SQLite support?

What is type affinity in SQLite and why does it matter?

What is the INTEGER PRIMARY KEY in SQLite and why is it special?

How do transactions work in SQLite?

How do you create and use indexes in SQLite?

How do you import and export data in SQLite?

What is the .sqlite_master table?

How do you enforce foreign keys in SQLite?

What are some common PRAGMAs every SQLite user should know?

How does SQLite compare with NoSQL stores like LocalStorage or AsyncStorage on mobile?

What is WAL mode in SQLite and why should you use it?

What concurrency model does SQLite use and where does it break down?

What is VACUUM in SQLite and when do you need it?

How does FTS (Full-Text Search) work in SQLite?

How do you work with JSON in SQLite?

What is a prepared statement and why should you use them?

What is better-sqlite3 and how does it differ from node-sqlite3?

How do you implement database migrations in SQLite?

What is Cloudflare D1 and how does it relate to SQLite?

What is Litestream and how does it provide SQLite replication?

What is libSQL and Turso and why are they significant?

How do you handle 'database is locked' errors in SQLite?

How do you back up a SQLite database safely while it's in use?

When does SQLite outperform Postgres or MySQL, and when should you choose differently?

How would you architect a high-throughput SQLite write workload (10,000+ writes/sec)?

How does SQLite implement ACID, and what trade-offs are involved with synchronous PRAGMA values?

How do you implement multi-tenancy on SQLite?

How would you build an offline-first mobile app with SQLite syncing to a server in India?

Companies Hiring SQLite

Salary Insights

Frequently Asked Questions