SQLite vtable extension for enabling storing `wp_posts`/`wp_config` in file system in a fully transparent manner

[vadimkantorov]

This would make easier interop with other blog engines (often SSG) which store posts as regular (often markdown) files and enable putting content text (and even metadata which can be stored as front matter) under simple git version control

---

Another useful feature could be a vtable for storing wp_config as json or yaml file

Configs also would benefit from being checked in version control

---

Using SQLite vtable extension mechanism can bring file-system
storage of posts/config to WordPress without changing any downstream SQL-queries or existing code. These vtable extensions can be implemented in C (or maybe in Rust) - and ChatGPT/Claude can already produce something workable

[JanJakes]

@vadimkantorov Thanks for the ideas; they're interesting indeed! However, is there anything in particular that you'd like to suggest for this repository? Please note that this is an SQLite driver written in PHP, and within this codebase, we have no control over the SQLite setup and extensions the consumers of the driver are using. Therefore, I don't think there's anything to be done for this issue in the SQLite driver, and I'll close the issue for now. If that's not the case, we can reopen.

[vadimkantorov]

within this codebase, we have no control over the SQLite setup and extensions the consumers of the driver are using

In my understanding, this repo also serves as a playground for WP <> SQLite integration in general, that's why I posted here. The vtable extensions can be loaded via calling SQL function:

• https://www.sqlite.org/lang_corefunc.html#load_extension
• https://www.sqlite.org/loadext.html#loading_an_extension

So setting up these features could be implemented as some "prefix" SQL to be executed. Allowing to execute some custom prefix SQL could be a useful feature in general as well. Would this be in-scope for this repo?

---

I think allowing to store/read wp_config and wp_posts in file system is useful in many interop setups, and it seems that SQLite integration could actually be the pathway for this feature :) Allowing to bridge to other storage (file system) without any modification of existing SQL code reading/writing wp_config/wp_posts

I hope that these exts could be also developed / standardized in core WordPress or in the SQLite plugin

[vadimkantorov]

Basically I'm interested whether a proof-of-concept for using SQLite vtable extensions for system WP tables would work. And figuring this out could inform some designs for SQLite integration (like maybe allowing to specify the dynamic extensions to be loaded or allowing to register an SQL to be executed - which would contain the load_ext). Or it might mean that certain DDL need not to execute or that the default physical tables wp_config would need to be dropped. Or how would one pass any config values to these vtable exts.

These may surface up while getting these vtable extensions to work - and could inform the development

[JanJakes]

In my understanding, this repo also serves as a playground for WP <> SQLite integration in general, that's why I posted here.

Currently, this is a WordPress plugin with an SQLite driver written in pure PHP. It's meant to work with any host and platform without the need to customize the PHP environment in any way. The only requirements are PDO, PDO SQLite, and PCRE.

So setting up these features could be implemented as some "prefix" SQL to be executed. Allowing to execute some custom prefix SQL could be a useful feature in general as well. Would this be in-scope for this repo?

It is generally possible to run custom queries in WordPress in many different scenarios using WordPress hooks. Specifically for this plugin, you can also customize the wp-content/db.php file to initialize the connection in any way you want. If you still think there would be a use case for some more extension points, it would be helpful to specify them in detail and describe what use cases it would unlock for the community.

I think allowing to store/read wp_config and wp_posts in file system is useful in many interop setups, and it seems that SQLite integration could actually be the pathway for this feature

This sounds intriguing, but there's a lot of complexity to be explored and considered before it makes sense to look at how to connect all of this with this driver. SQLite stores much more data than just the post contents, and implementing a custom vtable might be quite a project on its own. It will probably make sense to discuss it further once such an extension exists. I guess there's little reason to add some custom code to this plugin for something that currently doesn't even exist.

I hope that these exts could be also developed / standardized in core WordPress or in the SQLite plugin

Neither WordPress Core nor a WordPress plugin is a place for implementing platform-specific logic. It's quite the opposite—WordPress and its plugins are meant to run on any host on a wide range of PHP versions. Projects like SQLite extensions could become community projects, but they will probably never end up in WordPress Core, as WordPress itself has no means of installing them in the environment where it happens to be running, and it keeps its dependencies to the minimum.

If you're interested in storing post content in files rather than implementing a new database storage, I recommend checking out @adamziel's post with a demo that does just that: https://adamadam.blog/2025/01/08/wordpress-as-a-git-repo/

[vadimkantorov]

Yeah, I'll try out writing such a vtable ext when SQLite support is merged into core.

I've tried doing this with ChatGPT, and it produced quite compact C code (should be very portable, at least on unixes) which looked reasonably correct...

So far I'd guess what would be needed is registering a custom sql script to be run after "connecting " to SQLite

[vadimkantorov]

Regarding https://adamadam.blog/2025/01/08/wordpress-as-a-git-repo/ :

It seems very related to my usecases, but it still maintains both database and and the files in the file system and synchronizes between the two.

It appears that it is not on track for being merged in core, the PRs got closed:

• [Data Liberation] Edit static files directly in a git repository wordpress-playground#2109
• [Data Liberation][Huge PR] Git client, static files editor wordpress-playground#2118

Now seems to be developed in https://github.com/wordPress/php-toolkit/

---

I think the advantage of SQLite and vtable extensions for implementing abstracting data storage is that it ensures complete transparency, and no other code (including sql queries in plugins) would need to be modified

But you are right, it would require to have a small amount of C code to be compiled (although one could imagine to have a small C shim which would enable to have several vtable exts to be implemented in php - the shim would need to expose C vtable ext ABI and know how to call into php functions)