Introducing a new SQLite driver for WordPress

The new SQLite driver is now available in the SQLite Database Integration plugin (currently behind a feature flag), and powering the WordPress Playground website today.

Following the refactoring, the new SQLite driver now matches the current driver in terms of features and surpasses it in several key areas. However, there are still some aspects that would benefit from further feedback. Thanks to its new architecture, integrating additional features is now much faster, and work is underway to enhance MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. compatibility even further.

Architecture highlights

The new SQLite driver translates MySQL queries into SQLite statements and emulates MySQL behavior using a robust new approach, which includes:

1. A complete, fast MySQL SQL lexer written in pure PHPPHP PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML. https://www.php.net/manual/en/preface.php. with zero dependencies.
2. A complete, version-aware MySQL SQL grammar definition, adapted from the MySQL Workbench grammar.
3. Basic tooling to process and compress the grammar in pure PHP.
4. An extra lightweight, universal grammar parser written in pure PHP with zero dependencies, by @zieladam
5. An extensive test suite of 70 000 MySQL queries that we can parse successfully.
6. A new SQLite driver that translates and emulates MySQL queries on top of SQLite, using the abstract syntax tree produced by the new parser.
7. A comprehensive MySQL information schema emulation layer on top of SQLite.
8. An automated migrationMigration Moving the code, database and media files for a website site from one server to another. Most typically done when changing hosting companies. flow for databases created with the previous SQLite driver.
9. New test suites, including the WordPress CoreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress. PHPUnit test suite, with over 99% of tests already passing.

Trying the driver

• In Playground — try it directly on the WordPress Playground website.
• On your site — use the SQLite Database Integration plugin in version 2.2.1 or newer, and define the WP_SQLITE_AST_DRIVER constant as true in your wp-config.php file.

⭐️ Please share your feedback and real-world test results via GitHubGitHub GitHub is a website that offers online implementation of git repositories that can easily be shared, copied and modified by other developers. Public repositories are free to host, private repositories require a paid subscription. GitHub introduced the concept of the ‘pull request’ where code changes done in branches by contributors can be reviewed and discussed before being merged be the repository owner. https://github.com/ issues or as comments on this post.

Running WordPress on SQLite

WordPress and its plugins have long depended on MySQL, making a seamless switch difficult without breaking compatibility. Past SQLite integrations have mostly translated MySQL SQL into SQLite-compatible queries, but fully supporting advanced features and MySQL-specific behaviors remained challenging.

The new driver still processes MySQL queries by emulating them on SQLite, sometimes through direct conversion and other times with complex transformations. What’s new is a deeper emulation: queries are now parsed into an abstract syntax tree (AST), and key MySQL information schema tables are replicated in SQLite. This approach enables more robust support for MySQL features, greatly improving WordPress compatibility with SQLite.

New features

While the initial goal of the new SQLite driver was to reach full feature parity with the current implementation, it also introduces numerous enhancements and advanced capabilities:

• Advanced support of the MySQL syntax.
  • sub-queries.
  • UNION and UNION ALL operators.
  • SHOW and DESCRIBE statements.
  • INFORMATION_SCHEMA tables.
  •  TEMPORARY tables.
  • table administration statements (TRUNCATE, ANALYZE, CHECK OPTIMIZE, REPAIR).
• Support for 
  • STRICT_TRANS_TABLES and STRICT_ALL_TABLES SQL modes.
  • NO_BACKSLASH_ESCAPES SQL mode.
  • USE <database> statement for the main database and INFORMATION_SCHEMA.
• Automatic migration of databases created with the current SQLite driver.

Thanks to its redesigned architecture, the new driver lays the groundwork for future support of additional MySQL features, some of which were nearly impossible to implement in the current driver.

Examples

Take a brief look at some of the new tricks that the new driver can already handle.

Create the wp_users table:

CREATE TABLE wp_users (
  ID bigint(20) unsigned NOT NULL auto_increment,
  user_login varchar(60) NOT NULL default '',
  user_pass varchar(255) NOT NULL default '',
  user_nicename varchar(50) NOT NULL default '',
  user_email varchar(100) NOT NULL default '',
  user_url varchar(100) NOT NULL default '',
  user_registered datetime NOT NULL default '0000-00-00 00:00:00',
  user_activation_key varchar(255) NOT NULL default '',
  user_status int(11) NOT NULL default '0',
  display_name varchar(250) NOT NULL default '',
  PRIMARY KEY  (ID),
  KEY user_login_key (user_login),
  KEY user_nicename (user_nicename),
  KEY user_email (user_email)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The new driver can also execute arbitrarily complex queries using the INFORMATION_SCHEMA:

SELECT
  cols.DATA_TYPE,
  stats.INDEX_NAME,
  stats.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS cols
JOIN INFORMATION_SCHEMA.STATISTICS AS stats
  ON  cols.TABLE_SCHEMA = stats.TABLE_SCHEMA
  AND cols.TABLE_NAME   = stats.TABLE_NAME
  AND cols.COLUMN_NAME  = stats.COLUMN_NAME
WHERE
  cols.TABLE_SCHEMA   = 'wp'
  AND cols.TABLE_NAME = 'wp_users'
ORDER BY INDEX_NAME ASC;
-- bigint   PRIMARY         ID
-- varchar  user_email      user_email
-- varchar  user_login_key  user_login
-- varchar  user_nicename   user_nicename

You can add a bit more advanced syntax like UNION, DISTINCT, CTEs, and function calls:

WITH
  cols AS (
    SELECT COLUMN_NAME AS column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'wp' AND TABLE_NAME = 'wp_users'
  ),
  indexes AS (
    SELECT DISTINCT INDEX_NAME AS index_name
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'wp' AND TABLE_NAME = 'wp_users'
  )
SELECT CONCAT(column_name, ' (column)') AS name
FROM cols
UNION ALL
SELECT CONCAT(index_name, ' (index)') AS name
FROM indexes
ORDER BY name;
-- ID (column)
-- PRIMARY (index)
-- display_name (column)
-- user_activation_key (column)
-- user_email (column)
-- user_email (index)
-- user_login (column)
-- user_login_key (index)
-- user_nicename (column)
-- user_nicename (index)
-- user_pass (column)
-- user_registered (column)
-- user_status (column)
-- user_url (column)

Finally, let’s export the table schema:

SHOW CREATE TABLE wp_users;
-- CREATE TABLE `wp_users` (
--   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
--   `user_login` varchar(60) NOT NULL DEFAULT '',
--   `user_pass` varchar(255) NOT NULL DEFAULT '',
--   `user_nicename` varchar(50) NOT NULL DEFAULT '',
--   `user_email` varchar(100) NOT NULL DEFAULT '',
--   `user_url` varchar(100) NOT NULL DEFAULT '',
--   `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
--   `user_activation_key` varchar(255) NOT NULL DEFAULT '',
--   `user_status` int(11) NOT NULL DEFAULT '0',
--   `display_name` varchar(250) NOT NULL DEFAULT '',
--   PRIMARY KEY (`ID`),
--   KEY `user_login_key` (`user_login`),
--   KEY `user_nicename` (`user_nicename`),
--   KEY `user_email` (`user_email`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The definition is returned exactly as supplied, confirming faithful emulation of MySQL’s SHOW CREATE TABLE.

The above scenario was never supported by the previous driver, and implementing it would have been extremely difficult due to its basic, token-based architecture. With the new driver design, advanced use cases like this can now be supported much more naturally.

What’s next

Stabilizing the new SQLite driver in the 2.x line of the SQLite Database Integration plugin requires your feedback. Once there is enough confidence, a  3.0 of the pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party will be released where the new driver will be enabled by default (and not behind a feature flag like in 2.x). The new driver is also being continuously tested for compatibility with plugins from the WordPress Plugin Directory and expanding feature coverage.

After the 3.0 release and the new SQLite driver enabled by default, the development focus will shift to expanding MySQL functionality coverage, improving performance, and stabilizing the APIAPI An API or Application Programming Interface is a software intermediary that allows programs to interact with each other and share data in limited, clearly defined ways., with the future prospect of integrating the driver into WordPress Core.

Looking even further ahead, the new lexer and parser pipeline lays the foundation for supporting additional database engines within the WordPress ecosystem.

Props to for reviewing to @zieladam and @bph