WIP, coming soon – importing data from the original Northwind Traders database to the Nortwind Elixir Traders database, and running queries on it.
(BTW, I can imagine that Northwind Traders must have been a veiled reference to or an inside joke about Windows NT.)
Hello everyone,
First off, apologies for this update taking longer than usual, even despite my recent expectation that this update would come earlier, rather than later in last week. Life got in the way, and then I also got stuck with attempting to implement a clever-and-complicated solution to one of the things in this chapter using Enum.reduce_while/3 (after learning some cool things while working on IdenticonSvg, I unsuccessfully tried to construct a dependency tree between tables).
Still, this update is a medium-large one. 37 new pages have been added, and they cover using a dynamic Ecto repository to temporarily connect to a different SQLite database, inserting data in bulk, and various bits and pieces of Elixir that aren’t directly related to Ecto, but useful when writing Elixir applications and avoiding the hard-coding of data within them, regardless, such as the :application, Application and Module modules, the Kernel.apply/3 function, and the use of the __MODULE__ attribute.
With half of the tables having been modeled to a satisfactory degree over the past 9 chapters, the content of this chapter finally allows us to gradually import data from the original Northwind Traders database to tables as we gradually model them. With some laziness-driven automation through the DataImporter module, this will make such tasks in the next chapters fly by.
In the next chapters we can proceed with modeling the rest of the tables rapidly. My interim goal is to have all of the original dataset imported, and thus be able to execute queries both complex and simple. After the “basic” task of having the entire database implemented using Ecto, the goal is to branch out and explore the fringes a bit; consider improvements to the database to take it further, using embedded schemas and Enums, and other nooks and crannies of Ecto.
I’ve marked the completion degree as 28% but it’s closer to 75% realistically. Then again… famous last words. Pretty sure that the head shall meet the desk percussively and repeatedly many times over the next weeks.
As always, your feedback is welcome!
And, if you’re enjoying the book, I would appreciate if you would leave a comment on this thread, share a link to https://leanpub.com/northwind-elixir-traders on the social network of your preference, or anything else that you find meaningful to get more people to get the book and provide feedback.
Cheers,
Isaak
Oh ok. Thanks. 
New chapter dropping sometime around (my) midnight tonight. This one’s will be on modeling the remaining tables, except for the OrderDetails “join table” (which we’ll deal with in a future chapter on many-to-many relationships).
This one seemed simple at first, but once you’ve seen the quality of data of the :phone fields in the original Suppliers and Shippers table, you’ll realize that a custom validation function is needed.
As a preview: this is a regex for NANP-formatted phone numbers, tested on all phone numbers in the Northwind Traders database: https://regexr.com/80rgh.
Good thing that the Suppliers table includes a :country field, so that we can make sense of the mess!
Stay tuned. That’s going to be a fun one to write!
Hello, hello,
The first part of Chapter 11 on modeling further tables is now available.
It’s only “the first part”, because this chapter truly kicked my butt, causing a couple of partial rewrites.
It started off uneventfully, with the main goal of modeling the remaining three tables (OrderDetails is temporarily left aside). Yet, it rapidly escalated to dealing with the phone numbers of the Suppliers and Customers tables of the original Northwind Traders database, with GETting and parsing a CSV file containing ISO 3166-1 information including dial codes (for eventually formatting and validating phone numbers as internationally-formatted, dial-code-prefixed strings).
BTW, did you know that the Dominican Republic is the only country with three different dial codes? That one was a fun one to discover, as was dealing with columns in a CSV file that contain a comma-separated list of values that break simplistic String.split/2 calls when parsing a CSV row.
Other things that this chapter includes:
GET request using Erlang’s :httpc client.Part 2 of this chapter is coming within the next 4-5 days.
Cheers,
Isaak
Hello again from Athens, Greece!
This release completes Chapter 11, which proved to be the longest so far, despite initial appearances that it would be rather straightforward.
Chapter 11 has seen a meticulous approach to table modeling within the Northwind Elixir Traders (NET) database. We revisit our methodical SIPOC’ed process of table creation four times, adapting it for each table’s unique needs beyond the OrderDetails table, which was reserved for future query capabilities via Ecto.Query.
The focus in this chapter (and where most of the content is spent) shifted to data integrity and internationalization for Shippers and Suppliers tables with phone number fields. By creating a custom changeset validation function using helper functions, we automate conversions of NANP-formatted numbers into international formats across all pertinent tables, including the development of associated modules that model Country records, and the PhoneNumbers module that includes various helper functions for phone number processing. Another fruit of this labor is a country validation function that uses publicly available ISO 3166-1 data on CLDR display names (“Greece”), Alpha-3 codes (“GRE”), and dial codes (“+30”).
The various custom validation functions have now been refactored in a separate Validations module, to that they can be used across various modules’ changeset functions.
Finally, the chapter also involves rudimentary exploration of the original Northwind Traders dataset as an investigation and source of discussion for potential improvements to the NET database schema to better match the practical needs of a real-life B2B business.
We are thus now at the cusp of modeling the last table of NT, OrderDetails, and thus leaving the original database behind–for good, and eventually, for better!
The next chapter will do exactly that, before we proceed to Ecto queries and other Ecto topics, such as embedded schemas, Enums, and custom field types. These latter elements will be part of the closing chapters of the book that will take NET beyond the pedagogical status of NT and well into something that could serve as a starting blueprint for a real-life B2B business (which is informed by my professional experience as the Managing Director of TECTRA Ltd, where my business programming journey began).
As always, I welcome your feedback! And if you have been enjoying the book, I would appreciate if you could send me a short testimonial and profile photo by email to isaak@overbring.com, and your permission to add that to the book’s testimonials on Leanpub.
Until next week!
Isaak
Hello dear readers of Northwind Elixir Traders,
After a long break due to other professional engagements (three of which, happily Elixir-related!), I bring you a short new chapter on modeling the final table of the original Northwind Traders database: the OrderDetails table. Plus, minor improvements to the previous chapter.
With that final table in place and all data imported thanks to the DataImporter module continuing to pay dividends, the main mission of the book is complete! We now have a “Northwind Elixir Traders” database in place, with all the original data available and ready to be accesssed with queries.
That doesn’t mean that the development of the book is over, however. Beyond queries, I want to use the next chapters to:
As always, thank you to everyone who has provided feedback over email and Linkedin!
Best regards from Athens, Greece,
Isaak
isaak@overbring.com
PS: I have extended the 10% discount by another 10 copies for Elixir Forum readers. Use this link: https://leanpub.com/northwind-elixir-traders/c/elixirforum
Hello everyone, and especially readers of Northwind Elixir Traders!
This is just an update that I expect to be getting back on completing the book around mid-November, since I’ve been fully loaded with both Elixir-related and non-Elixir-related work since mid-July that made it impossible to maintain the weekly/bi-weekly writing pace.
At the same time, it’s great to see that the book has been so warmly received – so, in case any readers have feedback, feel free to share it here so that I can take it into account once I get back to writing in November!
The upside of the lull in writing is that I’ve been putting in practice everything in the book, plus Ecto queries (a pending chapter of Northwind Elixir Traders) while developing a Phoenix LiveView “sister app” to Breek.gr (which is written in NextJS with a PHP backend) – check it out:
And yes, Managers uses SQLite. And, it makes heavy use of my ExNominatim Elixir library for interacting with the OpenStreetMap Nominatim API.
Stay tuned!
(PS: I much prefer Phoenix LiveView to NextJS 
)
Will you be covering any PostGIS material? Doing queries by lat long, etc?
PS I bought the book yesterday, I’m already 75 pages in. I love the style of starting with the very basics and discovering the way forward via necessity.
Thank you Bryan for the feedback here and over email and LinkedIn! PostGIS is way beyond the scope of this book, as Northwind Elixir Traders uses SQLite as the database.
Hello everyone and Happy New Year!
I hope that 2025 finds you healthy and personally and professionally fulfilled, and ideally with more Elixir coding than in other programming languages
As Northwind Elixir Traders is nearing completion, I have reworked the first 12 chapters, fixed various typos, added clarifications, and improved the wording in a few sections. The PDF file is now generated with a new theme and a smaller (and more consistent) font size that affords more space for code on each page.
The latest version includes Chapter 13 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 following graph of dependencies between the tables of the ERD of Northwind Traders:
The next chapter drops within the next week and will be covering Ecto queries.
Until then, cheers from Greece,
Isaak
Chapter 14 on Ecto queries on the Northwind Traders toy dataset is dropping very soon within the week, possibly within the next 24 hours.
It is about Ecto queries primarily, but also includes sidequests on parallelization of queries (without a significant benefit, since we’re IO-bound), and dealing with money amounts (the type of the :price field was deliberately set as :float in the definition %Product{} schema many chapters ago and you can imagine what happens when we use floats in calculations of, e.g. the total value of an Order). It also includes some more insights about development (waterfall, agile, the sane middle ground, and a few other hints from my experience), and some experience-derived parts on what kinds of queries tend to be useful for a business like the fictional Northwind Elixir Traders aiming to grow its revenues.
Here’s an outtake of the development process 
, which has been kitten-assisted as of ~4 weeks ago. (Kindly do ignore the mouspad and all the garish RGB lights…)
After Chapter 14, there is not much more left to discuss. The last couple of chapters will investigate whether our application still works if we switch from SQLite to Postgres, and some ideas for taking this project further.
Stay tuned!
Hello readers both current and prospective,
Lo and behold, I bring you sweet tidings of great progress!
Northwind Elixir Traders 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.
The biggest change since the last update is 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!
Once again, if you particularly enjoyed the book and would like to contribute a testimonial for the Leanpub page, email me or connect with me on LinkedIn. My contact details are in the book.
Enjoy Chapter 14, and until very soon again,
Isaak
Hello there database admins of the Northwind Elixir Traders business,
It’s been a while since the last update–the reason is that Chapter 15 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?
Implementing a rolling-window query that uses an SQL fragment with RANGE over order dates instead of ROWS over… rows. I kicked this to Chapter 16, as Chapter 15 became enormous, and I saw that this query will need its own chapter, due to the limitations of SQLite (it doesn’t support INTERVAL).
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 Postgres to see that everything should still work without any other changes.
Chapter 15 brings a ton of content, including:
dynamic/2,As the book is nearing completion, I have reviewed the entire book 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!) There is now also an index of figures with clickable links to all functions implemented throughout.
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.
I am confident that I can bring you the 100% completed book by end of February.
Once the book is finished, I will mirror my 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.
Thank you for supporting the book while still in development. If you are open to writing a testimonial for the inside of the book, its landing page, and wherever else it will be published (wink wink nudge nudge), please email me at isaak@overbring.com.
That’s all for now–expect to receive one or two more updates within the next 10 days.
Cheers from Athens,
Isaak
Hello everyone,
A new release of the book has just been published. Here’s what has changed in the last 16 days:
hunspell -d en_US on all Markdown source files, so there should now be no (or close to no) spelling mistakes left.As somewhat of a perfectionist, I wouldn’t let Chapters 14 and 15 stand as they were, but this has delayed work on Chapter 16. I’m resuming work on it tomorrow. It’s expected to be a tough one, as it involves writing CTEs to deal with SQLite’s missing support for INTERVAL. Let’s see if I can finish within the coming week. In this release I’ve also included the first few pages of that chapter.
Thank you all for your patience, feedback, and patronage–it has all made this book something that I could never envision when I started working on it around a year ago!
Cheers from Athens, Greece,
Isaak
What a changelog. I admire your dedication to this work.
This is the kind of feedback that makes this kind of dedication manifest 
Thank you!
Hello and welcome to version 1.0 of Northwind Elixir Traders – now with a new subtitle!
Since the last update:
ROWS in a window fragment to perform window-aggregate calculations for rolling and running (sliding and expanding) windows across sequential and unique dates of dataset with triple partitioning–even for those dates that have no orders. There is also a lot of exploration of SQLite’s troubles with complex queries of that kind, as well as a section on profiling database queries with SQLite’s EXPLAIN QUERY PLAN (and with Elixir and Erlang’s :timer module) to determine what’s causing them to run slow.Ecto.Enum, redacting fields, and one-to-one associations) compared to what the book has covered in almost 500 pages, or was always truly beyond the scope of the book (switching to PostgreSQL, which shouldn’t pose an issue given how everything has been implemented).With this, the book is now 100% complete!
For those of who have have been asking me for the complete codebase, it’s now available and Apache-2.0 licensed: GitHub - waseigo/northwind_elixir_traders: The official repository of the "Northwind Elixir Traders" book. Every code block and every instruction to change something to the code corresponds to a git commit. The commit title is the title of the corresponding code block, or a snippet of the text with the instruction on what to change/remove.
The entirety of the book has now been “replayed”–that’s how the git repo has been built from scratch by following the book step by step. Still, to err is human, so should you get stuck anywhere, please let me know.
Thank you again for your patronage and for those of you who have contributed comments and suggestions, asked questions, and requested clarifications. It has made the book way better than I ever imagined!
The next step in the story of this book is the upcoming episode of the Elixir Mentor podcast (Podcast · Elixir Mentor) with Jacob Luetzow (jacobluetzow). We’ll record it on March 21st.
The next step for me, Elixir, and writing technical books is the upcoming and much shorter Elixir Chatbot Alchemy, in which we will follow a similar exploratory approach to develop a self-hosted chatbot with Elixir, the Ollama REST API, Ecto, and Phoenix LiveView under the severe constraint of getting a decent chatbot working with only CPU-based inference.
If you enjoyed the book (or even if you didn’t!), drop me an email or contact me here–I look forward to hearing what you liked, what you didn’t, and anything else you’d like to share!
Best regards,
Isaak
Awesome to see this gaining more popularity.
Thank you for Ecto SQLite3!
© Copyright Elixir Forum | Terms | Privacy & Cookies
