Ran into the same thing. SQLite works until you need cold start recovery or WAL contention with concurrent agents. Built a dedicated memory layer for agent workloads - happy to share: https://github.com/RYJOX-Technologies/Synrix-Memory-Engine
Ran into the same thing. SQLite works until you need cold start recovery or WAL contention with concurrent agents. Built a dedicated memory layer for agent workloads - happy to share: https://github.com/RYJOX-Technologies/Synrix-Memory-Engine
Or, when you have a Django project and started out on SQLite, but then begrudgingly introduce M-to-N relationships, but then suddenly notice, that many things you might want to do or implement with those M-to-N relationships are not supported by SQLite. Then you suddenly wish you had started with Postgres right away.
There are definitely some caveats / tradeoffs with SQLite, but I can't think of any that are specifically related to many to many relationships. Which features did you find missing? Lateral joins maybe?
I only remember from my last Django project, that I started out thinking: "OK, I will do things properly, no many-to-many relationships..." then at some point saw the need for them, or manually creating the intermediate relation and at that point using the Django way was what I was supposed to do. But then I got errors for some things I wanted about not being supported by SQLite.
The project is here: https://codeberg.org/ZelphirKaltstahl/web-app-vocabulary-tra... But I left it unfinished and a quick grep does not yield comments or something that explains why at some place I do something to circumvent the SQLite problems. I remember though, that I basically swore to myself, that I would not ever use SQLite in production with Django ORM. And if I am not using it in production, then testing also better should not be using it, because one should test with the same RDBMS as runs in production, or risk unexpected issues suddenly only happening in production. So SQLite is out for anything serious in Django projects for me.