SQL Server Support

PostgresDoc now supports SQL Server. SQL Server does not have the required JSON support, so the SQL Server version serializes to xml instead. The serializer supports .net classes, records and discriminated unions.

Create your store like this:

let storeSql = SqlStore "your connection string"

and create tables like this:

CREATE TABLE [dbo].[card](
	[Id] [uniqueidentifier] NOT NULL,
	[Data] [xml] NOT NULL,
 CONSTRAINT [PK_card] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)

Inserting, updating and deleting work the same as for Postresql. Querying requires the use of Sql Server's XML querying capabilities.

Querying

[
        "sourceUrl", box url
        "userId", box userId
]
|> query<Deck> store "select [Data] from [deck] 
where Data.value('(/Deck/userId)[1]', 'uniqueidentifier') = @userId 
and Data.value('(/Deck/sourceUrl)[1]', 'nvarchar(512)') = @sourceUrl" 

There are many more options available for querying sql server xml.

Indexing

Sql server xml indexes traditionally index everything, which is convenient, bulky and slow.

Since 2012 Sql Server also has selective indexing.