SQLite Forum

Native support for pluggable backends in SQLite
Login
Hi Gunther, thanks for the reply.

Virtual tables have many limitations listed here: [https://sqlite.org/vtab.html](https://sqlite.org/vtab.html)

> * One cannot create a trigger on a virtual table.
> * One cannot create additional indices on a virtual table. (Virtual tables can have indices but that must be built into the virtual table implementation. Indices cannot be added separately using CREATE INDEX statements.)
> * One cannot run ALTER TABLE ... ADD COLUMN commands against a virtual table. 

Also just the general syntax is different:

```SQL
CREATE VIRTUAL TABLE tablename USING modulename;
-- vs
CREATE TABLE tablename (col1 INTEGER PRIMARY KEY, etc);
```

The docs say:

> Each module-argument is passed as written (as text) into the constructor method of the virtual table implementation when the virtual table is created and that constructor is responsible for parsing and interpreting the arguments. The argument syntax is sufficiently general that a virtual table implementation can, if it wants to, interpret its arguments as column definitions in an ordinary CREATE TABLE statement.

But this is work that a true key-value store doesn't want to do, and would be a source of incompatibilities and bugs.

In short, the virtual table API is too high level for this purpose, and the virtual file system (VFS) API is too low level.

There are several projects which replace SQLite's native btree with other kv stores, in an ad hoc way:

* [https://github.com/LMDB/sqlightning](https://github.com/LMDB/sqlightning)
* [https://github.com/LumoSQL/LumoSQL](https://github.com/LumoSQL/LumoSQL)
* [https://docs.oracle.com/cd/E17276_01/html/bdb-sql/dbsqlbasics.html](https://docs.oracle.com/cd/E17276_01/html/bdb-sql/dbsqlbasics.html)
* [https://www.sqlite.org/src4/doc/trunk/www/design.wiki](https://www.sqlite.org/src4/doc/trunk/www/design.wiki) :)

Unifying these under a single official API would make things more robust and flexible. It would also probably help SQLite's own code quality (and testing?), by enforcing a clean layering. And it might help SQLite get a built-in LSM-tree back-end in the future. :)

(I'm sorry if the title of the thread was too general and lead to confusion. I was trying to avoid saying "key-value store" for people who might not be familiar.)

Ben