How to properly organize Knex queries?

Question

I fell into the "so many folders" trap and am finding it very difficult to track all of my Knex queries in the controllers folder. Just to give you an idea - I have over 140 different js files only with queries.

Here's a sample of my CONTROLLERS folder to give you an idea of how messy it is:

• queries
• standard
  • getOpenProjects.js
  • getClosedProjects.js
  • createNewProjects.js
  • getProjectsChart.js
  • getClosedProjectsChart.js
  • ... (41 more files)
• charts
  • lineChartOfTechs.js
  • delayedTechs.js
  • overallPie.js
  • ... (11 more files)
• tasks
  • getTasksByTech.js
  • delayedTasks.js
  • tasksColumnTask.js
  • ... (35 more files)

As you can see, I TRIED to keep it within folders but as I coded along - I just named it into files for reference. It's getting tiring searching through each folder for the exact file I need.

I tried to use Bookshelf JS but there's so many complex left joins - I eventually gave up and went back to KnexJS. Some of the answers from Bookshelf's channel eventually told me to use the Bookshelf Knex RAW command. I figured - if I'm going back to Knex - I might as well stick with it rather than adding another module.

Anyhow -- the question is - How do I structure this properly so I can easily find what I'm looking for? How would you do it in your project? Is there any good organization tool to save queries or Knex code?

Answer 1

We use objection.js on top of knex as an ORM (objection uses knex as its query builder and for migrations) and mostly write our queries directly in our controllers (I know, some people considers this as heretic).

Since most of the queries are just used once, there is no use to wrap everyone of them to some separate function. It just makes refactoring and changing the code harder and may end up with badly named query function if some later change modify query meaning, but function name is not fixed etc.

So for simple and one-time queries we do directly this:

let person = await Person.query().findById(personId);

If there is some complex query that is needed in multiple places, we write it as a method to corresponding Model class.

For example if query returns Person models that query would be written to Person class. Of course there will be exceptions to these base rules, but one can consider separately all of those cases what to do with them.

In our current project we have around 40 models and haven't experienced any problems when trying to figure out how to organize our queries.

EDIT:

I did miss the case in the original answer where we have methods which does multiple queries to multiple tables.

Usually we wrap those to some separate service API class which clearly states, which kind of functionality it provides for the application and we inject the API to the controller which needs it.