Skip to content

MSSQL

The MSSQL provider uses SQLAlchemy with pyodbc to talk to Microsoft SQL Server. It carries the same capability set as the PostgreSQL provider, except NATIVE_CLAIM, and is exercised by the same conformance tests.

Installation

The provider needs SQLAlchemy, pyodbc, and a Microsoft ODBC driver installed on the machine:

pip install "protean[mssql]"

pyodbc is a binding, not a driver. Install Microsoft ODBC Driver 18 for SQL Server separately through your operating system's package manager.

If pyodbc cannot be imported, domain.init() raises a ConfigurationError that names the extra to install. This covers a database_uri with pyodbc as its driver (mssql+pyodbc://) and one with no driver (mssql://). pyodbc installed without the unixODBC system library also fails to import, and domain.init() raises the same error. A database_uri that names another driver, such as mssql+pymssql://, goes to SQLAlchemy as written. So does a database config with a module key, which hands SQLAlchemy the DBAPI module to use in place of the URL's driver.

Configuration

[databases.default]
provider = "mssql"
database_uri = "mssql+pyodbc://sa:${MSSQL_PASSWORD}@localhost:1433/appdb?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&Encrypt=yes&MARS_Connection=yes"
Option Default Description
provider Required Must be "mssql"
database_uri Required pyodbc connection string, including the driver query parameter
schema dbo Schema that tables are created in
pool_size SQLAlchemy default Connections held open in the pool
max_overflow SQLAlchemy default Connections opened beyond pool_size under load

The driver query parameter is required: pyodbc has no default driver, and omitting it fails at connection time rather than at domain.init(). MARS_Connection=yes lets one connection hold several active result sets. The provider needs them when a repository issues one query in the middle of iterating another.

Capabilities

  • ✅ CRUD: Create, read, update, delete single records.
  • ✅ FILTER: Query and filter records with lookup criteria.
  • ✅ BULK_OPERATIONS: update_all() and delete_all().
  • ✅ ORDERING: Server-side ORDER BY.
  • ✅ TRANSACTIONS: Real commit and rollback atomicity.
  • ✅ OPTIMISTIC_LOCKING: Version-based concurrency control.
  • ✅ RAW_QUERIES: Execute raw SQL.
  • ✅ SCHEMA_MANAGEMENT: Create and drop tables.
  • ✅ CONNECTION_POOLING: SQLAlchemy pool management.
  • ✅ SCHEMA_MIGRATIONS: Versioned migrations through Alembic.
  • ✅ NATIVE_JSON and NATIVE_ARRAY: Dict and List fields are queryable. SQL Server has no JSON column type, so the provider stores them through a JSON type backed by NVARCHAR.

Indexes

MSSQL supports some of the Index options, and protean db setup creates the indexes:

  • Composite, descending (desc=), and unique (unique=) indexes.
  • Covering columns (include=), which map to SQL Server's INCLUDE clause.
  • Partial indexes (where=Q(...)) are not supported. The index is created without the predicate and a warning is logged.

String columns used as keys

SQL Server rejects an unbounded VARCHAR in a primary key or a unique constraint. Protean catches this when it generates the schema and raises IncorrectUsageError with the field's name. Without this check, the database fails with its own message about a column being invalid as a key:

@domain.aggregate
class User:
    email: String(max_length=255, unique=True)  # length is required here
    bio: String(max_length=None)  # fine, not a key column


@domain.aggregate
class Coupon:
    code: String(max_length=None, unique=True)  # raises: a key column with no length

String() has a default max_length of 255, so it is a valid key column. Protean raises IncorrectUsageError for a column with no length, such as max_length=None. A column with no length is fine on MSSQL as an ordinary column. Protean raises only when that column is a primary key or is unique=True.

Date and time columns

Field Column
DateTime DATETIME2
Date DATE

SQL Server's DATETIME rounds to increments of about 3 milliseconds: the last digit of the milliseconds becomes 0, 3 or 7, so .125 is stored as .127. Protean writes microsecond-precision timestamps, so two values written less than 3 ms apart could read back equal, and a value read back could differ from the one written. DATETIME2 keeps 100-nanosecond steps, so every DateTime column Protean generates uses it.

A table created before 0.19 keeps its DATETIME columns, because Protean does not change an existing table. See SQL Server stores DateTime as DATETIME2 for the statements that convert them.

Because a DATETIME2 column holds no UTC offset, Protean converts each value to UTC on write and in filter comparisons. Protean reads a naive value as UTC. A load returns a UTC-aware datetime. A filter value in any offset then matches the same instant. Rows written before 0.18.1 hold the wall-clock time they were saved with; see Datetimes are compared and stored as UTC to convert them.

SQLAlchemy model

You can supply a custom SQLAlchemy model in place of the one Protean generates, which gives you control over column types and constraints. The pattern is the same as for PostgreSQL.

# fragment
import os

import sqlalchemy as sa
from sqlalchemy.dialects import mssql

from protean import Domain
from protean.fields import String

domain = Domain(name="Accounts")
domain.config["databases"]["default"] = {
    "provider": "mssql",
    "database_uri": os.environ.get(
        "MSSQL_URL",
        "mssql+pyodbc://sa:Protean123!@localhost:1433/master"
        "?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
    ),
}


@domain.aggregate
class User:
    name: String(max_length=100)
    email: String(max_length=255)


@domain.database_model(part_of=User)
class UserModel:
    name = sa.Column(mssql.NVARCHAR(100))
    email = sa.Column(mssql.NVARCHAR(255), unique=True)


domain.init(traverse=False)

Note

Column names in the model must match the attribute names of the aggregate or entity they represent.

A column you declare in the model keeps the type you give it. Declare a timestamp column as DATETIME2 (sqlalchemy.dialects.mssql.DATETIME2) to keep microseconds.

Slow query detection

The provider emits the same structured protean.adapters.repository.sqlalchemy.slow_query WARNING and protean.adapters.repository.sqlalchemy.query DEBUG events as the PostgreSQL provider. Set the threshold with [logging].slow_query_threshold_ms in domain.toml.