SQLite RETURNING Clause: Retrieving Modified Data

Have you ever inserted or updated rows in an SQLite database and wished you could return the data you just changed without running a separate SELECT query? The RETURNING clause in SQLite allows you to do exactly that.

The RETURNING clause is a powerful SQLite extension that causes INSERT, UPDATE, and DELETE statements to return the values of columns that were modified by the statement, saving you from needing to query the database again to retrieve the changed data.

The RETURNING clause has been supported in SQLite since version 3.35.0 was released in March 2021. It provides functionality similar to the RETURNING clause in PostgreSQL.

Creating a Sample SQLite Table

Before we dive into the details of the RETURNING clause, let’s set up a simple example. Imagine you work at a company that tracks advertising campaign performance. You have a database table that stores information about each ad impression:

CREATE TABLE ad_impressions (
  id INTEGER PRIMARY KEY,
  campaign_id INTEGER,
  impression_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  revenue_cents INTEGER
);

Each row represents a single ad impression event, with columns for:

• id: A unique identifier for the impression
• campaign_id: The ID of the ad campaign
• impression_time: The date and time the impression occurred, defaulting to the current timestamp
• revenue_cents: The revenue amount in cents attributed to the impression

Basic Usage of SQLite RETURNING

Now let’s say your advertising system registers a new ad impression for campaign ID 123 with 10 cents of revenue. To insert this data into the table and retrieve the automatically generated id value and impression_time, you could use an INSERT statement with a RETURNING clause like this:

INSERT INTO ad_impressions (campaign_id, revenue_cents) 
VALUES (123, 10)
RETURNING id, impression_time;

The RETURNING clause specifies the id and impression_time columns so that SQLite will return the values of those columns for the newly inserted row. The result might look something like:

id  impression_time
--  --------------------
1   2023-04-24 09:30:00

This saves you from having to run a separate SELECT query to get the id value and impression_time after inserting the row. The RETURNING clause gives you the data back immediately.

You can also use the special * syntax in the RETURNING clause to return the values of all columns:

INSERT INTO ad_impressions (campaign_id, revenue_cents)
VALUES (123, 50) 
RETURNING *;

This would return:

id  campaign_id  impression_time       revenue_cents
--  -----------  --------------------  -------------
2   123          2023-04-24 09:35:00   50           

Using SQLite RETURNING with UPDATE and DELETE

The RETURNING clause also works with UPDATE and DELETE statements. For example, to update the revenue_cents for the impression with id 2 and return the updated row:

UPDATE ad_impressions
SET revenue_cents = 75
WHERE id = 2
RETURNING *;

Result:

id  campaign_id  impression_time       revenue_cents
--  -----------  --------------------  -------------  
2   123          2023-04-24 09:35:00   75

For a DELETE example, let’s remove all impressions older than 30 days and return the id values of the deleted rows:

DELETE FROM ad_impressions
WHERE impression_time < DATE('now', '-30 days') 
RETURNING id;

If any rows matched the WHERE condition, the RETURNING clause would give you back their id values.

Expressions in SQLite RETURNING

The RETURNING clause accepts a comma-separated list of expressions, not just simple column names. This allows you to apply functions and do calculations.

Extending our ad_impressions example, perhaps you want to convert the returned impression_time to a string format and also calculate the revenue amount in dollars. You could use expressions in RETURNING like:

INSERT INTO ad_impressions(campaign_id, revenue_cents)
VALUES (123, 200)
RETURNING 
  id,
  strftime('%Y-%m-%d', impression_time) AS impression_date,  
  revenue_cents * 0.01 AS revenue_dollars;

The strftime function formats the timestamp as YYYY-MM-DD and the revenue_cents * 0.01 expression divides the cent amount by 100. With the AS keyword, we can assign aliases to the result columns.

This might return:

id  impression_date  revenue_dollars
--  ---------------  ---------------
3   2023-04-24       2.0            

The expressions in RETURNING can refer to columns in the row being inserted, updated or deleted. For INSERT and UPDATE, the expressions see the column values after changing the row. For DELETE, they see the values before the row is removed.

Caveats and Limitations

There are a few things to keep in mind when using the SQLite RETURNING clause:

• It is only available on top-level INSERT, UPDATE, and DELETE statements, not within triggers.
• It does not reflect any changes made by AFTER triggers. It returns the data as seen by the triggering statement.
• It currently doesn’t work with virtual tables, though this may change in future SQLite versions.
• You cannot use RETURNING to feed data into another query or table. The data is only returned to the application.
• Aggregate and window functions are not allowed at the top level of RETURNING expressions, though they can be used in subqueries.
• The order of the returned rows is arbitrary and can change.

Also, be aware that SQLite accumulates the entire RETURNING result in memory while the statement runs. So if you update many rows or return large text/BLOB values, the statement may use substantial temporary memory until it finishes executing.

Conclusion

The SQLite RETURNING clause is a handy way to retrieve freshly inserted, updated, or deleted data without needing a separate SELECT query. While it has a few limitations, it can help simplify your application code when you need to get back auto-generated values or see the final state of modified rows.

Give RETURNING a try the next time you’re changing data in SQLite!