This page gives you a brief overview of some of the core concepts in Nevermore.

Nevermore is designed to map documents. What's a document? Well, for our purposes it's a class. Here's an example of a document we'll be working with. The only assumption is that you'll provide an "Id" property which is a string (though it doesn't have to be a string).

class Person
{
    public string Id { get; private set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    
    // Documents can have all kinds of things on them, including arrays, 
    // nested types, and more. All these properties will be serialized 
    // and stored in the JSON blob.
    public HashSet<string> Tags { get; } = new HashSet<string>();    
    public int[] LuckyNumbers { get; set; }
}

Note that the class has some properties with arrays or hashsets - things that don't get stored in columns in SQL very well. In Nevermore, anything that isn't written to a column is serialized as JSON and stored in a JSON column.

This allows documents to store other nested classes as well. For example, imagine an "order", with a list of line items, where each line item is an object. All of this data would be serialized and stored in the JSON column.

Each document type gets a SQL table. Here's a script that creates the SQL table we'll use for this document.

CREATE TABLE [Person] (
  [Id] NVARCHAR(50) NOT NULL CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED, 

  -- These are columns we might need to query against
  [FirstName] NVARCHAR(20) NOT NULL, 
  [LastName] NVARCHAR(200) NULL, 
  [Email] NVARCHAR(200) NOT NULL, 

  -- This is where we store everything else
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Person] ADD CONSTRAINT [UQ_UniquePersonEmail] UNIQUE([Email])

ℹ️ It's a good practice to name your constraints (primary key and unique constraints above, for example) in case you need to drop or disable them later.

If you haven't already, install Nevermore from NuGet:

Install-Package Nevermore -Version 11.1.1

Nevermore maps your document class to the database table using a DocumentMap:

class PersonMap : DocumentMap<Person>
{
    public PersonMap()
    {
        Column(m => m.FirstName).MaxLength(20);
        Column(m => m.LastName);
        Column(m => m.Email);
        Unique("UniquePersonEmail", new[] { "Email" }, "People must have unique emails");
    }
}

That's everything we need to model our document database. We just need to set up a RelationalStore.

// You just need a SQL Server connection string
var config = new RelationalStoreConfiguration(ConnectionString);

// And tell Nevermore about your document maps. 
config.DocumentMaps.Register(new PersonMap());

// Create your store. You'll do this once when the application starts up.
var store = new RelationalStore(config);

There are a lot of other things you can do with the RelationalStoreConfiguration, but most of it is for advanced usage (things like configuring how JSON is serialized, or how we map database types to CLR types).

Before using Nevermore for the first time you will need to run these SQL scripts in the target database.

Let's create a document!

var person = new Person 
{
    FirstName = "Donald", 
    LastName = "Duck", 
    Email = "donald.duck@disney.com", 
    Tags = {"duck", "disney", "\u2103"}, 
    LuckyNumbers = new[] { 7, 13 }
};

// BeginTransaction creates a SQL transaction around this scope
using var transaction = store.BeginTransaction();
transaction.Insert(person);
transaction.Commit();

// ID's are assigned automatically when the Insert call completes.
// Alternatively, if your Id property is settable, you can set it 
// yourself
person.Id.Should().Be("Persons-1");

If you know the ID of the document you want (e.g., it's in a URL query string), you can Load it:

var person = transaction.Load<Person>("Persons-1");

Load returns null if it can't find the document, so you might want to use LoadRequired, which will throw if the document is missing:

var person = transaction.LoadRequired<Person>("Persons-1");

Beyond "Load", most of the queries you'll write will be against collections of documents. Since properties that you "map" are stored as columns, you can query against those columns. Here are some different ways to query. Query gives you a strongly typed collection:

// This becomes the SQL "where" clause
var person = transaction.Query<Person>()
    .Where("FirstName = @name and Email is not null")                
    .Parameter("name", "Donald")
    .FirstOrDefault();

// If for some reason you want to query a SQL database but SQL scares 
// you, you can also use LINQ support: 
person = transaction.Query<Person>()
    .Where(m => m.FirstName == "Donald")
    .FirstOrDefault();

// Or, you can use a perfectly good language for querying SQL, called... SQL!
// Nevermore handles the mapping of the result set to the object type
person = transaction.Stream<Person>(
    "select * from dbo.Person where FirstName = @name",
    new CommandParameterValues {{"name", "Donald"}}
    ).Single();

// SQL Server 2016 and above supports JSON_VALUE as a function. This can be used to 
// query for data stored in the JSON blob at the end of the document.
// For example, you can use JSON_VALUE to query a single field within the JSON. 
// Or you can use OPENJSON to query values in an array. The only downside to doing 
// this of course is that you won't get to take much advantage of indexes.
person = transaction.Query<Person>()
    .Where(@"
       exists (SELECT value FROM OPENJSON([JSON],'$.Tags') where value = @tag1) and 
       exists (SELECT value FROM OPENJSON([JSON],'$.Tags') where value = @tag2)")
    .Parameter("tag1", "wb")
    .Parameter("tag2", "duck")
    .FirstOrDefault();

You don't just have to query mapped documents with JSON columns. If you're not shy to write a little SQL yourself, you can query other things:

You can write a query that returns a tuple:

var result = transaction.Stream<(string LastName, int Count)>(
    @"select LastName, count(*) 
      from dbo.Person 
      group by LastName 
      order by count(*) desc, 
               len(LastName) desc"
).ToList();

Or a query that returns an arbitrary class without a document map:

class Result
{
    public string FullName { get; set; }
    public string Email { get; set; }
}

var result = transaction.Stream<Result>(
    @"select 
        FirstName + ' ' + LastName as FullName, 
        Email 
      from dbo.Person 
      order by FirstName"
).First();

Load, Query, Stream? Which one do I use?

Here's the deal:

Load and Query, Insert, Update, Delete, and so on only work on documents. That is, a class with a DocumentMap. They are stored on a table with a JSON column, and reading them involves deserializing the JSON.

With Stream, you provide the SQL, and it can return whatever you like. It can map the result to a tuple, arbitrary class or document (so long as you include the JSON column). You just can't insert/update/delete them.

On the IRelationalTransaction you'll also find lots of other methods, like Update(document) or Delete(document). They work as you'd expect.

Nevermore is inspired by Dapper. Methods like Stream and Load are convenient wrappers, but we don't want to hide SQL from you. The IRelationalTransaction in Nevermore also exposes some lower-level operations. Don't be afraid to use them if the situation calls for it.

transaction.ExecuteNonQuery("insert into Foo (Id, Name) values (@id, @name)", 
   new CommandParameterValues 
   {
       { "id", 7 },
       { "name", "Bobby tables" }
   });

var count = transaction.ExecuteScalar("select count(*) from Machine");

using var reader = transaction.ExecuteReader("select * from SomeTable");
while (reader.Read()) // ...

Nevermore provides full async support - for all the operations illustrated above, you'll find async versions too.

Before you go scattering async/await everywhere, you might want to reconsider. Using async with SQL Server often doesn't provide much of a benefit. It tends to work best when SQL Server is far away, or when it takes SQL time to execute the query and prepare the first row.

As a rule of thumb, use async in HTTP requests (e.g., in controller actions). If you have a long-running background job (e.g., during a deployment), default to the synchronous versions. When in doubt, measure.

A final note: in our examples above, we created a transaction using BeginTransaction. You can also call:

using var readTransaction = store.BeginReadTransaction();
using var writeTransaction = store.BeginWriteTransaction();

A read transaction only has methods for reading from the database, although it does expose a handful of methods like ExecuteNonQuery which someone may use to write data.

A write transaction has both read and write operations (as you rarely write data without reading something first, and you want your reads to be in the same transactional scope).

The reason for using either is mostly to protect yourself. If you find yourself passing transactions to other functions, it's good to make the expectation clear.

For our website, Octofront, we use Azure SQL geo-replication to set up distributed read-only secondaries around the world. In our IOC container, if you inject an IReadTransaction, we resolve it from a relational store pointed at the read-only secondary. If you inject an IWriteTransaction, we resolve it from a store pointing at the primary database - which might be further away.

One final thing - Nevermore has built-in retry logic. Queries that result in SELECT and DELETE (like Load, Stream, Query, Delete, etc.) will automatically retry if a transient connection error is detected. This behavior can be overridden when you create a transaction.

Congratulations, you are now a Certified Nevermore Expert. All the best and good luck! 👋