DEV Community

VzlDev
VzlDev

Posted on

6

Integrating PostgreSQL with a .NET: A Step-by-Step Guide

In today's world of ever-evolving technologies, PostgreSQL stands out as a powerful, open-source relational database management system that is robust, reliable, and feature-rich. Integrating PostgreSQL with a .NET API can open up a lot of opportunities for building scalable and high-performance applications. In this guide, we'll walk you through the process of setting up and integrating PostgreSQL with a .NET API.

Prerequisites

Before we dive in, ensure you have the following installed on your machine:

  • PostgreSQL
  • .NET SDK

Step 1: Set Up Your .NET Project

First things first, create a .NET Project.

Step 2: Install Required Packages

Next, we need to install the necessary packages to work with PostgreSQL in .NET.
Install the following nuGet package:

  • Npgsql.EntityFrameworkCore.PostgreSQL

This package allows Entity Framework Core to communicate with PostgreSQL.

Step 3: Configure the Database Connection

Open appsettings.json and add your PostgreSQL connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=mydatabase;Username=postgres;Password=yourpassword"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}
Enter fullscreen mode Exit fullscreen mode

Step 4: Create a Data Context Class

Create a new class named MyDbContext.cs in the Models directory (create the directory if it doesn't exist). Define your DbContext class as follows:

using Microsoft.EntityFrameworkCore;

namespace MyApi.Models
{
    public class MyDbContext : DbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }

        public DbSet<User> Users { get; set; }
    }

    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
    }
}

Enter fullscreen mode Exit fullscreen mode

Step 5: Configure Services in Program.cs

Open program.cs and configure the services to use PostgreSQL:

 services.AddDbContext<MyDbContext>(options =>     options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));
Enter fullscreen mode Exit fullscreen mode

Step 6: Create and Apply Migrations
To keep your database schema in sync with your EF Core models, use migrations. Open a terminal and run:

dotnet ef migrations add InitialCreate
dotnet ef database update
Enter fullscreen mode Exit fullscreen mode

Step 7: Create API Endpoints

Create a new controller named UsersController.cs in the Controllers directory:

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using MyApi.Models;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

[Route("api/[controller]")]
[ApiController]
public class UsersController : ControllerBase
{
    private readonly MyDbContext _context;

    public UsersController(MyDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<User>>> GetUsers()
    {
        return await _context.Users.ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<User>> GetUser(int id)
    {
        var user = await _context.Users.FindAsync(id);

        if (user == null)
        {
            return NotFound();
        }

        return user;
    }

    [HttpPost]
    public async Task<ActionResult<User>> PostUser(User user)
    {
        _context.Users.Add(user);
        await _context.SaveChangesAsync();

        return CreatedAtAction(nameof(GetUser), new { id = user.Id }, user);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> PutUser(int id, User user)
    {
        if (id != user.Id)
        {
            return BadRequest();
        }

        _context.Entry(user).State = EntityState.Modified;

        try
        {
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!UserExists(id))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return NoContent();
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteUser(int id)
    {
        var user = await _context.Users.FindAsync(id);
        if (user == null)
        {
            return NotFound();
        }

        _context.Users.Remove(user);
        await _context.SaveChangesAsync();

        return NoContent();
    }

    private bool UserExists(int id)
    {
        return _context.Users.Any(e => e.Id == id);
    }
}

Enter fullscreen mode Exit fullscreen mode

Step 8: Run the Application

To manage postgreSQL databases, I use the pgAdmin 4, so you can see here an example of a user:

Image description

And that's it guys, a very very very simple case of postgreSQL integration with .net, there's still plenty more to learn, I hope you liked it, stay tuned for more!

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (2)

Collapse
 
lewisblakeney profile image
lewisblakeney

Great post! Integrating PostgreSQL with .NET is crucial for many modern applications. Your step-by-step guide is clear and practical, making it an invaluable resource for developers. This is a fantastic reference for anyone seeking .NET development services. Thanks for sharing such insightful content!

Collapse
 
vzldev profile image
VzlDev

Thank you very much!

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay