使用 C# 存取 PostgreSQL

之前筆記使用 C# 存取 Cassandra 提到想要將 log 存放至 NoSQL 中而正在嘗試某幾套 NoSQL，現在就來看看 PostgreSQL 的使用吧

基本環境說明

在 mac 上使用 docker 建立 PostgreSQL，接著使用 C# 連線至 PostgreSQL 執行基本 CRUD

macOS Mojave 10.14.2
Docker Community 18.09.1
Bogus 25.0.4
PostgreSQL 11.2
Npgsql 4.0.4

建立 PostgreSQL instance

透過 docker 僅建立單一 local PostgreSQL db instance，並設定預設帳號 postgres 密碼為 pass.123

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=pass.123 postgres

使用方式

建立 Database
```
create database benchmark;
```
建立 Schema
```
create schema test;
```

建立 Table

欄位使用 jsonb 是讀取快，json 則是寫入快，不過 json 欄位在 update 時沒有 jsonb_set 這類函數可用，針對 json 的更新我還沒找到簡易做法

create table test.Users
(
    Uid serial not null,
    "User" Jsonb
);

create unique index Users_Uid_uindex
    on test.Users (Uid);

alter table test.Users
    add constraint Users_pk
        primary key (Uid);

安裝 Npgsql NuGet 套件 : Npgsql - .NET Access to PostgreSQL
本次測試使用 Npgsql 版本為 4.0.4
- Package Manager
```
Install-Package Npgsql
```
- .NET CLI
```
dotnet add package Npgsql
```

實際存取 PostgreSQL

Insert

//準備連線字串
var connString = "Host=localhost;Port=5432;Username=postgres;Password=pass.123;Database=benchmark";

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();
    //準備 insert 用假資料
    var _user = GetFakeUserData();
                
    //透過 binary 方式來寫入 json (其中 User 欄位因為是保留字需要跳脫)
    using (var writer =conn.BeginBinaryImport("COPY test.users (\"User\") FROM STDIN (FORMAT BINARY)"))
    {
        //開始 row
        writer.StartRow();
        //將欲新增的 row 內容逐一寫入
        writer.Write(JsonConvert.SerializeObject(_user), NpgsqlDbType.Jsonb);
        //row 結束
        writer.Complete();
    }
}

Select

//準備連線字串
var connString = "Host=localhost;Port=5432;Username=postgres;Password=pass.123;Database=benchmark";

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();
    //依 jsonb 欄位過濾 Name 屬性資料
    using (var cmd = new NpgsqlCommand("SELECT * FROM test.users WHERE \"User\"->> 'Name' = @name; ", conn))
    {
        //直接條件內容
        cmd.Parameters.AddWithValue("name", "Yowko");
        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                //取得欄位 id 輸出
                reader.GetInt32(0).Dump();
    }
}

Update

//準備連線字串
var connString = "Host=localhost;Port=5432;Username=postgres;Password=pass.123;Database=benchmark";

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;
        //準備更新語法，將 jsonb 中的 Name 更新為 "Yowko"
        cmd.CommandText = "UPDATE test.users SET \"User\" = jsonb_set(\"User\",'{Name}','\"Yowko\"'::jsonb,false) WHERE \"User\"->> 'Name' = @name; ";
        //加入過濾條件
        cmd.Parameters.AddWithValue("name", "old name");
        cmd.ExecuteNonQuery();
    }
}

Delete

//準備連線字串
var connString = "Host=localhost;Port=5432;Username=postgres;Password=pass.123;Database=benchmark";

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;
        //準備刪除語法
        using (var cmd = new NpgsqlCommand("DELETE FROM test.users WHERE \"User\"->> 'Name' = @name; ", conn))
        {
            //加入過濾條件
            cmd.Parameters.AddWithValue("name", "Yowko");
            cmd.ExecuteNonQuery();
        }
    }
}

心得

PostgreSQL 在普遍印象中是套 RDBMS - 關聯式資料庫管理系統，但因為 open source 的特性，讓許多社群及貢獻者得以持續改善，所以在儲存 json 的功能上有著不輸其他 NoSQL 的表現

但就 C# client - Npgsql 的使用上來看，api 的操作上非常接近 ADO.NET：自行連線控制、手動準備 sql 指令及實際執行都非常相似，但近幾年多數 .NET 開發人員比較習慣的 LINQ 式操作在 Npgsql 上表現並不好

文章目錄

使用 C# 存取 PostgreSQL

基本環境說明

建立 PostgreSQL instance

使用方式

心得

參考資訊