As per Amazon documentation, Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud, that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases.Amazon Aurora is up to five times faster than standard MySQL databases and three times faster than standard PostgreSQL databases. It provides the security, availability, and reliability of commercial databases at 1/10th the cost. Amazon Aurora is fully managed by Amazon Relational Database Service (RDS), which automates time-consuming administration tasks like hardware provisioning, database setup, patching, and backups.
Benefits of Amazon Aurora
- High Performance and Scalability
- High Availability and Durability
- Highly Secure
- MySQL and PostgreSQL Compatible
- Fully Managed
- Migration Support
Connect to Aurora from Client application
We have two options to connect to Aurora database from our application
1) Using connection string with MySql / PostgreSQL libraries
In this we will use a traditional connection string method to connect to the Aurora MySQL / PostgreSQL. In this method, the client application should be in same VPC network where Aurora database exists.
2) Using Aurora data API
Here we will use API to run the SQL statements. In this method, there is no necessary to have the client application in same VPC as Aurora.
As all are aware of conventional database calling, I am discussing about connecting to the database using Data API in this post
Prerequisites for Working with Data API in Aurora
1) Enable Data API option:
For using the Aurora data API, we should enable Data API for the Aurora cluster. We can do in through RDS Console. While creating, this option will be available under Connectivity section. While modifying, you can find this option under Network & Security section.
2) Store DB cluster credentials in a secret:
Use AWS Secrets Manager to create a secret that contains credentials for the Aurora DB cluster. For instructions, check
Creating a Basic Secret
Helper class
Following is my helper class in .NET / .NET Core using C#. You need to include the AWSSDK.RDSDataService Nuget package for working with it.
using Amazon;
using Amazon.RDSDataService;
using Amazon.RDSDataService.Model;
using Amazon.Runtime;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
namespace AurorDotnet
{
public class AuroraHelper
{
AmazonRDSDataServiceClient client;
private readonly string accessKeyId, secretKey, secretArn, auroraServerArn, databaseName;
private readonly RegionEndpoint region;
public AuroraHelper(string accessKeyId, string secretKey, string region, string secretArn, string auroraServerArn, string database)
{
this.accessKeyId = accessKeyId;
this.secretKey = secretKey;
this.region = RegionEndpoint.GetBySystemName(region);
this.secretArn = secretArn;
this.auroraServerArn = auroraServerArn;
this.databaseName = database;
client = GetClient();
}
private AmazonRDSDataServiceClient GetClient()
{
if (client == null)
{
try
{
client = new AmazonRDSDataServiceClient(accessKeyId, secretKey, region);
}
catch (AmazonRDSDataServiceException ex)
{ Console.WriteLine($"Error (AmazonRDSDataServiceException) creating RDS client", ex); }
catch (AmazonServiceException ex)
{ Console.WriteLine($"Error (AmazonServiceException) creating RDS client", ex); }
catch (Exception ex)
{ Console.WriteLine($"Error creating AWS S3 client", ex); }
}
return client;
}
/// <summary>
/// Runs the command and returns the first record as given object type. If T is string, it return the first row first column data.
/// </summary>
/// <typeparam name="T">Object type to which the record to be converted</typeparam>
/// <param name="sqlCommand">Database command which needs to be executed</param>
/// <param name="parameters">Prameters for the sql statement</param>
/// <returns></returns>
public async Task<T> GetRow<T>(string sqlCommand, Dictionary<string, string> parameters = null)
{
var executeSqlRequest = CreateExecuteRequest(sqlCommand, parameters);
var data = await client.ExecuteStatementAsync(executeSqlRequest);
if (data.HttpStatusCode == System.Net.HttpStatusCode.OK && data.Records.Count > 0)
{
if (typeof(T) == typeof(string))
return (T)ParseValue(data.Records[0][0], "varchar");
else return ParseRecord<T>(data);
}
return default(T);
}
/// <summary>
/// Runs the command and returns the list of records of given object type
/// </summary>
/// <typeparam name="T">Object type to which the record to be converted</typeparam>
/// <param name="sqlCommand">Database command which needs to be executed</param>
/// <param name="parameters">Prameters for the sql statement</param>
/// <returns></returns>
public async Task<List<T>> GetRows<T>(string sqlCommand, Dictionary<string, string> parameters = null)
{
var executeSqlRequest = CreateExecuteRequest(sqlCommand, parameters);
var data = await client.ExecuteStatementAsync(executeSqlRequest);
if (data.HttpStatusCode == System.Net.HttpStatusCode.OK && data.Records.Count > 0)
return ParseRecordSet<T>(data);
return default(List<T>);
}
/// <summary>
/// Executes the Sql command and returns number of records effected
/// </summary>
/// <param name="sqlCommand"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public async Task<long> ExecuteSql(string sqlCommand, Dictionary<string, string> parameters = null)
{
var executeSqlRequest = CreateExecuteRequest(sqlCommand, parameters);
var data = await client.ExecuteStatementAsync(executeSqlRequest);
if (data.HttpStatusCode == System.Net.HttpStatusCode.OK)
{
if (data.Records.Count > 0)
return Convert.ToInt64(data.Records[0][0]);
else
return data.NumberOfRecordsUpdated;
}
return 0;
}
/// <summary>
/// Creates the ExecuteStatementRequest for Aurora data api
/// </summary>
/// <param name="sqlCommand">Database command which needs to be executed</param>
/// <param name="parameters">Prameters for the sql statement</param>
/// <returns></returns>
private ExecuteStatementRequest CreateExecuteRequest(string sqlCommand, Dictionary<string, string> parameters = null)
{
ExecuteStatementRequest executeStatementRequest = new ExecuteStatementRequest()
{
SecretArn = secretArn,
ResourceArn = auroraServerArn,
IncludeResultMetadata = true,
ContinueAfterTimeout = true,
Database = databaseName,
Sql = sqlCommand
};
if (parameters != null && parameters.Count > 0)
executeStatementRequest.Parameters.AddRange(BuildParams(parameters));
return executeStatementRequest;
}
private List<SqlParameter> BuildParams(Dictionary<string, string> parameters)
{
var sqlParameters = parameters.Select(p => new SqlParameter { Name = p.Key, Value = new Field() { StringValue = p.Value, IsNull = string.IsNullOrEmpty(p.Value) } }).ToList();
return sqlParameters;
}
/// <summary>
/// Converts the Data api response to the given object type and returns the first record
/// </summary>
/// <typeparam name="T">Object type to which the record to be converted</typeparam>
/// <param name="response">Data api response</param>
/// <returns></returns>
private T ParseRecord<T>(ExecuteStatementResponse response)
{
var records = response.Records[0].Zip(response.ColumnMetadata, (record, col) => new KeyValuePair<string, object>(col.Name, ParseValue(record, col.TypeName))).ToList();
return ConvertRecordToEntity<T>(records);
}
/// <summary>
/// Converts the Data api response to the given object type and returns the list of records
/// </summary>
/// <typeparam name="T">Object type to which the record to be converted</typeparam>
/// <param name="response">Data api response</param>
/// <returns></returns>
private List<T> ParseRecordSet<T>(ExecuteStatementResponse response)
{
List<T> data = new List<T>();
var records = response.Records.Select(x => x.Zip(response.ColumnMetadata, (record, col) => new KeyValuePair<string, object>(col.Name, ParseValue(record, col.TypeName))).ToList()).ToList();
foreach (var r in records)
{
T item = ConvertRecordToEntity<T>(r);
data.Add(item);
}
return data;
}
/// <summary>
/// Maps the KeyValuePair list to object
/// </summary>
/// <typeparam name="T">Object type to which the record to be converted</typeparam>
/// <param name="data">KeyValuePair list which needs to map to the object</param>
/// <returns></returns>
private T ConvertRecordToEntity<T>(List<KeyValuePair<string, object>> data)
{
Type temp = typeof(T);
T obj = Activator.CreateInstance<T>();
PropertyInfo[] propInfo = temp.GetProperties();
foreach (var item in data)
{
foreach (var pro in propInfo)
{
if (pro.Name.ToLower() == item.Key.ToLower())
{
pro.SetValue(obj, item.Value, null);
}
}
}
return obj;
}
/// <summary>
/// Parse the value from Amazon.RDSDataService.Model.Field
/// </summary>
/// <param name="field">Amazon.RDSDataService.Model.Field</param>
/// <returns></returns>
private object ParseValue(Field field, string colType)
{
object value = null;
if (field.IsNull)
value = null;
if (field.ArrayValue != null)
value = field.ArrayValue;
switch (colType.ToLower())
{
case "int":
case "smallint":
value = Convert.ToInt32(field.LongValue);
break;
case "bigint":
value = field.LongValue;
break;
case "tinyint":
case "boolean":
case "bit":
value = field.BooleanValue;
break;
case "float":
case "double":
case "real":
value = field.DoubleValue;
break;
case "blob":
case "binary":
case "varbinary":
value = field.BlobValue;
break;
case "time":
if (TimeSpan.TryParse(field.StringValue, out TimeSpan time))
value = time;
break;
case "date":
case "datetime":
if (DateTime.TryParse(field.StringValue, out DateTime date))
value = date;
break;
default:
value = field.StringValue;
break;
}
return value;
}
}
}
Usage:
Create the instance of the helper class as below
AuroraHelper auroraHelper = new AuroraHelper("<AWS Accesskey Id>", "<AWS Secret Key>", "<AWS region>", "<Aurora database secret store Arn>", "<Aurora database cluster Arn>", "<Aurora database name>");
Getting single record from the database
Dictionary<string, string> parameters = new Dictionary<string, string>()
{
{"accountid","10" }
};
var command = "SELECT account.id,account.name,account.isactive FROM account WHERE account.id=:accountid LIMIT 1";
var data = await auroraHelper.GetRow<Account>(command, parameters);
Console.WriteLine(JsonConvert.SerializeObject(data));
Getting set of record from the database
Dictionary<string, string> parameters = new Dictionary<string, string>()
{
{"accountname","%test%" }
};
var command = "SELECT account.id,account.accountname,account.isactive FROM account WHERE account.accountname LIKE :accountname";
var data = await auroraHelper.GetRows<Account>(command, parameters);
Console.WriteLine(JsonConvert.SerializeObject(data));
Executing an DDL Command
Dictionary<string, string> parameters = new Dictionary<string, string>()
{
{"accountname","Gopiportal" },
{"isactive","1" }
};
var command = "INSERT INTO account(accountname,isactive) VALUES(:accountname,:isactive)";
var data = await auroraHelper.ExecuteSql(command, parameters);
if(data>0)
Console.WriteLine("Data inserted successfully");
else
Console.WriteLine("Data insertion failed");
Executing a Stored procedure
Aurora Data API is not built for executing a stored procedure. But we can execute a stored procedure as an SQL command like below
Stored procedure which returns single record
Dictionary<string, string> parameters = new Dictionary<string, string>()
{
{"accountid","10" }
};
var command = "CALL usp_getAccount(:accountid)";
var data = await auroraHelper.GetRow<Account>(command, parameters);
Stored procedure which returns multiple records
Dictionary<string, string> parameters = new Dictionary<string, string>()
{
{"accountname","%test%" }
};
var command = "CALL usp_getAccounts(:accountname)";
var data = await auroraHelper.GetRows<Account>(command, parameters);
Stored procedure which deals with DDL command
Dictionary<string, string> parameters = new Dictionary<string, string>()
{
{"accountname","Gopiportal" },
{"isactive","1" }
};
var command = "CALL usp_insertAccount(:accountname,:isactive)";
var data = await auroraHelper.ExecuteSql<Account>(command, parameters);
Limitations of Executing Stored procedure using Data API
Following are the limitations using stored procedures with Data API
- It will not capture any output variables returned from stored procedure
- If the stored procedure returns more than one data set, it will hold the records for the first data set and ignore the remaining
- After executing an DDL command in the stored procedure, it will not return the NumberOfRecordsUpdated count. This value will always be zero.
Read more about Aurora Data API
here.
Happy Coding ! 😊
