id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28939	QuerySet used by prefetch_related() does not use expected connection.	Nick Pope	nobody	"**Update:** I should have mentioned that I was using a custom database router. I now know this issue occurred due to a bug in that router.

----

I've run into an case with prefetching where the connection used for the prefetch queries is not the one I would expect:

{{{#!python
# When using the default database connection:

In [1]: from django.db import connections

In [2]: connections['default'].queries
Out[2]: []

In [3]: connections['readonly'].queries
Out[3]: []

In [4]: User.objects.prefetch_related('operators').first()
Out[4]: <User: example>

In [5]: connections['default'].queries
Out[5]:
[{'sql': 'SELECT ... FROM ""auth_user"" ORDER BY ""auth_user"".""id"" ASC LIMIT 1', 'time': '0.205'},
 {'sql': 'SELECT ... AS ""_prefetch_related_val_user_id"", ... FROM ""operator"" INNER JOIN ""operator_users"" ON (""operator"".""id"" = ""operator_users"".""operator_id"") WHERE ""operator_users"".""user_id"" IN (1) ORDER BY ""operator"".""code"" ASC', 'time': '0.011'}]

In [6]: connections['readonly'].queries
Out[6]: []

# When specifying the database connection to use:

In [1]: from django.db import connections

In [2]: connections['default'].queries
Out[2]: []

In [3]: connections['readonly'].queries
Out[3]: []

In [4]: User.objects.using('readonly').prefetch_related('operators').first()
Out[4]: <User: example>

In [5]: connections['default'].queries
Out[5]:
[{'sql': 'SELECT ... AS ""_prefetch_related_val_user_id"", ... FROM ""operator"" INNER JOIN ""operator_users"" ON (""operator"".""id"" = ""operator_users"".""operator_id"") WHERE ""operator_users"".""user_id"" IN (1) ORDER BY ""operator"".""code"" ASC', 'time': '0.010'}]

In [6]: connections['readonly'].queries
Out[6]:
[{'sql': 'SELECT ... FROM ""auth_user"" ORDER BY ""auth_user"".""id"" ASC LIMIT 1', 'time': '0.002'}]
}}}

In the second case I would have expected all queries to be executed on the {{{readonly}}} connection by default.

This can be achieved by using {{{Prefetch('operators', queryset=Operator.objects.using('readonly'))}}}, but it means that plain strings cannot be used and often a lot of nested {{{Prefetch()}}} objects can be required.

A solution to this could be to do the following:

1. Use the connection from the original {{{QuerySet}}} that called {{{.prefetch_related()}}} by default.
2. ~~Possibly add {{{QuerySet.prefetch_related(using=...)}}} to allow overriding for all prefetch queries.~~ (Doesn't fit nicely with the API.)
3. Possibly add {{{Prefetch(using=...)}}} to allow overriding for a specific branch of prefetching. (This would propagate down.)"	Cleanup/optimization	new	Documentation	dev	Normal		prefetch, prefetch_related, using, connection		Accepted	0	0	0	0	0	0