Tds - MSSQL Driver for Elixir
MSSQL / TDS Database driver for Elixir.
NOTE:
Since TDS version 2.0, tds_ecto
package is deprecated, this version supports ecto_sql
since version 3.3.4.
Please check out the issues for a more complete overview. This branch should not be considered stable or ready for production yet.
For stable versions always use hex.pm as source for your mix.exs!!!
Usage
Add :tds
as a dependency in your mix.exs
file.
def deps do
[{:tds, "~> 2.0"}]
end
As of TDS version >= 1.2
, tds can support windows codepages other than windows-1252
(latin1).
If you need such support you will need to include additional dependency {:tds_encoding, "~> 1.0"}
and configure :tds
app to use Tds.Encoding
module like this:
import Mix.Config
config :tds, :text_encoder, Tds.Encoding
Note that :tds_encoding
requires Rust compiler installed in order to compile nif.
In previous versions only SQL_Latin1_General was suported (codepage windows-1252
).
Please follow instructions at rust website
to install rust.
When you are done, run mix deps.get
in your shell to fetch and compile Tds.
Start an interactive Elixir shell with iex -S mix
.
iex> {:ok, pid} = Tds.start_link([hostname: "localhost", username: "test_user", password: "test_password", database: "test_db", port: 4000])
{:ok, #PID<0.69.0>}
iex> Tds.query!(pid, "SELECT 'Some Awesome Text' AS MyColumn", [])
%Tds.Result{columns: ["MyColumn"], rows: [{"Some Awesome Text"}], num_rows: 1}}
iex> Tds.query!(pid, "INSERT INTO MyTable (MyColumn) VALUES (@my_value)",
...> [%Tds.Parameter{name: "@my_value", value: "My Actual Value"}])
%Tds.Result{columns: nil, rows: nil, num_rows: 1}}
Features
- Automatic decoding and encoding of Elixir values to and from MSSQL's binary format
- Support of TDS Versions 7.3, 7.4
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!).
Connecting to SQL Server Instances
Tds supports SQL Server instances by passing instance: "instancename"
to the connection options.
Since v1.0.16, additional connection parameters are:
:set_language
- check stored procedure outputexec sp_helplanguage
name column value should be used here:set_datefirst
- number in range1..7
:set_dateformat
- atom, one of:mdy | :dmy | :ymd | :ydm | :myd | :dym
:set_deadlock_priority
- atom, one of:low | :high | :normal | -10..10
:set_lock_timeout
- number in milliseconds > 0:set_remote_proc_transactions
- atom, one of:on | :off
:set_implicit_transactions
- atom, one of:on | :off
:set_transaction_isolation_level
- atom, one of:read_uncommitted | :read_committed | :repeatable_read | :snapshot | :serializable
:set_allow_snapshot_isolation
- atom, one of:on | :off
:set_read_committed_snapshot
- atom, one of:on | :off
Set this option to enable snapshot isolation on the database level. Requires connecting with a user with appropriate rights. More info here.
Data representation
TDS | Elixir |
---|---|
NULL | nil |
bool | true / false |
char | "é" |
int | 42 |
float | 42.0 |
text | "text" |
binary | <<42>> |
numeric | #Decimal<42.0> * |
date | {2013, 10, 12} |
time | {0, 37, 14} |
datetime | {{2013, 10, 12}, {0, 37, 14}} |
uuid | <<160,238,188,153,156,11,78,248,187,109,107,185,189,56,10,17>> |
Currently unsupported: User-Defined Types, XML
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
Contributing
Clone and compile Tds with:
git clone https://github.com/livehelpnow/tds.git
cd tds
mix deps.get
You can test the library with mix test
. Use mix credo
for linting and
mix dialyzer
for static code analysis. Dialyzer will take a while when you
use it for the first time.
Development SQL Server Setup
The tests require an SQL Server database to be available on localhost. If you are not using Windows OS you can start sql server instance using Docker. Official SQL Server Docker image can be found here.
If you do not have specific requirements on how you would like to start sql server in docker, you can use script for this repo.
$ ./docker-mssql.sh
If you prefer to install SQL Server directly on your computer, you can find installation instructions here:
Make sure your SQL Server accepts the credentials defined in config/test.exs
.
You also will need to have the sqlcmd command line tools installed. Setup instructions can be found here:
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.
License
Copyright 2014, 2015, 2017 LiveHelpNow
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.