.NET云原生应用实践（三）：连接到PostgreSQL数据库

东方的狼

本章目标

• 实现基于PostgreSQL的SDAC（简单数据访问层）
  
• 将Stickers微服务切换到使用PostgreSQL SDAC
  

为什么选择PostgreSQL数据库？

其实并不一定要选择PostgreSQL数据库，这里主要出于几个方面考虑：

• PostgreSQL免费易用，轻量效率高，能够满足目前的需求
  
• PostgreSQL生态成熟，资源丰富，遇到问题容易排查
  
• 后续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数据库：

• 本地机器直接安装PostgreSQL服务
  
• 使用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：

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

复制代码

这个Dockerfile目前只有一条指令，就是基于postgres:17.0-alpine这个镜像来构建新的镜像，你现在就可以使用docker build命令，基于这个Dockerfile来创建镜像，做法是，在postgresql文件夹下（在Dockerfile相同的目录下），执行docker build命令：

1. $ 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的文件，其内容为：

1. volumes:
2.   stickers_postgres_data:
4. services:
5.   stickers-pgsql:
6.     image: daxnet/stickers-pgsql:dev
7.     build:
8.       context: ./postgresql
9.       dockerfile: Dockerfile
10.     environment:
11.       - POSTGRES_USER=postgres
12.       - POSTGRES_PASSWORD=postgres
13.       - POSTGRES_DB=stickersdb
14.     volumes:
15.         - stickers_postgres_data:/data:Z
16.     ports:
17.         - "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数据库服务了：

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

复制代码

成功启动容器之后，应该可以看到类似下面的输出日志：

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

复制代码

创建数据库与数据表

可以使用pgAdmin等客户端工具连接到数据库，先创建一个名为stickersdb的数据库，然后在这个数据库上，执行下面的SQL语句来创建数据表：

1. CREATE TABLE public.stickers (
2.     "Id" integer NOT NULL,
3.     "Title" character varying(128) NOT NULL,
4.     "Content" text NOT NULL,
5.     "DateCreated" timestamp with time zone NOT NULL,
6.     "DateModified" timestamp with time zone
7. );
10. ALTER TABLE public.stickers OWNER TO postgres;
12. ALTER TABLE public.stickers ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY (
13.     SEQUENCE NAME public."stickers_Id_seq"
14.     START WITH 1
15.     INCREMENT BY 1
16.     NO MINVALUE
17.     NO MAXVALUE
18.     CACHE 1
19. );

复制代码

数据库创建成功之后，就可以进行下一步：设计和实现PostgreSQL的数据访问层了。
PostgreSQL数据访问层

在上一讲中，我们已经设计好了数据访问层的基本结构，现在只需要在上面进行扩展即可，详细的UML类图如下：

上图中淡黄色部分就是这次新增的类以及对外部类库的依赖。我们会新建一个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：

1. [AttributeUsage(AttributeTargets.Class)]
2. public sealed class TableAttribute(string tableName) : Attribute
3. {
4.     public string TableName { get; } = tableName;
5. }
7. [AttributeUsage(AttributeTargets.Property)]
8. public sealed class FieldAttribute(string fieldName) : Attribute
9. {
10.     public string FieldName { get; } = fieldName;
11. }

复制代码

然后，在模型类型上，应用这些Attribute：

1. [Table("Stickers")]
2. public class Sticker(string title, string content): IEntity
3. {
4.     public Sticker() : this(string.Empty, string.Empty) { }
5.    
6.     public int Id { get; set; }
8.     [Required]
9.     [StringLength(50)]
10.     public string Title { get; set; } = title;
12.     public string Content { get; set; } = content;
14.     [Field("DateCreated")]
15.     public DateTime CreatedOn { get; set; } = DateTime.UtcNow;
16.    
17.     [Field("DateModified")]
18.     public DateTime? ModifiedOn { get; set; }
19. }

复制代码

在上面的代码中，Sticker类通过TableAttribute映射到Stickers数据表，而CreatedOn和ModifiedOn属性通过FieldAttribute映射到了DateCreated和DateModified字段，而其它的没有使用FieldAttribute字段的属性，则默认使用属性名作为字段名。于是，就可以使用下面的方法来根据传入的对象类型来获得数据库中的表名和字段名：

1. private static string GetTableName<TEntity>() where TEntity : class, IEntity
2. {
3.     return typeof(TEntity).IsDefined(typeof(TableAttribute), false)
4.         ? $"public.{typeof(TEntity).GetCustomAttribute<TableAttribute>()?.TableName ?? typeof(TEntity).Name}"
5.         : $"public.{typeof(TEntity).Name}";
6. }
7. private static IEnumerable<KeyValuePair<string, string>> GetColumnNames<TEntity>(
8.     params Predicate<PropertyInfo>[] excludes)
9.     where TEntity : class, IEntity
10. {
11.     return from p in typeof(TEntity).GetProperties()
12.         where p.CanRead && p.CanWrite && !excludes.Any(pred => pred(p))
13.         select p.IsDefined(typeof(FieldAttribute), false)
14.             ? new KeyValuePair<string, string>(p.Name, p.GetCustomAttribute<FieldAttribute>()?.FieldName ?? p.Name)
15.             : new KeyValuePair<string, string>(p.Name, p.Name);
16. }

复制代码

然后就可以在PostgreSqlDataAccessor中使用这两个方法来构建SQL语句了。比如在AddAsync方法中：

1. public async Task<int> AddAsync<TEntity>(TEntity entity, CancellationToken cancellationToken = default)
2.     where TEntity : class, IEntity
3. {
4.     var tableName = GetTableName<TEntity>();
5.     var tableFieldNames = string.Join(", ",
6.         GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id))
7.             .Select(n => $""{n.Value}""));
8.     var fieldValueNames = string.Join(", ",
9.         GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id))
10.             .Select(n => $"@{n.Key}"));
11.     var sql = $@"INSERT INTO {tableName} ({tableFieldNames}) VALUES ({fieldValueNames}) RETURNING ""Id""";
12.     await using var sqlConnection = new NpgsqlConnection(connectionString);
13.     var id = await sqlConnection.ExecuteScalarAsync<int>(sql, entity);
14.     entity.Id = id;
15.     return id;
16. }

复制代码

其中GetColumnNames方法的excludes参数是一个断言委托，通过它可以指定不需要获得字段名的属性。比如上面的AddAsync方法中，向数据表增加一条记录的INSERT语句，并不需要将Id值插入（相反，是需要获得数据库返回的Id值），所以构建这条SQL语句的时候，就不需要获取Id属性所对应的字段名。
根据Lambda表达式构建SQL字段名

在ISimplifiedDataAccessor中，GetPaginatedEntitiesAsync方法的第一个参数是一个Lambda表达式，它通过Lambda表达式指定用于排序的对象属性：

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

复制代码

然而，我们使用Dapper和Npgsql查询数据库的时候，是需要构建SQL语句的，因此，这里就需要将Lambda表达式转换为SQL语句，重点就是如何通过Lambda表达式来获得ORDER BY子句的字段名。基本思路是，将Lambda表达式转换为MemberExpression对象，然后通过MemberExpression获得Member属性的Name属性即可。有一种特殊的情况，就是传入的Lambda表达式有可能是一个Convert方法调用（参考上文中StickersController.GetStickersAsync方法的实现），此时就需要先将Convert方法的参数转换为MemberExpression，然后再获得属性名。代码如下：

1. private static string BuildSqlFieldName(Expression expression)
2. {
3.     if (expression is not MemberExpression && expression is not UnaryExpression)
4.         throw new NotSupportedException("Expression is not a member expression");
5.     var memberExpression = expression switch
6.     {
7.         MemberExpression expr => expr,
8.         UnaryExpression { NodeType: ExpressionType.Convert } unaryExpr =>
9.             (MemberExpression)unaryExpr.Operand,
10.         _ => null
11.     };
12.     if (memberExpression is null)
13.         throw new NotSupportedException("Can't infer the member expression from the given expression.");
14.     return memberExpression.Member.IsDefined(typeof(FieldAttribute), false)
15.         ? memberExpression.Member.GetCustomAttribute<FieldAttribute>()?.FieldName ??
16.           memberExpression.Member.Name
17.         : memberExpression.Member.Name;
18. }

复制代码

那么，将Lambda表达式转换成ORDER BY子句，就可以这样实现：

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

复制代码

到这里，或许你会有疑问，在StickersController.GetStickersAsync方法中，它是从客户端RESTful API请求中以字符串形式读入排序字段名的，在该方法中，会将这个字符串的排序字段名转换为Lambda表达式然后传给ISimplifiedDataAccessor（其实也就是这里的PostgreSqlDataAccessor），可是到PostgreSqlDataAccessor中，又将这个Lambda表达式转回了字符串形式用来拼接SQL语句，岂不是多此一举？其实不然，因为从整体设计的角度，ISimplifiedDataAccessor中使用Lambda表达式来定义排序和筛选字段，这个是合理的，因为这样的设计不仅可以满足本文介绍的这种SQL字符串拼接的实现方式，而且还可以满足基于Lambda表达式的数据访问组件的设计（比如Entity Framework）。因此，不能因为一种具体实现的特殊性而影响一种更为通用的设计，说得具体些，此处将Lambda表达式再转换成字段名字符串，是PostgreSqlDataAccessor的特殊性导致的，而不是设计本身的通用性引起的问题。当然，Dapper也有一些非官方的扩展库，允许在排序和筛选的时候直接使用Lambda表达式，不过在这里我还是不想引入过多的外部依赖，把问题变得直接简单一些，同时也可以引出这里的设计问题和解决思路。
根据Lambda表达式构建WHERE子句

在StickersController中，有下面的代码，这段代码的目的是在创建一个“贴纸”的时候，先判断相同标题的贴纸是否已经存在：

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

复制代码

在ISimplifiedDataAccessor中，ExistsAsync方法通过一个Lambda表达式参数来决定数据的筛选条件，于是根据PostgreSqlDataAccessor的实现方式，这里就需要将这个Lambda表达式转换成WHERE子句，从而可以在Dapper上执行SQL语句进行查询。比如，假设上面的代码中，title的值是“这是一张测试贴纸”，那么，产生的WHERE子句就应该是这样：

1. WHERE "Title" = '这是一张测试贴纸'

复制代码

下面就是将Lambda表达式转换为SQL WHERE子句的代码：
[code]private static string BuildSqlWhereClause(Expression expression){    // 根据expression的类型来确定SQL中的比较操作符    var oper = expression.NodeType switch    {        ExpressionType.Equal => "=",        ExpressionType.NotEqual => "",        ExpressionType.GreaterThan => ">",        ExpressionType.GreaterThanOrEqual => ">=",        ExpressionType.LessThan => "