Yes, they're in a different file. But it's another file in the same codebase, and it's directly referenced by the code that's using it. This means it's not really any harder to track down than the code to a function that lives in a different file. It's really NBD.
And, in return for putting your SQL in .sql files, you get all sorts of nice things. Syntax highlighting for your SQL, for starters. And it's somewhere that autoformatters and linters and SQL unit testing tools and the like can get at easily. And you get SQL code that isn't being forced through whatever laundry wringer and or cheese grater is imposed by your language's string literal syntax. (This last one admittedly isn't such a problem in Python.)
This really isn't too far off from why your JavaScript code is generally happiest in .js files and your CSS is generally happiest in .css files, rather than making them all inline in your HTML.
Right. It’s not much different than a constants file.
Until recently I was looking for a good scheme to keep my sql in .sql files primarily for this reason. But some time in the past few releases, pycharm has started detecting SQL in strings, and if I add an instance of my DB as a data source to the project, it autocompletes table/column names, etc.
I still see some appeal to putting my SQL in .sql files, but good IDE support has given me most of the benefit while keeping it in local strings, plus some help with prepared statements that would have required some added effort if I'd moved it to SQL files.
With IDE-based solutions, even if you can guarantee everyone is using the IDE (Which is already something I don't even want to guarantee. Friends don't make vim-loving friends use heavyweight IDEs. But I digress.), you still have to also remember to get everyone to set their IDE up properly, and verify that they did it. Otherwise you have a tendency for lint to collect unnoticed in the code for a while before it becomes obvious that someone missed a step in the onboarding checklist.
https://marketplace.visualstudio.com/items?itemName=bbsimonb...
I was very new to Java at the time, so I had to spin my wheels on wrapping my head around how Java manages resources for an afternoon. It would have been done in no time at all if I had known about the Reflections library at the time.
It seems much more convenient to have some part of your code base, a separate module, which is dedicated to communication with the database.
The rest of your code communicates with that module through function calls and doesn't need to worry about how it's implemented. Realistically, you'll probably always use a database, but this does make it much easier to switch between databases, or decide you want to use a flat-file instead, or even use dynamodb for some high-traffic part which doesn't need much consistency.
Once you have a module, it's incredibly natural to de-duplicate queries. Instead of writing out each query once for each time it's called, you can stick it into a helper function and call that function from many places. You can organize queries by their purpose or which tables they use, making it easier to change all the queries impacted by each schema change.
In a world where you have large queries SQLAlchemy lets you give meaningful names to subqueries and different expressions in the larger queries, making them easier to understand. If you share some subqueries between queries... that module dedicated to working with your db starts to make a lot of sense!
In all, I can think of a lot of situations where "hides your queries away from the developer" is a cost out-weighed by many other benefits.
That module doesn't have to be .sql file. You can organize your code in whatever language to achieve what you are describing. And have IDE help with Jump/Peek definition.
For example https://github.com/cashapp/sqldelight for Kotlin integrates with IntelliJ to provide ctrl+click navigation (see gif in their readme), and https://github.com/simolus3/moor/ for Dart has similar features that integrate with VSCode (https://moor.simonbinder.eu/docs/using-sql/sql_ide/).
(...since everyone is linking their favorite libraries with a similar approach!)
Your queries should be hidden away from the developer - I mean obfuscation is never the goal but separation is... If your query is written inline in some business logic that section of code has poor tests and is unreliable. SQL is complex, I absolutely adore it but it's not simple - keep it isolated from the logic of your system and, if possible, use a layered architecture approach that allows the entire persistence layer to be detachable.
Looks like they do fancy stuff like template substitution &etc instead of just opening a text file and feeding it to the sql engine.
I was looking at it and thinking "why not just use jinga?" but then you wouldn't get the 'query manager' object and name spacing (and probably other features I've missed).
It actually seems a fairly good way to mix languages, I haven't looked at the code to see what they're really up to but I like the concept.
How heavy is that?
Users care.
That it. That's the key to any saas.