Biggy Basics Part 2: Getting Relational

Tuesday, March 2 2014 biggy database projects

Up to now I've posted mostly about the document-oriented features of Biggy, but that's only half the story. Let's take a look at the relational side of things

A Very Fast Query Tool

Let's say you have a large record collection full of Artists and Albums:


This is the Chinook database which is a nice alternative to Northwind and it's what we use for testing Biggy (many thanks to John Atten for making this and many other nice things happen). We can query this with Biggy easily:

//create your class
class Artist {  
  [PrimaryKey] //this is optional here as we snoop "ID" or "ArtistID"
  public int ArtistId { get; set; }
  public string Name { get; set; }

//crack open a DBList
var db = new SQLServerList<Artist>("chinook"); //just pass in the connection string name

//Query it with LINQ
var acdc = _artists.FirstOrDefault(a => a.Name == "AC/DC");  

This works for SQLServer, Postgres and even MongoDB (thanks to K. Scott Allen). There's really nothing more to talk about with the DBList T - it works like our document storage does by pulling all records into memory so you can query with Linq to Objects over any backing store we support (SQL Server, postgres Mongo so far).

It's really fast as the title image shows - 0ms for a joined association. Of course this degrades somewhat if you do deeply nested LINQ rollups - but if you find this degradation happening, or if you have way too many records to pull into memory - skip down to the next section.

DBTable T

Underlying all of what Biggy does is a newer version of Massive that's a bit more type-friendly. I've renamed Massive to DBTable and also set it to be generically typed. This doesn't mean it can't be dynamic - it sure can just use DBTable<dynamic>. More on that in a minute.

If you want to run a normal query you can use the DBTable flavor of your choice. Here I'll use Postgres:

var table = new PGTable<Artist>("chinook");  
var steve = table.Find(12); //12 is the id for steve  
var aNames = table.Where("Name LIKE 'a%'");  
var buddy = table.FirstOrDefault("Name = 'Buddy Guy'");  

You can see more on the Github README - including inserts, updates and deletes - all of which are just like using Massive.

Getting Dynamic

If you're not afraid of SQL and want to run an ad-hoc query, you sure can:

var table = new SQLServerTable<dynamic>(_connectionStringName);  
dynamic results = table.Query(@"select Artist.Name AS ArtistName, Track.Name, Track.UnitPrice  
                            from Artist inner join
                            Album on Artist.ArtistId = Album.ArtistId inner join
                            Track on Album.AlbumId = Track.AlbumId
                            where (Artist.Name = @0)", "ac/dc");
foreach(var track in results){  
  //do your thing

This can come in handy when you have a specific need for a join, rollup or... whatever. As mentioned above - LINQ is great for a lot of things but sometimes you'll just want to let your DB handle the load.

Transactional Writes

Not there yet - but hopefully coming soon. I was thinking it might be good to have UnitOfWork somehow but I think that can be invasive if people are using Repositories (and after all, those two things don't mix do they!).

The idea that we're looking into is creating a way to wrap a "CommandObject" - allowing you to formally declare your transaction and what goes into it. So far I'm thinking that we leverage what we have internally for executing an array of DBCommands within a transaction - and somehow figuring out what you want to do and in what order.

The problem comes in with dynamic key assignment. The classic use case is a new order or invoice - where you have to save the items against a new order key - but you have to write the order first to have that happen.

Anyway - that's the next step. For now this is where we're at.


Not until this thing is a bit more polished and we have transaction support :). We have the package hooks and you can download the bits straight from here if you want but I need a bit more time so our issue list doesn't fill up. The API will change - so I want to be sure you don't get caught out.