How (and why!) to build a Django based project with SQLAlchemy Core

Speaker 1: Hello, my name is Gleb. I'm a software developer from Ukraine, Kyiv, uh working with Python Django more than six years. Uh currently employed at Django Stars. And today uh I want uh to show to share with you actually a lot of information, so I have to be really fast to fit on the time. Um and uh I hope we will have some time for questions, but I don't promise it And actually I want to start with motivation. Why should we actually need to mix Django and TSK or Alchemy Core, which benefits we can get and in which cases it could be useful for us? So actually the main use case is uh data analysis application. Uh so your application uh works mostly with aggregations, so you don't work with uh like

Speaker 1: single lines of table you don't have REST API uh or you don't modify single rows of your data so you're only interested in aggregations and calculations So you likely will have a lot of data, so you have your queries to be really performant and precise. Um and maybe you could have some Uh if you have some pattern of the your pages you could build some uh wire which will generate such advanced queries for you. Uh so you maybe have such wire and it's really easy to build it with uh SQL Alchemy Um and maybe you also want to you design your queries in SQL in the console, you check performance, how it works, then you satisfy it and you want to move them to Python.

Speaker 1: It's really easy to do it with uh SQL Alchemy also. And uh maybe your data source which you work with doesn't natively supported uh by Django and maybe third-party applications uh which provide the support and integration, they are not uh fit your needs. So this um the main use case then you likely should uh s uh check this tool. So I really like Django. I really like Django RM, it's a fantastic tool. Um you even kind of forgot how to SQL. Uh but uh yeah it's uh greatly evolves. Uh it it it allows you to um uh focus on the implementation of business features instead of doing some low level stuff so you really can pro deliver your

Speaker 1: uh features much uh faster and more reliable way. And uh in last releases uh Django got uh a lot of cool features, uh so like subqueries, window functions, so uh you already heard it yesterday. um from Sigurd and uh so you have less reasons to switch to RAW SQL. Uh but if we build the data analysis application, uh Django RAM has uh its own specific so let's talk about this So for example if we want to we have properties and we have owners and we want to find uh for example five properties which in the city which start with the letter key and we want to uh get the username of the owner So we do in selector weighted which uh performs uh

Speaker 1: left outer join. Um we can also limit uh amount of fields which we want, so query is a bit simpler, but Yeah, in Python it's less readable. So I want you to focus on these two lines. So as you see, we have uh left outer join and we have limit. Um and if we See the explain uh how in which order this operation performed. Uh we see that uh first of all we find the properties which we are interested in. It's huge amount of properties. And only then we join to each of the row of the property, we join all nurses and only only after that we do a limit So this is not performant, this is not the way that we want to execute the query. Likely we want to have a query like this

Speaker 1: So um we have nested select, it's like correlated subquery, and we select only fields we're interested in, so we are doing limit there. And uh then we join uh users to these five rows. So it's very performant, it's good, believe me, you have not to read it. Um and uh with SQL Alchemy Core uh such query will look like this. And as you see on the first line, uh we simply build this nested select which uh uh doing a limit so this subquery and we simply insert it in other select uh and perform join. So it's really straightforward and you can really fast understand what's going on underneath Uh in Django RM this query will look like this. Uh that's why

Speaker 1: because uh all your query which you built it starts from model, model like declare for you uh the from clouse which will be used uh in the query. So Uh the subqueries that we have, they could be used uh in the select where having, but we can't put it into the from, so we can't make select from select. So we of course can build uh similar feature uh uh similar query. Uh we will can get uh uh a primary case of the properties which we're interested in and put it into subqueries. So it's also performant, so it's okay. Uh but this example showed us that actually Django RAM and SQL Alchemy Core, they are on different wires and they are designed to solve different needs.

Speaker 1: uh and different problems. So uh in Django Stack we have uh underlying non-public API which is a uh query set. query uh but documentation doesn't uh uh recommend to use it Um but uh in your SQL Alchemy Core um it's like on top of the SQL, so it's very close to SQL. And as you can see uh it's very readable, uh it's really easy to understand, and you can build your query easily. On the other hand, as you see uh jungle ram sample, the first filter uh it's would be actually the WER clouds. the values it would be a group by, uh annotate would be a list of fields, a list of aggregations which you want to perform on the query. And the last filter would be actually a heading clause which will apply on the top of your query

Speaker 1: So it's not string directly mapped to the SQL level. And when you building queries uh with jung uh with or on ORM, such queries you really feel that you don't have enough freedom. Uh and I want to show one more example. For example, we have two tables. Uh we have properties and owners, and in properties table we have apartments and we have buildings. So apartments uh place it in buildings and they have uh this building ID so it's self-relation to the table. So let's start building our fancy query with uh scale alchemy. Uh we Doing simple select from the properties table, we just select information which we want, like building ID, owner and sale price.

Speaker 1: And we want to get the properties which are for sale and which has sale price. So simple select. Then we can add more level uh where we're doing select from the previous select. So uh but here we want to replace uh the owner ID with user uh with username. So we have to join users and we simply replace it. Then we decide that we need to go deeper and we add one more level. So we built level three in which So we have building ID, so we can group buy our apartments and get the total price of the all apartments which are for sale in this building. So uh we do in group by, uh we apply uh the functions that we need uh and uh

Speaker 1: it's very readable. And on the last uh level we can we have only unique uh building ID so we can join building related information here, so we have only roles which we want to to join uh so we can add like total apartment uh amount uh to the to the building so uh Your query would be look like this so it's select from select from select from select and on each level you do in like join group by again join so This is the freedom I'm talking about. So uh SQL Alchemy allows you to build any type of such queries, which is really cool. Uh another example about um

Speaker 1: Django limitations and uh readability. Uh imagine the case we also work with properties. Uh so we want to find the first price uh which is bigger than one million and then we want to get all properties which has the same price which we just found. So the query we want to build uh is like this Uh so we uh put uh like where sale price equals to and aggregation over all the table by our condition. Let's try to do it in Django. Our first attempt would be to use aggregate, but aggregate uh queries I evaluated immediately so it will doesn't work for us because we have two queries uh to database to trips which is not performant. Uh so we want to use subqueries.

Speaker 1: Subqueries use with uh worked with uh query sets. Uh and uh s that's why we have to uh change our minimum aggregation minimal uh to the such trick like order by, ascending by price and get the first one. So it will get the minimal price. But as you see in our select we have uh also order by limit one and it's it's not readable and if you see order by or distinct account in your SQL query they are not performant. And this is not the way that you want to build queries if you work with big amounts of data. So we can leverage underlying on public API, which I mentioned. earlier. So as you see we have a query set

Speaker 1: dot query add annotation. So we manually add uh this annotation which we want. Its summary says that it should be applied over the old table And we actually get the query which we want. Uh but yeah, it's not recommended in the Django documentation, so it's up to up to you do you want to use it or not. And one more way, uh we can subclass subqueries and we can override template. Uh but uh yeah we also get uh the uh SQL which we want, but this solution is to RAW as for me. So on the other hand with the SQL alchemy this is pretty straightforward. As you see on the first line we build in also select and in the second line in the where we simply put this select into the condition and it will do all the magic

Speaker 1: and we will get the query which we want. Uh more limitations in Django RM, joins. You can't join tables which are not related. If they don't have relation, you totally don't have ability to do joins. Uh you can't do right outer join. Uh but yeah, it's very rare, but sometimes it happens, but I have to mention it. Uh also Django decides for you uh when to make inner or left outer join and you not uh you doesn't know uh what's happened underneath uh if you don't print query or you You can you you can't uh change it from left outer to inner if you can if you want to simply cut uh out uh some rows which has new values. Uh and

Speaker 1: uh Django also generates for you this uh join condition on condition, and you can barely customize it. Uh you can't get rid of it, you can't change this default. You can only use filter duration , which will add uh nth. And that's actually all. So you can't provide OR or some more explicit logic, some some more uh complex logic uh to do join. Recursive command table expressions are also not supported by Django yet. It's uh useful when you work with the roles which has uh like parent ID and there are uh it's uh point to another row which has parent ID, another row or another row and you want to

Speaker 1: uh take all of them and make some aggregation or calculation over them So uh currently you can do it with RAW SQL or SQL Alchemy. Uh also it there is a library like Django City Forest. uh but it has limitations and it implement implemented uh via extra uh but extra would be deprecated so it's not recommended to use so this library is not not usable currently One more example about uh how Django syntax is uh uh far far from SQL. So It's actually from Django documentation. I like it very much. So as you see we have book, we have authors, we can have store uh stores. And uh if we do in this annotation in one line, so of another um

Speaker 1: aggregation of multiple tables, so we get the wrong aggregation. So in fact uh instead of subqueries Uh it will be a joint. So you simply m uh multi multiply amount of rows and all your aggregation will produce wrong result. Uh count could be fixed with distinct true, so it will kick out all uh rows which uh uh redundant but uh actually other aggregations uh which uh will produce the result which you don't actually want to to get So this is if you not read documentation precise in us, you could fail in such issue. So to sum up, uh it's It's hard to read advanced queries in Django. It's hard to understand what's going on

Speaker 1: SQL level. And uh yeah, it takes time uh to convert uh query from SQL to Python if you design it somewhere and uh it's not so flexible so some parts you can't change or you have to uh change your original SQL query to make Django to be able to build it. Uh but this is not a problem in ninety-five percentage of the cases. Uh 95 is a number from my head So uh usually it's okay we have ability to switch to RAW SQL and if you have uh few such places it's not an issue and it doesn't hurt maintainability of our pro uh product Uh but if you build a data analysis application where you have only aggregations, it really could hurt.

Speaker 1: It's like the same slight as you saw. Uh so it's it's okay to look at such tools like SQL Alchemy uh for such type of project. So here is a brief tutorial for you how to start and uh what you have to expect uh with this integration. First of all you have to create engine uh it's a global variable which describe your connection. To database uh and uh SQL Alchemy comes by default with connection pooling functionality. So you get a Q pool Uh but if you want your connection to behave uh the same as a Django uh work uh like Django connection, you have to disable pooling with no pool. Um From architecture point of view, your application would look would look like this.

Speaker 1: So you have one instance, you have uh UGI, uh you have uh four workers which are processors, each of them configured to have eight threads. and each thread manage its own connection so you have 32 Django connections and 32 SQL Alchemy connections Uh so likely a waiter when you get the user so maybe when you instances have uh start scaling So you would like to add some connection pull-in wire like PG Bouncer, for example, if you use Postgres because for Postgres. It forks a process for each connection, so it's quite expensive to hold connections. It takes at least 10 megabytes of RAM. So if you have two instances, you just simply lost uh 1.

Speaker 1: 2 gigabytes of RAM simply to hold these connections. So that's why connection pooling wire uh would be recommended for you in if your application grow. Uh then you need to understand uh how you will work with the tables. Um so you have few options Uh there are two libraries uh which allows you to integrate a scale alchemy with Django. Uh first of them Alchemy it's uh has a mapping of the Django fields and it's built uh SPL alchemy representation of this field of the stables and attach it to the models. Another one, uh stable reflection, it's uh used Uh it's SQL Alchemy um technique which uh allows you wh like here you say okay I have this table I want to get

Speaker 1: uh Python representation of of this table. So it generates it for you, but it takes some time, so you have to cache it maybe on application launch. Uh also you can define your tables explicitly or define within line expressions. Uh if you define explicitly it's look like uh jungle models. Uh but you can keep it more simpler because we only read the data, so you can provide only names uh of the table of the columns. Uh but uh I also advise you to specify foreign keys because uh SQL Alchemy automatically pick up uh the this relation so you have not to specify on condition for joints so it would be a bit simpler in code. Um how

Speaker 1: you can use uh such tables So you build a query and you have C attribute which stores uh cons and you use it like a reference and uh uh result which you get uh each row of result is uh raw proxy which actually behaves like uh name it tuple or like tuple or like dictionary so it's very handy Uh and also it's possible to define your tables and columns uh with inline expressions. They are lightweight. Um So you simply put the names in the query or but if you have to uh keep the reference uh for the column uh to the table which is refers to, you can define it like this so uh the usage would be like on the previous slide.

Speaker 1: Um actually that's all what you need to know. Uh but did somebody say tests Uh death is not uh so straightforward. Uh it has some pitfalls and uh something which is worth to mention. Uh so first of all, if you remember we created in gen as a global variable, so once the interpreter will read this line, uh it will evaluate it. So we can't use uh overwrite settings, we can't use PyTest uh fixtures or hooks to change this setting because our line would be already evaluated. So likely you will have to introduce a function like this. So if it's test it adds add uh test prefix for you, uh otherwise you will lose your data.

Speaker 1: Um another interesting uh feature which I faced um it's how Py test work with uh cursors. So if you have um a result proxy it's uh actually a wrap around a DBRP cursor and you have ability to iterate over the roles so you don't wad all of them into memory you're just iterating across them So you have a function like process rows and maybe somewhere inside there is uh you write test and there is exception and it fails. So py test will mark this test as failed and it will hang on forever because uh it doesn't Allow Garbage Collector to close the connection. Usually it's does uh usually it's performed automatically

Speaker 1: So you have to do such tricks like uh, for example, write a decorator uh which applied to this function which iterate and produce exception, for example. uh in case of some error. Uh so you simply uh run across the all arguments uh and if it's uh result proxy if it's your cursor you close and erase exception. Uh but it yeah looks not so straightforward. And the most important thing which you have to keep in mind when you develop such type of application is that you have two connections and each of these connections have its own transaction isolation level So if you use test case and you populate the database with model mummy or uh factory boy, uh

Speaker 1: it will happen in uh Django connection. So for test case, in start of the each test Uh Django creates transaction for you, but it doesn't commit it in the end, it makes a rollback. So transaction is never committed and SQL community doesn't see the data which was produced in this test. So uh this some kind of issue uh but still you can use test case in your application uh but Just keep in mind that uh yeah it it uh when you write tests for the code which works with one connection and you populate uh the test uh database with the same connection. Uh but uh yeah if you populated the data with SQL Alchemy connection, you have to clean your tables by yourself between the tests, maybe with hooks or fixtures.

Speaker 1: And also it's theoretically it's possible to share the data which was created and not committed, so you need to change the turn uh transaction isolation level on the SQL alchemy. connection to read uncommitted but uh yeah it's not recommended but it's it's possible. And uh also you You can use transaction test case and there is no no issues with that. Uh so it's fine. But uh such tests work slower because there are uh in how this tr transaction test case works. They uh do in a post-migrate signal between each test so they truncate all the tables which have models. and then they launch post uh migrate signal which uh creates permissions and content types

Speaker 1: uh so it takes some time so such tests would be slower uh Yeah, and if you work with tables which doesn't have relation to Django models, you also have to clean up them between tests by yourself. So drawbacks. Uh it's a bit hard to start because it's not too much information about such integration and there are no such like tutorial like okay you have to do this. Um but documentation of a skill alchemy is quite good but sometimes it work uh some examples Uh but overall it's it's nice to work with. Um and uh yeah you can find that it's not uh very easy to work with uh uh uh if you want to debug your query which you made in python so you print it into the console

Speaker 1: and you s you will see that all parameters which you have maybe twenty or thirty of them if it's hard query uh They uh it would be a placeholders instead of values which you uh wanted to be there. So it's a bit annoying to insert them manually because uh this functionality is done by uh db uh which uh doing this uh this safe insert of the parameters into the query so a scale alchemy doesn't have such functionality of escaping your values uh to uh omit uh this uh in injection SQL injections. Uh and uh yeah it's there are some solutions uh but it's not so straightforward so it's a bit annoying. And uh you have uh

Speaker 1: slower tests Because likely you will use more transaction test cases which are slower, but it also depends. Um and yeah, you have more connections to database, but the most uh Important thing to keep in mind that you can't no longer use libraries like Django Futures or Pagination because they designed it to work with query sets and now you have only like dictionaries or tuples or lists so you have to find some uh query set independent uh libraries or come with your own solutions to cover this gap. But uh let's talk about benefits. So you actually get the full control over SQL level, which is really cool and it gives you freedom to build whatever you want.

Speaker 1: Uh yeah, it's faster to express a SQL in Python code. So if you have some data analysis guy which bring you tons of queries and say, okay, build these queries in Python, it's really easy to build them, modify Um yeah, and if you have some wire which uh creates such queries for you dynamically, uh you have uh in SQL Alchemy you have all obstructions over SQL, so you can use them like building blocks and you can build whatever you want. It's very handy to do this. And yeah, of course readability, maintainability, and and performance, because actually what you design it in SQL, you you generate it from Python so if you uh know SQL well in us so you will do uh correct queries and performance.

Speaker 1: Uh that's all. Uh I don't know do we have time for questions.

Speaker 2: We do have a few uh few minutes for questions. Uh so if anyone wants to come to the front um or if we have anyone online with Django uh DjangoCon QA hashtag we can answer a few questions.

Speaker 3: Uh hello, thanks for the talk. Um one question coming to my mind. You said you are having two database connections, which is obvious because you have Django and SQL Alchemy. Did you explore the possibility of writing a um, I think in SQL Alchemy it's a database dialect? Which would bridge over to the Django connection and reuse the underlying database API connection instead of having the two.

Speaker 1: Sorry, could you repeat?

Speaker 3: So um There would be the possibility to write a custom backend for SQL Alchemy, which would then reuse the connection from Django. Did you explore this?

Speaker 1: Actually there is uh a library. Currently it's under development, it's like uh Django SQL Alchemy and it tries to be inserted into the wire which uh generates these queries So there are some attempts to merge them together, but this is as I dev as far as I know they are currently under development and Likely it it would be not so easy and straightforward. So I I haven't tried this, but uh I'm not sure that this um goods good way to to move your application

Speaker 4: Okay, thanks for the um talk. It was interesting. Um I probably can guess uh the answer, but I wanted to know from you. Uh you haven't talked about uh the um Uh the admin stuff in Django. Would it be possible to use the SQLchemie with the admin Probably not.

Speaker 1: Uh no. Stack, but uh the solution which I'm currently talking about, it's mostly about yeah designing your queries which uh would be displayed for for the users maybe uh you can use uh it You if you uh start customizing a lot your Django admin uh maybe you can insert there, but uh it's n it will be not easily integrated and Django admin it's mostly It's ORM, so it works with single rows, but this is aggregation. So uh you can have built custom dashboard somewhere to show the numbers. uh overall numbers but if you want to work with single roles so you have to stick with uh jango

Speaker 1: rem.

Speaker 4: Okay thank you

Speaker 2: We have a little bit less than one minute so we can answer about one more question.

Speaker 5: Okay, I will try to be fast. Um so you mentioned in your talk that the you start usually with a big SQL query that you already have. So I was wondering if you explored to uh store that query as a stored procedure in the database and uh query and using query set to uh launch the stored procedures and what are the pros and cons compared to uh SQL ac in the project

Speaker 1: Um I think it's mostly about how how do you then um um manage uh and um maintain your storage procedures because if you have everything in Python code in Python project it's easily to manage to change and to influence in that. So I can say about uh pros and cons. It's From my point of view, it's mostly about how it will be maintainable in the long run. So uh my opinion it's better to have everything in in Python and not move this functionality to database because it it would be not so explicit and not transparent what will going on. So yeah.

Speaker 5: Thank you.