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.

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.

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

  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.

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.

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:

[
  %{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.

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.

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.

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.

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

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 .

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

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.

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

 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 😊.