Blog
My Northwind Elixir Traders book on modeling databases with Elixir, Ecto and SQLite is practically completed in terms of content–I am currently working on QA of chapters 15 and 16, but everything else is done. I expect my QA to conclude within the next couple of days.
These two remaining short chapters will cover queries with window functions, as well as some leftovers, such as using Ecto.Enum, redacting fields with sensitive data, modeling one-to-one associations, and finally checking whether the application also works with Postgres instead of SQLite.
In an earlier update 10 days ago, I reworked the first 12 chapters, fixed various typos, added clarifications, and improved the wording in a few sections. The Chapter 13 was also added. It focuses on tying some loose ends created by an oversimplified prioritization algorithm in an earlier chapter for determining the order of importing (or tearing down) tables with the DataImporter module’s functions. Though not central to working with databases, we debug and fix the prioritize/0 function by reimplementing it with a Depth-First Search algorithm on the graph of dependencies between the tables of the ERD of Northwind Traders.
The biggest change since then is the entirely new Chapter 14 on queries. Like a few other chapters before, this too had a big sidequest: dealing with the consequences of the price being originally modeled as a :float in the Ecto schema of the %Product{}. After investigating what this causes when we calculate aggregate values of orders’ revenues, we use SQL in a migration to convert the dollar prices to cents and another migration to change the field type to :integer.
We learn to build increasingly complex queries with both join: … and left_join: …, and use group_by and order_by to build subqueries and dynamic queries. All this allows us to calculate the different entities’ (Customer, Product, Category, Employee, etc.) share of revenues, also in a cumulative manner. Thus, we even manage to calculate the Gini coefficient of such data pairings!
Besides that, we explore Elixir’s parallel execution capabilities using the Task module, and the pointlessness of running I/O-bound queries in parallel. We also iteratively refactor the new Insights module’s function, so that our queries are composable. Throughout the chapter we run numerous queries to derive insights and, not leaving the context of the application behind, we also discuss what could motivate the implementation of such queries we write.
There is now also a Chapter 17 with ideas for where you could take the application with improvements and extensions to the database and the Elixir code.
Thank you to the many readers who gave this book a shot since its infancy in April 2024, and especially to all those who provided feedback. Without your patronage, this book would not have grown to 300+ pages over almost a year!
- The book is available as PDF and ePub on Leanpub
- Join the discussion on elixirforum.com
- Leanpub invited me to a short interview about the book