Skip to content

databases.md

Latest commit

 

History

History
99 lines (64 loc) · 3.96 KB

databases.md

File metadata and controls

99 lines (64 loc) · 3.96 KB
Raw

Tutorial Three: Add a Database

Working with Databases

In Tutorial Two: My First CRUD App, 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. This means that your data will be saved even after you shut down your application.

For this tutorial we will be using SQLite database but you may use any option that you prefer.

Setup SQLite database

Install the following tools and packages

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

SQLite EF Core Database Provider : provides access to multiple different databases using plug-in libraries that are called database providers.

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 : tools that perform design-time development tasks for Entity Framework Core. For example, they create and apply migrations and can generate code for a model based on an existing database.

TodoApi>dotnet tool install --global dotnet-ef

Microsoft.EntityFrameworkCore.Design : 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 create your database, there are a couple of steps we need to complete:

  1. Set the database connection string.
  2. Migrate your data model (see below) to a SQLite database.
  3. Create your database and schema

Create a data model

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

}

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)

image

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)

image

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 😺