SQLite 3.35 makes writes return rows and columns removable

An application often needs the row that a write just created. A migration sometimes needs a table to stop containing a column. SQLite 3.35.0, released on March 12, moves both operations into the SQL engine with RETURNING and ALTER TABLE DROP COLUMN. The release notes also broaden UPSERT and add optional mathematical functions, but the more substantial application change is how much work these two statements can express directly.

The additions have different consequences. RETURNING connects a change with a result set, allowing the application to receive values produced by that change. DROP COLUMN gives a concise name to a schema operation whose dependencies still need validation. One reduces the amount of follow-up querying an application expresses. The other moves responsibility for a migration operation into the database implementation.

I value SQLite's ability to keep useful relational operations inside a local application. These additions make that arrangement more practical without requiring a separate database service. Their exact semantics matter more to me than a broad claim about compatibility with another SQL engine.

A write can produce its own result set

The RETURNING regression tests in the release tag start with a table containing an integer primary key, an ordinary column, and a defaulted column. An insert supplies only the ordinary value and requests all three columns. The expected output includes the assigned key and the default. That is the application-level benefit in a compact form.

For example, the following statement follows the same supported pattern, with different names and values. The table declaration establishes the default, and the write requests the resulting row.

CREATE TABLE job (
  id INTEGER PRIMARY KEY,
  name TEXT,
  status TEXT DEFAULT 'queued'
);
INSERT INTO job(name)
VALUES ('build-report')
RETURNING id, name, status;

The returned values include the generated identifier and queued. The application does not have to reconstruct the database's choice of default in its own code. A later change to the default can remain a schema decision, provided the application continues to accept the returned value.

The tagged tests also cover multiple inserted rows and INSERT DEFAULT VALUES RETURNING *. This is a result-set operation, not a special mechanism that only retrieves one generated key. An application interface that treats every write as yielding only an affected-row count will need a way to consume rows when using this syntax. That follows directly from the statement's output shape.

The tests include INSERT ... SELECT ... RETURNING * as well. That form takes its input rows from another query and returns the resulting inserted values. Consequently, an application's decision to consume returned rows need not depend on whether it constructed a literal VALUES list. Both insertion forms have examples in the released test suite.

Updates and deletes support the clause too. In the release's tests, an update returns the affected identifiers and selected column values. A delete returns values from rows being removed. Those examples distinguish the returned information from a later query against the table, which cannot retrieve a deleted row after the deletion has completed.

UPDATE job
SET status = 'running'
WHERE id = 1
RETURNING id, status;

DELETE FROM job
WHERE id = 1
RETURNING id, name;

These examples are illustrative uses of the release-tested syntax. They do not assert an execution order for a multi-row result or any guarantee about external work the application performs after reading it. The database returns row values. Sending an email or updating another service remains a separate application operation.

UPSERT returns the row it actually changes

The same test file combines ON CONFLICT DO UPDATE with RETURNING. One case attempts to insert a row whose key already exists. The conflict action changes one existing column, and the returned row reflects that update. Another case mixes inserted rows and a conflicting row in one statement. Its expected results include both kinds of outcome.

This combination matters because an attempted insert and its actual effect can differ. Returning the submitted parameters would describe the attempted insert. Returning the affected database row exposes the result of the conflict action, including values the action retained from the existing record. Applications can use that distinction without issuing a second statement solely to discover the resulting values.

The release separately generalizes the conflict syntax. Multiple ON CONFLICT clauses can appear, and SQLite evaluates them in order. A final clause can omit a conflict target while still specifying DO UPDATE. Those changes make conflict handling more expressive, but the order now carries application policy. A reader needs to examine the earlier clauses before interpreting the final catch-all action.

That expressiveness does not justify merging unrelated policies into one difficult statement. A useful UPSERT still makes its uniqueness assumptions and assignments readable. RETURNING helps by making the result explicit, while the conflict clauses retain responsibility for deciding which change happens.

Removing a column still requires a valid schema

The DROP COLUMN tests in the 3.35.0 tag are particularly useful because they include refusals as well as successful changes. A plain three-column table can lose its middle column, then another column. Attempting to remove the final remaining column fails. A view and an FTS5 virtual table also reject this form of column removal.

The tests reject removing a primary-key column or a column declared unique. An index that still names the removed column produces an error. An expression index can create the same dependency even when the column is only one term of an expression. Concise syntax therefore does not mean that SQLite silently removes every dependent schema object.

CREATE TABLE draft (
  id INTEGER PRIMARY KEY,
  title TEXT,
  old_summary TEXT
);
ALTER TABLE draft DROP COLUMN old_summary;

This example removes an ordinary column with no dependent object in the example schema. Adding an index on old_summary would change the migration's prerequisites. The release tests explicitly cover an index left referring to a removed column, so a migration author has concrete evidence that dependencies matter.

Views provide another example. A test creates a view selecting a column from a table and then attempts to drop that column. SQLite rejects the change because the resulting view would refer to a missing column. A separate test shows that an already invalid view can also interfere with the operation. The database validates more than the altered table's short declaration.

Constraints can belong to different parts of a declaration

The distinction between a column constraint and a table constraint becomes visible in the tests. One table declares a CHECK expression at table level referring to the column to be removed. Dropping that column fails because the check would retain the reference. Another table places the check in the removed column's declaration, and dropping that column succeeds.

Both declarations express a similar condition before the migration. Their text belongs to different parts of the schema, which affects what remains after the column definition disappears. This is useful detail for migration tooling. A generator that treats every check as an interchangeable predicate can miss a difference that the database's alteration rules observe.

The test suite also exercises generated columns, both stored and virtual, and tables declared WITHOUT ROWID. These cases establish that the feature is broader than removing the last ordinary field from a simple rowid table. They do not establish that every possible dependency is removable. The surrounding expressions and constraints still determine whether a proposed schema remains valid.

I see the main benefit as reducing the amount of migration behavior that an application must implement itself. The database can expose one operation and reject a resulting invalid schema. That is useful even when a particular migration still needs earlier steps to remove dependencies.

Other changes affect how much work SQLite performs

The release introduces MATERIALIZED and NOT MATERIALIZED hints for common table expressions. Its notes say that CTEs used more than once now default to materialization. This matters to statements that share a common intermediate expression, since the engine's treatment of that expression can affect execution work. It is a separate change from making write results available through RETURNING.

Another change reduces the memory needed by VACUUM when a database contains very large text or blob values. The notes explain that the operation no longer needs to hold an entire such value in memory at once. That is a specific improvement in temporary working memory, not a claim that all SQL operations have become cheaper.

The new mathematical functions also require a compile-time option, SQLITE_ENABLE_MATH_FUNCTIONS. A version number alone does not establish that a particular embedded build includes them. In contrast, the release's SQL tests give direct examples of the statement forms discussed here. For an application adopting 3.35.0, those examples identify the changed interaction clearly, writes can yield affected values, and supported column removals can be expressed as database operations with schema validation.