Hello everyone! How are you? (I hope you are all fine despite the current situation)

Well, I already spent a good amount of time researching and testing some solutions and I’d like to see if anyone would like to leave a suggestion on other ways to solve this before I make it final.

I’m creating an application for game servers that can interact with the game’s database and enhance the administration experience for community managers.
So, usually, there’s already a database setup in place for those game servers (MySQL 90% of the times) and my application’s database only stores configurations, logs, table references, etc. I have two repositories configured for my application, one that I have control over the schema and the other which I don’t and need to query based on certain conditions.

One of the problems I’m facing right now is coming up with a good strategy to allow the administrators of the server to configure how queries are done against their database and successfully building those queries.

Currently, I’m storing configuration on a table as key/value pairs, and the user can set, for instance, the name of specific tables to be retrieved, primary keys (for cross-referencing) and which fields of a given table can be selected (like player rankings, etc).

Most of this configuration is saved as ‘string’ and Ecto, as far as my research went, only accepts ‘atom’ fields, so this requires me to at least convert these strings to atoms on the fly (which I discovered is not recommended).

A simple example would be retrieving a player from a table and only returning specific columns:

# Configs retrieved from the database
accounts_table = "accounts"
account_columns = "id, player_name, registration_date"
username_column = "player_name"

# Something to query
username_list = ["alpha", "bravo", "charlie"]

#
# Convert some of the string values before passing to the query (account_columns, username_column)
#

GameDatabaseRepo.all(
    from a in accounts_table,
    where: fragment("? in (?)", field(a, ^username_column), ^username_list),
    select: ^account_columns
)

One of the things I’m doing to prevent creating a lot of atoms is to limit the number of columns the user can specify, but I’m not completely happy with the solution so far because it is a very poor workaround.
I also tested storing the queries directly to the database and just including filters, but the fragment function does not play nice with only string params (SQL injection reasons).
I also tried running raw queries and setting SQL variables as an extension point but I lose a bit of composability and SQL variables have their own set of limitations.