[histriosum]

I've been tempted a few times to dip my toes into Elixir -- I like the language and the BEAM concepts, but in my environment I always bump into the following concern..

Ecto v3 has been out for more than a year now, and there are still only two supported adapters.. MySQL and Postgres. In my environment, I use a lot of MSSQL and SQLite in addition to Postgres, and those adapters haven't been successfully ported over to Ecto v3 yet.. in looking at some of the threads related to porting efforts, it appears that it must be a fairly daunting process.

I always get to that point in my technical evaluation and wonder whether I ought to sit back and wait for a while yet, until the database stuff catches up and solidifies. I realize Phoenix can still use Ecto v2, but that just seems like it would add legacy dependency issues.. In actual practice, is most everyone using Elixir and Phoenix just sticking with Postgres/MySQL? Is there some other mitigating method that folks are using that makes Ecto support a non-issue?

[josevalim]

The TDS adapter for MSSQL is almost ready for Ecto v3 thanks to efforts from Milan Jarić. We are also planning to merge it into Ecto SQL, so it doesn't fall behind again.

Meanwhile, I have worked with a company that had to use MSSQL and they ended-up using two Ecto MSSQL adapters at the same time so they could cover all use cases, which is obviously far from ideal.

It is one of these things that, if we had updated it as we went along, it wouldn't have been so much work, but because it fell behind, the amount of work becomes quite big.

However, there are no plans for a built-in sqlite3 adapter, so your observation still holds true. Sometimes you will stumble upon a part of the ecosystem that is not quite there, and then you need to make a decision between waiting, developing it, or using something else.

[fyfy18]

I think this hits on one of my biggest complaints with the Elixir and Erlang ecosystem. As a high level toolkit for building distributed systems it's great, but if you need to do something more low level you are often left on your own using a third party library. This on it's own isn't a big deal, Ruby has the same limitations. Unfortunately the Erlang and Elixir ecosystems aren't as big as Ruby, so that often means the third party libraries aren't so battle tested and features are limited. Often then are written for one company's use case, and don't do anything outside of that.

I run a SaaS product where the backend is Elixir (it's been going for 10 years, so originally written in pure Erlang) and part of it needs to do a lot of http requests. It's a bit of a unusual use case, as I need to do 1000s of requests per minute to different servers. It's not feasible to keep that many connections open, so the connection needs to be recreated each time. Using hackney (the #1 Erlang http client wrapper) I found that some requests often took much longer on average than other requests. I narrowed it down to being a problem somewhere on the client side, but Erlang doesn't provide any tooling for digging deeper into the lifecycle of a HTTP request. I figured maybe it's a problem with TLS and then found Erlang (I think its been improved in v22) had very poor performance when verifying SSL certificates, so I had to disable that. I switched to mint which is a wrapper written in pure Elixir which helped a lot, but still it a left a lot to be desired.

I wrote up a quick test in Go and found doing the same requests, it didn't have any performance issues or timing oddities, and the code was much simpler. I've hbeen working on extracting that part of the system into a Go app, called via gRPC. Unfortunately there is no official gRPC library for Erlang, and the only third party one has limited features that I've had to work around. So it's a bit painful, but don't get me wrong I love using Elixir and Erlang :-)

[methyl]

> I've hbeen working on extracting that part of the system into a Go app, called via gRPC

What if you called it via Port instead of gRPC?

We are also using Elixir at our SaaS for more than 2 years in production. There are times (although rare) when Elixir performance is just not enough. For those cases we tend to use Rustler, which makes integration with Rust code pretty safe and straightforward. Did you consider Rust instead of Go?

[ericsanchez]

This. Thanks, this was the kind of thing I came here for.

[bglusman]

I guess it depends on your needs and motivation... we recently completed the transition from Ecto2 to Ecto3, and while not totally trivial in our case, I think you could do in a way that wasn't hard if you maintained compatibility with both from the start, so that only config had to change. But also, what are your requirements for using SQLite and/or MSSQL? Are you looking at porting existing projects, or new services talking to existing production databases? It used to be common in Rails to use SQLite in development and in production use Postgres or MySQL, but I think everyone kind of acknowledges its better now to run the same database in local and production if possible, and it's so easy that motivation is gone. I once had to use MSSQL in production for a HIPPA compliance issue, so I understand that might be hard to avoid in some situations, and SQLite is certainly well suited to some embedded applications, but you could certainly try and work on porting the ecto 2 SQLite and/or MSSQL adapter from ecto2 to ecto3 as a project and I'm sure the community would be very supportive in helping if you have motivation. I might be interested in helping with SQLite, but I don't have any specific motivation so I'm not likely to start it on my own.

[tomc1985]

SQLite is really handy for smaller use cases and being able to spin up a new project quickly and without dependenices. TBH I'm kind of surprised its missing from the list

[histriosum]

In my case, MSSQL hosts our main business databases which are directly tied to revenue.. If it were being built today, it would probably be on Postgres -- but migrating it any time soon would be prohibitive, and just not profitable.. MSSQL is expensive, but is a darned good database, so there isn't much push to move.

As for SQLite, we use it internally a fair bit for ETL processes, and as a starting point for most of our applications. 90% of the business apps that we end up creating never need anything more than SQLite -- the 10% that do either end up on MSSQL (if they have to tie in to our main databases) or on Postgres (for everything else).

[bglusman]

Sounds fair on both counts... Good news from José above that the official MSSQL support is coming soon and will be merged into ecto_sql package.... and like I said, if you (or someone else) decides they want to work on updating the old ecto2 SQLite adapter to work with Ecto3, I can't promise much, but I'd be glad to try and help!

Also, as a note, my first production Elixir service didn't talk to a relational DB at all, and was basically a thin API wrapper around ElasticSearch... others I know have basically used it just for realtime features connecting to browsers with WebSockets for push via Phoenix Channels (or now LiveView)... so even without any of those database adapters you may well find a use case it's a perfect fit for, and where lack of those adapters is not an impediment! Either way, good luck!

[sbrother]

I've been working with Elixir (and often Phoenix) since nearly its beginning, and I recently took a project that involved some interop with MSSQL. It has been tenable, but not a great experience. I'm not even using Ecto, just https://hexdocs.pm/mssqlex/Mssqlex.html. It works but is a little buggy, not much community support, and none of the nice things that Ecto gives you.

In one different project, there was a desire to deploy a Phoenix API natively to Windows clients. After poking and spending a few days looking at years-old Stack Overflow posts with no activity, we ended up pushing back on the requirement and going with a containerized solution.

Elixir/Erlang/BEAM is a wonderful stack and I probably use it for 50%+ of my software, but I would not choose it in an environment where interop with MS technology was a requirement.

[the_angry_angel]

My personal experience with MSSQL drivers (ORMs and lower level) for many younger, non-Microsoft endorsed, languages is that whilst they will work you will get caught with sharp edges. Until several years down the line and enough people who have the will/time and consolidate their efforts. That said, even MS official endorsed libraries can suddenly disappear (I'm looking at you msnodesql).

Having been down this path, without the time to dedicate fixing/working on a driver, I wouldn't even consider a non-"blessed" language in combination with MSSQL anymore. It was too much pain.

[S04dKHzrKT]

I haven't looked into Elixir so it might not be at all similar, but you might look into F# + Saturn. I know Saturn is heavily influenced by Phoenix. There are a few different data access libraries that might fit your needs: Entity Framework, Dapper, F# Sql Provider, Rezoom.Sql.

https://github.com/SaturnFramework/Saturn