wp-sqlite-db

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.

> - Reduced maintenance (SQLite's library will be patched as part of WordPress). Are you sure about this? I think it's a lot more likely that it'll use PHP's sqlite3 extension. Edit: confirmed, it uses PHP's built-in PDO SQlite3 driver: https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/...

They basically took this implementation and just adapted it to coding standards: 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 And maybe now they will be fixed.

This sqlite abstraction has been working well for me: https://github.com/aaemnnosttv/wp-sqlite-db it’s one file — ‘db.php’ — you swap in for the core file. From there, it’s mostly been seamless. ~20% of the 5k LOC, is “Method to emulate MySQL XXX() function.” Less than that for query parsing, regex & rewrites https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/...

Author here, here's an in-depth writeup on how this works and why it's useful: https://make.wordpress.org/core/2022/09/23/client-side-webas... To answer your questions directly: WebAssembly is the magic sauce that transforms server-side code into client-side code. MySQL unfortunately is not yet supported by WebAssembly, so I applied a plugin that adds SQLite supports to WordPress [0]. The WebAssembly application has its own in-memory filesystem that lives in a specific browser tab and is scraped as soon as you close it. So – technically it exposes db credentials, and even the entire DB, but that you are the only user of that DB so it's okay. > What would the backend look like The only backend is a static file server where the code and the database live. Your browser downloads a copy of the database and allows you to modify it in the curre

I don't think this is a correct take, because most plugins that interact with the database already use the abstraction functions, eg get/set_option(), WP_Query, get_post_meta() and so on. These would all work out of the box. The only plugins that would not work are: 1. Plugins that register their own database tables (however there already exists prior art such as https://github.com/aaemnnosttv/wp-sqlite-db for handling these cases) 2. Plugins that do direct queries against the standard database schema (broadly either for invalid (bad code) or performance (valid but slim use case) reasons) Also, WordPress would of course keep the old query functions around and they would likely add a tag to the plugin repository so authors can mark plugins as supporting thes new ORM features. Great idea in my opinion!

There's a WordPress plugin that adds support for SQLite as an alternative to MySQL. The implementation is (to my mind) simply astonishing: they run regular expressions against the SQL to convert it from MySQL dialect to SQLite! https://github.com/aaemnnosttv/wp-sqlite-db/blob/9a5604cce13... I'm amazed that this works, but apparently it works really well. I would expect it to break the moment it ran into a WordPress plugin that executed custom SQL in a way they hadn't anticipated though.

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.

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...

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.

Someone did write a plugin to have wordpress use SQLite as the backend: https://wordpress.org/plugins/sqlite-integration/ Perhaps not great for production since Wordpress automatically updates itself, and you would have to keep up with any changes. And not just for wordpress, but for any other plugins that use the database. Edit: A single file fork (albeit 5k lines of PHP) of the plugin that looks interesting: https://github.com/aaemnnosttv/wp-sqlite-db