Getting Started with PostgreSQL in .NET: From Zero to Production

A practical guide to connecting PostgreSQL to your ASP.NET Core app using Entity Framework Core and Dapper — with Docker, migrations, JSONB, arrays, and everything you need for production.

Why PostgreSQL Over SQL Server?

If you’ve been working with SQL Server for a while, switching to PostgreSQL might feel unnecessary. But more and more .NET teams are making the move — and for good reasons.

PostgreSQL is open source, free, and runs on any platform. It’s battle-tested at scale by companies like Apple, Instagram, and Spotify. It handles relational data as well as SQL Server, but also supports native JSON storage, full-text search, arrays, custom types, and vector search through the pgvector extension — all without additional licensing costs.

For .NET developers, the ecosystem is mature. The Npgsql driver has first-class support for Entity Framework Core and Dapper, and the development experience is practically identical to what you’re used to.

Setting Up PostgreSQL Locally with Docker

The fastest way to get PostgreSQL running locally is with Docker. No installation, no PATH conflicts, no version headaches.

Create a docker-compose.yml in your project root:

version: '3.8'

services:
  postgres:
    image: postgres:16
    container_name: pg_dev
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: yourpassword
      POSTGRES_DB: myapp
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Start it with:

docker compose up -d

Verify it’s running:

docker exec -it pg_dev psql -U admin -d myapp

You can also use a GUI like pgAdmin or DBeaver if you prefer a visual interface.

Connecting with Entity Framework Core (Npgsql)

Install the packages:

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

Create your model and DbContext:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsAvailable { get; set; }
}

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).IsRequired().HasMaxLength(200);
            entity.Property(e => e.Price).HasColumnType("numeric(18,2)");
            entity.Property(e => e.CreatedAt).HasDefaultValueSql("NOW()");
        });
    }
}

Register in Program.cs and add your connection string:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Postgres"))
           .UseSnakeCaseNamingConvention());

{
  "ConnectionStrings": {
    "Postgres": "Host=localhost;Port=5432;Database=myapp;Username=admin;Password=yourpassword"
  }
}

Tip: Always use UseSnakeCaseNamingConvention(). PostgreSQL lowercases unquoted identifiers, so CreatedAt becomes created_at — enabling this convention avoids a whole class of runtime errors.

Connecting with Dapper

For raw SQL — reports, complex joins, stored procedures — Dapper is a great companion alongside EF Core.

dotnet add package Dapper
dotnet add package Npgsql

Create a connection factory:

public interface IDbConnectionFactory
{
    NpgsqlConnection CreateConnection();
}

public class PostgresConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public PostgresConnectionFactory(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("Postgres")!;
    }

    public NpgsqlConnection CreateConnection() => new(_connectionString);
}

And use it in a repository:

public class ProductRepository
{
    private readonly IDbConnectionFactory _factory;

    public ProductRepository(IDbConnectionFactory factory) => _factory = factory;

    public async Task<IEnumerable<Product>> GetAvailableAsync()
    {
        using var connection = _factory.CreateConnection();
        return await connection.QueryAsync<Product>(
            "SELECT * FROM products WHERE is_available = true ORDER BY created_at DESC");
    }

    public async Task<int> CreateAsync(Product product)
    {
        using var connection = _factory.CreateConnection();
        return await connection.ExecuteScalarAsync<int>(
            """
            INSERT INTO products (name, price, is_available)
            VALUES (@Name, @Price, @IsAvailable)
            RETURNING id
            """, product);
    }
}

PostgreSQL-Specific Features You’ll Love

JSONB — Flexible Schema Within a Column

JSONB stores JSON as parsed binary, making it indexable and fast to query. Perfect for dynamic attributes or metadata you don’t want to model as full columns.

public class Order
{
    public int Id { get; set; }
    public string CustomerEmail { get; set; } = string.Empty;
    public JsonDocument Metadata { get; set; } = JsonDocument.Parse("{}");
}

modelBuilder.Entity<Order>()
    .Property(o => o.Metadata)
    .HasColumnType("jsonb");

Query it with raw SQL:

SELECT * FROM orders
WHERE metadata->>'region' = 'EU'
  AND (metadata->>'total')::numeric > 500;

Arrays — Native Lists Without Junction Tables

public class Article
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string[] Tags { get; set; } = [];
}

modelBuilder.Entity<Article>()
    .Property(a => a.Tags)
    .HasColumnType("text[]");

LINQ works directly on arrays with Npgsql:

var articles = await context.Articles
    .Where(a => a.Tags.Contains("dotnet"))
    .ToListAsync();

Enum Types — Readable Values in the Database

Instead of storing magic numbers or raw strings:

public enum OrderStatus { Pending, Processing, Shipped, Delivered, Cancelled }

Register the enum with Npgsql and EF Core:

options.UseNpgsql(connectionString, o => o.MapEnum<OrderStatus>("order_status"))

modelBuilder.HasPostgresEnum<OrderStatus>("order_status");

Running Migrations with EF Core

dotnet tool install --global dotnet-ef
dotnet ef migrations add InitialCreate
dotnet ef database update

Never run dotnet ef database update directly in production. Instead, generate an idempotent SQL script and apply it through your deployment pipeline:

dotnet ef migrations script --idempotent -o migrations.sql

For containerised environments, you can apply pending migrations on startup:

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();
}

⚠️ Be careful with this in multi-instance deployments. Consider using an init container or a dedicated migration job instead.

Common Errors and How to Avoid Them

**column does not exist** → PostgreSQL lowercases identifiers. Use UseSnakeCaseNamingConvention() to align EF Core with PostgreSQL's expectations.

**No suitable constructor found for entity type** → Add a protected parameterless constructor alongside any custom constructor you've defined:

protected Product() { } // Required by EF Core

**SSL connection is required** → Azure Database for PostgreSQL enforces SSL. Add to your connection string:

Ssl Mode=Require;Trust Server Certificate=true;

Enum migration fails with type already exists → Use HasPostgresEnum() in your DbContext, which handles idempotency correctly during migrations.

Slow queries after bulk inserts → PostgreSQL uses table statistics for query planning. Run ANALYZE products; after large data loads or let autovacuum handle it in production.

Best Practices

• Use UseSnakeCaseNamingConvention() — always.
• Generate SQL scripts for production migrations, never dotnet ef database update.
• Index foreign keys manually — PostgreSQL doesn’t do it automatically.
• Use HasDefaultValueSql("NOW()") for timestamps instead of setting them in C#.
• Prefer jsonb over json — binary storage supports indexing.
• Always open and dispose NpgsqlConnection per request; don't treat it as a singleton.
• Enable pg_stat_statements in production to track slow queries.

Conclusion

PostgreSQL integrates seamlessly with the .NET ecosystem. Whether you’re using Entity Framework Core for migrations and LINQ, or Dapper for raw SQL performance, the tooling is mature and the experience is solid.

The features covered here — JSONB, arrays, enum types, and EF Core migrations — are the ones you’ll reach for most in real projects. Start with them, and you’ll quickly understand why so many .NET teams are moving away from SQL Server and not looking back.

Next in this series: PostgreSQL query performance — reading EXPLAIN ANALYZE, choosing the right indexes, and optimising what EF Core generates under the hood.

Originally published on adrianbailador.github.io