How much work to update an Ecto2 adapter to Ecto3?

I have to use MSSQL and I want to use Ecto 3. Sadly the adapters I found is only for Ecto2.

How much work is involved in updating an adapter from Ecto2 to Ecto3? Is there some form of documentation what changed and needs to be updated? I just need a ballpark guess, is it changing a few dependencies and names or a complete rewrite?

There are two parts to such an upgrade. First the foundational DBConnection library was updated to version 2.0. Changes required to update a database driver to use it are listed in this issue. Additionally, once the driver is updated, changes to the adpater API itself are listed in here, though for SQL adapters the changes are primarily to support the new features ecto provides and shouldn’t be huge.

3 Likes

Hi. Last year I started working on that transition, what so ever, as per discussion with Jose, the ecto part should be merged into ecto_sql repo. So far effort is here but I haven’t work on that last 3 months since I haven’t had time. I must say that there is not much work, most of the work is to check integration test which I’m sure they are passing all but some MSSQL features do not work as in case of postgresql or mysql.

For instance:

  • MSSQL datetime2 has 7 digitis precision, and elixir DateTime has only 6.
  • “LIKE” wildcarding works only on vrachar and nvarchar fields, most of integration test like is tested over text or ntext fields
  • TDS library uses erlang datetime tuple to work with date and time attributes, so loader and dumpers need to be created in MSSQL ecto adapter BUT cosidering above precision issue, I’m sure there are some edge cases that will cause some unexpexted results, e.g. since rounding last digit in nanosec it may exclude some results.
  • Transactions in MSSQL works differently comparing to transactions in postgresql, once failed transaction (error in query execution) will continue and allow execution of next batch in transaction, and that is not what ecto expects, I did some TDS driver changes that should if last batch in transaction is successful or not and allow executing next if previous didn’t error, but question is, if you are using MSSQL what behavior do you expect?
  • Some integration test threats binary as strings, in MSSQL that is not the case, and in most cases it will not pass.

Above are what I know so far. In ecto2 I copied integration test and chnage schemas so they fit MSSQL server more, but for ect_sql I think there should be some changes in integration test or to tag some unit test to skip in case of mssql.

BTW, TDS driver is migrated to DBConnection 2

EDIT:

The branch in github is tds-support

5 Likes

I thought Elixir’s was configurable?

Testing…

iex(7)> DateTime.utc_now.microsecond
{916163, 6}

Yeah, it’s a tuple that specifies both the digits within a second and the second element is the precision of it. So that should be able to work I’d imagine? This is a fairly new change for note so it is understandable that Ecto2’s mssql adapter didn’t take advantage of it.

I’d imagine the adapter should probably rebind things like text to varchar or whatever is appropriate for mssql as based on what I’m reading ‘text’ isn’t using in mssql (was deprecated, now it shouldn’t be used at all) anyway?

Elixir’s micro/nano/whateverseconds has a scale. :slight_smile:

Uh… that would be a huge can of ‘the-heck-is-happening’ to me if a failure didn’t stop a transaction outright… o.O

That should really be a different DB type, it should on postgresql too, do you have links to these?! o.O

iex(7)> DateTime.utc_now.microsecond
{916163, 6}

Yeah, it’s a tuple that specifies both the digits within a second and the second element is the precision of it. So that should be able to work I’d imagine? This is a fairly new change for note so it is understandable that Ecto2’s mssql adapter didn’t take advantage of it.

Ecto2 mssql adapter worked with erlang datetime tuple, so it kept precision ok so far. Problem is if we cut off 7th digit that MSSQL returns in select and then if you try to use that value, say in where clause, it will probably miss ore include some results. As I said that could be rare, but if you use DateTime2 you probably need that 7th digit. Second thing, in migrations, if you do not set precision for datetime2 it will default to 7 digits. Jose proposed that in migrations we use 6 digits precision for datetime2, if nothing is specified. But what if one is not using migrations (existing database and this is common cases for MSSQL dbs) and do not know that we nailed precision in ecto adapter to max 6 digits? I was thinking adding Ecto.Adapter.MSSQL.DateTime2 custom type BUT integration test are then useless for me. Integration test schema is not organised by type in ecto repo, so I can’t exclude tables or test cases that are using :datetime2 type.

MSSQL still uses text and ntext types, and what so ever, fulltext search works with such fields (project I’m working on is using fulltext search in MSSQL so I vote to support as is it now :slight_smile: )

Same as above, varbinary is what TDS is using for binary and almost all DB I worked on had varbinary in some tables and did binary operations to achieve e.g. masking. types in MSSQL really matters, and there are some penalties if you try not taking care about what you are sending. For example, if you send nvarchar parameter and try to compare it with varchar field, execution plan will have greater cost since number of “reads” will increase since each column has to be converted to nvarchar (unicode), so queries that was normally running in milliseconds then will run in seconds if table has millions of rows. Also, I will check, but I think that you can’t store nvarchar parameter value into varbinary filed, especially case if you try to store 16 unicode characters into varbinary max sized to 16 bytes :slight_smile:

All ecto types has corresponding mssql type, and I’m not sure why we should remove support for any db type. Especially cause MSSQL users are not startups that just started a business. They are rather users that decided to give elixir a try on existing database. Such users (companies) are, or near to be, fully developed and doing stuff from scratch is not so common thing unfortunately.

3 Likes

I created a custom Ecto type to handle the precision of 7 coming from the database. We migrated from Rails which stored dates with the full precision of 7. I ended up just truncating instead of rounding, but rounding may be a better option. The issue I ran into was IO.inspect or printing of the date would crash as it expected the precision to be 6.

microsecond() :: {0..999_999, 0..6}
iex(13)> record.updated_at
%Inspect.Error{
  message: "got ArgumentError with message \"argument error\" while inspecting %{__struct__: DateTime, calendar: Calendar.ISO, day: 2, hour: 19, microsecond: {1234567, 6}, minute: 58, month: 5, second: 47, std_offset: 0, time_zone: \"Etc/UTC\", utc_offset: 0, year: 2007, zone_abbr: \"UTC\"}"
}
iex(14)> record.updated_at.microsecond
{1234567, 6}

Also, the type is limited to 6 in the Calendar module:

Custom type Ecto.Adapter.MSSQL.DateTime2:

2 Likes

That sounds… buggy… Have you reported it to the Elixir repo? I’m curious what they have to say, especially considering that it has variable precision support, and it really should support more than 6 for things like other databases…

I looked through the elixir source and found 6 precision was hardcoded in many places, which might be worth updating. Creating date/time with new() will assume 6, but having that variable would probably also require a length check. Did find a discussion related to it from a few years ago where José mentions it is at 6 to support backwards compatibility. https://github.com/elixir-lang/elixir/issues/6299

1 Like

I am trying to use tds in my phoenix app using the following configuration:

# mix.exs
defp deps() do
  [
    {:ecto_sql, git: "https://github.com/mjaric/ecto_sql", branch: "tds-support"},
    {:tds, "~> 2.0"}
  ]
end
# repo.ex
defmodule MedikEcto.Repo do
  use Ecto.Repo,
    otp_app: :medik_ecto,
    adapter: Ecto.Adapters.MsSql
end

Is this ok?

Hi
I have been trying to find library to interact with mssql and found this thread. After some research, I think using Ecto with ecto_sql is the right way of integrating with mssql, however, I am wondering if tds_support branch is any closer to complete. If not, what are the plans to complete it and how long it may take.

Thanks

Hi @vikas15bhardwaj, I’m estimating remaining work but it should be done soon, it should be out with ecto_sql v3.3 i think

3 Likes

Thanks, will be looking forward to it.

This would make me SO happy :stuck_out_tongue:

ecto_sql v3.3.3 was released yesterday, but it looks like it didn’t make it in time. Is there some work that I could help get this ready? I’ve never contributed to an open source project before, but there’s a first time for everything. :slight_smile:

@euphbriggs, I don’t mind if you do :slight_smile: but I think I haven’t left you much. I finished writing adapter (covered all query features that mssql supports). At the moment there is huge effort to reuse integration test from ecto_sql and ecto repositories in order to add mssql test as part of CI tasks for ecto_sql lib. There are some differences in terms what is possible between mssql and pg or mysql. As soon as I address them it should be out.

Anyhow, I would really appreciate if you could test my branch “tds-support” in any project you are currently using tds_ecto by replacing tds_ecto dependency with ect_sql that points to my branch (tds_ecto is not required anymore). I’m curious if I managed not breaking any previous query “hacks” we had with tds_ecto.

Note that TDS is not yet officialy up in hex so use the one from github master

1 Like

BTW, who ever want to help the best way is to use above branches in your project and report any issue issue in github. I would really appreciate any report!!!

Thanks for the quick reply. I’ll start migrating over from mssqlex/mssql_ecto today and let you know if issues come up.

Thanks for your work on this project!

@euphbriggs, please note that at the moment it requires rust to compile (to support all collations). I will remove this requirement to optional add-on if one need any other collation than latin1

I’m currently working through that part of it. I’m on Windows 10 and have Rust installed, but getting TDS to compile is a bit of a struggle.

I’m trying to find the right balance of tools to compile TDS from the master branch, then I’ll add your tds-support branch and try to compile that.

Hopefully I can have that worked out today so I can start testing its tomorrow.

I filed an issue on the TDS repo about compiling on Windows. I’ll need to be able to deploy this to a Windows Server when it’s done, but in the meantime, I can develop in Linux (I prefer Linux anyways).

You mentioned that you wanted me to file any issues that I found in GitHub, but issues seem to be disabled for the fork under you account. Should I file it under the main elixir-ecto/ecto_sql repo, but reference that I’m working with your fork?

This may be common knowledge for OSS folks. I’m new in this space. :slight_smile: Please forgive me.