GitHub

SQL Server

A pooled SQL Server client: typed parameters, OUTPUT clauses, stored procedures with output parameters, errors read from SQL Server's error numbers, and migrations.

pyfect.sql.mssql speaks SQL Server's protocol through python-tds; install the pyfect-dev[mssql] extra. mssql.layer provides both MssqlClient and SqlClient, so the code from the SQL guide runs unchanged on SQL Server.

The snippet on this page reads the server, database and password from the environment:

MSSQL_SERVER=localhost MSSQL_DATABASE=app MSSQL_PASSWORD=... python connect.py

Connecting

MssqlClientConfig holds the server and either its port (1433 by default) or the instance_name of a named instance, the database, username and password (a Redacted, so it never shows up in logs), and the pool's size and connection lifetime. Connections are encrypted with TLS unless encrypt=False; trust_server=True accepts a certificate the machine doesn't trust, as a development server's. To read the settings from the environment, use mssql.layer_config({"server": fx.config.string("MSSQL_SERVER"), ...}).

Each statement runs through sp_executesql with typed parameters: str as NVARCHAR, int as BIGINT, float as FLOAT, bytes as VARBINARY, and so on (parameter_types changes the mapping). sql.param(TYPES.Int, 1) sends one value as a type of your choice. A few things work differently than on PostgreSQL:

  • returning(...) on the insert and update helpers becomes an OUTPUT INSERTED.* clause, so a statement that changes rows gives them back. A model repository's insert and update use it.
  • A failure's reason comes from SQL Server's error number: UniqueViolation (with the constraint or index name as constraint), DeadlockError, LockTimeoutError, AuthenticationError, ... A login that fails when the layer is built (a wrong password) fails the layer with ConnectionError, whose cause is the SqlError with the AuthenticationError, as Effect's layer wraps its first connection's error.
  • SQL Server has already rolled a deadlock victim's transaction back, so inside with_transaction the ROLLBACK fails too and the transaction dies with the DeadlockError as a defect, as in Effect: retry a deadlocked transaction on its defect (sandbox or catch_cause), not with catch_reason.
  • Interrupting a statement cancels it on the server; the connection is reused.
  • Streaming rows (sql(...).stream) isn't supported, as in Effect's client.

Stored procedures

A Parameter names a procedure parameter and its SQL type. procedure.make(name) builds a procedure from its input (param) and output (output_param) parameters; compile takes a value for each input, and sql.call(...) runs it. The result's output is read by the Parameter, typed by it, and its rows are those the procedure selected.

connect.py
import os

import pyfect as fx
from pyfect import redacted
from pyfect.sql import mssql
from pyfect.sql.error import UniqueViolation
from pyfect.sql.mssql import TYPES, Parameter, procedure

# MSSQL_SERVER=localhost MSSQL_PASSWORD=... MSSQL_DATABASE=app python connect.py
config = mssql.MssqlClientConfig(
    server=os.environ["MSSQL_SERVER"],
    port=int(os.environ.get("MSSQL_PORT", "1433")),
    database=os.environ["MSSQL_DATABASE"],
    username="sa",
    password=redacted.make(os.environ["MSSQL_PASSWORD"]),
    trust_server=True,
)

user_id = Parameter[int]("user_id", TYPES.Int)
email = Parameter[str]("email", TYPES.NVarChar)
email_of = procedure.make("email_of").param(user_id).output_param(email)


@fx.fn
def program():
    sql = yield from mssql.MssqlClient()
    yield from sql(
        "CREATE TABLE users (id INT IDENTITY PRIMARY KEY, "
        "email NVARCHAR(191) CONSTRAINT users_email UNIQUE)"
    )

    # returning(...) becomes SQL Server's OUTPUT clause.
    rows = yield from sql(
        "INSERT INTO users {}", sql.insert({"email": "ada@example.com"}).returning("*")
    )
    print(rows)

    # A failed statement's SqlError says why, read from SQL Server's error number.
    error = yield from sql("INSERT INTO users (email) VALUES ({})", "ada@example.com").flip()
    assert isinstance(error.reason, UniqueViolation)
    print(error.reason.constraint)

    # A stored procedure's output parameters come back typed by their Parameter.
    yield from sql(
        "CREATE PROCEDURE email_of @user_id INT, @email NVARCHAR(191) OUTPUT AS "
        "SELECT @email = email FROM users WHERE id = @user_id"
    )
    result = yield from sql.call(email_of.compile(user_id(1)))
    print(result.output[email])

    # A transaction rolls back when its effect fails.
    insert = sql("INSERT INTO users (email) VALUES ({})", "grace@example.com")
    yield from sql.with_transaction(insert.and_then(fx.fail("changed my mind"))).ignore()
    print((yield from sql("SELECT email FROM users ORDER BY id")))


fx.run_main(program().pipe(fx.provide(mssql.layer(config))))

Transactions

sql.with_transaction begins a transaction at READ COMMITTED, and nested calls use savepoints (SAVE TRANSACTION). SQL Server has no statement to release a savepoint, so a nested transaction that succeeds leaves its savepoint until the outer one ends.

Errors that end the transaction

Some errors make SQL Server roll the whole transaction back before the client hears of them: anything raised while XACT_ABORT is ON, a deadlock, and a few severe errors. with_transaction then sends its own ROLLBACK, which fails with error 3903 ("no corresponding BEGIN TRANSACTION"). A failed rollback is a defect, so the transaction dies with both errors, and catch_tag(SqlError) or catch_reason never sees them. Effect's SQL Server client does the same.

A stored procedure that rejects a request with THROW under SET XACT_ABORT ON is the common case. Either let the procedure own its transaction and call it outside with_transaction, where the THROW is an ordinary SqlError, or recover the defect with catch_cause (or sandbox). Either way the reason is an UnknownError for a number of your own, and mssql.error.error_number(error.reason.cause) reads it.

xact_abort.py
import os

import pyfect as fx
from pyfect import redacted
from pyfect.sql import mssql
from pyfect.sql.error import SqlError
from pyfect.sql.mssql.error import error_number

config = mssql.MssqlClientConfig(
    server=os.environ["MSSQL_SERVER"],
    port=int(os.environ.get("MSSQL_PORT", "1433")),
    database=os.environ["MSSQL_DATABASE"],
    username="sa",
    password=redacted.make(os.environ["MSSQL_PASSWORD"]),
    trust_server=True,
)


def thrown(cause: fx.Cause[SqlError]) -> int | None:
    """The error number a procedure threw, whether the effect failed or died with it."""
    error = cause.squash()
    return error_number(error.reason.cause) if isinstance(error, SqlError) else None


@fx.fn
def program():
    sql = yield from mssql.MssqlClient()
    yield from sql("CREATE TABLE entries (id INT IDENTITY PRIMARY KEY, amount INT)")
    yield from sql(
        "CREATE PROCEDURE post_entry @amount INT AS BEGIN SET XACT_ABORT ON;"
        " IF @amount > 100 THROW 50001, 'overdrawn', 1;"
        " INSERT INTO entries (amount) VALUES (@amount); END"
    )
    post = sql("EXEC post_entry @amount = {}", 500)

    # On its own, the THROW is a failure, and its number is on the reason's cause.
    error = yield from post.flip()
    print(type(error.reason).__name__, error_number(error.reason.cause))

    # In a transaction, XACT_ABORT has rolled it back before the client hears of the THROW,
    # so with_transaction's ROLLBACK fails (3903) and the transaction dies with both.
    failed = yield from sql.with_transaction(post).exit()
    assert isinstance(failed, fx.Failure)
    print([type(reason).__name__ for reason in failed.cause.reasons])
    defects = [d for d in failed.cause.defects if isinstance(d, SqlError)]
    print([error_number(defect.reason.cause) for defect in defects])

    # catch_tag(SqlError) can't see a defect; catch_cause can, and so finds the number.
    number = yield from sql.with_transaction(post).catch_cause(lambda c: fx.succeed(thrown(c)))
    print(number)


fx.run_main(program().pipe(fx.provide(mssql.layer(config))))

Migrations

mssql.migrator works like the SQLite migrator: migrator.layer runs the migrations when the layer is built, recording them in effect_sql_migrations.