Introduction
An ORM style layer which turns Azure Table Storage into a cheap and scalable indexed noSQL database - without the costs associated with Azure CosmosDB.
- Tiny Embedded .NET NoSQL Database with cloud storage in Azure Table Storage with massive scaleability
- .NET Objects are stored in Azure Table Storage
- Stores one property in one column on Azure for full compatibility and simplicity
- Can store Decimal/Currency type fields not normally supported in Table Storage
- Can store larger byte arrays over multiple columns automatically
- Properties can be indexed for lightning quick retrieval
- Pass in an example object with an index property set to lookup objects via index
- Properties can be full text indexed
- Pass in a search phrase to search the full text index
- Optional additional query on other columns
- Easy to read automatic time sequential IDs - similar to GUIDs but more user friendly and shorter - or use several other ID types or roll your own
- Optimistic concurrency built in
- Simple blob storage utilities built in
- Other utilities included - Randomize lists, Re-indexing, Backup of Table and Blobs
- Includes an ASP.NET session provider - although work is still needed on this to clear old session data, it is fully working
- USES: -- Multi-tenant apps -- Large scale apps
- In use currently on a multi tenant e-commerce platform
- In use currently on large scale classified selling platform
You can download ALTE code and the demo test project from the links at the top of this post.
Background
Azure Table Storage is a cheap, fast and scalable data store - with no frills. Running SQL Server or Cosmos on Azure is great, but expensive. How about somewhere in the middle?
ALTE was developed to bridge this gap. By using technical documents of how to work around Azure Table's limitations published by Microsoft and others, we thought that maybe we could do this work once, and then forget.
The result is ALTE - which provides a flexible, easy to use, scalable and INDEXED table store. Along the way, it also simplifies how to interact with Azure Tables and adds extra support for more types and bigger column sizes.
It doesn't claim to be a relational database, but it is fast, cheap and easy to use. Nothing to stop you from building your own relationships in code. We've tested it with HUGE data sets - and seen fantastic performance, but it's so easy to use, it works just as well for tiny databases, and in these cases, you pretty much get it for free.
How It Works
We've tried to keep Alte as compatible with normal Azure Tables as possible. In that way, you can use Azure Storage Explorer for diagnosis without too much extra work.
- It's POCO based - inheriting from a base class that includes database generated IDs (or your own!)
- Each POCO type is stored in a separate Azure Table - see below for naming conventions
- Each POCO property is stored in a separate Azure Table column. The exception here is Byte arrays which can span columns (automatically) - we plan to do the same with Strings at a later date
- Non supported types - Decimal for example - are converted to other types and back seamlessly
- We don't use the partition key at all - this may sound bad but it allows us to use batch processing
- We store our "primary" record using
RowID
as PK@RowID
- We store our "secondary" index records, which are a duplicate of the primary record (therefore, with some careful tracking, we only need to load ONE of these records when we retrieve the data) using a
RowID
generated from the column name and column value - We store our full text index separately in another Azure Table, with the partition key used as the keyword and the
rowkey
pointing to the actual ID of the record. We also store other columns along with the index for quick retrieval without going to the parent record. - Each table is prefixed in Azure Tables with our "tenant prefix" and when backed up is also suffixed by the date and time for example:
SWEETSHOPoPRODUCTS
or for backup copies SWEETSHOPoPRODUCTSo201812011255
For blob entries, we use similar:
sweetshop-productimages
or for backup copies sweetshop-productimages-201812011255
The naming convention may seem strange but we are limited by Azure. The "o
" characters are the best we can do at a separator and look a bit like a full stop!
Using the Code
A very simple code example is below in VB.NET - using in C# or other .NET languages is no harder.
Imports Alte
Module Module1
Sub Main()
Dim ACC As String = GetSetting("AlteTest", "Database", "AzureName", "")
Dim SEC As String = GetSetting("AlteTest", "Database", "AzureSecret", "")
Console.WriteLine("Enter Azure Storage Account Name:" +
(IIf(ACC = "", "", "Or press return to use " + ACC)))
Dim ACC2 As String = Console.ReadLine
If ACC2 <> "" Then
ACC = ACC2
SaveSetting("AlteTest", "Database", "AzureName", ACC)
Console.WriteLine("Enter Azure Storage Account Secret:")
SEC = Console.ReadLine
SaveSetting("AlteTest", "Database", "AzureSecret", SEC)
End If
Console.WriteLine("Creating Session")
Dim Session As New AlteSession(ACC, SEC)
Console.WriteLine("Creating Store")
Session.CreateStore(Of Company)()
Session.CreateBlobContainer("pictures")
Dim C As New Company(Session)
C.SetID(ID_Style.EasyBase30_12)
C.CompanyNumber = "123456"
C.CompanyName = "Aversa"
C.AddressLine1 = "The Road"
C.Town = "Stafford"
C.Country = "UK"
C.PostCode = "ST17 000"
C.Notes = "Some sample text for searching"
C.Category = "NewCustomer"
Console.WriteLine("Saving Company")
C.Save()
Console.WriteLine("Getting Company By ID")
Dim C2 = Session.GetObjectByID(Of Company)(C.ID)
Console.WriteLine("Company name : " + C2.CompanyName)
Console.WriteLine("Getting Companies By index")
Dim CS = Session.GetObjectsByIndex(Of Company)
(New Company With {.CompanyNumber = "123456"})
Console.WriteLine("Found " + CS.Count.ToString + " Companies By index")
Console.WriteLine("Getting Companies By index and extra query")
Dim CS1 = Session.GetObjectsByIndex(Of Company)_
(New Company With {.CompanyNumber = "123456"},,, "companyname eq 'Aversa'")
Console.WriteLine("Found " + CS.Count.ToString + " Companies By index")
Console.WriteLine("Getting Companies By Full Text Index")
Dim FTRs = Session.GetFullTextResults(Of Company)("searching")
Dim resft = FTRs.FindAll(Function(x) x.Properties("category") = "NewCustomer")
For Each FTR In FTRs
Console.WriteLine("Found ID " + FTR.ID)
Console.WriteLine("Propery Saved With Index (category) is " + _
FTR.Properties("category"))
Next
Dim CS2 = Session.GetObjectByFullTextResult(Of Company)(FTRs)
Console.WriteLine("Found " + CS2.Count.ToString + " Companies By full text index")
Dim res = CS2.FindAll(Function(x) x.CompanyNumber = "123456")
Console.ReadLine()
End Sub
End Module
Imports Alte
Public Class Company
Inherits AlteObject
Sub New()
End Sub
Sub New(AlteSession As AlteSession)
MyBase.New(AlteSession)
End Sub
Property Title As String
Property FirstName As String
Property LastName As String
<Index>
Property CompanyName As String
<Index>
Property CompanyNumber As String
Property AddressLine1 As String
Property AddressLine2 As String
Property Town As String
Property Country As String
Property PostCode As String
<FTSindex>
Property Notes As String
<FTSindex> <FTSstore>
Property Category As String
Property TurnoverGBP As Decimal
End Class
History
- 29th January, 2019: Initial version
Improvements and bug fixes are always being made - the project is in use in production sites.