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
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.
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))
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(" ")
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)
render_single_row(table_headers, widest_column_counts)
IO.puts("| #{String.duplicate("-", Enum.sum(widest_column_counts) + 9)} |")
render_multiple_rows(rows, widest_column_counts)
end
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(" ")
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 😊.
