Repository navigation
Add SQLite compatibility to wp db commands #234
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 --yesthe 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 worldwp 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.
Activity
schlessera commentedon Jan 5, 2023
@janw-me Currently, a lot of the
wp db *commands do not use the WPDB abstraction, but rather themysqlormysqldumpbinaries, 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.
danielbachhuber commentedon Jan 24, 2023
Removing the
buglabel because WP-CLI doesn't formally support SQLite. It would be cool if it did though![-]wp db doesn't work with SQLite[/-][+]Add SQLite compatibility to `wp db` commands[/+]swissspidy commentedon Oct 22, 2023
wp-cli/wp-cli-testsnow supports running tests against SQLite with the SQLite integration plugin. Usage:WP_CLI_TEST_DBTYPE=sqlite composer behatSo that's a start.
If the command detects
FQDBDIR/FQDB/DB_ENGINEit 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 createwould help unskip a test in the config-command repo, see wp-cli/config-command#168swissspidy commentedon Oct 23, 2023
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 queryorwp db create) run onafter_wp_config_loadand thus only know constants likeDB_NAMEandABSPATHfromwp-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 forFQDBor assume its location in order to access the database.wp db tablesalmost works. There are some extra tables added by the plugin, but that can be caught in tests. Extra args like--scopedon't work. The raw SQL queries for this are inwp-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 sizedoesn't work out of the box. Might require some custom queries that usedbstatif that is available (depends onSQLITE_ENABLE_DBSTAT_VTABcompile flag)wp db prefixorwp db columnsare simple enough that they should just work.The SQLite plugin requires PDO, so we could rely on that too to interact with SQLite
danielbachhuber commentedon Oct 23, 2023
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).
These sound like great Hack Day projects! 😁
swissspidy commentedon Oct 23, 2023
Yeah we can try that. Could be as simple as:
WP_CONTENT_DIR, default towp-contentif not defined. Ditto forWPINCWP_CONTENT_DIR . '/db.php'existsclass-wpdb.phpdb.phpDB_ENGINEdefined &&'sqlite' === DB_ENGINE, use SQLiteMight need to load some more files in (2) because the plugin's drop-in calls functions like
add_action()that might not be defined yet. So need to load the ones that could be needed.compat.phpandplugin.phpfor example.Caveat: what if the drop-in is not actually for SQLite but for something else? What do we do then?
Edit: perhaps just do
WP_CLI::get_runner()->load_wordpress();if SQLite is somehow detected?swissspidy commentedon Jul 24, 2025
We should check if the file defines
SQLITE_DB_DROPIN_VERSION