Key-Value store built with CockroachDB, .Net 5.0, and Entity Framework
Tags: cockroachdb, csharp, dotnet, azure
Published: 2021-11-07 19:46:32

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

- [You have setup your cockroachDB cluster](https://www.cockroachlabs.com/docs/cockroachcloud/create-a-serverless-cluster.html)
- [You have created an empty .net 5.0 web api project using visual studio or jetbrains.](https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-5.0&amp;tabs=visual-studio)

#### 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`

```powershell
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](https://www.cockroachlabs.com/docs/stable/build-a-csharp-app-with-cockroachdb.html?filters=windows#set-up-your-cluster-connection).  Also, replace `{cluster-id}` with the id of your cockroachDB cluster.

```powershell
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 

```json
"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](https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-5.0) 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

```c#
    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`

```c#
    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`

```c#
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. 

```powershell
    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.

```c#
    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](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](https://key-value-store.ndolestudio.com/index.html). Please no DDOS attacks 🙏