How to organize MySQL models and relationships in a Node.js project

Basically I am looking for a way to write clean, scalable, and easily maintainable code for the DB access layer in Node.js.

I know about the model pattern where for every table you create a class (a model) with methods like findAll, findOne, create, etc. Then your DB access code is isolated in a single place so it's easy to reflect schema changes and add new functionality. But it only works well if you only need to access a single table in a single query (or action, i.e. create, delete, etc.), which is not the case like 50% of the time.

Continuing with the model pattern, imagine there are two tables - item and order - and they have a many-to-many relationship. Now, where to put the code that returns all the order's items? At first, I would think that I should put it in the order's model, but then it would reference the item table, and if I changed the item schema later I would need to edit both the item and order models. Even worse case is when an order can be in either a pending or completed state and I want to query all the items of all the pending orders. Where do I put this code? I want to query items, so I think I should put it in the item model, but then the item model has to do a JOIN with the order model and know its structure and that it has completed and pending state and filter based on that. Even worse case is when I need to create a new order with items. Then I have to do a transaction in which I first create the order and then create items referencing it. Where do I put this code? In the order model? But then the order model is responsible for creating items. That looks absolutely wrong... If I later make some changes to one schema and forget to edit the other schema's model I might run into some serious bugs, considering there are not two but, say, 30 or more tables with relationships. So when I make a change to a schema then I have to search through my code base to find which models use the parts of the schema that I edited and then the model pattern loses its purpose totally, because there is actually no isolation and scaling and maintenance are no easier.

I tried googling things like "how to organize mysql models in node.js", "mysql data access layer best practices in node.js", etc., but the results were just MySQL documentation pages or some tips on how to improve the DB's performance, but nothing related to code organization. I understand that this is a really high level question so no surprise Google has trouble understanding what I am looking for. I believe I am now in a process of shifting my mindset from a mid-level towards more of a senior programmer's and I feel like it's time to finally learn how to write the DB access layer because it has caused me head aches in all the projects that I've created so far and still causes them now when I need to maintain these projects. If you know any books or online courses on this topic I would be more than happy if you shared them with me. I understand that a single article or two might not be enough to explain this thing and develop a sense how it should be done.