11 ms·
Wp-SQLite: WordPress running on an SQLite database
- mobilio 4y agoWould 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.
- phaer 4y agoYou could get WP "5.9.3" (https://github.com/stokry/wp-sqlite/blob/main/wp-includes/version.php#L19 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
- mobilio 4y agoI'm already evaluating changes with macos FileMerge.
- thasmin 4y agoAlmost 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.
- 2000UltraDeluxe 4y agoIf 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.
- discordance 4y agoGood 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.
- eatonphil 4y agoJust 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.
- _joel 4y agoand caching...
- orev 4y agoOutside 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.
- _joel 4y agoIt's not just static files but database calls to construct pages
- eatonphil 4y agoHmm? 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.
- _joel 4y agoI think we're arguing the same point, via different means.
- orev 4y agoJust 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.
- bretthopper 4y agoThere's an existing WP "drop-in" plugin that can be used with Composer: https://github.com/aaemnnosttv/wp-sqlite-db https://github.com/aaemnnosttv/wp-sqlite-db Using that + a WP composer package (like https://packagist.org/packages/roots/wordpress https://packagist.org/packages/roots/wordpress) is much easier to maintain that a full "fork" of WP.
- ValentineC 4y agoNow I'm hopeful that (a fork of) WordPress can eventually run on Cloudflare's new D1 service: https://blog.cloudflare.com/introducing-d1/ https://blog.cloudflare.com/introducing-d1/
- wildfire 4y agoThis 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).
- m-p-3 4y agoGlad to see SQLite used in more places!
- pineconewarrior 4y agoVery interesting. I will have to check this out for local development via DDEV!
- billpg 4y agoThank you. (I wrote this three years ago.) https://billpg.com/dear-wordpress-please-stop-using-mysql/ 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.
- jkingsman 4y agoWhat 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.)
- billpg 4y agoIf running WP this way becomes popular, plugins will adapt and advertise themselves as compatible with SQLite.
- todotask 4y agoI assume there is also a need to backup and deployment for web server configuration too?
- andai 4y agoIt 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.
- sergiosgc 4y agoNice side effect: SQLite's SQL is almost entirely compatible with Postgresql. This probably allows one to use Postgresql instead of Mysql.
- metadat 4y agoHow do "almost" and "probably" shake out in real life?
- ComputerGuru 4y agoFrom 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.
- eatonphil 4y agoIt'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.
- ComputerGuru 4y agoGP 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).
- andix 4y agoWordPress 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.
- andai 4y agoMost WP sites should be static sites.
- aritmo 4y agoAmen.
- freedomben 4y agoThose 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.
- ngrilly 4y agoYes, 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.
- throw0101a 4y ago> Most WP sites should be static sites. https://wordpress.org/plugins/simply-static/ https://wordpress.org/plugins/simply-static/ https://wp2static.com https://wp2static.com
- thunderbong 4y agoThis 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;');
- simonw 4y agoSo 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.
- simonw 4y agoAnswered 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 https://news.ycombinator.com/item?id=31398700
- simonw 4y agoI 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/14f9e917c5538673e3ff9111cede09361777e4a3/src/db.php#L2888 https://github.com/aaemnnosttv/wp-sqlite-db/blob/14f9e917c55...
- arichard123 4y agoYikes. That looks dangerous.
- jkingsman 4y agoYeah 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.
- woodruffw 4y agoThis 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 https://github.com/pingcap/parser [2]: https://github.com/square/mysql-parser https://github.com/square/mysql-parser
- anonymousDan 4y agoOut 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?
- woodruffw 4y ago
- simonw 4y agoThis repository is a bit misleading. It's really just a Wordpress install with this plugin preconfigured: https://github.com/aaemnnosttv/wp-sqlite-db https://github.com/aaemnnosttv/wp-sqlite-db That plugin is where the interesting stuff happens.
- staunch 4y agoThis 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.
- creatonez 4y ago> 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
- cakoose 4y ago> 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!
- ComputerGuru 4y agoI’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.
- viraptor 4y agoI 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".
- develatio 4y agoCloudflare'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 :)
- icemelt8 4y ago
- rvr_ 4y agoPlease 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.