miguelvillaresb
Ecto and TDS to access MSSQL Server databases
I’m using Ecto and TDS to access a Microsoft SQL Server instance from an Elixir application with multiple databases, specifically one database per client. When configuring the TDS repo, I’m required to specify a specific database, but in the application I need to access different databases depending on the client making the request.
The databases are named following the <customer_id>_database naming convention.
I’m trying to access multiple databases using a single repository, as there are dozens or hundreds of clients. I’ve tried using query prefixes as well as Ecto syntax, but in all cases, I receive errors indicating that the <customer_id>_database.dbo.table objects are inaccessible. The only way I’ve managed to access different databases is by directly executing SQL queries like this:
sql_query = """
SELECT c.snombrecli FROM \"14637_database\".dbo.clientes AS c WHERE c.icodcli = 1;
"""
{:ok, result} = Repo.query(sql_query, [])
Is there an alternative to using Ecto with Microsoft SQL Server to comfortably access N client databases?
Thanks in advance
Marked As Solved
miguelvillaresb
Really good catch, many thanks for that. My code does not work as it is but I tried to include the prefix as a tuple in the Ecto query and now I am getting data from the desired database and schema, with something like this:
query =
from(c in "clientes",
where: c.icodcli == 1,
select: c.snombrecli
)
results = MNRepo.all(query, prefix: {"14637_advAboUsu1", "dbo"})
Thanks again for helping!!!
Also Liked
sbuttgereit
I’m doing some MS SQL server work right now (not Elixir based) and thought I’d poke around.
I saw something interesting in the ecto_sql TDS related code (which is where the idea of prefix will be implemented, not the Elixir tds driver).
If you look at the these quote_table/2 definitions inside the TDS adapter, it looks like they may take the prefix in the form of a tuple. Specifically it looks like prefix could take the form {server, db, schema} or {db, schema}.
Here’s another snippet of code from ecto_sql where you can see the quote_table/2 function get called from insert/7 (ecto_sql/lib/ecto/adapters/tds/connection.ex at da2b3b5f42186d70395464f19fabf50e7d9aa1b5 · elixir-ecto/ecto_sql · GitHub):
["INSERT INTO ", quote_table(prefix, table), values]
Now some caveats: I’ve only done a cursory search of the source code over my morning coffee; I’ve not tested this; I’ve not done an exhaustive search over documentation; I haven’t looked to see if this was support that was started and not finished. All of that could apply and it could be that there is some parsing somewhere above these functions that is expecting a different form for the designations of db and schema. I could see issues for this in some representations.
Anyway, it’s a lead that might get you to a better answer.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Courses that'll move you from confusion to "Aha, now I get it!"
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"