How can I delete registries from different tables without using schema in Elixir

Question

I am trying to delete a register from some tables, all related to same user Id since Elixir, but using an external SQL Server database. I want to delete the tables in a specific order but without using Schema. I want to execute these SQL queries

SELECT * 
FROM UsersConfig 
WHERE UserId = 'D9960E26-3F7F-4F8C-954A-FF6316D7A3B3'

SELECT * 
FROM UserBranch 
WHERE UserId = 'D9960E26-3F7F-4F8C-954A-FF6316D7A3B3'

DECLARE @userCompanyPreferencesId uniqueidentifier

SELECT @userCompanyPreferencesId = UserCompanyPreferencesId 
FROM UserCompany 
WHERE UserId = 'D9960E26-3F7F-4F8C-954A-FF6316D7A3B3'

SELECT * 
FROM UserCompany 
WHERE UserId = 'D9960E26-3F7F-4F8C-954A-FF6316D7A3B3'

SELECT * 
FROM UserCompanyPreferences 
WHERE UserCompanyPreferencesId = @userCompanyPreferencesId

SELECT * 
FROM Users 
WHERE UserId = 'D9960E26-3F7F-4F8C-954A-FF6316D7A3B3'

But I dont want to add unused schemas to elixir protect. I am trying to do something like this:

def delete_user_by_user_number(user_id) do
    query = """
    DELETE UsersConfig WHERE UserId = $1
    DELETE UserBranch WHERE UserId = $1
    DECLARE @userCompanyPreferencesId uniqueidentifier
    SELECT @userCompanyPreferencesId = UserCompanyPreferencesId FROM UserCompany WHERE UserId = $1
    DELETE UserCompany WHERE UserId = $1
    DELETE UserCompanyPreferences WHERE UserCompanyPreferencesId = @userCompanyPreferencesId
    DELETE Users WHERE UserId = $1
    """

    Ecto.Adapters.SQL.stream(UsersRepo, query, [user_id])
  end

But only I get the query translated to SQL

%Ecto.Adapters.SQL.Stream{
  meta: %{
    cache: #Reference<0.3298680420.2890006536.27915>,
    opts: [timeout: 15000, pool_size: 2, pool:
m DBConnection.ConnectionPool],
    pid: #PID<0.749.0>,
    repo: ChainUtils.UsersRepo,
    sql: Ecto.Adapters.Tds.Connection,
    telemetry: {ChainUtils.UsersRepo, :debug,
     [:chain_utils, :users_repo, :query]
}
  },
  opts: [],
  params: ["D9960E26-3F7F-4F8C-954A-FF6316D7A3B3"],
  statement: "DELETE UsersConfig WHERE UserId = $1\nDELETE UserBranch WHERE UserId = $1\nDECLARE @userCompanyPreferencesId uniqueidentifier
\nSELECT @userCompanyPreferencesId = UserCompanyPreferencesId FROM UserCompany WHERE UserId = $1\nDELETE UserCompany WHERE UserId = $1\nDELETE UserCompanyPreferences WHERE UserCompanyPreferencesId = @userCompanyPreferencesId\nDELETE Users WHERE UserId = $1\n"
}

Can you help me with some ideas to do something like this?

Answer 1

The reason nothing is happening is because you're using Ecto.Adapters.SQL.stream. This function creates a lazy stream of results from your query and nothing will actually happen until you start consuming that stream. I wouldn't use it for a query that is only needed for its side-effects, like a delete - use Ecto.Adapters.SQL.query! instead. Maybe something like:

queries = [
  "DELETE UsersConfig WHERE UserId = $1",
  "DELETE UserBranch WHERE UserId = $1",
  "DECLARE @userCompanyPreferencesId uniqueidentifier",
  "SELECT @userCompanyPreferencesId = UserCompanyPreferencesId FROM UserCompany WHERE UserId = $1",
  "DELETE UserCompany WHERE UserId = $1",
  "DELETE UserCompanyPreferences WHERE UserCompanyPreferencesId = @userCompanyPreferencesId",
  "DELETE Users WHERE UserId = $1"
]

for query <- queries do
  Ecto.Adapters.SQL.query!(UsersRepo, query, [user_id])
end