WordPress Core to start using SQLite | Hacker News Reader

WordPress Core to start using SQLite

make.wordpress.org

134 pointsJPLeRouzic69 comments

This is good news. It will allow me to drop MariaDB, only used for a few low traffic websites I manage, making my things more robust, more lightweight and easier to maintain.

I don't care for managing ports / sockets, migrating configurations, doing the creating database (careful not to use the not-really-utf-8 charset), user and granting privileges dance. Overkill for my use case.

Backups will also be easier: a simple rsync call will do, no need to call the specific mysql backup command anymore (of course it's automated but that's one less thing that can fail, less moving parts)

I bet we are many people in this case.

> Backups will also be easier: a simple rsync call will do, no need to call the specific mysql command anymore (of course it's automated but that's one less thing that can fail, less moving parts)

Technically, that's incorrect way of doing it; practically it rarely fails (as writes are usually much rarer in many cases SQLite is used, especially if you backup in the middle of the night, and format itself is pretty resilient), but you should be doing one of methods here:

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

I used Wordpress with PostgreSQL in production for a few years, but that was not officially supported by Automattic like this is.

sqlite3 <dbfile> '.backup path/to/backup' && rsync ...

> They suggest that if the plugin grows to a million plus installations, then it would support the desire for SQLite to be introduced in WordPress core. As of publishing this article, the plugin had 30 installations.

This is a poison pill suggestion. I would absolutely switch to SQLite for my blog, but I'm not going to make that commitment using a plugin for something as important and central as the database layer. It's kind of ridiculous to even consider that, to be honest.

With a plugin I have to go through the installation process, install the plugin, migrate the data, and then serve my blog. No one is going to do that. With plugins I'd also be scared that I'd be locked out of upgrades in Wordpress if the plugin lagged behind, and I would definitely worry about the plugin being dropped altogether. Those concerns go away completely if it's built right into Wordpress itself. That's on top of the fact that the plugin explicitly states that it's for testing.

What is this quote from? I can't find it in TFA.

Edit: It looks like at some point the article was changed to point to the official WordPress site, I think this was the original link:

https://blogiestools.com/wordpress-sqlite-database/

Good for them! Perhaps this is an action item that came out of their WordPress Playground: A WordPress that runs entirely in the browser as discussed here:

https://news.ycombinator.com/item?id=36726593

> And if your hosting server does not support SQLite ...

I thought SQLite was file based. What's there to support?

I think you can choose to enable or disable the SQLite driver when compiling php.

Not sure why any host would disable it, but I could see it happening.

A host might disable it if they also offer mysql/postgres services

They’re probably thinking of shared hosting environments that don’t have the SQLite library for PHP installed. That seems like a concern you could raise about any database connector, though

It would be pretty hard to find one without the mysql connector

Maybe something like Heroku?

If a service provider doesn't provide a service you require, then find another provider!

With a bit of effort you can run WP on say a RPi or an Odroid. Use a free dynamic DNS service to get it out there. A couple of NAT port forwards on your router. Lets Encrypt gets you a free SSL cert. A bit of research into web servers gets you an A+ score at SSL labs and a frisson of security! WP has some app firewall style addons and the web server eg apache or nginx have some useful addons and modules.

Self hosting isn't for everyone, obviously. You are not restricted in any geopolitical sense when choosing a provider. If say, you are living in the US, you can rent a VPS in say Germany and crack on.

You can also get quite a lot of Cloudflare for free, for example.

Its 2023 and the options for publishing on the internets are absolutely astonishing. I used to use telnet to get to a rather plain text thingie at CERN back in the day (1994ish). Obviously the estate has changed somewhat since the and we have to deal with some really nasty issues that the early web didn't have.

I argue that self publishing is the only way to go but please either get yourself clued up on the security aspects of IT or hire it in or ensure your external platform is segregated from your non platform stuff (that might be VLANs, for example).

We all have an intrinsic ability and I think an inalienable right to be able to communicate a message. Not all messages are welcome and that is where things get tricky. I think we also have a right to not receive messages that we might find offensive. There is no agreed approach to "offensive". There are several "societal norms" and the like but those change depending on say location or even current mindset.

The internet allows anyone to communicate with anyone, via social media and other madness! That means that a Masai warrior, wandering the veldt and looking for a lion to take on, his asegai trembling in his hand so he can advance to manhood and then his phone finds a base station and he might suddenly be chatting with a child from the Netherlands (they both subscribe to the same Facebook Barbie fan group)! That is obviously nonsense but the world is really, very, very connected these days.

Oh sorry, SQLite? I have no idea why WP hasn't supported it for years. It is quite literally the obvious first choice. How many bloggers do you know that are also closet DBA's?

I'm running Home Assistant on an odroid, and it's using an SQLite database. It's really nice not having to manage a database service, although I'll probably end up piping the data to influxdb anyways.

Crack on mate. We have a huge amount of choice these days. I too am a massive HA fan - I manage six of them - one at home, one in the office and four at customer sites.

My rule one is that any important HA related stuff needs to be local and also fail safe. So my Reolink cameras never access the internet at large. I use PoE switches and a UPS for them. My doorbell is PoE powered too and also UPS backed. It has a wired chime too, so even if HA is dead it will still ring something.

I run HA on a Thinkcentre which is a bit of a gas guzzler but as you say, an Odroid is fine too.

I've got a battery pack for my odroid system, and the whole thing is completely local with the exception of pulling weather data in. My next plan is to build out a voice system around it- there are a lot of great options for this now too.

>The internet allows anyone to communicate with anyone,

Before the days cgnat and country wide firewalls

Is shared hosting at all relevant these days? A VPS costs only a few dollars and gives you complete freedom.

wget /wp-content/database

Laughing deeply in my whole heart.

Also, can't wait to use it (drastically simplify hosting for certain usecases) - hope it will land soon!

Long live Wordpres

Really should be in a parent directory that will never be visible by anyone's browser.

Which is not how wordpress install is set up to work, because of both webhost and wordpress developers incompetence

Before they added SQLite as WP plugin, I would use https://github.com/aaemnnosttv/wp-sqlite-db/ and I would use `define('DB_DIR', '/absolute/custom/path/to/directory/for/sqlite/database/file/');` to define the database location of my choice; I believe they would let users do the same with core support.

The usual mitigation for safely using sqlite in PHP projects is as follows:

- Have a .htaccess to block it. Only works with Apache of course but that covers most shared hosting.

- Have rewrite rules that takes precedence. Only works if the user enables url rewriting (automatic only on Apache)

- Part of the sqlite database file name will be randomized. eg sqlite_xJ4D6e1E3.db. That usually works well but I suppose in theory it can be bruteforced...

- The documentation will recommend it should be placed outside the webroot but the installer won't do it automatically because it can't safely assume the user has access to the parent folder. Realistically not that many people will end up doing that.

I, for one, am still excited to no longer have to deal with questionable plugin to use wordpress on a mysql-free server.

I really wish Wordpress would ditch the shared-hosting first deployment model and grow up a bit.

Thankfully https://roots.io/bedrock/ exists to bridge the gap if you're absolutely forced to use WP.

Hopefully the database file has to be located outside of DocumentRoot so you cannot do that.

Related - WordPress recently released WordPress Playground: https://playground.wordpress.net/ along with a wp-now command-line tool: https://developer.wordpress.com/2023/05/23/wp-now-launch-a-l...

These utilize WebAssembly (php-wasm) and an SQLite database backend to run a whole WordPress instance in the browser or a local Node.js instance.

Url changed from https://blogiestools.com/wordpress-sqlite-database/, which points to this.

Submitters: "Please submit the original source. If a post reports on something found on another site, submit the latter." - https://news.ycombinator.com/newsguidelines.html

Speaking about WordPress, what does anyone do about diffable version control and automated deployment? From my naive perspective, it seems like an opaque database is just a bad idea

Third party providers like Pantheon and Acquia will make their own deployment pipelines and manage pushes and pulls etc for you. The code (theme, plugins) is usually version managed but I don't believe the content is. Normally you'd pull the prod DB down to dev, then push dev code and prod DB together to stage, and then run regression tests (auto and manual) on stage. Then push to prod and hope no editor changed the content in a breaking way in the meantime.

It's not a great system.

You could ask the same question about any application that uses a database. The answer is you typically don’t do things that way.

If all you need is a static site generator with code in git, then go ahead, however the use for Wordpress is an audience who needs a full application to manage a site.

> the use for Wordpress is an audience who needs a full application to manage a site.

The use case for WordPress is businesses who don't really need a website at all but everybody demands that they have one and they want to outsource running a website so you can blame somebody else when it gets hacked into and can tell your marketing and sales department go bother somebody else about the website. <Raises hand>.

Anybody who actually needs an application to manage their site has staff and they don't use WordPress.

> needs a full application to manage a site.

AFAICT it’s not that. It’s that most shops want the ability to edit content without a developer. But such a requirement doesn’t mandate that data lives in a database vs something like, say, a set of plain-text files, that would be amenable to version control and diffing

My personal blog runs on Django + PostgreSQL, and I got fed up of not having a version history of changes I made to my content there.

I solved that by setting up a GitHub repo that mirrors the content from my database to flat files a few times a day and commits any changes.

It's worked out really well so far. It wasn't much trouble to setup and it's now been running for nearly three years, capturing 1400+ changes.

I'd absolutely consider using the same technique for a commercial project in the future:

Latest commits are here: https://github.com/simonw/simonwillisonblog-backup/commits/m...

Workflow is https://github.com/simonw/simonwillisonblog-backup/blob/main...

That is awesome! Did you consider using the temporal_tables extension? I think it's basically like version history for tables. I haven't used this before personally though. https://pgt.dev/extensions/temporal_tables

I didn't - I hadn't seen that extension before, it looks like it could be really useful in the future!

For my purposes here the feature I care most about is backups - having my content backed up to a GitHub repo feels extremely robust, since I know GitHub mirror content to (I believe) three continents.

A version history sounds great, but it's not really the problem I'm talking about.

I'm adding features and content to legacy Wordpress websites where the clients are liable to change config and add and alter content themselves via the admin portal. There seems no easy way to automate deployment from local development into production without accidentally erasing someone's changes, and to keep up to date with production without losing your own work.

It seems like a tricky problem to solve, and maybe a bespoke version control system might be required (e.g., throw CRDTs at the problem?), but essentially I should be able to diff and merge between my local work and the production version while clients are making their own edits.

Do those client edits get persisted to files on disk?

If so I'd be tempted to have a really shonky system - basically run a "git commit -a -m 'Updates' && git push" on an hourly cron, purely to capture their changes.

Merging in changes made elsewhere would still be hard, but at least you would know what was changed by them and when.

In Wordpress those changes get persisted to a MySql database, and now... SQLite? Seems like a lateral move. If they were files, it would be at least possible to diff against the production server, and to abort if there's differences since the last 'snapshot' (forcing the developer to pull them down at a later snapshot position)

- https://roots.io/bedrock/

- https://roots.io/trellis/

Do these tools work well when people are editing content and config from the site vs developers adding features/content by version control? It's this tension that has caused me some grief when working on legacy Wordpress sites

Yes. The content in the database remains the same. Developers can continue creating themes and plugins as they normally would.

Sometimes 'development' isn't as clear cut and that and does involve creating content to drives a custom plugin that's in development. I dream of a day when PHP moves from a database to a file structure of plaintext documents that can be merged and diffed, instead of to another database, and maybe even uses git to commit changes. But it's a pretty wild dream, and it's probably better to just use a different platform for new projects, and just wear the pain for the legacy projects.

I'm happy with any new Wordpress advancement, the only one I still struggle with is Gutenberg which still after however many years of being default leaves a lot to be desired.

Feels like fighting with Microsoft Word sometimes doesn't it?

Initially I felt the same way about Gutenberg, but it’s starting to grow on me. There’s a setting that understands markdown—so it’s easy to copy over posts from Obsidian when they are close to being ready. Plus the layout does just about everything I want with photos and video. I’m content with it.

I've run into more issues with Gutenberg than any other editor, every time I am forced to use it, I want to just abandon WordPress. A very stupid thing I noticed the other day, let's say you have a list, and you want to inline bold and color the entire list. Guess what? You can't. You have to select every list item one by one. Why would anyone design something like that? The old WYSIWYG editor can do that in 5 seconds.

The way it also treats Headings, etc as just different blocks from the rest of the text is so annoying. It takes 2-3 clicks to do something you used to be able to do instantly. It feels so unfinished, you just shouldn't have to spend so much time doing very basic things. Very baffling considering it's been out for so long at this point.

i remember a long time ago we used 3dcart as our ecommerce platform which used MSAccess as the database backend. I laughed my ass off at the time, but came to realize that 99% of their customer were like us... maybe did 5K a month in sales so not a huge amount of traffic so why pay for the license and cost of SQL Server. This is actually a really good move for WP as it will free up alot of resources on the server and simply deployments and migrations.

would be pretty neat to run a wasm wordpress on cloudflare pages or fly.io at the edge, with a copy of the full site db there too.

It's unfortunately awfully slow. Ten times slower. Probably fine if you never use any plugins, but who does that.

Why is that the case? SQLite isn't inherently slower than MySQL for these kinds of read-heavy uses (it's often faster!).

Because their approach is not native SQLite support, it's a translation layer.

Start supporting it natively without a plugin... but most users will still use WordPress with MySQL.

Waiting for obligatory comment of "WP sucks. Who still uses PHP in 2023"..

On a serious note, this is very interesting. SQLite is just awesome and this will be a welcome addition to the core.

Well... it does suck :) is a surprising amount of infrastructure to operate a mostly static website.

The weight of WordPress is a bummer. On the other hand, it usually covers any use-case, even those you don't know yet, while still remaining usable by non-technical people, which is quite a feat.

You did the opposite of waiting; you made it.

This is great. Apps and frameworks should work with the lowest-common denominator of SQL. I get physically ill when I walk into yet another Rails shop to find that they have used every cool feature of Postgres and as a result, the CI must spin up a huge postgres instance and multiple plugins just to run a single unit test. Ugh.

Honest question: why would CI only run a single unit test?

Well, if you're currently writing one unit test, and want to test that test...

Why wouldn’t you use your development medicine? “…”

lol "medicine"... damn phone. I meant "machine" of course.

You are advising application developers against using their chosen database optimally? I think it is not needed, I rarely see an application change its database.

While I agree, it's amusing to see this comment in a thread about an application providing a new database, and where one of the main reservations is around people needing to change the DB.

> WordPress developers who do not agree with the idea of introducing SQLite in WordPress core are mainly concerned about ... migration process or converting from one database to another

Tight coupling is bad, haven't you heard?

I don't know if all the blame goes to postgres or not, but a unit test in Rails is typically what others would call an integration test and Rails' architecture is more to blame than SQL. If someone chooses to use postgres in their application, don't be surprised when you see code that uses postgres. Wanting to force others to make applications that work with the lowest-common denominator of SQL could make one appear naive, and that you might not have ever faced the same problems others developers have faced.

> Apps and frameworks should work with the lowest-common denominator of SQL.

Pointless limitation that will make your app slower and SQL code worse.

> I get physically ill when I walk into yet another Rails shop to find that they have used every cool feature of Postgres and as a result, the CI must spin up a huge postgres instance and multiple plugins just to run a single unit test. Ugh.

shrug. We (not rails shop) just create temporary database, pass it to CI test, remove after. Picking database because your CI is done badly is like one of the worst ways to decide on architecture

shrug. a lot of systems have been in place long before modern CI

Assuming they can just "do it" is a bad assumption of both the depth of their existing stack and their companies budget for migrating.

I'm gonna guess you haven't been around long enough to have FTP'd files as a deployment.

> Pointless limitation that will make your app slower and SQL code worse

That seems to be the common opinion, but I don't think that it's based on anything tangible.

Reply on news.ycombinator.com