NoSQL and MongoDB

The problem: products with different shapes

Our shop sells phones, shirts and books. Each category has its own attributes:

1. Phone: memory, screen size, color.
2. Shirt: size, fabric.
3. Book: author, number of pages.

In a relational (SQL) database we have two simple ways, and both have problems:

1. One table with hundreds of columns. Most columns stay empty for each product, and the table changes with each new category.
2. An “attribute and value” table. Each attribute is one row. Building one product page needs several JOINs, and queries get complex.

In a document database, each product is one document with just the attributes it needs. The phone document and the shirt document do not need the same shape.

NoSQL families

The word NoSQL is not one kind of database. It is several different families that only share one thing: they are “not relational tables”:

In interviews, people ask most about document databases (like MongoDB). The rest of this lesson is about them.

The document model: embed or reference?

In SQL, you first normalize the data and then build each query with JOINs. In MongoDB it is the other way around. First you ask what the app reads together, then you build the document in that shape.

For an order, we have two choices:

1. Embed. The order lines are inside the order document itself.
2. Reference. The document is separate, and only its ID is stored, like a foreign key.

When should we embed?

1. When they are always read together. Order lines have no meaning without the order.
2. When their number is limited. An order has a few lines, not a million.
3. When they change together. Changing one document in MongoDB is atomic. So the order and its lines are saved together, without a separate transaction.

When should we reference?

1. When it grows without end. Do not put all of a customer’s orders inside the customer document. Each document in MongoDB is at most 16 megabytes.
2. When it is read or changed separately. The customer has their own profile page.
3. When the data is shared and repeated between several documents.

Code with MongoDB.Driver

The product class, with the changing attributes in a Dictionary:

public sealed class Product
{
    public ObjectId Id { get; set; }
    public string Name { get; set; } = "";
    public string Category { get; set; } = "";
    [BsonRepresentation(BsonType.Decimal128)] public decimal Price { get; set; }
    public Dictionary<string, string> Specs { get; set; } = new();
}

Creating the connection, writing and reading:

var client = new MongoClient(builder.Configuration.GetConnectionString("Mongo"));
var products = client.GetDatabase("shop").GetCollection<Product>("products");

await products.InsertOneAsync(new Product
{
    Name = "Phone X",
    Category = "phone",
    Price = 499m,
    Specs = new() { ["ram"] = "8GB", ["screen"] = "6.1 inch" }
}, cancellationToken: ct);

var cheapPhones = await products
    .Find(p => p.Category == "phone" && p.Price < 600m)
    .SortBy(p => p.Price)
    .ToListAsync(ct);

Like SQL, here too MongoDB reads all documents if there is no index. For the query above, create a compound index:

await products.Indexes.CreateOneAsync(
    new CreateIndexModel<Product>(Builders<Product>.IndexKeys
        .Ascending(p => p.Category)
        .Ascending(p => p.Price)),
    cancellationToken: ct);

The MongoClient class is safe for several Threads, and it manages connections by itself. So create it once and register it as a Singleton.

Scale: Sharding

One reason to choose NoSQL is spreading data over several servers. In MongoDB this is called Sharding. Each document goes to one of the servers based on a Shard Key.

Choosing the Shard Key is very important:

1. The main queries must include the Shard Key. If not, MongoDB must ask all the servers.
2. The values must spread well. If the Shard Key is the creation date, all new orders go to one server, and that server gets hot.
3. It is hard to change later. So choose it carefully from the start.

Transactions and consistency

1. Changing one document is always atomic. If you design the document well, most jobs change only one document.
2. Multi-document transactions also exist, but they cost more. If you need one for every simple job, maybe your document model is wrong, or the data is really relational.
3. Reads from copies may be stale. If you read from a Secondary, you may not see the latest write. This is like a Read Replica in SQL.

Choosing between SQL and NoSQL

Common mistakes