Hacker News Reader

Comment by graemep | Hacker News Reader

I think the point about not know what SQL an ORM generated query is the one that resonates with me, as does the dig at Django ORM.

I love Django, including aspects of the ORM (its very easy to write schema, migrations are pretty easy to use, even queries are very concise and easy to write) but I have no idea what the SQL will look like except in the simplest cases.

I often do not particularly care, and it is easy enough to see them when I do, but I feel a bit icky about the extra layer of stuff I do not really understand.

View on HN

If you use postgres, psycopg provides `cursor.mogrify(query, params)` which returns the actual query that will be executed . For example:

    cursor.mogrify(*queryset.query.sql_with_params())

Alternatively, you can set the log level of the `django.db.backends` logger to DEBUG to see all executed queries

https://docs.djangoproject.com/en/5.0/topics/db/sql/ :

> Django gives you two ways of performing raw SQL queries: you can use `Manager.raw()` to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly.

https://stackoverflow.com/questions/1074212/how-can-i-see-th... has :

  MyModel.objects.all().query.sql_with_params() 
  str(MyModel.objects.all().query)

And:

  from django.db import connection
  from myapp.models import SomeModel
  queryset = SomeModel.objects.filter(foo='bar')
  sql_query, params = queryset.query.as_sql(None, connection)
  
  with connection.connection.cursor(cursor_factory=DictCursor) as cursor:
      cursor.execute(sql_query, params)
      data = cursor.fetchall()

But that's still not backend-specific SQL?

There should be an interface method for this. Why does psycopg call it mogrify?

https://django-debug-toolbar.readthedocs.io/en/latest/panels... :

> debug_toolbar.panels.sql.SQLPanel: SQL queries including time to execute and links to EXPLAIN each query

But debug toolbars mostly don't work with APIs.

https://github.com/django-query-profiler/django-query-profil... :

> Django query profiler - one profiler to rule them all. Shows queries, detects N+1 and gives recommendations on how to resolve them

https://github.com/jazzband/django-silk :

> Silk is a live profiling and inspection tool for the Django framework. Silk intercepts and stores HTTP requests and database queries before presenting them in a user interface for further inspection

`str(queryset.query)` does not give you executable SQL. Query parameters are not escaped or encoded in that representation of the query. That escaping/interpolation of query parameters is performed by mogrify. I agree the name is odd, and I don't know why they use it other than "transmogrify" being an obscure word for "magically transform".

debug_toolbar and silk are both tools that show you what queries were executed by your running application. They're both good tools, but neither quite solves the problem of giving you the executable SQL for a specific queryset (e.g., in a repl).

There are a number of solutions listed in that stackoverflow post for logging queries, but AFAIU none call mogrify().

Maybe it should be called dialectize() or to_sql(parametrized=True)

I really wish Django would let you write raw queries, but wrap them in an object that still allows `.annotate()` and `.filter`, etc. I know you can do Model.objects.raw(), but I'm talking about a more complex query with nested joins, etc.

I understand why it doesn't allow that, and probably never can, but it would be nice when you need author OLAP queries but want to continue to use as much of the ORM as you can.

(I'm not a Python dev. Sorry.)

Guessing .annotation() for runtime type info? .filter() avoids re-executing query just to get a subset?

How do the (canonical utility methods) relate to nested JOINs? Because field types of nested sets aren't visible?

TIA.

Django's ORM has a Model class that wraps an underlying SQL table to can runs queries in an object-oriented approach. When using utility methods like .filter() and .annotate(), you're executing them against the Model (well a ModelManager to be more specific) so they inherently understand the model (and thus the underlying table) that you're querying against. This allows Django to translate the arguments you pass into those methods into SQL, while also handle SQL parameterization, etc.

If the ORM were to allow you do build a raw query, using multiple tables, views, nested queries, etc - it would be difficult for it to then allow those utility methods. The ORM wouldn't have the Model class and it's associated fields to use in while translating the method's args into SQL.

> but I have no idea what the SQL will look like

Why don't you print QuerySet.query ?

> but I have no idea what the SQL will look like except in the simplest cases

that's really on you as Django makes it very easy to find the answer to that because Python makes it very easy to inspect objects at runtime

that is what i meant by 'easy enough to see them'

"Is ORM still an anti-pattern?" (2023)

> In truth the best way to do the data layer is to use stored procs with a generated binding at the application layer. This is absolutely safe from injection, and is wicked fast as well.

[Who must keep stored procedures in sync with migrations,]

https://news.ycombinator.com/item?id=36497613#36503998

I used the stored procedures approach sometimes. The usual criticisms are

1. Almost nobody in the team knows SQL, stored procedures more so.

2. It's not possible to deploy and test stored procedures with the usual tooling. Actually I don't know what's the usual way to do it in projects that go with that approach.

Perhaps I can help, or maybe learn something.

I don't really understand the confusion here, you write a stored procedure, you document it, test it, peer review it, then stuff it into the database. You then write code that uses it. No sarcasm, but what's the problem?

> you ... test it

I think they're referring to ongoing automated tests, and not one-off tests.

Yes, what is the normal way to unit test stored procedures? Dev teams used to Node, Python, Ruby or whatever language have tools that work with ORMs to setup a test database, run tests and zero it. I did wrote some stored procedures in a Rails migration many years ago but that's not the place where one would normally write code. Furthermore there will be many migrations like that, almost one for every change to the stored procedure. The problem is in the frameworks, that are not designed for that. So, what's the recommended way to handle them?

Right, I'm starting to get the picture. I've never worked with an ORM as my SQL is pretty solid. I have to put my hands up here and say I don't know, sorry.

I think I can tell you the answer: you have a very slow system test that spins up a whole real database, sets up all the tables and stored procedures, and fires a load of requests through the system and inspects the responses and the final state of the system.

That works; it's just maybe thousands of times slower than unit tests, and much more work to create and maintain.

there Queryset.query but it's not obvious unless you read every line of the docs

also manage.py shell_plus --print-sql will print the sql after each command you enter

you can also print qs.query, qs.explain("analyze"), etc etc

that's a pretty basic thing to spot in the docs and Django docs are actually godtier

I have 90% allergy to their doc style. The only worse are old javadocs. I like python stdlib model, so I assumed it would be as nice.. but here it's a blend of newb info (oh look Forms!) and partial technical description. To the point that every time you ask something online.. people tell you straight away try ccbv or "read django source", which should be the first things listed on their website.

you may be mixing up their `ref` type docs and the `topic` type docs which are more tutorial-y. compare

- https://docs.djangoproject.com/en/5.0/topics/db/queries/

- https://docs.djangoproject.com/en/5.0/ref/models/querysets/

Even the refs didn't fit my needs for a comprehensive structure of the framework. It's cultural issue I think.

Reply on news.ycombinator.com