Key-Value store built with CockroachDB, .Net 5.0, and Entity Framework

7 0

Nov 7 '21

In this article, we are going to build a simple key-value store using Entity Framework 5.0, .Net 5.0, and CockroachDB Serverless.

Prerequisites

Install NuGet packages

Install the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Design nuget packages version 5.0.10. The Design package helps when we want to generate migrations from our model classes.

Install the entity framework core provider for PostgreSQL called Npgsql.EntityFrameworkCore.PostgreSQL version 5.0.10

dotnet add package Microsoft.EntityFrameworkCore --version 5.0.10
dotnet add package Microsoft.EntityFrameworkCore.Design --version 5.0.10
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 5.0.10

Setup connection to CockroachDB cluster

Download the cockroachDB certificate using the command below on powershell. If you use linux/mac, You can find alternative commands here. Also, replace {cluster-id} with the id of your cockroachDB cluster.

mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/{cluster-id}/cert -OutFile $env:appdata\.postgresql\root.crt

Add a connection string in the appsettings.json file.

replace {host} with your cluster host e.g free-tier.gcp-us-central1.cockroachlabs.cloud
replace {username} with your database username
repalce {password} with your database password
replace {cluster.database} with your cluster databse. e.g if your cluster name is key-value-store-123 on cockroachDB, and you plan to use the default database, then {cluster.database} = key-value-store-123.defaultdb
replace {cert-location} with the directory of the certificate

"ConnectionStrings": {
    "ApplicationDbContext": "Host={host};Port=26257;SslMode=Require;username={username};password={password};Database={cluster.database};Root Certificate={cert-location};"
},

**ProTip: **You can use dotnet user-secrets to ensure that your credentials are not commited to git.

Setup Models and DBConntext

Create an Entities directory in the root of the api project and add the following entity

    namespace KeyValueStore.Entities
    {
        public class KeyValueEntry
        {
            [Required]
            [Key]
            [StringLength(44)]
            public string Key { get; set; } = default!;

            [Required] [Column(TypeName = "text")]
            public string Value { get; set; } = default!;

            [Required]
            public DateTime CreatedAt { get; set; }
        }
    }

KeyValueEntry entity
Create another directory called Database in the Api prject and add an ApplicationDbContext

    namespace KeyValueStore.Database
    {
        public class ApplicationDbContext : DbContext
        {
            public DbSet<KeyValueEntry> KeyValueEntries { get; set; } = default!;

            public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
            {
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<KeyValueEntry>().ToTable("store");
                modelBuilder.Entity<KeyValueEntry>().HasKey(x => x.Key);
            }
        }
    }

Database Migrations

First, register the ApplicationDbContext with the .net dependency injection. Add the following in the ConfigureServices method in Startup.cs

services.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql(Configuration.GetConnectionString(nameof(ApplicationDbContext))));

Dependency Injection
Run the commands below in powershell to generate migrations and update your database.

    dotnet tool install --global dotnet-ef
    dotnet ef migrations add InitialCreate
    dotnet ef database update

Migrate Database

Create Controller

Create a controller called StoreController this will have the methods to add, update and delete key value entries from the cockroachDB database.

Note

Instead of storing the entire key, we create a sha256 has of the key. This helps reduce the amount of disk space needed to store the key
The value entries needs to be encoded in base64 this is to prevent all types of encoding issues on utf-x strings.
I used FlexLabs.EntityFrameworkCore.Upsert nuget version 5.0.0 in the PutAsync method to carry out an upsert database query.

    namespace KeyValueStore.Controllers
    {
        /// <summary>
        /// Add, update, delete and fetch key-value entries.
        /// </summary>
        [ApiController]
        [Route("v1/store/{key:maxlength(256):minlength(1)}")]
        [Consumes(MediaTypeNames.Text.Plain)]
        [Produces(MediaTypeNames.Text.Plain)]
        public class StoreController : ControllerBase
        {
            private readonly ApplicationDbContext _dbContext;

            public StoreController(ApplicationDbContext dbContext)
            {
                _dbContext = dbContext;
            }

            /// <summary>
            /// Get a specific value by the key
            /// </summary>
            /// <param name="key" example="995c0628-3fb3-11ec-9356-0242ac130003">The max length is 256 characters</param>
            /// <param name="cancellationToken">Used to cancel the request</param>
            /// <response code="200" example="dmFsdWU=">Value retrieved</response>
            /// <response code="404">There is no entry for the key in the store</response>
            /// <response code="500">Internal Server Error</response>
            [HttpGet]
            [ProducesResponseType(typeof(string), StatusCodes.Status200OK)]
            [ProducesResponseType(StatusCodes.Status500InternalServerError)]
            public async Task<IActionResult> GetAsync([FromRoute] string key, CancellationToken cancellationToken)
            {
                var item = await _dbContext.KeyValueEntries
                    .FirstOrDefaultAsync(x => x.Key == Hash(key), cancellationToken);

                if (item == null) return NotFound();

                return new OkObjectResult(item.Value);
            }

            /// <summary>
            /// Upsert a key value entry.
            /// </summary>
            /// <remarks>
            /// The body of the request will be stored as the value for the "key"<br/><br/>
            /// <strong>NOTE:</strong>
            /// - The body must be a <see cref="!:https://en.wikipedia.org/wiki/Base64">base64</see> encoded string. <br/>
            /// - The body must be maximum 5kb long. ie. the length should be less than or equal to 5120 characters 
            /// </remarks>
            /// <param name="key" example="995c0628-3fb3-11ec-9356-0242ac130003">The max length is 256 characters</param>
            /// <param name="body" example="dmFsdWU=">The value encoded in base64. e.g the word "value" encoded in base64 is "dmFsdWU="</param>
            /// <param name="cancellationToken">Used to cancel the request</param>
            /// <response code="204">Value set successfully</response>
            /// <response code="400">Validation Error</response>
            /// <response code="500">Internal Server Error</response>
            [HttpPut]
            [Produces(MediaTypeNames.Application.Json)]
            [ProducesResponseType(typeof(IList<string>), StatusCodes.Status400BadRequest)]
            public async Task<IActionResult> PutAsync(
                [FromRoute] string key,
                [FromBody] string body,
                CancellationToken cancellationToken
            )
            {
                var item = new KeyValueEntry
                {
                    Key = Hash(key),
                    Value = body,
                    CreatedAt = DateTime.UtcNow
                };

                await _dbContext.KeyValueEntries
                    .Upsert(item)
                    .On(x => x.Key)
                    .RunAsync(cancellationToken);

                return NoContent();
            }

            /// <summary>
            /// Delete a key value entry.
            /// </summary>
            /// <param name="key" example="995c0628-3fb3-11ec-9356-0242ac130003">The max length is 256 characters</param>
            /// <param name="cancellationToken">Used to cancel the request</param>
            /// <response code="204">Value deleted successfully</response>
            /// <response code="404">There is no entry for the key in the store</response>
            /// <response code="500">Internal Server Error</response>
            [HttpDelete]
            [ProducesResponseType(StatusCodes.Status500InternalServerError)]
            public async Task<IActionResult> DeleteAsync(
                [FromRoute] string key,
                CancellationToken cancellationToken
            )
            {
                var item = await _dbContext.KeyValueEntries
                    .FirstOrDefaultAsync(x => x.Key == Hash(key), cancellationToken);

                if (item == null) return NotFound();

                _dbContext.KeyValueEntries.Remove(item);

                await _dbContext.SaveChangesAsync(cancellationToken);

                return NoContent();
            }

            private static string Hash(string value)
            {
                using var hash = SHA256.Create();
                var byteArray = hash.ComputeHash(Encoding.UTF32.GetBytes(value));
                return Convert.ToBase64String(byteArray).ToLower();
            }
        }
    }

Conclusion

The entire code for this project can be found on github https://github.com/AchoArnold/key-value-store. I also hosted it on azure and you can play with it live on https://key-value-store.ndolestudio.com. Please no DDOS attacks 🙏