Technique Bulk insert avec Npgsql

Julien Chomarat Photo de Julien Chomarat

Julien Chomarat

Bulk insert avec Npgsql

PostgreSQL propose des mécanismes d'import/export de données en masse nettement plus performants que l'exécution répétée de commandes SQL classiques (INSERT/SELECT). Dans cet article, nous explorerons comment exploiter ces fonctionnalités avec Npgsql et EF Core pour réaliser un import de 100 000 enregistrements de manière efficace et sécurisée.

Mise en place du projet

Commencez par créer un nouveau répertoire et initialisez un projet console dotnet. Exécutez les commandes suivantes dans votre terminal :

# Création du projet console
dotnet new console --name PgBulk --output .

# Ajout des packages NuGet
dotnet add package Microsoft.EntityFrameworkCore.Design --version 9.0.1
dotnet add package Microsoft.Extensions.Hosting --version 9.0.1
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 9.0.3

# Ajout de la CLI dotnet-ef
dotnet tool install dotnet-ef --version 9.0.1 --create-manifest-if-needed

Configuration du contexte de base de données

Dans un fichier TestDbContext.cs, définissez le contexte et l'entité Employee :

using Microsoft.EntityFrameworkCore;

public class Employee
{
    public Guid Id { get; set; }
    public required string Name { get; set; }
    public DateOnly BirthDate { get; set; }
    public int SizeInCm { get; set; }
    public bool IsActive { get; set; }
}

public class TestDbContext(DbContextOptions<TestDbContext> options)
  : DbContext(options)
{
    public DbSet<Employee> Employees => Set<Employee>();
}

Configuration de l'application

Dans Program.cs, configurez l'hôte et le contexte de base de données :

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

var host = Host.CreateDefaultBuilder(args)
    .ConfigureServices(services =>
    {
        services.AddDbContextPool<TestDbContext>(
            opt => opt.UseNpgsql(
              "Server=localhost;Database=PgBulk;User Id=postgres;Password=Pwd12345!;")
        );
    })
    .Build();

Création et migration de la base de données

Générez la migration initiale avec :

dotnet ef migrations add InitialCreate

Ensuite, lancez un conteneur PostgreSQL pour vos tests :

docker run -d --name pg-bulk-test -e POSTGRES_PASSWORD=Pwd12345! -p 5432:5432 postgres:17.2

Appliquez la migration pour créer la base et son schéma :

dotnet ef database update

Génération et import de données

Création d'un jeu de données

Pour simuler un import en masse, nous allons générer 100 000 enregistrements d'employés à l'aide de la bibliothèque Bogus.

Ajoutez d'abord le package :

# Ajout du package NuGet Bogus
dotnet add package Bogus --version 35.6.1

Puis, ajoutez le code suivant à la fin de votre fichier Program.cs :

var employees = new Faker<Employee>()
        .StrictMode(true)
        .RuleFor(e => e.Id, f => Guid.CreateVersion7())
        .RuleFor(e => e.Name, f => f.Name.FullName())
        .RuleFor(e => e.BirthDate, f =>
            f.Date.BetweenDateOnly(new DateOnly(1950,1,1), new DateOnly(2000, 12, 31)))
        .RuleFor(e => e.SizeInCm, f => f.Random.Number(150, 200))
        .RuleFor(e => e.IsActive, f => f.Random.Bool())
        .Generate(100_000);

Import des données dans PostgreSQL

L'import en masse se réalise en exploitant la commande SQL COPY via la class NpgsqlBinaryImporter de Npgsql.

Voici le code d'import détaillé, que vous placez à la fin de votre Program.cs

using var db = host.Services.GetRequiredService<TestDbContext>();

Console.WriteLine($"[BEFORE] Total employees => {await db.Employees.CountAsync()}");

var conn = db.Database.GetDbConnection() as NpgsqlConnection
  ?? throw new InvalidOperationException();
await conn.OpenAsync();

async using (var writer = await conn.BeginBinaryImportAsync(
  """COPY "Employees" ("Id", "Name", "BirthDate", "SizeInCm", "IsActive") FROM STDIN (FORMAT BINARY)"""))
{
    foreach (var emp in employees)
    {
        await writer.StartRowAsync();
        await writer.WriteAsync(emp.Id, NpgsqlDbType.Uuid);
        await writer.WriteAsync(emp.Name, NpgsqlDbType.Text);
        await writer.WriteAsync(emp.BirthDate, NpgsqlDbType.Date);
        await writer.WriteAsync(emp.SizeInCm, NpgsqlDbType.Integer);
        await writer.WriteAsync(emp.IsActive, NpgsqlDbType.Boolean);
    }

    await writer.CompleteAsync();
}

Console.WriteLine($"[AFTER] Total employees => {await db.Employees.CountAsync()}");

Pour exécuter l'application et procéder à l'import, lancez :

dotnet run

Si tout se déroule correctement, vous devriez obtenir une sortie similaire à :

nu> dotnet run
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT count(*)::int
      FROM "Employees" AS e
[BEFORE] Total employees => 0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT count(*)::int
      FROM "Employees" AS e
[AFTER] Total employees => 100000

Analyse du code d'import

Quelques points essentiels à noter concernant le mécanisme d'import :

1. Accès direct à la connexion Npgsql

EF Core encapsule la connexion en tant que DbConnection, il est donc nécessaire de la downcaster en NpgsqlConnection pour accéder aux fonctionnalités spécifiques à PostgreSQL.

Il vous faut aussi ouvrir la connexion à la base données.

var conn = db.Database.GetDbConnection() as NpgsqlConnection
  ?? throw new InvalidOperationException();
await conn.OpenAsync();

2. Utilisation de la commande COPY

Le SQL utilisé ici exploite la commande COPY qui définit la table, les colonnes et le format d'import.

L'appel à CompleteAsync à la fin de l'import est obligatoire sinon la transaction sera annulée.

async using (var writer = await conn.BeginBinaryImportAsync(
  """COPY "Employees" ("Id", "Name", "BirthDate", "SizeInCm", "IsActive") FROM STDIN (FORMAT BINARY)"""))
{
    // ...

    await writer.CompleteAsync();
}

La fonction SQL COPY se décompose comme cela

-- table_name pour notre exemple "Employees"
COPY table_name
-- On ajoute ici la liste des colonnes pour notre exemple
-- ("Id", "Name", "BirthDate", "SizeInCm", "IsActive")
(column_name_1, column_name_2, ..., column_name_n)
-- L'origine des données viendra de notre application
FROM STDIN
-- Le format sera BINARY
(FORMAT BINARY)

3. Rôle des méthodes StartRowAsync et WriteAsync

Chaque ligne à importer doit commencer par un appel à StartRowAsync.

Ensuite suivi de plusieurs appels à WriteAsync pour insérer les valeurs dans l'ordre défini avec un type défini.

await writer.StartRowAsync();
await writer.WriteAsync(emp.Id, NpgsqlDbType.Uuid);
await writer.WriteAsync(emp.Name, NpgsqlDbType.Text);
await writer.WriteAsync(emp.BirthDate, NpgsqlDbType.Date);
await writer.WriteAsync(emp.SizeInCm, NpgsqlDbType.Integer);
await writer.WriteAsync(emp.IsActive, NpgsqlDbType.Boolean);

Attention

Il faut faire très attention à la méthode WriteAsync qui existe avec les signatures suivantes

public Task WriteAsync<T>(T value, CancellationToken cancellationToken = default)
public Task WriteAsync<T>(T value, NpgsqlDbType npgsqlDbType, CancellationToken cancellationToken = default)
public Task WriteAsync<T>(T value, string dataTypeName, CancellationToken cancellationToken = default)

Npgsql offre plusieurs surcharges pour WriteAsync. Privilégiez la version qui prend en paramètre un NpgsqlDbType pour éviter tout risque de conversion erronée, pouvant conduire à des exceptions ou à des corruptions silencieuses des données.

4. Correspondance des types SQL et dotnet

Pour vérifier la correspondance des types utilisés lors de l'import, consultez la migration EF Core générée. Par exemple, la table Employees est créée avec les types suivants :

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Employees",
        columns: table => new
        {
            Id = table.Column<Guid>(type: "uuid", nullable: false),
            Name = table.Column<string>(type: "text", nullable: false),
            BirthDate = table.Column<DateOnly>(type: "date", nullable: false),
            SizeInCm = table.Column<int>(type: "integer", nullable: false),
            IsActive = table.Column<bool>(type: "boolean", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Employees", x => x.Id);
        });
}

Conclusion

L'import en masse via la commande COPY est un outil puissant pour optimiser vos traitements de données, mais il requiert une attention particulière. Assurez-vous toujours que :

  • Les types des colonnes dans la base et ceux spécifiés dans WriteAsync sont cohérents.
  • La connexion utilisée est bien de type NpgsqlConnection pour bénéficier des fonctionnalités spécifiques à PostgreSQL.
  • La méthode CompleteAsync est appelée pour valider l'import et éviter un rollback involontaire.

Pour approfondir le sujet, vous pouvez consulter les documentations suivantes :

Une fois vos tests terminés, vous pouvez nettoyer votre environnement en supprimant le conteneur Docker :

docker rm pg-bulk-test --force
Tags : C# EntityFramework PostgreSQL