“Northwind Elixir Traders” updated: window queries

A new release of my Northwind Elixir Traders book on modeling databases with Elixir, Ecto and SQLite has been published.

It’s been a while since the last update–the reason is that Chapter 15 (titled “More and more-advanced queries”) seriously kicked my butt. I got stuck trying to make dynamic queries with positional bindings and scrapped half the chapter before I found a way to converge towards window queries with named bindings.

The great news is that the book is nearing completion. What’s still missing?

1. Implementing a rolling-window query that uses an SQL fragment with RANGE over order dates instead of ROWS over… rows. I kicked this to its own Chapter 16, as Chapter 15 grew to be humongous, and I saw that this query will need its own chapter, due to SQLite’s limitations (it doesn’t support INTERVAL).

2. Chapter 17 with the leftover bits and pieces that are not necessary for modeling Northwind Traders, but nevertheless good to know (Ecto.Enum for statuses, one-to-one associations, and redacting fields with sensitive data to help stay GDPR-compliant), plus a temporary switch to PostgreSQL to see that everything should still work without any other changes.

Chapter 15 brings a ton of new content, including:

• “automatic” joins across the ERD (“automatic” only after a lot of manual work) that make named bindings available for downstream queries and partitioning by up to two schemas’ IDs,
• queries that use Ecto.Query.dynamic/2 ,
• an exploration of queries with group_by/3 as a “degraded” version of queries with partitioning,
• window queries for the running total of revenues for the entire dataset and by schema,
• sliding-window queries for the rolling average (and min, max, and sum) of the entire dataset and partitioned by two (actually, three) schemas,
• a recursive disaggregation function to turn the partitioned output into consumer-friendly maps, and
• for these window queries, selectable aggregate functions and optional date filters, also with support for year or year-month combinations, so that e.g. display on a web UI becomes easier.

As the book is nearing completion, I have reviewed it up to Chapter 14 and fixed typos, as well as 1 function that I was referring to in the text, but had neglected to copy over from the codebase (thank you Benjamin !)

Furthermore, I have improved formatting so that the code on the pages is more dense, unnecessary IEx output is hidden or wrapped, and formulas entered in IEx are copy-pasteable into your terminal without selecting “\” characters that were being introduced by pandoc to wrap lines.

Once the book is finished, I will mirror my local git repository of the codebase to a public Github repository under the Apache-2.0 license. Also, I will go through all pages and hyperlink every mention to an Elixir function to the relevant documentation page, so that you can easily refer to the functions while you learn, and perhaps play around with the functions’ opts.

• 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