In-Memory Database with SQLite

Wolf Fact: With 200M+ olfactory cells housed in the nose, a wolf’s sense of smell is their most acute. By smell alone, wolves can locate prey, family members, or enemies. They can also tell if other wolves are nearby if they're male/female, etc.

---

Prerequisites: C#, Visual Studio, basic concepts of database, Web API

What is SQLite? How to install SQLite? How to create a database with it? Why do we need an in-memory DB? And how create it with SQLite?

SQLite: SQLite is a C-language library that provides a SQL database engine that is small, fast, self-contained, high-reliability, and full-featured. SQLite database files are commonly used as containers to transfer rich content between systems and as a long-term archival format for data. SQLite uses dynamic types for tables. It means you can store any value in any column, regardless of the data type.

Install: You can install SQLite from here. I am using Windows, so I used SQLite-tools-win32-x86–3380300.

For Windows

You should choose one based on your OS. You should see the following three files after extracting this zip.

Create a database:

Open command prompt(cmd)

• To create a new database

  sqlite3 DBName.db

• To see a list of the existing databases

  .databases

A database with the name localDB.db

What is an In-Memory Database: The data in a traditional database is stored on disk. When the data is needed, it’s called into the local system memory — or RAM — and then is processed by the CPU. Because it takes time to search for data that resides on disks, bottlenecks frequently occur.

In contrast, an in-memory database stores data directly in system memory, plugging directly into the high-speed, low-latency memory bus. It decreases data latency by reducing the time needed to analyze data. With an in-memory database, data is accessed much quicker, allowing you to analyze large volumes of complex data in real time.

When should you use it? An In-memory database uses RAM for storage, resulting in faster read-write operations. You should consider an in-memory database if

• The target system has data to manage, but no persistent media.

• The performance requirement simply cannot be met with a persistent database

There are a few common examples in which an in-memory database can be useful.

• Caching: You can increase performance by using a small in-memory database( can lose data) with a large disk-based database( reliable).

• Batch Write: You can perform a batch of read-write operations on the in-memory database. You can back up this database to a physical database after a predetermined period( like 10 mins) or a predefined number of operations( like 5000 queries), as needed.

Now let’s write some code.

We have created a physical database “localDB.db”. Now we need to add a table to this. We can do this using a query or manually using the UI. I am using DB Browser for SQLite to perform operations on the database. DB Browser for SQLite (DB4S) is a high-quality, visual, open-source tool to create, design, and edit database files compatible with SQLite. You can download it from here.

After installing DB Browser, We should add the “localDB.db” file to this.

To add db file

To access existing DB

Now let’s create a new table in the localDB.

Create Table TableOne(Id INT, Value Text)

Table Schema

In-memory database with SQLite: We will create an in-memory database in three main steps.

1. Duplicate physical database to in-memory: Copy physical DB schema( like tables ) to in-memory.
2. Operations on the in-memory database: Read-write operation on the in-memory database.
3. Back up the in-memory DB to the physical DB: Update the real database with the current state of the in-memory database. It is quite quick.

The complete source code can be found here. Let’s get started now.

• Create a Web API.

A simple Web API

• Add Nuget package to it.

Microsoft.Data.Sqlite

• Solution File Structure( I tried to keep things as simple as possible.)

File Structure

I mainly created three files. Let’s go over them one by one.

• IDbHelpers: This interface contains method declarations for acquiring physical and in-memory DB connections.

IDbHelpers

• DbHelpers: This class contains implementations for GetPhysicalDbConnection and GetInMemoryDbConnection methods.

DbHelpers

-> dbFilePath: Path to “localDB.db” file

-> GetPhysicalDbConnection(): I set the Data Source to dbfilePath with read-write permissions.

-> GetInMemoryDbConnection(): I set the Data Source to :memory: for in-memory database.

Don’t forget to open the DB connection.

• HomeController: The addRow API endpoint is available in HomeController. I’ll insert a row into TableOne using an in-memory database connection.

I am using SqlCommand in this example. You may use other methods like dapper. If necessary, you can additionally add a transaction to this connection.

Now let’s test it.

Swagger

and DB status

TableOne Data

We can see data has been successfully inserted and backed up into the physical database.

Friendly Suggestion: If you face an error during code execution that the Database is locked. Try closing the DB Browser.

Any comments or suggestions would be greatly appreciated.