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

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 Report

❌ Patch coverage is 14.76923% with 277 lines in your changes missing coverage. Please review.

Files with missing lines	Patch %	Lines
src/DB_Command_SQLite.php	4.06%	236 Missing ⚠️
src/DB_Command.php	48.10%	41 Missing ⚠️

📢 Thoughts on this report? Let us know!

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

The command_parts array was being passed directly to \WP_CLI\Process::create() which requires a string command. This caused proc_open() to fail with "expects parameter 1 to be string, array given". Also fixes the .read argument: sqlite3 expects ".read FILENAME" as a single quoted argument, not as two separate arguments. Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

sqlite3 .dump produces 'CREATE TABLE' without 'IF NOT EXISTS', so importing the dump back into the same database fails with "table X already exists" and exit code 1. Add a regex replacement to insert 'IF NOT EXISTS' into CREATE TABLE statements from the dump, consistent with the existing handling for CREATE INDEX statements. Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

sqlite3 .dump also outputs CREATE TRIGGER and CREATE VIEW statements without IF NOT EXISTS, causing re-import to fail with "already exists". Extend the transformations to add IF NOT EXISTS to all CREATE statements (TABLE, TRIGGER, VIEW, INDEX, UNIQUE INDEX) using regex, so that re-importing a dump into the same database succeeds. Co-authored-by: swissspidy <841956+swissspidy@users.noreply.github.com>

Hi @swissspidy! I was following wp-cli/search-replace-command#190, and seeing it was recently closed, I discovered this pull request. I only want to make sure that what is routed via $wpdb vs. what is re-implemented makes sense.

My main worry is that bypassing the SQLite driver can lead to data inconsistency and corruption, as the driver maintains information schema and applies MySQL-specific modifications. Looking at the command list:

✅ create, drop, reset: It makes sense to have these custom now. The SQLite driver doesn't support them.
❗import, export: I think these can have the custom variants that work with SQLite files instead of MySQL dumps, but I also think wp db import mysql-dump.sql should work. Perhaps a flag could be used for SQLite? We have the MySQL variants for SQLite in https://github.com/Automattic/wp-cli-sqlite-command/, so maybe there's a chance to consolidate these. Perhaps the SQLite plugin could offer some import/export APIs.
✅ query: Goes through $wpdb, all good. I think that this one should work with the MySQL syntax via $wpdb by default. Using it to directly execute SQLite queries bypasses the driver, which can cause data corruption. A low-level SQLite mode could be opt-in via a flag for those who understand the risks.
❗check, optimize, repair: These are supported by the SQLite driver and should be left to $wpdb.

For commands like cli, we could use a MySQL proxy that exposes the SQLite database via the MySQL binary protocol, but it would add some complexity.

All that said, this mostly applies to the new driver, which is not yet the default in the plugin itself and is only activated with the WP_SQLITE_AST_DRIVER constant. In 3.0.0-rc.1, the legacy driver is fully removed. So I'm thinking, maybe it will make sense to address some of these concerns for >= 3.0?

Another question is where should we keep the SQLite-specific logic. Does it make any sense to ship it with the SQLite plugin? Or do you think it's better to keep the support here, especially considering the fact that more and more statements will work natively via $wpdb?

If anything is missing on the SQLite driver side, we can implement it for 3.0. I think it should be possible to implement commands like CREATE DATABASE and DROP DATABASE. For the size command, I think it's also possible to have the information schema queries return sizes. I can look into that.

Finally, when we know the best path forward, I'm happy to help with these efforts.

Thanks for the ping!

3. ❗query: I think that this one should work with the MySQL syntax via $wpdb by default. Using it to directly execute SQLite queries bypasses the driver, which can cause data corruption. A low-level SQLite mode could be opt-in via a flag for those who understand the risks.

This one already use $wpdb if SQLite is detected, or am I missing something?

So I'm thinking, maybe it will make sense to address some of these concerns for >= 3.0?

Sounds good to me 👍 Is there any rough ETA for 3.0?

Another question is where should we keep the SQLite-specific logic.

Probably have to decide on a case-by-case basis. Generally I'm favorable of relying on the plugin to handle as much as possible (where you see fit of course) to reduce maintenance on our side, especially since it applies to other commands such as search-replace-command as well. Waiting for 3.0 to ship would be convenient for us, as otherwise there's gonna be confusion for users on the latest WP-CLI version but an older SQLite plugin version.

In any case, I'm supportive of improving SQLite support across all WP-CLI commands. Feel free to open specific issues or even PRs for anything in need of fixing.

May I ask why there's both wp db support for sqlite and also wp sqlitefrom https://github.com/Automattic/wp-cli-sqlite-command? Seems like these efforts should be consolidated somewhere...

Yeah that extra command doesn't really make sense nowadays :) They can just adopt this one here.

This one already use $wpdb if SQLite is detected, or am I missing something?

@swissspidy Ah, the PR description says:

query - Direct PDO execution with formatted output (now supports --skip-column-names)

But from the code, it appears that it goes via $wpdb indeed, so this one is good ✅

Sounds good to me 👍 Is there any rough ETA for 3.0?

Hopefully within the next couple of weeks. I'm already tagging RCs, but at the same time, no promises; priorities are always evolving 😆

May I ask why there's both wp db support for sqlite and also wp sqlitefrom https://github.com/Automattic/wp-cli-sqlite-command?

@nickchomey There is currently a significant difference in that wp db export exports a native SQLite dump, while wp sqlite export exports a MySQL-compatible .sql dump. Same for import. The naming looks a bit upside down.

I’d like these to converge so that wp db export has the same semantics for both SQLite and MySQL, and any SQLite-native functionality would be an extra argument or command.

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 Related to 'db' command command:db-create Related to 'db create' command labels Dec 22, 2025

Copilot started work on behalf of swissspidy March 16, 2026 21:47 View session

Remove unused $export_db tempnam() variable in sqlite_export()

5c63df1

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

Copilot AI requested a review from swissspidy March 16, 2026 21:48

Copilot finished work on behalf of swissspidy March 16, 2026 21:48

Update some tests

54be7a7