When should a SQLite column use DEFAULT rather than application-supplied values?

Use DEFAULT when a value is a reliable fallback for every insert that leaves that column out. For example, if new rows should normally start as pending, a schema-level default makes that behavior part of the table definition. SQLite uses the declared default when an INSERT omits the column. [S1]

Use an application-supplied value when the value varies by row or depends on information the application has for that insert. The key decision is whether omission should mean one consistent fallback, not whether a value can be written in SQL or application code.

A minimal illustration:

CREATE TABLE tasks (
  name TEXT,
  status TEXT DEFAULT 'pending'
);

They are different. In an INSERT with a column-name list, a column left out of that list receives its declared default, or NULL if it has no default. If you include status and give it NULL, that is an explicit value, so the default is not used. [S1][S2]

For the example table, compare:

-- status is omitted: SQLite uses 'pending'
INSERT INTO tasks (name) VALUES ('Pack order');

-- status is included and explicitly set to NULL
INSERT INTO tasks (name, status) VALUES ('Check stock', NULL);

-- status is included with a specific value
INSERT INTO tasks (name, status) VALUES ('Ship order', 'in_progress');

The first row gets the fallback; the second retains NULL; the third gets the supplied status. This describes the column-default behavior, not a claim that every possible schema accepts every value.

Only when the application wants the database’s fallback for that row. Omitting status is a clear way to request the declared default. If the application has a deliberate, different status for a particular insert, include the column and send that value instead. [S2]

For example, the application could omit status for an ordinary new task, but explicitly send 'in_progress' when that is the intended initial value. Do not send NULL as a shorthand for “please use the default”: SQLite treats it as the value supplied, not as omission. [S1]

Keeping these cases distinct makes the insert’s intent easier to review: omission means use the fallback; an explicit value means use that value; explicit NULL means supply NULL.

Ask these questions for each column:

1. Should an omitted value always get the same fallback? If yes, consider a column DEFAULT.
2. Does the value vary by row or depend on information available to the application? If yes, supply it for that insert.
3. Should NULL be a real, intentional value? If so, send NULL explicitly; do not expect it to trigger the default.
4. Does your INSERT actually leave the column out, or include it in the column list?
5. Test the three cases—omitted, explicit NULL, and explicit value—in a disposable example before relying on the behavior.

The source descriptions establish how SQLite handles omission and explicit NULL in INSERT statements. They do not determine which fallback makes sense for your application, whether a value is valid under your full schema, or how your application constructs its inserts. Those are design choices to check in your own code and table definition.