技術

ASP.NET Core WebアプリでNpgsqlを使いPostgreSQLに接続する

はじめに

前回は、NuGetでライブラリを入れて試すところまで実施した。
今回は、NuGetでNpgsqというPostgreSQLに接続するライブラリを使用し、PostgreSQLに接続するまでやってみる。

環境

Windows 11 Professional
Docker Desktop 4.34.3 (170107)
ASP.NET Core 8.0
PostgreSQL 17.0

Npgsqlとは

https://www.npgsql.org/

Npgsqlは、.NETアプリケーションからPostgreSQLデータベースに接続するためのオープンソースのデータプロバイダ。
PostgreSQLに特化したADO.NETのデータプロバイダとして機能し、.NETの標準的なデータアクセス技術（たとえば、Entity Framework Core）と統合できる。

Npgsqlは、PostgreSQLの機能に完全対応しており、クエリの実行、トランザクションの管理、ストアドプロシージャの呼び出しといった一般的なデータベース操作をサポートする。
これにより、.NETアプリケーション開発者は、PostgreSQLの強力な機能を活用しながら効率的にデータベース操作が可能となる。

Npgsqlの大きな利点は、高いパフォーマンスと使いやすさである。
さらに、非同期プログラミングにも対応しており、データベースへのアクセスを非同期で行うことでアプリケーションのパフォーマンスを向上させることができる。

NpgsqlはNuGetパッケージとして提供されており、プロジェクトに簡単に追加できる。パッケージをインストールした後は、接続文字列を指定することでPostgreSQLデータベースに接続し、SQLコマンドを発行してデータを読み書きすることが可能である。

準備

今回作成したアプリケーション

GitHubにあるので、面倒だということであればこれを見ればよい。
https://github.com/katsuobushiFPGA/ASPCoreNetSample

PostgreSQLをDockerで用意する

以下のように作成する。

compose.yml
services:
  db:
    image: postgres:17
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

initdb/init.sqlとして以下を作成する。

initdb/init.sql
-- 新しいユーザーの作成
CREATE USER app_user WITH PASSWORD 'password';

-- 新しいユーザーに権限を付与
GRANT ALL PRIVILEGES ON DATABASE postgres TO app_user;

-- ユーザーを管理するテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,         -- ユーザーID (自動生成)
    username VARCHAR(50) NOT NULL, -- ユーザー名
    email VARCHAR(100) UNIQUE NOT NULL, -- メールアドレス (ユニーク)
    password_hash VARCHAR(255) NOT NULL, -- パスワードのハッシュ
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 作成日時
);

-- TODO項目を管理するテーブル
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,         -- タスクID (自動生成)
    user_id INT NOT NULL,          -- 関連ユーザーのID
    title VARCHAR(255) NOT NULL,   -- タスクのタイトル
    description TEXT,              -- タスクの説明 (任意)
    is_completed BOOLEAN DEFAULT FALSE, -- タスクが完了しているかどうか
    due_date DATE,                 -- 期限 (任意)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 作成日時
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 更新日時
    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) -- ユーザーIDに対する外部キー
);

-- インデックスを作成して検索を高速化
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);

-- サンプルユーザーとタスクを挿入 (データの確認用)
INSERT INTO users (username, email, password_hash) VALUES
('john_doe', 'john@example.com', 'hashed_password'),
('jane_doe', 'jane@example.com', 'hashed_password');

INSERT INTO tasks (user_id, title, description, due_date) VALUES
(1, 'Buy groceries', 'Milk, eggs, and bread', '2024-10-30'),
(1, 'Finish project', 'Complete the project by the end of the week', '2024-11-01'),
(2, 'Plan vacation', 'Book tickets and find accommodations', '2024-11-15');

作成/準備ができたら、compose.ymlの階層でコンテナを立ち上げる。

docker compose up -d

A5M2やPgAdminなどで接続をして表示されればOK

今回は、A5M2で接続をして確認をした。

postgresql-docker-01

Npgsqlを入れる

前回作成したWebアプリケーションに入れる。

  1. プロジェクトを右クリック、「NuGetパッケージの管理」を選択する
  2. 「Npgsql」をインストールする
  3. インストール完了後、依存関係に 「Npgsql」が入っていることを確認

Microsoft.EntityFrameworkCoreを入れる

同様に、NuGetで入れる。

  1. プロジェクトを右クリック、「NuGetパッケージの管理」を選択する
  2. 「Microsoft.EntityFrameworkCore」をインストールする
  3. インストール完了後、依存関係に 「Microsoft.EntityFrameworkCore」が入っていることを確認

Npgsql.EntityFrameworkCore.PostgreSQLを入れる

同様に、NuGetで入れる。

  1. プロジェクトを右クリック、「NuGetパッケージの管理」を選択する
  2. 「Npgsql.EntityFrameworkCore.PostgreSQL」をインストールする
  3. インストール完了後、依存関係に 「Npgsql.EntityFrameworkCore.PostgreSQL」が入っていることを確認

Npgsql.EntityFrameworkCore.PostgreSQLを使ってPostgreSQLにアクセスをする

appsettings.jsonに接続情報を追記する

appsettings.json
{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*",

    "ConnectionStrings": {
        "PostgreSqlConnection": "Host=localhost;Port=5432;Database=postgres;Username=postgres;Password=postgres"
    }
}

Entityを作成する

User.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Entity
{
    public class User
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Required]
        [MaxLength(50)]
        [Column("username")]
        public string Username { get; set; }

        [Required]
        [MaxLength(100)]
        [Column("email")]
        public string Email { get; set; }

        [Required]
        [MaxLength(255)]
        [Column("password_hash")]
        public string PasswordHash { get; set; }

        [Column("created_at")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;

        // タスクとのリレーション
        public ICollection<Task> Tasks { get; set; } = new List<Task>();
    }
}
Task.cs
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace Entity
{
    public class Task
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Required]
        [MaxLength(255)]
        [Column("title")]
        public string Title { get; set; }

        [Column("description")]
        public string Description { get; set; }

        [Column("is_completed")]
        public bool IsCompleted { get; set; } = false;

        [Column("due_date")]
        public DateTime? DueDate { get; set; }

        [Column("created_at")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;

        [Column("updated_at")]
        public DateTime UpdatedAt { get; set; } = DateTime.Now;

        // 外部キーの定義
        [ForeignKey("User")]
        [Column("user_id")]
        public int UserId { get; set; }
        public User User { get; set; }
    }
}

DBContextを作成する

Context/ApplicationDbContext.cs
using Entity;
using Microsoft.EntityFrameworkCore;

namespace Context
{
    public class ApplicationDbContext : DbContext
    {
        public DbSet<Entity.User> Users { get; set; }
        public DbSet<Entity.Task> Tasks { get; set; }
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Entity.User>()
                .ToTable("users");

            // tasksテーブルのUserへの外部キー制約を設定
            modelBuilder.Entity<Entity.Task>()
                .ToTable("tasks")
                .HasOne(t => t.User)
                .WithMany(u => u.Tasks)
                .HasForeignKey(t => t.UserId)
                .OnDelete(DeleteBehavior.Cascade);

            base.OnModelCreating(modelBuilder);
        }
    }
}

Program.csでDbContextを登録する

Program.cs
using Microsoft.EntityFrameworkCore;
using Context;

var builder = WebApplication.CreateBuilder(args);

// PostgreSQLへの接続を設定
var connectionString = builder.Configuration.GetConnectionString("PostgreSqlConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString));

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

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

テーブルを表示するコントローラーを作成する

TasksController.cs
using Context;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace WebApplication2.Controllers
{
    public class TasksController : Controller
    {
        private readonly ApplicationDbContext _context;

        public TasksController(ApplicationDbContext context)
        {
            _context = context;
        }

        // GET: Tasks
        public async Task<IActionResult> Index()
        {
            var tasks = await _context.Tasks
                .Include(t => t.User) // ユーザー情報も一緒に取得
                .ToListAsync();

            return View(tasks); // ビューにタスクのリストを渡す
        }

    }
}

コントローラーに対応するビューを作成する

Index.cshtml
@model IEnumerable<Entity.Task>

@{
    ViewData["Title"] = "Tasks List";
}

<h2>Tasks List</h2>

<table class="table">
    <thead>
        <tr>
            <th>タスクID</th>
            <th>ユーザー</th>
            <th>タイトル</th>
            <th>説明</th>
            <th>完了</th>
            <th>期限</th>
            <th>作成日時</th>
            <th>更新日時</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var task in Model)
        {
            <tr>
                <td>@task.Id</td>
                <td>@task.User?.Username</td>
                <td>@task.Title</td>
                <td>@task.Description</td>
                <td>@task.IsCompleted</td>
                <td>@task.DueDate?.ToString("yyyy-MM-dd")</td>
                <td>@task.CreatedAt.ToString("yyyy-MM-dd HH:mm:ss")</td>
                <td>@task.UpdatedAt.ToString("yyyy-MM-dd HH:mm:ss")</td>
            </tr>
        }
    </tbody>
</table>

動作確認

「デバッグ」→「デバッグの開始」より起動する。

https://localhost:7006/tasks にアクセスし、DBの内容が表示されるかを確認する。
以下のようになっていればOK

task-01

プログラムのまとめ

全てのコミット内容は以下となる。
https://github.com/katsuobushiFPGA/ASPCoreNetSample/commit/8e30dfd13e1ceb2075301bd6090ff55d3360b202

補足: NpgsqlとNpgsql.EntityFrameworkCore.PostgreSQLの違いについて

Npgsql と Npgsql.EntityFrameworkCore.PostgreSQL は、両方とも.NETアプリケーションでPostgreSQLデータベースに接続するためのパッケージだが、それぞれ異なる目的で使用される。

Npgsql

Npgsql は、.NET用のPostgreSQLのデータプロバイダー
これは、ADO.NET（データアクセス技術）のラッパーとして、PostgreSQLとの基本的なやり取りを行うためのものである。
直接SQLクエリを実行し、データベースに接続するための機能を提供する。

主な用途は、Entity Framework Coreを使わずに、手動でSQLを実行したり、PostgreSQLデータベースと直接やり取りする場合となる。
NpgsqlConnectionやNpgsqlCommandなどのクラスを使って、データベースとの接続、クエリの実行、結果の取得を行う。

Npgsqlの機能

  • PostgreSQLへの低レベルな接続を管理
  • SQL文を直接記述し、手動でクエリを実行
  • ADO.NET APIと連携してデータリーダーやデータセットの操作が可能

例:

using Npgsql;

var connString = "Host=localhost;Username=my_user;Password=my_password;Database=my_database";

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();

    using (var cmd = new NpgsqlCommand("SELECT * FROM my_table", conn))
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetString(0)); // 0はカラムインデックス
        }
    }
}

Npgsql.EntityFrameworkCore.PostgreSQL

Npgsql.EntityFrameworkCore.PostgreSQL は、Entity Framework Coreを使ってPostgreSQLデータベースとやり取りするための拡張パッケージとなる。
Entity Framework Coreは、.NET用のORM（Object Relational Mapper）であり、データベースとアプリケーション間のデータ操作を抽象化し、SQL文を自動生成する。

主な用途は、Entity Framework Coreを使用してPostgreSQLを扱う場合となる。
DbContextやLINQクエリを使って、オブジェクトとデータベースの行をマッピングし、コードで簡単にデータ操作が可能。

Npgsql.EntityFrameworkCore.PostgreSQLの機能

  • Entity Framework Coreを利用してPostgreSQLとやり取り
  • データベース操作がオブジェクト指向的に抽象化される
  • マイグレーションのサポート
  • LINQを使ったクエリ生成

例

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Username=my_user;Password=my_password;Database=my_database");
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// 使用例
using (var context = new ApplicationDbContext())
{
    var entities = context.MyEntities.Where(e => e.Name == "example").ToList();
}

主な違い

項目NpgsqlNpgsql.EntityFrameworkCore.PostgreSQL
目的PostgreSQLとの直接接続、ADO.NETベースの操作Entity Framework Core を利用したPostgreSQL操作
使用場面手動でSQLクエリを実行する場合、低レベルなデータ操作を行いたい場合ORMによるデータの操作、LINQやマイグレーションを活用したい場合
主要機能SQLの手動実行 (NpgsqlConnection, NpgsqlCommand)DbContextとLINQによるデータ操作 (UseNpgsql)
データ操作方法SQLを直接記述して操作ORMによる自動的なSQL生成、マイグレーションのサポート
クエリ方法手動のSQLクエリLINQまたはDbSetを使用したクエリ
マイグレーションなしEntity Framework Coreのマイグレーション機能をサポート

まとめ

  • Npgsql は、直接SQLを使ってPostgreSQLと対話したい場合に使う。
  • Npgsql.EntityFrameworkCore.PostgreSQL は、Entity Framework Coreを使ってオブジェクト指向でPostgreSQLと対話し、LINQやマイグレーションを使いたい場合に使う。

どちらを使用するかは、アプリケーションでのデータ操作の要件による。

今回は、Npgsql.EntityFrameworkCore.PostgreSQL をメインとして使った。

参考

おわりに

PostgreSQL(DB)に接続するということを試せた。
次回はDapperを使ってみよう。

© 2020 - 2024 kbushi
Hugo で構築されています。
テーマ Stack は Jimmy によって設計されています。