Northwind Elixir Traders: A practical tutorial on databases with Ecto (Self-published/Leanpub)

by Isaak Tsalicoglou

Available as PDF and ePub on Leanpub.


Why this book is 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!

About the book

Northwind Elixir Traders 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, while also relying heavily on available sources of knowledge to understand how things work from the ground up, while also moving forward without getting caught in quagmires.

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.

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 aims to be both enriching and unforgettable.

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, prospective list of the topics that will be covered in chapters coming soon, some of which are already in development:

  • Changesets with schemas (up next!)
  • 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

About the author

Isaak Tsalicoglou is a mechanical engineer with extensive experience in developing software tools for engineering and business use cases, and has gone all-in on Elixir since late 2022.

For a limited time only, elixirforum.com readers can get 20% off the minimum price with the following coupon link: https://leanpub.com/northwind-elixir-traders/c/elixirforum

Edit: Many thanks and apologies for catching the 406 error – I have updated the coupon link and it now verifiably works.

9 Likes

The coupon code gives an HTTP error 406, as does any other code I try that contains a period.

Can you try making the coupon code with just “elixirforum” instead of “elixirforum.com”?

2 Likes

+1 for http error 406

1 Like

Interesting book :slight_smile:

1 Like

Adding this to my list. Are they any other Elixir/Phoenix books that are in leanpub?

Apologies, and many thanks for catching this – I have updated the coupon link in the post!

One that’s rather well known is “Joy of Elixir”!

Cool, thanks for the update. I bought a copy to support your work. I used the coupon code of course… sorry, but I’m a cheapskate. :sweat_smile:

1 Like

Thank you! No worries, that’s what the coupon is for! Plus, it’s a thank you gesture to users of elixirforum.com – this forum has gotten me unstuck numerous times :upside_down_face:

1 Like

Hello there!

I just finished and released a new chapter of the book on using changeset functions within modules with Ecto schemas, using built-in and custom validation functions, and basic persistence of data in the database using changesets.

The upcoming two chapters will deal with uniqueness constraints to avoid multiple copies of the same records, further functions of Ecto.Changeset, the most important functions and their options for inserts, updates, upserts and deletes using changesets, and – most importantly – the use of associations (has_one, belongs_to, etc.) to finally start connecting different tables of the “Northwind Traders” databased and thus gradually recreate more complex and more educational database operations.

Feedback is always welcome. Should you have something in mind that you would like to see covered or explored, don’t hesitate to comment below!

2 Likes

Hello again from Athens, Greece!

Another chapter has been released, this time on uniqueness constraints, including new lessons on the ramifications of using SQLite with Ecto–and how we can maintain compatibility with PostgreSQL, regardless.

Next to the new chapter, here are some other changes:

  • Amended the first chapter with a discussion of supervision trees and the content of application.ex that mix new northwind_elixir_traders --sup generates, so that we can use the Ecto Repo.
  • Edits for readability, typos and clarifications across all already-released chapters.

The next chapter will tackle the first associations between tables of the Northwind Traders database schema, as we inch closer to the more-complex center of the database’s ERD, with the ultimate goal of modeling the Many-to-Many relationship between orders and products in the OrderDetails table. But, first: has_one and belongs_to!

BTW, this is the ERD that we are modeling with Ecto in an SQLite3 database:
Northwind Traders on dbdiagram.io

Let me know what you think by email at isaak@overbring.com or by messaging on LinkedIn at https://linkedin.com/in/tisaak

Enjoy!
Isaak Tsalicoglou

Thanks! Will purchase your book in the future too :slight_smile:

1 Like

Hello everyone – wow, thank you for the positive feedback I’ve received on LinkedIn, by email, regarding this book!

Many thanks to @tiagodavi for allowing me to share the book on the Elixir Programming group that he admins on LinkedIn.

Leanpub took note of the rapid pickup of the book and kindly (and marketing-savvily) provided the opportunity for a short launch video:

The next new book chapter is dropping on Monday. As always, feedback is welcome and appreciated.

3 Likes

Thank you for mentioning me.

1 Like

Hello hello!

After a very intense Sunday, I bring you another chapter of the book: Chapter 7 – Basic Table Associations.

This has been tons of fun and insightful to write! The book now also has a preface, for those more philosophically-inclined.

I’ll try something new this time: an excerpt from the new chapter’s “Summary and outlook” closing paragraphs (every chapter gets one.)

In this chapter, we learned about the one-to-many association, how it’s implemented with a foreign key column, and how it’s accomplished within SQLite3.

We also explored numeric types for database columns, how SQLite handles them, and how we should utilized them even if we don’t use PostgreSQL. Plus, we explored SQLite’s storage classes, type affinities, and the peculiarities of its dynamic typing capabilities, compared to stricter databases. We also saw that this does not impact our application.

In small “side quests”, we explored practical aspects of modeling numeric values, including the possibility of using integers for storing money amounts. We resisted perfectionism and yak-shaving, and focused on learning more about the focus topic of this chapter. Meanwhile, we did not forget the numerous opportunities for improvement that arise, not only to the Products and Suppliers tables of the Northwind Traders ERD, but also across many of its tables.

Finally, we learned about foreign key constraints, how they work in SQLite, and how SQLite limits our use of Ecto’s validation function of foreign key constraints. Regardless, we forged ahead and circumvented this limitation through the definition of a robust custom validation function.

Overall, we faced quite a few challenges along the way, but forged ahead by checking the helpful documentation of Ecto, Ecto SQLite, SQLite3, and PostgreSQL.

We have come very far, and haven’t yet even scratched the surface of associations with Ecto! For example, we have not yet utilized Ecto.Schema’s features for modeling the “belongs to” and “has many” ways of describing an association. For sure, it would be nice, for example, if we could access the data of the Category associated with a Product record, from within the Product record itself.

This is what we’ll be exploring in the next chapter.

Enjoy!
Isaak

2 Likes