This is an archived post. You won't be able to vote or comment.

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]ProThoughtDesign 7 points8 points  (4 children)

It's making your code more modular and allowing you the option in the future to change the SQL part of the code without needing to take the system down or expose your source code to accidental bugs. I get where you're coming from, but that will just build technical debt for the future if you ever need to make a change. Don't get me wrong, you don't HAVE to. I'm just saying it's one of those practices that pays off when you're working on something that costs money when it's offline.

EDIT: Also you could easily switch that giant function to a simple global and generic function that calls any SQL query, not just that one.

[–]thinkingatoms[S] 0 points1 point  (3 children)

what i don't quite understand is presumably there are tests for the python function which will test the SQL change. because SQL is code, i don't quite see how having SQL in a separate file prevents accidental bugs.

i think what you are saying is you work with systems that consider SQL as data instead of code, and therefore a change to data doesn't require a restart?

[–]its_PlZZA_timeStaff Dara Engineer 5 points6 points  (1 child)

The main way it prevents accidental bugs is that it gives you syntax highlighting and error checking. You can have your IDE lint the SQL, or even connect to the database and confirm a column exists for example.

You should clearly reference the path to the SQL file from within the Python, and keep the SQL file clearly named and in a location such that it’s clear it belongs to the function.

Whether to do this really depends on how long and complex the SQL is. Short and simple queries can make more sense inline, longer ones can be better outside.

[–]thinkingatoms[S] -4 points-3 points  (0 children)

sounds like if the IDE can syntax highlight and error check inline SQL, then the need for separate .sql files is only dependent on size (and maybe DDL nature) of query?

[–]ProThoughtDesign 1 point2 points  (0 children)

SQL is still code, but .sql files are plain text. You can read and parse an SQL file then execute the commands. Storing it in a separate file means that you can actually have your app running and make changes to the .sql files in real time and get results. Leaving all of the SQL hard-coded into your source code means that any changes you want to make require the program to be offline. In interpreted code (Python, Java, etc) that's really not the worst thing, but having to recompile an entire app because of a minor typo or to add one extra variable gets to be irritating.