/ Adam's Pile of Hacks / blog

A native, static binary with SQLite support in C#

January 3, 2024

This is what I needed to do to get a completely static binary that will run on most (all?) Linux distros, regardless of what weird or ancient libc is on the system.

The project file - where most of the hard parts happened:

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net8.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
        <PublishAot>true</PublishAot>
        <InvariantGlobalization>true</InvariantGlobalization>
        <StaticExecutable>true</StaticExecutable>
        <StripSymbols>true</StripSymbols>
    </PropertyGroup>
    
    <ItemGroup>
        <PackageReference Include="Microsoft.Data.Sqlite.Core" Version="8.0.0"/>
        <PackageReference Include="SQLitePCLRaw.provider.sqlite3" Version="2.1.7"/>
        <DirectPInvoke Include="sqlite3"/>
        <NativeLibrary Include="/usr/lib/libsqlite3.a"/>
    </ItemGroup>
</Project>

PublishAot is set in Visual Studio when you enable AOT, but Visual Studio doesn’t run on Linux and AOT isn’t supported when cross-compiling.

InvariantGlobalization prevents the following error message:

Process terminated. Couldn't find a valid ICU package installed on the system. Please install libicu (or icu-libs) using your package manager and try again. Alternatively you can set the configuration flag System.Globalization.Invariant to true if you want to run with no globalization support. Please see https://aka.ms/dotnet-missing-libicu for more information.
Aborted (core dumped)

The error message isn’t quite right there - I don’t think it’d be able to load the icu library anyway as file and ldd both inform me it’s really a proper static binary, which is unsuprisingly what StaticExecutable is doing.

I’d originally found older documentation that said to pass a LinkerArg of -static, but that’s not correct: https://github.com/dotnet/sdk/issues/37643

Interestingly, another issue linked from there tells me that static linking is forbidden knowledge. This seems to me to be internal Microsoft rules (see references to the ‘crypto board’) leaking out.

Golang and Rust don’t seem to be so concerned about static linking. I can see why Microsoft don’t want to have to chase various binaries every time OpenSSL sneezes, though. Maybe they could fund OpenSSL or LibreSSL or even write their own managed-code implementation.

Anyway, this tiny example doesn’t pull in OpenSSL and would run just fine on an “embeddded” Linux system or in a “distroless” container.

The ItemGroup in the second half of the project file has a couple of tricks:

Unhandled Exception: System.DllNotFoundException: Unable to load shared library 'e_sqlite3' or one of its dependencies. In order to help diagnose loading problems, consider using a tool like strace. If you're using glibc, consider setting the LD_DEBUG environment variable: 
Dynamic loading not supported

Well yeah, I don’t think you can load a dynamic library from a static binary.

More searching yielded https://github.com/dotnet/runtime/issues/83353#issuecomment-1467098841 which leads to a broken link at https://github.com/dotnet/runtime/blob/main/src/coreclr/nativeaot/docs/interop.md#direct-pinvoke-calls which made me need to dig through Git history to find https://github.com/dotnet/runtime/pull/86704 which finally led to https://learn.microsoft.com/en-us/dotnet/core/deploying/native-aot/interop

Skipping back a step we need to:

  1. Tell Microsoft.Data.Sqlite / SQLitePCLRaw to use an SQLite library from the operating system
  2. Statically link said SQLite library
        <DirectPInvoke Include="sqlite3"/>
        <NativeLibrary Include="/usr/lib/libsqlite3.a"/>

…does what we need - although I’d like to know if you can avoid the full path to the .a file.

https://github.com/dotnet/runtime/issues/95319 shows how to make this a bit more robust and check what platform you’re compiling for, but I don’t care for now.

This brings us to the actual code:

using Microsoft.Data.Sqlite;
using SQLitePCL;

namespace ConsoleApp1;

class Program
{
    static void Main(string[] args)
    {
        SqliteConnection conn = new SqliteConnection("Data Source=:memory:");
        SQLitePCL.raw.SetProvider(new SQLite3Provider_sqlite3());
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT sqlite_version()";
        var version = cmd.ExecuteScalar();
        
        Console.WriteLine($"Hello from sqlite {version}");
    }
}

The only thing of note there is SQLitePCL.raw.SetProvider(new SQLite3Provider_sqlite3());, needed because we’re not using a “batteries included” SQLite package from NuGet.

I use the following Dockerfile to build this, but you could absolutely do it without it, you might just have to mess with a CC variable to point to a musl GCC. Okay, you could also do this with glibc, but do you really want to statically link the whole of glibc?

FROM mcr.microsoft.com/dotnet/nightly/sdk:8.0-alpine-aot as build
RUN apk add zlib-static sqlite-static
COPY . /src
WORKDIR /src
RUN dotnet publish -o /build /

FROM scratch
COPY --from=build /build

(Future readers: you probably won’t need the /nightly/)

One docker build . -o out/ and we have the following:

file out/ConsoleApp1
out/ConsoleApp1: ELF 64-bit LSB pie executable, x86-64, version 1 (SYSV), static-pie linked, BuildID[sha1]=fd06ce63f8eb3b954225c6b4b4162fe3fc221483, with debug_info, not stripped

(I didn’t strip the binary while I was working this out so I could use nm and look to see if I could see symbols from sqlite itself)

The resulting binary is 9.3M with symbols and 4.1M without. For comparison, go 1.21.5 with similar code produced a 7M debug binary and 3.6M stripped.

I’ll take the debugging experience of Visual Studio and not having to think about what a GOROOT is over a few hundred kilobytes.

I have not checked whether package trimming is enabled by default in .NET 8, so you might be able to save some space there too. I wonder if it’s smart enough to lop off the parts of sqlite that aren’t being used.

Stuff I missed initially:

last modified January 12, 2024