When to use SQLite over key-value storage?

What's really being asked

This question checks two things: architectural judgment about local persistence and procedural knowledge of the sqflite plugin. The interviewer wants to see that you do not default to a cannon for a mosquito. You should know when relational semantics, multi-table joins, or ACID transactions justify SQLite over shared_preferences. They also want to hear the exact bootstrap sequence because opening a database incorrectly leads to race conditions, locked files, or lost migrations in production.

The full answer

First, the scenario. Name a case with structured relational data, such as an offline-first task manager with categories, due dates, and assignees, or a cache that must support complex filtering and sorting. Contrast this with key-value storage, which is fine for booleans, theme modes, or auth tokens. Second, the setup steps in order. Add the sqflite and path_provider packages to pubspec.yaml. Resolve the database file path using getDatabasesPath or path_provider so the file lands in the correct platform directory. Call openDatabase with a version integer and an onCreate callback that executes CREATE TABLE statements. Return a singleton or service-wrapped instance so the database is not reopened on every query. Third, mention migrations. A senior candidate notes that version bumps should trigger onUpgrade to alter schema without dropping user data.

The mistakes people make

A red flag is choosing SQLite for a few primitive settings. That adds build size and complexity for no gain. Another red flag is hardcoding a file path like /data/data/myapp/db.db because it breaks on iOS, macOS, and Windows. Opening the database inside a build method or a StatelessWidget is also wrong; it belongs in a repository or service initialized before the app needs it. Finally, omitting the version parameter or running schema setup outside onCreate leads to tables being recreated on every launch or never created at all.

What usually comes next

The interviewer may ask how you handle migrations when the schema changes. They may also ask how you prevent database locks when reading and writing from multiple isolates, or how you unit-test code that depends on sqflite. Be ready to explain that sqflite is not isolate-safe by default and that heavy operations should be batched or run via a background port. They might also ask about encryption, which would push the conversation to sqflite_sqlcipher.

A concrete example

Suppose you are building an offline expense tracker. A key-value store forces you to serialize every receipt into a JSON blob, making it painful to sum amounts by month or category. SQLite fits because you can create an expenses table with amount, category, and date columns, then run SELECT SUM(amount) GROUP BY category. Setup means adding sqflite and path_provider, computing the path with join(await getDatabasesPath(), 'expenses.db'), calling openDatabase with version 1 and onCreate executing CREATE TABLE expenses(id INTEGER PRIMARY KEY, amount REAL, category TEXT, date TEXT), and wrapping the returned Database instance in a singleton repository class.