DEV Community: Trevoir Williams

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

Trevoir Williams — Fri, 01 Nov 2024 12:38:04 +0000

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

Visual Studio 2022 (or later) or Visual Studio Code with the .NET SDK 6+.
Free account on Neon.tech, a fully managed serverless PostgreSQL provider.
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 your PostgreSQL instance's connection credentials (Host, Database, User, Password, Port). 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.

If you want to level up your API development skills, the Ultimate ASP.NET Web API Development Guide on Udemy is the perfect course! 🎉
With hands-on projects, expert-led lectures, and a step-by-step approach, you’ll learn to build robust, scalable APIs using ASP.NET Core. This course is ideal for beginners and experienced developers who want to enhance their backend skills.

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 AppDbContextclass inherits from DbContext and specifies a DbSet for Product, enabling CRUD operations on the Products table.

Step 6: Configure the Database Connection

Open the appsettings.json file.
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:

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:

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:
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.

5 Programming Principles To Live By

Trevoir Williams — Wed, 01 Dec 2021 13:12:52 +0000

As programmers, we try to follow certain rules and patterns to govern how we write code that delivers software. While these patterns might not make the software 'work better', they are important for the maintenance and longevity of the application and contribute to easily newer developers can take up the baton and run.

Never forget that code should be reusable and maintainable. The following are 5 principles that will help you to maintain those standards.

1. KISS (Keep It 'Simple Stupid' or 'Super Simple')

This is first on the list because it can determine how easily another developer can come and see and understand your code. Frankly, it also affect how well you can understand it after a weekend off.

Commenting code is good, but when code is written in a simple and efficient manner, it can speak for itself.

Always ensure that your code is solving specific problems, using the simplest and most understandable techniques possible.

2. DRY (Don't Repeat Yourself)

Sometimes, it seems harmless to repeat a block of code. But then, we need the block another time...and in another place. Then the requirement changes, that's 5 places that this block of code was repeated and 5 additional bouts of maintenance work.

This is why methods and then classes exist. Try to abstract code that will be used multiple times and place in a central location that other places and developers can access it. This makes it easier to maintain in the long run and takes pressure off of maintenance and refactoring activities.

3. YAGNI (You Aren't Going To Need It)

I have seen too many times where the super star developer on the team writes some code for an edge case. Edge case meaning, maybe it will...maybe it won't. Sometimes, the code is no longer needed and they just comment it out and leave it there...just in case.

If you don't need code now, then don't write it. That is time that could be better spent ensuring that the current objectives are being met with relevant code. Also, don't develop attachment syndrome to code that is being deprecated. If you are using source control, then you can always retrieve the version of the code after you have removed (not just commented...but removed) it from the code base.

This ultimately leads to much cleaner code and readability.

4. Separation of Concern

The foundation of this principle is that you should always ensure that your blocks of code are dealing with one thing at a time. In practice, this isn't always easy to accomplish based of the framework or even language you are using, but it is something to always remember.

Here are simple examples of how this can be ensured:

A function should complete one operation.
Don't have it doing 2 or 3. eg. Sum(int num1, int num2)
Anything written in that function should be towards a single outcome.
eg. Sum(4, 1) returns 5. Not 4 (Product) and .25 (quotient)
All functions related to common tasks, should be put into one class.
class Calculator
{
int Sum(int num1, int num2){...}
int Difference(int num1, int num2){...}
int Product(int num1, int num2){...}
int Quotient(int num1, int num2){...}
}
All related classes get compiled into *one library *

I am sure you get the point, ensure that your code has one major task to completed per block.

5. Do A Design Document

Sometimes in our zeal, we rush head first into development based on a vision and then end up making major changes in the middle of it all.

It is important to have a clear design and I would extend that and say, design document. This helps you to think ahead and realize potential gaps beforehand and have a clear goal and path in mind.

Conclusion

Sometimes it is not about being a great programmer, it is more about being proficient, but adhering to good habits.

You might not always get it right the first time, but always be willing to critique yourself and accept feedback as you grow in your profession.