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": "*"
}

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; }
    }
}

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")));

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

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);
    }
}

Step 8: Run the Application

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

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!

Comments

[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!