Support SQL Server 2025 native JSON data type

[johngrimes]

Summary

Add configuration option to use SQL Server 2025's native JSON data type for the json column, and add SQL Server 2025 Preview to the test matrix.

Current state

The table schema currently uses NVARCHAR(MAX) for JSON storage (README.md lines 445-449):

CREATE TABLE [dbo].[fhir_resources] (
    [id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [resource_type] NVARCHAR (64) NOT NULL,
    [json] NVARCHAR (MAX) NOT NULL
);

Motivation

SQL Server 2025 Preview introduces a native JSON data type that provides:

• More efficient storage: Native binary format using UTF-8 encoding, approximately 18% storage reduction compared to NVARCHAR(MAX)
• Faster reads: Documents are pre-parsed, eliminating parse overhead
• Faster writes: Can update individual values without rewriting the entire document
• Automatic validation: Invalid JSON is rejected on insert/update

No query changes required: According to the Microsoft documentation, existing JSON functions (JSON_VALUE, JSON_QUERY, OPENJSON) work seamlessly with the native json data type without any modifications.

Proposed solution

Add a configuration option for the json column data type:

Programmatic API:

const sqlOnFhir = new SqlOnFhir({
  tableName: 'fhir_resources',
  schemaName: 'dbo',
  resourceJsonDataType: 'JSON'  // New option
});

CLI option:

npx sof-mssql load ./data \
  --host localhost \
  --resource-json-data-type "JSON"

Default behaviour:

• NVARCHAR(MAX) remains the default for backwards compatibility
• Users can opt-in to the JSON type when using SQL Server 2025+

Implementation tasks

1. Add resourceJsonDataType configuration option to SqlOnFhir constructor
2. Add --resource-json-data-type CLI flag
3. Update table creation logic to use configured data type
4. Add SQL Server 2025 Preview to test matrix
5. Verify all tests pass with JSON data type
6. Perform a manual test of relative performance between NVARCHAR and JSON
7. Update README.md to document the new configuration option

Documentation updates

README.md:

• Document new resourceJsonDataType configuration option in "Custom table configuration" section
• Update database setup section showing the JSON type as an option for SQL Server 2025+
• Note that NVARCHAR(MAX) remains the default

References

• SQL Server 2025 What's New
• JSON Data Type Documentation
• Working with JSON Data