Add native support for SQLite

[aristath]

A few months ago, a proposal was published to natively support SQLite in WordPress Core (​https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/).
That proposal outlines a lot of the benefits of supporting SQLite in Core.

The proposal has received a lot of positive feedback, both from the community and the WordPress leadership so this Trac ticket is the next step, to promote collaboration and discussion.

The performance team has been working on an implementation that adds support for SQLite via the SQLite Database Integration plugin (​https://wordpress.org/plugins/sqlite-database-integration/). Before being published as a stand-alone plugin, it was implemented as a module in the Performance Lab plugin (​https://wordpress.org/plugins/performance-lab/) where it received a lot of user-testing.

[aristath]

With the plugin active, all WordPress PHPUnit tests pass (with just a few exceptions that need patching in Core when the featured plugin gets merged, see ​https://github.com/WordPress/sqlite-database-integration/pull/9#issuecomment-1437018625 for details).

This is a POC for porting ​https://wordpress.org/plugins/sqlite-database-integration/ to Core.

Trac ticket: https://core.trac.wordpress.org/ticket/57793

[swissspidy]

In the blog post I see lots of unanswered questions about keeping it as a canonical plugin rather than merging it into core. Given that the post is already a few months old, I'd suggest writing a follow-up post with the latest updates (like the improved security thanks to the new parser implementation, which is pretty neat), which can then be discussed in dev chat as well.

[aristath]

Thank you for the feedback @swisspidy!

In the blog post I see lots of unanswered questions about keeping it as a canonical plugin rather than merging it into core.

Using the featured plugin is a great way to allow users to test the implementation, iron-out any issues etc. However, long-term it doesn't make sense to use this as a plugin.
Keeping it as a canonical plugin basically negates most of the SQLite benefits... A plugin can only be installed on an existing site - which would require having a MySQL database.

Given that the post is already a few months old, I'd suggest writing a follow-up post with the latest updates (like the improved security thanks to the new parser implementation, which is pretty neat), which can then be discussed in dev chat as well.

That's a good idea, adding it to my TODO list 👍

Update: This PR now translates queries using the ​MySQL lexer from sqlite-database-integration instead of ​regular expressions from wp-sqlite-db. The new implementation is a lot safer and more reliable.

I pushed a change as a proof-of-concept for how the installation screen can change.
It's not perfect, but it works and showcases how users can pick a database-type, and what that may look like.
If we don't want to provide users the option to select the db-type via the UI, we can revert the changes in these 2 files:

• src/wp-admin/setup-config.php
• wp-config-sample.php

I'm excited for this. I'd be happy to test this PR on my personal website whenever it's nearing readiness.

[joemcgill]

Adding this to the Future Release milestone for consideration once a stronger use case is presented for why this should be in core and not simply continue as a feature plugin.

This is really nice also because it would better enable usage of WordPress as a Static Site Generator (as it simplifies operating WordPress using file system only, for both settings and content)

hi folks.

I've seen a bit of pushback against SQLite because of both the license it uses, along with their policy for accepting contributions.

If this is still a bone of contention, it's worth knowing that there is a API compatible fork of SQLite called libSQL, which has a code of conduct, and accepts contributions, and uses an OSI approved open source licence.

Here's the manifesto published, explaining why it exists:
​https://turso.tech/libsql-manifesto

Here's the project:
​https://github.com/tursodatabase/libsql

It's also worth knowing that there is work ongoing to support libSQL in Laravel, and they detail their reasons for doing so too:
​https://github.com/laravel/framework/discussions/49340

For what it's worth, I'm a fan of SQLite (or SQLite compatible) support making it into Wordpress, so I'm happy to see it in core, as I actually think it could make for a nicer (albeit different) onboarding experience than the famous 5 minute install.

Just a quick question, when using the ​SQLite drop-in, does anybody get errors on wp 6.5 regarding the minimum MySQL version?

This would also lead a way to SSG mode where the content is stored in individual text files (as markdown or json). Writing a script for populating a temp SQLite database from a bunch of text/json files in directories should be quite easy

An important functionality for wpcli should be conversion from an existing MySQL db to a SQLite db.
Handrolled hacks are easy to come by, but having a more or a less CI-tested one on large realistic databases would be of great value

An important functionality for wpcli should be conversion from an existing MySQL Wordpress db to a SQLite db file

That's already possible from the terminal, but I completely agree... A WP-CLI script specifically for that task would simplify things a lot 👍

Also, maybe a UI admin button for downloading/exporting existing MySQL db as a SQLite file would be useful (for the cases where we don't have an ssh access to the hosted installation)

Also, an interesting idea could be implementing a SQLite vtab extension which would allow directly working with posts/pages stored in a file directory and e.g. config in a json file on disk

Also, maybe a UI admin button for downloading/exporting existing MySQL db as a SQLite file would be useful

And maybe this export functionality could be merged in the mainline even before the merge of the full Sqlite support changeset...

In general, curious what is the status of this feature/merging-into-the-mainline with the core Wordpress team? Looking forward to benefit from this feature in mainline :)

Just a quick question, when using the ​SQLite drop-in, does anybody get errors on wp 6.5 regarding the minimum MySQL version?

Yup. It seems we are now looking for MySQL and fail otherwise?