project structure with regards to handling data sources(sql)

[sfabriece]

I have a lot of custom sql in my handlers that I would like to abstract out as it is becoming hard to manage. What would the right way to handle that? Also I saw a lot of people referencing controllers, what do they do?

[calmdev]

Consider using an ORM/ODM that's compatible with whatever DBMS you're using. Abstract the sql into models and relationships. Think of a controller as a handler for your routes. Typically each resource you're working with may have its own controller for handling requests. Each controller may contain one or more methods depending on how many routes the resource handles requests for.

[sfabriece]

Thanks @calmdev. I'll have a look at that. By the way i'm using Mysql.

[devinivy]

We developed a hapi plugin specifically to work with MySQL/Postgres– it integrates objection ORM (hardly an ORM, really just a very intelligent query builder) in hapi. You might find it useful– https://github.com/BigRoomStudios/schwifty. Here's an example incorporating it into a project– https://github.com/devinivy/boilerplate-api/compare/pal...flavor-objection.

Regarding "controllers," that's a term typically used in the context of the "model-view-controller" pattern. The way objection fits into this is that it can be the foundation of your "model." A "controller" would generally be the "meat" of your route handlers, which use the model to make changes within your data store (in your case, MySQL). The "view" is some final presentation of the model after the changes made by the controller– for an HTTP server that means coming up with a response, setting status codes, headers, etc.

[sfabriece]

Thanks @devinivy