How to easily create your own SQLite Virtual Table

What is a Virtual Table?

“Normal” tables consist of rows and columns. Plenty of data sources – like APIs – don’t quite adhere to this format, but with a little “data massaging” can be made to output data into rows/columns. This is exactly what a virtual table does… SQLite can execute your own programs which have to output data in row/column format – but there are no limits as to from where that data is retrieved from!

Why?

SQL is the “lingua franca of data”. It’s not limited to database gurus or developers – literally almost any function in a company can leverage SQL

We have folks who work on marketing, engineering, architecture, analytics, operations, etc. At the intersection of all of these is data. If you can master our data, you’ll be able to work across the entire company.

https://ryanjohnson83.medium.com/college-students-are-big-on-languages-521fefecb2be

Now imagine being able to use this powerful language on any type of data… not just that data already in your database. Typical examples of VTABLES would be SQLite’s own “CSV” example, which allows you to load a CSV into an SQL table, and use all the powerful SQL queries on your data. Another example I alluded to before is translating APIs into tables which can be queries. This is exactly what the example included in go-sqlite3 does: it queries Github’s API and presents it in the form of a database table.

Sidenote: SQL and DevOps

In the IT world, SQL use is commonplace. However it’s possibly not that well known that SQL is also clawing it’s way into (Sec)DevOps. It started with OSQuery, probably Facebook’s most underrated open source security tool:

Now we’re seeing this concept extending into the cloud too, with companies like Steampipe offering open source tools to extend this concept into the cloud:

Digging a little deeper into tools like OSQuery, it’s no surprise finding SQLite’s Virtual tables at work in products like this:

Learning how to build your own SQLite Virtual Table should sound pretty useful at this point…

Building your own

PS: if you’re after something more server oriented – Postgres’ Foreign Data Wrappers are what you are looking for, and the awesome tcdi/pgx rust library is the place to start!

When it comes to SQLite VTABLES, the easiest way I’ve found is to use the excellent go-sqlite3 library.

The library already has a working example which leverages the Github repository API to create a virtual table which is almost ready to go:

https://github.com/mattn/go-sqlite3/blob/master/_example/vtable/vtable.go

I say “almost” ready to go because as it stands, the example uses an SQL query without any constraints:

select id, full_name, description, html_url from repo

And adding a constraint such as a WHERE clause will cause the example to error out. This is because the example doesn’t include a full implementation of the “Best Index” method. The fix itself is quite easy, a pull request has already been submitted to resolve this.

Compiling Gotcha

Don’t forget to compile your golang code using

go build -tags=sqlite_vtable

The “sqlite_vtable” option is currently missing from the README, though a pull request has been submitted to resolve this

You may also want to change your compiler settings to include the above build tag, for example in VSCode: