Quickstart

This page gives you a brief overview of some of the core concepts in Nevermore. It's based on

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 properties, and more. 
    // All these properties will be 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.

Every 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.

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).Nullable();
        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.Mappings.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).

Inserting

Let's create a document!

var person = new Person {FirstName = "Donald", LastName = "Duck", Email = "[email protected]", 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");

Loading

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");
person.FirstName.Should().Be("Donald");

You can also fetch all documents of a type:

var person = transaction.Query<Person>().ToList();

Of course, this is slow, so it's not a great idea.

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. TableQuery gives you a strongly typed collection:

// This becomes the SQL "where" clause
var person = transaction.TableQuery<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.TableQuery<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.TableQuery<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();