[アップデート] Amazon Aurora DSQL に .NET（Npgsql）と Rust（SQLx）向けコネクターが追加されたので .NET で使ってみた

using Amazon.DSQL;
using Amazon.DSQL.Model;
using Amazon.Runtime;
using Npgsql;

var clusterEndpoint = "ivtvahlywbnhlbci5ujpzw5wja.dsql.ap-northeast-1.on.aws";
var region = Amazon.RegionEndpoint.APNortheast1;

// トークンを生成
var credentials = FallbackCredentialsFactory.GetCredentials();
var token = DSQLAuthTokenGenerator.GenerateDbConnectAdminAuthToken(
    credentials, region, clusterEndpoint);

// Npgsql で接続
var connStr = new NpgsqlConnectionStringBuilder
{
    Host = clusterEndpoint,
    Port = 5432,
    Database = "postgres",
    Username = "admin",
    Password = token,
    SslMode = SslMode.VerifyFull
}.ToString();

await using var conn = new NpgsqlConnection(connStr);
await conn.OpenAsync();

await using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT 'Hello from Aurora DSQL!' AS greeting";
var greeting = await cmd.ExecuteScalarAsync();
Console.WriteLine(greeting);

using Amazon.AuroraDsql.Npgsql;

var clusterEndpoint = "ivtvahlywbnhlbci5ujpzw5wja.dsql.ap-northeast-1.on.aws";

await using var conn = await AuroraDsql.ConnectAsync(new DsqlConfig
{
    Host = clusterEndpoint,
    Profile = "hoge"
});

await using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT 'Hello from Aurora DSQL!' AS greeting";
var greeting = await cmd.ExecuteScalarAsync();
Console.WriteLine(greeting);

% dotnet new console -n DsqlSample
The template "Console App" was created successfully.

Processing post-creation actions...
Restoring /Users/iwasa.takahito/work/hoge0405dsqldotnet/DsqlSample/DsqlSample.csproj:
Restore succeeded.

% cd DsqlSample
% dotnet add package Amazon.AuroraDsql.Npgsql
info : X.509 certificate chain validation will use the fallback certificate bundle at '/usr/local/share/dotnet/sdk/10.0.101/trustedroots/codesignctl.pem'.

:

info : Writing assets file to disk. Path: /Users/iwasa.takahito/work/hoge0405dsqldotnet/DsqlSample/obj/project.assets.json
log  : Restored /Users/iwasa.takahito/work/hoge0405dsqldotnet/DsqlSample/DsqlSample.csproj (in 2.93 sec).

using Amazon.AuroraDsql.Npgsql;

var clusterEndpoint = "ivtvahlywbnhlbci5ujpzw5wja.dsql.ap-northeast-1.on.aws";

await using var conn = await AuroraDsql.ConnectAsync(new DsqlConfig
{
    Host = clusterEndpoint,
    Profile = "hoge"
});

await using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT 'Hello from Aurora DSQL!' AS greeting";
var greeting = await cmd.ExecuteScalarAsync();
Console.WriteLine(greeting);

% dotnet run
Hello from Aurora DSQL!

using Amazon.AuroraDsql.Npgsql;

var clusterEndpoint = "ivtvahlywbnhlbci5ujpzw5wja.dsql.ap-northeast-1.on.aws";

// コネクションプール作成（OCC リトライ有効）
await using var ds = await AuroraDsql.CreateDataSourceAsync(new DsqlConfig
{
    Host = clusterEndpoint,
    Profile = "hoge",
    OccMaxRetries = 3
});

// テーブル作成
await ds.ExecWithRetryAsync(
    "CREATE TABLE IF NOT EXISTS hogeusers (id UUID DEFAULT gen_random_uuid() PRIMARY KEY, name TEXT NOT NULL)");

// データ挿入（トランザクション + OCC リトライ）
await ds.WithTransactionRetryAsync(async conn =>
{
    await using var cmd1 = conn.CreateCommand();
    cmd1.CommandText = "INSERT INTO hogeusers (name) VALUES (@name)";
    cmd1.Parameters.AddWithValue("name", "hoge");
    await cmd1.ExecuteNonQueryAsync();

    await using var cmd2 = conn.CreateCommand();
    cmd2.CommandText = "INSERT INTO hogeusers (name) VALUES (@name)";
    cmd2.Parameters.AddWithValue("name", "fuga");
    await cmd2.ExecuteNonQueryAsync();
});

// データ取得
await using (var conn = await ds.OpenConnectionAsync())
{
    await using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT id, name FROM hogeusers";
    await using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["id"]} : {reader["name"]}");
    }
}

% dotnet run
04848cd0-1916-43d2-a50f-72286995c750 : hoge
3cc92d8e-e1b2-4740-8a13-2534e849d232 : hoge
4f87f7ee-c51a-448e-acc5-5cf10f7bf2d7 : fuga
eaa3c339-a726-4346-a31a-254ac28dc80f : fuga

// OCC リトライ付き更新
await ds.WithTransactionRetryAsync(async conn =>
{
    await using var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE hogeusers SET name = @newName WHERE name = @oldName";
    cmd.Parameters.AddWithValue("newName", "piyo");
    cmd.Parameters.AddWithValue("oldName", "hoge");
    await cmd.ExecuteNonQueryAsync();
});

await using var ds = await AuroraDsql.CreateDataSourceAsync(
    "postgres://admin@ivtvahlywbnhlbci5ujpzw5wja.dsql.ap-northeast-1.on.aws/postgres?profile=dev");

await using var ds = await AuroraDsql.CreateDataSourceAsync(new DsqlConfig
{
    Host = clusterEndpoint,
    ConfigureConnectionString = csb =>
    {
        csb.MaxPoolSize = 20;
        csb.MinPoolSize = 2;
        csb.CommandTimeout = 60;
    }
});

use sqlx::Row;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let pool = aurora_dsql_sqlx_connector::pool::connect(
        "postgres://admin@ivtvahlywbnhlbci5ujpzw5wja.dsql.ap-northeast-1.on.aws/postgres",
    )
    .await?;

    let row = sqlx::query("SELECT 'Hello, DSQL!' as greeting")
        .fetch_one(&pool)
        .await?;
    let greeting: &str = row.get("greeting");
    println!("{}", greeting);

    pool.close().await;
    Ok(())
}