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
