Add SQLite compatibility to wp db commands

Open Copilot opened this issue 3 months ago • 1 comments

Description

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
- query - Direct PDO execution with formatted output
- 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 (features/db-sqlite.feature) - CRUD, query, export/import, warnings
Documentation (README.md) - Supported commands, configuration, detection methods

Example Usage

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

Technical Notes

PDO used for direct operations, 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

Fixes #234

[!WARNING]

Firewall rules blocked me from connecting to one or more addresses (expand for details)

I tried to connect to the following addresses, but was blocked by firewall rules:

https://api.github.com/repos/Behat/Behat/zipball/1b6b08efa995fe4135901b862d112adc7e95ecbb

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/Behat/Gherkin/zipball/05a7459283e8e6af0d46ec25b8bb5960ca3cfa7b

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/PHPCSStandards/PHPCSUtils/zipball/d71128c702c180ca3b27c761b6773f883394f162

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/PHPCSStandards/PHP_CodeSniffer/zipball/0ca86845ce43291e8f5692c7356fccf3bcf02bf4

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/PHPCSStandards/composer-installer/zipball/845eb62303d2ca9b289ef216356568ccc075ffd1

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/Yoast/PHPUnit-Polyfills/zipball/134921bfca9b02d8f374c48381451da1d98402f9

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/bobthecow/mustache.php/zipball/176b6b21d68516dd5107a63ab71b0050e518b7a4

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/composer/pcre/zipball/b2bed4734f0cc156ee1fe9c0da2550420d99a21e

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/composer/semver/zipball/198166618906cb2de69b95d7d47e5fa8aa1b2b95

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/composer/xdebug-handler/zipball/6c1925561632e83d60a44492e0b344cf48ab85ef

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/myclabs/DeepCopy/zipball/07d290f0c47959fd5eed98c95ee5602db07e0b6a

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/nikic/PHP-Parser/zipball/3a454ca033b9e06b63282ce19562e892747449bb

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/phar-io/manifest/zipball/54750ef60c58e43759730615a392c31c80e23176

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/phar-io/version/zipball/4f7fd7836c6f332bb2933569e566a0d6c4cbed74

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/php-fig/event-dispatcher/zipball/dbefd12671e8a14ec7f180cab83036ed26714bb0

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/php-fig/log/zipball/f16e1d5863e37f8d8c2a01719f5b34baa2b714d3

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/php-parallel-lint/PHP-Console-Color/zipball/7adfefd530aa2d7570ba87100a99e2483a543b88

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/php-parallel-lint/PHP-Console-Highlighter/zipball/5b4803384d3303cf8e84141039ef56c8a123138d

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/php-parallel-lint/PHP-Parallel-Lint/zipball/6db563514f27e19595a19f45a4bf757b6401194e

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/php-stubs/wordpress-stubs/zipball/abeb5a8b58fda7ac21f15ee596f302f2959a7114

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/phpstan/extension-installer/zipball/85e90b3942d06b2326fba0403ec24fe912372936

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/phpstan/phpstan/zipball/2770dcdf5078d0b0d53f94317e06affe88419aa8

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/cli-parser/zipball/90f41072d220e5c40df6e8635f5dafba2d9d4d04

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/comparator/zipball/dc904b4bb3ab070865fa4068cd84f3da8b945148

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/complexity/zipball/bad4316aba5303d0221f43f8cee37eb58d384bbb

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/diff/zipball/7ab1ea946c012266ca32390913653d844ecd085f

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/environment/zipball/24a711b5c916efc6d6e62aa65aa2ec98fef77f68

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/exporter/zipball/016951ae10980765e4e7aee491eb288c64e505b7

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/global-state/zipball/ef1377171613d09edd25b7816f05be8313f9115d

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/lines-of-code/zipball/97ffee3bcfb5805568d6af7f0f893678fc076d2f

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/object-enumerator/zipball/1effe8e9b8e068e9ae228e542d5d11b5d16db894

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/object-reflector/zipball/4bfa827c969c98be1e527abd576533293c634f6a

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/php-code-coverage/zipball/67e8aed88f93d0e6e1cb7effe1a2dfc2fee6022c

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/php-file-iterator/zipball/961bc913d42fe24a257bfff826a5068079ac7782

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/php-invoker/zipball/12b54e689b07a25a9b41e57736dfab6ec9ae5406

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/php-text-template/zipball/e1367a453f0eda562eedb4f659e13aa900d66c53

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/php-timer/zipball/f258ce36aa457f3aa3339f9ed4c81fc66dc8c2cc

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/phpunit/zipball/d8f644d8d9bb904867f7a0aeb1bd306e0d966949

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/recursion-context/zipball/0b01998a7d5b1f122911a66bebcb8d46f0c82d8c

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/type/zipball/e549163b9760b8f71f191651d22acf32d56d6d4d

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/sebastianbergmann/version/zipball/3e6ccf7657d4f0a59200564b08cead899313b53c

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/staabm/side-effects-detector/zipball/d8334211a140ce329c13726d4a715adbddd0a163

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/dependency-injection/zipball/98af8bb46c56aedd9dd5a7f0414fc72bf2dcfe69

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/deprecation-contracts/zipball/63afe740e99a13ba87ec199bb07bbdee937a5b62

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/event-dispatcher-contracts/zipball/59eb412e93815df44f05f342958efa9f46b1e586

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/event-dispatcher/zipball/b7dc69e71de420ac04bc9ab830cf3ffebba48191

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/filesystem/zipball/e9bcfd7837928ab656276fe00464092cc9e1826a

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/finder/zipball/9f696d2f1e340484b4683f7853b273abff94421f

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/polyfill-ctype/zipball/a3cc8b044a6ea513310cbd48ef7333b384945638

Triggering command: /usr/bin/php8.3 -n -c /tmp/E3w2Iy /usr/bin/composer install --no-interaction (http block)

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/polyfill-mbstring/zipball/6d857f4d76bd4b343eac26d6b539585d2bc56493

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/polyfill-php73/zipball/0f68c03565dcaaf25a890667542e8bd75fe7e5bb

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/translation-contracts/zipball/65a8bc82080447fae78373aa10f8d13b38338977

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/translation/zipball/ec25870502d0c7072d086e8ffba1420c85965174

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/symfony/yaml/zipball/90208e2fc6f68f613eae7ca25a2458a931b1bacc

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/theseer/tokenizer/zipball/b7489ce515e168639d17feec34b8847c326b0b3c

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/wp-cli/entity-command/zipball/896b7fb5ed51fe556017b2c71126947db5cd2b68

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/wp-cli/php-cli-tools/zipball/f12b650d3738e471baed6dd47982d53c5c0ab1c3

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/wp-cli/spyc/zipball/6aa0b4da69ce9e9a2c8402dab8d43cf32c581cc7

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/wp-cli/wp-cli-tests/zipball/75b660dddaa9f512d104d93da4a0a3ede1183a93

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

https://api.github.com/repos/wp-cli/wp-cli/zipball/2cc5992047c5e7ce1ae745e46ae0ab14352e3da0

Triggering command: /usr/bin/php8.3 -n -c /tmp/ht0gSa /usr/bin/composer install --no-interaction (http block)

If you need me to access, download, or install something from one of these locations, you can either:

Configure Actions setup steps to set up my environment, which run before the firewall is enabled

Add the appropriate URLs or hosts to the custom allowlist in this repository's Copilot coding agent settings (admins only)

Original prompt

This section details on the original issue you should resolve

<issue_title>Add SQLite compatibility to wp db commands</issue_title> <issue_description>Describe the current, buggy behavior

Several wp db * commands don't work with sqlite.

SQLite is in testing fase

Describe how other contributors can replicate this bug

Install a new WP,
wp plugin install performance-lab --activate
In the performance-lab plugin settings activate the sqlite setting.
wp-content/databases/.ht.sqlite should exist.
wp db drop --yes the mysql gets dropped, This is fine for this test to make sure you don't read the mysql. But shouldn't it drop the sqlite file?
Click around in the admin/frontend. The website WordPress still works.
wp post list Shows the hello world
wp db query "SELECT * FROM wp_posts;" Gives an error.
wp db search 'hello' Does work, with a warning.

It appears wp-content/db.php doesn't get loaded. I don't know when/where it should be included. And if it even would help.

Keep in mind, FQDBDIR and FQDB constants can be set to change the location & filename of the sqlite file. </issue_description>

Comments on the Issue (you are @copilot in this section)

@schlessera @janw-me Currently, a lot of the `wp db *` commands do not use the WPDB abstraction, but rather the `mysql` or `mysqldump` binaries, as the DB could be in a non-working state and you still want to have control over it.

To support SQLite, we'd need to find a standardized way to act on the SQLite file through the shell, not the WPDB wrapper.</comment_new> <comment_new>@danielbachhuber

Removing the bug label because WP-CLI doesn't formally support SQLite. It would be cool if it did though!</comment_new> <comment_new>@swissspidy wp-cli/wp-cli-tests now supports running tests against SQLite with the SQLite integration plugin. Usage: WP_CLI_TEST_DBTYPE=sqlite composer behat So that's a start.

If the command detects FQDBDIR / FQDB / DB_ENGINE it could switch to SQLite mode and run raw SQLite queries where needed. And when using $wpdb, the MySQL queries would get translated by the plugin.

Edit: I'm actually running into some infinite loop when running tests right now 🤔 Tests just keep hanging and can't be cancelled.

Edit 2: Fixing wp db create would help unskip a test in the config-command repo, see https://github.com/wp-cli/config-command/pull/168</comment_new> <comment_new>@swissspidy

OK, now that tests are properly running I have a better picture of things. I am not super familiar with the db command though, so please bear with me.

One problem is that most commands (e.g. wp db query or wp db create) run on after_wp_config_load and thus only know constants like DB_NAME and ABSPATH from wp-config.php. Since they are loaded before WordPress, they do not know anything about SQLite. Not sure how to address this one. Maybe we need to manually look for FQDB or assume its location in order to access the database.

wp db tables almost works. There are some extra tables added by the plugin, but that can be caught in tests. Extra args like --scope don't work. The raw SQL queries for this are in wp-cli/php (wp_get_table_names()) and might require changing there. Or maybe this is something that needs to be fixed in the plugin.

wp db size doesn't work out of the box. Might require some custom queries that use dbstat if that is available (depends on SQLITE_ENABLE_DBSTAT_VTAB compile flag)

wp db prefix or wp db columns are simple enough that they should just work.

To support SQLite, we'd need to find a standardized way to act on the SQLite file through the shell, not the WPDB wrapper.

The SQLite plugin requires PDO, so we could rely on that too to interact with SQLite</comment_new> <comment_new>@danielbachhuber

One problem is that most commands (e.g. wp db query or wp db create) run on after_wp_config_load and thus only know constants like DB_NAME and ABSPATH from wp-config.php. Since they are loaded before WordPress, they do not know anything about SQLite. Not sure how to address this one. Maybe we need to manually look for FQDB or assume its location in order to access the database.

For this particular scenario, I think we could replicate WordPress' db loading logic inside of WP-CLI (i.e. assume db drop-in/SQLite given specific conditions).

wp db tables almost works. There are some extra tables added by the plugin, but that can be caught in tests. Extra args like --scope don't work. The raw SQL queries for this are in `wp...

Fixes wp-cli/db-command#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.

Nov 20 '25 16:11 Copilot

Codecov Report

:x: Patch coverage is 13.99417% with 295 lines in your changes missing coverage. Please review.

Files with missing lines	Patch %	Lines
src/DB_Command_SQLite.php	3.80%	253 Missing :warning:
src/DB_Command.php	47.50%	42 Missing :warning:

:loudspeaker: Thoughts on this report? Let us know!

Nov 20 '25 17:11 codecov[bot]