WordPress testing official SQLite Support | Hacker News Reader

WordPress testing official SQLite Support

github.com

122 pointsethanpil73 comments

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.

> Since WordPress doesn't have a database abstraction

How hard would it be to "add one"/refactor?

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.

> How hard would it be to "add one"/refactor?

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.

plug-ins are likely the real burn.

MediaWiki technically supports PostgreSQL and SQLite for many years, but extensions are really MySQL focused making it the only real choice.

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.

Behold: https://github.com/WordPress/WordPress/blob/master/wp-includ...

This brings back bad memories. I shouldn’t have reminded myself of what WP code looks like.

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.

> How hard would it be to "add one"/refactor?

To add one? Trivial.

To refactor all of WP core to use it? I could probably do it in a week or two of focused work.

To enable all plugins and wordpress themes to use it? It is, literally, probably a million times more LOC.

Probably easier to use MySQL as the db layer and have it use SQLite as the backend store somehow heh.

... of course it would be fucking cursed, it's wordpress

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!

This sqlite abstraction has been working well for me: https://github.com/aaemnnosttv/wp-sqlite-db

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

https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/...

PHP is ultra slow anyways.

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.

Exactly no cache no nothing required. Your only bottle-neck can be your disk and code execution speed.

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.

I once was using a plugin that logged every interaction to the database..

As a post, lmao. It was using its own post type in the wp_posts table and associated post meta table. Barmy.

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.

Many queries are slower than few yes, but doesn't mean it'slow

I think the sqLite website does like 200 queries per page view

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.

>rewrite it using a modern PHP web framework Do you mean build a UI for laravel?

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.

That's excellent, I have been sad to see Ghost lose support for SQLite[1]. I have been running a blog on it for years, works great.

1: https://github.com/TryGhost/Ghost/issues/14446

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.

This will be a huge infrastructure simplification for 95%+ of Wordpress site.

I have to imagine shared web hosts will love this.

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

Complexity? I have a small site that would run just fine with SQLite. I’d switch over in a heartbeat.

Complexity? MySQL 1 click installs on damn near every WordPress install. I fail to see how it is complex.

Keeping it updated and backed up is complex. Sqlite can be embedded inside an application (easy updates) and the database backup is a single file.

Zero clicks is better. And less resource usage, which can help on really small instances with multiple applications.

Someone's never seen a WP white screen of death because of some esoteric MySQL error the average person will have no idea how to debug.

I'm curious as to how much of your site depends on WordPress, vs. just using a static site generator.

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.

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.

SQLite would bring simpler installs, reduced administration needs, and a decreased attack surface.

Resource usage on a default MySQL install today is quite outrageous relative to what is needed for a typical blog.

Because 99% of Wordpress installations would be much better off without any sql database, aka something like a git backend.

Portable WordPress

This is at least a decade overdue. I've moved my personal site to Pico CMS (flat-file) for precisely this reason.

How cool would it be to bundle WordPress as a single PHAR file with one SQLite db file to go with it in the same directory?

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

Simple backup and restore. Different or less quirks. Less passwords. Easier to deploy on edge. Less Oracle

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

https://www.sqlite.org/backup.html

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.

"Download Wordpress.exe and double click to run it"

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.

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.

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.

That is exactly what we are working on at WasmLabs! https://wordpress.wasmlabs.dev

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.

https://github.com/WordPress/wordpress-playground/

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.

Yes! We will reach out

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.

WordPress does not require mpm_prefork. It works just as well with mpm_event (with php-fpm). Arguably, mpm_event is the most widely used one nowadays.

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

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

Migrated to Ghost and happy. All without PHP and Babylon of addons to getting things done.

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.

I was looking at the implementation. What the hell. Who would want to maintain this monster?

Awesome.

How will this work considering sqlite databases cannot be accessed concurrently from several processes or even threads?

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

Yes, and you can only do ONE SINGLE WRITE PER MINUTE when using SQLite.

People need to stop believing urban legend or stuff that was half-true 15 years ago and test for themselves.

Reply on news.ycombinator.com