Getting Started with ASP.NET Core API, EF Core, and PostgreSQL on Neon

Developing applications with ASP.NET Core is a powerful way to build high-performance and cross-platform web APIs. In this guide, we’ll integrate Entity Framework Core with PostgreSQL, hosted on Neon, to handle CRUD (Create, Read, Update, Delete) operations for a "Products" table.

Prerequisites

1. Visual Studio 2022 (or later) or Visual Studio Code with the .NET SDK 6+.
2. Free account on Neon.tech, a fully managed serverless PostgreSQL provider.
3. Postman (optional) to test the API endpoints.

Step 1: Setting Up a PostgreSQL Database on Neon

Sign up on Neon.tech and create a new project called aspnet-project (or your preferred name) and a database (with your preferred name).

Navigate to the Project Dashboard and note down the connection credentials (Host, Database, User, Password, Port) for your PostgreSQL instance. We’ll need these to connect our ASP.NET Core application to Neon.

Step 2: Create the ASP.NET Core API Project

You can set up an ASP.NET Core project using Visual Studio or the .NET CLI. Below, I’ll demonstrate using the .NET CLI, but Visual Studio follows similar steps.

Open a terminal, navigate to your preferred project directory, and run the following command:

dotnet new webapi -n ProductApi
cd ProductApi

This creates a new ASP.NET Core Web API project called ProductApi.

Step 3: Install Entity Framework Core and the PostgreSQL Provider

In the terminal, run the following command to install Entity Framework Core and the Npgsql provider for PostgreSQL:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

These packages allow us to use PostgreSQL as our database provider in Entity Framework Core.

Step 4: Define the Product Model

In your project, create a folder named Models and add a Product.cs file inside it. This file will contain the definition of our Product entity:

namespace ProductApi.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public decimal Price { get; set; }
    }
}

This Product model represents a simple product with an ID, Name, Description, and Price.

Step 5: Create the Database Context

In the Data folder, add a new class called AppDbContext.cs:

using Microsoft.EntityFrameworkCore;
using ProductApi.Models;

namespace ProductApi.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

        public DbSet<Product> Products { get; set; }
    }
}

The AppDbContext class inherits from DbContext and specifies a DbSet for Product, enabling CRUD operations on the Products table.

Step 6: Configure the Database Connection

1. Open the appsettings.json file.
2. Add a connection string for your Neon PostgreSQL database.

"ConnectionStrings": {
    "DefaultConnection": "Host=your_neon_host;Database=your_database;Username=your_user;Password=your_password;
}

Replace:
your_neon_host with PGHOST
your_database with PGDATABASE
your_user with PGUSER
your_password with PGPASSWORD

Recommendation: Use .NET Core User Secrets

When deploying to production, it's crucial to never store sensitive data—such as database connection strings—in plain text within your source code or configuration files. Exposing secrets can lead to security vulnerabilities and unauthorized access.

For local development, consider using .NET Core User Secrets to securely store sensitive information outside your codebase. You can leverage secure environment variables or a dedicated secrets manager in production.

To set up secrets in .NET Core:

In your project directory, run this command
dotnet user-secrets init

Then, add your connection string securely
dotnet user-secrets set "ConnectionStrings:DefaultConnection" "YourConnectionStringHere"

For added security, look into options like Azure Key Vault or AWS Secrets Manager to manage production secrets securely.

Next, open Program.cs and modify it to configure the database context:

using Microsoft.EntityFrameworkCore;
using ProductApi.Data;

var builder = WebApplication.CreateBuilder(args);

// Configure DbContext with PostgreSQL connection
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

// Add services to the container
builder.Services.AddControllers();

var app = builder.Build();

// Configure middleware
if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

Step 7: Add a Migration and Update the Database

Now, let’s generate a migration and apply it to our Neon PostgreSQL database to create the Products table.

In the terminal, run:

dotnet ef migrations add InitialCreate
dotnet ef database update

Entity Framework will create a migration that defines the structure of the Products table and apply it to the Neon PostgreSQL database. You can confirm that this table is created by visiting the Tables option in the side nav.

Step 8: Create the Products Controller

In the Controllers folder, add a new ProductsController.cs file:

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ProductApi.Data;
using ProductApi.Models;

namespace ProductApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly AppDbContext _context;

        public ProductsController(AppDbContext context)
        {
            _context = context;
        }

        // GET: api/products
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
        {
            return await _context.Products.ToListAsync();
        }

        // GET: api/products/{id}
        [HttpGet("{id}")]
        public async Task<ActionResult<Product>> GetProduct(int id)
        {
            var product = await _context.Products.FindAsync(id);

            if (product == null)
                return NotFound();

            return product;
        }

        // POST: api/products
        [HttpPost]
        public async Task<ActionResult<Product>> PostProduct(Product product)
        {
            _context.Products.Add(product);
            await _context.SaveChangesAsync();

            return CreatedAtAction(nameof(GetProduct), new { id = product.Id }, product);
        }

        // PUT: api/products/{id}
        [HttpPut("{id}")]
        public async Task<IActionResult> PutProduct(int id, Product product)
        {
            if (id != product.Id)
                return BadRequest();

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

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

            return NoContent();
        }

        // DELETE: api/products/{id}
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProduct(int id)
        {
            var product = await _context.Products.FindAsync(id);
            if (product == null)
                return NotFound();

            _context.Products.Remove(product);
            await _context.SaveChangesAsync();

            return NoContent();
        }

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

This controller provides methods to handle CRUD operations for the Products table. Each action method is decorated with HTTP verb attributes (HttpGet, HttpPost, HttpPut, HttpDelete) to indicate the type of operation.

Step 9: Testing the API

You can use tools like Postman or curl to test your API. Here are some example requests:

1. GET all products: GET https://localhost:5001/api/products

POST a new product:

{
  "name": "Sample Product",
  "description": "A description of the sample product",
  "price": 19.99
}

And there you have it! You now have a fully functional ASP.NET Core API connected to a PostgreSQL database hosted on Neon. This setup makes building robust applications with serverless PostgreSQL and .NET Core easy.