Documentation / Connectors

Microsoft SQL Server

Connect Microsoft SQL Server to Nexus over ODBC for full read, write, and merge, with T-SQL filter, aggregation, join, and pagination pushed down to the database engine. Views are discovered read-only and blocked for writes.

The Microsoft SQL Server connector (bundle version 2.0.5) lets you query, write, and explore SQL Server databases from Nexus using the T-SQL dialect. It is a full-pushdown SQL adapter: filtering, aggregation, joins, distinct, ordering, and pagination all execute on SQL Server rather than in memory, and the complete write surface — insert, update, delete, and MERGE upsert — is available. It suits any reachable SQL Server instance: on-premises, Azure SQL Database, or Azure SQL Managed Instance.

This page is for platform and data engineers configuring an MSSQL bridge. It covers connection fields and TLS, the exact pushdown and operator surface, T-SQL dialect specifics, the write-and-merge model, the read-only handling of views, and behavioral limits.

Capabilities

Capability	Supported
Read	Yes — `SELECT` with filter, aggregation, join, distinct, and `ORDER BY` pushdown
Insert	Yes
Update	Yes
Delete	Yes
Merge (upsert)	Yes — T-SQL `MERGE ... USING (VALUES ...)`
Schema discovery	Yes — databases, tables, views, columns (incl. identity/computed flags)
Filter pushdown	Full
Aggregation pushdown	Yes — `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`, `GROUP BY`, `HAVING`
Join / distinct / select-expression pushdown	Yes

Because MSSQL declares full pushdown and its operators are all pushable, the Nexus engine sends your WHERE, aggregation, ordering, and LIMIT/OFFSET straight to the database and does not re-filter returned rows in memory (the ADR-0058 rule described in Operators & pushdown). The full analytical shape of a report executes on SQL Server.

Connection fields

Configure the following when adding an MSSQL bridge. Secrets live in your Azure Key Vault; no credential is inlined in Nexus configuration.

Field	Required	Default	Notes
`host`	Yes	—	Hostname or IP of the SQL Server instance
`port`	No	`1433`	Standard SQL Server port
`username`	Yes	—	SQL Server login
`password`	Yes	—	SQL Server password
`database`	No	Server default	Target database name
`tls_mode`	No	`require`	See TLS section

A pre-assembled ODBC connection string can be supplied instead of discrete fields if your environment requires it. See Connections & bridges and Secrets.

TLS

TLS certificate validation is on by default. The default tls_mode of require enforces an encrypted connection and validates the server certificate. Disabling validation or encryption is an explicit, named opt-out — Nexus never silently skips certificate checks.

`tls_mode` value	Behavior
`require`	TLS enforced; certificate validated (default)
`prefer`	TLS without certificate validation (use only in trusted private networks)
`disable`	No encryption

Filter operators

The full standard operator set is available for pushdown:

$eq $ne $gt $gte $lt $lte $like $in $not_in $between $empty $contains $starts_with $ends_with $expr

$expr accepts a raw T-SQL fragment and is a trusted-author surface — it passes your expression to the database without additional escaping. Use it for predicates the standard operators cannot express, and bound authorship with execution roles.

T-SQL dialect specifics

Identifier quoting — object names are wrapped in [square brackets], e.g. [dbo].[Orders].
Pagination — uses OFFSET … FETCH NEXT … ROWS ONLY. SQL Server requires an ORDER BY when paging; if you do not specify one, the connector injects ORDER BY (SELECT NULL) automatically to satisfy the engine.
Upsert — uses T-SQL MERGE … USING (VALUES …) AS source ON … WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT.

Write operations and merge

Operation	Behavior
Insert	Parameterized `INSERT INTO ... VALUES (...)`.
Update	`UPDATE ... SET ... WHERE ...` with the fields and filter you specify.
Delete	`DELETE FROM ... WHERE ...`.
Merge (upsert)	Single `MERGE` statement keyed on the conflict columns you name; matched rows update, unmatched rows insert.

Views are read-only

Discovery flags views (table_type = 'VIEW') as read-only, and any write targeting a view is rejected before a query is sent. This writability gate is a per-adapter control layered beneath tenant RBAC: even a caller with write grants cannot write through a view. Identity and computed columns are likewise surfaced as read-only in discovery.

Schema discovery

When you discover an MSSQL bridge, Nexus queries the system catalog and returns:

Databases — all user databases (system databases excluded automatically).
Tables and views — from INFORMATION_SCHEMA.TABLES; views flagged read-only.
Columns — from INFORMATION_SCHEMA.COLUMNS, including data type, nullability, identity flag, and computed-column flag. Identity and computed columns are surfaced as read-only.

Discovery gives you the full schema of your instance without writing any queries manually.

Example: read then stage in a pipeline

async def run(ctx):
    rows = await ctx.adapters.sales_db.read({
        "collection": "dbo.Orders",
        "where": {"status": {"$eq": "open"}},
        "order_by": [{"field": "created_at", "direction": "desc"}],
        "limit": 5000,
    })
    await ctx.workspace.upsert("open_orders", rows)
    ctx.logger.info("staged %d orders", len(rows["rows"]))

sales_db is the bridge name you assign when configuring the connection. The where clause pushes to a T-SQL WHERE; order_by and limit render as ORDER BY … OFFSET 0 FETCH NEXT 5000 ROWS ONLY.

A write-back merge from staged data:

async def run(ctx):
    staged = await ctx.workspace.read("dim_customer_updates")
    await ctx.adapters.sales_db.write({
        "collection": "dbo.Customer",
        "operation": "merge",
        "key_columns": ["CustomerId"],
        "records": staged.rows,
    })

Limits & behavior notes

Views cannot be written. Writes to a view fail fast with a permission-style error.
Paging needs an order. Without an explicit order_by, a paged query gets a synthetic ORDER BY (SELECT NULL); supply your own order for deterministic results.
$expr is unescaped. Treat it as trusted-author only.
Connection budget. Per-replica DB connections are bounded by the bridge connection governor; orphaned connections are reaped.
Long queries are bounded by the run timeout. A heavy query in a pipeline is bounded by the per-run wall-clock timeout.

ODBC driver

The connector uses ODBC Driver 18 for SQL Server by default (included in the Nexus container image). If your environment requires a different driver version, override it via the connection string field.