sqlite is amazing and Frank Krueger as gone above-and-beyond in creating sqlite-net.

Sometimes however, it doesn’t quite do everything we want. Sometimes you need to load a sqlite extension to add functionality that isn’t built-in.

sqlite-net has a tantalizing EnableLoadExtensionAsync method, but no method to actually load an extension. Here I’ll share what I did to get it working on Windows and macOS. It should also work on iOS and Android but I’ve not tried it yet … when I do I’ll update this post.

Adding the extension library to your project

I’m assuming the extension you want to load has a loadable library … a DLL for Windows or a dylib on macOS. You’ll need to add them to your project:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net9.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="sqlite-net-pcl" Version="1.9.172" />
  </ItemGroup>

  <ItemGroup>
    <None Update="SqliteExtensions\macosarm\vec0.dylib">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="SqliteExtensions\winx86\vec0.dll">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>
</Project>

In this case I have a folder called SqliteExtensions with subfolders for each OS/processor architecture. The “vec0.xxx” is the library itself - I’ll talk more about what it is used for in my post on Getting, storing, and using LLM embeddings in a .NET Console App.

Loading the extension

In my C# Console application I have a method that returns the path to the extension, depending on the platform:

private string GetExtensionName()
{
  // Could also look at RuntimeInformation.ProcessArchitecture;
  if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
  {
    return "SqliteExtensions/winx86/vec0";
  }
  else if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
  {
    return "SqliteExtensions/macosarm/vec0";
  }
  else
  {
    throw new NotSupportedException("Unsupported platform");
  }
}

I then use it to load the extension:

var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Demo.db");

var sqliteFlags = SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create;
var connection = new SQLiteAsyncConnection(databasePath, sqliteFlags);

var extension = GetExtensionName();
await LoadExtension(connection, extension);

That’s not quite it - I’m not going to ask you to draw the rest of the owl… Here is the LoadExtension:

// e_sqlite3 is The sqlite dll from SQLitePCLRaw which sqlite-net depends on
[DllImport("e_sqlite3", EntryPoint = "sqlite3_load_extension", CallingConvention = CallingConvention.Cdecl)]
public static extern Result LoadExtension(SafeHandle db, [MarshalAs(UnmanagedType.LPStr)] string filename, int entry, int msg);
private async Task LoadExtension(SQLiteAsyncConnection connection, string extension)
{
  await connection.EnableLoadExtensionAsync(true);

  var connectionWithLock = connection.GetConnection();
  using var theLock = connectionWithLock.Lock();
  var handle = connectionWithLock.Handle;
  var result = LoadExtension(handle, extension, 0, 0);
  if (result != Result.OK)
  {
    throw new Exception("Failed to load extension: " + result);
  }
}

Thats it - basically we are binding to the native SQLItePCLRaw library’s sqlite3_load_extension function, and then invoking it with sqlite-net’s underlying native handle.

What comes next is actually using the extension - I’ll cover that in my post on Getting, storing, and using LLM embeddings in a .NET Console App along with a complete example on GitHub

Kind comments and suggestions are welcome