A SQL for Django | Stefan Baerisch

Speaker 1: Hello everybody. Yeah, SQL for Django It's mostly a talk because you know well it's started right at the beginning. I'm mostly a Python user and an occasional Django user. So Django is not really my original focus. Which is why I learned SQL before I learned Django. And I always went back and forth between either using the Django RM to implement something or using just a SQL statement. And I realized, well, I think we most have, that maybe not so much in our community, but in many communities there is They are strong opinions about whether an ORM or SQL or Query Builder

Speaker 1: is the right approach for particular problem. So I just like to share my thoughts, my finding, not to tell you what to do, because that ultimately depends on what you are more comfortable with. what your application demands. But maybe just to give you some thoughts if you are on one side of the spectrum or M user or the other SQL purist. Okay, now the good news and maybe I'll already share the conclusion is that in Django you can decide both to use the ORM and well basically as a query builder in the orm and you can decide to use SQL. You even have multiple ways in which you can use SQL depending on how much you want to use Django functionality or

Speaker 1: want to well be very specific on the terms under which you connect to your data. base. So Django is a large toolbox and well probably speaking we have two hammers for our particular problems and we can use both. And yeah it's Let's maybe start with some well preconceptions almost about things where an ORM should work well and where SQL is the right approach. For the moment, let's just assume that we go for an either or approach. So you would write an application and you want to decide I wanted to do either everything in SQL. whether it's practical in Django or not, or I was just want to use the RM and I never want

Speaker 1: to know anything about SQL or my database in general. Now what an OM always gives you is very nice and comfortable functions to create objects, manipulate them, delete them. get something that you can pass into a view, iterate over and ultimately render very, very comfortable. In SQL you'll have to do most of that by hand. So you'll have to write some queries and then you'll have to write tons of borderplate code to actually have objects that you can pass along. You have in with an ORM be somewhat careful if you work with complex object hierarchies in order to well not to run into too many or too inefficient queries. In SQL

Speaker 1: you see what you write, so you see at least your query expression. You don't normally know what the optimizer is going to do with uh with it. So you'll have to do more work, but maybe it's not so likely that you'll step into any particular traps. And if you work with analytic queries, so if you don't want to have objects, sometimes you need to think about or you normally know how your data should be stored. Structured. So you have some, let's say a tree, a specific table in mind. And then you'll have to think on how do I best express this with the methods of the ORM. Well, on the other hand, you can directly work with SQL. So what we already see is there are different scenarios and depending on the scenarios, we might prefer one solution or the other.

Speaker 1: Now, one idea could ultimately be that Django's ORM can only work with simple objects, and for everything else, you need SQL. especially if you talk to other people and compare Django to SQL Alchemy, you'll often hear that a SQL Alchemy has this nice expression builder and this. more capable and that Django is quite limited. I don't know the details of both systems, but what I found in my work in preparing this talk is that Basically, everything I want to do with Django and Zero RM, I can do with Django and Zero M. Just to let you know, this is my small example database The only thing that you need to notice is that it is quite join-heavy.

Speaker 1: So I have quite a lot of foreign key relationships in there. Just so I can show you to well how things look if you work with multiple table And then there are well some dates in that it's basically an order database. You have customers, you have salespersons, and you have products that are sold. Okay, now we'll look shortly over multiple scenarios on how to lose the Django ORM. And we will compare them to the SQL that Django creates in the background. The motivation is, well, twofold. First of all, to see how things look in Django and also how they would look like if you did an MSQL. So first of all, and we can keep this extremely short

Speaker 1: you can work with objects so i can create one of my customer objects mr example who has a discount ratio of 10 and then i can work with this customer change his discount delete him again, etc. It's just well what we do every day. And here I don't even see the SQL because I guess you can imagine it. And the interesting thing is that this particular use case At least in my experience, accounts maybe for 70% of all queries that I do, that I just want to have some specific objects, manipulate them, pass them into your view, and then be done with them. Now, of course, we can filter them. And here things already get more interesting because here we comp if we look at the SQL as well as at our

Speaker 1: Django query, we already see that they are quite close to each other. So I say, where do I want to get my information from? How do I want to limit the information that I get with in Django with this two filter? clauses. I can limit the information I want from a database so I don't select the whole customer object just the last name and then I can order by discount. see the well SQL query does not really provide any surprises. So I think this is very familiar and just still something that I can well we'd quite well. Now we can of course get more complex and I apologize for the rather tortured example. I don't know what I want wanted to just multiply the discount. But as you can see with the field references, if you just want to

Speaker 1: have a filter that refers to other fields or to the same field twice. It's easily done and well again it almost looks like what we would do in SQL. So we can go are minimal object manipulation and also we can get from Django just the data that we want so just the last name of a couple of customers Of course, I can also build a more complex query. So if I want to combine two query expressions with an all, I can do this in Django, and I can do this in SQL. So far we say Django and SQL scale quite nicely, but just looking back at first, we usually see that our Django solution is small.

Speaker 1: for the simple cases not just not so much writing and of course i can also um do annotations so if i want more than just a kind of um a comment an object. I can add some extra information. I can say the information that I want. So here I want to have the doubled discount for what reason what server. I can also filter by specific I Decent again. What we get in SQL looks amazingly. Well, we were at the similarity between SQL and um Django. In the interest of time, I'll just rush things a little bit along. So what you can see is especially you can do all the interesting more or less analytical queries

Speaker 1: joints annotations etc that you might want to do in Django and it is usually shorter than in SQL. So in Django to go over a hierarchy or well some relations of different tables you just have to combine them. In SQL you'll have to quite write quite a bit of joints. So and of course you can do aggregations in Django. So even simpler analytic queries where you don't really want to have objects but just want to have some well sums, averages about information in your tables, you can do this. And it is again well not much code in Django and also not too much code in SQL. So yeah, you can go along

Speaker 1: it. If you wanted to do really quite interesting tables, so if you wanted to go join information about customers and salespersons. or are the other ends of the object hierarchy and wanted to combine some information about them and then wanted to order about specific date ranges. You could do this in Django and you can do this in SQL And now, well, we're maybe a little bit a victim of code formatting here, but you might think, at least I do, that at this point in time the Shango query also looks quite complicated. So we maybe at a certain point in time we lose a little bit about the readability that we are used to. And yeah, this is basically one one of the reasons why I sometimes

Speaker 1: go back to um SQL to build my task.

Speaker 2: Of course

Speaker 1: there's also one other example that always comes up if people talk about problems with RMs. That's the one uh one plus one query problem. So let's say that I wanted to have all my orders. And for all orders that I have, I wanted to give a print some information about the associated customer and salesperson. And knowing Django, you probably know what will happen here. I have just one query to get my orders, and then for each sales person and customer, so for each order, I have to send two queries again to the database in order to, well, make sure that I have every all the information that I need. In this example I had about 1,000 orders. So I have slightly more again. So ultimately I ended up with three thousand

Speaker 1: queries. And that while every query in itself was simple, that took a while Of course. It's not hard for us to um solve this issue. So just add some select related But this is a point where maybe the abstraction that the ORM provides us with does not actually help us anymore. Because now we have to think not only about what data do I want to have out of my database. but also how do I structure my query in my database model in order to make it easy for Django to give me the right things in a performant way? And I think most of us have well sat in front of their application and had a little counter going how many data database queries for a specific page were fired, and then trying to optimize things down.

Speaker 1: So, well, perform. gets better and there are at least less queries executed. Yeah and this brings me to well to all the good things and maybe the bad things in Django and just to compare them and how things look for SQL. Now why do I want to do this? So my main reasons was Django OM gives us everything But SQL can give us other things that are also nice to have. And just as an example, the previously mentioned query problem. Writing in SQL and getting all the information that I need. I think it is clearer looking at the SQL

Speaker 1: what we want to accomplish. So we just can specify all the data that we need we can just say how we want to join this data and then we can execute it. We have a certain, let's say, separation from the data that we want and from the data processing part in general. And well, of course, we execute exactly the SQL that we've written up previously. That means that by using SQL for complex queries, you can achieve a certain separation. of concerns. Well, normally you would need to think about the Django query and how you want to process it. You can first think how do I want to, what target data do I want. And then basically

Speaker 1: refactor all your query logic, all the thinking about how do I get best get it into a SQL query. And then from the SQL query, either build some objects to pass into your views or well. just use the data as it is. So that can be helpful because it keeps it makes one complex problem into two smaller ones There is also sometimes this performance optimization problem. So oftentimes I mention that I find myself in a situation where I write something where then look why it is not performing as I like to and then look at the queries and then start to optimize. Versus in a SQL world for such complex

Speaker 1: queries or queries that you run especially often, you could just write the SQL, maybe optimize the SQL, and then integrate your information into Python. Which again breaks the problem down. For me, it makes it easier to think, but it well, it depends It is also again an issue where you can, well, how to break down your code. SQL has come quite a way since let's say the early 2000s in terms of how you can structure your code. And for example, with this thing, a common common table expression, you can just use basically subselects to define temporary in-memory tables, not normally not persisted

Speaker 1: That you can use in other queries. So you end up with really quite readable code and also quite modular code, and not these, let's say, monster queries that, well, we often dread and fear if we think about SQL. And for persons like me who are slightly lazy and use an EDIM an EDR Writing Pure SQL if your um IDE knows about the database is also quite easy and fast because you get code completion for your database your idea looks and how your tables are structured and yeah you don't really have to down all the joint clauses all the things that you want but you can just concentrate on simple things.

Speaker 1: And this is something that well with Django, even with good IDEs, you don't get to that extent. The EDE does not necessarily know how your model is structured. especially if you use this filter syntax with the double underscores to access different functions and fields Yeah, and ultimately, and that sometimes comes up in my daily work, is that I work with more people who know C then who know Django. So if you have business analysts, data scientists, or even some business users, and they want to specify a specific dashboard. They will sometimes find it easier to specify in SQL query that they want than to

Speaker 1: give you the exact details in a Django. queries. So SQL can also serve basically as a lingua franca, as a method of communication between different fields. And this also applies if you work with colleagues who are maybe not from the Python world, but if you have another team with another microservice using Java or Go to offices over. Now and the last thing, again, I'm slightly lazy in this regard, is if you want to know how to, well, basically wrangle data into a specific format or how to do a sliding window function, things that get not um complicated. It is usually easier to find answers in SQL, even if they are not for you your specific database, and then adapt them to your database, than it is to find um

Speaker 1: let's say up-to-date information in Django because well Django develops and changes a bit. These are my main reasons why I find SQL instead of the Django OL. Interesting in some parts. Now the good news of course is we don't have to decide on either or. And this is what I found extremely well made me quite happy when I realized how many possibilities I have. So I don't have to ignore Django ORM and all my objects and my well nice models. But I can just say that for a specific model and the query manager, I want to send a SQL query. And the SQL query will then return the data that I need to build my objects.

Speaker 1: And then I have a list of objects. Well, more lists of objects than a Gravity said, which I can use in my views or which I can iterate over and which I can save. And that is quite flexible. So as long as the data returned from your data database query is what Django expects as fields for the model. You can do whatever you want. So here I have this SODU with MSQL where I just defined some um literals and I can consider this a specific object. So you can if you want to have read-only access be quite flexible in how where you get the data from that you would like to use use and you could maybe even use existing object definitions with databases that do not conform to the um

Speaker 1: scheme that you got there, if you want to. You can even say that I just want to select some data. So maybe I just need the idea in the first name in a list view and I want to only show the uh last name and the discount or any other object attributes when I go to details. And Django will do the right thing. So it will get the objects based on the ID you will have already in memory the first name, but you can look up the last name. This will end in an additional query. But the good thing is it will work. And of course you can pass in parameters. So if you want to protect yourself from SQL selection SQL

Speaker 1: injection attacks, Django has you covered. It uses a slightly different syntax that you would sometimes be used to, which is basically well old style swing quotation. But you can put in an area of parameters and you get what you want. So if you're more familiar with SQL, if you s well as I am, if you sometimes like to read SQL more than reading Python code, it's a quite good way to get the data that you want. Of course, you also lose something. For example, if we send a query asking for specific customers and reduce this filter criteria, and we only want to have the one with the highest discount By default, the SQL query will give you all the give you all the data and your query set will have all the data.

Speaker 1: So this is executed earlier than we would normally have in Django and to get only the first thing of the list you'll have to well write your SQL query accordingly. The same goes basically for pagination So again, sometimes you'll have to see what queries get executed in order to not be surprised on how many there are. You can also use this raw SQL approach for annotations. Of course there are additional, you'll have to be especially careful. That's so that you annotate the right data with the right data. But you could, for example, decide that you have some rather complex queries that you'd like to have to annotate your custom object. And then use a standard

Speaker 1: Django query and annotate with a raw SQL statement. This again might be of interest if you already know how your business users or your domain expert structure their. queries and you already have queries that you can reuse. Now that was raw sql. So again what it gives us is the ability to use our existing models with handwritten queries. So I get model instances back by handwriting a query and sending it to the database. I don't have to do that. We can additionally just say, okay, I get one connection from Django's connection pool and I use it almost like a normal Python database call. So you can write your SQL, execute data, and you get rows.

Speaker 1: And then you'll have to build your objects yourself, or if you don't need an object, don't do that. So this is very nice if you don't have an object that you should return, but maybe a purely analytical query. And of course, you are always free to bypass Django directly and just go directly for the database driver, SQLite in this case, but could be Postgres or anything else should supported by Django or even not supported by Django that you wanted to use. And then you get the well additional things that you can use database driver specific functionality It could also be interesting if, for example, you have a Postgres instance with PG Bouncer and you for some of your queries you want to use functionality that is not supported in your PGBouncer

Speaker 1: configuration, depending on your session management. So well, we have many opportunities. And then in the interest of time, because of my hiccup, I'll just say The problem of course is boilerplate code. Writings, just pure SQL, can be quite long, but the good news is we don't have to do that. Another drawback is loss of extraction. So if we divide SQL, we normally don't have nice objects. Again, that's not true with Django. Django, we can decide to use SQL to get our objects and to a certain extent have the best of both worlds. And of course, we would, if we completely work without Django, use many nice features in Django, but we don't have to do that.

Speaker 1: that. What we do have is choice. So we can decide for different parts of our application to use the RM or to use raw SQL and still get objects or to use the Django connection pool and send SQL or to use native connections to the database. And we can also decide in principle to use Django models or use the data description language of SQL. So basically building our tables by hand. And with this mix approach, you can basically have the advantages of the URM and of Django in one application. So you can use Django models. to define your database to have nice migrations and then use SQL for queries where you think that maybe jumbo

Speaker 1: is slightly too complex or where you really, really have to fine-tune how do you get your data and how you speak to your database. Yeah Whew, caught up. I hope I didn't rush you along too much. Again, sorry for the interruptions. And if somebody could just um again print the link to this QA session in the chat because again my My computer went down and I just lost some browser windows. Okay, hello again. You're so much faster than I am.

Speaker 3: Okay.

Speaker 1: Can you indicate whether you can hear me, please?

Speaker 3: Yes.

Speaker 1: Okay, great. So the output device is still wrong. Give me a second. Okay, so now it should work. I don't want to talk about Stumped silence. You know, if you don't have questions, I will just keep talking. And that might get boring, so Okay. So ah

Speaker 3: Hello.

Speaker 1: Hello.

Speaker 3: Hi, uh very interesting. Um I I I have a personal connection to this topic because I worked uh using Django. uh a few years ago uh with a very big uh let's say analytical workload project and it was very interesting to see this happening because um I was kind of pushing for it back then In the end it got rejected because everyone preferred to use the ORM. And the reason was very strange in the sense that some people knew more how to use the ORM Then how to read and write SQL, uh which is the opposite of what you just uh kind of showed. Um And I I wanted to ask you how what's your opinion on the maintainability and evolution of of this?

Speaker 3: Also because for example if you have uh changing Django models somehow often then you have to adduce all all your SQL uh code. Also you would have to adjust all your mole code, but anyhow, w what's what's your opinion on this?

Speaker 1: Yeah, the the question with it you have to change both and I think the solution w need to be both the same. So watch the Django code you would write test cases to well basically you would mostly discover it during a normal smoke test because if the model instances are different it will be quite obvious. And you would need to do the same with SQL. So what I usually do is put the SQL statements into a separate file. So SQL file and just use um comments to basically grab the SQL out and put it into Django. And what you do do there is just to ensure that the SQL queries for a different uh for a defined data set still res. return results and the right results. And it you can even go the way that you say I can't

Speaker 1: this SQL statement should result in this Excel, or it did result in this Excel. Let's say you take the first hundred rows or so and then just do um basically a comparison test. Did anything change that we would not expect to change? And then you should see whether things things still work and whether they still get the same results. So, well, I think it's like I said, ultimately if you change your model often, you'll have to change your code. You can of course abstract if you say I have a layer, even above the model layer, where I just work with, let's say, query business objects where I already have pre-aggregated data or something like that to localize the changes But I think this is not again specific on whether you use lose the RM or whether you use SQL

Speaker 1: statements.

Speaker 3: Thank you.

Speaker 1: Thank you. Yeah. That's it it's interesting. So another interesting use case I think it was in Django 2 , S3. 2 where there were some new changes with the interface to JSON fields. And this can also be something where things might get interesting if you use something that is very specific, quite new, maybe even database specific. I mean we have this strong relationship to Postgres and you really want to use this and it doesn't fit your object model. So Yeah, it's strange. I think ultimately it comes down to to your questions, liking the OM or not, what you learned first.

Speaker 1: So for me it was SQL. Because we did this in university back with C back when we learned C. It was much worse SQL back then, not even the nice joint statements that we have now. But well that was basically the first. So I could always in my head impress um express things, okay, I liked I like this data tab, I know how to do this in SQL, and now I have to translate to Django. And for somebody else who has worked more with Django and has to translate it into SQL, the other way around. So I think there are just benefits in knowing both, because both have their strange and weaknesses. And well, just one thing with with the ORM, you saw that I included always a query that um gets output. To a certain extent you'll have to understand and read this query.

Speaker 1: So especially if you're not 100% sure whether you did use Django right, you'll have to check whether the query, the joint criteria, etc. are what you imagine they would be. So you should have a reading relationship with SQLizer. think. Okay, anybody else? You know, I I just see this wonderful wall of uh of letters, M I G C J M M G M S D D Okay then Um I think the slides will be published. So if you have any ask questions or remarks later on, so if I made some hurtful mistakes

Speaker 1: and uh

Speaker 4: I think you have a question on chat.

Speaker 1: Oh sorry, yes. Um could you read it because I'm technically overwhelmed and didn't see the chat.

Speaker 4: Yeah. Uh it's on this chat here. There's the button down there to work.

Speaker 1: No, no, I see it. Okay, yeah. My my fault, excuse that. So about a bit safety of scene and you're seeing migration signals, etc. Yeah, we went slowly into that. So in terms of SQL injections, if you use the um way to pass in parameters into the raw SQL statement Django has you covered. If you pass in your own hand-built strings, then it will well you're vulnerable. So there applies some protection like x equally as when if if you put some statements into the ORM, but it is quoted. In terms of signal migrations, if you continue to use models, so if you define your

Speaker 1: well database models in Django models, Then you can keep migrations. Of course, as we talked about in the previous questions, you may have to change queries. So there is no auto-migration for the SQL that's generated that you write. And in terms of signals , to my understanding, no. I would also shouldn't, I haven't tried it because I'm afraid what will happen. You could in theory use an arbitrary SQL statement to return model instances and then try to save them. So you could query one table, get a model instance with an ID and save them back, and then it would save back to the original database. If you continue to use the Django ORM for the parts where it is better, then you could also get signal.

Speaker 1: signals. However, that could mean that you use two queries. So you would use one query to get the data on the objects that you want. And then you change them in Um Django objects. So basically you use SQL for reading and Django objects for writing. And then of course you get the signals. Okay then. We have four minutes left, so anybody wants to do anything again? Otherwise, like I said, there is my email address on the slides. So if there are any further questions, corrections, especially interesting corrections, please let me know.

Speaker 1: And otherwise enjoy the rest of the conference. I think we have some very good speakers this year.

Speaker 4: I I have uh an example of a of a scenario where um raw queries can be useful, which is like you were saying earlier, when using some specific database function. So as an example I'm using um a foreign table on Postgres. Basically it's uh another server, a table on another server exactly. And sometimes you do need the raw Queries that there might be a way to use the RM to improve this, but most people will firstly get this to work with the the raw uh queries. To turn this into a question, you have any other typical examples like this where some tools that are database specific that you're using and you you find that you need this this

Speaker 1: I think it mostly covers it. So if you know how to do things in the database and well, maybe um Postgres um JSON B tables to mind we have a quite particular syntax and I think I support it now mostly but if you already know how to do it in the database And it's mostly easier to just adapt the SQL to a raw SQL. Same as you said with function. So I think it's possible. You can have some wrapper objects that you can use in the ORM. But it might just be easier to use a raw SQL query there. So thank you for the example. Okay, then guess it's more or less closes it.

Speaker 1: So everybody have a good nice conference and as I said, any further questions, please get in touch Thank you.

Speaker 3: Thank you, Bonday.