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!

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!

Agent.ai Challenge image

Congrats to the Agent.ai Challenge Winners 🏆

The wait is over! We are excited to announce the winners of the Agent.ai Challenge.

From meal planners to fundraising automators to comprehensive stock analysts, our team of judges hung out with a lot of agents and had a lot to deliberate over. There were so many creative and innovative submissions, it is always so difficult to select our winners.

Read more →

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay