Update SQLite plugin to 2.2.3 and enable new driver #1506

wojtekn · 2025-06-24T12:26:46Z

Related issues

Fixes STU-586

Proposed Changes

I propose to update the SQLite plugin to 2.2.3 and enable the new driver.

The driver uses constants to enable the new driver, but due to Playground/Studio/WP CLI specifics, the WP CLI command doesn't have access to the constants defined through Playground code. Thus, I used a custom WP CLI parameter to enable the driver explicitly. We can deprecate that parameter when we enable the driver by default.

Release plan:

Merge wp-cli-sqlite-command PR and release new version of library. Any Studio installation will automatically upgrade to the latest version, and the latest version of the command will still work with Studio that uses the SQLite driver 2.1.17-alpha.1
Merge this PR and release a new Studio version. It will upgrade the SQLite driver to 2.2.3 for its sites and will start using AST for the sites.

Testing Instructions

Pull the branch and run npm install
Clone the wp-cli-sqlite-command PR locally (Add support for the new SQLite driver wp-cli-sqlite-command#10)
Run composer install --no-dev --optimize-autoloader --ignore-platform-reqs in the wp-cli-sqlite-command directory
Replace wp-files/sqlite-command in the Studio code with a symlink to your local wp-cli-sqlite-command directory
Apply the patch to the Studio repo:

diff --git a/src/setup-wp-server-files.ts b/src/setup-wp-server-files.ts
index a81b7026..536c14a1 100644
--- a/src/setup-wp-server-files.ts
+++ b/src/setup-wp-server-files.ts
@@ -86,17 +86,11 @@ async function copyBundledWPCLI() {
 
 async function copyBundledSQLiteCommand() {
        const bundledSqliteCommandPath = path.join( getResourcesPath(), 'wp-files', 'sqlite-command' );
-       const bundledSqliteCommandVersion = await getSQLiteCommandVersion( bundledSqliteCommandPath );
-       if ( ! bundledSqliteCommandVersion ) {
-               return;
-       }
+       const bundledSqliteCommandVersion = '1.0.7';
        const installedSqliteCommandPath = getSqliteCommandPath();
        const isSqliteCommandInstalled = await fs.pathExists( installedSqliteCommandPath );
 
-       const installedSqliteCommandVersion = await getSQLiteCommandVersion( installedSqliteCommandPath );
-       const isBundledVersionNewer =
-               installedSqliteCommandVersion &&
-               semver.gt( bundledSqliteCommandVersion, installedSqliteCommandVersion );
+       const isBundledVersionNewer = true;
        if ( ! isSqliteCommandInstalled || isBundledVersionNewer ) {
                console.log( `Copying bundled SQLite command version ${ bundledSqliteCommandVersion }…` );
                await recursiveCopyDirectory( bundledSqliteCommandPath, installedSqliteCommandPath );
@@ -114,5 +108,5 @@ export async function updateWPServerFiles() {
        await updateLatestWordPressVersion();
        await updateLatestSqliteVersion();
        await updateLatestWPCliVersion();
-       await updateLatestSQLiteCommandVersion();
+       //await updateLatestSQLiteCommandVersion();
 }

Remove current command files:

~/Library/Application\ Support/Studio/server-files/sqlite-command

Run npm start. It will copy the symlinked command files to directory under server-files/
Click on the Import / Export tab
Click on the "Export database"
Observe .sql file is created
Open the .sql file and validate that the dump is correct - dump which uses AST driver will have wp_commentmeta as the first dumped table. Confirm that field has types with length set, that ENGINE=InnoDB exists.

In steps 1-5, we are ensuring that Studio uses the development version of the WP CLI SQLite command. Those won't be needed when we merge the release wp-cli-sqlite-command PR and release a new version.

The test cases worth testing:

Studio trunk with SQLite driver 2.1.17-alpha.1 and WP CLI SQLite v1.0.6 - current version, to confirm everything works.
Studio trunk with SQLite driver 2.1.17-alpha.1 and WP CLI SQLite from new-sqlite-driver branch - to confirm current Studio installation will work when we release new WP CLI SQLite and when Studio automatically upgrades it.
Studio update/sqlite-plugin-to-2.2.0 branch with SQLite driver 2.2.3 and WP CLI SQLite from new-sqlite-driver branch - to confirm everything works fine with AST driver

Pre-merge Checklist

Have you checked for TypeScript, React or other console errors?

src/constants.ts

wojtekn · 2025-07-03T10:55:25Z

I tested importing, exporting, and sync for combinations:

trunk with SQLite driver 2.1.17-alpha.1 and wp-cli-sqlite v1.0.6 (current version)
trunk with SQLite driver 2.1.17-alpha.1 and wp-cli-sqlite from new-sqlite-driver branch
update/sqlite-plugin-to-2.2.0 with SQLite driver 2.2.2 and wp-cli-sqlite from new-sqlite-driver branch

The pull action for the last one still fails, but it's being addressed under WordPress/sqlite-database-integration#203. If I manually apply this change to the driver on my site before the pull, the issue with undefined DB_NAME is resolved. However, another problem, Call to a member function get_charset_collate() on null, arises. I'm checking it with @JanJakes .

wojtekn · 2025-07-03T11:03:59Z

vendor/wp-now/src/wp-now.ts


-	const wpConfigConsts = {};
+	const wpConfigConsts = {
+		WP_SQLITE_AST_DRIVER: true,


We set the constant to enable the new driver for the site's WP admin and frontend.

wojtekn · 2025-07-03T11:04:23Z

src/lib/import-export/export/export-database.ts

 	// Execute the command to export directly to the temp file
 	const { stderr, exitCode } = await server.executeWpCliCommand(
-		`sqlite export ${ tempFileName } --require=/tmp/sqlite-command/command.php`,
+		`sqlite export ${ tempFileName } --require=/tmp/sqlite-command/command.php --enable-ast-driver`,


We set the parameter to enable the AST driver for CLI actions.

We probably won't add more commands, but I wonder if we should opt-in to use the AST driver more broadly. Like enable it by default, or auto-append the parameter in the wp-cli-process, similarly to how we increase the timeout:

studio/src/lib/wp-cli-process.ts

Lines 67 to 70 in 48bf68b

const timeout =

args[ 0 ] === 'sqlite' && [ 'import', 'export' ].includes( args[ 1 ] )

? IMPORT_EXPORT_RESPONSE_TIMEOUT

: DEFAULT_RESPONSE_TIMEOUT;

We could, but we should remove this parameter as soon as the SQLite driver enables it by default. What do you think?

Then, let's leave it as it is. There is no risk that we'll introduce more SQLite commands and forget to add the parameter in the meantime. 👍

wojtekn · 2025-07-04T12:06:17Z

Issues covered in previous comment are fixed now.

sejas

I followed the test instructions but I'm getting the DB_NAME error. @wojtekn , do you know if that's expected?

Node error:

Success: Exporting database...
<br />
<b>Fatal error</b>:  Uncaught Error: Undefined constant &quot;Automattic\WP_CLI\SQLite\DB_NAME&quot; in /tmp/sqlite-command/src/SQLiteDriverFactory.php:24

sejas · 2025-07-07T22:40:18Z

src/lib/import-export/export/export-database.ts

 	// Execute the command to export directly to the temp file
 	const { stderr, exitCode } = await server.executeWpCliCommand(
-		`sqlite export ${ tempFileName } --require=/tmp/sqlite-command/command.php`,
+		`sqlite export ${ tempFileName } --require=/tmp/sqlite-command/command.php --enable-ast-driver`,


We probably won't add more commands, but I wonder if we should opt-in to use the AST driver more broadly. Like enable it by default, or auto-append the parameter in the wp-cli-process, similarly to how we increase the timeout:

studio/src/lib/wp-cli-process.ts

Lines 67 to 70 in 48bf68b

const timeout =

args[ 0 ] === 'sqlite' && [ 'import', 'export' ].includes( args[ 1 ] )

? IMPORT_EXPORT_RESPONSE_TIMEOUT

: DEFAULT_RESPONSE_TIMEOUT;

wojtekn · 2025-07-09T16:48:42Z

Thanks for testing it @sejas .

I followed the test instructions but I'm getting the DB_NAME error. @wojtekn , do you know if that's expected?

Could you open this site's SQLite driver in wp-content/mu-plugins/sqlite-database-integration and confirm that readme.txt shows 2.2.3 version and that constants.php file sets DB_NAME if it's not set?

sejas · 2025-07-10T11:49:42Z

src/constants.ts


 // SQLite
-export const SQLITE_DATABASE_INTEGRATION_VERSION = 'v2.1.17-alpha.1';
+export const SQLITE_DATABASE_INTEGRATION_VERSION = 'v2.2.2';


We can use the new version v2.2.3

Suggested change

export const SQLITE_DATABASE_INTEGRATION_VERSION = 'v2.2.2';

export const SQLITE_DATABASE_INTEGRATION_VERSION = 'v2.2.3;

sejas · 2025-07-10T11:51:28Z

src/lib/import-export/export/export-database.ts

 	// Execute the command to export directly to the temp file
 	const { stderr, exitCode } = await server.executeWpCliCommand(
-		`sqlite export ${ tempFileName } --require=/tmp/sqlite-command/command.php`,
+		`sqlite export ${ tempFileName } --require=/tmp/sqlite-command/command.php --enable-ast-driver`,


Then, let's leave it as it is. There is no risk that we'll introduce more SQLite commands and forget to add the parameter in the meantime. 👍

sejas · 2025-07-10T11:53:33Z

Thanks for testing it @sejas .

I followed the test instructions but I'm getting the DB_NAME error. @wojtekn , do you know if that's expected?

Could you open this site's SQLite driver in wp-content/mu-plugins/sqlite-database-integration and confirm that readme.txt shows 2.2.3 version and that constants.php file sets DB_NAME if it's not set?

Actually I have the version 2.2.2. I tried to reproduce the testing steps from scratch and I'm getting another error: Database export failed: �[31;1mError:�[0m Parameter errors: unknown --enable-ast-driver parameter. So I cannot verify the correct behaviour.

Also we'll need to test the push process. I got an error when importing a dump that used the SQLite 2.2.2 without the new parameter in the command.

sejas · 2025-07-10T11:56:36Z

I think we need to add another test instruction at the beginning. We need to run npm install to download the new SQLite plugin.

wojtekn · 2025-07-10T12:11:48Z

@sejas all the instructions were written for 2.2.3, but I haven't pushed the code change for that. I've pushed 2.2.3 now.

I've added an explicit npm install step.

sejas

@wojtekn , I was able to execute the testing steps and export the database without any "errors".
But I see the database dump is almost empty. It has only one line:

-- Dump completed on 2025-07-10T12:15:57+00:00

The file has only 46 Bytes size:

sejas · 2025-07-10T12:42:47Z

After some more testing, the empty dump only happens in one of my sites. I confirmed the site has the 2.2.3 plugin, and removed other plugins like Gutenberg. The site starts correctly in Studio and I can access wp-admin. I'll test the site with the old driver.

The rest of sites export the database correctly and I was able to pull and push to production. 👍

sejas · 2025-07-10T13:14:15Z

On trunk, and after replacing the sqlite plugin to 2.1.17-alpha, the database is exported correctly.

JanJakes · 2025-07-10T13:38:50Z

@sejas Is there a way for me to reproduce that? Perhaps a DB dump? (We can also share it on Slack.)

sejas · 2025-07-10T13:45:20Z

I'll share the database. Let me know if you need the whole site.

JanJakes · 2025-07-11T09:50:58Z

@sejas So I checked this, and for me, the issue seems to be those problems with DB_NAME and after_wp_config_load that we’re discussing lately.

It seems to be like this:

The DB you sent to me seems to already have information schema tables from the new SQLite Driver.
Those information schema tables have DB name value local in them.
If I try to run the without after_wp_config_load, then the DB name will be different (fallback to database_name_here). This will result in the information schema finding no tables.

I think the solution is:

Put back the after_wp_config_load — I just did that. This should fix the export for you.
Fail the export if it cannot be done rather than making it silently empty. PR here.

sejas

I tried this PR with the latest commit Automattic/wp-cli-sqlite-command@957e06a on sqlite command and I confirm it works in all my sites, even the one that was failing.
I also have to say the export database is much faster than the regular driver.

bcotrim

I tested this PR in the context of STU-611 and it seems to be working without issues.
👍

wojtekn · 2025-07-15T13:56:21Z

If I try to run the without after_wp_config_load, then the DB name will be different (fallback to database_name_here). This will result in the information schema finding no tables.

@JanJakes @sejas there may still be a use case for which it won't work - for the site that was imported with a custom database name in wp-config.php before we changed the driver. The database uses database_name_here name, but DB_NAME in config points to a custom name, resulting in an empty dump.

However, the fix for such a case is straightforward - the user should change the database name in the config file to database_name_here. As it would be an edge case, with an easy workaround, we can live with that. Any thoughts?

JanJakes · 2025-07-21T12:05:21Z

@wojtekn

However, the fix for such a case is straightforward - the user should change the database name in the config file to database_name_here. As it would be an edge case, with an easy workaround, we can live with that. Any thoughts?

In SQLite Database Integration 2.2.4, this should fail with Incorrect database name. The database was created with name '', but 'database_name_here' is used in the current session.. The 2.2.4 version was not released yet, but I can tag it any time, if needed.

wojtekn added 3 commits June 24, 2025 14:22

Update SQLite plugin to 2.2.0 and enable new driver

2ac4e0e

Set constant earlier in the flow

d6b934c

Remove unnecessary change

6391677

wojtekn self-assigned this Jun 24, 2025

JanJakes reviewed Jun 26, 2025

View reviewed changes

src/constants.ts Outdated Show resolved Hide resolved

wojtekn added 2 commits June 26, 2025 15:35

Update SQLite plugin version to latest 2.2.2

8e8e528

Enable AST driver for sqlite CLI calls

48bf68b

wojtekn changed the title ~~Update SQLite plugin to 2.2.0 and enable new driver~~ Update SQLite plugin to 2.2.2 and enable new driver Jul 2, 2025

wojtekn commented Jul 3, 2025

View reviewed changes

Merge branch 'trunk' into update/sqlite-plugin-to-2.2.0

224cae3

wojtekn changed the title ~~Update SQLite plugin to 2.2.2 and enable new driver~~ Update SQLite plugin to 2.2.3 and enable new driver Jul 4, 2025

wojtekn requested a review from a team July 4, 2025 12:06

wojtekn marked this pull request as ready for review July 4, 2025 12:06

sejas reviewed Jul 7, 2025

View reviewed changes

sejas reviewed Jul 10, 2025

View reviewed changes

Update SQLite driver to 2.2.3

9a91b9b

sejas reviewed Jul 10, 2025

View reviewed changes

sejas approved these changes Jul 11, 2025

View reviewed changes

sejas mentioned this pull request Jul 11, 2025

Add support for the new SQLite driver Automattic/wp-cli-sqlite-command#10

Merged

bcotrim approved these changes Jul 14, 2025

View reviewed changes

wojtekn merged commit debb874 into trunk Jul 17, 2025
13 checks passed

wojtekn deleted the update/sqlite-plugin-to-2.2.0 branch July 17, 2025 09:46

wojtekn mentioned this pull request Jul 21, 2025

SQLite/MySQL incompatibility in AffiliateWP plugin #1464

Closed

ivan-ottinger mentioned this pull request Oct 31, 2025

Set default SQL modes and other options for database import Automattic/wp-cli-sqlite-command#17

Merged

	const timeout =
	args[ 0 ] === 'sqlite' && [ 'import', 'export' ].includes( args[ 1 ] )
	? IMPORT_EXPORT_RESPONSE_TIMEOUT
	: DEFAULT_RESPONSE_TIMEOUT;

	export const SQLITE_DATABASE_INTEGRATION_VERSION = 'v2.2.2';
	export const SQLITE_DATABASE_INTEGRATION_VERSION = 'v2.2.3;

Update SQLite plugin to 2.2.3 and enable new driver #1506

Update SQLite plugin to 2.2.3 and enable new driver #1506

Conversation

wojtekn commented Jun 24, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Related issues

Proposed Changes

Testing Instructions

Pre-merge Checklist

Uh oh!

Uh oh!

wojtekn commented Jul 3, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

wojtekn commented Jul 4, 2025

Uh oh!

sejas left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

wojtekn commented Jul 9, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

sejas commented Jul 10, 2025

Uh oh!

sejas commented Jul 10, 2025

Uh oh!

wojtekn commented Jul 10, 2025

Uh oh!

sejas left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

sejas commented Jul 10, 2025

Uh oh!

sejas commented Jul 10, 2025

Uh oh!

JanJakes commented Jul 10, 2025

Uh oh!

sejas commented Jul 10, 2025

Uh oh!

JanJakes commented Jul 11, 2025

Uh oh!

sejas left a comment

Choose a reason for hiding this comment

Uh oh!

bcotrim left a comment

Choose a reason for hiding this comment

Uh oh!

wojtekn commented Jul 15, 2025

Uh oh!

Uh oh!

JanJakes commented Jul 21, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

wojtekn commented Jun 24, 2025 •

edited

Loading

sejas left a comment •

edited

Loading