# Creating Tabular Representation of Database Indexes using Elixir Mix Task

Recently, while working on one of my personal Elixir projects. I came across a scenario where I needed to make some changes to the database schema.The changes mainly revolve around adding and removing `indexes` due to changes in the business requirements. Every time I made the changes by running the migrations I was going through the hassle of checking the columns in the database. To see the list of indexes in each table I had to run

* `\d table_name` in the Postgres terminal or
    
* Run the query `SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table_name';` in the database admin tools like `Beaver` or `PgAdmin`.
    

Since the changes were about indexes, I had to be extra cautious to understand their details. So, I was wondering if there could be a way I could see the result soon after running the migrations or whenever I need to view the list of indexes in the same terminal I am working. After doing some tinkering, a `mix task` was something that came to my mind to achieve the goal. Let's understand the requirements more deeply to make development easier.

### Requirement

We want a `mix` command that will list all the `indexes` in our application database. We will run the command `mix list_indexes` and it will render all the indexes in our application. Refer to the image below to get an idea of what output the command is going to render.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715611820971/ca0b5bfe-0d88-49a9-bed4-da2190f8856a.png align="center")

Notice, the perfect tabular structure its borders, gaps, etc. We're going to develop the logic to replicate this exactly. So, let's get started.

### Adding mix task command

Before starting to create a mix task (command) first I'll give you a brief introduction to what is a `mix task`. In any Phoenix project when we run `mix help` it lists all the custom mix tasks. Refer to the example diagram below to get an idea.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1717619653328/23199b8f-dd8e-4e33-a5aa-2ae6f8b9e427.jpeg align="center")

Now, we want to write our custom mix task to render the list of indexes. To achieve this, we will create a file `lib/mix/tasks/list_indexes.ex`. In the file, we will add the following code

```elixir
  defmodule Mix.Tasks.ListIndexes do
      use Mix.Task

      @shortdoc "To list all the indexes in the database"

      def run(_args) do
           # Write your task logic here
      end
  end
```

If you'll notice we have added `use Mix.Task` macro, which adds task capability to this file. To list this task within our application we can run `mix help` in the project terminal. You will get the list of the `tasks` but won't find the `task` we created. It is because we need to compile the project. We will do this by running `mix compile`. Now on running `mix compile` and later running `mix help` again we can see the task we created.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715667799617/da505b4a-3626-419a-81e5-e758aa4b754d.png align="center")

### Writing the main logic

Up until now, we understood the requirements and what we wanted to achieve and we set up the code structure. The main logic is remaining, so this will be our approach

* Query database to fetch all the indexes using Ecto.
    
* Get the longest name in each column
    
* Render header
    
* Render all rows
    

#### Query database to fetch all the indexes using Ecto.

This will be the simplest part of this blog. We will write a simple Ecto query to fetch all the indexes in the project, which will be passed down for further processing.

```elixir
defmodule Mix.Tasks.ListIndexes do
     ............
     ............
     ............
     query = from p in "pg_indexes",
        select: %{index_name: p.indexname, table_name: p.tablename},
        where: p.schemaname == "public"

     rows = Repo.all(query)

end
```

The query is quite simple and intuitive. We are querying the `pg_indexes` table, which is part of the `public` schema. For the sake of the demo, we are just querying `index_name` and `table_name`. Later, we pass the query to `Repo.all` to fetch it. To make the `query` (syntax) and `Repo.all` work, we have aliased `Ecto.Query` and imported `Repo`. This will return a list of structs with `index_name` and `table_name`, something like this:

```elixir
[
  %{index_name: "schema_migrations_pkey", table_name: "schema_migrations"},
  %{index_name: "distributors_pkey", table_name: "distributors"},
  %{index_name: "distributors_email_index", table_name: "distributors"},
  %{index_name: "distributors_tokens_pkey", table_name: "distributors_tokens"},
  .....
]
```

#### Get the longest name in each column

In this section, we will write a logic to find the longest string length for each column from fetched records. This will help us to add padding to each column. Suppose, you have two records for column `index_name` say `username_index` and `distributors_email_index`. The function we are going to write will return the length of `distributors_email_index` because it is the longest one. We will have to write this logic such that it will return a list with the length of two columns (since there are two columns `Index Name` and `Table Name`. The code will consist of two functions `longest_columns_values/1` and `find_longest_length/1`. First, we will understand the longest\_columns\_values.

👉 `longest_columns_values/1`:

This function takes a list of records as its argument. Each record is a map with keys `:index_name` and `:table_name`. The purpose of this function is to transform the records into a list of lists containing only the values of `:index_name` and `:table_name` and then find the longest length for each column by passing to the find\_longest\_length/1 function.

```elixir
def longest_columns_values(records) do
  Enum.map(records, fn index ->
    [index.index_name, index.table_name]
  end)
  |> find_longest_length
end
```

👉 `find_longest_length/1`:

This private function (you can avoid making it private) takes a list of columns of each row and calculates the maximum length of the strings in each column.

```elixir
defp find_longest_length(rows) do
  Enum.reduce(rows, [], fn row, acc ->
    Enum.zip(row, acc ++ [0])
    |> Enum.map(fn {str, len} ->
      String.length(str) |> Kernel.max(len)
    end)
  end)
end
```

* `Enum.reduce(rows, [], fn row, acc -> ... end)`:
    
    This line uses `Enum.reduce` to iterate over each row in rows. The `acc` (accumulator) starts as an empty list and is used to store the maximum lengths of each column.
    
* `Enum.zip(row, acc ++ [0])`:
    
    For each row, this line zips the current row with the accumulator. If the accumulator is shorter than the row, it is padded with zeros.
    
* `Enum.map(fn {str, len} -> String.length(str) |> Kernel.max(len) end)`:
    
    This line maps over the zipped list of {str, len} tuples. It calculates the length of str and compares it with len (the current maximum length), keeping the larger of the two.
    

The result of find\_longest\_length/1 is a list of integers representing the maximum length of the strings in each column. Refer to the example to understand its usage.

```elixir
rows = [
  %{index_name: "schema_migrations_pkey", table_name: "schema_migrations"},
  %{index_name: "username_key", table_name: "some_long_table_name"},
]

IO.inspect(longest_field_values(rows))
#=> [ 22, 20] - 22 is the length of `schema_migrations_pkey` 
#=> because it is the longest name in the first column i.e. `:index_name`,
#=> and 20 is the length of `some_long_table_name` 
#=> because it is the longest name in the second column i.e. `:table_name`.
```

#### Adding the header

Now that we have written the logic to get the list of the widest columns(using `longest_columns_values`), we will use this list to render the table header. For this, we will write a function `render_single_row` which we will use to render the header, and later we'll reuse it to render all the rows.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1717609691653/ea80576d-ac6c-4ce2-b0cf-f76333c06d01.png align="center")

Notice the vertical line `|` at the start, end, and in the middle of the two columns. The start line will be easy to add, but it will be tougher to add the middle and end lines. We don't know how much space we need to leave after each column letter. This is where `longest_columns_values` will shine. But how are we going to use it? There is a formula that we are going to follow to give space after each column and the formula is

```plaintext
spaces_count = widest_column_for_the_position - column_name
```

\=> Explanation

We have seen earlier in the previous section `widest_column_counts = [22, 20]` and as per the screenshot, the first column is `Index Name` which is of length `10` . The `Index Name` is the first column so we will use the first element of `widest_column_counts` . Hence, the value will be `space_count` will be `20-10=10` .

So, we will write a `render_single_row/2` function as per the above explanation. It will take two arguments i.e `row` (header in this case) and `widest_column_counts` .

```elixir
defp render_single_row(%{index_name: index_name, table_name: table} , widest_column_counts) do
    IO.write("| ")

    [index_name, table]
      |> Enum.with_index()
      |> Enum.each(fn {column_content, index} ->
          border = render_border(index, column_content, widest_column_counts)
          IO.write("#{item} #{border}")
        end)

    IO.puts(" ") #Next line
end
```

As you can see the function starts with rendering the leftest vertical line `|` . Then we iterate over the list of columns and render the content using `IO.write("#{column_content} #{border}")`. So, the above logic is nothing but the entire row rendering logic.

Also, we will write a function `render_border` which will render ending vertical lines along with trailing spaces as discussed earlier. It will take three parameters `position_of_column` , `item - the item to be rendered` and `columns_count`

```elixir

defp render_border(position_of_column, item, columns_count) do
    count = Enum.at(columns_count, position_of_column) - String.length(item)
    "#{String.duplicate(" ", count)} | "
end
```

The above-written code logic has already been explained. We are just fetching `space_counts` and we are duplicating spaces `" "` as per `count` value and ending with a vertical line `|` .

#### Render all rows

This is the final section of the table render logic. Remember we fetched the records, and it was nothing but a list of records. We've already completed the logic to render a single row. Now, we have to render a list of rows. For this, we just have to iterate over the `rows`. Below is the function that will do this job.

```elixir
defp render_multiple_rows(rows, widest_column_counts) do
  Enum.each(rows, fn row -> render_single_row(row, widest_column_counts) end)
end
```

### Final working code

```elixir
 defmodule Mix.Tasks.ListIndexes do
  use Mix.Task

  @shortdoc "To list all the indexes in the table"

  alias MyProject.{ Repo }
  import Ecto.Query


  def run(_args) do
    Mix.Task.run("app.start", [Repo])

    query = from p in "pg_indexes",
        select: %{index_name: p.indexname, table_name: p.tablename},
        where: p.schemaname == "public"

    table_headers = %{index_name: "Index Name", table_name: "Table Name"}
    rows = Repo.all(query)
    widest_column_counts = longest_field_values(rows)

    # To render Table header
    render_single_row(table_headers, widest_column_counts)

    # render Table header bottom  border
    IO.puts("| #{String.duplicate("-", Enum.sum(widest_column_counts) + 9)} |")

    # render Table rows
    render_multiple_rows(rows, widest_column_counts)
  end

  # Helper functions
  defp render_multiple_rows(rows, widest_column_counts) do
    Enum.each(rows, fn row -> render_single_row(row, widest_column_counts) end)
  end

  defp render_single_row(%{index_name: index_name, table_name: table} , widest_column_counts) do
    IO.write("| ")

    [index_name, table]
      |> Enum.with_index()
      |> Enum.each(fn {column_content, index} ->
          border = render_border(index, column_content, widest_column_counts)
          IO.write("#{item} #{border}")
        end)

    IO.puts(" ") #Next line
  end

  defp render_border(position_of_column, item, columns_count) do
    count = Enum.at(columns_count, position_of_column) - String.length(item)
    "#{String.duplicate(" ", count)} | "
  end

  defp find_longest_length(rows) do
    Enum.reduce(rows, [], fn row, acc ->
      Enum.zip(row, acc ++ [0])
      |> Enum.map(fn {str, len} ->
        String.length(str) |> Kernel.max(len)
      end)
    end)
  end

  def longest_field_values(records) do
    Enum.map(records, fn index ->
      [index.index_name, index.table_name]
    end)
    |> find_longest_length
  end
end
```

### Final Thoughts

This task may seem a little absurd, but my motivation was to avoid and save some clicks and switching between terminals. We can tweak the code a little further and pass flags to the command, something like `--table_name` and `--first_50/--last_50`, to render the first 50 or last 50 records of the passed \`table\_name\`. I felt like sharing this in the hope that it might benefit someone. I hope you like this blog. If you have any questions, please comment below. Thanks for reading 😊.