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


3 min read

I was quite intrigued by the recent version of [Bun]( 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 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
      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}");
  • 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}");
      // create an in-memory connection
      using var connection = new SqliteConnection("Data Source=:memory:");
      // backup db from temp connection to in-memory one
      // close the temporary connection and delete the temp file
  • 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())

    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.