This is a book for curious people. Learn about working with databases in Elixir by using Ecto’s migrations, changesets, and more to reimagine a classic database that millions around the world have used before for learning. By not pursuing the “happy path”, this immersive, memorable tutorial will help you to tackle real-world projects fearlessly.

Critical acclaim

“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!” – Petros Papapanagiotou, PhD / Head of Development

What’s the book about?

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.

Who is this book for?

This book has been written for anyone who already possess some Elixir programming skill and wants to understand Ecto better to a degree that will allow them to model any kind of data with these two technologies.

You will learn about incrementally converting a database schema to Ecto structures and writing the code necessary to manage the data therein, gradually (and retroactively, and iteratively) refining the database model with constraints, changesets and associations and helper functions, and working with dynamic repositories to import, validate and sanitize data, and making queries to generate insights.

This isn’t your typical 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 SQLite3 over the conventional choice of Postgres. 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 delving into the world of building databases for a microservice, a backend, or a Phoenix or Phoenix LiveView app, “Northwind Elixir Traders” promises an immersive learning experience that’s both enriching and unforgettable.

What kind of problems will you learn to solve?

On a high level, you will learn to solve problems such as:

• Thinking through an Entity Relationship Diagram (ERD) and gradually transforming it into Elixir/Ecto code.
• Making decisions about data types when having to deal with existing data that isn’t necessarily “clean”.
• Dealing with SQLite’s limitations that make us stray off the beaten path of Ecto’s excellent documentation.
• Automating the importing of data in bulk from another database and verifying its data quality.
• Thinking about real-world boundary conditions when improving a business database like this one.

Especially the latter is informed by my experience in running, digitalizing, and growing a B2B industrial equipment trading business, for which in recent years I have implemented various small pieces of software that are related to the modeling of a database like this one, and much more.

What else will you learn?

My primary goal for you is to understand how to use Ecto productively. However, through numerous “sidequests” we will exercise Elixir skills that take you far beyond just learning to utilize Ecto. We will use whatever is required to accomplish our objective. This includes Enum.reduce and Enum.reduce_while, use vs. import, basic streams, basic stuff from the Application, Module, Supervisor and Task modules, and even some parts of Erlang’s library, such as the :httpc module to make GET requests and import CSV data from an online source, or :tc for timing the performance of our queries. We will even implement a Depth-First Search algorithm to determine the order in which tables should be imported from the original database. If something could aid us in reaching our goal, we will try it out.

Therefore, my secondary goal for you with this book in terms of Elixir programming skills is to enter with basic knowledge and exit with strengthened skills as a side effect. Nothing works as well as hands-on practice and repetition for learning, and this book is like an Elixir gym.

My tertiary goal with Northwind Elixir is to bolster your confidence and expand your research and development skills by helping you acquire an engineer’s exploratory mindset and grit in problem-solving. There are numerous references and side discussions and even mini-essays/asides scattered across most chapters that aim to transmit to you some parts of my experience in the R&D of products of different kinds. Therefore, you will also learn about:

• the mentality and tools of working through a complex problem with pragmatism and an eye on managing complexity,
• the gradual implementation of a pedagogical example in a programming language that I absolutely love, and
• the engineer’s mindset of dealing fearlessly with obstacles as they arise and aiming to deeply understand what’s going on, before rolling up your sleeves to deal with them.

What’s so special about this book?

Those of us who were around in the 90s and learned some things about databases by playing with the earliest versions of Microsoft Access might remember the “Northwind Traders” database.

This simple database, complete with mock data across 8 interconnected tables, models the commercial operations of a business and serves as grounds for experimenting with table schemas, primary and foreign keys, one-to-many relations, and even a many-to-many relation.

It’s a simple, didactically well understood example that I am using as the basis of taking the “Northwind Traders” database from its initial state to an implementation with Elixir and Ecto as the database of “Northwind Elixir Traders”, complete with the original data and many different data analyses using Ecto queries.

The difference between this book and many other books, and compared to the few other books on Ecto, is that I’m not pursuing the “happy path”, which is why the book ends up being less dry and formulaic, and more an exploration of Ecto that should make the content more memorable than showing exactly what to do to get the results you want, but leaves you SOL when things don’t work as you intuited.

In particular, instead of going with Postgres, the default option for Ecto, I choose SQLite3, making a conscious trade-off in favor of simplicity against feature-richness. This means that not everything ends up working as you expect them to, because e.g. SQLite3 doesn’t support ALTER COLUMN (thus rendering some Ecto.Migration functions useless), or doesn’t respect the column’s :size option, or stores dates as TEXT, etc.

This means that the book teaches you to

1. make a conscious tradeoff in favor of simplicity instead of feature-completeness in terms of the database technology choice,
2. define things as if your database supported everything Postgres supports, so you still know what is possible with Ecto,
3. discover what doesn’t actually do anything or as much as you expected based on Ecto’s docs when you use SQLite3,
4. understand that it doesn’t really matter in terms of implementation (and learning!), and that you might have been too “smart” about it,
5. go one abstraction layer higher and work on #Elixir code with Changesets and the Repo instead of fussing over database technologies as if you’re part of the Postgres core team and/or building the next Github (famously running on MySQL).

Ultimately, the book teaches you to value reading documentation to inform yourself and figure things out without fretting, to fearlessly plow ahead even when things don’t work according to the “happy path” in the documentation, and to thus gain a deep understanding of Ecto.

Why not simply study Ecto’s documentation or follow other tutorials?

This isn’t your typical Ecto tutorial–instead of pursuing the usual “happy path” reflected in its official documentation and “Getting Started” guide, we’ll dive into the complexities and nuances of database implementation for business purposes, offering a refreshing exploration that goes beyond the ordinary and the expected.

Our adventure begins by embracing the simplicity of SQLite over the conventional choice of Postgres. This deliberate trade-off in favor of simplicity against feature-completeness introduces challenges and roadblocks that will enrich your learning experience as we navigate the intricacies of schema design, migrations, primary and foreign keys, contraints and validation functions, relational structures between the different tables, importing data from existing databases, and executing 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 achieve our goal, regardless. Through these challenges and related “side-quests”, you’ll gain invaluable insights into problem-solving and critical thinking with Elixir and Ecto. In hte process, you will also learn more about the highly versatile and popular SQLite database.

Embrace the engineer’s ethos of fearless exploration and systematic problem-solving 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 projects with confidence and finesse, instead of with copy-paste operations and “hopes and prayers” that it all works out in the end.

For a curious Elixir newcomer getting into the world of building databases for a microservice, a backend, or a Phoenix or Phoenix LiveView app, Northwind Elixir Traders confidently promises an immersive learning experience that’s both enriching and unforgettable.

How is this book different to other books on databases and Ecto?

We will explore the subject matter with a hands-on, outcome-focused approach of gradually modeling an existing pedagogically-mature database schema and by venturing on sidequests to gain a solid understanding of Ecto’s “gotchas” and the ramifications of using Ecto with SQLite instead of the default choice of Postgres.

Different to a book that takes a more “completionist” approach, Northwind Elixir Traders starts with a specific objective in mind and helps you work towards it by using only the elements of Ecto that are required for the subtasks of achieving the objective, without pursuing the coverage of the entirety of Ecto’s functionality as a reference manual. Databases and Ecto, combined, are an immense field of knowledge. If you have basic skills in those topics and want to get some practice in Elixir too, this is the book for you.

This book fosters truly “grokking” Ecto through practice and experimentation, and by exploring why things sometimes don’t work as one would expect and diving deep into the documentation to help you figure things out. As such, Northwind Elixir Traders is the right choice of book for those who prefer applied learning that covers the essentials to get the job done, before diving deeper into every nook and cranny of Ecto with a more comprehensive learning resource or into Ecto’s online documentation (which we will be referring to consistently).

Why SQLite, of all databases out there?

SQLite has been chosen in order to “spice things up” compared to Ecto’s out-of-the-box default of using Postgres, thanks to its ever-growing popularity and impressive, far better than just good-enough capabilities for some applications that your might want to build, such as Phoenix APIs or Phoenix LiveView websites with primarily read-heavy functionality. However, the book is written so that your acquired understanding of Ecto is transferable to using any other RDBMS that Ecto has an adapter for. If you want to use Postgres instead of SQLite after finishing this book, you will be well prepared.

What knowledge is required to get started?

• A basic understanding of Elixir’s kernel, in particular variable types, control flow, the Enum module, how to define modules and functions, and how to write functional pipelines.
• A basic understanding of RDBMS and how they are structured (tables, columns and their types, primary and foreign keys).
• Rudimentary skill in SQL and its syntax.
• Rudimentary skills in shell commands.

What will we not cover?

I assume that your interest in this book means that you already know how to install Elixir with your operating system’s native or package manager or with something like asdf , how to install the sqlite3 binary, and how to use an editor and IEx, so none of those topics will be covered. We will also not cover testing.

Why was this book written?

After reading everything I could get my hands on regarding the use of Ecto and applying what I was learning in my own Elixir and Phoenix LiveView applications, I realized that even the best books, such as “Programming Ecto”, though excellent otherwise, do not reflect the way I prefer to learn.

I started writing this book to provide an alternative take to learning about Ecto, and leave no stone unturned. By not pursuing the “happy path” of how things are done correctly from the start, I decided to start my exploration of Ecto from scratch and deal with all the stumbling blocks as they arise, by trying out what I thought might work, and referring to the amazing documentation to get unstuck.

In truth, I find that this is the way most of us learn something for good; the style of this book is a reflection of that belief, from my own experience learning everything I’ve ever studied that deserves aiming for a solid understanding–and Ecto most certainly deserves this!

What’s inside?

The book was first published in a Work-In-Progress state on Leanpub on April 1st, 2024 and rapidly rose through the ranks of the best-selling books in the Elixir category . Across approx. 350 pages, this book takes you on a journey that spans the following chapters:

• Chapter 1, The application and the repo: We jump straight into setting up the project and explain why we need a supervision tree (and what it is).
• Chapter 2, Creating the database schema: We analyze the ERD of Northwind Traders, model the first table, persist, delete and query data (an appetizer of what’s to come in Chapter 14), and discuss primary keys.
• Chapter 3, Refining the table schema: We extend our table with migrations and hit (and overcome) the first snags of SQLite’s limitations compared to more feature-rich databases.
• Chapter 4, Introducing constraints: We learn about constraints and SQLite’s limitations with them, and about database portability, changesets and validation rules, and validation errors.
• Chapter 5, Changesets in modules with schemas: We learn to implement custom validation functions and multiple changeset functions as we gradually build the modules that model our database.
• Chapter 6, Uniqueness constraints: We utilize unique indices and constraints, gain new insights about Ecto and SQLite, and learn about Ecto migrations and migration rollbacks.
• Chapter 7, Basic table associations: We learn about SQLite’s type affinity, about numeric fields and Northwind Traders’ dirty data, about one-to-many associations with a foreign key, and also implement a custom validation function to overcome SQLite’s limitations regarding foreign-key constraints.
• Chapter 8, Associations with Ecto.Schema: We take a break from coding with an interlude on exploration vs. exploitation in learning and in solving problems, we learn about the N+1 query problem and Ecto’s preloading, about one-to-many associations and has_many, and we use all that to improve existing associations.
• Chapter 9, Casting and putting associations: We explore casting and putting an association, creating new records, and casting the reverse association so that we can access the data of associated database records through a struct/schema.
• Chapter 10, Importing data from a dynamic repository: We learn how to use a dynamic Ecto repo to connect to the original database on demand, and implement a module for importing data from the original in bulk and in an automated manner and validating that everything was done correctly.
• Chapter 11, Modeling further tables, and data cleansing: We go on a a final march through a smorgasbord of pending tasks; we learn about mapping our next working steps with SIPOC, about dealing with phone numbers, about importing, validating and converting countries’ data from an external CSV source over HTTP GET, and then work through the rest of the tables (except for the OrderDetails table).
• Chapter 12, Modeling a join table: We model the centerpiece of the ERD, i.e. the join/association table that pulls everything together, and investigate Ecto’s many_to_many.
• Chapter 13, Cleaning up: Having completed the modeling of all tables and imported all data from the original database, we review whether everything works well and identify some loose ends and tie them up, including revisiting old assumptions about our automation in determining the order in which we import tables and improving our functions with a Depth-First Search algorithm.
• Chapter 14, Insights from data with queries: We learn about the kinds of insights that a business might want to extract from the dataset. We also learn about the difference between Elixir vs. database functions, running simple queries and queries that span multiple tables, and creating composable queries. We stumble on an earlier assumption about the data type of price data and write migrations to make things right. We briefly explore parallelizing our queries, and write many more functions to extract the insights we need.
• Chapter 15, More and more-advanced queries: Beyond looking at the entire dataset, we learn about queries that allow us to look at subsets of the data to identify trends, and also how to exclude data based on criteria.
• Chapter 16, Leftover bits and pieces: With our objective almost conquered, we tackle the few leftovers that are useful in working with databases in Ecto: we implement a field that can only admit one from a set of values, we create a one-to-one association, and learn about redacting fields to prevent sensitive data from being shown to users of our application and anything downstream from it. Finally, we switch the database repo from SQLite to Postgres and see what breaks, then briefly explore (but don’t implement) fixes.
• Chapter 17, Towards “Northwind Elixir Traders 2.0”: With everything finished, we say farewell to our job well done by listing ideas for improvements and next steps for our Northwind Elixir Traders and other Elixir applications that might build upon it.

Where can I find more about this?

There is a discussion in the relevant forum thread on Elixir Forum.