SQL Server Guide for FraiseQL

Introduction

SQL Server is a first-class FraiseQL runtime target — not a fallback for teams that cannot migrate to PostgreSQL. If your organization already runs SQL Server, your existing T-SQL investment maps directly onto FraiseQL’s SQL-as-business-logic architecture.

FraiseQL compiles your Python schema definitions into a runtime configuration and executes against your existing T-SQL views and stored procedures. There is no ORM layer to fight, no migration required, and no PostgreSQL parity gap to work around.

Why SQL Server Teams Choose FraiseQL

Capability	SQL Server advantage
T-SQL debugging	SSMS and Azure Data Studio support breakpoints, variable inspection, and step-through execution inside stored procedures — debuggability that PostgreSQL’s plpgsql doesn’t match
Enterprise adoption	Most .NET, finance, healthcare, and government teams have decades of T-SQL expertise and existing stored procedure libraries
FOR JSON PATH	Native JSON generation maps cleanly onto FraiseQL’s single-column data view pattern
Service Broker	Built-in async messaging for decoupled mutation side-effects without an external queue
Temporal tables	SYSTEM_TIME versioning provides point-in-time audit trails as a first-class engine feature
Always On AG	Built-in HA/DR with automatic failover — no Patroni, no Repmgr
Always Encrypted	Column-level encryption for PCI-DSS and HIPAA without application changes
Azure SQL	Seamless cloud deployment with managed identity, auto-backup, and geo-replication

Feature Summary

• ~18 WHERE Operators: Suitable for complex GraphQL input types
• FOR JSON PATH: Native JSON generation matching GraphQL schema
• Indexed Views: Automatic materialized view maintenance (unique to SQL Server)
• Always On Availability Groups: Built-in HA/DR without additional tools
• Transparent Data Encryption: Enterprise security at database level
• Row-Level Security: Column and row filtering for multi-tenancy
• Azure SQL: Seamless cloud deployment with managed backup/HA
• Strong Windows Integration: Active Directory, Azure AD authentication

Core Architecture

Single JSON Data Column Pattern

Like all FraiseQL databases, views expose entities as single JSON columns named data:

-- Every v_* view returns:
-- 1. Metadata columns (id, is_current, etc.)
-- 2. Single JSON column named 'data' containing complete entity

SELECT
  id,
  is_current,
  data
FROM v_user
WHERE id = @user_id;

Why? The FraiseQL Rust server receives the complete entity as a single JSON payload, no assembly needed.

Trinity Pattern: UNIQUEIDENTIFIER + BIGINT PKs

FraiseQL uses dual identifiers:

CREATE TABLE dbo.tb_user (
  pk_user    BIGINT           PRIMARY KEY IDENTITY(1,1),
  id         UNIQUEIDENTIFIER NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),  -- Public UUID
  identifier NVARCHAR(255)   NOT NULL UNIQUE,  -- Human-readable key (e.g. email, username)
  name       NVARCHAR(255)   NOT NULL,
  deleted_at DATETIMEOFFSET  NULL,
  created_at DATETIMEOFFSET  DEFAULT GETUTCDATE(),
  updated_at DATETIMEOFFSET  DEFAULT GETUTCDATE()
);

CREATE TABLE dbo.tb_post (
  pk_post    BIGINT           PRIMARY KEY IDENTITY(1,1),
  id         UNIQUEIDENTIFIER NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
  identifier NVARCHAR(255)   NOT NULL UNIQUE,  -- Human-readable key (e.g. slug)
  fk_user    BIGINT           NOT NULL REFERENCES dbo.tb_user(pk_user) ON DELETE CASCADE,
  title      NVARCHAR(255)   NOT NULL,
  deleted_at DATETIMEOFFSET  NULL,
  created_at DATETIMEOFFSET  DEFAULT GETUTCDATE()
);

Why?

• pk_* (BIGINT IDENTITY): Fast joins, small FK storage, internal only — never exposed in GraphQL
• id (UNIQUEIDENTIFIER): UUID exposed in GraphQL, immutable across systems
• identifier (NVARCHAR): Human-readable domain key (email, slug, SKU, etc.) — always unique
• NEWSEQUENTIALID(): Sequential UUIDs avoid index fragmentation

Note

SQL Server uses IDENTITY(1,1) in place of the ANSI standard GENERATED ALWAYS AS IDENTITY used on PostgreSQL. Both are equivalent for FraiseQL’s purposes — the column is internal and never exposed in the GraphQL API.

Resolver Functions

Every table has UUID to BIGINT resolver functions:

-- File: 02001_resolver_functions.sql
CREATE FUNCTION dbo.fn_get_pk_user(@user_id UNIQUEIDENTIFIER)
RETURNS BIGINT AS
BEGIN
  RETURN (SELECT pk_user FROM dbo.tb_user WHERE id = @user_id);
END;

CREATE FUNCTION dbo.fn_get_user_id(@pk_user BIGINT)
RETURNS UNIQUEIDENTIFIER AS
BEGIN
  RETURN (SELECT id FROM dbo.tb_user WHERE pk_user = @pk_user);
END;

Mutation Response Type

SQL Server stored procedures return JSON results:

{
  "status": "success",
  "message": "User created successfully",
  "entity_id": "550e8400-e29b-41d4-a716-446655440000",
  "entity_type": "User",
  "entity": { "id": "...", "identifier": "john@example.com", "name": "John Doe" },
  "updated_fields": ["identifier", "name"],
  "cascade": { "updated": [] },
  "metadata": { "operation": "INSERT", "timestamp": "2024-02-08T10:30:00Z" }
}

View Structure: v_* (Regular Views)

Views are the source truth for read operations:

-- File: 02411_v_user.sql
CREATE OR ALTER VIEW dbo.v_user AS
SELECT
  u.id,
  CASE WHEN u.deleted_at IS NULL THEN 1 ELSE 0 END AS is_current,
  (SELECT
    u.id         AS id,
    u.identifier AS identifier,
    u.name       AS name,
    u.created_at AS created_at
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS data
FROM dbo.tb_user u
WHERE u.deleted_at IS NULL;

Note

SQL Server uses FOR JSON PATH, WITHOUT_ARRAY_WRAPPER to generate JSON objects from rows. This is equivalent to PostgreSQL’s jsonb_build_object().

Nested Views (One-to-Many Relationships)

-- File: 02412_v_user_with_posts.sql
CREATE OR ALTER VIEW dbo.v_user_with_posts AS
SELECT
  u.id,
  CASE WHEN u.deleted_at IS NULL THEN 1 ELSE 0 END AS is_current,
  (SELECT
    u.id         AS id,
    u.identifier AS identifier,
    u.name       AS name,
    (SELECT
      p.id         AS id,
      p.identifier AS identifier,
      p.title      AS title
    FROM dbo.tb_post p
    WHERE p.fk_user = u.pk_user AND p.deleted_at IS NULL
    FOR JSON PATH) AS posts
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS data
FROM dbo.tb_user u
WHERE u.deleted_at IS NULL;

Note

SQL Server FOR JSON PATH returns NULL for empty subqueries rather than []. Handle this in your application layer or use COALESCE((...FOR JSON PATH), '[]').

Indexed Views: tv_* (Materialized Projections)

SQL Server’s indexed views are the equivalent of materialized views — they are maintained automatically by the database engine:

-- File: 02414_tv_user.sql
-- Standard table-backed projection (like other databases)
CREATE TABLE dbo.tv_user (
  id            UNIQUEIDENTIFIER PRIMARY KEY,
  is_current    BIT              DEFAULT 1,
  data          NVARCHAR(MAX)    NOT NULL,  -- JSON stored as NVARCHAR
  updated_at    DATETIMEOFFSET   DEFAULT GETUTCDATE(),
  last_synced_at DATETIMEOFFSET  NULL,
  sync_count    BIGINT           DEFAULT 0,
  is_stale      BIT              DEFAULT 0
);

CREATE INDEX idx_tv_user_is_current ON dbo.tv_user(is_current);

Refresh stored procedure:

CREATE OR ALTER PROCEDURE dbo.sync_tv_user_single
  @user_id UNIQUEIDENTIFIER
AS
BEGIN
  DECLARE @new_data NVARCHAR(MAX);
  SELECT @new_data = data FROM dbo.v_user_with_posts WHERE id = @user_id;

  IF @new_data IS NOT NULL
  BEGIN
    MERGE dbo.tv_user AS target
    USING (SELECT @user_id AS id, @new_data AS data) AS source
    ON target.id = source.id
    WHEN MATCHED THEN
      UPDATE SET data = source.data, updated_at = GETUTCDATE(),
                 last_synced_at = GETUTCDATE(), sync_count = sync_count + 1, is_stale = 0
    WHEN NOT MATCHED THEN
      INSERT (id, data) VALUES (source.id, source.data);
  END
END;

Mutation Stored Procedures

FraiseQL calls mutation stored procedures by name, matching the sql_source parameter of the @fraiseql.mutation decorator. Procedures must follow the fn_{action}_{entity} naming convention.

-- File: 03311_create_user.sql
CREATE OR ALTER PROCEDURE dbo.fn_create_user
  @input_payload NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @identifier NVARCHAR(255) = JSON_VALUE(@input_payload, '$.identifier');
    DECLARE @name       NVARCHAR(255) = JSON_VALUE(@input_payload, '$.name');
    DECLARE @user_id    UNIQUEIDENTIFIER = NEWSEQUENTIALID();
    DECLARE @entity_data NVARCHAR(MAX);

    IF @identifier IS NULL OR @identifier = ''
    BEGIN
      SELECT JSON_QUERY('{"status":"failed:validation","message":"identifier is required"}') AS result;
      ROLLBACK; RETURN;
    END;

    -- Check for duplicate identifier
    IF EXISTS(SELECT 1 FROM dbo.tb_user WHERE identifier = @identifier AND deleted_at IS NULL)
    BEGIN
      SELECT '{"status":"conflict:duplicate_identifier","message":"Identifier already in use"}' AS result;
      ROLLBACK; RETURN;
    END;

    INSERT INTO dbo.tb_user (id, identifier, name)
    VALUES (@user_id, @identifier, @name);

    SELECT @entity_data = data FROM dbo.v_user WHERE id = @user_id;

    EXEC dbo.sync_tv_user_single @user_id;

    COMMIT;

    SELECT JSON_MODIFY(
      JSON_MODIFY('{"status":"success","message":"User created successfully"}',
        '$.entity_id', CAST(@user_id AS NVARCHAR(36))),
      '$.entity', JSON_QUERY(@entity_data)
    ) AS result;

  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    SELECT '{"status":"error","message":"Create user failed"}' AS result;
  END CATCH;
END;

Note

Security: All input examples use parameterized queries (@param placeholders) to prevent SQL injection. JSON values are extracted using JSON_VALUE() before SQL composition.

T-SQL as Business Logic

FraiseQL’s mutation model assumes that business logic lives in the database, not in application code. SQL Server’s T-SQL ecosystem is among the most mature for this pattern.

Debugging Stored Procedures

SSMS and Azure Data Studio both support stepping through stored procedures with breakpoints — something PostgreSQL’s plpgsql debugger requires an extension to do:

-- In SSMS: Debug > Step Into, set breakpoints on any line
-- In Azure Data Studio: use the "SQL Debugger" extension
-- Both support: variable watch, call stack, step over/into/out

CREATE OR ALTER PROCEDURE dbo.fn_create_order
  @input_payload NVARCHAR(MAX)
AS
BEGIN
  DECLARE @user_id UNIQUEIDENTIFIER = JSON_VALUE(@input_payload, '$.user_id');
  DECLARE @total DECIMAL(10,2) = JSON_VALUE(@input_payload, '$.total');

  -- Set breakpoint here in SSMS to inspect @user_id before the insert
  INSERT INTO dbo.tb_order (fk_user, total) VALUES (
    dbo.fn_get_pk_user(@user_id), @total
  );
  -- ...
END;

Teams migrating existing stored procedure libraries to FraiseQL do not need to rewrite debugging workflows — existing SSMS-based development practices carry over directly.

Service Broker for Async Side-Effects

SQL Server’s built-in Service Broker provides transactional async messaging for mutation side-effects (email delivery, downstream sync, audit writes) without an external queue:

-- Send a background task from within a mutation stored procedure
CREATE OR ALTER PROCEDURE dbo.fn_create_user
  @input_payload NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRANSACTION;

  -- ... insert user ...

  -- Queue a background notification (transactional — rolls back if insert fails)
  DECLARE @msg NVARCHAR(MAX) = JSON_MODIFY('{}', '$.user_id', CAST(@user_id AS NVARCHAR(36)));
  DECLARE @handle UNIQUEIDENTIFIER;
  BEGIN DIALOG CONVERSATION @handle
    FROM SERVICE [FraiseQLSender]
    TO SERVICE 'FraiseQLReceiver'
    ON CONTRACT [FraiseQLContract]
    WITH ENCRYPTION = OFF;
  SEND ON CONVERSATION @handle
    MESSAGE TYPE [FraiseQLEvent] (@msg);

  COMMIT;
  -- ...
END;

Unlike external queues (Redis, SQS, RabbitMQ), Service Broker messages are committed or rolled back with the surrounding transaction — no dual-write problem.

Temporal Tables for Audit Trails

SQL Server’s temporal tables (SYSTEM_TIME) automatically record row history as a first-class engine feature, complementing FraiseQL’s deleted_at soft-delete pattern:

-- Add system-time versioning to any table
ALTER TABLE dbo.tb_user
ADD
  valid_from DATETIMEOFFSET GENERATED ALWAYS AS ROW START NOT NULL
    CONSTRAINT df_user_valid_from DEFAULT SYSUTCDATETIME(),
  valid_to   DATETIMEOFFSET GENERATED ALWAYS AS ROW END   NOT NULL
    CONSTRAINT df_user_valid_to   DEFAULT CONVERT(DATETIMEOFFSET, '9999-12-31 23:59:59.9999999'),
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to);
ALTER TABLE dbo.tb_user SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tb_user_history));

-- Query point-in-time state from a view (expose via a va_* analytics view)
CREATE VIEW dbo.va_user_at_date AS
SELECT u.id, u.identifier, u.name, u.valid_from, u.valid_to
FROM dbo.tb_user FOR SYSTEM_TIME ALL AS u;

Temporal history is queryable through standard T-SQL — no application-layer event sourcing infrastructure required.

Enterprise Features

Always On Availability Groups

-- Connection string for read-write (primary)
-- Server=primary.ag.fraiseql.com;Database=fraiseql;...

-- Connection string for read-only (secondary)
-- Server=fraiseql-ag;ApplicationIntent=ReadOnly;...

Row-Level Security

-- Multi-tenancy via RLS
CREATE FUNCTION dbo.fn_security_predicate(@tenant_id UNIQUEIDENTIFIER)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS result
WHERE @tenant_id = CAST(SESSION_CONTEXT(N'tenant_id') AS UNIQUEIDENTIFIER);

CREATE SECURITY POLICY dbo.tenant_filter
ADD FILTER PREDICATE dbo.fn_security_predicate(tenant_id) ON dbo.tb_user
WITH (STATE = ON);

Transparent Data Encryption

-- Enable TDE for encryption at rest
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong-password';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

USE fraiseql;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE fraiseql SET ENCRYPTION ON;

Configuration and Performance

Connection String

mssql://user:password@host:1433/fraiseql?encrypt=true&trustServerCertificate=false

FraiseQL-Optimized SQL Server Configuration

-- Optimize for JSON workloads
ALTER DATABASE fraiseql SET COMPATIBILITY_LEVEL = 150;  -- SQL Server 2019+
ALTER DATABASE fraiseql SET QUERY_STORE = ON;
ALTER DATABASE fraiseql SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE fraiseql SET AUTO_CREATE_STATISTICS ON;

-- Memory optimization
-- Set max server memory to 75% of available RAM via SQL Server Configuration Manager

Troubleshooting

FOR JSON Returns NULL for Empty Subquery

-- Problem: No posts = NULL instead of []
-- Solution: COALESCE with empty JSON array string
SELECT
  u.id,
  COALESCE(
    (SELECT p.id, p.title FROM dbo.tb_post p WHERE p.fk_user = u.pk_user FOR JSON PATH),
    '[]'
  ) AS posts
FROM dbo.tb_user u WHERE u.id = @id;

JSON_VALUE Returns NULL

-- JSON_VALUE returns NULL for nested paths or arrays
-- Use JSON_QUERY for objects and arrays
SELECT
  JSON_VALUE(@json, '$.email') AS email,      -- scalar value
  JSON_QUERY(@json, '$.metadata') AS metadata  -- object or array

Indexed View Refresh Lag

Indexed views in SQL Server are automatically maintained. If you need manual refresh:

-- Force statistics update
UPDATE STATISTICS dbo.tv_user WITH FULLSCAN;

-- Rebuild indexes on the view
ALTER INDEX ALL ON dbo.tv_user REBUILD;

Performance Benchmarks

Query Type	Latency	Notes
Single entity (v_*)	0.5-2ms	Index lookup on id
List query (1000 rows)	10-40ms	With is_current filter
Nested JSON (5 levels)	30-150ms	Depends on FOR JSON depth
Materialized view (tv_*)	1-5ms	Pre-computed JSON
Analytics view (va_*)	100-1000ms	GROUP BY aggregation

Optimization Tips:

• Use NEWSEQUENTIALID() instead of NEWID() to avoid index fragmentation
• Enable Query Store to track and force good query plans
• Use columnstore indexes for analytics workloads
• Always On read replicas for reporting queries

Multi-Transport Support

FraiseQL generates transport-aware views for SQL Server:

• JSON-shaped views (GraphQL + REST) use FOR JSON PATH
• Row-shaped views (gRPC) use standard SELECT with typed columns

All three transports (GraphQL, REST, gRPC) are supported on SQL Server.

See Also

Database Overview

Compare all supported databases

SQL Server Enterprise Guide

Azure AD, Always On, TDE, and compliance patterns

PostgreSQL Guide

PostgreSQL patterns for FraiseQL

SQL Server Troubleshooting

SQL Server-specific troubleshooting