6 ms·

WordPress to support SQLite back end

164 points by eamann 4y ago

jraph 4y ago
Nice. That will simplify my installs and my backups and their reliability. There are not many writes, very few concurrent ones, accesses are mostly cached using a WP cache extension. I have a lot of trust in SQLite's reliability. It should fit right in. WordPress is the only reason MariaDB is installed on my servers. Nextcloud too, actually, and SQLite might not be a good idea for this.
maxpert 4y ago
I will be testing it with Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) to help scale Wordpress when needed! IMO Wordpress + SQLite is a no brainer for 95% of its sites. Hopefully do a demo video. Marmot will help folks scale their sites without central MySQLs.
TIPSIO 4y ago
What's the benefit?
- gjulianm 4y ago
  I assume that easier installation, reduced attack surface, possibly more lightweight (regarding server load).
- zimpenfish 4y ago
  You don't have to spin up a MySQL (or Postgres) instance which is a moving part which doesn't need looking after. Obvs. you'll still need SQLite backups but they're a lot easier, especially with e.g. litestream.
- dongobread 4y ago
  For me, the ease of migration is huge as well as the ability to run WP on extremely low-end VPS with limited RAM.
  - dpkirchner 4y ago
    Bet you could even host it in a container-based on demand system ala Google Cloud Run (ideally behind a CDN).
- Someone1234 4y ago
  - Entirely file based (and can be reasoned about as such, including backups/restores/migrations). - Reduced attack surface (e.g. no DBMS running on a potentially unsecured port, fewer code-execution vectors). - Reduced maintenance (SQLite's library will be patched as part of WordPress). Will it scale worse? Sure, but a lot of Wordpress installations simply don't need it, in particular if you throw a Wordpress cache (e.g. Super Cache, W3, etc) in front of the thing. You could Wordpress cache and then server cache on top of that (e.g. Cloudflare, CloudFront, etc), and I bet the thing could handle millions+ of impressions.
  - fabian2k 4y ago
    You still have to keep the database nature of the SQLite file in mind when doing backups, as far as I understand it is not safe to just copy the file while the DB is running unless you use snapshots or ensure that nothing is written to the DB during that time. SQLite is still easier to manage than a standalone database server, so it is interesting in any case. But I'm not sure it makes backups easier.
    - deleted 4y ago
      [deleted]
    - duskwuff 4y ago
      Copying the database file is fairly safe, especially for an application that's likely to be read-mostly like WordPress. It could fail if the database was large enough that copying it took a nontrivial amount of time, and the database was modified during the copy operation -- but even then, there's a fair chance it'd be recoverable. SQLite is pretty resilient. The "safe" way of doing the backup would be: sqlite3 wordpress.db ".backup wordpress-backup.db" which writes out a new copy of the database file. Restoring the database would be as simple as renaming the backup to the main file -- the backup is a "real" database file; there's no import operation required.
    - johnchristopher 4y ago
      Well, you don't have to fiddle with drop statements and tag.gz formats and things like that and restoring is just a matter of putting the file back instead of importing huge numbers SQL instructions. You don't depend on php/phpmyadmin or a sql command to get a backup or to run it (no cpu are harmed during the process). Just copy the file. I can see old gotchas disappear like that (can't wait for the new ones !).
CharlesW 4y ago
To save you a click, the actual headline is "Help us test the SQLite implementation", and the news is that WordPress developers are asking (urging!) hosts, plugin authors, and theme developers to test the implementation and report bugs. The most interesting bit for me: "The SQLite code used has been in use for many years and has been battle-tested. We opted to start with a tried solution instead of starting from scratch because many of the problems we would have encountered have already been addressed and solved in the pre-existing implementation."
- newjersey 4y ago
  In theory, yes. In practice, no. I say this because in theory Drupal core supports postgresql 100% In practice, unless you actually comb through the code base if all the modules you install, you will have some that won't work. I have only tried this on Drupal 8 but I doubt much will change with 9 or 10.
  - shallow-mind 4y ago
    > In theory, yes. In practice, no. > I say this because in theory Drupal core supports postgresql 100% > In practice, unless you actually comb through the code base if all the modules you install, you will have some that won't work. I have only tried this on Drupal 8 but I doubt much will change with 9 or 10. Why no one mentions typo3 [1] [1]https://docs.typo3.org/m/typo3/tutorial-getting-started/main/en-us/SystemRequirements/Index.html https://docs.typo3.org/m/typo3/tutorial-getting-started/main...
- achairapart 4y ago
  They basically took this implementation and just adapted it to coding standards: https://github.com/aaemnnosttv/wp-sqlite-db https://github.com/aaemnnosttv/wp-sqlite-db This has been around since some time and is itself a fork of a previous work. The interesting part is that this drop-in replacement (mostly) already works well, there are a few issues that are related to some quirks in the WordPress core itself, for example: https://github.com/aaemnnosttv/wp-sqlite-db/issues/18 https://github.com/aaemnnosttv/wp-sqlite-db/issues/18 And maybe now they will be fixed.
stuckinhell 4y ago
WHOA, suddenly I'm not so interested in django, ruby, or laravel.
- Cyberdog 4y ago
  Presuming by “Ruby” you meant Rails, you probably won’t find WordPress a satisfactory replacement for any of them if you’re more of a developer than a site-builder. As a web development framework, WordPress is nearly useless computers to the others.
- awestroke 4y ago
  Really?
  - stuckinhell 4y ago
    Yea to be honest, I program in .net for a living, but I've been looking for a simple SAAS on the side. I've been looking for a simple plugin type solution. Django, RoR, etc all have the promise of third party packages but honestly most wordpress templates are easy and more feature rich for alot of things. SQLite along with a jam stack service for auth would make my life super easy.
- simonw 4y ago
  Django has run fine on SQLite for well over a decade at this point.
zimpenfish 4y ago
Hopefully this will entice more people into supporting SQLite as their backend DB. There's been more than a few things I've purposefully not installed because they require MySQL (it is forbidden on my servers.)
- Cyberdog 4y ago
  I’ll bite at the bait: I prefer SQLite in most cases too, but why have you forbidden MySQL?
  - zimpenfish 4y ago
    Mainly residual trauma from the early days - I was a big proponent in the early days (back when you could get personal support from Monty) but too many security holes, corruption bugs, etc., lead me to the arms of SQLite + Postgres and I've stayed there ever since. It's entirely possible MySQL is a superb DB engine now and won't cause me any more trauma but ...
    - chrsig 4y ago
      There's a world of difference between the myisam & innodb storage engines. Not encouraging you to return or anything, just throwing it out there that many/most storage related problems were because of myisam.
      - zimpenfish 4y ago
        Oh, definitely, but it was some time after innodb arrived and it brought a different set of (undoubtedly teething) problems. It was kinda damned if you do, damned if you don't at that time. Life was just easier without MySQL.
        Cyberdog 4y ago
        Fair enough I suppose. I guess I've been lucky enough to not encounter any show-stopping problems with MySQL, at least that I can recall. Maybe you started using it well before I did. For what it's worth, in the cases where you need a client-server database, I know that Postgres is supposed to be "better," but MySQL is everywhere and it's always been up for almost any task I've thrown at it. I've found it to always be sufficiently good enough.
rafaelturk 4y ago
Amazing. Just waited 10yrs for this!
haunter 4y ago
At this point I’d just go with a flat file CMS, there are so many good systems nowadays https://github.com/ahadb/flat-file-cms https://github.com/ahadb/flat-file-cms
- yamtaddle 4y ago
  If you're just hosting a blog or simple informational site, there are better options, for sure. ... except that you can just go click a couple buttons, and be ready to start editing your first blog post, right in your browser, with Wordpress, because there are a bunch of easy-to-use hosting solutions. If you're doing much more than that with your site, it can be hard to sell a solution other than Wordpress. The plugin ecosystem and 3rd-party service support is pretty damn good. Consider how few other web platform ecosystems exist that can support individuals and even small teams selling commercial plugins—like, full-time, that's your day job. I think WP's a fucking mess and hate developing for it, but it's hard to advocate for anything else, for a pretty large set of Web use cases.
  - johnchristopher 4y ago
    I usually half joke (who can tell ! I can't !) that nobody got fired for buying IBM, Microsoft or Wordpress when asked why we use wordpress @work.
- CharlesW 4y ago
  For me, SQLite support makes it less likely that I'd switch to a flat-file site generator over WordPress.
  - tyingq 4y ago
    The database code, though, ends up being a crazy mishmash of Wordpress' existing faked-up/string-escaping wpdb_prepare() then auto-translated from mysql syntax to SQLite. I'm sure they will test it pretty well, but it's a bizarre setup.
- deleted 4y ago
  [deleted]
blacksmith_tb 4y ago
This was posted a few days ago[1], I have to say I am interested, as Ghost lost support for SQLite (and I have used that combo happily for years). 1: https://news.ycombinator.com/item?id=34020786 https://news.ycombinator.com/item?id=34020786
SPBS 4y ago
The embedded nature of SQLite is somewhat offset by the stack still requiring PHP + Apache installed, what's one more dependency (MySQL)? All WordPress hosts already have MySQL installed, while installing WordPress from scratch is still a pain (or requires programs that manage that complexity for you e.g. XAMPP).
- simonw 4y ago
  Do you definitely need Apache? I thought PHP worked fine without that these days - or does Wordpress only work with something that supports .htaccess too?
  - interstice 4y ago
    Wordpress worked just fine with nginx when I was self hosting sites 6 or so years ago
  - blowski 4y ago
    Yes, the standard setup for any PHP application in the last 10 years is a webserver (e.g. Apache, nginx, Caddy, lighttpd) in front of PHP FPM. I guess to the OP's point, it does still require PHP, FPM, a number of other PHP extensions, and the webserve all to be installed on the server. At which point, MySQL is just one more dependency. I would argue that SQLite is a little easier to back up and transfer than MySQL, but WordPress does that make easy anyway. I wonder if there's a plan afoot to embed the whole thing inside an Electron app for some kind of portable TiddlyWiki like service.
  - kevmarsden 4y ago
    WordPress also works well with nginx. That's what WordPress.com runs on.
- RedShift1 4y ago
  What does it matter that it requires Apache? It's a super solid webserver, anything else to server it to the web just does the exact same thing.
laveur 4y ago
While I'm glad to see them supporting a DB backend other than MySQL, I would also like to see a PostgreSQL backend get built.
laveur 4y ago
I'm happy to see WP finally supporting a DB other than MySQL. Maybe they'll support PostgreSQL as well.
wplearner53 4y ago
Does anyone has a resource to learn Wordpress? Udemy or Youtube. I am graphic designer , and if I need to publish a site, I want to try WP. Thanks
- timbit42 4y ago
  Just type 'Wordpress' into a search box. There are tons of resources out there for it.
  - wplearner53 4y ago
    Someone that has learned and knows that is worthy. Anyway thank you for the reply.
dang 4y ago
WordPress testing official SQLite Support - https://news.ycombinator.com/item?id=34020786 https://news.ycombinator.com/item?id=34020786 - Dec 2022 (73 comments) Related: Let’s make WordPress officially support SQLite - https://news.ycombinator.com/item?id=32807601 https://news.ycombinator.com/item?id=32807601 - Sept 2022 (115 comments) Wp-SQLite: WordPress running on an SQLite database - https://news.ycombinator.com/item?id=31396732 https://news.ycombinator.com/item?id=31396732 - May 2022 (97 comments)

164 points by eamann 4y ago

Nice. That will simplify my installs and my backups and their reliability. There are not many writes, very few concurrent ones, accesses are mostly cached using a WP cache extension. I have a lot of trust in SQLite's reliability. It should fit right in. WordPress is the only reason MariaDB is installed on my servers. Nextcloud too, actually, and SQLite might not be a good idea for this.

maxpert 4y ago

I will be testing it with Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) to help scale Wordpress when needed! IMO Wordpress + SQLite is a no brainer for 95% of its sites. Hopefully do a demo video. Marmot will help folks scale their sites without central MySQLs.

TIPSIO 4y ago

What's the benefit?

gjulianm 4y ago
I assume that easier installation, reduced attack surface, possibly more lightweight (regarding server load).
zimpenfish 4y ago
You don't have to spin up a MySQL (or Postgres) instance which is a moving part which doesn't need looking after. Obvs. you'll still need SQLite backups but they're a lot easier, especially with e.g. litestream.
dongobread 4y ago
For me, the ease of migration is huge as well as the ability to run WP on extremely low-end VPS with limited RAM.
- dpkirchner 4y ago
  Bet you could even host it in a container-based on demand system ala Google Cloud Run (ideally behind a CDN).
Someone1234 4y ago
- Entirely file based (and can be reasoned about as such, including backups/restores/migrations). - Reduced attack surface (e.g. no DBMS running on a potentially unsecured port, fewer code-execution vectors). - Reduced maintenance (SQLite's library will be patched as part of WordPress). Will it scale worse? Sure, but a lot of Wordpress installations simply don't need it, in particular if you throw a Wordpress cache (e.g. Super Cache, W3, etc) in front of the thing. You could Wordpress cache and then server cache on top of that (e.g. Cloudflare, CloudFront, etc), and I bet the thing could handle millions+ of impressions.
- fabian2k 4y ago
  You still have to keep the database nature of the SQLite file in mind when doing backups, as far as I understand it is not safe to just copy the file while the DB is running unless you use snapshots or ensure that nothing is written to the DB during that time. SQLite is still easier to manage than a standalone database server, so it is interesting in any case. But I'm not sure it makes backups easier.
  - deleted 4y ago
    [deleted]
  - duskwuff 4y ago
    Copying the database file is fairly safe, especially for an application that's likely to be read-mostly like WordPress. It could fail if the database was large enough that copying it took a nontrivial amount of time, and the database was modified during the copy operation -- but even then, there's a fair chance it'd be recoverable. SQLite is pretty resilient. The "safe" way of doing the backup would be: sqlite3 wordpress.db ".backup wordpress-backup.db" which writes out a new copy of the database file. Restoring the database would be as simple as renaming the backup to the main file -- the backup is a "real" database file; there's no import operation required.
  - johnchristopher 4y ago
    Well, you don't have to fiddle with drop statements and tag.gz formats and things like that and restoring is just a matter of putting the file back instead of importing huge numbers SQL instructions. You don't depend on php/phpmyadmin or a sql command to get a backup or to run it (no cpu are harmed during the process). Just copy the file. I can see old gotchas disappear like that (can't wait for the new ones !).

CharlesW 4y ago

To save you a click, the actual headline is "Help us test the SQLite implementation", and the news is that WordPress developers are asking (urging!) hosts, plugin authors, and theme developers to test the implementation and report bugs. The most interesting bit for me: "The SQLite code used has been in use for many years and has been battle-tested. We opted to start with a tried solution instead of starting from scratch because many of the problems we would have encountered have already been addressed and solved in the pre-existing implementation."

newjersey 4y ago
In theory, yes. In practice, no. I say this because in theory Drupal core supports postgresql 100% In practice, unless you actually comb through the code base if all the modules you install, you will have some that won't work. I have only tried this on Drupal 8 but I doubt much will change with 9 or 10.
- shallow-mind 4y ago
  > In theory, yes. In practice, no. > I say this because in theory Drupal core supports postgresql 100% > In practice, unless you actually comb through the code base if all the modules you install, you will have some that won't work. I have only tried this on Drupal 8 but I doubt much will change with 9 or 10. Why no one mentions typo3 [1] [1]https://docs.typo3.org/m/typo3/tutorial-getting-started/main/en-us/SystemRequirements/Index.html https://docs.typo3.org/m/typo3/tutorial-getting-started/main...
achairapart 4y ago
They basically took this implementation and just adapted it to coding standards: https://github.com/aaemnnosttv/wp-sqlite-db https://github.com/aaemnnosttv/wp-sqlite-db This has been around since some time and is itself a fork of a previous work. The interesting part is that this drop-in replacement (mostly) already works well, there are a few issues that are related to some quirks in the WordPress core itself, for example: https://github.com/aaemnnosttv/wp-sqlite-db/issues/18 https://github.com/aaemnnosttv/wp-sqlite-db/issues/18 And maybe now they will be fixed.

stuckinhell 4y ago

WHOA, suddenly I'm not so interested in django, ruby, or laravel.

Cyberdog 4y ago
Presuming by “Ruby” you meant Rails, you probably won’t find WordPress a satisfactory replacement for any of them if you’re more of a developer than a site-builder. As a web development framework, WordPress is nearly useless computers to the others.
awestroke 4y ago
Really?
- stuckinhell 4y ago
  Yea to be honest, I program in .net for a living, but I've been looking for a simple SAAS on the side. I've been looking for a simple plugin type solution. Django, RoR, etc all have the promise of third party packages but honestly most wordpress templates are easy and more feature rich for alot of things. SQLite along with a jam stack service for auth would make my life super easy.
simonw 4y ago
Django has run fine on SQLite for well over a decade at this point.

zimpenfish 4y ago

Hopefully this will entice more people into supporting SQLite as their backend DB. There's been more than a few things I've purposefully not installed because they require MySQL (it is forbidden on my servers.)

Cyberdog 4y ago
I’ll bite at the bait: I prefer SQLite in most cases too, but why have you forbidden MySQL?
- zimpenfish 4y ago
  Mainly residual trauma from the early days - I was a big proponent in the early days (back when you could get personal support from Monty) but too many security holes, corruption bugs, etc., lead me to the arms of SQLite + Postgres and I've stayed there ever since. It's entirely possible MySQL is a superb DB engine now and won't cause me any more trauma but ...
  - chrsig 4y ago
    There's a world of difference between the myisam & innodb storage engines. Not encouraging you to return or anything, just throwing it out there that many/most storage related problems were because of myisam.
    - zimpenfish 4y ago
      Oh, definitely, but it was some time after innodb arrived and it brought a different set of (undoubtedly teething) problems. It was kinda damned if you do, damned if you don't at that time. Life was just easier without MySQL.
      - Cyberdog 4y ago
        Fair enough I suppose. I guess I've been lucky enough to not encounter any show-stopping problems with MySQL, at least that I can recall. Maybe you started using it well before I did. For what it's worth, in the cases where you need a client-server database, I know that Postgres is supposed to be "better," but MySQL is everywhere and it's always been up for almost any task I've thrown at it. I've found it to always be sufficiently good enough.

rafaelturk 4y ago

Amazing. Just waited 10yrs for this!

haunter 4y ago

At this point I’d just go with a flat file CMS, there are so many good systems nowadays https://github.com/ahadb/flat-file-cms https://github.com/ahadb/flat-file-cms

yamtaddle 4y ago
If you're just hosting a blog or simple informational site, there are better options, for sure. ... except that you can just go click a couple buttons, and be ready to start editing your first blog post, right in your browser, with Wordpress, because there are a bunch of easy-to-use hosting solutions. If you're doing much more than that with your site, it can be hard to sell a solution other than Wordpress. The plugin ecosystem and 3rd-party service support is pretty damn good. Consider how few other web platform ecosystems exist that can support individuals and even small teams selling commercial plugins—like, full-time, that's your day job. I think WP's a fucking mess and hate developing for it, but it's hard to advocate for anything else, for a pretty large set of Web use cases.
- johnchristopher 4y ago
  I usually half joke (who can tell ! I can't !) that nobody got fired for buying IBM, Microsoft or Wordpress when asked why we use wordpress @work.
CharlesW 4y ago
For me, SQLite support makes it less likely that I'd switch to a flat-file site generator over WordPress.
- tyingq 4y ago
  The database code, though, ends up being a crazy mishmash of Wordpress' existing faked-up/string-escaping wpdb_prepare() then auto-translated from mysql syntax to SQLite. I'm sure they will test it pretty well, but it's a bizarre setup.
deleted 4y ago
[deleted]

blacksmith_tb 4y ago

This was posted a few days ago[1], I have to say I am interested, as Ghost lost support for SQLite (and I have used that combo happily for years). 1: https://news.ycombinator.com/item?id=34020786 https://news.ycombinator.com/item?id=34020786

SPBS 4y ago

The embedded nature of SQLite is somewhat offset by the stack still requiring PHP + Apache installed, what's one more dependency (MySQL)? All WordPress hosts already have MySQL installed, while installing WordPress from scratch is still a pain (or requires programs that manage that complexity for you e.g. XAMPP).

simonw 4y ago
Do you definitely need Apache? I thought PHP worked fine without that these days - or does Wordpress only work with something that supports .htaccess too?
- interstice 4y ago
  Wordpress worked just fine with nginx when I was self hosting sites 6 or so years ago
- blowski 4y ago
  Yes, the standard setup for any PHP application in the last 10 years is a webserver (e.g. Apache, nginx, Caddy, lighttpd) in front of PHP FPM. I guess to the OP's point, it does still require PHP, FPM, a number of other PHP extensions, and the webserve all to be installed on the server. At which point, MySQL is just one more dependency. I would argue that SQLite is a little easier to back up and transfer than MySQL, but WordPress does that make easy anyway. I wonder if there's a plan afoot to embed the whole thing inside an Electron app for some kind of portable TiddlyWiki like service.
- kevmarsden 4y ago
  WordPress also works well with nginx. That's what WordPress.com runs on.
RedShift1 4y ago
What does it matter that it requires Apache? It's a super solid webserver, anything else to server it to the web just does the exact same thing.

laveur 4y ago

While I'm glad to see them supporting a DB backend other than MySQL, I would also like to see a PostgreSQL backend get built.

laveur 4y ago

I'm happy to see WP finally supporting a DB other than MySQL. Maybe they'll support PostgreSQL as well.

wplearner53 4y ago

Does anyone has a resource to learn Wordpress? Udemy or Youtube. I am graphic designer , and if I need to publish a site, I want to try WP. Thanks

timbit42 4y ago
Just type 'Wordpress' into a search box. There are tons of resources out there for it.
- wplearner53 4y ago
  Someone that has learned and knows that is worthy. Anyway thank you for the reply.

dang 4y ago

WordPress testing official SQLite Support - https://news.ycombinator.com/item?id=34020786 https://news.ycombinator.com/item?id=34020786 - Dec 2022 (73 comments) Related: Let’s make WordPress officially support SQLite - https://news.ycombinator.com/item?id=32807601 https://news.ycombinator.com/item?id=32807601 - Sept 2022 (115 comments) Wp-SQLite: WordPress running on an SQLite database - https://news.ycombinator.com/item?id=31396732 https://news.ycombinator.com/item?id=31396732 - May 2022 (97 comments)