Integrating PostgreSQL with a .NET: A Step-by-Step Guide
Tags: postgres, postgressql, csharp, dotnet
Published: 2024-06-27 12:54:47

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5rmj7fi8zk2f6kfouuny.png)


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!