NpgsqlとEntityFramework Coreを使ってPostgreSQLをCRUDする

C#で書かれた.NET CoreアプリケーションからPostgreSQLのデータを操作する機会があり、NpgsqlとEntity Framework Core(EF Core)の使い方を調べました。

Npgsql

NpgsqlはPostgreSQL用のADO.NETデータプロバイダで、C#やVB.NETからPostgreSQLに接続してSQLを実行するためのライブラリです。
NpgsqlではEF Core用のプロバイダを提供しています。
Npgsql - .NET Access to PostgreSQL | Npgsql Documentation

DBの構築

今回はDocker HubのPostgreSQLコンテナを使ってテスト用のDB環境を作りました。
https://hub.docker.com/_/postgres/

テスト用にmembersテーブルとtodosテーブルを作成します。
membersとtodosは1:Nのリレーションを持っており、membersのidカラムが外部キーとなっています。

members

カラム名	型	制約
id	varchar(32)	primary key
name	varchar(32)	not null

create table members (
    id varchar(32) primary key,
    name varchar(32) not null
);

todos

カラム名	型	制約
id	serial	primary key
member_id	int	foreign key
content	varchar(256)	not null
due	date
done	boolean

create table todos (
    id serial primary key,
    member_id varchar(32),
    content varchar(256) not null,
    due date,
    done boolean,
    foreign key (member_id) references members (id)
);

Npgsqlのインストール

project.jsonにNpgsql.EntityFrameworkCore.PostgreSQLの1.1.0（現行最新版）を追記してdotnet restoreでインストールします。

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Npgsql.EntityFrameworkCore.PostgreSQL": "1.1.0"
  },
  "frameworks": {
    "netcoreapp1.1": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.1.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

エンティティクラス

アプリケーションの実装に入っていきます。

最初にテーブルレコードとマッピングされるエンティティクラスを作成します。
ここではmembersテーブルレコードに対応するMemberクラス、todosテーブルレコードに対応するTodoクラスを定義しています。

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ConsoleApplication.Models
{
    [Table("members")]
    public class Member
    {
        [Key]
        [Column("id")]
        public string Id { get; set; }

        [Required]
        [Column("name")]
        public string Name { get; set; }

        public virtual ICollection<Todo> Todos { get; set; }
    }

    [Table("todos")]
    public class Todo
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Required]
        [Column("member_id")]
        public string MemberId { get; set; }

        [Required]
        [Column("content")]
        public string Content { get; set; }

        [Column("due")]
        public DateTime Due { get; set; }

        [Column("done")]
        public bool Done { get; set; }

        public virtual Member Member { get; set; }
    }
}

DBコンテキストクラス

次にDbContextをオーバライドして、今回アクセスするtestデータベース用のDBコンテキストを作ります。
EF CoreではDbContextOptionsBuilderオブジェクトを介して接続文字列やロガーなどの設定をします。 今回はOnConfiguringで先程作成したローカルDBへ接続しています。

using ConsoleApplication.Models;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApplication.Database
{
    public class TestDbContext : DbContext
    {
        public DbSet<Member> Members { get; set; }
        public DbSet<Todo> Todos { get; set; }

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

データの挿入と参照

ここまで準備ができたら、membersとtodosにレコードをinsertして、さらにselectしてみます。

using System;
using System.Collections.Generic;
using System.Linq;
using ConsoleApplication.Database;
using ConsoleApplication.Models;

namespace ConsoleApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new TestDbContext())
            {
                var members = new List<Member>
                {
                    new Member { Id = "ohke1", Name = "ohke1" },
                    new Member { Id = "ohke2", Name = "ohke2" },
                };

                var todos = new List<Todo>
                {
                    new Todo { Member = members[0], Content = "content1", Due = DateTime.Now, Done = false },
                    new Todo { Member = members[0], Content = "content2", Due = DateTime.Now, Done = false },
                    new Todo { Member = members[1], Content = "content3", Due = DateTime.Now, Done = false },
                };
                
                db.Members.AddRange(members);
                db.Todos.AddRange(todos);

                db.SaveChanges();
            }

            using (var db = new TestDbContext())
            {
                // [出力]
                //   ohke1, ohke1
                //   ohke2, ohke2
                foreach (var member in db.Members)
                {
                    Console.WriteLine($"{member.Id}, {member.Name}");
                }

                // [出力]
                //   ohke2, 3, content3
                foreach (var todo in db.Members.Where(m => m.Id == "ohke2").SelectMany(m => m.Todos))
                {
                    Console.WriteLine($"{todo.MemberId}, {todo.Id}, {todo.Content}");
                }
            }
        }
    }
}

実行しているSQLクエリをコンソールに出力する

DBへ発行されているSQLを見たい場合は、DbContextにLoggerFactoryを設定します。
ここではMicrosoft.Extensions.Loggingを使ってConsoleにDebug出力するLoggerFactoryを作成・設定しています。

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("Host=localhost;Username=ohke;Password=ohke;Database=test");

    var loggerFactory = new LoggerFactory().AddConsole().AddDebug();
    optionsBuilder.UseLoggerFactory(loggerFactory);
}

"dependencies": {
    "Npgsql.EntityFrameworkCore.PostgreSQL": "1.1.0",
    "Microsoft.Extensions.Logging": "1.1.0",
    "Microsoft.Extensions.Logging.Console": "1.1.0",
    "Microsoft.Extensions.Logging.Debug": "1.1.0"
},

先程のプログラムで実行されるSQLを見てみますと、membersへの挿入では2つのレコードがinsertされていますが、DBへは1回のリクエストにまとめられています。

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (59ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p3='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "members" ("id", "name")
      VALUES (@p0, @p1);
      INSERT INTO "members" ("id", "name")
      VALUES (@p2, @p3);
Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory:Information: Executed DbCommand (59ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p3='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "members" ("id", "name")
VALUES (@p0, @p1);
INSERT INTO "members" ("id", "name")
VALUES (@p2, @p3);

また、ohke2に紐づくtodosレコードの検索ではmember_idをキーとしてinnner joinされており、memberの検索とtodoの検索が別々のSQLクエリで行われるいわゆるN+1問題の発生をEntity Framework側で防いでいることがわかります。

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "m.Todos"."id", "m.Todos"."content", "m.Todos"."done", "m.Todos"."due", "m.Todos"."member_id"
      FROM "members" AS "m"
      INNER JOIN "todos" AS "m.Todos" ON "m"."id" = "m.Todos"."member_id"
      WHERE "m"."id" = 'ohke2'
Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory:Information: Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "m.Todos"."id", "m.Todos"."content", "m.Todos"."done", "m.Todos"."due", "m.Todos"."member_id"
FROM "members" AS "m"
INNER JOIN "todos" AS "m.Todos" ON "m"."id" = "m.Todos"."member_id"
WHERE "m"."id" = 'ohke2'

.NET CoreアプリケーションでNLogを使う

.NET Coreアプリケーションでログ出力にはNLogが良いみたいですね。

NLog

NLogは導入が容易で拡張性が高いログ出力ライブラリで、最近ではlog4netよりも人気があるようです。

github.com

.NET Coreの場合は、NLog.Extensions.Loggingを使います。

github.com

NLogのインストール

project.jsonにNLog.Extensions.Loggingを記載してインストールします。

{
  "version": "1.0.0-*",
  "dependencies": {
    "Microsoft.Extensions.Configuration": "1.1.0",
    "NLog.Extensions.Logging": "1.0.0-rtm-beta2"
  },
  "frameworks": {
    "netcoreapp1.1": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.1.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

NLog.config

プロジェクトフォルダの直下にNLog.configファイルを作成します。

• targetsタグ内でログの出力先（ここではfile.txt）を指定します。
• rulesタグ内で指定のtargetへの出力条件（ここではDebugレベル以上）を記載します。

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <targets>
        <target name="logfile" xsi:type="File" fileName="file.txt" />
    </targets>

    <rules>
        <logger name="*" minlevel="Debug" writeTo="logfile" />
    </rules>
</nlog>

アプリケーション実装

今回はコンソールアプリケーションでログ出力させてみます。
NLog.LogManager#GetCurrentClassLoggerでLoggerオブジェクトを取得しています。 引数なしの場合、生成したクラス（ここではProgram）が呼び出し元（caller）のクラスとしてログに出力されます。

using System;
using NLog;

namespace ConsoleApplication
{
    public class Program
    {
        private static Logger _logger = LogManager.GetCurrentClassLogger();

        public static void Main(string[] args)
        {
            _logger.Trace("trace message");
            _logger.Debug("debug message");
            _logger.Info("info message");
            _logger.Warn("warn message");
            _logger.Error("error message");
            _logger.Fatal("fatal message");
        }
    }
}

実行すると、file.txtに以下のように出力されます。
Traceレベルが出力されていないことと、Programが呼び出し元として出力されていることに注意してください。

2017-02-24 21:36:40.1753|DEBUG|Program|debug message
2017-02-24 21:36:40.2848|INFO|Program|info message
2017-02-24 21:36:40.2848|WARN|Program|warn message
2017-02-24 21:36:40.2848|ERROR|Program|error message
2017-02-24 21:36:40.2857|FATAL|Program|fatal message

ログレイアウトをカスタマイズする

NLog.configのtargetsタグではログのレイアウトを細かく設定できます。
例えば↓の感じで設定すると、実行中のクラスとメソッドと行番号まで出力されます。

<targets>
    <target name="logfile" xsi:type="File" fileName="file.txt" 
            layout="${level:uppercase=true:padding=-5} ${longdate} &quot;${message}&quot; ${callsite}#${callsite-linenumber}" />
</targets>

DEBUG 2017-02-24 21:35:09.8425 "debug message" ConsoleApplication.Program.Main#13
INFO  2017-02-24 21:35:10.0356 "info message" ConsoleApplication.Program.Main#14
WARN  2017-02-24 21:35:10.0377 "warn message" ConsoleApplication.Program.Main#15
ERROR 2017-02-24 21:35:10.0391 "error message" ConsoleApplication.Program.Main#16
FATAL 2017-02-24 21:35:10.0421 "fatal message" ConsoleApplication.Program.Main#17

JSONやCSVへの出力も全く難しくなく、例えばJSONの場合はlayoutタグでJsonLayoutを指定するだけでOKです。

<targets>
    <target name="logfile" xsi:type="File" fileName="file.txt">
        <layout xsi:type="JsonLayout">
            <attribute name="level" layout="${level}" />
            <attribute name="timestamp" layout="${longdate}" />
            <attribute name="message" layout="${message}" />
            <attribute name="callsite" layout="${callsite}#${callsite-linenumber}" />
        </layout>
    </target>
</targets>

{ "level": "Debug", "timestamp": "2017-02-24 21:27:15.0592", "message": "debug message", "callsite": "ConsoleApplication.Program.Main#13" }

他にもいろいろなレイアウトがあります。
ログの出力クラス（Layout render）は自前で実装することもできます。

ログ出力先を切り替える

例えば、ログレベルがError以上の場合は、通常のログファイルに加えて別のファイルに出力したいといったケースもあるかと思います。
その場合、targetsに通常のログ出力用とErrorログ出力用の2つのtargetを定義して、rulesでログレベルに応じてtargetを切り替えることになります。 下のように設定すると、errorlog.txtにはログレベルがErrorまたはFatalのみが出力されるようになります。

<targets>
    <target name="logfile" xsi:type="File" fileName="log.txt" />
    <target name="errorlogfile" xsi:type="File" fileName="errorlog.txt" />
</targets>
<rules>
    <logger name="*" minlevel="Debug" writeTo="logfile" />
    <logger name="*" minlevel="Error" writeTo="errorlogfile" />
</rules>

出力先にはファイル以外にもメールやDBやコンソールに出力できます。

まとめ

Layout renderやtargetの自前実装について触れませんでしたが、そういった拡張をしなくてもちょっとしたアプリケーションなら十分なログが得られることがわかりました。

Lambda(C#)でDynamoDB Streamsを使う

DynamoDB StreamsをLambda(C#)で使ってみました。

DynamoDB Streams

DynamoDBにはテーブルへの更新（作成、変更、削除）をキャプチャし、その情報(レコード)をバッファリングするストリームを作成することができる「DynamoDB Streams」という機能が提供されています。
レコードは1回のみ現れること・項目の更新順にレコードが取得されること・レコードは24時間保持されることが保証されており、Kinesis Streamとほぼ同じように扱うことができます。

docs.aws.amazon.com

今回はこのDynamoDB Streamsを使って、DynamoDBテーブルの更新を別のDynamoDBテーブルへ同期させるLambdaアプリケーションを作ります。

DynamoDBの作成

同期元となるmaster-tableと同期先となるreplica-tableを作成します。 いずれもパーティションキーKeyが設定されているだけです。

Lambdaの実装

これまで同様、AWS ToolsをインストールしたVisual StudioからLambdaプロジェクトを作成します。

まずは、project.jsonにAmazon.Lambda.DynamoDBEventsとAWSSDK.DynamoDBv2を追記して、パッケージをインストールします。
DynamoDBEventsは後述するDynamoDBEventクラスで、DynamoDBv2はreplica-tableの更新で、それぞれ必要となります

{
  "version": "1.0.0-*",
  "buildOptions": {
  },

  "dependencies": {
    "Microsoft.NETCore.App": {
      "type": "platform",
      "version": "1.0.0"
    },

    "Amazon.Lambda.Core": "1.0.0*",
    "Amazon.Lambda.Serialization.Json": "1.0.1",
    "Amazon.Lambda.Tools": {
      "type": "build",
      "version": "1.2.1-preview1"
    },
    "Amazon.Lambda.DynamoDBEvents": "1.0.0",
    "AWSSDK.DynamoDBv2": "3.3.1.5"
  },

  "tools": {
    "Amazon.Lambda.Tools" : "1.2.1-preview1"
  },

  "frameworks": {
    "netcoreapp1.0": {
      "imports": "dnxcore50"
    }
  }
}

Lambdaのソースコード(デフォルトであればFunction.cs)を記述します。
ポイントはハンドラの引数として渡されるAmazon.Lambda.DynamoDbEvents.DynamoDBEventオブジェクトで、このオブジェクトの中にどのテーブルのどの項目に対してどんな更新（作成 or 変更 or 削除）が行われたのか、といった情報が詰められています。 今回はこのDynamoDBEventオブジェクトを解析して、同じ変更をreplica-tableに対しても行います。
例えば、EventNameが"INSERT"の場合は、DynamoDB.Keysからkey、DynamoDB.NewImageからvalueの値をそれぞれ取得して同じ値を設定し、現在時刻をtimestampに付加して、replica-tableにデータを作成しています。

using System;
using Amazon;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.DataModel;
using Amazon.Lambda.Core;
using Amazon.Lambda.DynamoDBEvents;
using Newtonsoft.Json;

[assembly: LambdaSerializerAttribute(typeof(Amazon.Lambda.Serialization.Json.JsonSerializer))]

namespace DynamoReplicationLambda
{
    public class Function
    {
        private static readonly AmazonDynamoDBClient DbClient = new AmazonDynamoDBClient(RegionEndpoint.USWest2);

        // DynamoDBの更新情報がDynamoDBEventオブジェクトとして渡される
        public void FunctionHandler(DynamoDBEvent dynamoEvent, ILambdaContext context)
        {
            // DynamoDBEventオブジェクトをログに出力
            context.Logger.LogLine(JsonConvert.SerializeObject(dynamoEvent));

            using (var dbContext = new DynamoDBContext(DbClient))
            {
                foreach (var r in dynamoEvent.Records)
                {
                    if (r.EventName == OperationType.INSERT || r.EventName == OperationType.MODIFY)
                    {
                        var item = new ReplicaTableItem
                        {
                            // キーはKeys（パーティションキーのみの場合は1つ、ソートキーを含む場合は2つ持つ）、
                            Key = r.Dynamodb.Keys["key"].S,
                            // 変更後の値はNewImageから取得する
                            Value = r.Dynamodb.NewImage["value"].S,
                            Timestamp = DateTime.Now.ToString(),
                        };

                        dbContext.SaveAsync(item).Wait();
                    }
                    else if (r.EventName == OperationType.REMOVE)
                    {
                        // REMOVEの場合は、キーのみが渡される
                        var item = new ReplicaTableItem
                        {
                            Key = r.Dynamodb.Keys["key"].S,
                        };

                        dbContext.DeleteAsync(item).Wait();
                    }
                }
            }
        }
    }

    // replica-tableのエンティティ
    [DynamoDBTable("replica-table")]
    internal class ReplicaTableItem
    {
        [DynamoDBHashKey]
        [DynamoDBProperty(AttributeName = "key")]
        public string Key { get; set; }
        
        [DynamoDBProperty(AttributeName = "value")]
        public string Value { get; set; }

        [DynamoDBProperty(AttributeName = "timestamp")]
        public string Timestamp { get; set; }
    }
}

Lambdaのデプロイ

AWSのコンソール画面からLambdaを作成してデプロイします。

キーとなるのはトリガの設定で、先ほど作成したmaster-tableを指定することで、master-tableの更新イベントがDynamoDB Streamsに流れてLambdaが起動・実行されます。

したがってLambdaに割り当てるロールには対象のDynamoDBテーブルへdynamodb:GetRecords、dynamodb:GetShardIterator、dynamodb:DescribeStream、dynamodb:DescribeTableの4つの操作が許可されている必要があります。
それ以外のdynamodb:DescribeTable、dynamodb:UpdateItem、dynamodb:DeleteItemは、replica-table更新のために許可しています。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "lambda:InvokeFunction"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dynamodb:GetRecords",
                "dynamodb:GetShardIterator",
                "dynamodb:DescribeStream",
                "dynamodb:ListStreams",
                "dynamodb:DescribeTable",
                "dynamodb:UpdateItem",
                "dynamodb:DeleteItem",
                "logs:CreateLogGroup",
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": "*"
        }
    ]
}

動作確認

master-tableに項目を追加してみます。

すると、Lambdaでは設定したトリガからLambdaが起動されます。

DynamoDBEventオブジェクトをトレースしたログを見ると、以下のイベントが渡されます。
EventName.Valueで新規作成されたこと、EventSourceArnでmaster-tableのDynamoDB StreamがLambdaを起動していることがわかります。 また、Dynamodb.Keysにパーティションキー、DynamoDB.NewImageに追加された値がそれぞれ入っていることも確認できます。

• 変更では、EventName.Valueが"MODIFY"となり、DynamoDB.OldImageに変更前の値も入ります。
• 削除では、EventName.Valueが"REMOVE"となり、DynamoDB.OldImageもDynamoDB.NewImageも空でDynamoDB.Keysのみに値が詰められます。

{
    "Records": [
        {
            "EventSourceArn": "arn:aws:dynamodb:us-west-2:XXXXXXXXXXXX:table/master-table/stream/2017-02-17T04:50:43.602",
            "AwsRegion": "us-west-2",
            "Dynamodb": {
                "ApproximateCreationDateTime": "2017-02-17T04:59:00Z",
                "Keys": {
                    "key": {
                        "B": null,
                        "BOOL": false,
                        "IsBOOLSet": false,
                        "BS": [],
                        "L": [],
                        "IsLSet": false,
                        "M": {},
                        "IsMSet": false,
                        "N": null,
                        "NS": [],
                        "NULL": false,
                        "S": "key1",
                        "SS": []
                    }
                },
                "NewImage": {
                    "value": {
                        "B": null,
                        "BOOL": false,
                        "IsBOOLSet": false,
                        "BS": [],
                        "L": [],
                        "IsLSet": false,
                        "M": {},
                        "IsMSet": false,
                        "N": null,
                        "NS": [],
                        "NULL": false,
                        "S": "value1",
                        "SS": []
                    },
                    "key": {
                        "B": null,
                        "BOOL": false,
                        "IsBOOLSet": false,
                        "BS": [],
                        "L": [],
                        "IsLSet": false,
                        "M": {},
                        "IsMSet": false,
                        "N": null,
                        "NS": [],
                        "NULL": false,
                        "S": "key1",
                        "SS": []
                    }
                },
                "OldImage": {},
                "SequenceNumber": "2656400000000004815564263",
                "SizeBytes": 25,
                "StreamViewType": {
                    "Value": "NEW_AND_OLD_IMAGES"
                }
            },
            "EventID": "6208a61d1fae612ca60e9857acbb9cda",
            "EventName": {
                "Value": "INSERT"
            },
            "EventSource": "aws:dynamodb",
            "EventVersion": "1.1"
        }
    ]
}

同じデータがタイムスタンプ付きでreplica-tableにも作成されていることが確認できました。