Northwind Elixir Traders
Northwind Elixir Traders
Learn Elixir and database modeling with Ecto and SQLite, all in one project
About the Book
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.
Step back into the nostalgic realm of 90s database exploration and learning with a modern twist! Remember the "Northwind Traders" database from the early days of Microsoft Access? It's back and ready for a reimagining in the dynamic world of the Elixir programming language and its Ecto database layer.
Join me on a learning exploration as we transform the familiar "Northwind Traders" into the captivating "Northwind Elixir Traders" through database migrations, table alterations, schema definitions, CRUD operations, references, queries, changesets and the core of what you need to use Ecto productively.
This isn't your typical Elixir or Ecto tutorial. Instead of pursuing the usual "happy path" reflected in the official documentation and "Getting Started" guide, we'll dive into the complexities and nuances of database implementation, offering a refreshing exploration that goes beyond the ordinary.
Our adventure begins by embracing the simplicity of SQLite over the conventional choice of PostgreSQL. This deliberate trade-off in favor of simplicity against feature-completeness introduces challenges and roadblocks that will enrich your learning experience as we navigate through the intricacies of schema design, migrations, primary and foreign keys, relational structures between the different tables, and queries that return meaningful insights.
Discover the joy of experimentation as we encounter unexpected hurdles and exercize our Elixir skills in figuring out why things don't work as expected, and how to still get things done, regardless. Through these challenges, you'll gain invaluable insights into problem-solving and critical thinking with Elixir and Ecto.
Embrace the ethos of fearless exploration as we delve deeper into Ecto's capabilities, focusing on the art of reading documentation and adapting to unforeseen obstacles. With each chapter, you'll elevate your understanding of Ecto, empowering yourself to tackle real-world database projects with confidence and finesse instead of with copy-paste operations and hopes and wishes that it all works out in the end.
For a curious Elixir newcomer intending to improve their skills in the programming language itself and/or in modeling databases for a microservice, a backend, or a Phoenix or Phoenix LiveView app using Ecto (and any supported database, beyond SQLite), "Northwind Elixir Traders" promises an immersive learning experience that's both enriching and unforgettable.
Reader Testimonials

Petros Papapanagiotou, PhD
Head of Development
This rare gem of a technical book is a must-read for anyone wanting to build practical skills in modeling databases with Elixir and Ecto. But it’s not only about coding—it also emphasizes real-world problem-solving, learning by doing, and tackling development roadblocks head-on with confidence, in a relentless pursuit of understanding. Truly, an invaluable resource for everyone tired of cookie-cutter tutorials and theoretical fluff!

Benjamin Gandhi-Shepard
Designer / Developer at Solvm
With this excellent book about modeling data with Ecto and SQLite, I was up and running quickly without snags! I learned how SQLite compares with PostgreSQL and how to work between their differences. Despite Northwind Traders being a toy database, the modeling we do in the book is top notch. After thoroughly modeling the Northwind Traders DB with Ecto, we then craft a gang of queries to glean excellent business "insights" within the data. The author has a strong understanding and passion in this area of development and it shows. Northwind Elixir Traders is an excellent read and a phenomenal Elixir resource.
Table of Contents
- Before we begin
- About this book
- Our journey along the book’s chapters
- Software versions used
- License of the code in this book
- Typographic conventions
- Foreword
- Preface
- Chapter 1: Our objective, the application and the repo
- Creating the repository (the “repo”)
- The need for a supervision tree
- Summary and outlook
- Chapter 2: Creating the database schema
- Building the Northwind Elixir Traders database
- Preparing and using the data structure of a Category
- Persisting data successfully
- Deleting records
- Basic queries
- Auto-incrementing integer primary keys
- Summary and outlook
- Chapter 3: Refining the table schema
- More options for the table
- To create is to destroy; to alter is to preserve
- Investigating the impact of our alterations
- Summary and outlook
- Chapter 4: Introducing constraints
- Constraints with SQLite
- Considering database portability
- Changesets: intended changes to data
- Changesets and validation rules
- Turning validation errors into helpful messages
- Summary and outlook
- Chapter 5: Changesets in modules with schemas
- Custom validation functions
- More than one changeset function
- Creating another migration
- Conditionally persisting data in the repo
- Summary and outlook
- Chapter 6: Uniqueness constraints
- Unique indexes and unique constraints
- New insights about Ecto and SQLite
- Ecto migration rollbacks
- Summary and outlook
- Chapter 7: Basic table associations
- Fields with numbers
- A one-to-many association using a foreign key
- Dealing with SQLite’s limitations on foreign key constraints
- A custom validation function for a foreign key constraint
- Summary and outlook
- Chapter 8: Associations with Ecto.Schema
- Interlude: exploration vs. exploitation
- The N+1 query problem vs. Ecto’s
preload/3
- One-to-many relationships and
has_many
- Creating the flip-side of a one-to-many relationship
- Improving upon an existing association
- Summary and outlook
- Chapter 9: Casting and putting associations
- Casting an associated field (unsuccessfully)
- Putting an association
- Creating a new record using
cast_assoc/3
- Casting the reverse association
- Summary and outlook
- Chapter 10: Importing data from a dynamic repository
- Fetching the original Northwind Traders data
- Using a dynamic Ecto repo
- Implementing a data importer module
- Importing in bulk (is cheaper)
- Automatically prioritizing tables for importing
- Validating that our data importing is robust
- Summary and conclusion
- Chapter 11: Modeling further tables, and data cleansing
- SIPOC of the task of modeling a table
- Data cleansing: dealing with phone numbers
- Side-quest: importing data from an online CSV data source
- Extracting country names and dial codes
- Creating the countries table
- Finalizing our automatic generation of the
countries
table
- Converting phone numbers to the international format
- Creating a validation function for phone numbers
- Tearing down the database and re-importing data
- Modeling the Customers and Orders tables
- Side-quest:
DATETIME
orDATE
in theorders
table?
- Defining the new
Order
module
- The cherry on top: country name validation
- Reaping the rewards: enabling the
has_many
calls
- Quality-assuring the entire importing process
- Summary and outlook
- Chapter 12: Modeling a join table
- Using
belongs_to
andhas_many
- Importing the OrderDetails table data
- Using
many_to_many
- Summary and outlook
- Using
- Chapter 13: Cleaning up
- Discovering some loose ends of our table prioritization
- Building a proper table dependency graph
- Implementing a Depth-First Search algorithm
- Summary and outlook
- Chapter 14: Insights from data with queries
- Taking inspiration from a real situation
- Elixir functions vs. database functions
- Simple joins
- The woes of money amounts and floating-point numbers
- Side-quest: timing the execution of functions
- Side-quest: rudimentary parallelization using the Task module
- Floating-point values for prices? No, thanks.
- Converting dollar prices in our database to cents (integers)
- Completing the switch from dollars to cents
- Back to querying data and answering questions
- Using a sub-query to create a composable query
- Side-quest: customers’ share of revenue, and business resilience
- Calculating the Gini coefficient
- Calculating the revenue share of any entity, Part I
- Virtual fields and fragments
- Calculating the revenue share of any entity, Part II
- The vital few vs. the trivial many
- It’s not only about revenues
- Making our code more flexible
- Time filtering
- Implementing a date(time) filter in our queries
- Side-quest: switching from positional to named bindings
- Back to the date(time) filter
- Improving the date filter for monthly and annual breakdowns
- Calculating metrics over time
- Revenues and quantities by the customer’s country
- Summary and outlook
- Chapter 15: More and more-advanced queries
- Window functions
- Dynamic queries with window functions
- Dynamic joins and the pains of positional bindings
- Using named bindings to traverse the ERD
- Refactoring query logic for consistency and composablity
- Back to the dynamic window query—for real this time
- Expanding-window queries
- Using the date-filtering feature once again
- Historical running total of revenue or quantity per customer, or anything else
- Generalizing our window query
- Sliding-window queries
- Generalizing the subquery of the sliding-window query
- Making the sliding-window query more flexible
- Side-quest: plotting
:agg
vs.:date
in IEx
- Single partitioning
- Partitioning at the right place, and to the right degree
- Double (actually, triple) partitioning
- Tying loose ends, and fulfilling a promise
- Summary and outlook
- Chapter 16: Very complicated window queries
- Adding zero rows
- Recursive Common Table Expressions (CTEs)
- Recursive CTE for a sequence of dates
- Implementing the recursive CTE in Ecto
- Getting
Order
rows without actual orders by using aLEFT JOIN
- Using the recursive CTE with other queries
- Coalescing data to turn
NULL
into 0
- The Holy Grail: Rolling and running
n
-day aggregation with partitioning
- Performance woes, and indexing
- How to (not) make the queries’ foundation less blatantly expensive
- Summary and outlook
- Chapter 17: Towards “Northwind Elixir Traders 2.0”
- Ideas and exercises
- Summary and outlook
- About the Author
- Before we begin
Other books by this author
The Leanpub 60 Day 100% Happiness Guarantee
Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.
Now, this is technically risky for us, since you'll have the book or course files either way. But we're so confident in our products and services, and in our authors and readers, that we're happy to offer a full money back guarantee for everything we sell.
You can only find out how good something is by trying it, and because of our 100% money back guarantee there's literally no risk to do so!
So, there's no reason not to click the Add to Cart button, is there?
See full terms...
Earn $8 on a $10 Purchase, and $16 on a $20 Purchase
We pay 80% royalties on purchases of $7.99 or more, and 80% royalties minus a 50 cent flat fee on purchases between $0.99 and $7.98. You earn $8 on a $10 sale, and $16 on a $20 sale. So, if we sell 5000 non-refunded copies of your book for $20, you'll earn $80,000.
(Yes, some authors have already earned much more than that on Leanpub.)
In fact, authors have earnedover $14 millionwriting, publishing and selling on Leanpub.
Learn more about writing on Leanpub
Free Updates. DRM Free.
If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).
Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.
Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.
Learn more about Leanpub's ebook formats and where to read them