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

4 comments

#tds

0 928 4

2024-01-08 15:06:58 UTC

First 4 of 4 Posts

joddm

A possible point in the right direction might be dynamic repos

Replicas and dynamic repositories — Ecto v3.11.1 (hexdocs.pm)

Underjord | Ecto & Multi-tenancy - Dynamic Repos - Part 1 - Getting started

Edit:
Also one reason why you’re not getting query prefixes to work is maybe you haven’t configured connection prefixes correctly? Multi tenancy with query prefixes — Ecto v3.11.1 (hexdocs.pm)

Underjord | Ecto & Multi-tenancy - Prefixes - Part 3

(I don’t have any experience using MS SQL or TDS)

Post #1

miguelvillaresb

Thanks for the reply. I already checked these references but I am not getting good results making some tests. It seems that query prefix support is fully implemented on PostgreSQL and MySQL but not really sure about Microsoft SQL Server with TDS.

Post #2

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).

https://github.com/elixir-ecto/ecto_sql/blob/da2b3b5f42186d70395464f19fabf50e7d9aa1b5/lib/ecto/adapters/tds/connection.ex#L1662C10-L1662C21

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.

Post #3

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!!!

Post #4

— All posts loaded —