sqlalchemy-azure-sql

# SQLAlchemy on Azure SQL Database SQLAlchemy reaches Azure SQL Database through a dialect whose generated SQL differs from every other backend in one specific way, and almost every surprise here traces back to it. Every Python block below was verified on 2026-09-03 against SQLAlchemy 2.0.52 and pyodbc 5.3.0 and its output quoted verbatim; the version boundary below was measured the same day against 2.0.8, 2.0.9 and 2.0.10. Engine error numbers are from Microsoft Learn. The dialect is named `mssql` and that name belongs only in the URL scheme. Everywhere else the product is **Azure SQL Database**, and an agent that adopts the dialect name has begun giving advice for a different product with different limits. ## The engine URL ```python import os from sqlalchemy import create_engine from sqlalchemy.engine import URL credential = { # read them, never inline them "username": os.environ["SQL_USER"], "password": os.environ["SQL_PASSWORD"], } url = URL.create( "mssql+pyodbc", host=os.environ["SQL_HOST"], # <server-name>.database.windows.net port=1433, database=os.environ["SQL_DATABASE"], query={"driver": "ODBC Driver 18 for SQL Server"}, **credential, ) engine = create_engine(url, pool_pre_ping=True) print(url.render_as_string(hide_password=True)) ``` `URL.create` escapes the characters that break a hand built URL; `render_as_string` logs one with the password back as `***`. Driver choice, retry and passwordless connections belong to other skills; see References. ## The OUTPUT clause, which is the whole skill To learn a server generated key the dialect adds an OUTPUT clause to the INSERT. Do not take that on trust, compile it: ```python from sqlalchemy import Column, Integer, String, insert from sqlalchemy.dialects import mssql from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): # columns shared by both models id = Column(Integer, primary_key=True) sku = Column(String(20)) class Order(Base): __tablename__ = "orders" class Audited(Base): # this one carries a trigger __tablename__ = "audited_orders" __table_args__ = {"implicit_returning": False} # so: no OUTPUT clause d = mssql.dialect() print(insert(Order).values(sku="x").compile(dialect=d)) print(insert(Audited).values(sku="x").compile(dialect=d)) print(insert(Audited).values(sku="x").returning(Audited.id).compile(dialect=d)) ``` Three lines out, and every claim below is one: ``` INSERT INTO orders (sku) OUTPUT inserted.id VALUES (:sku) INSERT INTO audited_orders (sku) VALUES (:sku) INSERT INTO audited_orders (sku) OUTPUT inserted.id VALUES (:sku) ``` Azure SQL Database refuses the first when the target carries an enabled trigger: ``` Msg 334, Level 15, State 1 The target table 'dbo.audited_orders' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. ``` Four properties decide how this plays out, and each is where agents guess wrong. 1. **It is per table, and nothing detects it.** The dialect has no trigger reflection and does not probe for one, so a codebase where 40 models work and one fails is the normal shape of the bug. 2. **The switch is on the table, not the engine**, as `__table_args__` above or `implicit_returning=False` passed to `Table` in Core. The key then comes back from `scope_identity()`, one extra round trip that works on a triggered table. Set it per table, not everywhere as a precaution: it costs the batched insert path on tables that never needed it. 3. **The engine level parameter is a trap.** `create_engine(url, implicit_returning=True)` and the `False` form are both accepted, both raise `SADeprecationWarning`, and neither changes one character of the SQL above. 4. **The flag does not cover an explicit `returning()`.** That is the third printed line: with the flag off, a hand written `insert().returning(...)` still compiles to OUTPUT without INTO, and `update().returning(...)` and `delete().returning(...)` do the same. Triggers cluster on auditing and history tables, exactly where an ORM meets an existing schema. Find them before mapping one, with the query under Check it worked. ## fast_executemany, and what changed in 2.0 **Same cause as the trigger error above: the generated returning clause.** An ORM insert of a mapped class with a server generated key carries one, because that is the OUTPUT clause, and that is the case the flag does not reach, so by default it changes nothing for exactly the bulk inserts people set it for. `fast_executemany=True` has existed since SQLAlchemy 1.3; what changed is what it does, and the boundary is measurable with no database: ```bash python3 -c "import sqlalchemy; from sqlalchemy.dialects import mssql; \ print(sqlalchemy.__version__, mssql.dialect().use_insertmanyvalues)" ``` That prints `2.0.8 True`, `2.0.9 False`, `2.0.10 True`. SQLAlchemy 2.0 introduced **insertmanyvalues**, a batched INSERT form that returns keys, and because it returns keys it took precedence over the driver's array binding and the flag stopped having any effect. 2.0.9 gave that effect back for multi-parameter INSERT statements **carrying no returning clause**, switching insertmanyvalues off here entirely to do it; 2.0.10 restored it, so **2.0.10 is the floor**. So the flag bites only where the INSERT carries no returning clause: the rows already hold their keys, or the table is declared `implicit_returning=False`, or the load goes through Core rather than tracked ORM objects. Documented costs make this a decision, not a default: the batch must **fit in memory**, the parameter is honoured for the Microsoft ODBC driver only, and `setinputsizes` is **not used** there, which is where the surprising type handling on large loads comes from. ## Type mapping, before the first migration ```python from sqlalchemy import Column, MetaData, String, Table, Text, Unicode, UnicodeText from sqlalchemy.dialects import mssql from sqlalchemy.schema import CreateTable t = Table("t", MetaData(), Column("a", String(50)), Column("b", Unicode(50)), Column("c", Text()), Column("d", UnicodeText())) print(CreateTable(t).compile(dialect=mssql.dialect())) # not connected print(CreateTable(t).compile(dialect=mssql.dialect(deprecate_large_types=True))) # connected ``` The second print is what the ORM creates: ``` a VARCHAR(50) NULL, b NVARCHAR(50) NULL, c VARCHAR(max) NULL, d NVARCHAR(max) NULL ``` `String` is **not** Unicode. A model that uses it for names, addresses or user text gets a non-Unicode column, and the mismatch between it and the Unicode parameter Python binds is the implicit conversion that stops an index being used. Prefer `Unicode` or `NVARCHAR(n)` explicitly. That the two prints differ is the second trap. Offline, `deprecate_large_types` is `None` and `c` and `d` compile to `TEXT` and `NTEXT`; on first connect the dialect sets it from the server version, and Azure SQL Database is always past the threshold. Compiling offline is the normal way to inspect DDL and wrong for exactly this pair, so never report a `TEXT` or `NTEXT` read out of a compiled statement as what will be created. One default worth pinning: `Sequence("s")` with no `start` compiles to a bare `CREATE SEQUENCE s`, and Microsoft Learn gives an ascending sequence's default start as the minimum value of its type, `-9223372036854775808` for the default `bigint`. Pass `start=1`, and prefer an identity column for surrogate keys. ## Alembic Alembic drives the same dialect, so all the above applies to the DDL it emits. Read that DDL before it runs, which is the point of offline mode: ```bash alembic revision --autogenerate -m "add audited_orders" alembic upgrade head --sql > migration.sql # writes SQL, runs nothing grep -nE 'TEXT|NTEXT|VARCHAR\(' migration.sql # the type mapping above, before it ships alembic upgrade head ``` Three operations need arguments here that they need nowhere else, and autogenerate will not add them: - `alter_column` requires `existing_type` when changing nullability. - `drop_index` requires `table_name`. - `drop_column` on a column with a DEFAULT, CHECK or single foreign key constraint takes `mssql_drop_default`, `mssql_drop_check` or `mssql_drop_foreign_key` to drop the unnamed constraint first. These come from the Alembic documentation, not from a run: Alembic was not installed on the machine that produced the numbers above. Whether a migration is safe to run at all, expand and contract, and never migrating on startup, are `schema-migrations-safely`. ## Check it worked Three checks, in the order they catch things. **Which tables carry an enabled trigger** is first, because nothing in Python will tell you: ```bash sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -b -m-1 -Q \ "SELECT OBJECT_SCHEMA_NAME(parent_id) + '.' + OBJECT_NAME(parent_id) AS triggered_table FROM sys.triggers WHERE parent_class = 1 AND is_disabled = 0 ORDER BY 1;" ``` Expect one row per table needing `implicit_returning=False`, none on a schema with no triggers. Keep `-m-1`: a severity 10 message otherwise prints with no `Msg` number, and `-b` will not fail on one. **`-m-1` is an ODBC `sqlcmd` instruction**, meaning the 18.x build from `mssql-tools18` or the Microsoft command line utilities. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x build `brew install sqlcmd` and `winget install sqlcmd` install, prints no `Msg` header on a severity 10 message at any `-m` value, so on that build a severity 10 message prints with no number whether you keep the flag or not. Query `sys.messages` or use the ODBC build when the number matters. **Every model that needs the flag has it.** Print the other list and compare: ```python print(sorted(t.name for t in Base.metadata.tables.values() if t.implicit_returning)) ``` `Table.implicit_returning` is `True` by default and `False` once set, so expect no name from the `sqlcmd` output here. Any that appears is the next `Msg 334`. **The columns the engine actually created.** Run this against the migrated database: ```sql SELECT OBJECT_NAME(c.object_id) AS tbl, c.name AS col, t.name AS type_name FROM sys.columns AS c JOIN sys.types AS t ON t.user_type_id = c.user_type_id WHERE OBJECTPROPERTY(c.object_id, 'IsUserTable') = 1 AND t.name IN ('varchar', 'char', 'text', 'ntext'); ``` Expect zero rows anywhere text can hold a non-ASCII character. A `varchar` or `char` row is a `String` column, a `text` or `ntext` row is DDL from a dialect that never connected, and the engine accepts both without complaint, which is why nothing else catches them. ## Do not - Do not claim `fast_executemany=True` sped up an ORM bulk insert without first checking that the statement carries no returning clause. On the default configuration it did not. - Do not inline a credential in an engine URL, and do not call the product `mssql`. ## References - [SQLAlchemy Microsoft SQL Server dialect](https://docs.sqlalchemy.org/en/20/dialects/mssql.html): triggers, identity, sequences, fast_executemany, setinputsizes. Fetch it before asserting what the dialect emits. - [insertmanyvalues](https://docs.sqlalchemy.org/en/20/core/connections.html#engine-insertmanyvalues): what the batched form generates and how to turn it off. Read it when a bulk load is slow or noisy. - [The OUTPUT clause](https://learn.microsoft.com/sql/t-sql/queries/output-clause-transact-sql): the restriction in the engine's own words, per DML action. Read it to confirm the rule, not the text. - [Alembic operations reference](https://alembic.sqlalchemy.org/en/latest/ops.html): the dialect-specific arguments above. Read it while writing a migration that alters or drops a column. - `connect-from-python`: driver choice, installation, pooling. Open it before writing the URL; the first-party driver has a dialect only from SQLAlchemy 2.1.0b2, not for production. - `connect-to-azure-sql`: encryption, retry, pool sizing. Open it the moment a transient error appears: `pool_pre_ping` tests a connection at checkout and is not a retry policy. - `entra-id-auth`: passwordless connections, which carry no user, no password and no `Trusted_Connection`. Open it before putting a token flow near the engine URL. - `design-azure-sql-schema`: key length, sizing, collation. Open it before the first migration. - `schema-migrations-safely`: the migration doctrine Alembic inherits, including why a pipeline can report success on a migration that only warned.