MssqlEcto

Installation

Erlang ODBC Application

MssqlEcto requires the Erlang ODBC application to be installed. This might require the installation of an additional package depending on how you have installed Elixir/Erlang (e.g. on Ubuntu sudo apt-get install erlang-odbc).

Microsoft's ODBC Driver

MssqlEcto depends on Microsoft's ODBC Driver for SQL Server. You can find installation instructions for Linux or other platforms on the official site.

Hex

With Application Inference

If you are using application inference, i.e. application in your mix.exs looks something like this:

def application do
  [extra_applications: [:logger]]
end

Note, the lack of :applications key. Then, you just need to add the following dependencies:

def deps do
  [{:mssql_ecto, "~> 1.2.0"},
   {:mssqlex, "~> 1.1.0"}]
end

Without Application Inference

If you are explicitly calling out all running applications under application in your mix.exs, i.e. it looks something like this:

def application do
  [applications: [:logger, :plug, :postgrex]]
end

Then, you need to add mssql_ecto and mssqlex to both your deps and list of running applications:

def application do
  [applications: [:logger, :plug, :mssqlex, :mssql_ecto]]
end

def deps do
  [{:mssql_ecto, "~> 1.2.0"},
   {:mssqlex, "~> 1.1.0"}]
end

Configuration

Example configuration:

config :my_app, MyApp.Repo,
  adapter: MssqlEcto,
  database: "sql_server_db",
  username: "bob",
  password: "mySecurePa$$word",
  hostname: "localhost",
  instance_name: "MSSQLSERVER",
  port: "1433"

Example Project

An example project using mssql_ecto with Docker has kindly been created by Chase Pursłey. It can be viewed here.

Type Mappings

Ecto Type	SQL Server Type	Caveats
:id	int
:serial	int identity(1, 1)
:bigserial	bigint identity(1,1)	When a query is returning this value with the `returning` syntax and no schema is used, it will be returned as a string rather than an integer value
:binary_id	char(36)
:uuid	char(36)
:string	nvarchar
:binary	nvarchar(4000)	Limited size, not fully implemented
:integer	int
:boolean	bit
{:array, type}	list of type	Not Supported
:map	nvarchar(4000)	Not Supported
{:map, _}	nvarchar(4000)	Not Supported
:date	date
:time	time	Can write but can't read
:utc_datetime	datetime2
:naive_datetime	datetime2
:float	float
:decimal	decimal

Features not yet implemented

Table comments
Column comments
On conflict
Upserts

Contributing

Integration Test Setup

Running the integration tests requires an instance of SQL Server running on localhost and certain configuration variables set as environment variables:

MSSQL_DVR should be set to the ODBC driver to be used. Usually SQL Server Native Client 11.0 on Windows, ODBC Driver 17 for SQL Server on Linux.
MSSQL_UID should be set to the name of a login with sufficient permissions, e.g. sa
MSSQL_PWD should be set to the password for the above account

The tests will create a database named mssql_ecto_integration_test

Code of Conduct

This project had a Code of Conduct if you wish to contribute to this project, please abide by its rules.

findmypast-oss / mssql_ecto

readme