dotnet sqlite

How to use SQLite db as an embedded resource in .NET

· 3 min read · Updated July 14, 2024
How to use SQLite db as an embedded resource in .NET
Photo by Hunter Harritt on Unsplash

I was quite intrigued by the recent version of [Bun](https://bun.sh/docs/bundler/executables#embedding-sqlite-databases) supporting a one liner call to embed a SQLite file and use it as part of a single file binary. This is super cool. See the code below:

import myEmbeddedDb from "./my.db" with {type: "sqlite", embed: "true"};

console.log(myEmbeddedDb.query("select * from users LIMIT 1").get());

Even with Golang, you can pull of an embedded database easily using https://pkg.go.dev/embed with few line of code.

This piqued my interest to look at how would I achieve the same if I am using .NET. The solution is definitely not a one liner but a little more involved. Read further.

Also I am sure, this question might be lingering in your mind

Why would I even want to embed a SQLite database?

You would use this mechanism in the following scenarios:

  • When you are building some demo app or site, you might want use a database seeded with demo data which can be readily used.

  • When you allow users to play with the app, the demo data would get changed and you may want to periodically reset it to a pristine state etc.

With Microsoft.Data.Sqlite, there is no way to load a stream gotten from an embedded resource into an in-memory SQLite database. So we are left with a slightly convoluted approach as outlined below:

  • Read the embedded resource content as a stream and write it to a temporary file

    // SQLite file northwind.db is added as an embedded resource
// Using https://github.com/jpwhite3/northwind-SQLite3/blob/main/dist/northwind.db
var resourceName = 'MyNamespace.northwind.db'
// create a temporary file with the content of the embedded resource
var tempFileName = Path.GetTempFileName();
using var resourceStream = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName)
            ?? throw new FileNotFoundException($"Embedded resource {resourceName} not found.");

using var tempStream = File.Create(tempFileName);

// We are using a buffer to efficiently copy possibly large files.
const int bufferSize = 8 * 1024; // 8 KB buffer (adjust as needed)
var buffer = new byte[bufferSize]; 

int bytesRead;
while ((bytesRead = resourceStream.Read(buffer, 0, buffer.Length)) > 0)
{
    tempStream.Write(buffer, 0, bytesRead);
}

  • Load the file into a temporary SqliteConnection

    using var tempConnection = new SqliteConnection($"Data Source={tempFileName}");
tempConnection.Open();

  • Create an in-memory connection and copy the database using BackupDatabase() method. Also note that the reason why we are doing a BackupDatabase from temp file to in-memory is that we don’t want the temporary file to be lingering for longer and want to use the in-memory one to reduce the opportunity for someone wanting to access the file and read it while the app is running.

    // temporary connection to load the db from temp file
var tempConnection = new SqliteConnection($"Data Source={tempFileName}");
tempConnection.Open();

// create an in-memory connection
using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();

// backup db from temp connection to in-memory one
tempConnection.BackupDatabase(targetConnection);

// close the temporary connection and delete the temp file
tempConnection.Close()
File.Delete(tempFileName);

  • Now, we can run queries as below

    using var command = new SqliteCommand("select * from Products limit 10", connection);
using var reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader["ProductId"]}{reader["ProductName"]}");
}

    Note that the state of the in-memory database is gone once the app is closed. As outlined earlier, this is a good feature for demo type functionality where we want to reset the whole data on to the initial state of database easily just by restarting the application.

#dotnet #sqlite