AWS DynamoDB as Virtual Table with CRUD in Dataverse – Part 2
AWS DynamoDB as Virtual Table in Dataverse sounds initially impossible, but it isn’t. The key is to set up an OData service with CRUD functionality. Today I show you how I apply full read and write access from Dataverse to my AWS Data.
To shorten a long story, firstly I setup a DynamoDB table as database in AWS. Secondly, I attach this database to my serverless AWS Lambda function, which host my OData API service. Afterwards, I implement create, read, update, and delete (CRUD) operations in my OData API. And finally, I use my Virtual Table based on my OData API per example in a canvas app in Dataverse.
In my previous blog post I already described how-to setup an OData Service in AWS. I created and prepared my AWS stack (API Gateway, AWS Lambda Function, Roles, …). Furthermore, I used my OData service as Virtual Data Source in Dataverse.
But, I did not create an AWS DynamoDB to store my data. I also did not add CRUD operations to my serverless OData service. For this reason, my current OData API only provides an Entity Data Model (EDM) for Dataverse and read functions for my ProductCategory entity:
AWS DynamoDB
An AWS DynamoDB is a fast, flexible NoSQL database service for single-digit millisecond performance at any scale similar to an Azure Cosmos DB to store my data.
My example data are Product Categories. For that reason, I navigate in my AWS account to my DynamoDB tables and create a new table ProductCategories. Furthermore, I use “id” (string) for my partition key:
Now it’s time to access my DynamoDB from my ASP.NET Core project. First of all, I add a package reference to AWSSDK.DynamoDBv2:
Afterwards, I create a new class DatabaseService in my project. I do this, because I want to wrap my DynamoDB access in a service. Furthermore, I add a method GetDbContext() to my service class.
Here I create and return a new DynamoDBContext based on a new AmazonDynamoDBClient. Additionally, my AmazonDynamoDBClient is configured with AmazonDynamoDBConfig and my specific region endpoint EUWest1 where I have created my DynamoDB:
public class DatabaseService
{
private ILogger _logger;
public DatabaseService(ILogger<DatabaseService> logger)
{
_logger = logger;
}
private DynamoDBContext GetDbContext()
{
var config = new AmazonDynamoDBConfig { RegionEndpoint = RegionEndpoint.EUWest1 };
var client = new AmazonDynamoDBClient(config);
return new DynamoDBContext(client);
}
// ...
}Getting started is easy, because AWS provides a huge documentation with great examples about DynamoDB and .NET.
As result, I add 4 methods to my database service which implements LIST, READ, WRITE, and DELETE:
public class DatabaseService
{
// ...
public async Task<IEnumerable<T>> Load<T>()
{
using (var context = GetDbContext())
{
var scan = context.ScanAsync<T>(Enumerable.Empty<ScanCondition>());
return await scan.GetRemainingAsync();
}
}
public async Task<T> Load<T>(Guid key)
{
using (var context = GetDbContext())
{
return await context.LoadAsync<T>(key);
}
}
public async Task Save<T>(T value)
{
using (var context = GetDbContext())
{
await context.SaveAsync(value);
}
}
public async Task Delete<T>(Guid key)
{
using (var context = GetDbContext())
{
await context.DeleteAsync<T>(key);
}
}
}Now, I must specify my DynamoDB table and its partition key. I can do this in my model class for my entity ProductCategory:
[DynamoDBTable("ProductCategories")]
public class ProductCategory
{
[DynamoDBHashKey("id", typeof(GuidAttributeConverter))]
[Key]
[JsonPropertyName("Id")]
public Guid Id { get; set; }
[JsonPropertyName("Name")]
public string? Name { get; set; }
[JsonPropertyName("Description")]
public string? Description { get; set; }
}In addition, I am adding a type converter for my partition key column “id”. This is because DynamoDB does not support the Guid type. My virtual table in Dataverse, however, requires a primary key column of type Guid.
Here is also the implementation of my GuidAttributeConverter:
public class GuidAttributeConverter : IPropertyConverter
{
public object FromEntry(DynamoDBEntry entry)
{
var primitive = entry as Primitive;
if (primitive == null || primitive.Value is not string || string.IsNullOrEmpty((string)primitive.Value))
throw new ArgumentOutOfRangeException();
return Guid.Parse((string)primitive.Value);
}
public DynamoDBEntry ToEntry(object value)
{
return new Primitive
{
Value = value?.ToString() ?? value,
};
}
}Finally, I add my Database service in my ASP .NET Core startup class in my OData service:
public class Startup
{
// ...
public void ConfigureServices(IServiceCollection services)
{
// ...
services.AddSingleton<DatabaseService>();
// ...
}
// ...
}…and to my ODataController ProductCategoryController:
[ApiController]
[ODataAttributeRouting]
[ODataRouteComponent("odata/ProductCategories")]
public class ProductCategoryController : ODataController
{
private readonly ILogger _logger;
private readonly DatabaseService _database;
public ProductCategoryController(ILogger<ProductCategoryController> logger, DatabaseService database)
{
_logger = logger;
_database = database;
}
// ...
}I guess, now I’m read to use my DynamoDB in my OData controller class and provide additional HTTP routes for my CRUD functionality.
CRUD Operations
Basic CRUD Operations in OData are to create, read, update, and delete an entity. In other words, I must implement these operations as HTTP methods in my OData controller class:
Create
Dataverse send a HTTP POST request to create a new record. Additionally, the data of my new entity is send as HTTP body to my service. I use the Delta<T> class as parameter to receive my new entity:
[ApiExplorerSettings(IgnoreApi = false)]
[HttpPost("odata/ProductCategories")]
public async Task<ActionResult<ProductCategory>> Create(Delta<ProductCategory> value)
{
var original = new ProductCategory();
value?.Put(original);
if (Guid.Empty == original.Id)
{
original.Id = Guid.NewGuid();
}
await _database.Save(original);
return Created($"{Url.Link("", null)}({original.Id})", value);
}Moreover, I create a new model class, and apply the changes from my Delta<T> parameter value. Afterwards, I assign a new key and store my entity in my DynamoDB. Finally, Dataverse needs a Location, of the created entity in the HTTP response. In other words, a link to get the new entity.
I use the following HTTP request to test my local service:
POST http://localhost:5000/odata/ProductCategories
Content-Type: application/json
{
"Name": "Category 1",
"Description": "New Category"
}As you see, my response contains the needed information as HTTP Header Location:
HTTP/1.1 201 Created
Connection: close
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true; charset=utf-8
Date: Sat, 04 Feb 2023 15:31:22 GMT
Server: Kestrel
Location: https://localhost:5001/odata/ProductCategories(10ba3425-8292-4dfa-9d92-705ae3b127b2)
Transfer-Encoding: chunked
OData-Version: 4.0
{
"@odata.context": "https://localhost:5001/odata/$metadata#ProductCategories/$entity",
"Id": "10ba3425-8292-4dfa-9d92-705ae3b127b2",
"Name": "Category 1",
"Description": "New Category"
}Read
Dataverse need 2 HTTP routes to read my data. One is to read a whole list and the other is to read a single element.
That means, I need first a method to read whole list. Furthermore, my OData service can use per example filtering the result by passing ODataQueryOptions as parameter and returning an IQueryable as result:
[ApiExplorerSettings(IgnoreApi = false)]
[EnableQuery]
[HttpGet("odata/ProductCategories")]
public async Task<IQueryable<ProductCategory>> Get(ODataQueryOptions options)
{
return (await _database.Load<ProductCategory>()).AsQueryable();
}My second method return a single entity to Dataverse identified by the primary key. In addition, Dataverse uses “odata/ProductCategories({key})” as route. Therefor I pass the key as parameter to my database service and return the result:
[ApiExplorerSettings(IgnoreApi = false)]
[HttpGet("odata/ProductCategories({key})")]
public async Task<ProductCategory> Get(Guid key)
{
return await _database.Load<ProductCategory>(key);
}Update
Updating an entity is similar to creating an entity. Dataverse send 2 information as PATCH method to my service. This first one is the key of my entity and are the changes made on my entity as value. I use also the Delta<T> type for my value parameter to patch(…) the changes to my original entity:
[ApiExplorerSettings(IgnoreApi = false)]
[HttpPatch("odata/ProductCategories({key})")]
public async Task<UpdatedODataResult<ProductCategory>> Update(Guid key, Delta<ProductCategory> value)
{
var original = await _database.Load<ProductCategory>(key);
value?.Patch(original);
await _database.Save(original);
return Updated(original);
}
Delete
Finally, Dataverse uses the HTTP Delete method to delete an entity. The parameter is just the key of my entity, which I simply pass to my database service:
[ApiExplorerSettings(IgnoreApi = false)]
[HttpDelete("odata/ProductCategories({key})")]
public async Task Delete(Guid key)
{
await _database.Delete<ProductCategory>(key);
}Virtual Table Setup
It’s time to test my changes! Much better, I don’t need to change my virtual table setup from my last last post. This is because I did not change my table structure.
In addition, my OData API provides now the needed CRUD operations for Dataverse:
Short while after deployment, my service is available in AWS. That means, my Virtual Table in Dataverse should have at least 1 entry directly from my DynamoDB.
But I see this:
Something went wrong… In other words, I screwed up my demo example!
Yes, I did but I also can explain what happened. I tested my service locally and my OData service worked well. During rollout, I forgot to grant permissions to my AWS Lambda Function which is now accessing my DynamoDB. For the lack of permission, my service fail to retrieve records from my DynamoDB.
Let’s fix this! That means, I add permissions in AWS to my AWS Lambda Function execution role:
Well done, now Dataverse shows my records after this small change correctly:
My AWS Catalog App
Last time I created a model-driven-app. This time I create a new canvas app based on my virtual table. Therefore, I set up a new connection reference to Microsoft Dataverse in my solution:
Afterwards, I navigate to my Home-Screen and create a new App from Dataverse:
Here I can use my connection and connect to my virtual table:
As result, PowerApps Studio is creating a new canvas app based on my virtual AWS table in Dataverse. A few seconds later, these screens are added to my app:
That’s pretty darn impressive or isn’t it?
Summary
First of all, I have shown how I create and setup my DynamoDB table to store my entity data. Afterwards, I implemented and added a new service to my ASP .NET Core API hosted as serverless AWS Lambda Function. In addition, I explained and implemented create, read, update, and delete (CRUD) operations in my OData service. Finally, I created a new canvas app in Dataverse based on my virtual table to test my example.
Everything works now pretty smooth and looks very simple. On the other hand, it was hard to collect the needed information and combine everything in a working demo. And there is even more …
In detail, I’ll show you in one of my next posts how I transport and setup my AWS virtual tables solution in Dataverse for Teams. In addition, I extend my OData endpoint and provide CUD events for Dataverse to react in Power Automate Flows on data changes. Finally, I’ll dive a little bit deeper into the creation of Virtual Entities.
Stay tuned!