ORM: The Sequel by Katie McLaughlin

Hi, I'm Katie. I'm a site reliability engineer at DVO. We do hosted Django as a service. If that sort of thing sounds interesting to you, come talk to me afterwards. Um and as Russell said, I do a lot of things around Django, but I'm not currently a Django developer. I still look at the odd Django admin page and go, oh my gosh, this is so cool! I can just go in here and I can see my records and I can go and edit them and my day is made easier. But the more I learn about Django, the more I understand how the admin works. It's powered by the ORM, the ORM, the object relational mapper. Which allows us to interact with our data as Python objects.

Now, here's where I could start teaching you about how the ORM works based on abstract thoughts and concepts, assuming that you know nothing about technology, but I'm gonna go a different way. You see, I might be new to Django, but I've been in tech for more than a decade now, and I have an eclectic background. I've been doing development in various languages like Ruby and Haskell and PowerShell and Cybase Power Builder and Oracle. And All of these things have something in common. When you're working with data, you need a database, and databases mostly have a shared language. SQL, structured query language. Yes, I know there's GraphQL now, but having

knowledge of how to write SQL is still a fundamental skill for developers, and it will be for a long time. And it's a good skill to have because it means you have a common context. In everything, context is important. For a developer coming at a problem from a different tech stack, being able to leverage shared skills is fundamental to be able to fast-track learning. So given that this talk is going to go a bit differently. I'm not going to assume that you're a little tiny bird who doesn't know a terminal from a graphical user interface. And we're not going to start with a blank project Instead, we're going to jump straight into an existing project, assuming that you know how to do SQL.

And we're going to go hunting for bugs So for our application, we're going to use something near and dear to my heart. Emoji. I'm giving a talk at a DjangoCon. Did you think I wasn't going to mention emoji? So, here is our sample project based on uh the website Emojipedia. My version is called Unicodex. It's just a little tiny Django application that shows how emoji appear on different platforms. So on the front page we can see that there's sparkles and a unicorn in a desert island and if I click on the sparkles I can see how the sparkle emoji appears on different platforms. There are a few different versions for Android and Apple, but as you can see there's a bug.

One of the records here has something wrong and the image isn't loading properly in our web browser. So we could jump in and use the admin to work out what's going on, but instead we're going to use something called the Django Shell. The Django Shell is a command line interface into your project. And a lot of my examples today are going to be shown using this literal terminal, which looks a lot like what I use every day, which is the ITEM2 terminal on macOS Mojave for those playing at home. So to get into the Django shell, we first need to navigate to our project, and then depending on what your project setup is, you're going to have to initialize your virtual environment, set up Pipben, run a Docker container. And then we're going to run manage. py shell.

Once you run that, you will get a command line interface into your Django project. And all we've done here is load the Django environment path into this particular shell. So from here we can interact with our Django project directly. And the shell is pretty powerful, but you need to understand how to call Django and ORM commands in order to be able to use it. So How would I, as a database nerd, interact with it? Well, an unknown system, the first thing I want to do is find all the tables. All of them. Now, depending on what flavor of database I'm used to, I could show tables. I could select start from dba. tables. But we're not in a database console, we're in the ORM.

So to list all the tables in a new Django project in the ORM There's no good way and this is what made me get stuck for a little bit because I had no idea what was going on. But turns out I worked it out and now I'm gonna tell you In Django, you have a project, and your project has multiple applications, and each of those can have one or more models, and the model is the powerhouse. The ORM manipulates model objects. But to be able to work out what models we have without looking at the code, because I don't know Django, I just have a shell, I don't know what I'm doing. I want to be able to somehow get a list of all the models so I can import them and then start working out what they do. But thankfully Django is clever and has a whole bunch of internal APIs that I can leverage.

So what I can do is I can use this particular helper script. You may not understand what it does right now, but by the end, if you go back, it'll make sense All I'm doing is going through all the apps of my project and for all those apps, getting all the models, and for all those models, generating an import statement. So if I go back to my terminal and paste this particular code, I end up getting a whole bunch of useful import statements. As a new Django developer, this is so very helpful. And by looking at this, I can see that Django's got a bunch of models there. And I can also see something called Unicodex, which matches my project name, which is really helpful to me, because that's probably the models that I'm most interested in. So we can see that there's code point, vendor, vendor version, and design.

And because I'm interested in the database, I want to know exactly what these models map to. So I can use the Django API again. and I can check for the DB table for my particular model. To be able to do that, I first need to go from Unicodex. modelsImportCode point, and then I can ask it for the database table. In this case, Unicodex underscore score code point. We are going to be referring to this table and similar tables a lot. Next thing I'd want to do is see all the columns in the table and I can do that with various database things again. Or instead of The other call that I had before, which was get db table, I can go get fields. And I can see all the fields against my particular table in this case. There's an ID, a name, a description, and a code point

So now I know what my database looks like. Now I want to see what's in there. I want to select star. To do that, I just call objects. all, which gets me a list of all the objects that I have. And it matches what I saw on the homepage of my project, which is really useful and good because it means that I'm in the right place, which is helpful. This is being returned as a query set, which we can act on further. A query set is not a list. As much as it may look like a list, it is not a list. It is special. So now that we know how to get all the things, how do we just get some of the things? How do we add a WHERE clause? Well, the RM makes it easy. We can add a filter. So I can ask it to filter on just code points that are sparkles, and I can get the sparkles.

But this is still a set. I want to get a specific object, and to do that I need to use get. And if I get it, I will get just a instance of the model. I won't get a query set. And get is important because get will return one and one only. And if you try to get more than one thing uh Python will explode marvelously at you and tell you that you have a multiple objects returned error. This one has hit me multiple times. And now that we've covered the basics, this is where joins come in. Anyone who has done any SQL for any amount of time has had countless interactions with inner joins, outer joins, leftmost, outer up, down joins, charm joins and it can get a bit confusing.

But thankfully the ORM makes it really easy and you don't have to remember all that stuff. If I wanted to do say a join on my code point and design table, joining on my foreign key, instead of having to do all this SQL, I'd just filter on something a little bit more complicated. I filter on code point double underscore name equals sparkles. If I run this in my terminal, I get a list of all sparkles. What's happening here is we have two fields. and we can go from one particular field to another field and we separate them all by double underscores and Django knows what we're talking about. By default, if we we need to end on a lookup. So

from what we saw before, we had a design and a code point. and a code point had a name. So we're not ending on a lookup. Django interprets this as an exact match. So in this particular case these two queries would be the same. As an aside, we've seen exact before. In the very first screenshot of an inside of an of the admin screen, it was sitting up there the entire time. The filters on the right hand side are just adding uh filters onto our query. And that particular one is is superuser double underscore exact equals one, which happens to match what my UI has selected. So the admins running this ORM stuff, that's pretty cool. So, what if we were to try to build the longest possible chain here?

We'd need to find out how all our models link together so we could jump from one to the next and one to the next. To do that, we would need to discover the entire schema. And we have tools to do this already. We could go and get all the fields for each of our models and work out how they relate together. Or we could use UML diagrams. UML, unified modeling language. And I am sorry we are going to talk a bit about UML because Even though you may have some terrible memories from your college or university days about this sort of stuff, for an unknown system, they are extremely useful to be able to work out how things interact together And because it's Django, we have a helper for this. It does require us to do some installing beforehand because while it's not core Django, it's still very useful Django.

So to do that, I'm on a Mac. So this is how you do it on a Mac. First we need something to visualize. So we install GraphFiz, which allows us to define dot models, which then have a graphical user, a graphical implementation that can go from text to pretty picture. We've installed that, great. Then we need the Python wrapper for GraphFees, and we also need this wonderful thing called Django extensions. So far, everything I've showed you is native Django I'm a SRE. I'm a site reliability engineer. I normally don't get to install random stuff on clients' machines. So finding out that Django extensions existed didn't happen for a while. until after the last time I gave this talk and someone told me about it. But if you have the ability to, please install it.

It is so useful for things that are outside Django Core. Once we install that, we have to tell Django that it's around. So we need to go into our settings file and we need to add it into the installed apps. And if you haven't used Django extensions before You install it with a hyphen and you add it to install apps with an underscore. That is very important to know the difference because I keep on getting it. missing that kind of stuff. And it's not just this package, it's other packages as well, because there's this thing called like valid characters that are like not a super set of each anyway. Unicode. Once we have that installed, we can start using the extra method that Django Extensions allows us to do on manage.

py. Which allows us to run instead of how before we ran shell, we can run graph models. And it takes a couple of parameters. In this case, I'm telling it to graph all of Unicodex and I want to output to a uml. png file And we don't get any output, but we do get a file on disk, which is this. And this shows how our different models work together. It also matches how the Django Project. com website looked like in 2005, so let's update it. In this particular diagram, we can see that the various models are interacting with each other linking on foreign keys. So a design has a foreign key for code point and vendor version. Code point has a bunch of values, but then vendor version relates to vendor. This is because we're in third normal form, and otherwise we'd be duplicating data about our vendors everywhere.

And how it works in reality, because databases sometimes reflect reality. It's a thing they do. A emoji is a code point in a standard, but the graphical representation on your device doesn't have to match the standard and it doesn't have to to match anyone else and they've evolved over time. So both Android and Windows used to be black and white and then they both got updated and now they're converging. And so we can see how these emoji have evolved over time. And this is what our project is designed to do. It's designed to show these differences over time. So now that we have our model, we can get our longest possible chain which we can start from the left and work our way over to the right. So our longest possible chain is going to be

well we have to use all the models here so we could import from all the things or we could just import from star because we're lazy And then we can start from one side and work our way across. So code point, objects filter, design, vendor version, vendor name, contains, because that's a valid lookup. Microsoft. And then we get all the code points that map to a design, that map to a vendor version, that map to a vendor that's called Microsoft. And there's a bunch of different lookup options we can use here. We can use like, we can use various forms of like, we can use later than, greater than, range is null and etc. And there's a whole bunch more. Thing is, we've been doing ands together and image and this and that.

We want to be able to do ors and nots and all the other fancy stuff. So this is where Q comes in. Q as in query, which allows us to build nots and OR statements in our WER clause. So let's start with some comparisons. We're going to go back to a really simple example where we want code points that are called sparkles. and have the description shiny. And this would be this particular SQL, which is going to be select staff from code point where the name is and the description is. We know that this is exact same as declaring double underscore exact on both because we're not declaring a lookup at the end. So going back here, what we could also do is we can chain our filters together. A filter returns a query set, so we can then

have another filter on that query set which returns another query set and we can chain these commands together which helps us build up some really complex filters But what we can also do, instead of just having these positional arguments, we can wrap these in Q. And this allows us to have more flexibility. In this particular case, we're chaining these together just as separate parameters as filter. But what we can also do is use AND, which will have the same SQL output. In all these examples, our SQL hasn't changed and these are all equivalent searches. What we could also do is we could just have one as Q and one as a argument. But we can't do it the other way because in all of this we still need to have valid Python. It's a little bit annoying, but

Another example of this is when we want to start searching on the same field. If we want to build up a knot or an OR, we want to see if something is called something or something. We can't put it together unless we wrap it in Q because it's not valid Python because we're duplicating our keyword arguments. If we wrap it in Q, they're both now positional arguments and it doesn't conflict. in Python's state of how syntax works. And from before we know that we can replace a comma with an ampersand, and from there we can have the union z where The code is both called Sparkles and Unicorn, but no such emoji exists. What we actually need is an OR, which will be OR. So it'll be either Sparkles or Unicorn

But how does that even work? I mean it's valid Python, but we're using this pipe character. Um, who here has done bit masking the last five years? Yeah. So this is a little bit of magic, but we're also about to see some actual Django core code, so don't be alarmed. This is part of what Django is doing. It's declaring the class Q, and what it's doing is in the double underscore OR double underscore or dunder or, which is the Python syntax valid way of referring to just the single ampersand and the single pipe because there are methods in Python that you cannot refer to by their actual names because functions have to be called certain things What we're doing is we're overriding what happens when two Q

objects are orded together or anded together, and we're combining them Which means that we're meta-typing in the middle of Python, which means that we can construct some really elegant queries, and that's great. And what's also great is this stuff's been in Django for 10 years now and it still works and it's still great and it still gets the functionality that you want. What we can also do here is we can start using nots. Using tilde as a negation, we can say we can have queries that say, I want it to be sparkles or not unicorn. And once we have more filters, we can have more results. We can start having more complex things where we want to check whether the filter the code point has a design that has an image that contains And we can throw this into our thing and we can work out what's going on.

But the problem here is we've got some truncated results Turns out this is a feature and not a bug. We actually have as many results as is returned. But because we're printing this out to the terminal and there is a feature in Django that means that it stops after 20 seconds. records because otherwise it could print out a million records into your terminal which is an actual bug that actually happened and now they have this feature. You're welcome. What we can do is we can check how many results we're actually going to get with count, and we can see that there's 44 of them. But how does this method actually work? What happened is, well, that's the thing. I could show you the SQL or I could show you how to find the SQL of your own statements. What we can do is we can from Django DB Connection import

queries, and what we can do is ask it to return the last query that it used when it connected to the database. and we get out some interestingly formatted SQL, which I can then prettily format out for you. We're doing a select count star on the tables with the join with the things. But why don't we start changing things? If we have a count here and what we're doing instead of having the filter, we have two filters. If we want to filter on design image contains and then starts with, well, we can run the query and we can see that there are far too many records to be reasonable. And if we check the query We can see that we've got a Cartesian product going on, which if you've been a database developer for any amount of time, you know that these are the bane of your existence.

And trying to find out these things before they catch you in production is really useful The problem here is that we're joining on two tables, but we're not connecting them together. So it's having all of the results and all of the results and multiplying them together. So instead of 44 results, we get 700. But what we can do is we can also check what we're going to run before we run it, where we can run dot query on our particular filter. And if we ask it for a string representation, we can get some SQL. You can use this to check kind of what your query is going to be executing before you run it, but it will not be the exact same. Why? If we go back into our terminal and instead of running this particular filter, we save it to a value.

We haven't executed anything yet, so there's not going to be any query. Because we started a new terminal, we don't have any state. But if I check for the string representation and then I execute it and then I compare the two SQL queries, we can see that Django has added the limit 21 for us. So just if you're going to be using the doc query, just Be aware that it may not be the exact query and if you want to check it, check what's what's actually run against your database. So putting it all together, let's go bug hunting. Let's go back to the beginning. Let's try to find where this particular error occurred. Let's find the bug. First thing we want to do, load up our new shell. But because we've got Django extensions installed, we can use this cool thing called Shell Plus, which means that everything's

imported for us automatically, which is really useful. As long as you remember to always use Shell Plus, because otherwise you will forget to then import your statements when you're on the native shell again. Ask me how I know. So we want to find the particular uh design that's broken. And we know that that design is a code point sparkles, and we also remember that it was Twitter. So we want to filter on codename sparkles, vendor version, vendor name, Twitter. And we get back all the Sparkles from Twitter. So from here we want to get just the one result. We can save this result in a variable, and then we can see that we've saved in the variable, and then we can do our get in a number of ways. We can get just the first

instance the the the index zero of the particular object that we want or we could filter further where we check the exact 1. 0 name And then, since this is a singleton query set, we can then get it. We can either get by adding a get onto the end, because it's one and get only does one, that'd be great Or we can change our listing to be get in the first place, and then we get our design. From here, something is wrong with the values in this. So what were our fields again? Ah yes, we had a file field called image. And if we use that to work out what the value of this field is, ah , found the bug. That's not a valid file name. That's why we had a broken image. We found a bug, it's an actual bug emoji.

So The ORM is great and all, right? But what if the ORM doesn't do what we want? What if we just really want to drop into raw SQL? Well, don't. Never use raw SQL anymore. But but but but but but but what if I I want to do aggregation. Well the ORM does aggregation for you. You can do counts in aggregate and you can see how many objects you have. But but but I want other calculated fields. I want to be able to do uppers and I want to be able to have calculated queries and stuff. Well you can do that anyway with annotate. You can amaze your friends by how much you can do with the ORM natively without dropping in to raw SQL. But but I want subqueries.

I want to be able to have a query in a query so I can query where I query. Well you can just do that anyway. But but but explain plans. The RM doesn't do explain plans. Yes it does But but but it doesn't do this obscure Postgres command. Well you can just write your own query expression. But but but but the docs the docs say that you can fall back to raw SQL Yes, the docks do say that. But these docks haven't been updated in ten years. The warning about how ROARSQL can subject you to SQL injection attacks has been added five years ago I will be sprinting on updating the documentation to make sure that it is up to date with the current state of how

the model query APIs mean you never have to use RAW again. No, really, don't use RAW And that's my time. So discover more yourself. There's a bunch that I didn't cover today, including uh F where you can actually interact with your fields. There are more functions And there's also how to actually create the models in the first place, which I still don't really know how to do, which is fun, and also the types you can have and the relationships between them. And that's all I had. Hopefully this will help you go and scout and find out bugs and also be reminded that Even if you've worked in any other kind of tech stack before, any other kind of industry before, you have skills and you can apply these skills in many ways. And you never start from zero. You always build on what you know

Thank you for your time.