-
Notifications
You must be signed in to change notification settings - Fork 20
/
Copy pathdatabases.html
60 lines (57 loc) · 20.1 KB
/
databases.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<!DOCTYPE html>
<html lang="en-US">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Tutorial Three: Add a Database | Minimal APIs</title>
<meta name="generator" content="VuePress 1.9.7">
<meta name="description" content="Building APIs in C#">
<meta name="theme-color" content="#d9C5C1">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black">
<link rel="preload" href="/assets/css/0.styles.c9c98798.css" as="style"><link rel="preload" href="/assets/js/app.7c598cc9.js" as="script"><link rel="preload" href="/assets/js/2.2bd089a4.js" as="script"><link rel="preload" href="/assets/js/22.cd43dcc4.js" as="script"><link rel="prefetch" href="/assets/js/10.5d8d1e81.js"><link rel="prefetch" href="/assets/js/11.add2209d.js"><link rel="prefetch" href="/assets/js/12.7aa50d5b.js"><link rel="prefetch" href="/assets/js/13.d91548c1.js"><link rel="prefetch" href="/assets/js/14.40f9daca.js"><link rel="prefetch" href="/assets/js/15.afaafacf.js"><link rel="prefetch" href="/assets/js/16.0c95cc17.js"><link rel="prefetch" href="/assets/js/17.06f7bb9b.js"><link rel="prefetch" href="/assets/js/18.0e07dd6a.js"><link rel="prefetch" href="/assets/js/19.5e96ddbc.js"><link rel="prefetch" href="/assets/js/20.eba95d43.js"><link rel="prefetch" href="/assets/js/21.e40cf0df.js"><link rel="prefetch" href="/assets/js/23.8da194c6.js"><link rel="prefetch" href="/assets/js/24.3ad48732.js"><link rel="prefetch" href="/assets/js/3.2e7fa9cd.js"><link rel="prefetch" href="/assets/js/4.9d8f745a.js"><link rel="prefetch" href="/assets/js/5.0ac92ac3.js"><link rel="prefetch" href="/assets/js/6.1fae0437.js"><link rel="prefetch" href="/assets/js/7.2d00f8e9.js"><link rel="prefetch" href="/assets/js/8.aecd003b.js"><link rel="prefetch" href="/assets/js/9.4a670d5b.js">
<link rel="stylesheet" href="/assets/css/0.styles.c9c98798.css">
</head>
<body>
<div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/logo.png" alt="Minimal APIs" class="logo"> <span class="site-name can-hide">Minimal APIs</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/hello-minimal/" class="nav-link">
Hello Minimal
</a></div><div class="nav-item"><a href="/quickstart/" class="nav-link">
Getting Started
</a></div><div class="nav-item"><a href="/tutorials/" class="nav-link router-link-active">
Tutorials
</a></div><div class="nav-item"><a href="https://github.com/Minimal-APIs/samples" target="_blank" rel="noopener noreferrer" class="nav-link external">
Samples
<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div><div class="nav-item"><a href="https://docs.microsoft.com/aspnet/core/fundamentals/minimal-apis" target="_blank" rel="noopener noreferrer" class="nav-link external">
Microsoft Docs
<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/hello-minimal/" class="nav-link">
Hello Minimal
</a></div><div class="nav-item"><a href="/quickstart/" class="nav-link">
Getting Started
</a></div><div class="nav-item"><a href="/tutorials/" class="nav-link router-link-active">
Tutorials
</a></div><div class="nav-item"><a href="https://github.com/Minimal-APIs/samples" target="_blank" rel="noopener noreferrer" class="nav-link external">
Samples
<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div><div class="nav-item"><a href="https://docs.microsoft.com/aspnet/core/fundamentals/minimal-apis" target="_blank" rel="noopener noreferrer" class="nav-link external">
Microsoft Docs
<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav> <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>Tutorials</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/tutorials/" aria-current="page" class="sidebar-link">Tutorials</a></li><li><a href="/tutorials/first-steps.html" class="sidebar-link">Tutorial One: Minimal APIs 101</a></li><li><a href="/tutorials/crud.html" class="sidebar-link">Tutorial Two: My First CRUD App</a></li><li><a href="/tutorials/databases.html" aria-current="page" class="active sidebar-link">Tutorial Three: Add a Database</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/tutorials/databases.html#working-with-databases" class="sidebar-link">Working with Databases</a></li><li class="sidebar-sub-header"><a href="/tutorials/databases.html#setup-sqlite-database" class="sidebar-link">Setup SQLite database</a></li><li class="sidebar-sub-header"><a href="/tutorials/databases.html#enable-database-creation" class="sidebar-link">Enable database creation</a></li><li class="sidebar-sub-header"><a href="/tutorials/databases.html#set-connection-string" class="sidebar-link">Set connection string</a></li><li class="sidebar-sub-header"><a href="/tutorials/databases.html#add-your-context-to-your-services" class="sidebar-link">Add your context to your services</a></li><li class="sidebar-sub-header"><a href="/tutorials/databases.html#migrate-data-model" class="sidebar-link">Migrate data model</a></li><li class="sidebar-sub-header"><a href="/tutorials/databases.html#create-your-database-and-schema" class="sidebar-link">Create your database and schema</a></li></ul></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="tutorial-three-add-a-database"><a href="#tutorial-three-add-a-database" class="header-anchor">#</a> Tutorial Three: Add a Database</h1> <h2 id="working-with-databases"><a href="#working-with-databases" class="header-anchor">#</a> Working with Databases</h2> <p>We just learned how to build a basic <a href="/tutorials/CRUD.html">CRUD</a> 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.</p> <p>For this tutorial we will be using <a href="https://www.sqlite.org/index.html" target="_blank" rel="noopener noreferrer">SQLite database<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a> but, you may use one that works better for you.</p> <h2 id="setup-sqlite-database"><a href="#setup-sqlite-database" class="header-anchor">#</a> Setup SQLite database</h2> <p><strong>Setup SQLite Database using Entity Framework Core(EF Core)</strong>*</p> <p><strong>Install the following tools and packages</strong></p> <p>Using .NET CLI / Visual Studio package manager UI, install the following packages:</p> <p><strong><a href="https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Sqlite/6.0.0-preview.5.21301.9" target="_blank" rel="noopener noreferrer">SQLite EF Core Database Provider<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></strong> : can access many different databases through plug-in libraries called <a href="https://docs.microsoft.com/ef/core/providers/?tabs=dotnet-core-cli" target="_blank" rel="noopener noreferrer">database providers<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>. The package below is the SQLite database provider for EF Core.</p> <div class="language-console line-numbers-mode"><pre class="language-text"><code>TodoApi>dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 6.0.0-preview.6.21352.1
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong><a href="https://docs.microsoft.com/ef/core/cli/dotnet" target="_blank" rel="noopener noreferrer">Entity Framework Core tools <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></strong>: 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.</p> <div class="language-console line-numbers-mode"><pre class="language-text"><code>TodoApi>dotnet tool install --global dotnet-ef
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong><a href="https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Design/6.0.0-preview.5.21301.9" target="_blank" rel="noopener noreferrer"><code>Microsoft.EntityFrameworkCore.Design</code><span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></strong> : contains all the design-time logic for EF core to create your database.</p> <div class="language-console line-numbers-mode"><pre class="language-text"><code>TodoApi>dotnet add package Microsoft.EntityFrameworkCore.Design --version 6.0.0-preview.6.21352.1
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="enable-database-creation"><a href="#enable-database-creation" class="header-anchor">#</a> Enable database creation</h2> <p>In order to enable database creation they are couple of steps we need to complete:</p> <ol><li>Set the database connection string.</li> <li>Migrate your data model <em>(see below)</em> to a SQLite database.
<strong>Create a data model</strong></li></ol> <div class="language-cs line-numbers-mode"><pre class="language-cs"><code><span class="token keyword">class</span> <span class="token class-name">TodoItem</span>
<span class="token punctuation">{</span>
<span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> Id <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span>
<span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">string</span><span class="token punctuation">?</span></span> Item <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span>
<span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">bool</span></span> IsComplete <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><ol start="3"><li>Create your database and schema</li></ol> <h2 id="set-connection-string"><a href="#set-connection-string" class="header-anchor">#</a> Set connection string</h2> <p>In <code>Program.cs</code> below your app builder <code>var builder = WebApplication.CreateBuilder(args);</code> add a connection string.</p> <div class="language-cs line-numbers-mode"><pre class="language-cs"><code><span class="token class-name"><span class="token keyword">var</span></span> connectionString <span class="token operator">=</span> builder<span class="token punctuation">.</span>Configuration<span class="token punctuation">.</span><span class="token function">GetConnectionString</span><span class="token punctuation">(</span><span class="token string">"todos"</span><span class="token punctuation">)</span> <span class="token operator">??</span> <span class="token string">"Data Source=todos.db"</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="add-your-context-to-your-services"><a href="#add-your-context-to-your-services" class="header-anchor">#</a> Add your context to your services</h2> <p>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.</p> <p>Replace your current in-memory database implementation <code>builder.Services.AddDbContext<TodoDb>(options => options.UseInMemoryDatabase("items"));</code> in your build services with the SQLite one below:</p> <div class="language-cs line-numbers-mode"><pre class="language-cs"><code>builder<span class="token punctuation">.</span>Services<span class="token punctuation">.</span><span class="token generic-method"><span class="token function">AddSqlite</span><span class="token generic class-name"><span class="token punctuation"><</span>TodoDb<span class="token punctuation">></span></span></span><span class="token punctuation">(</span>connectionString<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="migrate-data-model"><a href="#migrate-data-model" class="header-anchor">#</a> Migrate data model</h2> <p>With EF Core migration tool, you can now start your first migration <code>InitialCreate</code>. In a terminal window, run the <code>migrations</code> command below:</p> <div class="language-console line-numbers-mode"><pre class="language-text"><code>TodoApi> dotnet ef migrations add InitialCreate
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>EF Core will create a folder called Migrations in your project directory containing two files <em>(see image below)</em></p> <p><img src="https://user-images.githubusercontent.com/2546640/128618497-8de76c16-cd38-48a8-9704-599c15d115f0.png" alt="image"></p> <h2 id="create-your-database-and-schema"><a href="#create-your-database-and-schema" class="header-anchor">#</a> Create your database and schema</h2> <p>Now that you have completed the migration, you can use it to create your database and schema. In a terminal window, run the <code>database update</code> command below to apply migrations to a database:</p> <div class="language-console line-numbers-mode"><pre class="language-text"><code>TodoApi> dotnet ef database update
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>You should see a newly created <code>todos.db</code> file in your project directory <em>(see image below)</em></p> <p><img src="https://user-images.githubusercontent.com/2546640/128619644-e35bce93-0a87-4367-8790-0d09e837ba97.png" alt="image"></p> <h3 id="learn-checklist-four-✔️"><a href="#learn-checklist-four-✔️" class="header-anchor">#</a> Learn checklist four ✔️</h3> <ul><li>Setup SQLite database</li> <li>Create a SQLite database</li> <li>Perform SQLite CRUD operation from our todo api</li></ul> <p>Your persistent database is set up! Happy coding 😺</p></div> <footer class="page-edit"><!----> <!----></footer> <div class="page-nav"><p class="inner"><span class="prev">
←
<a href="/tutorials/crud.html" class="prev">
Tutorial Two: My First CRUD App
</a></span> <!----></p></div> </main></div><div class="global-ui"><!----></div></div>
<script src="/assets/js/app.7c598cc9.js" defer></script><script src="/assets/js/2.2bd089a4.js" defer></script><script src="/assets/js/22.cd43dcc4.js" defer></script>
</body>
</html>