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

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.

Babu Annamalai's Blog

Babu Annamalai's Blog

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