Tds - MSSQL Driver for Elixir

MSSQL / TDS Database driver for Elixir.

Configuration

Example configuration

import Mix.Config

config :your_app, :tds_conn,
  hostname: "localhost",
  username: "test_user",
  password: "test_password",
  database: "test_db",
  port: 1433

Then using Application.get_env(:your_app, :tds_conn) use this as first parameter in Tds.start_link/1 function.

There is additional parameter that can be used in configuration and can improve query execution in SQL Server. If you find out that your queries suffer from “density estimation” as described here

You can try switching how tds executes queries as below:

import Mix.Config

config :your_app, :tds_conn,
  hostname: "localhost",
  username: "test_user",
  password: "test_password",
  database: "test_db",
  port: 1433,
  execution_mode: :executesql

This will skip calling sp_prepare and query will be executed using sp_executesql instead. Please note that only one execution mode can be set at a time, and SQL Server will probably use single execution plan (since it is NOT estimated by checking data density!).

Dates and Times

Tds can work with dates and times in either a tuple format or as Elixir calendar types. Calendar types can be enabled in the config with config :tds, opts: [use_elixir_calendar_types: true].

Tuple forms:

• Date: {yr, mth, day}
• Time: {hr, min, sec} or {hr, min, sec, fractional_seconds}
• DateTime: {date, time}
• DateTimeOffset: {utc_date, utc_time, offset_mins}

In SQL Server, the fractional_seconds of a time, datetime2 or datetimeoffset(n) column can have a precision of 0-7, where the microsecond field of a %Time{} or %DateTime{} struct can have a precision of 0-6.

Note that the DateTimeOffset tuple expects the date and time in UTC and the offset in minutes. For example, {{2020, 4, 5}, {5, 30, 59}, 600} is equal to '2020-04-05 15:30:59+10:00'.

UUIDs

MSSQL stores UUIDs in mixed-endian format, and these mixed-endian UUIDs are returned in Tds.Result.

To convert a mixed-endian UUID binary to a big-endian string, use Tds.Types.UUID.load/1

To convert a big-endian UUID string to a mixed-endian binary, use Tds.Types.UUID.dump/1

Special Thanks

Thanks to ericmj, this driver takes a lot of inspiration from postgrex.

Also thanks to everyone in the Elixir Google group and on the Elixir IRC Channel.