Nice idea, but this pattern hides your queries away from the developer. In practice, developers need the query transparent and not hidden in a file somewhere away from the code they're reading. Otherwise you're building a pattern for using queries without knowing the query's cost. If you need raw SQL, use SQLAlchemy Core and just don't use the ORM features.
I use roughly this approach in Java, and I would argue that it makes the queries more available to the developer.
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.
> And, in return for putting your SQL in .sql files, you get all sorts of nice things. Syntax highlighting for your SQL, for starters.
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.
I definitely see an advantage there. But I still tend to prefer non-IDE solutions for things like code formatting and linting, because it means you have the option to configure them once in the build settings and forget about it.
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.
I stick the *.sql files in the resources, and then created my own helper functions to grab them by filename.
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.
Sure, with SQLAlchemy Core you might start out by adding some `conn.execute` statements where necessary. But, inevitably, you'll want to use some queries in multiple places. Some of them you'll even want to use in multiple different files. Your schema will surely change over time, and your queries along with them. It would be a shame if you needed to hunt for queries across all of your files and make the necessary changes to each copy of your queries which need to be change.
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.
> It seems much more convenient to have some part of your code base, a separate module, which is dedicated to communication with the database.
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.
How is this different from having a repository of queries somewhere - this is basically a query repository but not in the active language in fact.
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.
> How is this different from having a repository of queries somewhere - this is basically a query repository but not in the active language in fact.
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.
Sorry - is it because you don't analyze the queries even though you write them (and thus are responsibly for optimization) that is the issue? Or do you have the same complaints about .NETs Entity Framework?
Yes, this feels like it reinvents prepared statements in a less portable way. It kind of goes against the spirit of their opening justification for using it: "SQL is code, you should be able to write it, version control it, comment it, and run it using files." If that's the goal encourage the use of a way to leverage native SQL that could run on any platform or engine.
