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 documents

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 documents

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");

Querying documents

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();

Querying other things

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();