Using .NET to interact with Amazon Aurora DSQL

This section describes how how to use .NET to interact with Aurora DSQL.

Before you begin, make sure that you have completed the following prerequisites.

Connect to your Aurora DSQL cluster

First define a TokenGenerator class. This class generates an authentication token, which you can use to connect to your Aurora DSQL cluster.

using Amazon.Runtime;
using Amazon.Runtime.Internal;
using Amazon.Runtime.Internal.Auth;
using Amazon.Runtime.Internal.Util;

public static class TokenGenerator
{
    public static string GenerateAuthToken(string? hostname, Amazon.RegionEndpoint region)
    {
        AWSCredentials awsCredentials = FallbackCredentialsFactory.GetCredentials();

        string accessKey = awsCredentials.GetCredentials().AccessKey;
        string secretKey = awsCredentials.GetCredentials().SecretKey;
        string token = awsCredentials.GetCredentials().Token;

        const string DsqlServiceName = "dsql";
        const string HTTPGet = "GET";
        const string HTTPS = "https";
        const string URISchemeDelimiter = "://";
        const string ActionKey = "Action";
        const string ActionValue = "DbConnectAdmin";
        const string XAmzSecurityToken = "X-Amz-Security-Token";

        ImmutableCredentials immutableCredentials = new ImmutableCredentials(accessKey, secretKey, token) ?? throw new ArgumentNullException("immutableCredentials");
        ArgumentNullException.ThrowIfNull(region);

        hostname = hostname?.Trim();
        if (string.IsNullOrEmpty(hostname))
            throw new ArgumentException("Hostname must not be null or empty.");

        GenerateDsqlAuthTokenRequest authTokenRequest = new GenerateDsqlAuthTokenRequest();
        IRequest request = new DefaultRequest(authTokenRequest, DsqlServiceName)
        {
            UseQueryString = true,
            HttpMethod = HTTPGet
        };
        request.Parameters.Add(ActionKey, ActionValue);
        request.Endpoint = new UriBuilder(HTTPS, hostname).Uri;

        if (immutableCredentials.UseToken)
        {
            request.Parameters[XAmzSecurityToken] = immutableCredentials.Token;
        }

        var signingResult = AWS4PreSignedUrlSigner.SignRequest(request, null, new RequestMetrics(), immutableCredentials.AccessKey,
            immutableCredentials.SecretKey, DsqlServiceName, region.SystemName);

        var authorization = "&" + signingResult.ForQueryParameters;
        var url = AmazonServiceClient.ComposeUrl(request);

        // remove the https:// and append the authorization
        return url.AbsoluteUri[(HTTPS.Length + URISchemeDelimiter.Length)..] + authorization;
    }

    private class GenerateDsqlAuthTokenRequest : AmazonWebServiceRequest
    {
        public GenerateDsqlAuthTokenRequest()
        {
            ((IAmazonWebServiceRequest)this).SignatureVersion = SignatureVersion.SigV4;
        }
    }
}

CRUD examples

Now you can run queries in your Aurora DSQL cluster.

using Npgsql;
using Amazon;

class Example
{
    public static async Task Run(string clusterEndpoint)
    {
        RegionEndpoint region = RegionEndpoint.USEast1;

        // Connect to a PostgreSQL database.
        const string username = "admin";
        // The token expiration time is optional, and the default value 900 seconds
        string password = TokenGenerator.GenerateAuthToken(clusterEndpoint, region);
        const string database = "postgres";
        var connString = "Host=" + clusterEndpoint + ";Username=" + username + ";Password=" + password + ";Database=" + database + ";Port=" + 5432 + ";SSLMode=VerifyFull;";

        var conn = new NpgsqlConnection(connString);
        await conn.OpenAsync();

        // Create a table.
        using var create = new NpgsqlCommand("CREATE TABLE IF NOT EXISTS owner (id UUID PRIMARY KEY, name VARCHAR(30) NOT NULL, city VARCHAR(80) NOT NULL, telephone VARCHAR(20))", conn);
        create.ExecuteNonQuery();

        // Create an owner.
        var uuid = Guid.NewGuid();
        using var insert = new NpgsqlCommand("INSERT INTO owner(id, name, city, telephone) VALUES(@id, @name, @city, @telephone)", conn);
        insert.Parameters.AddWithValue("id", uuid);
        insert.Parameters.AddWithValue("name", "John Doe");
        insert.Parameters.AddWithValue("city", "Anytown");

        insert.Parameters.AddWithValue("telephone", "555-555-0190");

        insert.ExecuteNonQuery();

        // Read the owner.
        using var select = new NpgsqlCommand("SELECT * FROM owner where id=@id", conn);
        select.Parameters.AddWithValue("id", uuid);
        using var reader = await select.ExecuteReaderAsync();
        System.Diagnostics.Debug.Assert(reader.HasRows, "no owner found");

        System.Diagnostics.Debug.WriteLine(reader.Read());

        reader.Close();

        using var delete = new NpgsqlCommand("DELETE FROM owner where id=@id", conn);
        select.Parameters.AddWithValue("id", uuid);
        select.ExecuteNonQuery();

        // Close the connection.
        conn.Close();
    }
    
    public static async Task Main(string[] args)
    {
        await Run();
    }
}