Northwind Elixir Traders (PragProg)

PragmaticBookshelf

Forum Sponsor

Isaak Tsalicoglou

Built for the curious, this isn’t your average programming book—it’s nearly 500 pages of hands-on mentorship beyond coding, fusing core skills in Elixir, Ecto, and SQLite with business experience and R&D tenacity. Think of it as a $500 course distilled into one powerhouse resource, with tons of learning-by-doing, in a single project. Acquire hard skills in Elixir and database modeling with Ecto’s migrations, changesets, and more, re-imagining a classic database that millions around the world have used before for learning. By fearlessly ditching the “happy path”, this immersive, exploratory, memorable, project-based tutorial helps you confidently tackle real-world projects with Elixir and Ecto.

Built for the curious, Northwind Elixir Traders isn’t your typical programming tutorial. This immersive, project-based adventure reimagines the classic Northwind database, guiding you through Ecto’s migrations, changesets, and queries to model data with real-world edge. Ditching the “happy path,” it’s one ambitious journey—crafting a schema step by step, wrestling with SQLite’s quirks, and automating bulk imports, all while sharpening Elixir skills via side-quests like refactoring and systematic problem-solving. Drawing from the author’s R&D and B2B equipment-trading experience, it’s nearly 500 pages of hands-on mentorship—think Elixir gym crossed with an engineer’s playground.

You’ll transform an Entity Relationship Diagram into code, tame messy data, and unearth insights with queries both simple and complex. It’s not about exhausting Ecto’s every nook; it’s about grokking the essentials through practice, fearless experimentation, and diving into docs when things don’t work according to intuition or expectation. SQLite spices up the challenge, but the skills you’ll build also apply to working with PostgreSQL. Sprinkled with mini-essays from the author’s experience, it builds a gritty mindset to confidently tackle real-world projects—no copy-paste prayers needed. For Elixir newcomers, database dabblers, or those looking to build the data foundation of their Phoenix LiveView app, this is a speed-run in upskilling, wrapped in an enjoyable and unforgettable exploration.

This work was written and produced entirely by the author. We are proud to be distributing it.

Isaak Tsalicoglou is a mechanical engineer, management consultant, and software engineer. After a corporate decade in Machine Learning in turbocharger R&D, development processes, and product management of portable radar devices, since 2020 he has been managing SaaS and B2B trading businesses in Greece, and developing open-source and commercial software in Elixir, Python, Go, PHP, and NextJS.

Full details: Northwind Elixir Traders: Learn Elixir and database modeling with Ecto and SQLite, all in one project by Isaak Tsalicoglou

Don’t forget you can get 35% off with your Devtalk discount! Just use the coupon code “devtalk.com" at checkout

77 comments

#ecto #pragprog #sqlite #published-book #pragmaticbookshelf #book-northwind-elixir-traders

4 3637 77

2026-07-23 07:56:09 UTC

Showing Posts 31 to 40

Show Best Posts
Show All (oldest first)
Show All (newest first)

waseigo

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

Post #31

waseigo

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!

Post #32

waseigo

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

Post #33

waseigo

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:

“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 dynamic/2,
an exploration of queries with group_by 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 schemas, and
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 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

Post #34

waseigo

Hello everyone,

A new release of the book has just been published. Here’s what has changed in the last 16 days:

Chapters 14 and 15 have been rewritten almost from scratch, following reader feedback. The resulting functions are now way more composable, and the logic behind their gradual development and “stacking” has been made more clear.
Some parts of Chapter 15 were moved to 14, where they were needed earlier, also to avoid rework in 15, and to make the build-up towards composable window queries more straightforward.
Everything until and including Chapter 15 has been “replayed”. This includes all IEx code snippets, and all migrations, modules, functions and changes made throughout.
As I’ve been replaying all chapters so far, I’ve been building a local git repository from scratch. Every change to the codebase is shown in a figure, and every figure and every small change corresponds to a commit.
Once Chapters 16 and 17 are completed and quality-assured, the git repo will be made public on GitHub and licensed under Apache-2.0. I will also see how feasible it is to link every figure to the corresponding git commit.
Almost all Elixir function names are now hyperlinked to their corresponding online documentation. Any remaining stragglers will be fixed until 100% completion.
I started using hunspell -d en_US on all Markdown source files, so there should now be no (or close to no) spelling mistakes left.
The PDF is now 461 pages long (actual content: 453 pages). It includes so many side-quests and explorations of Elixir features besides Ecto that I’m considering changing the subtitle to “Learn Elixir and how to model databases with Ecto and SQLite in one fell swoop” or so. For sure, if you enter this book with basic knowledge of Elixir, you come out way stronger out of it.

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

Post #35

arcanemachine

What a changelog. I admire your dedication to this work.

Post #36

waseigo

This is the kind of feedback that makes this kind of dedication manifest Thank you!

Post #37

waseigo

Hello and welcome to version 1.0 of Northwind Elixir Traders – now with a new subtitle!

Since the last update:

Chapter 16 was written from scratch. We now have complex (complicated) queries that use Common Table Expressions and 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.
Chapter 17 (“Leftover bits and pieces”) was dropped, since it seemed too “poor”, especially for a closing chapter. After reviewing the book in its entirety, I concluded that everything that was supposed to be in there was either truly trivial (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 a handful of exceptions, now all Elixir, Ecto and SQLite functions have been hyperlinked to their online documentation. I considered hyperlinking the book contents to the git commits, but after trying it out I found that it made already-busy book pages even busier, hindering readability.

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 · GitHub. 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

Post #38