#353: SQLModel: The New ORM for FastAPI and Beyond Transcript

00:00 Two frameworks that have taken the Python world by storm lately are FastAPI and Pydantic.

00:05 Once you already have your data exchange model in Pydantic, you might want to use that code for

00:10 storing or talking to your database. And if you have database models, you might want to somehow

00:15 use those models to power and document the APIs you're already building with FastAPI.

00:21 But the popular ORMs, such as SQLAlchemy and others, far predate Pydantic. But could those

00:27 two things be put together? Sebastian Ramirez is here to tell us the answer is yes. We're

00:33 covering his project SQL model, which is the marriage between Pydantic and SQLAlchemy.

00:37 This is Talk Python2Me, episode 353, recorded January 17th, 2022.

00:56 Welcome to Talk Python2Me, a weekly podcast on Python. This is your host, Michael Kennedy.

01:01 Follow me on Twitter where I'm @mkennedy and keep up with the show and listen to past episodes

01:06 at talkpython.fm. And follow the show on Twitter via at Talk Python. We've started streaming most of

01:12 our episodes live on YouTube. Subscribe to our YouTube channel over at talkpython.fm/youtube

01:18 to get notified about upcoming shows and be part of that episode. This episode is brought to you by

01:23 datadog and tonic.ai. Please check out what they're both offering during their segments.

01:29 It really helps support the show. Transcripts for this episode are brought to you by Assembly AI.

01:33 Their speech to text APIs automatically transcribe and understand audio and video in just a few lines

01:39 of Python code. Check them out at talkpython.fm/assemblyai.

01:45 Sebastian, welcome back to Talk Python2Me.

01:47 Thank you very much. Thank you for inviting me. It's a pleasure to be here again.

01:50 It's great to have you back. When you were on the show, we were talking about FastAPI,

01:54 and it seemed like so much had happened. You've done so much, and now there's like all these other

01:59 frameworks that you've built and all sorts of exciting things, right?

02:02 Yeah. Very, very exciting stuff. It's like very exciting because Python is getting so excited.

02:08 Well, it's very exciting. It has always been exciting, but there's so many new things that

02:13 it's great to build things on top of.

02:15 It's interesting. I feel like your frameworks, more than many, take advantage of and almost depend upon the latest aspects of Python.

02:22 Yeah, yeah, absolutely. Like, someone made a meme at some point on Twitter about, like,

02:29 this guy that was like, pen, pineapple, apple pen. And it was like, type annotations and another

02:35 library and just put it together. And that's what I'm building. And yeah, it's pretty accurate.

02:39 Yeah, absolutely. You know, there's, as a community, we sort of muddled our way through the Python 2 to 3

02:46 transition, and it took a lot longer than even Guido and everyone, many other people expected it to

02:52 take. But now that we're on the other side of it, stuff like what you're creating and other people are

02:57 creating, that's what would have been possible had we gone sooner, right? But now it's like,

03:03 no, everyone's putting their effort into this, these new ideas and these new aspects that are now

03:07 possible.

03:08 Yeah, yeah.

03:09 I think it's great.

03:09 Yeah, absolutely. And I feel like the way Python is growing and improving is amazing. Like, you know,

03:17 there are some growing pains, like as with any project or with anything, but like, so like,

03:22 it's been able to grow in the directions that are needed and to support all the things that's

03:27 what users are needing. And like, we can do very cool stuff that will not even be possible

03:32 in other languages. And it's, I don't know, for me, it's pretty exciting.

03:35 Yeah. It's the same as for me as well. It just gets more exciting. You know, you could see it,

03:41 you just keep working the same thing. And it just keeps, ah, well, I've been doing that for a long

03:44 time. I need to change, but I don't feel that way at all. I feel like every day there's something new

03:48 and amazing and still the possibility for more incredible things to come is certainly out there.

03:54 I don't feel like we've hit the limit of what it's possible for framework authors like you to build

03:59 or for the core devs to make Python do, you know, there's the whole performance resurgence thing

04:05 that Guido Van Rossum and Mark Shannon are doing, that Sam Gill did, that Anthony Shaw is doing,

04:11 and others. Yeah. I think there's, it's good, right?

04:14 Yeah. It's amazing. And I feel like the, how the, the energy between the community and the

04:20 core developers and like editors and all the tooling, like all growing together and all supporting each

04:27 other, like helps each other part to grow more and better. And it's so exciting, you know,

04:32 to get like, for example, like support for very recent things and have been able to use them

04:38 right away in editors. It's like, oh, so cool. It is absolutely cool. And yeah,

04:42 the editors are definitely coming along as well. Now, before we get into your latest project,

04:47 SQL model, which is very exciting, let's just get a quick update on you. You know,

04:53 you told us the story, how you got into programming Python before, so we're not going to ask you that

04:57 again, but what have you been up to since you were on the show last? So when I was on the show last,

05:01 we were talking about FastAPI, right? And the cyber already existed if I'm wrong, right?

05:06 Yeah. I think that those were the two things you had built and FastAPI was...

05:10 Okay. Okay. Yeah.

05:11 Had FastAPI made it to the top three web frameworks yet? I'm not sure if it had, but it was,

05:16 it was right around that time. Yeah. That's incredible.

05:18 I think it was very close to that point. That was mind blowing that people were being able to use it so

05:25 much and adopted so much. And like when it came out, out in the service like that, it was super cool.

05:31 Yeah. It was, yeah, super exciting. And yeah, like, I don't know, like I have been, you know,

05:35 like I have been trying to focus, like I have always been trying to focus on whatever is the next

05:40 thing that I can work on that will have the biggest deep pipe that can the most. And I ended up just like

05:47 changing areas and like trying to improve different areas and different things. And recently that I was

05:52 working with the SQL databases, well, or recently, I don't know, some on-sable, that I was working

05:58 with SQL databases and I was working some of the existing libraries and I wanted to have like all

06:02 the benefits of the new features of Python, but I wasn't able to have like as much things as I could

06:09 because most of these libraries were built before we had all these, all these new features.

06:14 So I wanted to be able to get that. And I figured that the best way to build it was applying the ideas

06:20 that I had and the learnings that I had from the other tools and from the other things that just like

06:24 put the thing together because there were some libraries that were trying to do similar things, but like

06:30 I felt like there was still a bit more that could be done. So yeah, I was just like trying to get that.

06:35 And that's how we ended up starting.

06:37 Yeah. Fantastic. Now I feel like SQL model, I don't know for sure I'm asking you, but it seems to me

06:43 looking in from the outside that SQL model was something you're like, I need a good ORM

06:50 or FastAPI. And it, the things out there didn't click for you in the way that you wanted it. So

06:55 you're like, I'm going to build something that fits with this. Right.

06:58 The good thing with FastAPI is that it doesn't have any need for tightly coupling it with any ORM,

07:04 with any database. So it can be used with anything, but still there are some, some things that might not

07:11 be as convenient in the ORM itself. Like if you use it alone. And for example, with FastAPI, then you use it to

07:17 declare all the data models, all the shapes of the data that you want to receive and you want to send

07:22 back and to do like all the data validation, documentation, serialization. Then you declare

07:26 a bunch of those data models with Pydantic. But at the same time, you will end up declaring,

07:31 duplicating a lot of that information in a separate ORM just to connect to the database and to handle the

07:37 database and stuff with Python objects. But then you have to duplicate the information in two different ways.

07:43 And that's what was like, it was not the best developer experience, I guess. And I was trying to

07:48 make it a bit more user friendly, a bit more developer friendly, I guess, to work with databases and data

07:56 models and avoid all that duplication information. And at the same time, make it as easy as possible

08:02 to write a code, just using the same standard type annotations and just using the same, the same

08:07 intuitive things that we can already use. And that's the point that I was trying to hit.

08:12 For people to see how that clicks together, I know 12% of the community who builds web APIs and

08:18 frameworks is using FastAPI, but there's a decent percent out there who maybe haven't heard or

08:24 looked into FastAPI. Maybe they've heard about it, but they don't know the pieces. Maybe given that

08:30 that's some of the motivation, maybe give us a sense of how do you build data models that match your APIs

08:36 and how do you do things like generate the open, the swagger documentation and stuff like that, like

08:42 set the stage for why just straight SQLAlchemy or some other standard pony ORM or something like that

08:49 didn't just directly map over to how FastAPI works. Awesome. So like just a very quick intro to FastAPI,

08:57 it's a web framework that is focused a lot on building web APIs. The main idea is that using

09:02 the standard type annotations or type hints, so the way that you in a function you declare what is the

09:07 type of some particular variable, using that same information, that same information by default will

09:13 give you some certainty that the code is correct and will give you the completion and the inline errors

09:18 in the edit. FastAPI uses that same information to do data validation of the data that you receive in

09:24 the web API and to do the serialization of the data that you are returning back and to do automatic

09:30 documentation. This is all based on a bunch of standards, open API, JSON schema and a bunch of

09:35 other things. And because it's based on these open standards, then it can generate and it can also

09:40 provide Swagger UI, as you were saying, which is this web user interface that shows all the information of

09:47 the API. All the endpoints, where are the data, the shapes that you can send, and you can actually interact

09:53 with the API directly from the routes without having to go to some documentation site and then update and

10:01 the wiki gets updated and things like that. Yeah, absolutely. I built a weather service API for one of

10:08 my courses. This has limited data. People don't try to use this for actual weather service. But it's a

10:13 FastAPI API. And in addition to all the other cool things it does, like quickly generate the stuff it needs

10:19 to, you can just go to slash docs and it'll give you the schemas. It'll give you API endpoints, the values

10:27 that go in, the return value, all of this. I guess the most important aspect of this is probably Pydantic,

10:34 correct? Yes, absolutely. Like the most important part. You define your stuff in Pydantic models and

10:38 then that drives so many of these things. Yeah. So FastAPI is built on top of two tools. Pydantic does

10:44 all the data stuff, data validation, serialization, documentation, and Starlette does all the web stuff.

10:49 FastAPI just puts them together in a way that they work together. And that's some extra things on top.

10:54 But Pydantic is the thing that powers all this data validation and all this automatic documentation.

10:58 Right. Pydantic is so based on the same type annotations, the standard Python type annotations. So you

11:03 can just like use the same intuition that you will have for a standard Python and they get all these

11:08 data processing. Yeah. And FastAPI does several things with the Pydantic models. It does model

11:17 binding, I guess I'll call it. That term's not super common in the Python world, but you can just say

11:22 my API function or web function takes this model and then FastAPI will like create the Pydantic model and

11:31 set the values and do the validation. And then also the return value, you can say it will drive this

11:36 documentation and so on. So the reason I wanted to set the stage so much around Pydantic is that's one of the core

11:42 elements of SQL model, right? And not just using that library, but so that it can be used as the models in

11:50 FastAPI, right? Yes, exactly. So SQL model is a library, what they usually call an ORM. And if you don't know

11:57 what an ORM is, it's just a library to connect SQL database with Python objects and classes. I don't

12:04 know why we use the term ORM. I feel it's a bit abstract, but it's just a library to connect SQL

12:09 databases with Python objects and classes. And the thing with SQL model is that it does a lot of work

12:17 inside so that each model that you create is already a Pydantic model. It's not that it internally uses a

12:24 Pydantic model or internally creates some additional Pydantic model is that each model is itself a

12:30 Pydantic model. And at the same time, SQL models is built on top of Pydantic for data processing,

12:37 validation, all this stuff. And another library that does all the work to communicate with SQL databases,

12:42 which is called SQLAlchemy. And each one of these models is both Pydantic and SQLAlchemy.

12:47 Yeah. It's an interesting marriage between Pydantic and SQLAlchemy. Much of the way that you work

12:55 with it would be very familiar to people who do SQLAlchemy today, right?

12:59 Yes. That's the idea that it will be very familiar for people that is already working with Pydantic,

13:04 probably because they are using Plastic BI. But at the same time, it will be very familiar for people

13:08 working with SQL model because it's just the same look and feel. And it's indeed a strange marriage

13:14 because these two libraries are so different that getting them to connect and work together in the

13:20 in the very different ways they are built. It was very, very strange. But they actually ended up like

13:25 working quite the way. Yeah. I imagine that it was pretty tricky. You know, anytime that you get in

13:30 the middle of an ORM and its model, I've tried to do that with other frameworks and said, oh, it would

13:37 be great if I could say use inheritance in this way on my model so that there's not duplication. Like,

13:43 oh, no, no, no, you can't do that because the thing really depends upon the exact class that

13:50 derives from its sort of like ORM base class. That's what it uses for its determining what columns are

13:57 there and so on. Right? Yeah. I mean, it was so crazy. I spent so much time in the

14:03 the book that we were trying to figure out what was happening underneath and studying so much about

14:08 like the black magic in Python, the stuff that I always fear, like all the meta classes and stuff

14:14 and all that weird stuff. I studied so much of that to be able to mix these things together. But,

14:19 yeah, like because they do things in a very different way. At the same time, that's facilitated,

14:25 allowing one thing to do its job and the other thing to do its own job in their own particular ways. So,

14:33 yeah, it was fun. Yeah. Very cool. Whenever I think about an ORM, the thing that I first go to focus on is

14:40 the Python classes. Because for me, the whole point of the ORM is to let me talk to my database

14:49 through those classes and model my application through those classes. Right? So let's maybe get

14:54 started by talking about how do I create a class, a model, a SQL model model here that is both a

15:04 Pydantic model and a SQLAlchemy-like model. I'm going to talk us through what does it look like?

15:09 Cool. From SQL model, you will import this class, SQL model. And SQL model, you inherit from this class.

15:17 You can, for example, create a class hero. And then let's jump to the internal parts of that. Then you

15:23 will define some attributes for this class hero. For example, you could say that it has an ID,

15:28 that this ID will be an integer. The way you declare that is with standard Python type annotations.

15:34 You could say that it has a name and it's a string. If you are familiar with Pydantic, it basically

15:38 could exactly be a Pydantic model. Yeah.

15:41 The way you declare your ORM. Yeah.

15:42 In the simple case, right? Yeah. Yeah, exactly.

15:45 If it really is just an integer and it just has a number, you don't have to make it auto-increment or

15:50 any weird stuff like that, right? Exactly. In the simplest cases, it will be,

15:54 it will look just exactly like a Pydantic model. And in fact, it will be a Pydantic model. And then for

16:01 some particular cases where you need to add a little bit of extra information to tell SQL

16:06 model and SQLAlchemy underneath to tell it, "Hey, this does this thing with the database," then you can

16:11 pass additional parameters and additional configurations. So for example, when you create

16:15 the ID of this class, this will be the ID of the table and it has to be a primary key. So then you can

16:23 use the function field to say, "Hey, this still has a default value of none, but I need this particular

16:30 field or this particular attribute or this particular column, however you want to call it. I need this to

16:35 be the primary key." And then that information is passed through to SQLAlchemy underneath, which is the one that does all the work.

16:43 And there's something particularly interesting here is that you are saying, "Hey, this has a default value of none,

16:49 and that none default value will be used by Pydantic in the Pydantic side of things, but at the same time, it will be

16:58 used in the SQL side of things. So in the database, this will have also the particular default value.

17:04 In the case of the primary key, it's just because when you create a model, you still don't know what

17:09 the primary key is until you say." Most of the time, the database generates that. You could say

17:15 your primary key could be your email address, but it's common to have it just auto-generated by the

17:21 database. Yeah. A UUID or auto-increment integer or something like that. Exactly. Yeah.

17:26 For those cases, you want to have the type annotations very precise so that your code can tell you,

17:32 "Hey, this could be known at some point." That's just a particular detail. But the thing is,

17:37 the important thing is that you use standard type annotations to declare attributes, and then these

17:42 will be mapped to the data model in Pydantic, but at the same time will be mapped to the table in the SQL

17:48 data. Nice. So it kind of behaves in the two ways. And that means that what you put into your database is pretty much what your API model is as well, right? Exactly.

17:58 This portion of Talk Python to me is brought to you by Datadog. Are you having trouble visualizing latency

18:06 and CPU or memory bottlenecks in your app? Not sure where the issue is coming from or how to solve it?

18:11 Datadog seamlessly correlates logs and traces at the level of individual requests, allowing you to quickly

18:18 troubleshoot your Python application. Plus, their continuous profiler allows you to find the most

18:22 resource consuming parts of your production code all the time at any scale with minimal overhead.

18:28 Be the hero that got that app back on track at your company. Get started today with a free trial at

18:33 talkpython.fm/datadog or just click the link in your podcast player's show notes. Get the insight you've been missing with Datadog.

18:42 That's the idea. Like in the most basic situation, the cool thing is that with this approach and with this tool, you can then create additional models that don't map to one particular table in the database,

18:56 but are just for handling data with the API. For example, if you create, if you have an API that receives data to create a user, it will probably receive a password from the user. It will have the username and the password.

19:08 And you want to be able to have that information in the model that you want to receive in the API. But you don't want to save a password as plain text in the database.

19:18 You don't? Isn't that the easiest way? I get these warnings from these various sites like, oh, your password can't be more than eight characters long because no, yeah, please don't save it in the database. That's a really interesting scenario, right? You need to receive it on one end, but you must not put it into the database.

19:34 Exactly.

19:35 You must, it should not carry on.

19:37 And for example, and then in that same situation, you create the user and you want to return the information of the user back to whatever is the client. You don't want to return the plain text password. You want to say, hey, this is the username, but like, that's it.

19:51 Yeah. Yeah. Probably not. It's very unlikely that you want to return the hash as well. You just, you don't want it to return at all. Right?

19:56 Yeah. How do you handle that?

19:57 Yeah. For these particular cases is where SQL model will shine because you can create one base model that will have like all the base attribute scores.

20:05 I'm probably will have the name, the last name, the address, the email, blah, blah, blah.

20:10 And then you can inherit from that model and they have different models for the particular use cases. For example, for creating data. So you will have a plain text password and for returning data, you will have no password at all. But then one of these models will be the actual model that stays in the database. The one that reflects the information in the database. And this one is the one that will have the hash password. But you didn't have to duplicate all the information for the model because they only inherit from the same base model.

20:38 Is that the section that I got on the screen here that says multiple models with FastAPI?

20:42 Yes.

20:43 Like how you do that?

20:44 Yes, exactly.

20:45 Yeah. So the idea is obviously you have got some shared information about the user, like the email and their name and stuff. You want to share that probably their ID, but you don't want to, you don't want to share, say there, like you said, the password or whether or not they're an admin on the site or those kinds of things you probably don't want to exchange over the API. Right?

21:04 Exactly.

21:05 Exactly. And if you need to duplicate all the information for each one of these particular models, there's a high chance that at some point, whenever you refactor the code, some part will be out of sync. And then you will have a bunch of errors and a bunch of bugs that are very difficult to detect. When you have duplication of code and you have to synchronize it by hand, it makes, it creates a lot of potential bugs that are very difficult to detect.

21:31 Yeah. So in the way you do your models, this is pretty neat. One of the things that you do is you've got your model hierarchy, you've got SQL model, which is the base class of all the things that interact with, well, SQL model.

21:46 And those are typically the classes that you create that would be like SQLAlchemy or Django ORM models. But in your world, you can have inheritance. And then somewhere in that hierarchy, you set table equals true as you create the class.

22:00 So it's not necessarily that just, oh, you derive from this class. So that's a table. It gives you more flexibility and go, this part is a table. That part is a table. Like in the scenario we're talking about, you have a base user where there's a name and a password, a hash password and stuff.

22:15 No, sorry. You wouldn't want to put that. You would put like your shared stuff into the base class.

22:21 And then you, right.

22:22 Name, last name, address, email, I don't know.

22:24 And then the thing that derives from a user would derive from like user base, which would say like table equals true and it could have its secrets there.

22:31 Exactly. Exactly.

22:32 Okay.

22:33 So that makes a lot of sense. Inbound. What about outbound? So I've got a FastAPI endpoint, right? It could even be Flask or whatever, right? And I've done a query to the database and I get the table version that has the secrets.

22:46 I can easily go to FastAPI and say the response model is the base thing. So the documentation is right. But if I go to the object I got from the database and I say as dictionary or to dictionary, I forgot exactly what the right term is, but the thing that sends it back, it's going to include everything in it, isn't it?

23:03 So this is one of those particular details of FastAPI that I think people in many cases miss. And is that in FastAPI, you can say, hey, this is the response model. So this is the model that I want you to use for the data that I'm sending back.

23:19 So this is the most obvious result of that is that in the automatic documentation, you will get the schema of what is the response data. And that is like the most obvious and visible. But FastAPI will also use that same model to filter out the data.

23:34 So if you say, if you say the response model is user out, for example, and the class user out, which is a Pydantic class or something like that, this class user out doesn't include the hash password.

23:48 From the function, you can return an object that includes the hash password or a dictionary that includes the hash password. But FastAPI will omit that field. And FastAPI will only return the particular fields that were defined in the response model that you say that will be returned.

24:05 Okay. I did not know that that also affected the outbound data, not just the documentation. That's pretty interesting.

24:13 Yeah. And in fact, that's in many cases, people ask, why does FastAPI use this parameter response model instead of using the return type annotation?

24:23 Because in Python, when you create a function, you can define what are the types of the parameters that the function receives. And you can also define what is the return of that particular function.

24:32 If FastAPI use the return value, but then you can say like, hey, the return value is this user out, but then the object that you were returning from that particular function was different object.

24:43 Then the editor will complain, the tooling and the tools that detect those typing errors like mypy will complain and they will detect, hey, you're saying that you're returning something, but you're returning a completely different thing.

24:54 So that's the reason why their return type is not what is used to extract that information.

24:59 And instead it uses this particular configuration responsible because it's used for filtering data.

25:05 Right. Okay. Interesting.

25:06 So for people who don't know, haven't seen this in action, you put a decorator like an app dot or API dot get, for example, just like you would say in Flask or something.

25:15 And you say, here's the URL, but then you also may put response model equals some Pydantic type in FastAPI.

25:21 And that drives a Swagger documentation.

25:24 And I am learning now drives the filtering of the allowed return values as well, which is pretty excellent.

25:30 Yeah. In fact, it will also validate that.

25:33 So if you are saying, hey, this will return this data and then whatever you're returning doesn't include that.

25:39 That will actually be an error on the server because you are saying that the contract is that I will return this data, but then suddenly you are not returning it.

25:47 Then it will raise an error inside of the server and it will tell you, hey, the data that you're sending is incorrect.

25:52 So there's something going on here.

25:54 There's something wrong with your code because you're sending something invalid from what you say that you were going to send.

26:00 That's pretty fantastic.

26:01 Okay. I didn't realize it made such great use of that response model.

26:05 So that's just a whole nother level to bringing Pydantic into that world.

26:09 So there's a bunch of comments and thoughts here in the audience.

26:13 So I kind of want to bring some of them in because there's a bunch of great ones.

26:16 First of all, former guest Waylon says your mustache is fabulous, which is always required when you're on a video.

26:22 Thank you very much.

26:24 Sveta Link says big like on FastAPI.

26:27 I think FastAPI is absolutely a good thing.

26:30 Hey, thank you.

26:30 And Jacqueline also very much.

26:33 Poplin says, I have a question.

26:35 If the data schema is complex and has nested JSON structure, in what case would you validate?

26:41 Like it's pretty straightforward to just nest the Pydantic, but this brings us, you know,

26:47 if you're going to be in a world where you're nesting Pydantic things, you may want to save them to the database.

26:52 What's the story on relationships?

26:55 And this basically, I've received some data that is like nested related data.

27:00 What do I do in SQL model?

27:01 So if you need to receive some complex data structure and you need to extract the information,

27:07 you can declare, you can declare models with Pydantic or with SQLAlchemy, sorry, with SQL model,

27:13 say that, hey, this is just a data model.

27:15 And then you can manually extract the sub components and then just add them to the database independently or something like that.

27:21 There wouldn't be a straightforward way to say like, hey, I received this giant JSON and automatically generate a bunch of different models that don't exist yet or something like that.

27:31 Or to automatically infer where to put each information.

27:34 It will be as straightforward.

27:36 Like it will have a lot of different design possibilities.

27:40 So it will be easy to get it drawn.

27:42 So the way that you will do it is that you define the complex data shape that you want to receive.

27:48 And then once you take it, you just extract it, each part of the information and each particular object or each particular data point that you want to then say to the database.

27:59 Now, to return data to the user, with SQL model, you can have relationships and relationships between different tables,

28:08 have like automatic joins and all that stuff.

28:10 This is all thanks again to SQLAlchemy, which is the thing that works underneath.

28:14 It already models that, yeah.

28:16 Yeah, exactly.

28:16 But then you can use that information and you can just like declare the models.

28:20 And this again works well with this idea of having narratives to be able to declare, hey, I want to return this model and I want it to include this particular relationship model.

28:31 So it will include the information from other tables.

28:34 We will just extract that information and return it to the client.

28:37 That's really cool.

28:37 What about lazy loading?

28:40 I'll ask this in two aspects.

28:42 If I've got a relationship, I can do a join or a subquery load on it in SQLAlchemy so that if I know I'm going to be traversing that relationship,

28:52 I don't end up with the dreaded N plus one performance problem where I thought I was doing one query and I'm doing 51 queries.

28:58 If I got a, you know, 51 or 50 results back, something like that.

29:02 Is that support flow through SQL model as well?

29:05 The joins?

29:06 Yeah.

29:06 So the thing is that SQL load actually just like exposes the same interface as SQLAlchemy because it's actually just using SQLAlchemy underneath.

29:15 And SQLAlchemy supports everything, including like lazy loading.

29:19 SQLAlchemy actually supports things that are not supported by many other ORMs.

29:23 I forgot the name.

29:24 Having primary keys that are composed of different, of several columns.

29:28 Things like that.

29:29 Composite indexes and composite keys.

29:31 Composite keys.

29:32 Yeah.

29:32 There's a bunch of things that SQLAlchemy supports.

29:35 If SQLAlchemy supports them, then the SQL model automatically supports them because SQL model is just generated directly from SQLAlchemy.

29:43 Yeah, that's really cool.

29:44 Now, the question I was thinking about is if I have a result from the database that has relationships, a relationship, and I return it from a FastAPI endpoint, is that going to go and start iterating the relationship?

29:59 Do I need to be worried about N plus one problems by returning these models that then are getting serialized in eager ways?

30:08 Yeah.

30:09 You know, where it's like tracing through all the relationships so it can build the whole JSON to get it back out.

30:14 And then return the whole database in a single JSON.

30:16 Wow, that took a while.

30:18 That was slow.

30:18 That would be so painful.

30:19 Yeah.

30:20 No, by default, FastAPI and SQL model won't include relationships in models, won't include them in the data that is returned back.

30:30 But if you need to include some of those, you can declare, again, using inheritance, you can declare a different model that defines, hey, this relationship, this particular attribute, this should be included.

30:41 That way you can define that particular one in the specific endpoints that you want to include the information in the resulting value.

30:48 Right, okay.

30:49 That will work to force SQL model and to force, well, FastAPI and SQL model to do all the N plus one queries and to just extract information and send it back.

31:00 But if you are returning that data including the relationships, you will probably want to eagerly load that information, which is something that is supported by SQL model and by SQL.

31:12 So you will load the information that you need including the relationships and then you just return that object directly and you define the model.

31:19 Hey, I want this to include the relationships.

31:21 So it will just like include the information that is already there.

31:23 I can see that this N plus one issue is without the join or eagerly load is happening through a profiler.

31:31 If I was doing this in something like Django or Pyramid, I could look into the debug toolbar and it'll actually show me the SQLAlchemy statements that are running.

31:41 It'll be like, why are there 50 queries on this page?

31:44 It's harder, I suspect, in FastAPI, especially if it's operating in API mode where it doesn't have like debug toolbars and stuff like that.

31:52 Probably one way you could see it is to say echo equals true on the engine.

31:56 Yeah, exactly.

31:57 Exactly.

31:58 Because FastAPI is not integrated with any database and SQL just makes it super easy to work with FastAPI, but SQL could be used with any other framework.

32:09 That was the intention.

32:09 SQL model doesn't depend on FastAPI and FastAPI doesn't depend on SQL.

32:13 So you just integrate very well.

32:14 But then you could just enable the table thing with SQLAlchemy that will then show all the particular SQL statements and show you, hey, this is what is running.

32:24 This is what is happening.

32:24 Right.

32:25 Yeah.

32:25 So if you're connecting to the database, then this is a SQLAlchemy thing, but obviously it'll flow through.

32:32 Right.

32:32 You just say when you create the engine, you give it the connection string, you can say echo equals true.

32:37 And if you are doing queries that are doing a bunch of indirect behind the scene, lazy queries for you, your console window, your terminal, whatever is just going to blow up with a query.

32:48 You're like, why is so much SQL screaming by, right?

32:51 Yeah, exactly.

32:51 Exactly.

32:52 Exactly.

32:52 That's what it will work.

32:53 Let's talk about editor support really quick.

32:55 So one of the things that's really nice about Pydantic is it requires you to state the types, whether those are fundamental types, whether those are nullable types, like optional of int, or they're nested types, like a user contains an address Pydantic model.

33:13 All of those scenarios result in really good editor support, right?

33:17 Yeah.

33:18 Yeah, exactly.

33:19 And what's the story for editors in SQL model?

33:21 That's something you specifically call out about how it has good support there.

33:24 So if you check the source code for SQL model, it's actually super short, but it's just like a bunch of a lot of tricks together.

33:32 And many of those tricks are actually about type annotations because the thing that allows your editor to provide the auto-completion and the inline errors are the declarations of these types, the type annotations or type hits.

33:46 And SQL model does a lot of internal work so that whenever you use any part of SQL model, you will get that type information in your editor.

33:56 For example, if you query some model, query some table to get information, to get data from the database, the result that you get back, the object that you get back, will have internally all that type information so that the editor will be able to provide you with all the autocoption and all the inline errors and all those things.

34:16 SQL model, in fact, sacrifices some of the more advanced or obscure or sophisticated use cases that SQLAlchemy supports and sacrifices those to instead get very good autocompletion and inline errors everywhere in the code.

34:33 And this is another thing that the results and clear in SQL model is that it uses some draft standards that are not even implemented yet.

34:43 I don't know, I don't know, I'm not part of the standards, official standards yet, but they are already supported by some editors.

34:51 For example, Visual Studio Code already supports providing autocompletion when you are creating a new instance of a particular class or for the particular class of a model.

35:00 Having this autocompletion is not very easy to do with other tools because the editor doesn't have any information about what are the parameters that you can pass, what are the arguments that you can pass?

35:11 Right.

35:12 When you create a Pydantic model, it doesn't in anywhere indicate here is the constructor or initializer and here are the keyword arguments that happen to be all the static values.

35:24 It will just say like keyword arguments or like data, star, star, something like that.

35:29 Yeah.

35:30 But I always think thanks for nothing when I see that.

35:32 Yeah, exactly.

35:34 But actually Pydantic 1.9 includes this same trick.

35:38 So now you get autocompletion in Visual Studio Code.

35:41 In PyChart, you already have autocompletion with Pydantic because they have a plugin for Pydantic to provide autocompletion for those things.

35:49 But it requires this particular plugin.

35:51 Now with this extension, you can get also autocompletion in VS Code.

35:55 And with the same extension without needing any plugin, you get autocompletion for SQL model in Visual Studio Code.

36:01 I think the people from PyChart were also taking out to maybe support the same standard, which will allow PyChart to provide automatic autocompletion for SQL model and other libraries like Pydantic and Attrs and others.

36:16 Yeah, that's great.

36:17 Definitely the widespread use of Pydantic effectively is forcing everyone to go like, all right, how can we make this work better on the creation side?

36:27 So RJL out there has a comment, which then leads me to an interesting question.

36:33 Says, I'm old fashioned.

36:34 I use direct SQL statements.

36:36 No ORM.

36:37 I really need to take the time to go down this route.

36:38 Indeed, I do think so.

36:40 It's certainly worthwhile.

36:42 What are your thoughts on using straight SQL versus not?

36:45 Then I'll ask my question.

36:46 So I think, you know, like it just, it's a lot about taste and how people prefer to code.

36:52 There's a lot of people that are so comfortable with SQL and that can do so many things with SQL very easily that it's just more efficient to just use SQL directly.

37:02 For me, some of the advantages with ORMs is that I get inline errors that I get autocompletion for what is the name of the attribute.

37:10 If I forget that I say secret underscore name, the editor will autocomplete that for me.

37:15 But if I'm typing that inside of just a long string in Python using SQL, then I have to remember because no one will tell me that I have a syntax error in my SQL or that I'm using an attribute that doesn't exist.

37:27 One of the things that actually blew my mind is PyCharm.

37:30 If you set it up to, if you basically connect the database to your project, it'll give you autocomplete and error checking inside strings inside Python.

37:40 That's super cool.

37:41 For your schema, which is amazing.

37:44 That said, I never do that because to me, one of the things that is super valuable, one is this autocomplete.

37:49 The other is refactoring as well.

37:52 Like, oh, did we change the name of that?

37:54 Well, oh, there was that one query we didn't update and now it crashes in production, but only sometimes when it hits this case.

38:01 And, you know, it's just the way it sticks together and stays consistent to me seems a lot stronger if you're using models as well.

38:08 Yeah, absolutely.

38:08 And also the ability to swap backends, right?

38:10 The way you do parameterized queries is different across different database backends.

38:15 Yeah.

38:15 Also, if you write SQL by hand, then you have to be super careful and probably you have to be a SQL wizard and, like, know how to sanitize all the data that you're putting or, like, you know, otherwise you could end up in nasty situations.

38:29 But, like, I'm saying that there's a lot of people that prefer, really prefer writing SQL directly.

38:36 You know, like, the same author of Pydantic, which SQL model is based on, prefers to write SQL directly.

38:42 And, like, he's using FastAPI and everything, but he's just more comfortable to me.

38:46 And the author of PyPy, PsychoPG, the driver for PostgreSQL, he just uses SQL directly.

38:55 It's just, like, more comfortable for him.

38:57 He uses FastAPI a lot, but still, like, it's just more comfortable.

39:00 So I guess, like, it depends a lot.

39:02 For me, I depend a lot on the tooling and editor support and refactoring, as you were saying.

39:08 Like, if I change a name, I know that it's changed everywhere because I won't remember.

39:13 I won't remember.

39:14 Well, did I make this mistake?

39:15 Yeah.

39:16 Yeah, absolutely.

39:17 Martin in the audience asked an interesting question.

39:19 Down here is a better example.

39:21 One of the challenges of ORMs is to make set-based operations apply back to the database.

39:28 Like, I want to change this field.

39:31 Like, I want to set a is on sale flag to true for all products where the price is less than $10.

39:38 Right?

39:39 Where I'm not going to pull.

39:40 I don't want to go, let me query all products whose price are less than $10.

39:44 Change it on the object and then push those changes.

39:47 I want to push the...

39:48 I just want to say, update where this set that.

39:51 You know what I mean?

39:52 Yeah, yeah.

39:52 What's the story about that with SQL model?

39:55 Because that's one of the things that can really just hammer productivity or speed, I guess,

39:59 is if you've got to, like, pull back a whole bunch of stuff to just make sort of consistent

40:04 changes across them.

40:05 You know what I'm saying?

40:05 Yeah, yeah.

40:06 Like, this is one of the use cases where you will want to interact directly with SQLAlchemy.

40:11 And you can do that through SQL model, but you can't write, like, where it's as complex

40:16 as you want through SQL model, but, like, just using pure SQLAlchemy underneath.

40:21 And, like, you can use, like, very advanced things with SQLAlchemy.

40:25 SQL model focuses a lot on, like, the simplest and most common use cases, providing, like,

40:32 the best developer experience.

40:34 You know, like, certainty that the code is as ever free as possible because you have all

40:39 these type annotations and all these type checks.

40:41 But for any case that is a little bit more advanced, you can just, like, drop down directly

40:46 to SQLAlchemy.

40:47 And because SQL model is just pure SQLAlchemy, the models are themselves.

40:51 Just SQLAlchemy.

40:52 So you can just, like, use SQLAlchemy directly.

40:54 In fact, you could use one of these models with a SQLAlchemy engine directly, and it would

41:00 work.

41:00 Okay.

41:01 Sky points out in the audience that you were too humble to call out the PR that got that

41:06 autocomplete for VS Code was actually by Tandic.

41:09 It was by you.

41:10 So well done.

41:10 Thank you.

41:11 Keep moving it forward on both fronts.

41:13 So what about performance?

41:15 There's extra goodness in the validation and the type conversions and stuff like that of,

41:22 say, Pydantic.

41:22 But is there a large overhead for using this, say, over SQLAlchemy over, say, raw SQL?

41:29 Yeah, well, so when you use, when you declare a model and you say, hey, this is table model,

41:35 so this is, like, the equivalent of a SQLAlchemy model, then a lot of the validation and that

41:40 stuff with Pydantic is a very written.

41:42 So when you create a model, it will not be validated on creation for that particular table

41:47 because this will be handled directly by SQLAlchemy.

41:50 And for example, with SQLAlchemy, you can create an instance of a model without setting

41:56 all the attributes and then you can set the attributes manually afterwards.

41:59 If Pydantic was doing validation for that, like, that will explode and that will say, hey,

42:04 this is invalid.

42:04 Yeah.

42:04 So when you are working with SQLAlchemy alone, well, like, with the SQL parts alone through

42:09 SQL model, then it's just, like, using SQLAlchemy.

42:11 Okay, so it's not really any different in terms of, like, whatever SQLAlchemy does, this

42:17 does in terms of performance.

42:18 Exactly, exactly.

42:19 It just has, like, and actually, the code is so, so sleek, it's so little that, like, whatever

42:24 is the overhead will be, I will think it will be negligible.

42:27 At the same time, I'm not optimizing for, like, squeezing the maximum performance, but

42:33 for getting the maximum correctness in the code and the best developer experience.

42:38 Because I feel it helps a lot more to be a lot, as a developer, building the tool and making

42:45 sure that it's all correct, than having the code super fast, but very difficult to debug and

42:49 to understand and to write correctly.

42:51 Okay, so I guess you just got to decide, like, is an ORM the right fit at all?

42:56 Yeah.

42:56 And if it is, then this is a pretty good choice if you like this API.

42:59 Yeah, absolutely.

43:00 If you were needing, like, the maximum performance that you can get, you will probably end up

43:05 just, like, getting an async, an async drive it directly and just, like, writing SQL directly

43:10 for the particular endpoint that needs this extra boosting performance.

43:14 But for most of the other cases, this will probably help making sure that the code is

43:18 correct and making sure that you can write quickly and, like, be done with the feature

43:23 that you're implementing quickly.

43:26 This portion of Talk By The Nome is brought to you by Tonic.ai.

43:29 Creating quality test data for developers is a complex, never-ending chore that eats in the

43:35 valuable engineering resources.

43:36 Random data doesn't do it.

43:38 And production data is not safe or legal for developers to use.

43:42 What if you could mimic your entire production database to create a realistic data set with

43:47 zero sensitive data?

43:50 Tonic.ai does exactly that.

43:52 With Tonic, you can generate fake data that looks, acts, and behaves like production data

43:58 because it's made from production data.

44:00 Using their universal data connectors and a flexible API, Tonic integrates seamlessly into

44:06 your existing pipelines and allows you to shape and size your data to scale, realism, and degree

44:11 of privacy that you need.

44:13 Their platform offers advanced subsetting, secure de-identification, and ML-driven data synthesis

44:20 to create targeted test data for all your pre-production environments.

44:24 Your newly mimicked data sets are safe to share with developers, QA, data scientists, and, heck,

44:30 even distributed teams around the world.

44:32 Shorten development cycles, eliminate the need for cumbersome data pipeline work, and mathematically

44:38 guarantee the privacy of your data with Tonic.ai.

44:41 Check out their service right now at talkpython.fm/tonic, or just click the link in your

44:47 podcast player's show notes.

44:49 Be sure to use our link, talkpython.fm/tonic, so they know you heard about them from

44:54 us.

44:55 I've got one more thing I want to talk to you about.

45:02 One of the things, though, that you spoke about is the ability of having, and this is just

45:13 generally true for SQLAlchemy.

45:16 These models, they're tied back to what's called a session or a unit of work to do with the database,

45:22 and you can't do a query, get a record, and then go from a separate situation and try to jam it back.

45:29 It's got to be stuck to the session that it comes from, right?

45:33 So you don't share the models across sessions, but one of the things that would be nice is just to have a single one.

45:38 And so FastAPI has a dependency injection system that you talked about can be used for basically always providing one and only one session to an API endpoint or a web endpoint that then could be the database management.

45:56 Like creating a unit of work that is the lifetime of the request, basically.

45:59 Want to talk about that?

46:00 Yeah, exactly.

46:01 I think you described it perfectly.

46:03 I don't know what else can I yell at the bullets, right?

46:05 So this dependency injection system is just like you declare some function and FastAPI will make sure to run that function and provide the value to all the things that need that value for one particular request.

46:17 Right.

46:18 This has nothing to do with the database, by the way.

46:20 This could be anything.

46:20 It could be a login framework, whatever, right?

46:22 Exactly.

46:23 So this is very useful for doing logging, for doing authentication, for doing authorization with roles and whatnot, for doing logging or setting up things that load stuff to remote servers like Sentry or like Datalog or I don't know, for all those things that you need to do.

46:40 And that can be, and that is, there is some logic that needs to be shared and that could run before the request is handled and maybe after the request is done.

46:48 And then you can share this information.

46:50 So in many frameworks, there's a concept of a middleware, which is something that runs before the request and after the request.

46:56 But then you have to run this thing for every request.

46:59 With dependencies, with this dependency injection system, you can define exactly where you want it to run.

47:04 And you can define, I want this to be run with a group of endpoints or with a group of path operations, as I used to call them, for just one particular endpoint or for a bunch of endpoints.

47:14 And with this system, you can extract and you can generate whatever it is that you need to generate for the particular request.

47:21 And the good thing about the dependency injection system is that if you're extracting information from the request, for example, from a header, then this information will be also extracted and included with past API and with all the open API and all these standards.

47:34 So you will get that information in the automatically generated user interface to explore the API.

47:40 Yeah.

47:40 Very cool.

47:41 So what steps do I have to take to do dependency injection to get that session to show up?

47:47 I remember you had it in the documentation, but I don't remember where it is right now.

47:51 I think for the particular case of SQL model, even though FastAPI and SQL model are independent, are made to be very compatible with that independent.

48:01 I have a lot of documentation about writing applications with FastAPI and SQL model in the SQL model docs.

48:08 The way that you will handle a FastAPI dependency in general is that from FastAPI, you import this special function depends.

48:17 And then you create some function that will return sole value.

48:21 This function will have the same style as any other function that handles a particular request.

48:26 So it can have some parameters with some types and that information will be extracted from the request.

48:32 And it just returns something.

48:33 This is just plain old function.

48:35 And then you pass function is this is what will be the dependence.

48:39 Then you pass that function as a parameter to depends.

48:42 And then you put depends as the default value of sole parameter in your main function that is handling the request.

48:50 I see.

48:50 So you could say session equals like depends.

48:53 Here's some function that will get called to create the session.

48:56 Session equals depends and calling depends with the function that is named get session or something like that.

49:02 Do you have a way to see both sides of that?

49:05 With dependency injection.

49:06 So does it just return the value or can you like create a session and then yield the value and then keep processing or something along those lines?

49:13 Exactly like that.

49:14 Okay.

49:14 You can create a session that you can yield the value.

49:17 And then after the request is done, you can continue doing more stuff after yielding that particular session.

49:24 So you can create a session, give the session from the dependency, and then the main code that will handle the request will have that session.

49:31 And then the same dependency can take care of closing the session after the request is done.

49:36 Exactly.

49:36 Try, yield the session, finally close it.

49:40 Maybe like if there's no exception, commit it, something like that.

49:43 Exactly.

49:43 Okay.

49:43 That's pretty flexible.

49:44 Yeah.

49:45 And in the main code that you have, you don't have to take care about creating the session or closing it or making sure that there are no exceptions because you can do all that stuff in the dependency and share that logic throughout your code.

49:58 And the other thing is that dependencies can themselves depend on other dependencies.

50:03 So you can create a dependency that gets the session for the database, and then you can use that in another dependency that gets the current user and extracts from the header or from the authentication token or whatever, extract the user ID, then gets the user ID from the database and then returns the current user.

50:21 So that you have a whole dependency that just takes care of returning the current user and making sure that it's authenticated.

50:27 And then in all your, and you can reuse that code in all your endpoints or in all the main functions that handle the requests.

50:34 And all those functions will be able to just get the current user right away without having to have all the logic to extract the information, process the token, all the stuff.

50:43 Sure.

50:44 This is very neat.

50:45 I haven't used this enough during my work with FastAPI, so I got to check this out.

50:49 All right.

50:50 Now, let's do a bit of a lightning round here of the Twitter questions, because I've seen some of these questions come up in the live chat, but I also think I pulled out these ones that I thought people put on Twitter that were pretty good.

51:01 LeMatte Webster says, any plans to ramp or replace Alembic to make migrations more developer friendly?

51:08 So first of all, migrations, you've got to keep your database in sync with your models.

51:13 Otherwise, SQLAlchemy and hence SQL Model will freak out about that because it's going to be a problem.

51:19 But Alembic is, while it works, is a little bit hard to say, like, here's all the models you need to pay attention to.

51:25 And here's the scenario where you run.

51:26 It's a little bit clunky.

51:28 It works well, but it's not super smooth.

51:30 And I think that's what Matt's asking here.

51:32 Alembic is the official tool from SQLAlchemy to do the migrations.

51:36 And because SQL Model is itself also just SQLAlchemy underneath, Alembic works with it perfectly.

51:42 Alembic is a great tool.

51:44 It's super advanced and helps a lot.

51:47 It can even generate automatic migrations and things like that.

51:50 I think the main problem with Alembic is that in some cases it's not as intuitive.

51:55 So, yes, what I want to do at some point is to wrap a bit of Alembic.

51:59 I will replace it because it's already doing a magnificent job.

52:03 But, and it will be super difficult to write all that logic and all that work that Mike has been doing for a very long time with SQL Model and Alembic.

52:12 I will wrap it and I will try to add a bit more of documentation to explain how to handle the simplest cases, which is the same that I'm doing with SQL Model.

52:20 If you need something more confidence, that will probably just go Alembic directly or to SQLAlchemy directly.

52:25 Related to this, not Twitter question, Michael question, do you have any thoughts about testing code using these models and stuff like fake data or mocking out the database beyond just the standard stuff you would do to SQLAlchemy?

52:39 Like, is there anything special about SQL Model that makes testing it easier or different than SQLAlchemy?

52:44 No, the testing will be pretty similar to SQLAlchemy.

52:47 Pretty similar.

52:47 Yes, I just have like a bunch of documentation of how to do testing and even how to do testing with FastAPI applications using SQLAlchemy and how to, for example, use SQL Lite database or testing that will be run on memory instead of the production database that will be a PostgreSQL or MySQL or whatever.

53:05 Just change the connection string to the engine and let it go.

53:08 Yeah, okay.

53:09 And then make it run with a database in memory and then make it work correctly with threads and everything.

53:14 So something like a pytest fixture that initializes the database, but you just use colon memory colon for the connection string so that it just goes away.

53:23 Yes, exactly like that.

53:24 It's documented exactly like that.

53:26 All right, right on.

53:27 All right.

53:28 Ricky Lim says, would SQL Model be part of, should, could be part of the standard Python library?

53:34 I have some thoughts on this, but I want to hear your thoughts first.

53:36 I have some historical perspective on this, but I want to hear your thoughts on this.

53:39 Okay, so it will not be part of the standard library.

53:42 Ideally, it will not be part of the standard library because if it was part of the standard library, it will mean that it will be available in Python 3.13 or something.

53:53 And the users of Python 3.10 right now will not be able to use it on one side.

53:59 On the other side, having more stuff in the Python standard library adds more inconvenience and more burden to the core maintainers, to the core developers, Python, which makes it even more difficult for them to continue supporting Python and all the different versions.

54:14 And it will also complicate things for Red Cannon that is trying to figure out a way to slim down Python so that you can, for example, run it directly on the web browser.

54:26 Right.

54:26 Right.

54:27 How do we build it in WebAssembly?

54:28 This doesn't help.

54:28 Yes, WebAssembly.

54:29 And you know, Red Cannon and Kristen Hanks have been doing like a very cool job.

54:33 It's very exciting.

54:34 A lot of that will probably require actually is leaning down a bit.

54:38 Standard library.

54:39 As far as I understand, but I'm not no expert.

54:41 Yeah, I imagine a world where we have, I don't know what the right word for it is, but there's like a standard cross environment Python minimum set of language features of standard library where things like, you know, stuff that talks on the network or does UI things or whatever.

55:02 We're just, that is not part of this like minimum subset of Python that we're guaranteed to have so that we can put it on WebAssembly.

55:10 We can put it on mobile devices.

55:12 We can put it on servers.

55:13 And as long as you program to this minimum set, your place where your Python can exist is broader, you know, like MicroPython potentially.

55:20 I think that that's the trend and not the trend towards putting more stuff there.

55:24 Yeah, exactly.

55:25 Absolutely.

55:26 Absolutely.

55:27 And it's fun that it's already happening.

55:29 MicroPython is already that.

55:31 It's just that it cannot say it's a standard Python because it has to link that a bunch of things.

55:35 But being able to have a microcontroller and write code in Python that is running the microcontroller, that's amazing.

55:42 That's mind blowing.

55:43 Yeah, the most mind blowing thing for me is that you can hook a Lambda expression directly to a hardware interrupt.

55:51 That is like, what?

55:53 You can do what?

55:54 That's amazing.

55:55 The historical perspective that I want to bring up here is I believe the core developers actually considered this for requests.

56:01 And they decided that, no, they're not going to put requests in the standard library to replace URL lib because it would limit requests' ability to grow.

56:10 Like, changes could only come once a year.

56:12 It couldn't come three times a week if there was important changes, right?

56:16 Like, the speed of development would be hindered.

56:19 So they said, you know what?

56:20 No, we don't want to.

56:21 Yeah.

56:21 Plains to tell us.

56:21 Yep.

56:22 All right.

56:22 Next, Dimitri Figo says, are you considering working on generating TypeScript declaration files based on what's defined on the FastAPI backend?

56:33 That was that documentation I showed where it has the schema and all that and, like, the endpoints.

56:37 Yeah.

56:37 To make it, to explain it a little more, when you go to the automatic interactive documentation for the API, that is all based on this standard schema of the API called OpenABI.

56:49 This is just a huge JSON that defines all the data shapes that you're using, all the endpoints, everything.

56:54 But that same thing, because it's a standard, then you can use that same thing to generate code for clients that communicate with your backend.

57:02 And in fact, there's a bunch of client generators for many languages, including TypeScript.

57:06 And I have used them.

57:07 I have used some of them.

57:08 And they are actually very, very good.

57:10 Like, you can achieve things like defining in the backend where the data shapes that you're using.

57:16 Then you update something.

57:18 And then you regenerate the client in the frontend.

57:20 And now, after that, the frontend team will be able to have access to this new API endpoint with autocompletion in their editor and everything.

57:29 It works very well.

57:31 It's super exciting.

57:32 I just haven't had the time to document, like, the whole recipe to make it work.

57:37 But it's already there.

57:38 It's already working.

57:39 And it already does a great job.

57:41 Yeah.

57:41 Maybe somebody wanted to contribute a PR or do some help there.

57:45 They could, right?

57:46 Yeah.

57:46 You know, like, even a blog post will just, like, that will be a lot faster to get out.

57:52 And that will help a lot.

57:53 And a lot of people.

57:54 Indeed.

57:55 Zach Kud says, I'd love to hear how you approach figuring out the integration with SQLAlchemy.

58:00 I mean, we talked a bit about this.

58:02 But any other lessons you've learned from basically getting in the middle of SQLAlchemy and all of that it does?

58:08 Yeah.

58:09 It's very interesting that SQLAlchemy was created at a time where, like, it was, I don't know, Python 2.something.

58:17 There were no context managers.

58:19 So that thing that you do with a blog that you say with something, something, as blah, blah, blah.

58:25 And then inside of that report code, that was not available, but that didn't exist.

58:30 SQLAlchemy was made before that.

58:32 So SQLAlchemy had to do a lot of sophisticated tricks to make everything work.

58:37 And then getting down inside of it and trying to understand a lot of things like, why is this thing doing this?

58:44 I'm working like this.

58:46 And it's because of those things.

58:47 I think I ended up learning a lot about those little details and a lot about how classes work internally and how a class is an instance of what and all those things and how you can configure all that.

58:59 But the idea with SQL World is to make it super easy for you to use it without having to deal with all the internal complexity.

59:07 Because it's crazy.

59:08 You don't have to know.

59:09 Only you had to know and go through it.

59:11 So I think it's worth pointing out, I did have Mike Bayer on the show recently to talk about SQLAlchemy 2.0 and how they're moving to have basically the client side view of that be everything as a context manager and sort of change that up a bit.

59:26 So how close is this to the 2.0 model or is it the 1.0 model API?

59:31 Yeah.

59:31 So Mike Bayer did a lot of work to make the compatibility transition as easy as possible.

59:37 And SQLAlchemy, the latest available version, which is 1.4, is compatible with the previous style and with the new style.

59:45 So code that is written with the new style will be compatible with SQLAlchemy 2.0 whatever and above.

59:51 SQL model is based on this new style.

59:54 So if you, for example, if you have an old application with SQLAlchemy, the first thing that you will want to do is to migrate to SQLAlchemy 1.4 and make sure that it's compatible with the new style.

01:00:05 And make sure that you don't have any warnings.

01:00:07 That's the main thing that you will do to make sure that it's compatible.

01:00:09 And then after that, you can migrate to SQL more.

01:00:12 The migration is also super simple.

01:00:14 It's just like changing some classes for type annotations.

01:00:17 Yeah.

01:00:17 Absolutely.

01:00:18 There was a question.

01:00:21 Yeah.

01:00:22 So the question by Python at night was, what would the level of effort or benefit, if any, of converting SQLAlchemy models and schemas to SQL model?

01:00:31 Sounds like the effort is small and the benefit is all the features we spoke about, right?

01:00:36 Yeah.

01:00:36 Yeah.

01:00:37 Okay.

01:00:38 Would you recommend it?

01:00:40 I mean, people are using SQLAlchemy.

01:00:42 They're like, you know, I'd really would like to have some of that Pydantic magic.

01:00:45 When would you say, okay, the trouble of making a change is sufficient?

01:00:50 So if it was me, I would just use it right away.

01:00:53 Right now, it's in version 0.0 point something.

01:00:56 I will release 0.1 point 0 once I have 100% of test coverage.

01:01:01 Right now it's 97% just because I'm not freaked out of that.

01:01:05 But like most of it should already work.

01:01:07 It's actually very simple.

01:01:09 And if anything, the work that it does is actually very small because all their needs is all done by SQLAlchemy.

01:01:17 If anything went wrong, you could also just like switch back to SQLAlchemy directly.

01:01:22 It's just that you will lose the benefits.

01:01:24 Right.

01:01:24 Basically change your class back to driving from SQLAlchemy based and you're good to go.

01:01:29 Yeah.

01:01:29 Okay.

01:01:29 The benefit that you will get is auto-completion and inline errors everywhere where you are using these classes that you will normally not get.

01:01:37 And the integration with response model.

01:01:39 And the integration, yes, of course, integration with response model is that, yeah, actually that's a lot of code that you will save if you can share that with Bidantic and with SQL Mode.

01:01:47 Just make sure that you follow all the information about how to pin and how to upgrade versions because it's very detailed how you should go about that.

01:01:57 Because as things are still changing and as it still has like a little bit of extra testing to do, you should be careful about how to pin.

01:02:05 Just not install like whatever version comes.

01:02:08 Just make sure that you've been the right version.

01:02:10 You have tests and then when you upgrade, you make sure that the tests are pausing and then you can upgrade the version.

01:02:16 Yeah.

01:02:16 Good advice.

01:02:17 Already talked about that one.

01:02:19 So Lado asks, are you going to stretch modern Python conventions from the backend part, which you already did?

01:02:26 We talked about like using the types and stuff for model binding to Python challenging front end as well.

01:02:35 Should we expect something like Reactipy, like React, but in Python?

01:02:39 That will be it.

01:02:40 Do you care about the front end in the sense that you have any intention to build stuff for it?

01:02:45 Yes, I care a lot from then and I have actually, I think I started in that and I have worked with Angular, React, Vue.js and like all the stuff.

01:02:53 I think it will be amazing to be able to write Python in, to write Python for front end.

01:02:58 But if someone is going to make that happen at some point, they'll probably let Connor and Christine Haynes making WebOssently work for Python.

01:03:05 Yes.

01:03:05 Yeah.

01:03:06 You need the runtime there first and then it'll go much more easily.

01:03:10 I absolutely think that it is not quite negligence, but it's close that the browser makers don't package other runtimes that are WebAssembly compatible, right?

01:03:21 Like they should go to Ruby, they should go to Java, they should go to .NET and they should go to Python and say, are you willing to provide us a runtime that does X, Y, and Z that we can integrate in a generic way that we can include in our browser?

01:03:36 So you don't have to say, oh, well, you can't use these other advanced things because the WebAssembly download is 10 megs.

01:03:43 Well, if Firefox, Chrome, and Safari all shipped, you know, five of those, the five most common languages as binaries, like you would just have it.

01:03:53 And it would just be this WebAssembly.

01:03:54 Why does this not happen?

01:03:56 So, but that's the real problem to Reactipi is that that's not there, right?

01:04:01 Yeah.

01:04:01 All right.

01:04:02 Quasi says, what is he doing to address the bus factor?

01:04:05 That is, if you get hit by a bus and then related to that in the audience, Prashat Rana says, well, you include a moderator to the project so it can become a community driven project and there's less burden on you.

01:04:19 I think those are kind of similar questions from a different perspective.

01:04:22 Yeah.

01:04:23 And so the thing is for most of these projects, like most of the work can already be done a lot by the community.

01:04:28 It's not that the work is, cannot be done.

01:04:31 I just want to simply enable a bunch of permissions to a lot of people.

01:04:36 We just go and merge all requests that very quickly because I like to make sure that everything works.

01:04:42 For example, yesterday, for yesterday's FastAPI release, it had like four approvals, the pull request, but still it had a couple of bugs and a couple of things that needed to be solved.

01:04:54 And like, I need to make sure that the code quality is still kept and that everything is working correctly.

01:05:00 So for now, I'm still like making sure that I can give each one of the pull requests.

01:05:05 But if people went and checked those pull requests and reviewed the code and tested it and like made sure, hey, this is working and I'm using it and it's working in my application or things like that, that will, of course, help a lot.

01:05:17 Of course, that will help a lot.

01:05:19 That's great.

01:05:19 Obviously, it's open source.

01:05:21 People can fork it.

01:05:22 They can run with it.

01:05:23 Like if you actually got hit by a bus, I think FastAPI would keep going.

01:05:27 There'll just be a figuring out of like, all right, well, where's it going to send her back around before it settles down?

01:05:31 Yeah, exactly.

01:05:31 Not that anybody wants to.

01:05:33 I think these questions are a reflection of how significant the impact you're having on the community is, right?

01:05:38 Yeah.

01:05:39 And, you know, like it's, I find the bus factor fun.

01:05:42 I have been wanting to write a blog post about that for a while because I think the bus factor is something that works a lot for investors or for like founders that are not developers.

01:05:55 And they are like associating with someone that is the only one that knows the product, but they want to be the owners of half of it.

01:06:01 And if this person dies, they just lose all their investment.

01:06:03 But when you're working with one of the things.

01:06:05 They can keep it going.

01:06:06 Yeah.

01:06:06 Yeah.

01:06:06 Absolutely.

01:06:07 Like, for example, many of the projects from Nicole is mainly Tom Christie, which is one person.

01:06:11 The maintainer of Flask, which is huge.

01:06:15 It's mainly David Lord, and he's just like suffering through all of it and through all the abuse of developers and doing like a lot of the work.

01:06:23 Probably like, I don't know, see, he has like another contributor or something like that.

01:06:26 In the case of FastAPI, there's people like Marcelo that is helping a lot.

01:06:29 And even Samuel Copping is also helping.

01:06:31 That, you know, helps a lot with keeping the community, maintaining it, and like doing all the work that is needed to be done underneath.

01:06:39 That doesn't really affect how it's working, you know?

01:06:43 Like, the fact that the repository is not in another GitHub owner, in a GitHub organization or something like that.

01:06:51 It's just because it's easier to handle.

01:06:53 But at the same time, there's a lot of people that are already contributing.

01:06:56 And that's the work that actually makes maintaining it and sustaining it.

01:07:01 Exactly.

01:07:02 That's the stuff that matters.

01:07:03 Yeah.

01:07:03 Absolutely.

01:07:04 Alupia, just by the way, out there also says, I just want to say thanks to...

01:07:08 You, so, your work is so important and just great.

01:07:12 So, yeah, definitely a lot of people out there loving it.

01:07:14 Okay, let's keep going.

01:07:16 Back to SQL model.

01:07:18 Roadmap for the future.

01:07:19 What are the plans?

01:07:20 So, migrations.

01:07:21 I want to have a small wrapper to have a command line interface built on top of typer so that you can get into completion in the terminal as well.

01:07:29 To have migrations based on an embed.

01:07:32 To documentation for using ASIC with SQL model.

01:07:37 ASIC is already supported by SQLAlchemy.

01:07:39 Yeah.

01:07:39 And that's a new thing, right?

01:07:41 That is, you've got to create now an ASIC client or an ASIC session rather.

01:07:45 So, I think it is instead of a regular session in SQLAlchemy.

01:07:48 But that's one of the 2.0 big changes that Mike just pushed out.

01:07:52 So, that flows through?

01:07:54 Yeah.

01:07:54 Yeah.

01:07:55 Like, you can already use it.

01:07:56 In fact, there's people using it in applications, in production applications right now.

01:08:00 But it's just that I don't have it documented yet.

01:08:02 SQLAlchemy already supports both the normal, the blocking interface, the regular interface, and the ASIC interface.

01:08:10 And you can already use it with SQL models.

01:08:12 But I want to document all that.

01:08:14 Right.

01:08:15 So, what you should do is you should just change your dependency injection based on whether you have a DEF method or an ASIC DEF method in FastAPI.

01:08:22 And either create an ASIC client or ASIC session rather, or regular session, and then boom, off you go, right?

01:08:28 That is one of the things that I think is so smart about the design of SQLAlchemy, that SQL model in Arids, is that the thing that handles if it's ASIC or not is the engine, not the models themselves.

01:08:39 So, you can use the same models even if it's ASIC or not.

01:08:43 I agree.

01:08:44 Very nice.

01:08:44 Good question by Lars, but I'm going to keep going because we're short.

01:08:47 David Smith asked, are there plans to add ASIC?

01:08:49 Yes.

01:08:50 I think the question is, are there plans to document ASIC?

01:08:52 Yeah, exactly.

01:08:53 At this point, right?

01:08:54 We kind of touched on this one about whether you should use it for an existing, like, should you be migrating?

01:08:59 And we touched on that one.

01:09:00 Rainer, who I think I saw in the audience, yeah, earlier.

01:09:02 Hey, Brandon.

01:09:03 Asked, could you go ahead and make NoSQL model?

01:09:05 This is a chance for me to mention Beanie out there.

01:09:09 I wanted to kind of ask you if you'd had a chance to look at this.

01:09:12 So, Roman Wright also was out in the audience.

01:09:15 I saw them.

01:09:15 So, Beanie is an ODM, like for MongoDB, but also basically very similar based on Pydantic.

01:09:22 So, I thought it was a, this immediately came to mind when I thought about SQL model is like, well, here's the MongoDB version.

01:09:29 Yeah.

01:09:30 It also, you know, tries to do the same thing.

01:09:32 Have you thought about a NoSQL story?

01:09:34 Have you looked at Beanie?

01:09:35 What are your thoughts here?

01:09:36 Yeah.

01:09:36 And like, I really like both, that there are two alternatives for MongoDB with Bynantic.

01:09:42 One is Beanie.

01:09:43 The other one is Odmantic.

01:09:44 I think they are both doing a great job.

01:09:46 I like this particular thing about Odmantic is that it uses the same style of the interface of SQLAlchemy.

01:09:53 that the thing that decides if it's async or not is the engine and not the model, which means that it will probably be easier to implement.

01:10:02 Because both Beanie and Odmantic, both are async.

01:10:05 Yeah.

01:10:06 Both having the engine being the one that is async or not will allow implementing a regular or blocking version of it.

01:10:15 So that you could have MongoDB models that are shared and reused for async code and for blocking code in the same application.

01:10:23 So you could migrate more slowly and things like that.

01:10:26 But I think both are doing a great job and like have a very nice interface that is like very close to Python.

01:10:32 Yeah.

01:10:32 Just as a sidebar, I do wish it was easier in Python to convert an async call to a synchronous call, knowing that it would block.

01:10:41 Just go like, okay, here's async method.

01:10:44 If I could just go dot wait or dot result or something and just make it execute and basically stop the async running from there.

01:10:52 Then you could just do the query like dot.

01:10:54 Yeah.

01:10:54 Give me the answer, right?

01:10:55 Whereas it's a little more tricky.

01:10:57 You've got to get it like in the loop and run the loop to completion and stuff like that.

01:11:00 Yeah.

01:11:00 But you know, like that's why I just built a async, which is built on top of any IO.

01:11:05 And the idea is that you have this function asyncify and the function syncify just to do that.

01:11:12 So you can pass one function that is async and it will be run inside of the main event loop in async way.

01:11:20 Or you can say, hey, asyncify this thing.

01:11:23 And it will run the blocking function in a thread pool so that it's not blocking the main event loop, all the stuff.

01:11:30 But it's actually, the work is actually all done by any IO.

01:11:33 It's again, just doing the same thing of getting type annotations and other completion and all the stuff on top of the thing that is doing the work.

01:11:40 Yeah, I definitely came across this not long ago and it looks very exciting.

01:11:43 And I wanted to talk to you about it.

01:11:45 But as you can see, we're way over time already for just our main thing.

01:11:48 So let's go back to it.

01:11:49 But maybe next time you're on, we could just talk about async stuff all day.

01:11:52 Mike out there asks, what is the risk of using it in production?

01:11:56 The risk is the risk that you have for using any software in production.

01:12:01 Let me maybe rephrase it.

01:12:03 So what is the readiness for production, I guess, is probably what he was thinking.

01:12:08 Yeah.

01:12:08 So the thing is, most of the work is done by Pylandtica and SQLAlchemy.

01:12:12 And they have been used for years and they are doing an amazing job and they are already used by a lot of tools.

01:12:18 SQLModal only does like a little bit of extra stuff on top just so that you can need all the type annotations.

01:12:24 Most of the work is done by those.

01:12:26 The other thing is that the test coverage is at 97%.

01:12:31 So you have some certainty that it's working as intended, at least.

01:12:35 I want to have it at 100% and I want to have tests in continuous integration with several databases.

01:12:42 Because right now the tests are only run in SQLite.

01:12:44 But you know that all the SQL stuff is actually done by SQLAlchemy, which is already tested in all the databases.

01:12:51 So, you know, like there's a trade-off of trying this thing that still has a little bit of extra stuff to do.

01:12:57 Most of the extra things that I will do on top of SQLModal are actually documentation.

01:13:02 Not that much whole changes.

01:13:04 And the ability to flip from one to the other pretty quickly means if you had to say, oh, no, this is not working out.

01:13:09 We're switching back.

01:13:10 It's not like, oh, well, you're completely rewriting.

01:13:13 Yeah.

01:13:13 It's not that much work.

01:13:14 You just have to make sure that you're in the versions and you upgrade correctly.

01:13:17 But otherwise it should be.

01:13:19 And you will get more certainty about the code correctness because you have all the type.

01:13:23 So, yeah.

01:13:24 Yep.

01:13:24 All right.

01:13:25 We got one question left.

01:13:26 You have this ability to take what are often somewhat existing APIs and then improve them in ways that people really connect with.

01:13:36 Right.

01:13:36 Like FastAPI didn't start from open TCP socket and let's start from there.

01:13:41 It started on top of Starlet, right?

01:13:43 Like you already mentioned, Tom Christie.

01:13:45 This is on top of two very important libraries that they should go together but didn't.

01:13:50 So the question Pierre asks is, you know, how did you learn to sort of come up with APIs like you have?

01:13:56 Like what's your recipe for building these?

01:13:57 I think the thing is that I have been always trying to solve a problem and I have always trying to improve my developer experience.

01:14:06 and to improve the way that things work for me.

01:14:09 And like I have ended up just like trying to understand what is the best way to achieve those things.

01:14:14 And at some point I ended up learning a little bit about type annotations and I realized that, hey, this can be super powerful.

01:14:21 I can reuse it for different things.

01:14:22 And after looking and looking for different frameworks that did what I wanted, I ended up like saying, okay, I just have to build this because it doesn't exist yet.

01:14:30 But it was just like trying to achieve getting the thing that I want.

01:14:35 For example, I wouldn't go and build a NoSQL model or MongoDB because there's already Beanie and Nolpatic.

01:14:42 They are already solving the problem.

01:14:44 I try to avoid building new things.

01:14:46 But when there's the case that nothing is really something that I want to have, then I go and try to build it.

01:14:54 The other thing is I think one of the main features of all these tools is just a good documentation.

01:14:59 And I guess like the only thing about that is that I write it as I will have liked to learn those things when I was just starting and was struggling to understand what are all these things.

01:15:11 And I always have in mind like how will that newbie learn these things and understand it.

01:15:17 I guess that's probably the main thing that I'm trying to do.

01:15:20 To make it as easy to use as possible.

01:15:22 Yeah, I feel there's a strong blend of like, let's take the new things that are really useful that maybe not everyone's using and make them very accessible.

01:15:29 Make them very easy and default and so on.

01:15:31 Yeah.

01:15:31 Yeah, that's the learning spirit, I think.

01:15:33 Yeah, absolutely.

01:15:34 All right.

01:15:35 Well, that's all the questions.

01:15:37 We've been going a little bit long, but I really appreciate the time.

01:15:40 Let's just real quickly ask you the final two questions.

01:15:42 I'll let you get out of here.

01:15:43 All right.

01:15:44 So if you're going to write some Python code, work on SQL model or something else, what editor do you use these days?

01:15:50 I think the both main editors, Visual Studio Code and PyCharm are doing an amazing job at supporting all these tools.

01:15:57 Right now, my main one is Visual Studio Code, but yeah, I will just one of those.

01:16:02 Okay.

01:16:02 Very good.

01:16:03 Very good.

01:16:03 And then notable PyPI packages.

01:16:06 I feel like we touched on these a little bit.

01:16:07 We just mentioned them both, Manthic and Beanie for doing the same SQL model stuff, but for MongoDB.

01:16:14 Yeah, right on.

01:16:14 I agree.

01:16:15 Those are both great.

01:16:16 All right, Sebastian, it was great to have you back and congratulations on SQL model.

01:16:21 Maybe next time we'll talk async.

01:16:23 What do you think?

01:16:23 Awesome.

01:16:24 Sounds great.

01:16:24 Thank you very much, Michael, for inviting me.

01:16:26 Thank you everyone for staying this long.

01:16:28 Yeah, you bet.

01:16:29 See ya.

01:16:29 See ya.

01:16:30 Bye.

01:16:30 This has been another episode of Talk Python to Me.

01:16:33 Thank you to our sponsors.

01:16:35 Be sure to check out what they're offering.

01:16:37 It really helps support the show.

01:16:38 Datadog gives you visibility into the whole system running your code.

01:16:42 Visit talkpython.fm/datadog and see what you've been missing.

01:16:46 Go throw in a free t-shirt with your free trial.

01:16:48 Tonic.ai creates quality test data that does not contain personally identifiable information.

01:16:55 Your generated data sets are safe to share with developers, UA, and data scientists.

01:17:00 Most importantly, they behave like production because they're made from production data.

01:17:05 Check them out at talkpython.fm/tonic.

01:17:09 Want to level up your Python?

01:17:11 We have one of the largest catalogs of Python video courses over at Talk Python.

01:17:15 Our content ranges from true beginners to deeply advanced topics like memory and async.

01:17:20 And best of all, there's not a subscription in sight.

01:17:23 Check it out for yourself at training.talkpython.fm.

01:17:26 Be sure to subscribe to the show.

01:17:27 Open your favorite podcast app and search for Python.

01:17:30 We should be right at the top.

01:17:32 You can also find the iTunes feed at /itunes, the Google Play feed at /play, and the direct RSS feed at /rss on talkpython.fm.

01:17:42 We're live streaming most of our recordings these days.

01:17:44 If you want to be part of the show and have your comments featured on the air, be sure to subscribe to our YouTube channel at talkpython.fm/youtube.

01:17:52 This is your host, Michael Kennedy.

01:17:54 Thanks so much for listening.

01:17:55 I really appreciate it.

01:17:57 Now get out there and write some Python code.

01:17:58 I really appreciate it.

01:18:19 Thank you.