Running a document database on Sql Server.
Since Microsoft is slow to come out with a product of their own to support the growing need for document databases (Yes,I know there are other good products on the market like RavenDB, CouchDB, MongoDB from other venders). I want to show how you can run a document database on Sql Server with the help of SisoDb!
First, what is a document database?
"A document-oriented database is, unsurprisingly, made up of a series of self-contained documents. This means that all of the data for the document in question is stored in the document itself — not in a related table as it would be in a relational database. In fact, there are no tables, rows, columns or relationships in a document-oriented database at all. This means that they are schema-free; no strict schema needs to be defined in advance of actually using the database. If a document needs to add a new field, it can simply include that field, without adversely affecting other documents in the database. This also documents do not have to store empty data values for fields they do not have a value for." [ from Exploring CouchDB ]
What is SisoDb?
SisoDb (Simple Structure Oriented database) is a document-oriented db-provider for Sql-Server written in C#. It lets you store entity objects POCOs(Plain Old CLR Object) without having to configure any mappings. And SisoDb is Open-source and is written in C# and completely free!
The C# Code
Here is a quick sample of the C# code needed to store ex a Customer in the DB.
public class Customer
{
public Guid StructureId { get; set; }
public int CustomerNo { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
}
class Program
{
static void Main(string[] args)
{
var cnInfo = new Sql2008ConnectionInfo(@"sisodb:provider=Sql2008||plain:Data source=.\SQL2008;Initial catalog=SisoDb.Profiling;Integrated security=SSPI;");
var db = new Sql2008DbFactory().CreateDatabase(cnInfo);
db.EnsureNewDatabase();
var customer = new Customer
{
CustomerNo = 35,
Firstname = "Jon",
Lastname = "Doe"
};
db.WriteOnce().Insert(customer);
}
}
{
public Guid StructureId { get; set; }
public int CustomerNo { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
}
class Program
{
static void Main(string[] args)
{
var cnInfo = new Sql2008ConnectionInfo(@"sisodb:provider=Sql2008||plain:Data source=.\SQL2008;Initial catalog=SisoDb.Profiling;Integrated security=SSPI;");
var db = new Sql2008DbFactory().CreateDatabase(cnInfo);
db.EnsureNewDatabase();
var customer = new Customer
{
CustomerNo = 35,
Firstname = "Jon",
Lastname = "Doe"
};
db.WriteOnce().Insert(customer);
}
}
What is happening under the hood?
The concepts of SisoDb are really simple. You design an object entity in C# that in SisoDb is called a Structure. A Structure is basically a Document in a document-oriented database. The only demand SisoDb has, is that you add one property containing the Id of the structure like the StructureId in the Customer example above.
All objects (structures) you pass to SisoDb are stored as JSON and as key-value indexes and SisoDb enables us to go from an object entity to JSON. For each type of entity there will be a set of tables created for you on the fly.
In the customer example above I got four table created in my SisoDb.Profiling database
· CustomerIndexes
· CustomerStructure
· CustomerUniques
· SisoDbIdentities
The Structure-table, is the "master" table and it holds the Id of the entity and the JSON-representation of the structure/document.
SELECT TOP 1000 [RowId]
,[StructureId]
,[Json]
FROM [SisoDb.Profiling].[dbo].[CustomerStructure]
The Indexes-tables they hold the value of each indexed property in the object graph, with the purpose of providing a more friendly representation of the entity.
SELECT TOP 1000 [RowId]
,[StructureId]
,[MemberPath]
,[StringValue]
,[IntegerValue]
,[FractalValue]
,[DateTimeValue]
,[BoolValue]
,[GuidValue]
FROM [SisoDb.Profiling].[dbo].[CustomerIndexes]
Uniques-table holds values of the scalar properties that you mark as unique and ensures that your unique constraints are enforced.
SELECT TOP 1000 [RowId]
,[StructureId]
,[UqStructureId]
,[UqMemberPath]
,[UqValue]
FROM [SisoDb.Profiling].[dbo].[CustomerUniques]
Identities-table holds entities created,
SELECT TOP 1000 [EntityHash]
,[EntityName]
,[CurrentId]
FROM [SisoDb.Profiling].[dbo].[SisoDbIdentities]
Conclusion
I really like it! Usually when dealing with NoSql databases I missed the great infrastructure features that SQL-server provides: Security, replication, scheduler, transactions, ETL options etc.
Would I use SisoDb in a high load scenario? Probably no since there is an overhead to use it, BUT I'm using it in production today, in other scenarios with good experience!
Where to go from here?
There is good documentation on the SisoDb site that helps you get started, really quick. Check out the below links.
