Status update on the SQLite project

This post is an update to the proposal for officially support SQLite in WordPress. The initial implementation was included in the Performance Lab 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 Plugin Directory or can be cost-based plugin from a third-party and then released as a stand-alone plugin.

That initial implementation was based on wp-sqlite-db by @aaemnnosttv, which in turn, was a fork of an older plugin.

Over the course of the last 6 month, issues with that implementation surfaced and the project experienced some limitations. As a result, we (@zieladam and @aristath) decided to rewrite it using a more future-proof concept.

The code has been completely rewritten to use an SQL Lexer and is now stable and able to handle all WordPress queries properly. The SQL Lexer is part of the PHPMyAdmin/SQL-Parser project (licensed under the GPLGPL GNU General Public License. Also see copyright license. 2.0) and it was adapted for WordPress, effectively implementing a MySQL to SQLite translation engine. This provides improved security, as well as compatibility.

The update has already been released in the standalone plugin and will soon be ported to the Performance Lab plugin. Most WordPress Unit Tests now pass, with the exception of a few that require patching WordPress CoreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress..

The next step would be to implement these changes to WordPress core instead of using a plugin. To that end, we have a draft Pull Request and an accompanying Trac ticket.

Why should this be in Core and not as a plugin?

In its current form, the SQLite implementation is a plugin. Just like all plugins, it can only be installed on a pre-existing website. As a result, a site can only use an SQLite database if it already has a MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. database – effectively negating all the benefits that SQLite can bring to WordPress.

Using the featured plugin is a great way to allow users to test the implementation and iron out any issues etc. However, long-term, it doesn’t make sense to use it as a plugin. 

What are the next necessary decisions?

There needs to be a decision, if the WordPress project wants to provide an option to users during WordPress installation, so they can choose whether they want to use a MySQL or an SQLite database. If no option is available in the UIUI User interface, then users would have to manually add define( 'DB_ENGINE', 'sqlite' ); in their wp-config.php file.

Adding an option to select the database type may initially raise some eyebrows thinking that the famous 5-minute installation process will become more complicated, but in fact, it’s the exact opposite: If users pick SQLite as their database, they don’t need to create a MySQL db or enter their credentials in the installation screen, as no separate server is required.

A prototype of the UI changes are already available via the pull request in WordPress Core, to showcase how easy it would be to install WP using SQLite instead of MySQL.

The proof-of-concept UI in that implementation checks if the server supports SQLite and MySQL:

  • If SQLite is supported but not MySQL, then it defaults to SQLite and no UI option is shown to users. This is for example what happens in the WordPress Playground – it is vanilla WordPress that defaults to SQLite.
  • Similarly, if the server supports MySQL and not SQLite, then the installation will use MySQL and no option will be shown to users.
  • The UI option will only show if both SQLite and MySQL are supported by the server.

How can this implementation be tested?

The next step would be to thoroughly test the implementation with all the plugins you normally use. You can use the SQLite Database Integration plugin to test an SQLite database on your existing website, or better yet – you can test the pull request in WordPress Core.

If you test the plugin, keep in mind that it does not copy your current data from MySQL to SQLite, as mentioned in the call-for-testing post.

Props @zieladam for proofreading and contributing to this post, @bph for peer review