Deep Inside Django's ORM: How Django Builds Queries

Speaker 1: Yeah, thank you very much. Welcome to my talk about some of the internals of the Django RM. I've had that quote on my last talk at PyCon in Germany in April by Enfre Godwin. The ORM is the majority of Django. So that's true here even more because we are going to have a look in the inner mechanics of query set query managers. as you will see. But first let me introduce myself. So my name is Bas. So my real name is Sebastian Steins. I'm a German living in the Netherlands.

Speaker 1: And uh for my Dutch friends I go by bus, for my German friends I go by ZB. Um I'm currently working for a company called Milteni Biotech and there we produce devices for lab devices for blood cell analysis and there's a lot of uh biological and physical knowledge in there and my role is actually to teach um python to these biologists and physicists I've been programming since the age of fourteen, using Django since 2008, three years after its inception, which makes Django already kind of a boring technology

Speaker 1: Which I would consider is a good thing. So what can we expect from this talk So we will go through the call chain from manager to SQL compiler. We will see how laziness works inside the RM And we will see how the RM manages what is called where nodes. So these are the trees in which um complicated um filter expressions live. And at the end we will have a short look at database side views. So a little word of caution here.

Speaker 1: Um the class structures I show here, they are not officially documented. And the direct use of some of the methods I will show you is discouraged. And that is for basically two reasons. First is uh the architecture of these classes is so well designed um that you can easily hack everything you want. um via publicly documented APIs, such as managers or unmanaged models, as we will see later And second is especially with like regard to uh the ongoing work on async or m These classes might change over

Speaker 1: time without further notice. So anyway, we are curious to see how it's working, so just be warned but follow me along So we start with a very very simple set of models. Actually we only use the block model here and this should already s uh look familiar to any one of you. So and then we have basically the hello world query of Django ARM, which is just the select star without further WAR Clause. But we can try to deconstruct it. So what do we have here? So first of all we have blog, capital

Speaker 1: B singular, which is our model. Then we have objects, which is our manager, which is implicitly created by Django's ORM when we define the model. And then we have all. This is basically again select star without further filter. And all of this gives you a query set which is then stored in blocks lowercase b plural that's what it is about So from this very simple query you can already deduct some design goals of Django's ORM There are no SQL like

Speaker 1: words, so I mean we have filter, but we don't have where. And um there are also no SQL strings, um like partial queries or something like that, which you might have seen in other RM frameworks such as Hibernate or or the like But merely all of what we do here, all of how we interact with the RM is just by using pure pure Python function calls. So and the call chain, so basically from your intention to get to some objects to actually hitting the database, um these are the classes which are

Speaker 1: w which play a role in that game. So we start with the manager. Um the manager creates a query set And the query set creates a query, and that query is passed to an SQL compiler class, which eventually then puts out the string, select, blah blah from where and so on. So what the SQL compiler now does is um I copied the uh doc string of the original method. It gets you an expression, the SQL with params

Speaker 1: and an alias. And we will see what the alias is about in a couple of minutes So what it needs is the base model of the query. That's logical because from the meta class of the model it can deduct the base table It can determine uh the order of the columns in the model. Um this is also relevant because um uh the result sets from the database will come in a enumerated way and not in a hashed way like it like in a dictionary And then we have related class infos which are mostly for for um compiling joints.

Speaker 1: So this is how you how it looked like if you really looked into the uh the internal uh internal attributes of that query class. So you might have already used the qs. query uh alongside with print So that gives you the actual SQL statement. Um but this is okay in this case it's cached, um but on the way how it is compiled is There are a couple of instance variables which are quite important for it. First one is alias map. Alias map actually is a dictionary

Speaker 1: with the table name as a key pointing to a data structure called base table to reference the actual table. So then there is the alias ref count. This is important, this set here to zero because we start counting at zero as always. But it would increase to one, two, three if we add additional joint tables into our lookup. So this is automatically increased then. So and then we have the base table, which is actually derived from the models metaclass. In our case it's demo underscore block.

Speaker 1: So even if we have seen the SQL query here, that does not mean um that we have hit um the database at all. So what makes a lazy RM start doing its work? Or in other words, um Unless you a as long as you don't call specific methods, the dB is not hit at all. Um and we will now have a look at what these specific methods are. So most of the time you can like uh chain more methods to to a query set like adding other filters, adding other

Speaker 1: include statements uh exclude statements and and the like. But still this does nothing uh with the database. Instead it would just return a clone of that original query set with the additional filters applied. So and these special meas m methods that triggers the execution um are mo in most of the time special Python dunder methods like think double underscore. So for example the query set implements an underscore underscore iter underscore underscore method. This one is basically used when you write something like four block

Speaker 1: in QS in your in your code. Same goes for underscore underscore len underscore underscore which gives you the count , not the SQL count, but the actual instances count of your query set. And there is also get item um which allows us to um basically limit um the number of returned records. So that's basically Django 's way to implement different limit implementations for different database vendors. So now now the question is, what if we add

Speaker 1: filters? Um like if we don't write blogs. objects. org, but blogs. objects. filter and then put some uh keyword arguments in there So what happens is um whether we write filter or exclude, both of that ends up in a method called underscore filter or exclude in place in the query set class. So what happens here is everything you put in the in your filter method ends up as a regular Q object. And these Q objects are very well documented. So Um there are a couple of advantages here to do the

Speaker 1: do it that way. The first one is the query set class itself. that doesn't have a special case for filter or exclude, it just handles Q objects all the time. Um and The second advantage is that when you are dealing with Q objects anyway, you can customize your query very easily with well-defined and publicly documented APIs of that Q object. So the takeaway here is that filter and exclude are just abstractions on the Q objects which are then used internally. So out of these Q

Speaker 1: objects the Django RM will build something which is called a WHERE node um which is actually can be seen as a tree and it is stored in the instance variable where of the query sets query like blocks. query dot where will give us something like this if we apply just like this simple filter So what now if we add a more complex filter? So for example we want to have anything starting with A or having the ID of 1. So this gives you like a structure which actually looks like that.

Speaker 1: It has and or exact call demo block. It looks like so you don't have to recognize that immediately, but i i if you have a look at it w you will recognize how these things are coming together. But that whole thing looks a lot like a Lisp source code with all the brackets around and stuff like that. So what happens here is you have combined two filters, one exact filter and one ice starts with filter with an OR. So that's what the OR is for But what is the end for

Speaker 1: Any idea? What is the first and for in in in that where node? There were two?

Speaker 2: I think it's like when you have more than one interest, you add them together and demand.

Speaker 1: Yeah, exactly anywhere. Exactly. That is just if you will a preparation for the next change filter to come. So the next filter will be added with an AND. And this is really how it works. This is the original source code of the method that will add the Q object to the WERE node. Um there's a bunch of th uh stuff we can't cover here now for for uh brevity reasons, especially when it comes to joins. But what you see here is there is a connector And that is exactly what you said. That is the end connector for the next query.

Speaker 1: And it figures out if I have a branch in my node or in my tree which is negated or which is not negated. And based on these branches I do a tree traversal uh with that simple for loop here and I end up with very complex um where node uh tree objects. So, one practical example Um now that we have a little understanding of the inner mechanics, um and that is something I've really seen in production

Speaker 1: level code, um, is now well you have a model And this model is obviously attached to a database table, but for some reason, maybe performance or whatever, the same information of that model is stored inside a view in the database. So what the idea was here is well I want to query the view for my read access. But what I want to get back from my RM is still the original object. So how could we do that? Any idea?

Speaker 1: That's the better option. Yeah, abstract models, unmanaged models, yeah that's certainly the better option. But I show you the worse option first. We we we can monkey patch the query object So as I told you there is alias map, alias ref count, table map and base table, and I can just overwrite it, change the behavior of the SQL compiler eventually that way. But still end up with um an instance of my block and not of my second block or anything else And now yes, there is a better and obvious and more obvious way to do that

Speaker 1: by leveraging unmanaged models. I can just inherit from the model I wanted to query. set the meter meta options managed to false and specify the database table I really want to query, which in that case would then be a view. So now that we have get our intention to to seek data from the database brought it all down to the database. Now the question is how it comes back. So what steps are taken to get the results from the database and then on the way back create sorry

Speaker 1: create a model instance for us So, and this is pretty straightforward actually. There is in QueryPy that model iterable And it just takes the results uh the compiler has given us and instantiates um Like mo uh our model class. So and here now you know where why all these internal internal mapping fields have like numeric values attached to it is because um the database doesn't give you like a dictionary back but a list and you have somehow to

Speaker 1: um have a correspondence between the name of a field and the actual position of that field and that is here in that um in in that um list comprehension done. So then a final word about managers or um No, not that one. Um but managers in general. Um The recap of the class architecture I just showed you is manager creates a query set, creates a query, and finally it goes down to the SQL

Speaker 1: compiler. So each of these classes add one layer of abstraction to my whole query or to my whole intention. Um and almost every need you might have for custom queries can be met by writing a custom manager because the custom manager can you have access to all of the underlying mechanics so you can just do that inside your manager And as a short example, this is again like a very trivial one on the hello world of ORM managers is say you have You want to implement a soft delete model, which is whenever someone deletes something,

Speaker 1: you don't want to drop it from your database, but instead you set a flag, delete it And when you read it, you just what don't want to have everything which is marked deleted. So of course there might be GDPR issues here, but that's another story. So what what you do is you just add a deleted field to your model or any other field which can indicate a status And then you get another manager for that object. I created a block manager. And what that manager does is it gets it overrides the getQuerySet method. But it doesn't really just overwrite it, but rather

Speaker 1: um it gets the original query set from the built-in manager and then change another filter call to it. In that case, filter everything deleted equals false. So what does that mean in consequence is that whenever you now write block. objects, dot filter, dot all, dot get, whatever you will never be able to access these deleted things again and that is why because in that case this particular manager you have given the name objects. which is the default name for managers in Django ORM.

Speaker 1: So if you want to have access to both, deleted and undeleted, you would just have to give it another name. So um I just heard we have a couple of minutes left. Um I try to cover almost everything here Um thank you for for listening. We might still have a little room for questions. Yeah.

Speaker 3: Yes, I think we can do one, maybe two questions. So please raise your hand if you have a question. Um you are in the front.

Speaker 4: Uh I don't know. I want the hard question about uh deep into the uh query. Hard question is um at first uh uh you show how you use query, but in this query you can use for um class uh join klass and few klass VR class. You can define the complex filter. You can define more complex filter than in normal OREM. This is a big difference. And uh if I have complex uh complex um complex query, what is better? To use a row query

Speaker 4: Or I should use undocumented query joints and query uh and query extra there classes. What do you think about it?

Speaker 1: Okay, if I got the question right it is about the more complex stuff which query set doesn't give you access to. So the question is do I use like the query class? Uh like I showed you here or instead a raw query. Well I would go for the raw query because it's it's documented and it seems more stable in its implementation. But I I know the the there is not uh that's maybe one of the questions where where where there is no silver bullet for all. And I remember our last discussion, so that's something perfectly fit for the coffee break