SqlDoc is a unit of work + document database on Postgresql (JSON), Sql Server (XML) and Sql Server 2016 (JSON). There are many reasons why Postgres makes a good document store including speed, stability, ecosystem, ACID transactions, mixing with relational data and joins.
As your program runs, record a series of data updates (the unit of work). At the end of the unit of work persist all the changes in a transaction. Changes can be inserts, updates or deletes. PostgresDoc also provides a querying API.
PostgresDoc is written in F# but provides APIs for F# and C#. The C# version simply translates to the F# API.
The CSharp API uses a variation of the IDocumentSession API from RavenDB and Marten.
public class DocumentSessionAPITests { [Fact] public void ICanAddADocumentAndReadItBack() { Giv.n(IAddADocument); Th.n(ICanReadItBack); } [Fact] public void ICanAddADocumentAndDeleteItAndItsGone() { Wh.n(IAddADocument) .And(IDeleteTheDocument); Th.n(TheDocumentIsGone); } [Fact] public void ICanAddADocumentAndUpdateItAndTheChangesPersist() { Wh.n(IAddADocument) .And(IUpdateTheDocument); Th.n(TheChangePersists); } private void IAddADocument() { _aDocument = new PersonCs { _id = Guid.NewGuid(), Name = "Docsesh", Age = 90, FavouriteThings = new[] { "Golf", "Statue of liberty" } }; _documentSession.Store(_aDocument._id, _aDocument); _documentSession.SaveChanges(); } private void ICanReadItBack() { var fresh = _documentSession.Load<PersonCs>(_aDocument._id); Assert.True(_aDocument.Equals(fresh)); } private void IUpdateTheDocument() { _aDocument.Age += 1; _documentSession.Update(_aDocument._id, _aDocument); _documentSession.SaveChanges(); } private void TheChangePersists() { var fresh = _documentSession.Load<PersonCs>(_aDocument._id); Assert.Equal(91, fresh.Age); } private void IDeleteTheDocument() { _documentSession.Delete(_aDocument._id, _aDocument); _documentSession.SaveChanges(); } private void TheDocumentIsGone() { var result = _documentSession.Query<PersonCs>( "select data from PersonCs where Data.value('(/FsPickler/value/instance/idkBackingField)[1]', 'uniqueidentifier') = @id", new Dictionary<string, object> { { "id", _aDocument._id } }); Assert.Empty(result); } private IDocumentSession<Guid> _documentSession = new DocumentSession<Guid>(SqlConnection.From(ConfigurationManager.AppSettings["ConnSql"])); private PersonCs _aDocument; } type Person = { _id: System.Guid; age: int; name: string } let store = { connString = "Server=127.0.0.1;Port=5432;User Id=*******;Password=*****;Database=testo;" } let julio = { _id = System.Guid.NewGuid(); age = 30; name = "Julio" } let timmy = { _id = System.Guid.NewGuid(); age = 3; name = "Timmy" } // newer operations are prepended let uow = [ delete timmy._id timmy; update julio._id { julio with age = 31 }; insert julio._id julio; insert timmy._id timmy; ] commit store uow let peopleWhoAreThirty = [ "age", box (30) ] |> select<Person> store "select data from people where data->>'age' = :age" The database table should have the same name as the type, an id column matching the type used for identifiers, and a json or jsonb data column. The table name should be lowercase.
In the example above I have used Guid (uuid) identifiers and a type called Person so:
create table "person" ( id uuid NOT NULL PRIMARY KEY, data json NOT NULL ); CREATE TABLE [dbo].[person]( [Id] [uniqueidentifier] NOT NULL, [Data] [xml] NOT NULL, CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED ( [Id] ASC ) CREATE TABLE [dbo].[person]( [Id] [uniqueidentifier] NOT NULL, [Data] [nvarchar](max) NOT NULL, CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED ( [Id] ASC ) - Install Paket
- Build
- Create Postgres, old Sql Server and Sql Server >= 2016 databases matching the connection strings in
Tests/app.config. - Run the tests