This is a book for the curious, and for the explorers.

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.

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’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 is this book being 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. It iwll continue to be updated every week until the full envisioned scope is realized. The chapters released so far cover the creation of an application with an SQLite3-backed Ecto repository, the overview of the classic Northwind Traders database schema, the first steps of the creation of the database tables, the refinement of table schemas, and the use of schemaless changesets and validation functions.

Here is a non-exhaustive, list of the topics already covered (✅) or those that will be covered:

• Creating an application with Ecto SQLite3 ✅
• Defining table schemas with Ecto ✅
• Exploring table data with SQLite3 ✅
• Refining table schemas with options ✅
• Altering a table ✅
• Working with data structures ✅
• Persisting data ✅
• Persisting data and deleting records with basic queries ✅
• Primary keys and auto-incrementing integers ✅
• Constraints with SQLite ✅
• Considering database portability ✅
• Schemaless changesets ✅
• Built-in and custom validation functions ✅
• Changesets in modules with Ecto schemas ✅
• Conditional persistence of data in the repo ✅
• Migrations and rollbacks ✅
• Unique indices on tables, and unique contraints in changesets ✅
• One-to-one, one-to-many, and many-to-one relationships between tables ✅
• Adding data with associations between tables ✅
• Queries in general
• Queries with window functions to summarize data
• Embedded schemas
• Seeding the database
• Grouping multiple repository operations
• Enums for field values
• Custom fields types
• Switching to PostgreSQL

Announcements happen on the relevant forum thread on elixirforum.com.