これは PostgreSQL Advent Calendar 2016の15日目の記事です。
この Web サイトは、今年の4月から ASP.NET を Core の方に更新して Ubuntu サーバーで運用しています。実際に運用してみて、IIS よりも Nginx を使う方が便利だと思うようになってきたので、サーバーはすべて Linux サーバーにする予定です。
その際の問題は、データベースをどうするかということです。ASP.NET とはなんといっても SQL Server を使うのが定番です。それで、自分も SQL Server Express を使ってアプリを作っていました。しかし、残念なことに SQL Server が Linux に対応するのは来年の半ばということです。
最近、PostGIS を使いたいという思惑もあって、PostgreSQL を使ってみたら、結構いけていると感じたので、この記事を書くことにしました。まだ、使い始めて1ヶ月なので、今後問題が見つかるかもしれないので最終的どうなるかはわかりませんが、PostgreSQL を使っていこうと思っています。
Entity Framework Core で使う
.NET Core や .NET Framework から、PostgreSQL を使う場合は、Npgsql というデータプロバイダーを使用します。Npgsql には、Entity Framework Core（EF Core）用のプロバイダーもあるので、EF Core で使っている範囲では、SQL Server を使うのと違いは殆どありません。
例として、Visual Studio の ASP.NET Core の Web アプリケーションのテンプレートで認証を「個別のユーザーアカウント」にした場合に、EF Core が使用されているので、それを PostgreSQL を使うように修正してみます。
-
Nuget パッケージ の追加 Npgsql.EntityFrameworkCore.PostgreSQL を追加します。
-
Startup.cs の修正 以下のように、options.UseSqlServer と SQL Server を使う設定になっているので、
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
services.AddIdentity<ApplicationUser, IdentityRole>()
.AddEntityFrameworkStores<ApplicationDbContext>()
.AddDefaultTokenProviders();
------ 以下省略
}
options.UseNpgsql に変更して、Npgsql を使うようにします。
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddDbContext<ApplicationDbContext>(options =>
options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));
services.AddIdentity<ApplicationUser, IdentityRole>()
.AddEntityFrameworkStores<ApplicationDbContext>()
.AddDefaultTokenProviders();
------ 以下省略
}
- appsettings.json に記載してある接続文字列を修正します。
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=test;Username=postgres;Password=password"
},
------ 以下省略
}
SQL Server から PostgreSQL を使うように変更するのはそれだけです。
接続文字列に設定したユーザーが、データベースを作成できる権限を持っているユーザーであれば、これでデータベースも勝手に作ります。もし、そうでなければ事前にデータベースを作っておきます。
ただし、それで Run して、ユーザーの登録をしようとするとエラーになります。Data ディレクトリに Migration フォルダーが作成されていて、SQL Server 用のものが事前に作られているためです。 それで、それらのファイルを削除してからするとちゃんとしたエラーメッセーが出ます。メーセージの通りにマイグレーションをしてやると正常に動くようになります。
A database operation failed while processing the request.
NpgsqlException: Exception while reading from stream
IOException: Unable to read data from the transport connection: 既存の接続はリモート ホストに強制的に切断されました。.
SocketException: 既存の接続はリモート ホストに強制的に切断されました。
Use migrations to create the database for ApplicationDbContext
In Visual Studio, use the Package Manager Console to scaffold a new migration and apply it to the database:
PM> Add-Migration [migration name]
PM> Update-Database
Alternatively, you can scaffold a new migration and apply it from a command prompt at your project directory:
> dotnet ef migrations add [migration name]
> dotnet ef database update
コードファーストで開発する場合は、SQL Server なのか PostgreSQL なのかを気にしなくても開発できると思います。
細かい点でいうと作成されたテーブルをみると下の図のようにカラム名が Pascal ケースになっています。
SQL Server では、カラム名の大文字小文字を区別しないのでPascalケースは便利なのですが、PostgreSQL では、列名の大文字小文字を区別し、SQL文の中の列名は小文字に変換されます。それで、カラム名が「AspNetUsers」だと SQL 文の中では、常に二重引用符でくくって "AspNetUsers" としないといけなくなって結構手間になります。そのまま Pascal ケースで使うのか、それとも fluent API か attributes を使ってスネークケースに変換して使うのか悩ましいところです。自分は EF Core で使うのが主で、生の SQL をあまり使わないので、Pascal ケースのままでもいいのではないかと思っています。
データベースファーストの場合も、SQL Server を使うのと殆ど違いはなく、Microsoft.EntityFrameworkCore.SqlServer.Design の代わりに Npgsql.EntityFrameworkCore.PostgreSQL.Design を追加し、
dotnet cli の場合は、
dotnet ef dbcontext scaffold "Host=localhost;Database=mydatabase;Username=myuser;Password=mypassword" Npgsql.EntityFrameworkCore.PostgreSQL
Powershell の場合は、
Scaffold-DbContext "Host=localhost;Database=mydatabase;Username=myuser;Password=mypassword" Npgsql.EntityFrameworkCore.PostgreSQL
というように、Microsoft.EntityFrameworkCore.SqlServer を Npgsql.EntityFrameworkCore.PostgreSQL に置き換えてやれば、EF model を作成できます。
なお、DbContext のプロパティ名はカラム名を Pascal ケースに変換したものになります。
以上のように EF Core で使う限りにおいては、SQL Server を使うのと差はなく、実際に使っても今のところ大きな問題はでていません。
PostgreSQL を使うメリット、デメリット
SQL Server よりも便利な点としては、where 句で下のように正規表現を使うとサーバー側で処理をしてくれるという点です。( 参照 ドキュメント)
var customersStartingWithA = context.Customers.Where(c => Regex.IsMatch(c.CompanyName, "^A"));
まだ使っていませんが、circle 等の幾何データ型、配列型、JSONB型のマッピングは可能なようなので、EF Core でも、これらのデータの取得と更新は可能なようです。
ただし、PostgreSQL の独自の機能を本格的に使うためには、EF Core ではなくて、Npgsql を直接使う必要があり少し手間がかかります。
でも、少し調べてみると、.NET で PostgreSQL を Document Db として使うためのソフトとして Marten というソフトが開発されています。まだ使い始めたばかりですが、結構使いやすい感じです。
PostgreSQL のデメリットといえば、慣れの問題かも分かりませんが pgAdmin よりは、Visual Studio Management Studio の方が使いやすいという点です。
PostgreSQL にするか MySQL にするか
Linux で動作するオープンソースの RDB といえば、PostgreSQL よりも MySQL の方が有名です。
まず、.NET Core 対応という点で比較すると、PostgreSQL の方は、Npgsql の .NET Core 対応はアルファ版が出てもう1年以上になります。その後も開発が進められてきており、かなり安定してきていると思います。一方、MySQL の方は .NET Core に Connector/Net 7 で対応予定で現在開発版が公開されています。
最近の状況では、ZDNet Japan の「PostgreSQL」がスタートアップ企業に選ばれる理由という記事が参考になると思います。以下に最近のデータを載せておきます。
