How to Connect PostgreSQL Database with Entity Framework Core in .NET 6
Learn how to connect a PostgreSQL database with a .NET 6 App using Entity Framework Core in a few easy steps.
In this tutorial, you will learn how to connect a PostgreSQL database to your .NET 6 application using Entity Framework Core and how you can apply migrations to the database using EF Core migrations.
Using EF Core to create/update migrations on the database makes it easier to deploy it with containers, etc... Imagine having a dockerized .NET solution that automatically configures the database upon deployment of the stack.
I have prepared a .NET 6 CRUD API that you can download from my GitHub account. Let us update that in order for it to connect to the PostgreSQL database and run the EF Core Migrations, so you can make your application fully code-first and sit back while you deploy the solution. I will not go in-depth on how the PostgreSQL database works - only show you how to start one using Docker and connect to it.
Christian-SchouRequirements
To keep up with this tutorial you will need the following tools installed on your computer.
- .NET SDK - This includes both the CLI (Command Line Tools), .NET Runtime and libraries, and the
dotnetdriver. - Visual Studio Code or IDE. Both these code editors are capable of running on Linux, macOS, and Windows. You can also use Rider or any other code editor you prefer.
- If you decide to use Visual Studio Code, you would need to install the C# Extension by Microsoft.
- Docker or native installation of PostgreSQL - Docker will allow you to accelerate how you build, share, and run modern applications. In this tutorial, I will use Docker Compose to deploy two containers. The first one will be PostgreSQL and the second one will be an admin interface for PostgreSQL.
Christian Schou
Create PostgreSQL database + PgAdmin4 with Docker Compose
For this project, I will be using a simple docker-compose file for configuring and running a PostgreSQL Database along with an Admin UI for PostgreSQL. I have prepared a compose file you can copy and paste below - it is also available in the project repository, and at the reference bookmark below:
version: '3.5'
services:
postgres:
container_name: postgres_db_container
image: postgres
environment:
POSTGRES_USER: ${POSTGRES_USER:-postgres}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
PGDATA: /data/postgres
volumes:
- postgres:/data/postgres
ports:
- "5432:5432"
networks:
- postgres
restart: unless-stopped
pgadmin:
container_name: pgadmin_db_container
image: dpage/pgadmin4
environment:
PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
PGADMIN_CONFIG_SERVER_MODE: 'False'
volumes:
- pgadmin:/var/lib/pgadmin
ports:
- "${PGADMIN_PORT:-5050}:80"
networks:
- postgres
restart: unless-stopped
networks:
postgres:
driver: bridge
volumes:
postgres:
pgadmin:This Compose file contains the following environment variables:
POSTGRES_USERthe default value is postgresPOSTGRES_PASSWORDthe default value is changemePGADMIN_PORTthe default value is 5050PGADMIN_DEFAULT_EMAILthe default value is pgadmin4@pgadmin.orgPGADMIN_DEFAULT_PASSWORDthe default value is admin
Access PostgreSQL Database
localhost:5432- Username: postgres (as a default)
- Password: changeme (as a default)
Access PgAdmin
- URL:
http://localhost:5050 - Username: pgadmin4@pgadmin.org (default)
- Password: admin (default)
Add Server in PgAdmin
- Host name/address
postgres(service name) - Port
5432 - Username as
POSTGRES_USER, by default:postgres - Password as
POSTGRES_PASSWORD, by defaultchangeme
khezenClone and run the .NET 6 CRUD Web API
I have created a simple .NET 6 Web API with a CRUD repository implementation for this tutorial. By the end of this tutorial, we will have a fully implemented database context communicating with PostgreSQL.
You can either apply the code in your own application or you can clone my project and continue developing that along with me. It is available at the link below.
Christian-SchouInside this project I have used the following technologies:
Add PostgreSQL support in .NET 6 Web API
Let's move on to the fun part and implement support for communicating with a PostgreSQL database using EF Core and .NET.
Add PostgreSQL database provider package using NuGet
Open up the Package Manager Console from the root of your project where you have the database context stored. Then enter the command below to install the EF Core database provider for PostgreSQL using the NuGet Package Manager.
Install-Package Npgsql.EntityFrameworkCore.PostgreSQLAdd a connection string to appsettings.json
Now open up appsettings.json and add the following code to define a new connection string entry in your application. You can set the name of the connection string to what you prefer. Then enter the connection string in this format: User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;
When we are running the migrations using Entity Framework Core, the values in our code at runtime will be replaced by the values defined in our connection string.
Your updated appsettings.json file should look like this if you use my CRUD template.
{
"ConnectionStrings": {
"PostgreSQL": "User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}
It might look different if you have other configurations inside it. I always place my connection strings at the beginning of this file, if I do not configure it in a separate configuration file.
Change Database context to use PostgreSQL
In the CRUD Web API, the DataContext class is named LibraryContext and is located at /Data/LibraryContext.cs. I have named it LibraryContext since the CRUD is about authors and books. In .NET we use this file for accessing the data through Entity Framework.
As you can see in the code we derive from the Entity Framework DbContext class and got two public properties for accessing and managing authors and books.
To use PostgreSQL we have to update the OnConfiguring() method to PostgreSQL instead of the current configured in-memory database. This is done by replacing optionsBuilder.UseInMemoryDatabase("LibraryDatabase"); with
If you have done it correctly, you should have a LibraryContext class that looks like this:
using Microsoft.EntityFrameworkCore;
using PostgreSQL.Demo.API.Entities;
namespace PostgreSQL.Demo.API.Data
{
public class LibraryContext : DbContext
{
protected readonly IConfiguration _configuration;
public LibraryContext(IConfiguration configuration)
{
_configuration = configuration;
}
public DbSet<Author> Authors { get; set; }
public DbSet<Book> Books { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(_configuration.GetConnectionString("PostgreSQL"));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasMany(b => b.Books)
.WithOne(a => a.Author)
.OnDelete(DeleteBehavior.Cascade);
}
}
}
As you also might have noticed, we got a relationship defined in the OnModelCreating() method. This is defining a one-to-many relationship using Entity Framework.
Create PostgreSQL Database using Code-First approach with Entity Framework Migrations
Now that we have connected the PostgreSQL database to our .NET 6 Web API, let's make our lives a bit easier and apply automatic database creation using Entity Framework Core.
Install required tools globally
To create new migrations for our Web API, we can utilize the EF Core Tools to make the heavy lifting for us. You must install the EF Core tools globally on your computer using the following command in your PowerShell Terminal.
dotnet tool install -g dotnet-efYou can read more about the EF Core tools here.
Add EF Core Design Library/Package to Web API
Inside your Package Manager Console, you can run the command below to install the EF Core Design library into your .NET application. This library gives you access to generate the EF Core Migrations, that will be applied to the database.
Install-Package Microsoft.EntityFrameworkCore.DesignGenerate EF Core migrations manually
Generate new EF Core migration files by running the command dotnet ef migrations add Initial. You have to do it from the root of the project where the database context is located.
For this project, it is stored inside PostgreSQL.Demo.API. When you execute this command EF Core will create the migration that will contain the code to create the database and tables for your application.
Execute EF Core migrations manually
With the dotnet CLI we can update our database from the code (Code First Approach). All you have to do is open your Package Manager Console and select the project where your database context lives. Set your API as the startup project and then run this command in the terminal: dotnet ef database update and watch the magic happen.
Now open up PostgreSQL and see the database with the tables you have specified in the Database Context (DbSet<T>) and the _EFMigrationsHistory.
As you can see we now got the tables from our Database Context (LibraryContext). This is a manual process and I like to automate things, but keep in mind that automation can be dangerous! Below is a full description of how you can make automatic migrations using the Entity Framework Core Database Context Service.
Execute database migrations automatically using the EF Core DB Context Service
It is possible to make the application do the migrations for us during the startup of the application. This is great when we run the application in our own development environment and during migrations tests.
- Suppose you got multiple instances of your applications running. In that case, both applications could attempt to apply the migrations concurrently and that would in most cases fail and in some cases lead to data corruption.
- Imagine having an application running using the old version of the database and then upgrading the database to a new version. That could lead to severe issues.
- You need to assign elevated access for the application to modify the database schema. I would not recommend allowing an application to have this permission in a production environment.
- If you use the manual strategy where you use the
dotnet CLIto upgrade the database you are able to roll back an applied migration to the database in case something should break. - If the application is running the migration update, you will not see any issues and be able to inspect them. In a production environment, this is in my opinion very dangerous.
Due to the reasons above, I will not include code to run automatic migrations in this project as I like to run my migrations manually in order to have full control of what is going on.
Update Repository and Controllers to work with PostgreSQL
At the moment we are returning void when creating and updating authors and books in the application. This will result in an exception from Entity Framework Core like this: Invalid attempt to call ReadAsync when reader is closed. The problem is that we are Y and that needs to be fixed by firing the triggers async.
Luckily for us, it is easy to fix. We only need to replace void with Task as return type and change the methods to be awaitable (async methods).
The updated AuthorService looks as follows:
using AutoMapper;
using Microsoft.EntityFrameworkCore;
using PostgreSQL.Demo.API.Data;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Helpers;
using PostgreSQL.Demo.API.Models.Authors;
namespace PostgreSQL.Demo.API.Services
{
public interface IAuthorService
{
/// <summary>
/// Get all authors in database. Set includeBooks to true if you want to include all books made by the author.
/// </summary>
/// <param name="includeBooks">Optional parameter to include books</param>
/// <returns>All authors in database</returns>
Task<IEnumerable<Author>> GetAllAuthorsAsync(bool includeBooks = false);
/// <summary>
/// Get a single author by Id and include books if requested by the includeBooks boolean.
/// </summary>
/// <param name="id">Id of Author</param>
/// <param name="includeBooks">Optional parameter to include books</param>
/// <returns>A single author</returns>
Task<Author> GetAuthorByIdAsync(int id, bool includeBooks = false);
/// <summary>
/// Create a new author in the database
/// </summary>
/// <param name="model">Create Author request model</param>
Task<int> CreateAuthor(CreateAuthorRequest model);
/// <summary>
/// Update an author in the database if the author already exists.
/// </summary>
/// <param name="id"></param>
/// <param name="model"></param>
Task UpdateAuthor(int id, UpdateAuthorRequest model);
/// <summary>
/// Delete a single author in the dabase. Will delete the author if the author exists in the database.
/// Cascading is enabled and will delete the authors books from the database at the same time. Use with caution.
/// </summary>
/// <param name="id">Id of the author to delete</param>
Task DeleteAuthor(int id);
}
public class AuthorService : IAuthorService
{
private LibraryContext _dbContext;
private readonly IMapper _mapper;
public AuthorService(LibraryContext dbContext, IMapper mapper)
{
_dbContext = dbContext;
_mapper = mapper;
}
public async Task<int> CreateAuthor(CreateAuthorRequest model)
{
// Validate new author
if (await _dbContext.Authors.AnyAsync(x => x.Name == model.Name))
throw new RepositoryException($"An author with the name {model.Name} already exists.");
// Map model to new author object
Author author = _mapper.Map<Author>(model);
// Save Author
_dbContext.Authors.Add(author);
await _dbContext.SaveChangesAsync().ConfigureAwait(true);
if (author != null)
{
return author.Id; // Author got created
}
return 0;
}
public async Task DeleteAuthor(int id)
{
Author? author = await _getAuthorById(id);
_dbContext.Authors.Remove(author); // Delete the author and books (Cascading is enabled)
await _dbContext.SaveChangesAsync().ConfigureAwait(true);
}
public async Task<IEnumerable<Author>> GetAllAuthorsAsync(bool includeBooks = false)
{
if (includeBooks)
{
// Get all authors and their books
return await _dbContext.Authors
.Include(b => b.Books)
.ToListAsync().ConfigureAwait(true);
}
else
{
// Get all authors without including the books
return await _dbContext.Authors
.ToListAsync().ConfigureAwait(true);
}
}
public async Task<Author> GetAuthorByIdAsync(int id, bool includeBooks = false)
{
return await _getAuthorById(id, includeBooks).ConfigureAwait(true);
}
public async Task UpdateAuthor(int id, UpdateAuthorRequest model)
{
Author? author = await _getAuthorById(id).ConfigureAwait(true);
// Validation
if (model.Name != author.Name && await _dbContext.Authors.AnyAsync(x => x.Name == model.Name))
throw new RepositoryException($"An author with the name {model.Name} already exists.");
// copy model to author and save
_mapper.Map(model, author);
_dbContext.Authors.Update(author);
await _dbContext.SaveChangesAsync();
}
/// <summary>
/// Get a single author and the books if requested. Looks in the database for an author and returns null, if the author did not exist.
/// </summary>
/// <param name="id">Author ID</param>
/// <param name="includeBooks">True to include books</param>
/// <returns>A single author</returns>
private async Task<Author> _getAuthorById(int id, bool includeBooks = false)
{
if (includeBooks)
{
Author? author = await _dbContext.Authors
.AsNoTracking()
.Where(x => x.Id == id)
.Include(b => b.Books)
.FirstOrDefaultAsync().ConfigureAwait(true);
if (author == null)
{
throw new KeyNotFoundException("Author not found");
}
return author;
}
else
{
Author? author = await _dbContext.Authors
.AsNoTracking()
.Where(x => x.Id == id)
.FirstOrDefaultAsync().ConfigureAwait(true);
if (author == null)
{
throw new KeyNotFoundException("Author not found");
}
return author;
}
}
}
}
The updated AuthorController looks as follows:
using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Models.Authors;
using PostgreSQL.Demo.API.Services;
// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
namespace PostgreSQL.Demo.API.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class AuthorController : ControllerBase
{
private IAuthorService _authorService;
private IMapper _mapper;
public AuthorController(IAuthorService authorService, IMapper mapper)
{
_authorService = authorService;
_mapper = mapper;
}
// GET: api/<AuthorController>
[HttpGet]
public async Task<IActionResult> GetAllAuthors()
{
IEnumerable<Author> authors = await _authorService.GetAllAuthorsAsync();
return Ok(authors);
}
// GET api/<AuthorController>/5
[HttpGet("{id}")]
public async Task<IActionResult> GetAuthorById(int id, bool includeBooks = false)
{
Author author = await _authorService.GetAuthorByIdAsync(id, includeBooks);
return Ok(author);
}
// POST api/<AuthorController>
[HttpPost]
public async Task<IActionResult> CreateAuthor(CreateAuthorRequest model)
{
int authorId = await _authorService.CreateAuthor(model);
if (authorId != 0)
{
return Ok(new { message = $"Author was successfully created in database with the id {authorId}" });
}
return StatusCode(StatusCodes.Status500InternalServerError, "The author was not created in the database.");
}
// PUT api/<AuthorController>/5
[HttpPut("{id}")]
public async Task<IActionResult> UpdateAuthor(int id, UpdateAuthorRequest model)
{
await _authorService.UpdateAuthor(id, model);
return Ok(new { message = "Author was successfully updated in database" });
}
// DELETE api/<AuthorController>/5
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteAuthor(int id)
{
await _authorService.DeleteAuthor(id);
return Ok(new { message = "Author was successfully deleted in database" });
}
}
}
The updated BookService looks as follows:
using AutoMapper;
using Microsoft.EntityFrameworkCore;
using PostgreSQL.Demo.API.Data;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Helpers;
using PostgreSQL.Demo.API.Models.Books;
namespace PostgreSQL.Demo.API.Services
{
public interface IBookService
{
/// <summary>
/// Get all book in database.
/// </summary>
/// <returns>All books in database</returns>
Task<IEnumerable<Book>> GetAllBooksAsync();
/// <summary>
/// Get a single book by Id
/// </summary>
/// <param name="id">Id of book</param>
/// <returns>A single book</returns>
Task<Book> GetBookByIdAsync(int id);
/// <summary>
/// Create a new book in the database
/// </summary>
/// <param name="model">Create book request model</param>
Task<int> CreateBook(CreateBookRequest model);
/// <summary>
/// Update a book in the database if the book already exists.
/// </summary>
/// <param name="id"></param>
/// <param name="model"></param>
Task UpdateBook(int id, UpdateBookRequest model);
/// <summary>
/// Delete a single book in the dabase. Will delete the book if the book exists in the database.
/// </summary>
/// <param name="id">Id of the book to delete</param>
Task DeleteBook(int id);
}
public class BookService : IBookService
{
private LibraryContext _dbContext;
private readonly IMapper _mapper;
public BookService(LibraryContext dbContext, IMapper mapper)
{
_dbContext = dbContext;
_mapper = mapper;
}
public async Task<int> CreateBook(CreateBookRequest model)
{
// Validate new book
if (await _dbContext.Books.AnyAsync(x => x.ISBN13 == model.ISBN13))
throw new RepositoryException($"A book with the ISBN {model.ISBN13} already exist in the database");
// Map model to new book object
Book book = _mapper.Map<Book>(model);
// Save book in database
_dbContext.Books.Add(book);
await _dbContext.SaveChangesAsync().ConfigureAwait(true);
return book.Id;
}
public async Task DeleteBook(int id)
{
Book? book = await _getBookById(id);
_dbContext.Books.Remove(book);
await _dbContext.SaveChangesAsync().ConfigureAwait(true);
}
public async Task<IEnumerable<Book>> GetAllBooksAsync()
{
return await _dbContext.Books
.ToListAsync()
.ConfigureAwait(true);
}
public async Task<Book> GetBookByIdAsync(int id)
{
return await _getBookById(id);
}
public async Task UpdateBook(int id, UpdateBookRequest model)
{
Book? book = await _getBookById(id);
// Validate the book
if (model.ISBN13 != book.ISBN13 && await _dbContext.Books.AnyAsync(x => x.ISBN13 == model.ISBN13))
throw new RepositoryException($"A book with the ISBN number {model.ISBN13} already exist in the database.");
// Copy model data to book object and save it in the database
_mapper.Map(model, book);
_dbContext.Books.Update(book);
await _dbContext.SaveChangesAsync().ConfigureAwait(true);
}
private async Task<Book> _getBookById(int id)
{
Book? book = await _dbContext.Books
.AsNoTracking()
.Where(x => x.Id == id)
.FirstOrDefaultAsync().ConfigureAwait(true);
if (book == null)
{
throw new KeyNotFoundException("Book was not found in database");
}
return book;
}
}
}
The updated BooksController looks as follows:
using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Models.Books;
using PostgreSQL.Demo.API.Services;
namespace PostgreSQL.Demo.API.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class BooksController : ControllerBase
{
private readonly IBookService _bookService;
private IMapper _mapper;
public BooksController(IBookService bookService, IMapper mapper)
{
_bookService = bookService;
_mapper = mapper;
}
// GET: api/<BooksController>
[HttpGet]
public async Task<IActionResult> GetAllBooks()
{
IEnumerable<Book> books = await _bookService.GetAllBooksAsync();
return Ok(books);
}
// GET api/<BooksController>/5
[HttpGet("{id}")]
public async Task<IActionResult> GetBookById(int id)
{
Book book = await _bookService.GetBookByIdAsync(id);
return Ok(book);
}
// POST api/<BooksController>
[HttpPost]
public async Task<IActionResult> CreateBook(CreateBookRequest model)
{
int book = await _bookService.CreateBook(model);
if (book != 0)
{
return Ok("The book was successfully added to the database");
}
return StatusCode(StatusCodes.Status500InternalServerError, "The book was successfully added to the database");
}
// PUT api/<BooksController>/5
[HttpPut("{id}")]
public async Task<IActionResult> UpdateBook(int id, UpdateBookRequest model)
{
await _bookService.UpdateBook(id, model);
return Ok("The book was successfully updated in the database");
}
// DELETE api/<BooksController>/5
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteBook(int id)
{
await _bookService.DeleteBook(id);
return Ok("The book was successfully deleted in the database");
}
}
}
You can see all changes in the commit details below:
Christian-SchouFix Date and Time Handling for PostgreSQL
Npgsql 6.0 introduced some important changes to how timestamps are mapped. I have added a Switch to enable the legacy timestamp behavior in order to store values in the database without receiving an error at creation and update of entities in the database.
Open Program.cs and add the following line, just below your .AddDbContext<T>(); method:
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);In the Program.cs file it would look like this:
using PostgreSQL.Demo.API.Data;
using PostgreSQL.Demo.API.Middleware;
using PostgreSQL.Demo.API.Services;
using System.Text.Json.Serialization;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<LibraryContext>();
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
builder.Services.AddCors();
...You can see the full change at this commit:
Christian-SchouTime for test
The development of the API is done and we have connected it to the PostgreSQL database + created the database and updated it to be at the same level as our domain model in the application.
I have decided to make this as a video, as it would end up in a lot of sections showing test results of each endpoint in the API.
As you can see it works like a charm. A fully connected and ready to use .NET Web API connected to PostgreSQL with CRUD functionality using AutoMapper.
Summary
PostgreSQL is very easy to integrate with .NET. You can either run PostgreSQL on a separate server, in a container, or on your development machine (as I showed in the video above).
The final code is available at my Github profile if you would like to fork it or just need some part of the code for your next project.
Christian-SchouI hope you learned something new from this tutorial about connecting PostgreSQL to .NET. If you got any questions, please let me know in the comments below. If you liked the tutorial and wann help me spread the word about TWC, then please help me share the article.