How to close Xandra db connection?

Questions / Help
,
1

I am using Xandra for Cassandra in my Phoenix application.

My connection looks like

def cass_conn do
    {:ok, conn} = Xandra.start_link(
      nodes: [Application.get_env(:diamond, :env_vars)[:cass_host]],
      authentication: {Xandra.Authenticator.Password, 
        [
          username: Application.get_env(:diamond, :env_vars)[:cass_host], 
          password: Application.get_env(:diamond, :env_vars)[:cass_pass]
        ]},
      pool: DBConnection.Poolboy,
      pool_size: 10)

    conn
  end

To execute a query

def find_by_id(audit_log_id) do
    
    stm = "SELECT * FROM audit_log_keyspace.rules_engine_audit_logs WHERE id=:id;"
    
    conn = cass_conn()

    prepared = conn
      |> Xandra.prepare!(stm)
      
    conn
      |> Xandra.execute!(prepared, %{ "id" => audit_log_id })
      |> Enum.fetch!(0)
  end

So here we will be calling find_by_id multiple of times which in turn calls cass_conn which returns new connection every time it is called.

Now I have doubt if the new connection be closed once the query is executed or do we have to close it manually from code.

Otherwise there might be performance issue due to multiple open db connections.

Any suggestions.

2

Xandra uses DBConnection under the hood so you can call DBConnection.close/3 like so:

DBConnection.close(conn, prepared)

A better approach is to have the Xandra process managed by the supervision tree. So you can leverage the pool for better use of resources, automatic reconnection, and automatic clean up when the application finishes.

To do that add to your workers list at the application entrypoint:

 workers = [
    ...,
   {Xandra, name: MyXandra, nodes: [Application.get_env(:diamond, :env_vars)[:cass_host]],
      authentication: {Xandra.Authenticator.Password, 
        [
          username: Application.get_env(:diamond, :env_vars)[:cass_host], 
          password: Application.get_env(:diamond, :env_vars)[:cass_pass]
        ]},
      pool: DBConnection.Poolboy,
      pool_size: 10}
     .....,
  ]

Then to use this process to execute the queries you will reference by the name you gave to it, in the example it was MyXandra:

def find_by_id(audit_log_id) do
    
    stm = "SELECT * FROM audit_log_keyspace.rules_engine_audit_logs WHERE id=:id;"

    prepared = MyXandra |> Xandra.prepare!(stm)
   
    MyXandra
    |> Xandra.execute!(prepared, %{ "id" => audit_log_id })
    |> Enum.fetch!(0)
end