TestContainers in .NET with PostgreSQL and PgVector

What problem are we trying to solve?

How to include postgresql extensions in docker images via C# for integration testing.

Overview

I’m using C# with the Testcontainers.PostgreSql NuGet suite, which makes it easy to launch PostgreSQL containers for integration testing. The core idea behind this setup is to ensure that each test class gets a clean, isolated instance of the database. This repository is used to perform integration testing and is then deleted.

Testcontainers for .NET is a library that supports testing using all disposable Docker container executables that are compatible with .NET Standard versions. The library is built on top of the .NET Docker remote API and provides lightweight implementations to support testing environments in all scenarios.

Container configuration

Here’s how I configure the PostgreSQL container:

private const ushort Port = 5432;
private const string Username = "postgres";
private const string Password = "postgres";
private const string Database = "postgres";
private const string Host = "localhost";

private readonly PostgreSqlContainer _dbContainer = new PostgreSqlBuilder()
    .WithImage("pgvector/pgvector:pg16")
    .WithUsername(Username)
    .WithPassword(Password)
    .WithPortBinding(Port, true)
 .WithWaitStrategy(Wait.ForUnixContainer().UntilPortIsAvailable(Port))
    .Build();

Note that I used the pgvector:pg16 Docker image because it already has the pgvector extensions configured.

New PostgreSQL extension

This project requires three PostgreSQL extensions:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasPostgresExtension("vector");  
    modelBuilder.HasPostgresExtension("btree_gin");
    modelBuilder.HasPostgresExtension("pg_trgm");
}

Using HasPostgresExtension ensures that EF Core manages these extensions as part of the migration or schema process. Automatically generate and execute the following SQL commands:

CREATE EXTENSION IF NOT EXISTS "vector";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

HasPostgresExtension and UseVector

There is a difference between HasPostgresExtension(“vector”) and using the extension directly via UseVector:

Main differences:

HasPostgresExtension(“vector”)

• Ensure that EF Core includes extensions during migrations or schema updates.
• Manage database schema and ensure extensions are installed.

Use vectors

• Configure Npgsql to take advantage of vector extension features, such as advanced type mapping and query capabilities.

• Enable application-level usage, such as performing vector similarity queries:

SELECT * FROM my_table ORDER BY embedding <-> query_embedding LIMIT 10;

Initialize the database using a script

In addition to extending functionality through EF Core settings, I found an alternative way to initialize the database in the test container using SQL scripts. This is achieved through WithResourceMapping:

WithResourceMapping("./Scripts/extensions.sql", "/docker-entrypoint-initdb.d")

What is the role of docker-entrypoint-initdb.d?

The docker-entrypoint-initdb.d directory allows you to provide SQL or script files that PostgreSQL automatically executes during container initialization. This approach is ideal for preloading extensions or setting up other database configurations without embedding the logic directly in the code.

in conclusion

Using EF Core with Testcontainers, I set up a way to ensure that every test gets a fresh and clean database. Extensions such as Vector, btree_gin, and pg_trgm provide useful functionality, and automation makes it easy to get everything ready without extra work. This makes testing reliable and easy to maintain.