> In that case for the SQL, you would be pulling in data from one table, putting it into another
In django ORM lingo, that's declaring 2 models, assuming they're even in the same namespace or app. plus the request you use to fill them. If you don't find this awkward, its on you, not me :)
> Views just end up getting swapped out when the planner deals with the query (in Postgres at least)
Huh? do you actually know how views work? You don't even have an easy way (non-sql) of declaring views in Django. And apparently, you also seem to doesn't seem to grasp implementation details regarding views and materialized views - views are "server-side cached queries", but materialized views are physical pre-computed tables. Also, in PostgreSQL (as well as many other databases) a view can actually hold data from several tables, including remote tables. The whole "this is a RDBMS system and we shall abide by it" went through the window the moment I can use a SQLish database to query eg. CSV files and/or import them as local "tables".
> Of course I get this cuz I spent a lot of time with the ORM
Don't get me wrong, but it seems you're not spending enough time. Let me enlighten you using a personal anecdote: a project management solution where you log in your tasks during the day, the hours and it would keep track of the collaborator's project allocation during the project execution time. Each collaborator (from less than 100) would introduce from 1 to probably 20 tasks per day, on the projects they were allocated. Reports were generated - per project, daily, monthly, etc- you get the point. Obviously, those reports were computed using python and the ORM - so at some point, getting eg. a company-wide report with a couple of projects for a year would trigger some specific conditions that made the report take more than 10 minutes to generate. A dataset I could tabulate in excel (couple of hundred thousand lines). Half of the time was actually spent on allocating objects in memory for stuff that had 1 field read. Of course the report routine reused the existing code functions for specific computations, that increased 10-fold the execution and memory allocation. The 20-line sql query that replaced hundreds of python lines executed in less than a hundred milliseconds.
I could blame the ORM, but instead I blame the application design that follows ORM constraints. Someone detached from a data source would tell you "the parent API needs to provide that" - instead, you use what you have at hand. Just because it works, it doesn't mean its good.
> o stuff like "share schema definitions between classes" is easy and straightforward
That is actually the shit-show design I always want to prevent. Classes should not share schema definitions, but data formats (data objects). In the specific context of Django, Models are a piss-poor data object representation (because it is a quite decent Model implementation, mind you), and the whole apps scaffolding is just... nuts. The notion of apps are self-contained units of functionality, but they quickly become a cesspit of cross-referencing imports. Rule of thumb in most languages, if different "modules" of the application need to share a schema definition, you're doing it wrong. But heyyy, Python.
Alright I'm not going to engage with you on this, except to say that I've also written a report generator that does the whole "aggregate by time bucket and certain conditions across multiple tables" and it was in the Django ORM and it would end up as a single query that looked like what the SQL I would have written is. Lots of `query.explain()` calls locally to make sure I was getting what I want, but hey I'd be doing that for SQL in perf-sensitive queries as well.
I'm partial to the ORM being easy to lull you into writing quadratic-behavior stuff. You still gotta know how your DB works! But "mostly using the ORM, using SQL for some tricky things" works really well, especially given that the Django ORM lets you inject SQL fragments for a lot of behavior and have that "just work".
In django ORM lingo, that's declaring 2 models, assuming they're even in the same namespace or app. plus the request you use to fill them. If you don't find this awkward, its on you, not me :)
> Views just end up getting swapped out when the planner deals with the query (in Postgres at least)
Huh? do you actually know how views work? You don't even have an easy way (non-sql) of declaring views in Django. And apparently, you also seem to doesn't seem to grasp implementation details regarding views and materialized views - views are "server-side cached queries", but materialized views are physical pre-computed tables. Also, in PostgreSQL (as well as many other databases) a view can actually hold data from several tables, including remote tables. The whole "this is a RDBMS system and we shall abide by it" went through the window the moment I can use a SQLish database to query eg. CSV files and/or import them as local "tables".
> Of course I get this cuz I spent a lot of time with the ORM
Don't get me wrong, but it seems you're not spending enough time. Let me enlighten you using a personal anecdote: a project management solution where you log in your tasks during the day, the hours and it would keep track of the collaborator's project allocation during the project execution time. Each collaborator (from less than 100) would introduce from 1 to probably 20 tasks per day, on the projects they were allocated. Reports were generated - per project, daily, monthly, etc- you get the point. Obviously, those reports were computed using python and the ORM - so at some point, getting eg. a company-wide report with a couple of projects for a year would trigger some specific conditions that made the report take more than 10 minutes to generate. A dataset I could tabulate in excel (couple of hundred thousand lines). Half of the time was actually spent on allocating objects in memory for stuff that had 1 field read. Of course the report routine reused the existing code functions for specific computations, that increased 10-fold the execution and memory allocation. The 20-line sql query that replaced hundreds of python lines executed in less than a hundred milliseconds. I could blame the ORM, but instead I blame the application design that follows ORM constraints. Someone detached from a data source would tell you "the parent API needs to provide that" - instead, you use what you have at hand. Just because it works, it doesn't mean its good.
> o stuff like "share schema definitions between classes" is easy and straightforward
That is actually the shit-show design I always want to prevent. Classes should not share schema definitions, but data formats (data objects). In the specific context of Django, Models are a piss-poor data object representation (because it is a quite decent Model implementation, mind you), and the whole apps scaffolding is just... nuts. The notion of apps are self-contained units of functionality, but they quickly become a cesspit of cross-referencing imports. Rule of thumb in most languages, if different "modules" of the application need to share a schema definition, you're doing it wrong. But heyyy, Python.