.NET雲原生應用實踐（三）：連線到PostgreSQL資料庫

dax.net發表於2024-10-22
SQL資料庫

本章目標

  1. 實現基於PostgreSQL的SDAC（簡單資料訪問層）
  2. 將Stickers微服務切換到使用PostgreSQL SDAC

為什麼選擇PostgreSQL資料庫？

其實並不一定要選擇PostgreSQL資料庫，這裡主要出於幾個方面考慮：

  1. PostgreSQL免費易用，輕量效率高，能夠滿足目前的需求
  2. PostgreSQL生態成熟，資源豐富，遇到問題容易排查
  3. 後續Keycloak以及Hangfire/Quartz.NET整合PostgreSQL比較方便，減少一個資料庫例項的部署，可以減少一部分成本開銷，至少在我們的這個簡單案例中是這樣

基於文件的MongoDB也是一個不錯的選擇，但是出於上面第三點考慮，有些所需依賴的第三方解決方案對MongoDB的支援並不是那麼完美，所以，在我們的案例中選擇了PostgreSQL作為資料庫。

訪問PostgreSQL資料庫可以使用ADO.NET體系，具體說是使用官方的Npgsql包，然後用ADO.NET的程式設計模型和程式設計正規化來讀寫PostgreSQL資料庫。由於在某些場景下使用ADO.NET還不是特別的方便，比如根據Id查詢某個“貼紙”實體的時候，需要首先構建DbCommand物件，然後將Id作為引數傳入，之後執行DbReader逐行讀入資料，並根據讀入的資料構建Sticker物件，最後關閉資料庫連線。所以，在本案例中，我會選擇一個輕量型的物件對映框架：Dapper來實現基於PostgreSQL的SDAC。

準備PostgreSQL資料庫與開發工具

你可以選擇兩種方式來準備PostgreSQL資料庫：

  1. 本地機器直接安裝PostgreSQL服務
  2. 使用Docker

單從開發的角度，選擇第一種方式更為簡單，下載PostgreSQL伺服器安裝包後直接安裝就能執行起來，但在本案例中，我選擇使用Docker來執行PostgreSQL。首先，部署和分發更為簡單，有一個Dockerfile的定義檔案就可以編譯出Docker映象並在本地執行容器，Dockerfile檔案可以方便地託管在程式碼倉庫中進行版本控制；其次，今後的雲端部署以Docker容器的方式也會更為簡單方便，使用Azure提供的Web App for Containers託管服務，或者部署到Azure Kubernetes Service（AKS），都可以很方便地將容器化的應用程式直接部署到Azure上。

如果你仍然選擇使用本地機器直接安裝PostgreSQL服務的方式，請跳過接下來的Docker部分，直接進入資料庫和資料表的建立部分。

在Docker中執行PostgreSQL資料庫

你可以直接使用docker run命令來執行一個PostgreSQL資料庫，不過，在這裡我們稍微做一點調整：我們基於PostgreSQL的官方映象，自己構建一個自定義的PostgreSQL映象，這樣做的目的到本文最後部分我會介紹。

首先，在src資料夾的同級資料夾中，建立一個名為docker的資料夾，然後再在docker資料夾下，建立一個名為postgresql的資料夾，然後在postgresql資料夾中，新建一個Dockerfile：

$ mkdir -p docker/postgresql
$ cd docker/postgresql
$ echo "FROM postgres:17.0-alpine" >> Dockerfile

這個Dockerfile目前只有一條指令，就是基於postgres:17.0-alpine這個映象來構建新的映象，你現在就可以使用docker build命令，基於這個Dockerfile來建立映象，做法是，在postgresql資料夾下（在Dockerfile相同的目錄下），執行docker build命令：

$ docker build -t daxnet/stickers-pgsql:dev .

注意：這裡我使用daxnet/stickers-pgsql作為映象名稱，因為後面我需要將這個映象釋出到Docker Hub中，所以映象名稱中帶有我在Docker Hub中的Registry名稱。請可以根據自己的情況來決定映象名稱。

注意：在選擇Base Image（也就是這裡的postgres映象）時，通常我們會指定一個特定的tag，而不是使用latest tag，這是為了防止在持續整合的時候由於使用了一個更新版本的映象而導致一些版本相容性問題。

我們還可以使用Docker Compose來構建映象。在docker資料夾下，新建一個名為docker-compose.dev.yaml的檔案，其內容為：

volumes:
  stickers_postgres_data:

services:
  stickers-pgsql:
    image: daxnet/stickers-pgsql:dev
    build:
      context: ./postgresql
      dockerfile: Dockerfile
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=stickersdb
    volumes:
        - stickers_postgres_data:/data:Z
    ports:
        - "5432:5432"

使用Docker Compose的好處是，它可以批次構建多個映象，並且你不需要在每次編譯的時候，都去考慮每個映象應該使用什麼名稱什麼tag，不僅如此，同一個Docker Compose檔案還可以用來定義容器啟動的配置和引數，並將所定義的容器一併執行起來。

請注意這裡的Docker Compose檔名（docker-compose.dev.yaml）中有“dev”字樣，這是因為，我打算在這個Docker Compose檔案中僅包含支援開發環境的基礎設施相關的容器，比如資料庫、Keycloak、日誌儲存、快取、Elasticsearch等等，而今後我們的案例應用程式本身的docker容器並不會包含在這個Compose檔案中，這樣做的好處是，只需要一條Docker Compose命令就將執行和除錯我們的案例程式的所有基礎設施服務全部啟動起來，然後只需要在IDE中除錯我們的程式即可。

準備好上面的docker-compose.dev.yaml檔案之後，即可使用下面兩條命令來編譯和執行PostgreSQL資料庫服務了：

$ docker compose -f docker-compose.dev.yaml build
$ docker compose -f docker-compose.dev.yaml up

成功啟動容器之後，應該可以看到類似下面的輸出日誌：

stickers-pgsql-1  | 2024-10-17 12:55:55.024 UTC [1] LOG:  database system is ready to accept connections

建立資料庫與資料表

可以使用pgAdmin等客戶端工具連線到資料庫，先建立一個名為stickersdb的資料庫，然後在這個資料庫上，執行下面的SQL語句來建立資料表：

CREATE TABLE public.stickers (
    "Id" integer NOT NULL,
    "Title" character varying(128) NOT NULL,
    "Content" text NOT NULL,
    "DateCreated" timestamp with time zone NOT NULL,
    "DateModified" timestamp with time zone
);


ALTER TABLE public.stickers OWNER TO postgres;

ALTER TABLE public.stickers ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public."stickers_Id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

資料庫建立成功之後，就可以進行下一步：設計和實現PostgreSQL的資料訪問層了。

PostgreSQL資料訪問層

在上一講中，我們已經設計好了資料訪問層的基本結構，現在只需要在上面進行擴充套件即可，詳細的UML類圖如下：

.NET雲原生應用實踐（三）：連線到PostgreSQL資料庫

上圖中淡黃色部分就是這次新增的類以及對外部類庫的依賴。我們會新建一個Stickers.DataAccess.PostgreSQL的類庫（包，或者稱之為.NET Assembly），它包含一個主要類：PostgreSqlDataAccessor，對標之前我們設計的Stickers.DataAccess.InMemory類庫和InMemoryDataAccessor類，這個類也實現了ISimplifiedDataAccessor介面，只不過它的具體實現部分需要透過Npgsql來訪問PostgreSQL。正如上面所說，這裡還引用了Dapper庫，用來簡化ADO.NET的操作。

將PostgreSqlDataAccessor置於一個單獨的類庫中，其原因在前一篇文章中我也介紹過，因為PostgreSqlDataAccessor有外部依賴，我們不應該將這種依賴“汙染”到核心庫（Stickers.Common）中，這樣的隔離還可以帶來另一個好處，那就是不同的Simplified Data Accessor（SDAC）可以被打包成不同的元件庫，這樣，不僅可以提高系統的可測試性，而且在實現應用程式時，可以選擇不同的元件接入，提供系統的穩定性和靈活性。

從上圖還可以看出，接下來StickersController將會被注入PostgreSqlDataAccessor的例項，從此處開始，InMemoryDataAccessor將退出歷史舞臺。

接下來，我將介紹一下PostgreSqlDataAccessor中的一些實現細節。

物件關係對映（ORM）

我們沒有引入ORM框架，雖然Dapper的官方定義是一個簡單的物件對映框架，在直接使用Dapper的時候，可以在Dapper上以各種不同的方式實現物件對映（從C#物件到資料庫表、欄位的對映），但是在我們的設計中，卻沒有辦法在PostgreSqlDataAccessor中得知，我們應該基於哪個物件進行對映操作，因為這裡的物件模型Sticker類是一個業務概念，它是定義在StickersController中的，我們不能假設在PostgreSqlDataAccessor中操作的物件型別就是Sticker類，所以也就沒有辦法在PostgreSqlDataAccessor中直接寫出能被Dapper所使用的對映方式。所以，我們需要自己定義一個簡單的物件關係對映機制，然後在PostgreSqlDataAccessor中基於這個機制來生成Dapper所能使用的對映方式。

目前我們的設計還是比較簡單，只有一個業務物件：Sticker，並且也沒有其它物件跟它有直接關係，所以，就怎麼簡單怎麼做，問題也就變成了：將一個物件對映到一張資料表上，並將該物件的屬性對映到表的欄位上，而且，在應用程式中，都是使用已有的資料表和欄位，並不存在需要在應用程式啟動的時候去建立資料表和欄位的需求，所以，在這個對映上，我們甚至不需要去定義各個欄位的型別以及約束條件。

我們可以使用C#的Attribute，用來指定被修飾的物件和其屬性應該對映到哪個表的哪些欄位，例如可以在Stickers.Common中定義兩個Attribute：

[AttributeUsage(AttributeTargets.Class)]
public sealed class TableAttribute(string tableName) : Attribute
{
    public string TableName { get; } = tableName;
}

[AttributeUsage(AttributeTargets.Property)]
public sealed class FieldAttribute(string fieldName) : Attribute
{
    public string FieldName { get; } = fieldName;
}

然後，在模型型別上，應用這些Attribute：

[Table("Stickers")]
public class Sticker(string title, string content): IEntity
{
    public Sticker() : this(string.Empty, string.Empty) { }
    
    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Title { get; set; } = title;

    public string Content { get; set; } = content;

    [Field("DateCreated")]
    public DateTime CreatedOn { get; set; } = DateTime.UtcNow;
    
    [Field("DateModified")]
    public DateTime? ModifiedOn { get; set; }
}

在上面的程式碼中，Sticker類透過TableAttribute對映到Stickers資料表，而CreatedOn和ModifiedOn屬性透過FieldAttribute對映到了DateCreated和DateModified欄位，而其它的沒有使用FieldAttribute欄位的屬性，則預設使用屬性名作為欄位名。於是，就可以使用下面的方法來根據傳入的物件型別來獲得資料庫中的表名和欄位名：

private static string GetTableName<TEntity>() where TEntity : class, IEntity
{
    return typeof(TEntity).IsDefined(typeof(TableAttribute), false)
        ? $"public.{typeof(TEntity).GetCustomAttribute<TableAttribute>()?.TableName ?? typeof(TEntity).Name}"
        : $"public.{typeof(TEntity).Name}";
}
private static IEnumerable<KeyValuePair<string, string>> GetColumnNames<TEntity>(
    params Predicate<PropertyInfo>[] excludes)
    where TEntity : class, IEntity
{
    return from p in typeof(TEntity).GetProperties()
        where p.CanRead && p.CanWrite && !excludes.Any(pred => pred(p))
        select p.IsDefined(typeof(FieldAttribute), false)
            ? new KeyValuePair<string, string>(p.Name, p.GetCustomAttribute<FieldAttribute>()?.FieldName ?? p.Name)
            : new KeyValuePair<string, string>(p.Name, p.Name);
}

然後就可以在PostgreSqlDataAccessor中使用這兩個方法來構建SQL語句了。比如在AddAsync方法中：

public async Task<int> AddAsync<TEntity>(TEntity entity, CancellationToken cancellationToken = default)
    where TEntity : class, IEntity
{
    var tableName = GetTableName<TEntity>();
    var tableFieldNames = string.Join(", ",
        GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id))
            .Select(n => $"\"{n.Value}\""));
    var fieldValueNames = string.Join(", ",
        GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id))
            .Select(n => $"@{n.Key}"));
    var sql = $@"INSERT INTO {tableName} ({tableFieldNames}) VALUES ({fieldValueNames}) RETURNING ""Id""";
    await using var sqlConnection = new NpgsqlConnection(connectionString);
    var id = await sqlConnection.ExecuteScalarAsync<int>(sql, entity);
    entity.Id = id;
    return id;
}

其中GetColumnNames方法的excludes引數是一個斷言委託，透過它可以指定不需要獲得欄位名的屬性。比如上面的AddAsync方法中，向資料表增加一條記錄的INSERT語句，並不需要將Id值插入（相反，是需要獲得資料庫返回的Id值），所以構建這條SQL語句的時候，就不需要獲取Id屬性所對應的欄位名。

根據Lambda表示式構建SQL欄位名

在ISimplifiedDataAccessor中，GetPaginatedEntitiesAsync方法的第一個引數是一個Lambda表示式，它透過Lambda表示式指定用於排序的物件屬性：

Task<Paginated<TEntity>> GetPaginatedEntitiesAsync<TEntity, TField>(Expression<Func<TEntity, TField>> orderByExpression,
    bool sortAscending = true, int pageSize = 25, int pageNumber = 0,
    Expression<Func<TEntity, bool>>? filterExpression = null, CancellationToken cancellationToken = default)
    where TEntity : class, IEntity;

然而，我們使用Dapper和Npgsql查詢資料庫的時候，是需要構建SQL語句的，因此，這裡就需要將Lambda表示式轉換為SQL語句，重點就是如何透過Lambda表示式來獲得ORDER BY子句的欄位名。基本思路是，將Lambda表示式轉換為MemberExpression物件，然後透過MemberExpression獲得Member屬性的Name屬性即可。有一種特殊的情況，就是傳入的Lambda表示式有可能是一個Convert方法呼叫（參考上文中StickersController.GetStickersAsync方法的實現），此時就需要先將Convert方法的引數轉換為MemberExpression，然後再獲得屬性名。程式碼如下：

private static string BuildSqlFieldName(Expression expression)
{
    if (expression is not MemberExpression && expression is not UnaryExpression)
        throw new NotSupportedException("Expression is not a member expression");
    var memberExpression = expression switch
    {
        MemberExpression expr => expr,
        UnaryExpression { NodeType: ExpressionType.Convert } unaryExpr =>
            (MemberExpression)unaryExpr.Operand,
        _ => null
    };
    if (memberExpression is null)
        throw new NotSupportedException("Can't infer the member expression from the given expression.");
    return memberExpression.Member.IsDefined(typeof(FieldAttribute), false)
        ? memberExpression.Member.GetCustomAttribute<FieldAttribute>()?.FieldName ??
          memberExpression.Member.Name
        : memberExpression.Member.Name;
}

那麼，將Lambda表示式轉換成ORDER BY子句，就可以這樣實現：

var sortExpression = BuildSqlFieldName(orderByExpression.Body);
if (!string.IsNullOrEmpty(sortExpression))
{
    sqlBuilder.Append($@"ORDER BY ""{sortExpression}"" ");
    // ...
}

到這裡，或許你會有疑問，在StickersController.GetStickersAsync方法中，它是從客戶端RESTful API請求中以字串形式讀入排序欄位名的，在該方法中，會將這個字串的排序欄位名轉換為Lambda表示式然後傳給ISimplifiedDataAccessor（其實也就是這裡的PostgreSqlDataAccessor），可是到PostgreSqlDataAccessor中，又將這個Lambda表示式轉回了字串形式用來拼接SQL語句，豈不是多此一舉？其實不然，因為從整體設計的角度，ISimplifiedDataAccessor中使用Lambda表示式來定義排序和篩選欄位，這個是合理的，因為這樣的設計不僅可以滿足本文介紹的這種SQL字串拼接的實現方式，而且還可以滿足基於Lambda表示式的資料訪問元件的設計（比如Entity Framework）。因此，不能因為一種具體實現的特殊性而影響一種更為通用的設計，說得具體些，此處將Lambda表示式再轉換成欄位名字串，是PostgreSqlDataAccessor的特殊性導致的，而不是設計本身的通用性引起的問題。當然，Dapper也有一些非官方的擴充套件庫，允許在排序和篩選的時候直接使用Lambda表示式，不過在這裡我還是不想引入過多的外部依賴，把問題變得直接簡單一些，同時也可以引出這裡的設計問題和解決思路。

根據Lambda表示式構建WHERE子句

在StickersController中，有下面的程式碼，這段程式碼的目的是在建立一個“貼紙”的時候，先判斷相同標題的貼紙是否已經存在：

var exists = await dac.ExistsAsync<Sticker>(s => s.Title == title);
if (exists) return Conflict($"""Sticker "{sticker.Title}" already exists.""");

在ISimplifiedDataAccessor中，ExistsAsync方法透過一個Lambda表示式引數來決定資料的篩選條件，於是根據PostgreSqlDataAccessor的實現方式，這裡就需要將這個Lambda表示式轉換成WHERE子句，從而可以在Dapper上執行SQL語句進行查詢。比如，假設上面的程式碼中，title的值是“這是一張測試貼紙”，那麼，產生的WHERE子句就應該是這樣：

WHERE "Title" = '這是一張測試貼紙'

下面就是將Lambda表示式轉換為SQL WHERE子句的程式碼：

private static string BuildSqlWhereClause(Expression expression)
{
    // 根據expression的型別來確定SQL中的比較運算子
    var oper = expression.NodeType switch
    {
        ExpressionType.Equal => "=",
        ExpressionType.NotEqual => "<>",
        ExpressionType.GreaterThan => ">",
        ExpressionType.GreaterThanOrEqual => ">=",
        ExpressionType.LessThan => "<",
        ExpressionType.LessThanOrEqual => "<=",
        _ => null
    };

    // 目前僅支援上面列出的這些運算子
    if (string.IsNullOrEmpty(oper)) throw new NotSupportedException("The filter expression is not supported.");

    if (expression is not BinaryExpression { Left: MemberExpression leftMemberExpression } binaryExpression)
        throw new NotSupportedException("The filter expression is not supported.");

    // 獲得資料庫中對應的欄位名
    var fieldName = BuildSqlFieldName(leftMemberExpression);
    string? fieldValue = null;
    
    // 獲得用於WHERE子句的欄位的值，如果BinaryExpression
    // 的右邊部分是一個常量表示式，則直接使用常量，否則就要用
    // 反射的方式計算獲得欄位的值。
    switch (binaryExpression.Right)
    {
        case ConstantExpression rightConstExpression:
            fieldValue = FormatValue(rightConstExpression.Value);
            break;
        case MemberExpression rightMemberExpression:
            var rightConst = rightMemberExpression.Expression as ConstantExpression;
            var member = rightMemberExpression.Member.DeclaringType;
            fieldValue = FormatValue(member?.GetField(rightMemberExpression.Member.Name)
                ?.GetValue(rightConst?.Value));
            break;
    }

    // 返回WHERE子句的組成部分
    if (!string.IsNullOrEmpty(fieldValue))
        return $"""
                "{fieldName}" {oper} {fieldValue}
                """;

    throw new NotSupportedException("The filter expression is not supported.");

    // 將欄位值進行格式化的本地方法，比如如果值是一個字串，就要根據PostgreSQL的
    // SQL語句規範，將值的兩邊加上單引號
    string? FormatValue(object? value)
    {
        return value switch
        {
            null => null,
            string => $"'{value}'",
            _ => value.ToString()
        };
    }
}

我加了一些註釋，但基本上可以歸納為：

  1. 確保Lambda表示式是一個Binary Expression
  2. 從該表示式獲得比較運算子
  3. 從Binary Expression的左邊部分獲得欄位名
  4. 從Binary Expression的右邊部分獲得欄位值
  5. 將欄位名、運算子、欄位值拼接成一個條件語句
  6. 目前僅支援有限的比較運算子，並且不支援複雜的條件表示式（AND、OR這些）

如果有興趣還可以繼續擴充套件上面的方法，使其能夠支援更為複雜的WHERE子句的構建邏輯，這裡就不再展開了。

PostgreSQL下分頁查詢的實現

分頁查詢需要在資料庫這一層實現，其原因在《在ASP.NET Core Web API上動態構建Lambda表示式實現指定欄位的資料排序》一文中我已經介紹過，就不多囉嗦了。這裡的一個重點是，如何在一次資料庫查詢中同時獲得當前頁的資料集以及一共有多少條記錄。在PostgreSQL中，要實現這個邏輯有不少方法，我選擇了一個比較簡單的，雖然它的效能並不一定是最好的，但目前來說已經夠用了。

可以使用類似這樣的SELECT語句來實現同時返回分頁的資料集和總資料條數：

SELECT "Id" Id, 
    "Title" Title, 
    "Content" Content, 
    "DateCreated" CreatedOn, 
    "DateModified" ModifiedOn, 
    COUNT(*) OVER() AS "TotalCount" 
FROM public.Stickers 
ORDER BY "Id" 
OFFSET 0 LIMIT 20

在執行完這條SQL語句之後，我們不能透過Dapper的connection.QueryAsync<TEntity>(sql)呼叫直接將結果集轉換為Sticker物件列表，因為這條SQL語句的返回欄位中，不僅包含了Sticker物件的所有欄位，而且還包含了一個用於儲存記錄總條數的TotalCount欄位。直接將查詢結果集轉換為Sticker物件列表會丟失TotalCount資訊。

所以，這裡只能使用connection.QueryAsync(sql)這個函式過載（注意這裡並不帶有泛型引數）來獲取一個動態（dynamic）物件的列表，由於這些動態物件本身都是IDictionary<string, object>的實現型別，所以，在讀入這些物件的時候，只需要直接建立Sticker物件，然後透過反射，把每個欄位的值賦上去就行了：

private static TEntity? BuildEntityFromDictionary<TEntity>(IDictionary<string, object> dictionary)
        where TEntity : class, IEntity
{
    var properties = from p in typeof(TEntity).GetProperties()
        where p.CanRead && p.CanWrite && dictionary.ContainsKey(p.Name.ToLower())
        select p;
    var obj = (TEntity?)Activator.CreateInstance(typeof(TEntity));
    foreach (var property in properties) property.SetValue(obj, dictionary[property.Name.ToLower()]);

    return obj;
}

在這裡使用dynamic和反射，會影響效能嗎？使用dynamic和反射當然沒有直接訪問物件的效能好，但是相對於與外部儲存機制的訪問和網路傳輸的延遲而言，這點效能損耗還是可以忽略不計的。事實上如果不是自己拼接SQL語句，而是使用ORM框架，那麼不僅在某些技術的實現部分（對映、表聯結JOIN等），而且在一些附加的功能實現（物件狀態跟蹤等）上，都會有效能損耗，所以不必多慮。

實現分頁的詳細程式碼這裡就不列出來了，請直接參考本章案例程式碼即可。

在StickersController上使用PostgreSqlDataAccessor

在成功實現了PostgreSqlDataAccessor之後，在StickersController上使用它就變得非常簡單。首先，將PostgreSQL的連線字串作為配置引數提供給ASP.NET Core Web API應用程式，目前我們還處於除錯階段，暫時先放在appsettings.Development.json檔案中即可，在這個檔案中加一個db的屬性，然後指定連線字串：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "db": {
    "connectionString": "User ID=postgres;Password=postgres;Host=localhost;Port=5432;Database=stickersdb;Pooling=true;Connection Lifetime=0;"
  }
}

然後，在Stickers.WebApi專案上新增對Stickers.DataAccess.PostgreSQL專案的引用，再在Program.cs檔案中，將PostgreSqlDataAccessor注入即可：

var dbConnectionString = builder.Configuration["db:connectionString"];
if (string.IsNullOrWhiteSpace(dbConnectionString))
    throw new ApplicationException("The database connection string is missing.");

builder.Services.AddSingleton<ISimplifiedDataAccessor, PostgreSqlDataAccessor>(_ =>
    new PostgreSqlDataAccessor(dbConnectionString));

至此，StickersController就可以透過PostgreSqlDataAccessor來使用PostgreSQL資料庫了，由於我們採用了合理的設計，因此在資料訪問層實現了無縫替換，整個過程沒有修改StickersController中的一行程式碼。

讓程式執行起來

首先啟動PostgreSQL資料庫（我用docker啟動）：

$ cd docker
$ docker compose -f docker-compose.dev.yaml up

然後，在Visual Studio 2022或者JetBrains Rider中，按下F5直接除錯Stickers.WebApi應用程式，然後新建幾條貼紙資料（為了減少篇幅，這裡我就只建一條）：

daxnet@daxnet-HP-ZBook:~/Projects/stickers/docker$ curl -X 'POST' \
  'http://localhost:5141/stickers' \
  -H 'accept: */*' \
  -H 'Content-Type: application/json-patch+json' \
  -d '{
  "title": "測試貼紙",
  "content": "這是一張測試貼紙。"
}' -v && echo
Note: Unnecessary use of -X or --request, POST is already inferred.
* Host localhost:5141 was resolved.
* IPv6: ::1
* IPv4: 127.0.0.1
*   Trying [::1]:5141...
* Connected to localhost (::1) port 5141
> POST /stickers HTTP/1.1
> Host: localhost:5141
> User-Agent: curl/8.5.0
> accept: */*
> Content-Type: application/json-patch+json
> Content-Length: 73
> 
< HTTP/1.1 201 Created
< Content-Type: application/json; charset=utf-8
< Date: Tue, 22 Oct 2024 13:26:19 GMT
< Server: Kestrel
< Location: http://localhost:5141/stickers/7
< Transfer-Encoding: chunked
< 
* Connection #0 to host localhost left intact
{"id":7,"title":"測試貼紙","content":"這是一張測試貼紙。","createdOn":"2024-10-22T13:26:19.834994Z","modifiedOn":null}

然後，呼叫獲取貼紙API，每頁2條記錄，以貼紙建立時間做降序排列：

daxnet@daxnet-HP-ZBook:~/Projects/stickers/docker$ curl "http://localhost:5141/stickers?sort=CreatedOn&asc=false&page=0&size=2" | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   310    0   310    0     0  91122      0 --:--:-- --:--:-- --:--:--  100k
{
  "items": [
    {
      "id": 7,
      "title": "測試貼紙",
      "content": "這是一張測試貼紙。",
      "createdOn": "2024-10-22T13:26:19.834994Z",
      "modifiedOn": null
    },
    {
      "id": 6,
      "title": "this is a text",
      "content": "test",
      "createdOn": "2024-10-22T12:20:44.83564Z",
      "modifiedOn": null
    }
  ],
  "pageIndex": 0,
  "pageSize": 2,
  "totalCount": 5,
  "totalPages": 3
}

題外話：資料庫和資料表的初始化

有一個問題，就是如果我是第一次執行PostgreSQL資料庫容器，或者是容器所依賴的卷被刪掉了，再次執行PostgreSQL容器時，資料庫和資料表都沒有了，那我怎麼去新建資料庫和資料表呢？其實，PostgreSQL Docker映象本身是支援資料庫初始化指令碼的，也就是在資料庫正常啟動之後，PostgreSQL會從/docker-entrypoint-initdb.d目錄中按字母順序逐個讀入SQL檔案然後逐一執行。於是，問題就變得非常簡單了，就是把資料庫和資料表的初始化SQL指令碼檔案放在這個目錄下就行了，但是一定要注意：SQL檔案中的語句必須是冪等的。

在docker\postgresql目錄下的Dockerfile中多加一行：

FROM postgres:17.0-alpine
COPY *.sql /docker-entrypoint-initdb.d/

然後，新建一個名為1_create_stickers_db.sql的SQL檔案，內容為：

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP TABLE IF EXISTS public.stickers;
SET default_tablespace = '';
SET default_table_access_method = heap;

CREATE TABLE public.stickers (
    "Id" integer NOT NULL,
    "Title" character varying(128) NOT NULL,
    "Content" text NOT NULL,
    "DateCreated" timestamp with time zone NOT NULL,
    "DateModified" timestamp with time zone
);


ALTER TABLE public.stickers OWNER TO postgres;

ALTER TABLE public.stickers ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public."stickers_Id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

重新編譯Docker映象重新啟動PostgreSQL容器，此時如果該容器所使用的卷不存在的話，資料庫和資料表就會自動建立。

總結

本文比較長，主要是從幾個關鍵點介紹了PostgreSqlDataAccessor的實現過程中遇到的問題，然後針對StickersController切換到PostgreSqlDataAccessor以及資料庫的初始化等內容進行了簡單的介紹。下一講將開始整合認證與授權部分，強烈建議有興趣的讀者先閱讀下面的文章：

  • 《在Keycloak中實現多租戶並在ASP.NET Core下進行驗證》
  • 《Keycloak中授權的實現》
  • 《ASP.NET Core Web API下基於Keycloak的多租戶使用者授權的實現》

原始碼

本章節相關原始碼在此：

https://gitee.com/daxnet/stickers/tree/chapter_3/

下載原始碼後，進入docker目錄，然後編譯並啟動容器：

$ docker compose -f docker-compose.dev.yaml build
$ docker compose -f docker-compose.dev.yaml up

現在就可以直接用Visual Studio 2022或者JetBrains Rider開啟stickers.sln解決方案檔案，並啟動Stickers.WebApi進行除錯執行了。

相關文章