The .changes dot command in SQLite is a helpful utility for developers working with databases, especially when tracking the number of rows affected by SQL statements like INSERT, UPDATE or DELETE.

When used in the SQLite command-line interface, .changes toggles an option that shows the count of modified rows after executing a command.

This article looks at how the .changes command works, how it can be enabled or disabled, and provides an example to illustrate how it works.

What is the .changes Command?

In SQLite, the .changes command is a toggle that, when enabled, displays the number of rows affected by the most recent SQL command. It also displays the total number of rows that have been modified in the current connection.

This can be particularly useful for tracking the impact of modifications on the database and ensuring that operations are affecting the intended records. The .changes command is specific to the SQLite command-line shell and is not an SQL statement itself—it is a “dot command,” used solely within SQLite’s interactive shell environment.

Purpose and Use Cases for .changes

The .changes command can be used to:

• Verify Updates and Deletions: It can help to confirm that the correct number of rows have been updated or deleted. For example, if an UPDATE command is supposed to modify five records but .changes shows a different number, it signals that something might have gone wrong.
• Monitor Insertions: When inserting data, especially with bulk operations, the .changes command can provide quick feedback on how many rows were successfully inserted.
• Debugging and Validation: For debugging purposes, .changes can be helpful when testing SQL commands and scripts, as it quickly provides feedback on the number of rows impacted.

How to Use the .changes Command

The .changes command works as a toggle switch within the SQLite command-line interface. When enabled, the number of affected rows from the most recent INSERT, UPDATE, or DELETE operation is displayed whenever a query is run. This includes subsequent queries that don’t affect data (such as SELECT statements). For these queries that don’t change data, .changes simply repeats the numbers from the most recent operation that changed rows.

Executing .changes ON enables this command, while .changes OFF disables it.

Syntax

.changes [ON | OFF]

If no parameter is provided, the .changes command shows its usage (i.e. Usage: .changes on|off).

Enabling and Disabling .changes

To enable .changes, simply type:

.changes ON

To disable .changes so that the number of rows affected is no longer displayed, use:

.changes OFF

Once enabled, SQLite will display the number of rows impacted by any subsequent INSERT, UPDATE, or DELETE commands until it is disabled again.

Example of Using .changes

Let’s look at an example that illustrates the use of .changes. Assume we have a table named employees and we insert four rows:

-- Create table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    position TEXT,
    salary REAL
);

-- Insert data
INSERT INTO employees (name, position, salary) VALUES ('Bryce', 'Manager', 75000);
INSERT INTO employees (name, position, salary) VALUES ('Ally', 'Developer', 65000);
INSERT INTO employees (name, position, salary) VALUES ('Sasha', 'Designer', 60000);
INSERT INTO employees (name, position, salary) VALUES ('Hesham', 'Designer', 55000);

Step 1: Enable .changes

First, we enable the .changes command:

.changes ON

Step 2: Execute an Update Statement

Next, we run an UPDATE statement to modify the salary of employees with the position “Developer”:

UPDATE employees SET salary = salary * 1.10 WHERE position = 'Designer';

Here’s the output I got on my system:

changes: 2   total_changes: 143

This output tells us that two rows were affected by the UPDATE statement, meaning two employees with the position “Designer” had their salary updated. The total_changes reflects how many rows have been changed in my current connection. So if I change one more row, it will display 144, and so on.

Step 3: Execute a Delete Statement

Suppose we want to delete all employees whose salary is below 65000:

DELETE FROM employees WHERE salary < 65000;

Output:

changes: 1   total_changes: 144

This output tells us that one row was deleted from the table. We can also see that total_changes has incremented to 144 as expected.

Step 4: Run a Read-Only Query

The output of .changes persists, even on queries that don’t change data. On such queries, it simply maintains the value based on the most recently changed rows.

We can run the following query to illustrate this:

SELECT * FROM employees;

Output:

+----+-------+-----------+---------+
| id | name  | position  | salary  |
+----+-------+-----------+---------+
| 1  | Bryce | Manager   | 75000.0 |
| 2  | Ally  | Developer | 65000.0 |
| 3  | Sasha | Designer  | 66000.0 |
+----+-------+-----------+---------+
changes: 1   total_changes: 144

We can see that the .changes output is appended to the query results. The query didn’t change any rows, so .changes maintained the output from the previous statement that changed rows (which in our case, was our DELETE example above).

Step 5: Disable .changes

If we no longer want to display the row count after each operation, we can disable .changes with:

.changes OFF

From this point onward, SQLite will stop showing the affected row count data.

Notes

Here are a few things to remember when using the .changes command:

• Only Available in the SQLite CLI: The .changes command is specific to the SQLite CLI and will not work within SQL queries or scripts outside of the command-line shell.
• Similar to changes() Function: SQLite also provides a changes() SQL function, which returns the number of rows modified by the last INSERT, UPDATE, or DELETE statement. However, changes() can be used in SQL queries, whereas .changes is purely for command-line toggling.
• Session-Based: The .changes command only applies to the active SQLite session and does not persist across different SQLite shell sessions.