How to Connect a SQLite Database to EF Core

In this article, we’ll discuss connecting a SQLite database to an ASP.NET Core Web API using EF Core.

So, let’s get going.

Introduction to SQLite

SQLite is a compact and efficient relational database management system that can be embedded directly into applications. The entire database is self-contained in a single disk file. As a result, it is an excellent choice for mobile and standalone applications that need to persist data without the overhead of a separate database server.

SQLite supports most of the SQL standard features, such as Atomic, Consistent, Isolated, and Durable (ACID) transactions for ensuring data integrity. We can copy the single database file for backup or migration purposes, making it super portable. Additionally, its dynamic typing system allows data to be stored in any column, providing flexibility in data schema design.

SQLite is an excellent choice for lightweight, local storage needs, especially where simplicity and minimal configuration are critical. For local storage with moderate data size and access requirements, it stands out for its simplicity, ease of integration, and robustness. Consequently, it’s a favorite among the developer community.

Configure the Project to Use SQLite

Now, let’s examine the steps to configure SQLite in an ASP.NET Core Web API project using EF Core.

Firstly, we’ll create a new ASP.NET Core Web API project using the dotnet new webapp CLI command or Visual Studio.

After that, let’s install a few NuGet packages:

• Microsoft.EntityFrameworkCore.Sqlite – The EF Core provider for using SQLite as the database engine
• Microsoft.EntityFrameworkCore.Design – Provides design-time services like migrations to manage the database schema
• Microsoft.EntityFrameworkCore.Tools – Adds EF Core tools in Visual Studio for creating migrations and updating the database

Once we add these packages, we can work with the SQLite provider of EF Core in the project.

Configure the Models and DbContext

Now, it’s time to create the models and DbContext for our application.

First, let’s create a Product model with three properties for Id, Name, and Price:

public record Product(int Id, string Name, decimal Price);

Next, let’s add our SQLite connection string to the appsettings.json file:

{
  ...
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=app.db"
  },
  ...
}

Here, we define a connection string named DefaultConnection that specifies the SQLite database file app.db as the data source for the application.

Now, let’s configure the ApplicationDbContext to use SQLite by registering the DbContext in the Program class:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlite(
        builder.Configuration.GetConnectionString("DefaultConnection"));
});

The next step is to create a migration using the EF Core tools to set up the initial database schema based on our models.

For that, let’s open the Package Manager Console in Visual Studio and run the Add-Migration command:

Add-Migration InitialCreate

This step creates a migration in the application’s Migrations folder.

After that, let’s apply the migration to create the SQLite database and schema by running the Update-Database command:

Update-Database

This creates our application’s app.db SQLite database with the specified schema. 

Create the API Endpoints 

Next, we will create the API endpoints for performing the CRUD operations.

Let’s define a ProductsController class:

[Route("api/[controller]")]
[ApiController]
public class ProductsController(ApplicationDbContext context) : ControllerBase
{
}

Here, we inject the ApplicationDbContext into the controller to perform CRUD operations.

Now, let’s create a GET endpoint method:

[HttpGet]
public ActionResult<IEnumerable<Product>> GetProducts()
{
    return _context.Products;
}

The GetProducts() endpoint method retrieves and returns a list of all products from the data context.

After that, let’s create the POST endpoint method:

[HttpPost]
public ActionResult<Product> PostProduct(Product product)
{
    _context.Products.Add(product);
    _context.SaveChanges();

    return CreatedAtAction("GetProduct", new { id = product.Id }, product);
}

The PostProduct() method adds a new product to the database and returns a response indicating the product was created, including its Id.

Both the above methods interact with the DbContext to perform the operations in the underlying SQLite database and return an appropriate HTTP response.

Test the API Endpoints

Let’s test the API endpoints to ensure that our application can store and retrieve data from the SQLite database.

First, let’s create a new product by invoking the POST endpoint:

curl -X 'POST'
  'https://localhost:7146/api/Products' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '{
  "id": 1,
  "name": "Bread",
  "price": 5
}'

Here, we use curl to create a new Product entry in the database and returns a 201 Created response.

Next, let’s invoke the GET endpoint to fetch all the products from the database:

curl -X 'GET' \
  'https://localhost:7146/api/Products' \
  -H 'accept: text/plain'

With this operation, we return the Product entry that we just created:

[
  {
    "id": 1,
    "name": "Bread",
    "price": 5
  }
]

We can see that the product record is saved and retrieved from the database.

When to Use SQLite

SQLite is a great database option when we need something simple and quick to set up. It works well for mobile and desktop apps, requiring a lightweight and efficient solution. A key feature of SQLite is that it allows us to include the entire database with the application. This means everything needed for the app to run is in one package, making it easier to deploy and reducing the chance of errors.

Another big benefit of SQLite is its portability. We can easily copy and move the database between different systems, which is perfect for cross-platform apps. Also, it is especially helpful when dealing with complex data structures that need to be stored in a way that works with SQL queries, giving us the flexibility to manage data easily.

SQLite is also useful for software testing and prototyping. It removes the need to set up a separate database server, which can be complicated and time-consuming. By using SQLite, we can make our workflows more efficient, allowing for quicker testing and feature development without the burden of managing a server. This straightforward approach saves time and helps keep the focus on development, making it a preferred choice for projects with tight deadlines.

When Not to Use SQLite

While SQLite can work well for many applications, sometimes it’s better to avoid using this lightweight database. One major limitation is its performance in situations where many users are trying to write data simultaneously. When multiple processes try to write to the database, SQLite can slow down, leading to longer wait times and possible conflicts.

SQLite also struggles to support large-scale applications that handle a lot of data. As the amount of information and the complexity of tasks grow, we may face performance issues that can affect transaction processing. Applications that need high speed and low delay for handling transactions may find SQLite lacking.

Another important point is the need for networked database access. If our application requires many users to connect to the database from different locations, SQLite is not the best choice. It does not have built-in features for user access control that are necessary for keeping data secure in a multi-user setting. Therefore, if our application has strict needs for security, data integrity, and user management, SQLite may not be the right solution.

Given these factors, it is often better to choose a more powerful client-server database system, like SQL Server, PostgreSQL, or MySQL. These options offer better scalability, more support for concurrent users, and stronger security features, which are essential for meeting the needs of complex and large-scale applications.

Conclusion

In this article, we learned how to integrate a SQLite database with an ASP.NET Core Web API using EF Core. We started with an introduction to SQLite and then walked through the steps to configure it in an ASP.NET Core project. Then, we discussed building and testing API endpoints for CRUD operations. Finally, we looked at scenarios where SQLite excels and situations where it may fall short.