Azure Storage Table as a database

Usually when you build a website or an application you need to store your data somewhere. Depending on various aspects you choose which data storage to choose. The most popular options are MS SQL, PostgreSQL, MySQL, MongoDB, and Cosmos DB. These database engines are perfect if you need a data source with high usage. But there are many cases when the data is not going to be used so usually.

How did I come to this?

Lately I found out that I am not that famous and not many people checking my personal website 😢
Previously my website was connected to Azure SQL Database. I chose the lowest configuration of that service. It is so sad to confess but the resources never been used more then 10%. So, I thought maybe I should find cheaper service to contain my data. I have had two options SQLite or Azure Storage Table. The advantage of the second over SQLite is that you can access it using both SDK and API. And it is very similar to the Cosmos DB. So, I started migrating my website to the Azure Storage Table.

When would you think about it?

Well, you should understand that the Azure Storage Table is not that fast. It is not created to replace your SQL or no SQL databases for high demand applications. Usually this can be helpful in two scenarios. The first scenario is when you have not much data or you do not access much. The second scenario is to archive the data in this service. It is cheap to store a large amount of that.

How to get started?

The first thing you need is Azure subscription. But if you don’t have you can Get $200 credit for experimenting with Azure services. With this credit you can create storage which will be free for the next 12 months. To create Azure Storage Table, you need to create regular Azure Storage and then from the left menu choose Azure Table. This will enable Table services.
When you are done creating the service now you need to find two things: storage endpoint URL, storage name and Shared Key. Find the Endpoint section on the left menu. There you should copy and save the Table service endpoint URL. Then go to Access Keys. You will need the Storage account name and Key1’s key.
Before starting coding maybe, you need to install Microsoft Azure Storage Explorer. This app will act as a Microsoft SQL Management Studio if you use SQL database.
Now, there are two options on how to access and manage Azure Storage Table: SDK and API. You need to choose which one you will use. The SDK is available for .NET, Java, Python and TypeScript/JavaScript. If you use any of these technologies, then I strongly recommend using it. The SDK gets updates any time Microsoft makes any changes to the service or API. Plus, SDK adds more options for local models. If you develop in any other language or technology, then you will need to work with API. API is easy to implement so that can work as well. In this article I will show all the examples using C#/.NET and matching SDK.
Creating Repository Class
To add Azure Storage Table SDK to the project just add it from NuGet.
dotnet package add Azure.Storage.Tables
Azure Storage Table SDK sends and receives dictionaries. So, you can implement not fixed schema. In my case I have hardcoded models with initially known properties. Let’s assume I need to keep information about my career. For this reason, I have created the JobInfo model. To use this model as an entity in SDK you have to implement ITableEntity interface. It includes four properties: PartitionKey, RowKey, Timestamp, ETag. These properties are used to separate the data and it is necessary if you archive data. Every time you try to create a new record in the table you have to add PartitionKey and RowKey values. Timestamp and ETag are filled in by Azure.

public class JobInfo : ITableEntity
{
    public int ID { get; set; }
    public string CompanyName { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public string Position { get; set; }
    public string Description { get; set; }
    public bool IsCurrent { get; set; }
    public string CompanyLogo { get; set; }
    public string PartitionKey { get; set; }
    public string RowKey { get; set; }
    public DateTimeOffset? Timestamp { get; set; } = null;
    public ETag ETag { get; set; }
}

Now you need to create a Repository class. This class will implement the SDK functions and will prove connection to Azure. Usually, you will use dependency injection to implement this repository. But since I have only one table, I will implement it as a standalone class with all data. Create Repository class. This class will have one private read-only field of type TableServiceClient. Using this field, we will establish connection to Azure Storage. In a constructor implement this code.

tableServiceClient = new TableServiceClient(
            new Uri("Storage URL from previous section"),
            new TableSharedKeyCredential(
                "Storage name from previous section",
                "Access Key from previous section”
            ));

Now we must implement five functions to have CRUD functionality.

With tableServiceClient we connected to Azure Storage. Now we need to connect to the specific table. For this we use the following code:
TableClient table = tableServiceClient.GetTableClient(tableName);

Now using the table, you can make any action to the table. Below is the code for CURD.
*Get all entities in the table: *

public List<T> GetEntities<T>(string tableName) where T : class, ITableEntity, new()
{
        TableClient table = tableServiceClient.GetTableClient(tableName);
        return table.Query<T>().ToList();
}

*Get specific entity by partition and row: *

public T GetEntity<T>(string tableName, string partiionKey, string rowKey) where T : class, ITableEntity, new()
{
       var table = tableServiceClient.GetTableClient(tableName);
   return tableClient.GetEntity(partitionKey, rowKey);
}

*Create new entity: *

public bool AddEntity<T>(string tableName, T entity) where T : class, ITableEntity, new()
{
        var table = tableServiceClient.GetTableClient(tableName);
        Response response = table.AddEntity(entity);
        if(response.IsError)
            return false;
        return true;
}

*Update existing entity: *

public bool UpdateEntity<T>(string tableName, T entity) where T : class, ITableEntity, new()
{
        var table = tableServiceClient.GetTableClient(tableName);
        entity = table.UpdateEntity(entity);
        return entity;
}

*Delete entity: *

public void DeleteEntity<T>(string tableName, string partiionKey, string rowKey) where T : class, ITableEntity, new()
{
       var table = tableServiceClient.GetTableClient(tableName);
   tableClient.DeleteEntity(partitionKey, rowKey);
}

Now you have a fully functioning repository which is ready to work as a database manager.

P.S. After practicing this technology, I found following problems or undocumented issues. This list will be added by new issues I'll find. Please comment on any issue you have had and I will try to recreate and solve the problem.

• If your entity contains any Date or Time values you should make sure the Kind property of the value is Utc. Otherwise, you will receive an error. By default, DateTime objects' Kind property is set either Local or Unspecified.