Part 5 demonstrates using Codecs and the Transcoder for saving and fetching resources from a database.
ROI – Codecs for Database Persistence
Part 3 introduced Codecs and the Transcoder for server side usage. Part 4 reused these on the client. In this installment, we use Codecs and the Transcoder when saving and fetching resources from a database.
Many systems use a document or NoSql database such as Mongo or Couchbase. These databases typically persist the resource as a serialized (encoded) document.
Codecs and the Transcoder are ideal for mapping the resource to a database document.
Concepts
Encode / Decode for the Database
Codecs and the Transcoder are a great fit for mapping the resource to a format the database can persist. They are also excellent for decoding the resources fetched from the database.
Database persistence “maps” the resource instance to a structure supported by the database. Document databases often serialize the resource to JSON or BSON formats, however they usually rely on reflection based approaches which directly tie the format to the class structures. This is not ideal and not easily tested.
The Transcoder and Codecs can encode (serialize) the resource into document format with more control over the formatting and logic.
Persist the MediaType in the Database
The MediaType should be saved with the resource in the database.
As the service evolves, it will add support for new MediaTypes. If the codebase retains the older codecs, it will still be able to read older formats.
Database data conversions are not required when adding support for a new MediaType. When reading a record from the database, the service can detect the format from the MediaType and use the proper codec to decode the data into a resource. This works, even if the format is an older format.
Promoted Fields
The example uses a relational database (sqlite) to store documents. The table must have the following columns for simple read and write by ID.
- ID
- MediaType
- Content
For queries that filter on other fields inside the document, the row must include a copy of that field so that SQL queries can filter rows efficiently. I call these “promoted” fields. A promoted field is a read-only copy of a value from the document for inclusion in a SQL where, group, or order by clause.
The promoted field is set by the repository when saving the row. It will be up to date as long as all database updates go through the service.
The example promotes the WeatherForecast’s Temperature field so that the queries can filter forecasts by min/max temperature ranges.
Coarse-Grained Repository Design
The data repository reads and writes the whole resource. Changes to the resource happen outside the repository. Then the whole resource is saved back to the database.
Sqlite Repository
The Codec Example uses a Sqlite database for simplicity. It has no extra setup or containers or processes.
Sqlite is a relational database, not a document database, but it is perfectly capable of storing encoded blobs, along with metadata in additional columns.
See the CodecExample.Data.Sqlite project for the data repository implementation.
SqlKata and Dapper
The example uses SqlKata and Dapper for data repository queries and commands. These two libraries are powerful but simple.
SqlKata is excellent at building a SQL statement dynamically. This is common for HTTP APIs that accept multiple querystring parameters. If some parameters are optional, then they should not be included in the database query. However, building a SQL query is deceptively difficult. Thankfully, SqlKata makes it simple. It also supports a variety of SQL dialects including SQL Server, Sqlite, MySql, etc.
The code snippets below show how SqlKata constructs the query dynamically, and executes it safely via parameterized SQL statements.
WeatherForecastsRepository.FindAll()
/// <summary>
/// Get a collection of forecasts.
/// </summary>
public async Task<IEnumerable<WeatherForecast>> FindAll(
int? minTempCelcius = null,
int? maxTempCelcius = null)
{
using var connection = new SqliteConnection(ConnectionString);
// Use SqlKata to build up the SQL dynamically,
// and safely with parameterized SQL parameters.
var db = new SqlKata.Execution.QueryFactory(
connection,
new SqlKata.Compilers.SqliteCompiler());
var query = db.Query("WeatherForecasts")
.Select("ID", "MediaType", "Content");
// Add this where clause, but only if the value was supplied.
if (minTempCelcius.HasValue)
{
query = query.Where("TempCelcius", ">=", minTempCelcius.Value);
}
// Add this where clause, but only if the value was supplied.
if (maxTempCelcius.HasValue)
{
query = query.Where("TempCelcius", "<=", maxTempCelcius.Value);
}
// Execute the query to fetch the records.
// This uses Dapper to map the columns into the specified type.
var rows = await query.GetAsync<EncodedContentRow>();
// Decode the content on each row into the resource.
var resources = rows.Select(async r =>
await DecodeRow<WeatherForecast>(r));
return await Task.WhenAll(resources);
}
/// <summary>
/// Decodes the response representation into the specified resource type
/// using the specified mediatype to select the proper codec.
/// </summary>
/// <typeparam name="T">The type of resource to return.</typeparam>
/// <returns>The resource decoded by the transcoder and codecs.</returns>
private async Task<T> DecodeRow<T>(EncodedContentRow row)
{
// Use MS class. NET6 only.
var mediaTypeValue = MediaTypeHeaderValue.Parse(row.MediaType);
using (var stream = new MemoryStream(row.Content))
{
var decoderContext = new DecoderContext()
{
InputStream = stream,
MediaType = mediaTypeValue,
ModelType = typeof(T)
};
var responseObject = await Transcoder.ReadAsync(decoderContext);
return (T)responseObject;
}
}
ROI – What did we gain?
While simple, this example demonstrates some important capabilities. Some of these capabilities are difficult to achieve.
1 – Versioning at the Database
Versioned MediaTypes allow the service code and database to evolve forward with minimal friction.
2 – Full Control of Data Formats
Codecs are able to handle arbitrarily complex logic to encode and decode the resource. The format need not match the structure of the resource classes.
3 – Code Reuse
It is often feasible to use the same codecs for HTTP request/response formats and database representations.
Conclusions (Part 5)
The Sqlite repository for the CodecExample shows how to use the Transcoder and Codecs for database persistence. The encapsulated formatting logic is a good fit for data repositories, capable of handling multiple representations with minimal complexity.
Series Conclusions
Content negotiation for HTTP APIs provides numerous benefits to both the service author, and to consumers of the API. It aids with versioning, decouples deployment timing, and empowers the client to request the format it finds most suitable.
ASP.NET Core supports content negotiation, but the APIs are challenging to use. They also couple the formatting logic to ASP.NET, making the formatting logic difficult to reuse elsewhere.
Codecs and the Transcoder provide numerous benefits. They improve and enable rich content negotiation between the client and server. The same codecs can be reused on the client and the server, ensuring quality and correctness. They are modular and easily testable. And as we’ve seen in this last installment, the same concepts benefit the server side data persistence.
Services need strong contracts around the data formats and the endpoints (API) they expose. Codecs encapsulate these details into first-class building blocks, which can be used in multiple parts of the service to ensure long term compatibility, while also enabling the service to evolve forward.
This post is part of a series:
the alnernative 
Leave a Reply