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.
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.
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.
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.
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!
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.
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.
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.
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.
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.)
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.
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.
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.
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.
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).
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.
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).
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.
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.
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.
> 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!
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.
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).
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.
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.
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.
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 :)
In those cases SQLite will probably have much better speed and a lower memory footprint.