SQLite CLI .dump Command Behavior Change and Documentation Clarification

Missing Indexes in .dump Output for Specific Tables

The SQLite CLI .dump command is a powerful tool for exporting database schemas and data into SQL scripts. However, a significant change in its behavior has been observed between SQLite versions 3.30 and 3.35. Specifically, when dumping a single table using the .dump command, the output no longer includes associated indexes, whereas it did in earlier versions. This change has led to confusion among users who rely on the .dump command for database migrations or backups.

For example, consider a database with the following schema:

CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES ( 'aaa' );
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );
CREATE TABLE tbl2 ( b );
INSERT INTO tbl2 VALUES ( 'bbb' );
CREATE INDEX index3 ON tbl2 ( b );
CREATE VIEW tbl3 AS
SELECT a FROM tbl1
UNION ALL
SELECT b FROM tbl2;

In SQLite version 3.30, running .dump tbl1 would produce:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );
COMMIT;

However, in SQLite version 3.35, the same command produces:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
COMMIT;

Notice that the CREATE INDEX statements are missing. This behavior is not explicitly documented in the CLI documentation, leading to user confusion and potential data integrity issues when migrating databases.

Ambiguity in .dump Command Documentation and Behavior

The root cause of this issue lies in the ambiguity of the .dump command’s documentation and its implementation. The built-in help for the .dump command states:

.dump ?TABLE?      Render database content as SQL
  Options:
   --data-only      Output only INSERT statements
   --newlines       Allow unescaped newline characters in output
   --nosys          Omit system tables (ex: "sqlite_stat1")
   --preserve-rowids Include ROWID values in the output
  TABLE is a LIKE pattern for the tables to dump
  Additional LIKE patterns can be given in subsequent arguments

The documentation implies that the .dump command is primarily for dumping tables, with no mention of indexes, views, or triggers. However, the actual behavior of the command has evolved to support dumping other database objects, such as indexes and views, but this functionality is not clearly communicated.

For instance, running .dump tbl1% in SQLite 3.35 includes the CREATE INDEX statements for indexes associated with the tbl1 table:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
COMMIT;

Similarly, running .dump tbl1 tbl1_index1 index2 includes both specified indexes:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );
COMMIT;

This discrepancy between the documented behavior and the actual behavior has led to confusion. Users expecting the .dump command to include all related objects by default are left with incomplete SQL scripts, which can cause issues during database restoration or migration.

Clarifying .dump Command Usage and Enhancing Documentation

To address this issue, the following steps can be taken to clarify the behavior of the .dump command and ensure users can reliably export their database schemas and data:

Understanding the Current Behavior

The .dump command in SQLite 3.35 and later versions requires explicit specification of indexes, views, or triggers if they are to be included in the output. This is a departure from earlier versions, where related objects were included by default when dumping a table. Users must now use LIKE patterns or explicitly list the objects they want to include.

For example:

• To dump a table and its associated indexes, use a pattern like .dump tbl1%.
• To dump specific indexes, list them explicitly: .dump tbl1 tbl1_index1 index2.
• To dump all objects, use .dump without arguments.

Updating Documentation

The documentation for the .dump command should be updated to reflect its current behavior. The following changes are recommended:

1. Replace the term "TABLE" with a more inclusive term such as "OBJECT" or "ENTITY" to indicate that the command can dump tables, indexes, views, and triggers.
2. Provide examples demonstrating how to use LIKE patterns and explicit object names to include indexes, views, and triggers in the output.
3. Clearly state that related objects are not included by default when dumping a single table.

For example, the updated help text could look like this:

.dump ?OBJECT?      Render database content as SQL
  Options:
   --data-only      Output only INSERT statements
   --newlines       Allow unescaped newline characters in output
   --nosys          Omit system tables (ex: "sqlite_stat1")
   --preserve-rowids Include ROWID values in the output
  OBJECT is a LIKE pattern for tables, indexes, views, or triggers to dump
  Additional LIKE patterns can be given in subsequent arguments

Best Practices for Using .dump

To avoid issues when using the .dump command, follow these best practices:

1. Always specify the objects you want to include in the dump. Use patterns or explicit names to ensure all necessary objects are included.
2. Verify the output of the .dump command before using it for migrations or backups. Check for missing indexes, views, or triggers.
3. Use the --data-only option with caution, as it excludes schema definitions and only outputs data.

Example Workflow

Here is an example workflow for dumping a table and its associated indexes:

# Dump the table and its indexes using a pattern
sqlite3 source.db ".dump tbl1%" | sqlite3 dest.db

# Alternatively, list the table and indexes explicitly
sqlite3 source.db ".dump tbl1 tbl1_index1 index2" | sqlite3 dest.db

By following these guidelines and understanding the current behavior of the .dump command, users can avoid issues and ensure their database schemas and data are accurately exported.