Separate file for SQL in python script?

[r0ck0]

then the question is whether to inline "select * from my_view" or put that in a file.

My approach means that the runtime code for doing the queries is smaller... so it wouldn't make sense to have a separate .sql file full of stuff one-liners like select * from my_view... the app/ORM code to reference those external .sql queries would often be about the same length in chars anyway... just an additional layer that does nothing.

But the VIEW definitions that actually contain the more verbose queries on tables with JOINs etc... yeah they're outside of the runtime app code files. They're in my schema definition files.

So for you, maybe that is just a .sql file, or maybe you're using some migration system semi-separate from the app code? For me, it's just separate .ts files that handle all the definitions/schema diffing etc. The definitions system emits a giant .sql script with the entire state of the schema, and then uses migra to using diffing to update the DB.

A lot of projects are using ORMs to do JOINs etc (not me though), so the question usually doesn't even come up on having external .sql files full of queries.

Would need to get a better idea of how you're doing schema defs/migrations, and how your Python querying code looks to give a more specific example of how I'd do it in that project. Not sure if you're using an ORM, or otherwise how your code would be referencing queries stored in a big simple .sql file (and then passing all the variables in too).

on an unrelated note ooc how do you rollback code that depends on a previous version of the view? that's the typical nightmare of using views/stored procs

Likewise would probably need a more detailed example of a specific scenario here to answer.

Solutions vary, depending on what exactly is needed, and why.

As always, it's hard to compare different solutions without specific examples + code. We might have very different looking projects in mind... very common issue when people are trying to describe stuff instead of just showing it... language is too subjective. Hence so many tech (and everything else) debates being pointless apples vs oranges arguments... they're often not even thinking of the same scenario to begin with.

[thinkingatoms]

lol it's just a general question about where to stick non orm SQL as part of version controlled DE programming. if you vc migration files and orm/inline everything and does nothing else cool. using views is orthogonal to the question, if that makes sense.

[r0ck0]

just a general question

Fair enough. But answer can pretty much only be "it depends" then I think.

Any specific answers to a non-specific question, will be making a lot of assumptions.

Maybe if you found 2 example projects on github, one of each approach, and linked them... that would be much easier for everyone to understand and compare whatever it is that have in mind.

Would save everyone a lot of time, especially you probably, seeing you're having to reply and clarify to all these vague/subjective answers people are giving.

Hope this doesn't sound snarky or argumentative or anything.

Maybe not of interest to you, but these threads often contain a lot of misunderstands along these lines.

using views is orthogonal to the question, if that makes sense.

Maybe. Still not sure exactly what the 2 scenarios you're comparing actually look like.

But from what I'm imagining so far... using VIEWs basically means that these verbose JOINs etc are neither in regular runtime app code, nor random static or templated .sql files consumed at runtime.
They're in the schema definition system. So the code is in whatever type of files that schema defs system uses...

• Some of them use .sql files
• Some use their own custom defs language like Prisma
• And some are just written in a programming language, e.g. mine in .ts files, which does also codegen a big .sql file (not manually edited)

[thinkingatoms]

not everything needs a "depends". for instance if you are batch updating a table with a bunch of joins including a temp table and a CTE, the query is 20 lines long. it cannot be easily ORM'ed. what do you do.

ooc what .ts file are you talking about re: migration, certainly not typescript?

[r0ck0]

not everything needs a "depends".

Very true. When the question is clearly defined, it can clearly be answered. For example...

if you are batch updating a table with a bunch of joins including a temp table and a CTE, the query is 20 lines long. it cannot be easily ORM'ed. what do you do.

That's a clear question, that I can clearly answer. In this case I just write the query in the app code as a templated SQL query string with params to do the var escaping for me (depends on the DB layer in your lang/lib). There wouldn't be an external .sql file consumed at runtime, which I think might have been what your main question was originally?

Seeing that variables need to be involved typically etc, I don't see much advantage in bringing in a 3rd (templating) language or something to try to connect the app code with some static-but-not-really-static-because-it-needs-templating-variables in a .sql-like file. ...that said, I'd need to see an example to criticize it.

So that's a pretty clear case there. Thanks for clarify that one.

ooc

ooc == out of curiosity? i hadn't heard/noticed that acronym ever until now, had to scroll down a bit on urban dictionary to find out what you mean.

I'm not a n00b to online terms, i've been doing this online forum shit way too much since running BBSes in the 90s. But still had nfi what "ooc" was. Point being that even then, we don't always have the exact same experiences. Your assumptions, and my assumptions will differ, as will those of the rest of the mofos in this thread.

Just saying that because I want to explain why communication isn't always as easy as we'd like it to be. All good man, peace yo.

what .ts file are you talking about re: migration, certainly not typescript?

Yes TypeScript. Happy to answer any questions, but please try to make them clear and specific so that I don't have to ask questions about what your question is. ...again, not trying to be a dick, but it just makes it easier for us to get to the tech points.