Add SQLite compatibility to wp db commands by Copilot · Pull Request #299 · wp-cli/db-command

Copilot · 2025-11-20T16:18:52Z

Add SQLite compatibility to `wp db` commands

This PR implements SQLite support for database commands when using the SQLite Database Integration plugin. Commands now detect SQLite via DB_ENGINE constant, SQLITE_DB_DROPIN_VERSION, or db.php drop-in inspection.

Changes

New DB_Command_SQLite trait - Isolated SQLite operations using PDO:
- create/drop/reset - File-based database lifecycle with sqlite3 binary
- query - Direct PDO execution with formatted output (now supports --skip-column-names)
- export/import - SQL dump/restore with proper identifier escaping
- size - File size calculation
- Database path resolution supporting FQDB, FQDBDIR, DB_FILE constants
Modified DB_Command methods - Detect SQLite and route accordingly:
- create, drop, reset, query, export, import - Full SQLite implementation
- size - SQLite file size vs MySQL information_schema query
- check, optimize, repair, cli - Warning messages (not applicable to SQLite)
- tables, prefix, columns, search, clean - Unchanged (work via $wpdb)
Test scenarios - Updated to support SQLite for --skip-column-names flag
Documentation (README.md) - Supported commands, configuration, detection methods

Security Updates

Fixed command injection vulnerabilities: All sqlite3 CLI commands now use Utils\esc_cmd to properly escape arguments
Added sqlite3 binary check: Preflight validation with clear error messages if sqlite3 is not installed

Recent Updates

Added --skip-column-names support for SQLite: The sqlite_query() method now accepts $assoc_args and respects the --skip-column-names flag
Replaced custom display_table() with Formatter class: Using the standard WP-CLI Formatter class for consistency with other commands
Updated tests: Removed MySQL-only restriction from --skip-column-names test as SQLite now supports it

Example Usage

# Works automatically when SQLite is detected
wp db query "SELECT * FROM wp_posts"
wp db query "SELECT * FROM wp_posts" --skip-column-names
wp db export backup.sql
wp db size

Technical Notes

Requires sqlite3 CLI binary for create/drop/reset/export/import operations
PDO used for query execution, enabling functionality at after_wp_config_load stage
SQLite identifiers escaped with double-quotes, values via PDO::quote()
Default database path: wp-content/database/.ht.sqlite
Full backward compatibility - MySQL/MariaDB code paths unchanged
Uses WP-CLI Formatter class for query output formatting
All shell commands properly escaped using Utils\esc_cmd

Fixes #234

✨ Let Copilot coding agent set things up for you — coding agent works faster and does higher quality work when set up for your repo.

Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

codecov · 2025-11-20T17:32:19Z

Codecov Report

❌ Patch coverage is 15.31250% with 271 lines in your changes missing coverage. Please review.

Files with missing lines	Patch %	Lines
src/DB_Command_SQLite.php	4.18%	229 Missing ⚠️
src/DB_Command.php	48.14%	42 Missing ⚠️

📢 Thoughts on this report? Let us know!

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

- Use Utils\esc_cmd to properly escape database path arguments - Add is_sqlite3_available() helper to check for sqlite3 binary - Add preflight check with clear error message if sqlite3 not found - Addresses security concerns about unescaped shell commands Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

Copilot

Pull request overview

Copilot reviewed 17 out of 17 changed files in this pull request and generated 7 comments.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

You can also share your feedback on Copilot code review. Take the survey.

Copilot · 2026-03-12T09:48:53Z

src/DB_Command.php

+					// Don't bother warning for term relationships (which is just 3 int columns) or SQLite.
+				} elseif ( ! preg_match( '/_term_relationships$/', $table ) && ! $this->is_sqlite() ) {
 					WP_CLI::warning( $primary_keys ? "No text columns for table '$table' - skipped." : "No primary key or text columns for table '$table' - skipped." );


In search(), the new condition && ! $this->is_sqlite() is inside the per-table loop. is_sqlite() can hit the filesystem (drop-in inspection), so calling it repeatedly can add avoidable overhead when searching many tables. Cache the result once at the start of search() (e.g. $is_sqlite = $this->is_sqlite();) and use that variable here.

src/DB_Command_SQLite.php

Copilot · 2026-03-12T09:48:54Z

src/DB_Command_SQLite.php

+		if ( ! file_exists( $db_path ) ) {
+			WP_CLI::error( 'Database does not exist.' );
+		}
+


sqlite_export() invokes the sqlite3 CLI but doesn’t perform the same preflight check used by sqlite_create()/sqlite_reset(). If sqlite3 isn’t installed, this will fail later with a generic “Could not export database” error. Please call the sqlite3 availability check up front and emit the clearer error message before attempting any export steps.

Suggested change

if ( ! $this->is_sqlite3_available() ) {

WP_CLI::error( 'The sqlite3 binary could not be found. Please install sqlite3 to use the export command.' );

}

Copilot · 2026-03-12T09:48:54Z

src/DB_Command_SQLite.php

+		$temp_db   = tempnam( sys_get_temp_dir(), 'temp.db' );
+		$export_db = tempnam( sys_get_temp_dir(), 'export.db' );


$export_db is created via < B622 code class="notranslate">tempnam() but never used. This leaves an unnecessary temporary file on disk and makes the export logic harder to follow. Remove this variable (or use it if it was intended for a later step).

Suggested change

$temp_db = tempnam( sys_get_temp_dir(), 'temp.db' );

$export_db = tempnam( sys_get_temp_dir(), 'export.db' );

$temp_db = tempnam( sys_get_temp_dir(), 'temp.db' );

Copilot · 2026-03-12T09:48:54Z

src/DB_Command_SQLite.php

+		$export_db = tempnam( sys_get_temp_dir(), 'export.db' );
+
+		copy( $db_path, $temp_db );


The export temp file creation/copy isn’t checked for failure (tempnam() can return false; copy() can fail). If either fails, subsequent sqlite3 commands will run against an invalid path and produce confusing errors. Please validate these return values and bail with a clear error, cleaning up any created temp files.

Suggested change

$export_db = tempnam( sys_get_temp_dir(), 'export.db' );

copy( $db_path, $temp_db );

if ( false === $temp_db ) {

WP_CLI::error( 'Could not create temporary database file for export.' );

}

$export_db = tempnam( sys_get_temp_dir(), 'export.db' );

if ( false === $export_db ) {

// Clean up any previously created temporary database file.

@unlink( $temp_db );

WP_CLI::error( 'Could not create temporary export file.' );

}

if ( ! @copy( $db_path, $temp_db ) ) {

// Clean up temporary files if the copy operation fails.

@unlink( $temp_db );

@unlink( $export_db );

WP_CLI::error( 'Could not copy database to temporary file for export.' );

}

src/DB_Command_SQLite.php

Copilot · 2026-03-12T09:48:55Z

src/DB_Command_SQLite.php

+
+		$command = Utils\esc_cmd(
+			implode( ' ', array_fill( 0, count( $command_parts ), '%s' ) ),
+			...$command_parts
+		);
+
+		$command .= ' < ' . escapeshellarg( $import_file );
+
+		WP_CLI::debug( "Running shell command: {$command}", 'db' );
+
+		$result = \WP_CLI\Process::create( $command, null, null )->run();
+


The import command uses shell redirection ($command .= ' < ...'). This relies on a shell being available and makes the command line harder to reason about. Prefer using sqlite3’s built-in .read command (passed as an argument) or providing the SQL file contents via the process’ STDIN, so the import works without shell-specific redirection.

Suggested change

$command = Utils\esc_cmd(

implode( ' ', array_fill( 0, count( $command_parts ), '%s' ) ),

...$command_parts

);

$command .= ' < ' . escapeshellarg( $import_file );

WP_CLI::debug( "Running shell command: {$command}", 'db' );

$result = \WP_CLI\Process::create( $command, null, null )->run();

$command_parts[] = '.read';

$command_parts[] = $import_file;

// Build debug-friendly command string without using shell redirection.

$debug_command = Utils\esc_cmd(

implode( ' ', array_fill( 0, count( $command_parts ), '%s' ) ),

...$command_parts

);

WP_CLI::debug( "Running shell command: {$debug_command}", 'db' );

$result = \WP_CLI\Process::create( $command_parts, null, null )->run();

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Initial plan

733e172

Copilot AI assigned Copilot and swissspidy Nov 20, 2025

Copilot started work on behalf of swissspidy November 20, 2025 16:19 View session

Copilot AI and others added 4 commits November 20, 2025 16:30

Add SQLite support trait and integrate into DB_Command

cbcf158

Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

Fix sqlite_reset to not double-call sqlite_create

711e37b

Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

Add SQLite test scenarios and documentation

f7c1165

Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

Improve identifier escaping in SQLite export

0d1fb30

Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

Copilot AI changed the title ~~[WIP] Add SQLite compatibility to wp db commands~~ Add SQLite compatibility to wp db commands Nov 20, 2025

Copilot AI requested a review from swissspidy November 20, 2025 16:41

Copilot finished work on behalf of swissspidy November 20, 2025 16:41

swissspidy added 16 commits November 23, 2025 12:16

Undo readme change

925ed48

Fix formatting

8c4e96f

PHPCS fixes

35a91ba

PHPStan fixes

a89fe31

Some test improvements

19f6813

Remove test

63f7865

Improve some tests

1105c5e

Use gmdate

b3bb681

More test fixes

75f52d2

Fix regex

8eeea43

More fixes

0e1637e

More fixes

1ab8410

Correctly load sqlite drop-in

3d5f055

Update PHPStan config

3638ff2

Fix another test

020b1d1

Merge branch 'main' into copilot/add-sqlite-compatibility

261299f

github-actions bot added command:db command:db-create labels Dec 22, 2025

Adjust some tests

f655661

swissspidy mentioned this pull request Feb 10, 2026

MySQL-compatible WP-CLI export command WordPress/sqlite-database-integration#36

Open

swissspidy added 2 commits February 10, 2026 23:14

more tweaks

e2e8e94

More fixes & tests

3033052

swissspidy requested a review from Copilot February 11, 2026 11:19

Copilot started reviewing on behalf of swissspidy February 11, 2026 11:20 View session

This comment was marked as resolved.

Sign in to view

swissspidy and others added 5 commits February 11, 2026 12:32

Update phpcs.xml.dist

da09918

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Update src/DB_Command_SQLite.php

200ba9e

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Update src/DB_Command_SQLite.php

b532a03

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Update src/DB_Command_SQLite.php

4f9c14a

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Remove extra file

41709cd

Copilot started work on behalf of swissspidy February 11, 2026 11:42 View session

Copilot AI and others added 2 commits February 11, 2026 11:44

Lint fix

28ef391

Copilot finished work on behalf of swissspidy February 11, 2026 11:44

Cleanup

4ee087b

This comment was marked as resolved.

Sign in to view

swissspidy added 3 commits February 11, 2026 13:30

DRY

186fd29

Cleanup

ed01c92

Fix errant method call

f3315fa

swissspidy marked this pull request as ready for review February 11, 2026 13:00

swissspidy requested a review from a team as a code owner February 11, 2026 13:00

Merge branch 'main' into copilot/add-sqlite-compatibility

e1fa540

swissspidy requested a review from Copilot March 12, 2026 09:42

Copilot started reviewing on behalf of swissspidy March 12, 2026 09:43 View session

Copilot AI reviewed Mar 12, 2026

View reviewed changes

swissspidy and others added 2 commits March 13, 2026 22:02

Update src/DB_Command_SQLite.php

0eb080b

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Update src/DB_Command_SQLite.php

e59f2df

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add SQLite compatibility to wp db commands#299

Add SQLite compatibility to wp db commands#299
Copilot wants to merge 50 commits intomainfrom
copilot/add-sqlite-compatibility

Uh oh!

Uh oh!

This comment was marked as resolved.

Uh oh!

This comment was marked as resolved.

This comment was marked as resolved.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

+		if ( ! $this->is_sqlite3_available() ) {
+			WP_CLI::error( 'The sqlite3 binary could not be found. Please install sqlite3 to use the export command.' );
+		}

		$temp_db = tempnam( sys_get_temp_dir(), 'temp.db' );
		$export_db = tempnam( sys_get_temp_dir(), 'export.db' );

		$export_db = tempnam( sys_get_temp_dir(), 'export.db' );

		copy( $db_path, $temp_db );

-		$export_db = tempnam( sys_get_temp_dir(), 'export.db' );
-		copy( $db_path, $temp_db );
+		if ( false === $temp_db ) {
+			WP_CLI::error( 'Could not create temporary database file for export.' );
+		}
+		$export_db = tempnam( sys_get_temp_dir(), 'export.db' );
+		if ( false === $export_db ) {
+			// Clean up any previously created temporary database file.
+			@unlink( $temp_db );
+			WP_CLI::error( 'Could not create temporary export file.' );
+		}
+		if ( ! @copy( $db_path, $temp_db ) ) {
+			// Clean up temporary files if the copy operation fails.
+			@unlink( $temp_db );
+			@unlink( $export_db );
+			WP_CLI::error( 'Could not copy database to temporary file for export.' );
+		}

Conversation

Uh oh!