How do you safely add a non-nullable column in sqflite?

What's really being asked

This question tests whether you understand the lifecycle of a local SQLite database in Flutter and the strict separation between creating a schema for new users versus migrating data for existing users. It also checks your knowledge of SQLite ALTER TABLE semantics, specifically that adding a NOT NULL column to a table that already contains rows is only safe if you supply a DEFAULT value. Interviewers want to see that you think about data durability and versioned, incremental migrations rather than destructive rebuilds.

The full answer

First, increment the database version integer passed to openDatabase so that the framework knows a migration is required. Second, explain that onCreate is only invoked when the database file does not exist; inside it you should execute the full latest CREATE TABLE statement including the new non-nullable column so fresh installs never run migrations. Third, describe onUpgrade as the path for existing users; here you execute an ALTER TABLE ADD COLUMN statement that includes both NOT NULL and a DEFAULT value, which lets SQLite populate existing rows safely without data loss. Fourth, mention wrapping the migration in a batch or transaction so the upgrade is atomic; if any step fails the database rolls back and the app does not end up in a half-migrated state. Fifth, note that you should structure onUpgrade with a switch or cascading if statements on oldVersion so that users jumping multiple versions are handled correctly.

The mistakes people make

A major red flag is editing onCreate to add the column while leaving onUpgrade empty; this works for new installs but crashes existing users because their old schema never gets updated. Another red flag is dropping the existing table and recreating it without first copying data out; that destroys user data. Some candidates suggest adding the column with NOT NULL but omitting DEFAULT; on a non-empty table SQLite rejects this and the app crashes. Finally, failing to bump the version number means neither callback fires and every user stays on the old schema.

What usually comes next

The interviewer might ask how you would handle a migration that is too complex for ALTER TABLE, such as adding a foreign key or splitting a column into two; the correct pattern is create a new table, copy data with transformations, drop the old table, and rename. They may also ask how you test migrations; the robust approach is to keep a set of schema-versioned database files, open them with the new code, and assert the final schema and data. Another follow-up is downgrades; sqflite offers onDowngrade, and the safest default is often onDatabaseDowngradeDelete, though that wipes data so you must warn users.

A concrete example

Imagine a todo app where version 1 has a Task table with id and title. In version 2 you need a priority integer that cannot be null and defaults to zero. You set version to 2. In onCreate you run CREATE TABLE Task id INTEGER PRIMARY KEY, title TEXT, priority INTEGER NOT NULL DEFAULT 0. In onUpgrade when oldVersion is 1 you run ALTER TABLE Task ADD COLUMN priority INTEGER NOT NULL DEFAULT 0 inside a batch commit. Existing tasks all get priority zero, new tasks must supply a value, and no data is lost.