Make WordPress Core

Opened 17 months ago

Last modified 2 weeks ago

#57793 new feature request

Add native support for SQLite

Reported by: aristath's profile aristath Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version:
Component: Database Keywords: has-patch
Focuses: performance Cc:

Description (last modified by 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.

Change History (18)

#1 @aristath
17 months ago

  • Description modified (diff)

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).

#2 @aristath
17 months ago

  • Description modified (diff)

This ticket was mentioned in PR #3220 on WordPress/wordpress-develop by @aristath.


17 months ago
#3

  • Keywords has-patch added

#4 @swissspidy
17 months ago

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.

#5 @aristath
17 months ago

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 👍

Last edited 17 months ago by aristath (previous) (diff)

@zieladam commented on PR #3220:


17 months ago
#6

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.

This ticket was mentioned in Slack in #hosting-community by javier. View the logs.


17 months ago

@aristath commented on PR #3220:


17 months ago
#8

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

sangaman commented on PR #3220:


12 months ago
#9

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

#10 @joemcgill
12 months ago

  • Milestone changed from Awaiting Review to Future Release

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.

vadimkantorov commented on PR #3220:


5 months ago
#11

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)

mrchrisadams commented on PR #3220:


5 months ago
#12

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.

@dingo_d commented on PR #3220:


4 months ago
#13

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

This ticket was mentioned in Slack in #core-php by dd32. View the logs.


3 months ago

vadimkantorov commented on PR #3220:


5 weeks ago
#15

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

@aristath commented on PR #3220:


5 weeks ago
#16

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 👍

vadimkantorov commented on PR #3220:


4 weeks ago
#17

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)

vadimkantorov commented on PR #3220:


2 weeks ago
#18

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

Note: See TracTickets for help on using tickets.