WordPress testing official SQLite Support

https://github.com/WordPress/performance/pull/547

by ethanpil • 4 years ago

122 73 4 years ago

Awesome.

danjoredd• 4 years ago

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

upon_drumhead2• 4 years ago

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

TrueSlacker0• 4 years ago

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

pstuart2• 4 years ago

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

Veen• 4 years ago

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.

jchulce• 4 years ago

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

ergonaught• 4 years ago

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

nix23• 4 years ago

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

pacifika• 4 years ago

Portable WordPress

rchaud2• 4 years ago

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

ethanpil• 4 years ago

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?

_0w8t• 4 years ago

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

jbverschoor• 4 years ago

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

ilyt5• 4 years ago

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

schwartzworld• 4 years ago

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.

seydor• 4 years ago

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

captn3m0• 4 years ago

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.

2000UltraDeluxe• 4 years ago

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.

c0nsumer2• 4 years ago

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

jcoby• 4 years ago

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.

Ayesh• 4 years ago

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.

c0nsumer2• 4 years ago

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

ilyt5• 4 years ago

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

blacksmith_tb• 4 years ago

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

kstrauser2• 4 years ago

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.

adobrawy• 4 years ago

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

ilyt5• 4 years ago

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.

corobo• 4 years ago

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.

rawoke0836002• 4 years ago

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

samwillis• 4 years ago

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.

maxpert2• 4 years ago

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

maxpert2• 4 years ago

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.

tiffanyh• 4 years ago

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

I have to imagine shared web hosts will love this.

unity10012• 4 years ago

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.

quags• 4 years ago

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.

bretthopper2• 4 years ago

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.

MuffinFlavored• 4 years ago

Since WordPress doesn't have a database abstraction

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

bretthopper2• 4 years ago

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.

CharlesW• 4 years ago

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.

tyingq• 4 years ago

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

unity10012• 4 years ago

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.

AdamJacobMuller• 4 years ago

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.

bombcar• 4 years ago

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

ilyt5• 4 years ago

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

rawoke0836002• 4 years ago

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

noahtallen• 4 years ago

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!

smcnally• 4 years ago

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

maxloh• 4 years ago

PHP is ultra slow anyways.

ilyt5• 4 years ago

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

zqfuz• 4 years ago

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

htp• 4 years ago

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

stopsqlfud• 4 years ago

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.

paulcarroty• 4 years ago

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

nikolay2• 4 years ago

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.

anaganisk• 4 years ago

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

nikolay2• 4 years ago

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!

lioeters2• 4 years ago

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.

ridruejo2• 4 years ago

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

lioeters2• 4 years ago

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.

thdespou• 4 years ago

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

WordPress testing official SQLite Support

Related Stories