Better-sqlite3: Add support for virtual tables

[ceymard]

As said in the title.

Virtual tables are a pretty powerful feature of sqlite, would be incredibly nice to be able to use them in JS too !

[JoshuaWise]

Adding direct support for defining virtual tables in JS would be very complicated and difficult to get right. However, I do plan on (in the near future) adding support for loading extensions at runtime, which will allow you to accomplish the same thing (and much more)

[ceymard]

Loading extensions would mean writing it in C or C++ (or any other language that will produce a shared library), which is a little disappointing, especially when we have such wonderful things as user defined functions.

Kudos on this library by the way, it really unstuck me in my quest of using sqlite effectively with node.

[JoshuaWise]

I've given it some thought, but I've decided it would be a generally bad idea to expose virtual tables to JavaScript. It would add way too much complexity to better-sqlite3 and it would be very difficult for people to use. Making a virtual table is usually something that takes a huge amount of effort to truly get right (protecting against all the edge cases that exist).

However, you're welcome to make virtual tables yourself as SQLite3 extensions, which are now supported in better-sqlite3 version 3.3.0.

And thanks, I'm glad you're enjoying the library! 😃

[ceymard]

Would you eventually consider a pull request for virtual tables ? Or even maybe try to work on some specs on how to bring them to better-sqlite3 ? Maybe we could look at a simpler version at first (no support for sync, begin, etc., maybe even just plain eponymous tables, ...)

This is such a potent mechanism, I find it a little sad to not consider it at all -- especially since python's sqlite bindings have support for it.

I would like to avoid fiddling too much with C/C++ and having a way of creating them through javascript seems really great.

This could of course be a requirable extension, but I feel like this should be part of this library.

[JoshuaWise]

Interesting, I'm not aware of the Python support for them. I'll do some research to see how they manage the complexity. Thanks for bringing that up.

[ceymard]

Here is what I found. It would seem that the core sqlite bindings don't support it, but there is that :

• http://charlesleifer.com/blog/extending-sqlite-with-python/
• https://github.com/coleifer/sqlite-vtfunc
• https://rogerbinns.github.io/apsw/vtable.html

I do not know as of yet which is the best of them.

[ceymard]

It would seem that APSW is the most complete solution, from what I can gather from their docs.

https://rogerbinns.github.io/apsw/index.html
https://github.com/rogerbinns/apsw

[JoshuaWise]

I've decided this feature is out of scope of this package, as it would complicate things way too much. Such a feature could be incorporated into a separate package, etc.

[Prinzhorn]

I assume your decision still stands. But that doesn't keep me from suggesting that a subset of virtual tables (table-valued functions) would be an incredible feature to have. I think table-valued functions are an incredible feature that is very much underutilized (given the lack of support in many libraries). It seems to be possible to implement a single extension (sqlite3_module) that abstracts away the complexity and makes generic table-valued functions work well enough. That's the approach that https://github.com/coleifer/sqlite-vtfunc seems to have taken. It might not be as optimized as a dedicated C extension, but if you need that amount of performance and control you can still go that route.

Here's a simplified example of how the API could look like, inspired by how easy it is to define custom functions (and inspired by https://github.com/coleifer/sqlite-vtfunc of course).

db.table('regex_matches', {
  columns: ['match', 'group'],
  init: (context, text, regex) => {
    context.text = text;
    context.regex = new RegExp(regex, 'g');
  },
  next: context => context.regex.exec(context.text)
});

SELECT regex_matches.match, regex_matches.group
FROM data, regex_matches(data.content, 'stuff=(\w+)')

This is a very simple example but shows how powerful table-valued functions can be to extract structured data. The official json extension is a prime example for that. Even if this never makes it into better-sqlite3, maybe it inspires some discussion.