I'm highly in favour of this, but wanted to point out an important implementation detail in case people don't want to look through the code.
Since WordPress doesn't have a database abstraction, SQLite integration is done by transforming the SQL query strings meant for MySQL. This not only means doing regexp matches with string replacement, but trying to emulate MySQL functions with either SQLite equivalents, or in the worst case, in PHP application code.
it’s one file — ‘db.php’ — you swap in for the core file. From there, it’s mostly been seamless. ~20% of the 5k LOC, is “Method to emulate MySQL XXX() function.”
Less than that for query parsing, regex & rewrites
Lol after decades of programming, there actually WAS a need in THIS project to 'abstract/subclass' the db.code. You know in case we want to switch dbs in the future.:)
Interestingly, WordPress does (kind of) have a database abstraction. The way you get it is to format your data model as a “custom post type,” and then you basically get an object store with various predefined functions to access it… kinda works for more use cases than you’d think, but also weird!
It doesn't even currently use real parameterized queries...it has a method that sounds like they are real, but they aren't...just a really hairy bunch of string escaping. They really need to re-write the database layer from scratch.
From a technical POV: This is potentially straightforward if WordPress leverages (and "blesses", for plug-in developers) a proven abstraction layer like Doctrine DBAL that supports both MySQL and SQLite.
From a non-technical POV: There are tens of thousands of WordPress plug-ins, and even updating the top 1,000 that are good/popular will be a multi-year lift.
I'd guess very hard. But just refactoring the WordPress codebase isn't enough, you'd ideally want every plugin to adopt the new API as well... Although you could always keep this implementation as the fallback behaviour.
The problem is not adding one. The problem is that it would complicate the infrastructure. From adding more performance requirement to WP wherever it is hosted to the compatibility concerns with plugins. Its a tall tale.
The ratio of writes (few) to reads (lots) on a blog/cms is such that SQLite is ideal. Even better this positions WordPress well for edge deployment on platforms such as Fly or CloudFlare Workers.
With these edge platforms you really want your DB at the edge with you app code to reduce latency. In fact not doing so can make your app slower due to increased latency on multiple round trips to the DB. SQLite with this type of app is ideal for it, just copy your DB file to the edge VM/Worker any time it changes.
Effectively you move your whole app and DB closer to the user, and being a small embedded sql engine rather than a full server, your edge deployments become very light weight. Makes it possible to have more of them distributed closer to a higher number of users.
For people who are wondering if SQLite is the right database for Wordpress, I recommend taking a look at https://www.sqlite.org/whentouse.html where SQLite authors explains what sites it makes sense for.
In short: for most of Wordpress deployment, especially for long tail of hair salons, car dealerships, personal blogs and other non-tech SME sites
I expect it will quickly get reputation of being slow purely on how some plugins work with database. Many of them will turn relatively static site into requiring to run some inserts on every query and inserts from multiple processes is the SQLite's worst use case.
Changing away from the “wp_posts table for all things” as a convention would vastly improve WordPress performance.
Talk to someone about how gross this is and they’ll start justifying it to you as “history” and then talk about how cool it is that WordPress was never meant for what it’s used for today but still works. It makes all the managed hosting feel like one big conspiracy.
I can’t tell you how many times I’ve had to optimize raw queries in that stupid framework. Thank god for the rise of SSG and decoupled content management system.
WordPress support is one thing: the gazillion plugins supporting it - yet another! Although WordPress is the biggest pile of spaghetti running on a huge share of servers today, it's much better than the plethora of low-quality and barely maintained plugins and themes rotting in conflicts between each other! I spent hundreds of dollars buying plugins for a nonprofit website and every time when there's an issue, the recommendation is: disable all other plugins and switch to the standard theme! Instead of investing more effort into this, they should rewrite it using a modern PHP web framework or, let's say, TypeScript.
I'm not saying that - I am fully aware of the Laravel ecosystem and the rest of the popular frameworks. They should start one from scratch - given the footprint of WordPress, it's not a stupid idea. Also, themes should stop being code!
You should check out Winter CMS, we're built on top of Laravel and themes are powered by Twig - no heavy PHP code allowed in themes. Instead, complex logic is handled by "Components" that are provided by plugins.
It's extremely powerful and developer friendly (no more "custom post types" for every record type under the sun), definitely my preferred way to build websites that WordPress would normally handle.
Whoa, it doesn’t even officially support MariaDB. Its only supported DB is Oracle MySQL. I know many people don’t share my opinion on this, but for me, that’s a dealbreaker.
No. They wont do anything with it. The compatibility concerns with existing 60,000+ plugins are more important than whatever performance improvement sqlite could bring. This is especially less important in shared hosting where cpu is cheap and shared. Adding a 5-10% performance on average is not something that would rationalize changing entire infra.
For shared it’s important to not suddenly break sites, even during security updates. That’s were you get hardened php and keeping php 5 still alive on many shared hosts. I can see this as just being something added to new Wordpress installs but backwards compatability will always be kept for the current shared hosting. However it does give an opening for a new comer on the block who starts fresh with a simplified set up since they can start fresh on with a simpler infrastructure.
Is there any reason to want to use SQLite for Wordpress? Its always great to have more options, but Im unclear as to why someone would use that instead of MySQL
MySQL is a bit of a faff for the average person who wants to self-host WordPress. SQLite can be made more user friendly. Plus, MySQL is more expensive to host.
Uhh, kinda. You're not supposed to just copy SQLite file for backup purpose [1] but I'd wager many people will just go HURR DURR IT'S A FILE I WILL JUST BACKUP A FILE and then be surprised if something fucks up
I use Hugo, and I have a relatively ergonomic workflow set up where I use iA Writer to write posts on my iPad, then Working Copy to push them to a git repo. Then my server pulls that repo on a cron job, and builds and deploys it. It’s fine. It gets the job done.
But I confess that being able to post a new entry to an API and have it published instantly sure is attractive. If WordPress had a better security track record, I’d be tempted to switch to it. You don’t get much more secure than a bunch of static files served straight from disk, though.
My mother in law is the one writing content for it. She knows Wordpress from working for other companies. In theory I could teach her something new, but why bother? She enjoys what she’s doing now. I don’t want to take away from that.
That's understandable. My question wasn't meant as a challenge--I'm assuming that most sites could transition to a static site with judicious JS to lively up things.
One killer feature for me aside from usability for less-technical people are comments. While comments are not be necessary for certain types of websites, I consider them essential for anything you might call a blog.
For a VM with a couple of wordpress blogs that I administer SQLight should allow to reduce memory from 4 to 2GB. In addition the blogs will no longer share the database improving security (while the blogs has the same Wordpress version the plug-in set is different, so a vulnerability in one is not necessary affects another).
Hopefully you'll be able export your entire site as a single .db file. SQLite also has easy serialization to JSON or CSV built in. I've never worked with mysql but I use SQLite in web workflows a lot and it's just so simple.
As someone who works with WordPress-hosting: Simplicity.
A lot of people use WordPress for drag'n drop site building. Getting such sites over on Sqlite would increase uptime (less chance of failure withou the separate DB server), reduce support load (easier to move from other providers) and so on.
Alas, compatibility will not be that great with various plugins, so I fear adding SQLite this late will just have the opposite effect.
You could run it on the edge, serverless. CloudFlare D1 supports a SQLite database that your code on the edge can access. That gives you an amazing way to run WordPress without worrying about the OS.
PHP isn’t natively supported there yet, but I can imagine such a thing could be built and be successful.
WordPress having official SQLite support will make it more portable and self-contained for hosting on edge platforms, as well as in the browser via WebAssembly.
I love the work going on there at WasmLabs, especially enjoying the articles with in-depth technical explorations.
After the article about running WordPress in the browser was published, there's a new project called WordPress Playground which is gradually preparing NPM or Composer packages to make it easier for people to run it.
They've been doing very detailed work, like making some patches to PHP and SQLite for improved compatibility with Emscripten, etc. It seems there's a lot of overlap with what WasmLabs has achieved and probably have continued to develop further. Perhaps there's an opportunity for collaboration.
This'd be great. I'm curious about performance, of course, but for personal blogs without comments, coupled with a caching module... This could be great.
One thing I'm still trying to suss out is how to have Apache host Wordpress and stuff via HTTP/2. Wordpress which needs PHP which requires mpm_prefork, which precludes mod_http2. Guess I should just proxy WP to another instance of Apache or some other httpd...?
Take a look at php-fpm. It works with all the major servers and let’s them do what they do best and offloads the php requests to php when needed. It also has better caching and overall performance than mod_php since it’s a long running daemon.
I just put everything behind haproxy and handle most of "which domain goes to where" there. It's designed to be reverse proxy from the scratch, it's light and very good at it
> Wordpress which needs PHP which requires mpm_prefork, which precludes mod_http2. Guess I should just proxy WP to another instance of Apache or some other httpd...?
php-fpm have been de facto method you should host PHP for a decade+. It also allows to have different permissions for web server and PHP app which can make it more secure
Yeah, I guess I should move back to php-fpm... I did years ago, but then consolidated to a very simple server where I just wanted Apache with mod_php doing its thing.
Guess I should take some time this winter to restructure things...
I wonder how terribly it will work with some of the plugins out there, especially the "security" ones that decided best way to protect against attacks is to log every request, turning sites that would normally "just" read from db into producing steady stream of inserts. IIRC SQLite really doesn't like multiple threads writing...
Perfect! This will be a perfect use-case for Marmot (https://github.com/maxpert/marmot) to help scale Wordpress when needed! People can just publish (low writes + high reads), and Marmot will take care of replicating it as sidecar. Beauty of sidecar will pay off here, 0 modifications to Wordpress codebase itself to horizontally scale it.
IMO Wordpress + SQLite is a no brainer for 90% of sites on internet. I was actually surprised when I discovered the most famous CMS doesn't support SQLite.
This seems... not true, as https://www.sqlite.org/appfileformat.html indicates it "automatically coordinates concurrent access to the same document from multiple threads and/or processes" (search for: "Concurrent Use By Multiple Processes").
Since WordPress doesn't have a database abstraction, SQLite integration is done by transforming the SQL query strings meant for MySQL. This not only means doing regexp matches with string replacement, but trying to emulate MySQL functions with either SQLite equivalents, or in the worst case, in PHP application code.