# Tutorial Three: Add a Database

# Working with Databases

We just learned how to build a basic CRUD application with an in-memory database. Now, we are going to step it up notch and work with a persistent database. Meaning your data will be saved even after you shut down your application.

For this tutorial we will be using SQLite database (opens new window) but, you may use one that works better for you.

# Setup SQLite database

Setup SQLite Database using Entity Framework Core(EF Core)*

Install the following tools and packages

Using .NET CLI / Visual Studio package manager UI, install the following packages:

SQLite EF Core Database Provider (opens new window) : can access many different databases through plug-in libraries called database providers (opens new window). The package below is the SQLite database provider for EF Core.

TodoApi>dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 6.0.0-preview.6.21352.1

Entity Framework Core tools (opens new window): tools for Entity Framework Core perform design-time development tasks. For example, they create migrations, apply migrations, and generate code for a model based on an existing database.

TodoApi>dotnet tool install --global dotnet-ef

Microsoft.EntityFrameworkCore.Design (opens new window) : contains all the design-time logic for EF core to create your database.

TodoApi>dotnet add package Microsoft.EntityFrameworkCore.Design --version 6.0.0-preview.6.21352.1

# Enable database creation

In order to enable database creation they are couple of steps we need to complete:

Set the database connection string.
Migrate your data model (see below) to a SQLite database. Create a data model

class TodoItem
{
    public int Id { get; set; }
    public string? Item { get; set; }
    public bool IsComplete { get; set; }

}

1
2
3
4
5
6
7

Create your database and schema

# Set connection string

In Program.cs below your app builder var builder = WebApplication.CreateBuilder(args); add a connection string.

var connectionString = builder.Configuration.GetConnectionString("todos") ?? "Data Source=todos.db";

# Add your context to your services

In the CRUD portion of this tutorial, we used an in-memory database. Now we are going to replace the in-memory database with a persistent database.

Replace your current in-memory database implementation builder.Services.AddDbContext<TodoDb>(options => options.UseInMemoryDatabase("items")); in your build services with the SQLite one below:

builder.Services.AddSqlite<TodoDb>(connectionString);

# Migrate data model

With EF Core migration tool, you can now start your first migration InitialCreate. In a terminal window, run the migrations command below:

TodoApi> dotnet ef migrations add InitialCreate

EF Core will create a folder called Migrations in your project directory containing two files (see image below)

# Create your database and schema

Now that you have completed the migration, you can use it to create your database and schema. In a terminal window, run the database update command below to apply migrations to a database:

TodoApi> dotnet ef database update

You should see a newly created todos.db file in your project directory (see image below)

# Learn checklist four ✔️

Setup SQLite database
Create a SQLite database
Perform SQLite CRUD operation from our todo api

Your persistent database is set up! Happy coding 😺

← Tutorial Two: My First CRUD App Tutorial Four: Secure your application by adding authentication & authorization →