Northwind Elixir Traders (PragProg)

Hi all, I recently published an early release of bindings to Turso, a modern re-implementation of SQLite in Rust (see my post for more details.) I’d love any feedback that you may have.

I look forward to reading this one in the near future.

@waseigo Hi, I bought the book from PragProg today but it didn’t have a proper TOC.

So, I made the TOC clickable. The only downside is that it’s clickable in the sidebar but not in the TOC pages.

I already sent an email with an updated book to you.

Hello everyone. I was checking in after a long time to write a status update for Phoenix Product Codex, and thought I’d share some things I did over the past 4 months while porting Breek.gr to Elixir and Phoenix / Phoenix LiveView (not yet done, but well underway, which is why I’ve had to set book-writing aside for a while).

So, long story short:

Breek.gr is implemented in PHP for the backend, with Wordpress and the ACF plugin as a headless CMS, with a MySQL database, as one does with Wordpress. The frontend is in NextJS and TypeScript. When I was delivered v1.0 by the external developers 2 years ago, it was in NextJS and mostly TypeScript with the typical “any” garbage. All was well for the first year of operations though. Things were passably fast.

I spent 7 months cleaning up the mess that contract developers typically deliver, primarily insane code duplication on both the frontend and the backend, which meant that fixing a bug here needed fixing in 5 or 10 other places. I reached v2.0 with passably cleaned-up codebases and kept developing point releases, burning down technical debt and features that initially the external contracts would have implemented, had they not bumped their hourly rate by ~40% overnight – which is why I took it upon myself.

Things were still good in the rest of 2024 and the first half of 2025, and then we got an influx of interested users in mid-2025 as the market matured. Things were still good in terms of performance in Q3 of 2025. And then we got a lot of new records.

And the wp_users and wp_posts grew and grew. But most importantly: the wp_postmeta table exploded. The table’s EAV approach led to long table scans, plus the fact that the business domain shows a lot of interconnected records’s data (especially in list views) meant that the original sin of N+1 queries caught up with us.

Additionally, I got sick and tired of having to duplicate parts of the business logic on the frontend and the backend, and of the JSON ping-pong between PHP and NextJS.

So I thought… what if I could read the MySQL database from Elixir?

Obviously possible. Set up an Ecto repo with the MyXQL adapter and it’s possible.

And then I thought… how can I load a WP_Post’s data and all its associated ACF fields’ data into Elixir, as proper structs?

So I wrote an Elixir module that parses the JSON that ACF generates with its configuration for post types and fields and field types etc., and a GenServer that periodically fetches and caches that JSON payload from the PHP REST API.

And then I wrote code to parse all those special ACF fields (datetime, date, etc.) into proper Elixir data types, and took all this and shoved it into an :acf text field, JSON-encoded, of two new structs:

1. %MigratedPost{} on the migrated_posts table.
2. %MigratedUser{} on the migrated_users table.

Those are in an ephemeral SQLite database. The WP_User/WP_Post key data (user_email, user_login, ID, post_type, post_modified, etc.) are proper fields/columns, but all the ACF fields are under %{data: %{acf: acf}}, and with custom Ecto types and the ACF field parsing are shown as proper Elixir data when any of those records are loaded.

Then I thought… let’s connect to the Redis instance (that I used for caching DTOs from PHP to get around the increasingly slow data-fetching) on the VPS running Wordpress and do this:

• When a WP_User or WP_Post is inserted or updated (or any of its fields are modified), we use Wordpress hooks to publish an event to topics/channels that the Elixir app subscribes to.
• The Elixir app picks those up and uses Oban workers to enqueue upserts to the appropriate table. This gets around the write contention issue of SQLite.

With this done, and a GenServer periodically running OnDemandMigrator.sync/0 to make sure that nothing is missing, what we end up with is a very fast “document store” on which I then run various Ecto queries with fragment() and using SQLite’s JSON_EXTRACT and other JSON-related functions to perform proper JOINs.

With proper Ecto queries, was then able to start implementing a “sister backend” with Phoenix 1.8. Properly implemented %Scope{} mechanism, used a plug to re-use the JWT that the PHP backend issues.

Queries were so fast that I was able to generate a “fat scope” containing the :id values of records of different entity tupes that a user has access to within 70 ms.

Then it was time for the list views, and getting rid of the N+1 query problem. Eventually I landed on using the registry pattern and writing Elixir modules to selectively preload only parts of the :acf map, for those cases when I don’t need all data from a WP_Post (now a %MigratedPost{}) or from a WP_User (now a %MigratedUser{}).

Indicative example for one of the heaviest list views that pulls in tons of associated records’ data: it went from 18 (!) seconds for a certain number of items shown, down to around 500 ms (!). If you’re wondering about the 18 seconds? Abysmal, but probably a combination of the PHP code being wasteful with get_post() and get_field(), and of the wp_postmeta table having grown to 700’000 rows by then.

The Phoenix LiveDashboard was instrumental in profiling how my attempts at bringing down these timings went.

We’ve been live for 3-4 months now with this setup, and it’s been amazing. All new features are implemented in Elixir and Phoenix, still serving the NextJS app alongside the (increasingly) legacy PHP backend, that now serves most writes.

I say “most writes” and not “all writes”, because I took it one step further: with POST/PATCH/DELETE route handlers (accessible only by the Elixir app with proper authentication) on the PHP side, and with Ecto embedded schemas on the Elixir side, plus with a “facade” WpBreexRepo that is a fake repo with a similar API to an Ecto.Repo, I now increasingly write to MySQL via HTTP calls, after passing e.g. a %Property{} Ecto schema through its scoped changeset function, which gives me typical Elixir/Ecto developer ergonomics, without worrying about how the writes happen. The PHP route handlers perform all the Wordpress/ACF-specific stuff using the proper way; otherwise, I’d need to write Elixir code that writes to MySQL’s tables in a way that satisfies both Wordpress and ACF. And since I’m going to ditch everything for Elixir, Phoenix and Phoenix LiveView eventually, that would be a waste of time.

So, here we are now. Strangler fig pattern, more-than-decently-fast loading times on the frontend thanks to the SQLite “document store” that is always updated via PubSub events triggering Oban workers performing upserts.

It works well, and SQLite saved the day.

Does it mean that this would not have been possible with PostgreSQL or MySQL and Elixir? Clearly, no. But there’s something nice about being able to podman-containerize the entire app, and have it initialize its own ephemeral SQLite database, without network roundtrips to a database server (other than the initial sync from MySQL to SQLite).

There’s another upside: it’s possible to scale this horizontally. caddy round-robin load-balancing across two or more instances of the Elixir/Phoenix app, each with its own SQLite database that gets updated through the same PubSub/Oban/upsert path.

It hasn’t been needed so far, and for sure by the time such horizontal scaling will be needed I will have already ported everything to Elixir, including the web app, and gotten rid of:

Wordpress as a headless CMS: decent choice for an MVP or v1.0 or until you reach PMF, but awful after that. And if you’re going to manage non-EAV non-Wordpress tables, what’s the point of running the backend in PHP?

ACF PRO as an annual $49 subscription: decent choice and cheap too. Very nice admin and development ergonomics, huge flexibility while the business domain and the feature set evolves. However, the more you buy into ACF PRO’s features (e.g., repeater fields), the more you stray away from the inflection point when the business domain modeling is solid enough that you could implement a proper database ERD with Ecto and your choice of database. That’s the next step for us, and already underway, with time split between developing and proofing new customer-led features, and gradually building a proper database schema in Elixir with Ecto and PostgreSQL–something that’s possible in parallel to the rest of the SQLite/Oban/etc. state, because adding another Ecto repo with PSQL and periodically building out the Ecto schemas and gradually migrating data is super simple with Elixir and Ecto.

NextJS and TypeScript: I kinda like those, and don’t get why many people complain that much. We run this on a VPS with PM2, no Vercel dependency at all. It runs well. Downside: breakage with every major version, because Vercel is moving the chairs around. And I certainly won’t miss having to define interfaces and types everywhere, even though this made a huge improvement to the quality of the web app between v1.0 and v2.0. Biggest downside though: next dev is awfully, terribly slow as the app grows. The comparison to the DX of writing Phoenix LiveViews is night and day.

JSON ping-pong and a crappy REST API: I’m tired of the frontend-backend split. Tired of not having plugs and pipelines, tired of duplicating large parts of the business logic between frontend forms and the backend route handlers, tired of the asynchronous nature of it all, of useSWR and checking whether what we received conforms to the “contract” of the interface, tired of synchronizing interfaces with what the growing backend delivers.

Two codebases: I’m also tired of having a Codium window open for the NextJS codebase (and the periodic crashes of next dev after long coding sessions), one window for the PHP codebase with a terminal that periodically rsyncs the custom plugin’s code tree to the dev VM, and one Bruno window for testing the PHP rest API. Tired of not having IEx available to try things out on the fly, of waiting things to sync or recompile, or of the frontend periodically hitting the backend to see if e.g. there are new notifications available. All those things are a child’s play with Phoenix PubSub, LiveViews and LiveComponents. Coming back to the PHP and NextJS codebases after writing Elixir feels like I’m time-traveling to the past.

When this rewrite finishes, we will end up with a single codebase, written in a single language, easier to maintain, easier to debug, easier to monitor (thanks, LiveDashboard and Telemetry!), easier to test emails with Swoosh (though Mailpit, which I’m self-hosting is awesome), faster to build, more responsive to user clicks, easier to update the UI with notifications and toasts… I can keep going on.

And by that time, we won’t be using SQLite anymore. But hey–it really saves our bacon right now, right when we have started reaching PMF / market traction.

Thanks to everyone working on Elixir, Ecto, Phoenix, Req, and all the other parts of this amazing ecosystem! Getting into Elixir back in 2022 is one of the best decisions I’ve ever made; a seminal moment equal in importance to getting into Linux in 1997 and into self-hosting in 2001. One of the best investments of time ever made. For me, at least.

(And yeah, writing Northwind Elixir Traders was instrumental in this journey!)

Edit: before anyone asks “why Wordpress? If PHP, why not Laravel? Why NextJS/React? Why not Elixir from the start?”… the simple answer is: because we operate in the Greek market and wanted to keep development here to make communications smoother, and the Greek market is chock-full of PHP (mostly Wordpress, for e-shops etc.) and JavaScript programmers. Hindsight is 20/20, and in retrospect the decision was perhaps short-sighted. Then again, the flexibility that Wordpress and ACF afforded was immense, while we were learning about the business domain. The supposed speed-up of development by decoupling the backend from the frontend never materialized though. It all turned into transaction costs and into a frontend tightly coupled to the backend. Whereas now, with a proper Elixir/Phoenix app and context modules that are used for both a REST API and the Phoenix LiveViews, we get a proper REST API that can be opened to “enterprise” customers, and as little code duplication as possible.

I wasnt thinking of it as a per tenant basis when I wrote the statement, but you are right 1k per-t is generous and also didnt think of using mnesia

something like a reddit clone could be trivially made, tie a sub reddit to a tenant + mnesia. The beam is really powerful, feels like cheating…..

On a per-tenant basis, this limit seems pretty generous, no? Except if the tenant represents an organization with multiple users. And even then, for frequent writes it makes more sense to handle this with Mnesia and periodically dump to disk for persistence. Or (neater), have a separate repo backed by an SQLite db to which you periodically dump from Mnesia. Perhaps even empty Mnesia when you dump, and then write a module that fetches from “hot” (Mnesia) for recent events, or “cold” (for older events), or both, if the query spans both.

The biggest challenge I foresee is applying migrations so that all tenant-specific databases have the same schema. Upon user sign-up, you’d have to create the database and apply all migrations. You’d also need to increase the maximum number of open file descriptors with ulimit, if you have too many users. An interesting discussion.

Also, you couldn’t perform cross-db queries; but given that you can use Task.async_stream/3 across the repos, and access is crazy fast on NVMe (and even SSD), and that each open SQLite DB also has some in-memory cache, the task then becomes writing a function that aggregates the results of multiple queries that you run in parallel across the databases.

On the other hand, it also means that the memory requirements scale with the number of tenants, depending on how you set up the size of the pool and the page-cache size. At the very least, it makes sense to not leave Repo processes of inactive tenants running all the time, but to start each tenant’s dynamic repo at login, and stop it at logout. Authentication would still require a central database.

The more I think about it, the more I like it, at the bare minimum as an interesting exercise, especially if coupled with OpenTelemetry so that you can track things on the LiveDashboard.

Edit: never used encryption with SQLite, but this could also be a way to have each tenant’s db encrypted with a different key.

i wonder how far you can push the marriage with the beam and sqllite

That would be cool, even beyond distinguishing between paying/non-paying customers! It would help massively with fulfilling GDPR requests. Oh, you want your data? Here you go! (Share the .db file.)

You can do this with Ecto and a dynamic repository, similarly to how we switch between Repo and the :nt dynamic repo that holds the original Northwind Traders database in Northwind Elixir Traders. But even better would be to have one GenServer per user-specific (or company/team-specific) SQLite database, because then every user/company/team writes to a different file, so the write contention issue are also mitigated to an extent!