Wp-SQLite: WordPress running on an SQLite database | Hacker News Reader

Wp-SQLite: WordPress running on an SQLite database

github.com

182 pointslsferreira4297 comments

WordPress should be on SQLite by default. Most installations run only on one sever and are rather small (less than a few GB database size). And not a lot of inserts/updates to the DB.

In those cases SQLite will probably have much better speed and a lower memory footprint.

Most WP sites should be static sites.

Amen.

Those are orthogonal and not mutually exclusive. I have a couple wordpress sites that are static.

There are a number of different ways to do it which range from Next.js with WP as a headless CRM (my current preference), or using a static site generator plugin inside of WP. I can recommend "Simply Static" as a handy plugin that (combined with nginx) will make as much of your site as possible static but still updates quickly and can allow some dynamism.

But it does introduce complexity, and the more frequently the site changes the less it makes sense. As the site grows also most sites end up wanting dynamic stuff that often can't (or shouldn't) be done on the client-side, slowly making the static build less and less useful. It's just not that simple.

Yes, but you can still build a static site reading the content from a SQLite file instead of a directory of HTML files. That’s the beauty of SQLite: this is “just” a database library and file format.

> Most WP sites should be static sites.

https://wordpress.org/plugins/simply-static/

https://wp2static.com

Wouldn’t that make managing tags and categories more difficult?

It's a little more difficult but not by much.

On WordPress, you can change a tag, hit save, and the change is live.

On a static site, the change would look more like: edit a file, rebuild the site, deploy to production.

I left WordPress for static sites 8 years ago and never looked back. It's so nice to eliminate the database and web server from your stack and have everything under source control.

On my internal network servers I use wp-supercache to generate static pages on page visit, then I use a shell script that iterates through all possible article, rss, tag, and category pages with curl at varying intervals, forcing a cache build. In parallel I rsync all the images and statically built pages to my public edge. The public only gets to interact with content that is completely static and don't have php installed at all. It is easy enough, and the only real way to allow people to install whatever dumb wordpress plugins that they want on the internally installation without getting the sites owned all the time.

This is assuming users are technical user, which isn't the case in most WP site.

Wait, rather small as in GB!?

I don't know much about WordPress but I wouldn't expect a simple website's DB to be more than a few MB.

They start getting a lot bigger once you start accepting and saving user input, e.g. running a WooCommerce store.

That's smaller than "a few GB", so no problem ;)

SQLite can get tricky, if the database files get bigger. But up to a few GB there should be no problem at all (SQLite supports up to 281 TB though)

The practical DB size limit for WordPress is around 60-70GB. After that it becomes rather sluggish.

That is terrifying that you know this. I am almost curious but would rather not know more.

It'll become unusable at a few hundred meg when it takes 180 SQL queries to render a landing page.. I wish I didn't know that either.

A few hundred megs for a single page? Yes.

For an affiliate site with millions of pages you need much more to make WordPress sluggish. On the other hand, it only takes a single badly written plugin to achieve the same effect. :D

I host quite a lot and the size is around 40-80 MB for presentation sites. Shops are around 100-300 MB. So a nice fit for SQLite. Would be a really nice options, but it should be part of the normal WP distribution.

Ghost[1] can be spun up using sqlite3 or MySQL, I have been supporting a company Ghost blog on sqlite for years now, works nicely.

1: https://ghost.org/docs/architecture/

Isn’t this going away in a near future update?

Exactly. Is there any benefit running mysql instead of sqlite for Wordpress? Feels like default db should have been sqlite all along.

I figured out how this works. The secret sauce is this code, which uses regular expressions to rewrite the MySQL SQL queries for compatibility with SQLite!

https://github.com/aaemnnosttv/wp-sqlite-db/blob/14f9e917c55...

Yikes. That looks dangerous.

Yeah plugins' abilities to issue arbitrary sql queries are a bit of an Achille's Heel for any database reworking within Wordpress -- there's no telling what quirks a plugin might be using. I don't know if there's a neat way around this... maybe something that can intercept a soft parse and have a one-to-one conversion to a different syntax. I don't think there's a non-brittle solution here.

This is a disaster waiting to happen. Regular expressions should never be used to parse non-regular languages, of which SQL is one.

There are a variety of mature MySQL dialect parsers available[1][2], and MySQL should have its own public APIs for transforming a query into an AST. Any of those would be a safer and more correct alternative.

[1]: https://github.com/pingcap/parser

[2]: https://github.com/square/mysql-parser

Out of interest, do you know if there is any similar tool/library for getting a MySQL query plan (either logical or physical) in a serialiable format?

Not that I know of, sorry. There’s probably something in the MySQL APIs, but it’ll probably need additional work to get it into a serializable format.

https://stackoverflow.com/questions/1732348/regex-match-open...

Wordpress isn't database agnostic by default? That seems strange to me!

Wordpress was first released in 2003. There wasn't a database abstraction layer for PHP at the time (PDO was first released in 2005), so it was tightly coupled to MySQL.

And PDO isn't even a proper database abstraction layer, it's just a way to connect to various databases and issue prepared statements. You still have to write your own SQL queries and create table statements, which will most certainly not be portable.

Fair. It's 2022 now. I figured in the 19 years since they would have done something like this.

They’ve consistently prioritized backwards compatibility over pretty much anything else.

It doesn't even use placeholders and prepared statements. What looks like prepared statements, for example, are not.

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

This is wild and hard to believe nobody has found a way to do some sql injection from there

I wonder how much can we call SQL a standard with so many differences between two different servers like this.

Or at least how much WP could be changed to meet at a common ground. How hard would be to run WP on Postgres? MSSQL? Or that legal company that sells a DB?

There have been many attempts to support PostgreSQL. Generalized SQL is difficult because WP also has plugins that assume MySQL, and RDBMS vary widely in their dialects even for relatively simple things like adding columns.

Fantastic! The author just provided a path for a lot of us to quickly convert our MySQL-driven PHP apps to SQLite.

There's an existing WP "drop-in" plugin that can be used with Composer: https://github.com/aaemnnosttv/wp-sqlite-db

Using that + a WP composer package (like https://packagist.org/packages/roots/wordpress) is much easier to maintain that a full "fork" of WP.

Now I'm hopeful that (a fork of) WordPress can eventually run on Cloudflare's new D1 service:

https://blog.cloudflare.com/introducing-d1/

This repository is:

- the WP-sqlite-db plugin

- akismet added

- themes before twentytwenty removed

- an empty 'wordpress.db'

- wp-config.db setup set to use the WP-sqlite-db plugin with the right file

- README

Which is all work to do to get setup correctly.

NOTE: the wp-config.php also needs to be modified to remove the keys and salts as well.

(edit: formatting).

This repository is a bit misleading.

It's really just a Wordpress install with this plugin preconfigured: https://github.com/aaemnnosttv/wp-sqlite-db

That plugin is where the interesting stuff happens.

Thank you. (I wrote this three years ago.) https://billpg.com/dear-wordpress-please-stop-using-mysql/

Short version: With MySQL, I can't simply backup or deploy a copy of WP by zipping up a folder full of files. The database has to be backed-up and deployed separately but in sync with the folder of files too. Moving to an architecture where the database are files inside that folder would simplify all of this.

What are your thoughts on plugin backward compatibility? That could create a lot of issues. (To be clear, I /love/ the idea of sqlite being the default; I just genuinely can't figure out a way around the plugin headache.)

If running WP this way becomes popular, plugins will adapt and advertise themselves as compatible with SQLite.

I assume there is also a need to backup and deployment for web server configuration too?

It isn't safe to copy a SQLite database because the file might be in the middle of a transaction. You need to use the ".backup" command instead.

This is true, but because most Wordpress sites hardly ever do inserts etc it’s probably not going to be an issue in this scenario.

If that's true, that's a flaw in SQLite. The plug might be pulled at any moment and you can't stop it.

I am incorrect in this regard.

You can use litestream for proper sqlite replication

Just a reminder, always add on some static page generator on top of Wordpress so you completely avoid the class of problems with "too many database connections". Yes that error is MySQL specific but the class of problems you're avoid is: not hitting the database for fairly static content like blog posts.

and caching...

Outside of using a CDN, just simply using static files gives you caching for free at the file system level. Linux automatically keeps files in RAM when they’re used.

It's not just static files but database calls to construct pages

Hmm? Generating static pages avoids database calls to construct pages. You do it once when you publish/edit a post. Then every reader never makes a database call again.

I think we're arguing the same point, via different means.

Just to help with future understanding of how threads go off the rails: eatonphil added a comment about using static files instead of dynamic, then your reply was “and caching”, meaning “in addition to static files, also use caching”. At this point in the conversation, we’re talking about only static files with caching, so it doesn’t really follow that you’re bringing up “database connections” again, since that’s already been excluded in this thread.

I should have put caching also helps if you can't produce static stites via the application (i.e. some shopping cart software), not just wordpress.

Ah, now I understand what you meant.

Is there a plug-in or another software that does this? I’ve been looking for ways to statically “archive” a Wordpress site so I can host it on GitHub Pages but still have the editing/theme/plug-in support. I don’t care about the search functionality.

Simply Static

https://wordpress.org/plugins/simply-static/

Has anyone actually done this? I looked into it briefly, came away thinking it was going to be non-trivial to get working (or might have downstream bugs), and gave it up as not worth it.

But I could have been completely wrong. Anyone have real world experience?

(BTW - My site is small and 99% of users are not logged in, and 99% of pages are unchanging, so a perfect use case for a static site.)

Yep. Did it this weekend. Moved main blog over to a different subdomain, added basic auth to stop it being crawled, added Simply Static, pressed button and a few minutes later the main site was updated.

Some perf tweaks I made were to point the main site’s wp-content/uploads at the WP htdocs via a nginx path mapping, and to tell SS to never export anything in uploads. Reduces the site generation to just the html, css, and JS.

The only issue I've found so far is that archive URLs like /YYYY/MM/, which would be handled by Wordpress to list all posts in that month, don't get exported by Simply Static. I suspect it's because they're all available by a select dropdown that's using a JS hook for onChange(). I solved this by creating an unlinked page, and put in an Archives block, then told Simply Static to add that unlinked page to the crawl list.

The other caution is that it doesn't export anything that's not linked. So if you've got no links to the yearly archives, then you get no index.html files for /YYYY/ either. Unfortunately, the Archives block in Wordpress doesn't offer a toggle between months and years, so you can't just create another block in the "archive finder" page and set it to years.

The support forum section of wordpress.org for the plugin appears to be a lot of people asking for support and no comments by the plugin author. If it works for you, then that's probably not an issue.

Yep, it wasn't too hard to get it working. I modified my theme slightly to get rid of the search bar that doesn't work, and I hid comments, since those won't work either.

My homepage running on vanilla Wordpress: https://www.skyfaller.space/

The same site using Simply Static: https://static.skyfaller.space/

Does it feel slightly faster?

You can feed both URLs to Google's Page Speed tool. The dynamic page scores 75/96 (mobile/desktop), and the static scores 72/98. So perhaps for your specific case it's not much of an improvement (I'd consider the 3 point drop for static mobile to be within margin of error).

[0] https://pagespeed.web.dev/report?url=https%3A%2F%2Fwww.skyfa...

[1] https://pagespeed.web.dev/report?url=https%3A%2F%2Fstatic.sk...

Actually for me the Wordpress version loaded faster! But I guess pagespeed is the right way to check.

Problem with these plugins is that if you have a large database with posts/pages, it could run into memory/CPU issues while trying to generate the static files and can intermittently fail unless you are on a beefy server.

Cloudflare is a great option for this.

Sure that's fine too all I mean is that you can run WP on a $5 Linode/DO VM and handle the front page of HN but you need to do static page generation.

I think people underestimate what's possible cheaply and simply with static pages. (Not saying Cloudflare is not free for this load but it is a separate additional step you'd need.)

I hadn't realized how easy static pages were to add to Wordpress - looks like it's just a plugin. Yeah that's probably easier than Cloudflare (which is still very easy).

Some 10ish Years ago what I really needed for work was a way to have a dynamic/staging environment with wordpress, and then to be able to deploy a fully statically generated version of the site. We had dozens of government agencies we were working with at the time, and there just wasn't anything suitable available.

One of wordpress's delights on the DB layer at the time (may still be true?) is that a sizeable number of the database entries were marshalled php objects. So many of them also ended up encoding the domain name in some way, that what we ended up doing was writing some code to essentially just rsync files from one server to the other, and then download the tables from MySQL, update all the records one at a time, and then upload to the new database location. It worked, remarkably well, but yikes. The potential fragility in that solution always scared me.

When I left that company was when static site generators started to take off, but they were all still very techie oriented, with markdown files, rather than any kind of dynamic website to create the content in.

That was one of those rare cases where it felt like I could actually see a potential product & market.

long long ago i never trusted wp or other cms/ blogging software but since i wanted to play with all of them at the time i would just download the html it outputs delete the php files and replaced them with the static html. Adding new content either involved repeating the process or i wrote some simple newpost.php that would update all the html files. the traffic to the admin area looking to run known exploits was quite funny.

I suppose one could automate the process and temporarily de-weaponize the website to enable updates then swap the html back in when done.

This is an excellent plugin though the big problem for me was that it wasn't using 'wal' mode to connect to the database.

This can be fixed by adding the following in db.php at the end

  $GLOBALS['wpdb']->query('PRAGMA journal_mode = wal;');

> SQLite has the following noticeable features: self-contained, serverless, zero-configuration, transactional.

This "serverless" part confused me initially, since that term has somehow come to mean "the platform handles routing and scaling up/down your servers".

But this use of the term, "there is no server process", is much more sensible!

Good one! This just makes sense to me.

WP is high on reads, low on writes and caches easily. It would also make backups so much easier and without the need to rely on wacky, untrustworthy WP plugins.

I’ve long wanted to write a proxy server that would translate MySQL ddl/sql to PostgreSQL using the actual logic/code stripped from both engines. Iirc, when I looked into it the Postgres syntax was available more or less as an OSS standalone module but the MySQL backed was trickier to isolate so I gave up on that. That would let me move the PHP parts of our website (WordPress, vB/XenForo, etc) to match our custom appliances all running on postgres.

Amazon did this in reverse; if I’m not misremembering their Postgres support is actually a shim that translates pgsql queries to those compatible with their Aurora db, a MySQL derivative. I don’t know if they only support the subset of operations that can be translated one-to-one or if they also added some core features to support Postgres-specific functionality.

An interesting case is going from SQLite to anything else - you can actually just create a VFS (SQLite storage abstraction plugged directly into the library/engine) to store the underlying data on a remote MySQL/PostgreSQL db altogether without translating anything. Reads and writes would continue to go through the SQLite api but end up stored on the rdbms of your choice, locally or remotely.

I wanted something similar for a different reason. Basically a parser for any SQL flavour into a common AST format + a dumb executor. Dumb as in tables are lists of tuples, there are no optimisations and everything is single threaded. It would still be a great common interface to quickly run software tests against. I hate the current options for unit testing with real queries which is basically "just spawn a database".

This isn't something one can actually recommend, right? It's not officially supported, probably not super well tested(?), could easily break at any point, won't work with various plugins/tools, etc.

It'd be great of WordPress supported SQLite officially but I don't think I'd recommend this approach to anyone for a real site, even a personal one.

Nice side effect: SQLite's SQL is almost entirely compatible with Postgresql. This probably allows one to use Postgresql instead of Mysql.

How do "almost" and "probably" shake out in real life?

From experience, upgrading a custom CMS with handwritten SQL (that had grown organically for around eight years) from SQLite to Postgres basically involved only double-quoting non-lowercase table/column names.

It's not really closer IMO. The most obvious difference between PostgreSQL and MySQL is quotes. But you can have MySQL use ANSI quotes as a per-connection setting. PostgreSQL and MySQL both have advanced date manipulation functions and SQLite not so much. MySQL and PostgreSQL have much bigger standard library of functions than SQLite. SQLite comes with hardly any aggregation methods (stddev, median, mode, percentile, etc.).

Yes you can compile some of these in as extensions but for example even the sqlite3 CLI you have from your distro comes with almost none of these extensions.

GP is saying SQLite and Postgres are the ones with similar syntax.

The problem is that it’s designed so you can go from SQLite to pgsql, not the other way around - as you mention, SQLite is lean on features (and associated syntax).

Please don't use this project as it makes no attempt to avoid the database file from being directly downloaded. Any sane PHP project (of witch WP is not one of) has it's index.php (and any user-facing stuff) inside a 'public/' folder, never exposing the entire project and relying on the webserver to secure things with .htaccess rules.

Would be great if this is do using a patch file.

Now it's near impossible to see what changes they do over original WP core.

You could get WP "5.9.3" (https://github.com/stokry/wp-sqlite/blob/main/wp-includes/ve...) from upstream and diff -ru them, i think? But yes, ongoing maintenance and following upstream might be more difficult then necessary with this fork

I'm already evaluating changes with macos FileMerge.

Almost all of the relevant code is in wp-content/db.php. It's using Wordpress's hook that lets you use custom code to connect to the database. The code is rewriting MySQL flavored SQL into SQLite flavored SQL which is impressive but potentially flaky. I'd want to see how well it handles SQL from popular plugins before I'd start building a website using this.

If you want a simple blog with little extras, I'd expect this plugin to work just fine.

The predecessor plugin, SQLite Integration, was fairly stable with most other plugins, but plugins trying to do unconventional stuff or hit the DB directly would refuse to work. The main issue was that the plugin author stopped updating the plugin, while newer versions of WordPress didn't suppport it.

So I guess this is a fork? It's not clear to me how this would keep up with changes to WordPress core, especially since the commit history is basically flat so there's no easy way to see the diff between this and the original.

Answered by this comment - it's not a fork, it's a pre-configured Wordpress instance using an existing SQLite plugin: https://news.ycombinator.com/item?id=31398700

> WordPress is much faster and secure with this kind of implementation.

Is this making a comparison, or is it just saying it's fast and secure in absolute terms? I have a feeling there's not much of a difference either way

Cloudflare's announcement from a few days ago (D1, SQLite databases for workers) and now this... It's almost like if the universe wanted to guide us into deploying WP into Cloudflare's workers :)

Glad to see SQLite used in more places!

Very interesting. I will have to check this out for local development via DDEV!

Reply on news.ycombinator.com