An open connection to a server. It belongs to one thread at a time; Hmssql.Connection has the rules.

How to connect. Start from defaultConnectInfo and change the fields that differ. Textual settings are Text, the host, port and instance included.

Azure SQL Database accepts both the default EncryptOn, which is TDS 7.4, and EncryptStrict, which is TDS 8.0. The server name in the login defaults to connectHost. Under Azure's Default connection policy, a connection from inside Azure through the public endpoint is redirected to the node that hosts the database, on a port from 11000 to 11999, and connect follows the redirect, so those outbound ports must be open.

azure = defaultConnectInfo
  { connectHost = "myserver.database.windows.net"
  , connectUser = "your_login"
  , connectPassword = "..."
  , connectDatabase = "your_database"
  }

To log in with Entra ID instead of a SQL login, put an access token in connectAccessToken and leave the user and password empty.

The defaults, which the example at the top of Hmssql starts from:

• localhost on port 1433, over ProtocolAuto, to the default instance.
• No user, password, access token or database.
• EncryptOn with defaultTlsSettings: the whole session is encrypted, and the server's certificate is validated against the system store.
• 8000-byte packets, no session setup and no MARS.

What a connection travels on (connectProtocol).

Shared Memory and Named Pipes exist on Windows only; asking for one elsewhere makes connect throw. Both carry the same bytes as TCP, so encryption, logins and everything after work the same. On Windows under GHC's native I/O manager (+RTS --io-manager=native), TCP goes through network's own calls and the pipes through the manager when hmssql was built against network 3.3 or later with a GHC whose native manager network supports, with or without -threaded. Without -threaded, network's blocking calls (the lookup, the connect, and a send once the server stops reading) and the open of a pipe stop every Haskell thread while they block. A build that can't use the native manager needs -threaded to run under it.

Open a connection with connect, run the action with it, and close it afterward, whether the action returns or throws.

Open a connection and log in.

If the server can't be reached, this throws ConnectionClosedBy (TransportFailed e) with the underlying IOException. When ProtocolAuto tried several protocols, the exception says why each one failed. There is no built-in connect timeout, so wrap the call in timeout where one is needed. A host with several addresses gives each but the last 4 seconds to answer before the next is tried, so a timeout of 15 seconds still reaches a working address behind a silent one.

A login the server redirects, as Azure SQL does from inside Azure and an availability group listener does, is sent again to the host and port named, over TCP and with the same settings otherwise, up to ten times in a row. The eleventh redirect throws LoginRedirected, and so does a redirect received in plaintext under EncryptOff.

Close the connection. It is safe to call from any thread, and more than once, through any handle to the connection, a released one included. A request in progress on another thread is interrupted and fails with ConnectionClosedBy ClosedByClient, as does any later use of the connection.

The TLS goodbye can block if the server doesn't read. It can be interrupted, and calling close again completes it. A closed connection keeps under a kilobyte alive, so one that a pool still references costs nothing.

Reset the connection so that it can be returned to a connection pool. Return False if the connection is not usable, because it was closed, it is broken, or the reset itself failed, and so a later request could not use it; that means it should be closed instead of returned to the pool.

The reset does two things:

• It cancels the active query, as cancelActiveStatement does, so that a stream that wasn't read to its end doesn't leave the connection busy. On a MARS connection, every open stream is canceled.
• It marks the next request with the RESETCONNECTION bit, so that the server resets the session before running that request. That costs no round trip now. However, if release is called in the middle of a transaction, it resets the connection immediately instead, so that the transaction's locks are released before the connection idles in the pool.

Nothing is sent to probe the server. A session the server dropped without closing the socket is found by the next request, not here.

The handle given is released whether the cleanup succeeded or not. Further requests through it, and further reads from its streams, throw ConnectionReleased. close, renew and read-only inspection still work through it, and the next borrower gets a new handle from renew.

Like a request, release waits while another thread holds the connection, and it can be interrupted. An interrupted release leaves the connection broken, so a pool can wrap it in a timeout and treat Nothing as False:

ok <- fromMaybe False <$> timeout 5000000 (release conn)

Calling release inside withTransaction is not supported. Hmssql.Connection has a complete recipe for the resource-pool package.

Issue a new handle to the connection, for the next borrower from a pool, and make every earlier handle stale. A stale handle throws ConnectionReleased, so a borrower that kept its handle past release can't use the connection under the next borrower's feet. The connection itself is shared, and only the handle is new. Like a request, renew waits while another thread holds the connection. It sends nothing. The handle connect returns needs no renew.

A connection known to be unusable is refused with the exception a request would throw, ConnectionClosed or ConnectionBroken. The usual case is a session the server ended while the connection idled in the pool. The earlier handles are then left as they were, so close the connection and borrow another. unusableReason says what is known without throwing.

Whether the connection is known to be unusable, and why: it was closed, an earlier failure broke it, or the server ended it while it idled. Nothing means that no failure has been seen, not that the connection is healthy. Nothing is sent to probe the server, so a session the server dropped without closing the socket is found by the next request. Like a request, it waits while another thread holds the connection.

It describes the connection, not the handle. A released handle to a healthy connection gives Nothing, and a request through that handle still throws ConnectionReleased.

How much of the connection is encrypted (connectEncryption). The first four are the values of the ENCRYPTION option of PRELOGIN, where the client and the server negotiate. EncryptStrict is TDS 8.0, which never negotiates.

How the server's certificate is validated, in every mode that uses TLS. defaultTlsSettings validates against the system store; the fields add trust, override the name checked, or turn validation off.

Validate the server's certificate against the system store, for the host that was connected to, with no extra certificates trusted.

SQL text together with the parameters it refers to. Build one from a string literal, with mkQuery, or from pieces joined with <>; Hmssql.Query shows each way.

The positional parameters are sent as @p1, @p2, ... in the order queryParams lists them, and the named parameters by name. <> appends the text and the parameters of two queries and renumbers the right operand's placeholders past the left operand's positional parameters, so any two queries can be joined. A placeholder that refers to no parameter makes sending the query throw EncodeError.

A query with no parameters is sent as a plain SQL batch, and one with parameters through sp_executesql. fromString (OverloadedStrings) is text as it is, with no parameters. Show is the text as it would be sent, without the parameter values.

SQL text with its parameters. The parameters are those of the ToSqlRow value: a tuple, a record with a derived instance, an Only for a single parameter, a list of SqlParam, or NamedParams. In the text, @p1, @p2, ... refer to the positional parameters and @name to the named ones.

mkQuery "UPDATE dbo.customer SET email = @p1 WHERE id = @p2" (email, customerId)

@pN, in either case, is a placeholder wherever it appears outside a string literal, a quoted identifier or a comment, even where the SQL means something else by it. So a variable of that name, as in DECLARE @p1 int, can't be used in a mkQuery text; use another name. A placeholder that refers to no parameter makes sending the query throw EncodeError.

A string literal (OverloadedStrings) is the same as mkQuery text () for text without placeholders.

The text is lexed once per application of mkQuery to it, not once per set of parameter values. With a literal, the lexing floats out to the top level when mkQuery is inlined, so the text is lexed once per program run however many times the query is built with new parameters.

A positional parameter: a placeholder in the text, and the value sent with the query.

"WHERE id = " <> param (42 :: Int)

A named parameter: @name in the text, and the value sent with the query. The leading @ is optional. The name must be a T-SQL variable name (see paramNameProblem), and not of the form @pN, which is reserved for the positional parameters. A query that uses one name for two parameters can't be sent, and throws EncodeError.

"OFFSET " <> namedParam "skip" (100 :: Int) <> " ROWS"

An identifier (a table or column name from data, say) quoted with brackets: [name], with ] doubled.

"SELECT " <> escapeIdentifier col <> " FROM " <> escapeIdentifier table

Text spliced into the query as it is, with no parameters; a string literal gives the same. Nothing in it is parsed, so a @p1 in it is left to the server. For text that comes from data, use param, namedParam or escapeIdentifier instead.

The SQL text as it is sent, with @pN for the placeholders.

A placeholder next to a character that could continue a variable name gets a space between them: "x = " <> param 1 <> "1" is sent as x = @p1 1, not x = @p11 (another parameter), and "N" <> param s as N @p1, not the identifier N@p1.

Run a statement, or a batch of them, and return the number of rows affected, which is the sum of the counts the server reports. Any result sets are read and discarded.

Run a statement as execute does, and discard the row count. The whole response is still read, and a server error is still thrown.

The rows of the first result set, decoded into the row type by its FromSqlRow instance, with the column types checked before the first row. A query with no result set, such as an UPDATE, gives []. The rest of the response is read to its end, so a later result set is discarded, and an error from a later statement is still thrown.

query for exactly one row. It throws UnexpectedResult if the first result set has no rows or several.

query for at most one row. It returns Nothing if the first result set has no rows, and throws UnexpectedResult if it has several.

The rows of the first result set as a Stream, read from the socket as they are consumed, so memory doesn't grow with the result. Hmssql.Query shows how to consume one.

Read the stream to its end before making another request on the connection. The next request cancels the statement and discards what was left, and the old stream then throws ResultAbandoned. So does a stream whose read was interrupted, by timeout say, which cancels the statement at once, and a stream run a second time after it was drained.

A stream not read to its end leaves its statement running on the server, holding its locks, until the next request, cancelActiveStatement or release cancels it. On a MARS connection (connectMars) other requests leave the stream open instead, so it stays open until it is read to its end, canceled with cancelActiveStatement, or the connection is released. withQueryS is the form that can't leak a stream.

Canceling a statement that is still running inside a transaction under SET XACT_ABORT ON rolls the transaction back (TransactionEndedEarly). Where the transaction must survive, read the stream to its end.

queryS in a scope. The action gets the stream, and when the action ends, a stream not read to its end is canceled (cancelResponse). When the action returns, or throws an exception of its own, withQueryS returns once the statement is over on the server and its locks are released. On a MARS connection (connectMars) this is the way to read a stream beside other requests. Without MARS it works too, under the rule of queryS: a request the action makes abandons the stream.

withQueryS conn "SELECT id FROM dbo.parent" $ \parents ->
  S.mapM_ (\(Only pid) -> query conn ("SELECT ... WHERE parent_id = " <> param (pid :: Int))) parents

An interruption (timeout, killThread) doesn't wait. The statement is canceled at once, and the connection's next request reads the rest of the response first; a transaction function, release and cancelActiveStatement do the same. Until then the statement's locks may still be held, and cancelActiveStatement waits for them to go. The stream must not be used after the action.

Fold over the rows of the first result set as they arrive, without building a list or a stream: the fastest way to count, sum or write rows out. The step runs with the connection lock held, in the middle of reading the response, so it must not use the connection. On a MARS connection it may, because the request takes a session of its own. An exception from the step cancels the response at once and propagates. If it is the step's own exception, the response is drained before it propagates; if it is an interruption, the drain is left to the next request. After the fold, the rest of the response is read to its end, as with query.

Run one statement text once for each row of parameters, all in one request, and return the number of rows each affected, in order. The text refers to a row's parameters as a mkQuery text does, with @p1, @p2, ..., and a row is any ToSqlRow value.

executeMany conn "INSERT INTO item (id, name) VALUES (@p1, @p2)" [(1 :: Int, "one" :: Text), (2, "two")]

Each row is its own sp_executesql call, with the text and the parameter declaration shared (read and encoded once, not per row), so the server compiles the statement once and reuses the plan. It is executeStatements conn (map (mkQuery sql) rows) with that sharing, and everything executeStatements says about round trips, errors, transactions and interruption applies: statements after a failed row may have run, so use withTransaction for all or nothing, or executeManyOutcomes to see which rows failed. For a large load into one table, bulkInsert is faster.

Run several statements in one request, which is one round trip, and return the number of rows each affected, in order, as execute would return it. Each statement is sent as its own sp_executesql call, all in one RPC message. That includes a statement with no parameters, so a SET option it sets or a #temp table it creates lasts only until it returns; send such statements with execute. For many rows of one statement, executeMany is the same with the text read once. To know which statements failed, use executeStatementsOutcomes.

The server runs the statements in order. If some fail, the whole response is still read, and then SqlServerError is thrown for the first that failed. Statements after a failed one may have run: an error that only ends its statement (a constraint violation, say) lets the server go on; one that aborts the batch (THROW, a conversion error, a deadlock, any error under SET XACT_ABORT ON) ends the request there. For all-or-nothing, run it inside withTransaction.

A statement that can't be sent (see queryRequest) throws EncodeError naming its position, before its message is sent. A long list goes out in messages of about 1 MiB, each read before the next is sent, so memory stays bounded; the split changes nothing else. Interrupted, it cancels the running statement; the statements before it have run. An empty list returns [] without a round trip.

How to decode a response that holds several result sets, one at a time in order, for queryResultSets. Build one from resultSet and skipResultSet with the Applicative or Monad combinators; as a Monad, a later decoder may depend on earlier rows. Statements that produce no result set, such as DECLARE, SET and UPDATE, are skipped over.

(parents, children) <- queryResultSets ((,) <$> resultSet <*> resultSet) conn
  "SELECT id, name FROM dbo.parent; SELECT id, parent_id FROM dbo.child"

The rows of the next result set, as query would return them. It throws UnexpectedResult if the response has no further result set.

Read the next result set and discard its rows. It throws UnexpectedResult if the response has no further result set.

Run a batch that returns several result sets, and decode them one at a time, each into its own row type.

(parents, children) <- queryResultSets ((,) <$> resultSet <*> resultSet) conn
  "SELECT id, name FROM dbo.parent; SELECT id, parent_id FROM dbo.child"

The whole response is read, so an error in a later statement still throws SqlServerError, and result sets the ResultSets didn't ask for are discarded.

Run the action in a transaction. It commits if the action returns, and rolls back if the action throws, an asynchronous exception from killThread or timeout included. Beginning the transaction is a round trip of its own before the action runs, and the COMMIT is another.

The connection is held for the whole transaction, so requests from other threads wait until it ends instead of running inside it. The action must not wait for another thread that uses the connection.

On a MARS connection (connectMars) every request the body makes, and every stream it opens, runs in the transaction. No response may be unfinished when it begins or when the body returns (TransactionRequestsActive): begin first, open the streams inside with withQueryS, and let them end before the body returns. If the body throws, the responses it left open are canceled before the rollback.

Throws TransactionAlreadyActive if a transaction is already active; TransactionEndedEarly (after cleaning up) if the body returns but the transaction it started is gone; and TransactionStillOpen (after rolling everything back) if the body began a nested transaction and didn't end it. If the rollback after a failure itself fails, the connection is marked broken (RollbackFailed) rather than left in an unknown state, and the exception that caused the rollback propagates, unless an asynchronous exception interrupted the rollback.

withTransaction at the given isolation level. The begin request sets the level, and the COMMIT or ROLLBACK batch sets it back to READ COMMITTED, the session's level at login, however the transaction ends, so no round trips are added. The session is assumed to be at READ COMMITTED outside withTransactionLevel, and a level the application set by hand is not preserved.

SQL Server's transaction isolation levels, for withTransactionLevel. ReadCommitted is the session's level at login. Snapshot needs ALLOW_SNAPSHOT_ISOLATION turned on for the database, or the transaction fails.

Types that decode from a whole row, one column per field in order. Tuples of up to twelve fields and Only have instances. Derive one for a record whose fields are FromSqlField types, one field per column of the SELECT, in order:

data Customer = Customer { customerId :: Int, name :: Text, birthDate :: Maybe Day }
  deriving stock (Show, Generic)
  deriving anyclass (FromSqlRow)

The column types are checked against the fields before the first row is decoded, and a mismatch is a DecodeError naming the column. For a row decoder written by hand, see singleField.

Types that decode from one column. The usual Haskell types have instances, and Hmssql.Types has the table of which SQL types each accepts. Maybe accepts NULL; without it, a NULL is a DecodeError. An instance of your own defines fieldDecoder, usually with refineField over a built-in type's decoder.

Types that can be sent as a list of parameters: the @p1, @p2, ... of mkQuery, or a row of executeMany or bulkInsert. Derive it for a record whose fields are ToSqlField types, one parameter per field in order:

data NewCustomer = NewCustomer { newName :: Text, newBirthDate :: Maybe Day }
  deriving stock (Generic)
  deriving anyclass (ToSqlRow)

Tuples of up to ten fields, (), Only, a list of one ToSqlField type, a list of SqlParam to mix types, and NamedParams have instances.

Types that can be sent as one parameter, through param or as a field of a ToSqlRow value. The usual Haskell types have instances, and Hmssql.Types has the table of which SQL type each is sent as. An instance of your own defines fieldEncoder, usually with contramap over a built-in type's encoder.

The 1-tuple type or single-value "collection".

This type is structurally equivalent to the Identity type, but its intent is more about serving as the anonymous 1-tuple type missing from Haskell for attaching typeclass instances.

Parameter usage example:

encodeSomething (Only (42::Int))

Result usage example:

xs <- decodeSomething
forM_ xs $ \(Only id) -> {- ... -}

Any value with a ToSqlField instance, so a parameter list can mix types.

Parameters that the SQL text refers to by name. The leading @ is optional. A name must be a T-SQL variable name, and not of the form @pN, which is reserved for the positional parameters; the query functions check both before sending anything. Bulk copy matches the names, with that one @ removed, against the table's columns (Hmssql.BulkCopy).

mkQuery "UPDATE dbo.customer SET email = @email WHERE id = @id"
  (NamedParams [("email", SqlParam newEmail), ("id", SqlParam customerId)])