How to load vfs extensions in Microsoft.Data.Sqlite?

[voltagex]

I am trying to load https://github.com/voltagex-forks/sqlite_zstd_vfs (which is https://github.com/mlin/sqlite_zstd_vfs made to build on Windows). The extension appears to load, but the vfs is not found.

The vfs requires particular URI parameters to be configured, see https://github.com/mlin/sqlite_zstd_vfs#performance

I cannot find any examples of loading or using a VFS extension with Microsoft.Data.Sqlite.

            SqliteConnection conn = new SqliteConnection("Data Source=:memory:");
            conn.EnableExtensions(true);
            conn.LoadExtension(@".\lib\zstd_vfs.dll", "sqlite3_zstdvfs_init");
            conn.ConnectionString = "Data Source=file:test2.db?vfs=zstd&outer_unsafe=true";
            conn.Open();

throws

Microsoft.Data.Sqlite.SqliteException
  HResult=0x80004005
  Message=SQLite Error 1: 'no such vfs: zstd'.
  Source=Microsoft.Data.Sqlite
  StackTrace:
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal..ctor(SqliteConnectionStringBuilder connectionOptions, SqliteConnectionPool pool)
   at Microsoft.Data.Sqlite.SqliteConnectionPool.GetConnection()
   at Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(SqliteConnection outerConnection)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at Test.Program.Main(String[] args) in C:\git\test\Program.cs:line 17

Include version information

Microsoft.Data.Sqlite version: 6.0.4
Target framework: NET 6
Operating system: Windows 10 21H2

[bricelam]

LoadExtension is lazy and only executes when the connection is opened. It should work if you do this:

  SqliteConnection conn = new SqliteConnection("Data Source=:memory:");
  conn.EnableExtensions(true);
  conn.LoadExtension(@".\lib\zstd_vfs.dll", "sqlite3_zstdvfs_init");
+ conn.Open();
+ conn.Close();
  conn.ConnectionString = "Data Source=file:test2.db?vfs=zstd&outer_unsafe=true";
  conn.Open();

[bricelam]

Also, you don't need EnableExtensions--that just allows SELECT load_extension() queries.

[voltagex]

Thank you, loading the extension, opening the connection, closing the connection and opening the connection with the "final" connection string works. Much appreciated, @bricelam.

[bricelam]

Using an extension initializer to register the vfs seems strange to me. But on the other hand, it works, so... 🤷‍♂️

In my mind, registering a VFS should look something like this:

SomeNativeMethodToRegisterTheVFS();
var conn = new SqliteConnection("Data Source=file:test.db?vfs=the-vfs");

// Or

SqliteConnection.RegisterVFS(
    SomeNativeMethodToCreateTheVFS(),
    makeDefault: true);
var conn = new SqliteConnection("Data Source=test.db");

But those are also a lot more complex than just loading the extension on a dummy connection.

[ajcvickers]

Re-opening to confirm this should be closed and to add the correct label.