Piccolo: A fast, async ORM for Python (updated)

00:00 ORMs are one of the main tools to put first-class data access in the hands of non-SQL-loving

00:05 developers.

00:06 And even for those who do love SQL, making them way more productive.

00:10 When you hear about ORMs in Python, we often hear about either SQLAlchemy or Django ORM.

00:15 And we should, they're great.

00:17 But there are newer ORMs that take better advantage of modern Python.

00:20 On this episode, you'll meet Daniel Townsend.

00:23 He's the creator of Piccolo ORM.

00:25 A great ORM that is async first, but also has a synchronous API.

00:30 It has a super clean query syntax, and it's easy to learn.

00:33 This is Talk Python to Me, episode 328, recorded July 22nd, 2021.

00:39 Welcome to Talk Python to Me, a weekly podcast on Python, the language, the libraries, the

00:57 ecosystem, and the personalities.

00:59 This is your host, Michael Kennedy.

01:00 Follow me on Twitter, where I'm @mkennedy.

01:02 And keep up with the show and listen to past episodes at talkpython.fm.

01:06 And follow the show on Twitter via at Talk Python.

01:09 This episode is brought to you by Linode, us over at Talk Python Training, and the transcripts

01:15 are brought to you by Assembly AI.

01:16 Please check out what we're all offering during our segments.

01:19 It really helps support the show.

01:21 Do you want to learn Python, but you can't bear to subscribe to yet another service?

01:25 At Talk Python Training, we hate subscriptions too.

01:28 That's why our course bundle gives you full access to the entire library of courses for

01:33 one fair price.

01:34 That's right.

01:35 With the course bundle, you save 70% off the full price of our courses, and you own

01:40 them all forever.

01:41 That includes courses published at the time of the purchase, as well as courses released

01:46 within about a year of the bundle.

01:47 So stop subscribing and start learning at talkpython.fm/everything.

01:54 Dan, welcome to Talk Python to Me.

01:55 Yeah, thanks for having me.

01:56 I'm a big fan of the show, so it's kind of like a little dream to be on the show.

01:59 Oh, how nice.

02:00 That means a lot.

02:01 Thank you.

02:01 It's great to have you here, and you've built some really neat software that I'm looking forward

02:06 to diving into.

02:07 It's interesting because we're going to talk about your ORM, but it's also, there's so many

02:12 different areas at which now they're kind of past Python 2, and there's a lot of folks

02:16 who are saying, you know what, we can really put Python 2 behind us, and let's just build

02:20 for the future.

02:21 That just opens up so many doors, right?

02:23 Like, oh, well, type-ins, of course, type-ins.

02:25 Why not type-ins?

02:25 Well, Python 2 is why not type-ins, but not anymore, right?

02:29 And async and all these other things, and just, it's really great to have these frameworks

02:35 like yours coming along that just go, modern Python, what can we build now?

02:39 Yeah, I totally agree.

02:40 It felt like Python 3.6 kind of closed the door on the, should we still be using Python

02:44 2 conversation?

02:45 Because by that point, you had async, you had type annotations, data classes, I think

02:50 were Python 3.7, enums.

02:52 It just goes on and on.

02:53 Like, it just felt like the Python community knocked out of the park, Python 3.6 and on.

02:58 Yeah, I have a theory that is completely unsubstantiated, that actually a lot of the progress has to

03:05 do with f-strings.

03:06 So many people are like, ah, I just want to easily format this code.

03:09 What do I need?

03:10 I need Python 3.6.

03:11 Fine.

03:11 That's true, actually.

03:12 We're going to switch.

03:13 And it's such a minor feature.

03:15 Still minor.

03:16 The pain of going back would be horrible.

03:18 I couldn't imagine using Python 2 now, actually.

03:20 I know.

03:21 Yeah.

03:21 Same.

03:22 Quick welcome to some folks on Livestream.

03:24 Chris May.

03:24 Hey.

03:24 Happy to see you here.

03:25 Paul Everett.

03:26 So glad you can stop by.

03:27 And Teddy as well.

03:28 We'll get some questions in from you all, I'm sure.

03:30 Now, before we get into Piccolo and all the cool stuff that I was alluding to there, let's

03:36 just start with your story, Dan.

03:37 How do you get into programming in Python?

03:38 Yeah, so I've been programming for quite a long time now.

03:40 I might look quite youthful on this camera, but that's just because my webcam's got a filter

03:44 on it.

03:44 But I've actually been through.

03:46 The touch up my appearance is checked.

03:47 Yeah.

03:48 I've actually been.

03:48 I'm just kidding.

03:49 Yeah.

03:49 Programming with Python for about 14 years.

03:52 So Django was kind of one of my first frameworks and just really fell in love with it because

03:57 in university I learned C. And then it's like, oh, this world of Python exists and it's

04:01 a bit more easier than C. And so I've used it extensively since then.

04:05 I've mostly been working for startups and design agencies and then really fell down the

04:11 async rabbit hole over the last few years.

04:14 So I really enjoy working with WebSockets and building kind of interactive front ends and

04:20 also really kind of excited the way that Postgres continues to grow and have new features.

04:25 They're the things that really excite me in the Python world.

04:27 Yeah.

04:27 Those things are super exciting and the web frameworks are coming along to embrace them.

04:32 The language features, you know, async and await.

04:35 We had asyncio in Python 3.4, but it didn't really gain a ton of adoption because there

04:40 was, it sort of had this callback style of working, which is not the same as, you know,

04:45 await a thing and just keep rocking.

04:46 Totally agree.

04:47 Yeah.

04:47 So I think they pretty much took what Twisted had created and ported it over.

04:51 And like I say, it's all callbacks and it was still better than, it was still good

04:55 to have that option in the standard library.

04:57 But I totally agree.

04:57 Async await really kind of set it on fire because it was quite hard to approach asyncio at

05:02 first.

05:02 It was, you don't have to think about how you write code differently.

05:05 You just put the awaits in the spots and then, you know, everything kind of flows.

05:08 It's, I know it's not the same, but from how you think about structuring your code is real

05:13 similar.

05:14 Yeah.

05:14 And I think the maturity is there now with the libraries and the standard library and it

05:18 feels like you can just dive in and it feels a bit like a no brainer now to use async.

05:22 Yeah.

05:22 I think it is a no brainer for sure.

05:24 So it's interesting that you got into Django in the early days because Django is the only

05:31 major web framework that says, here's your ORM.

05:34 Here's how you use it.

05:35 As opposed to Flask that says, do what you want.

05:38 Or Pyramid that says, do what you want.

05:40 You know, you could use an ORM, but you don't have to.

05:42 But Django is, it's part of the culture and the zen of it.

05:45 Right?

05:46 Yeah, I'm a big fan of Django.

05:47 I think it's kind of a masterpiece because it stood the test of time so remarkably well

05:52 and how you could have written a app 12 years ago and then in an afternoon, you could have

05:58 upgraded it to the latest version.

05:59 So there's a lot to be said for Django and I really like the tight integration.

06:03 So people tend to prefer one or the other, the kind of the Flask route where you can make

06:08 all of your choices.

06:09 But I like really tightly integrated solutions where you come to a problem and then you pretty

06:16 much know what to pick.

06:17 Yeah.

06:18 So that's definitely inspired Piccolo to some extent.

06:20 Yeah.

06:21 I love the diversity of technology and libraries and ways of doing things in the Python space.

06:26 But when people come from other technologies, it's a challenge to go, well, here's 20 different

06:33 ORMs you could use and seven different web frameworks.

06:35 And they're like, I don't want seven.

06:36 I want to know what I should be doing.

06:38 What should I do?

06:39 Right.

06:39 And so it's a mixed bag.

06:41 I really like that.

06:42 But the thing is, if you come from the JavaScript world, then it's a relief because you've only

06:45 got 20, not 2000.

06:46 Oh my goodness.

06:48 This one's seven months old.

06:50 It's like ancient.

06:50 Can we still use it?

06:51 Yeah.

06:52 Yeah.

06:53 No, no.

06:53 That's a whole different discussion.

06:55 The JavaScript churn over there.

06:57 Yeah.

06:57 But yeah, let's focus in on what you built here.

07:00 So let's talk about Piccolo.

07:01 You just sell it.

07:02 You describe it as a fast async ORM for Python.

07:06 That's easy to learn.

07:07 And I think it seems like it really hits on all those points.

07:10 Tell us about it.

07:10 Yes.

07:11 So all those terms are like fairly aspirational.

07:13 So the fast is because it's built on asyncpg.

07:16 So this is a really fast Postgres adapter that the Magic Stack guys built.

07:20 Part of the reason it's so fast is it's written in Syphon.

07:23 So it's compiled.

07:24 And then there's also a few other features in Piccolo, which kind of help it speed wise, like

07:28 frozen queries where it doesn't have to generate the SQL each time.

07:32 It kind of caches some of it.

07:33 So that's kind of where the fast comes from.

07:35 Async.

07:36 Oh, nice.

07:37 Async.

07:37 Async.

07:38 Async.

07:38 Async.io.

07:38 That was really one of the core reasons for building it is because I was a Django channels

07:43 power user and I had to build a lot of chat applications in my day job.

07:47 So for me, async was kind of essential.

07:49 And then the easy to learn is called also aspirational.

07:52 I put like as much effort as I possibly can into the documentation and stuff like that.

07:56 But I'll let the readers be the judge of that.

07:59 Yeah.

07:59 That's in the eye of the beholder.

08:00 Right.

08:00 But at the same time, you have done a lot of things that I don't see others doing.

08:05 Like the documentation is good.

08:06 Obviously, that's pretty standard on a lot of the different frameworks, although they can

08:11 be super intense.

08:12 Right.

08:12 Like I love SQLAlchemy.

08:13 But when I go and read there, I'm like, OK, here's a 20 page doc.

08:16 I better pay attention and turn off my music and just like not miss something on the other

08:21 hand.

08:21 But what you are doing that's pretty unique is you've got like a playground.

08:24 You've got a playground for the admin backend.

08:26 Oh, by the way, there's an admin backend.

08:27 And you've got a playground for trying out queries and stuff like that.

08:32 Right.

08:32 That's pretty unique.

08:33 Yeah.

08:33 So I was really excited by the playground concept.

08:36 So it's something I borrowed from the Swift world.

08:38 And because Apple are trying to make Swift accessible to newcomers, they have this concept

08:43 of a playground, which is basically a pre-populated chunk of code.

08:47 And then you can just play around with it as kind of the name implies.

08:51 So I use IPython to achieve it.

08:53 So a lot of people know IPython, just running it on the command line like a terminal.

08:57 But you can actually embed it within your Python code.

09:00 So the way the playgrounds work is it creates a SQLite database.

09:04 It loads an example schema, populates it with data, and then basically launches the IPython

09:10 shell.

09:11 So you basically have all of that set up.

09:13 Because one of the problems with learning an ORM is the barrier is huge when you think

09:17 about it for a newcomer.

09:18 You have to set up a database.

09:21 You have to understand schemas and migrations and running them and populating data.

09:26 So I just wanted to say, look, I'm going to shortcut all of that.

09:28 You install Piccolo.

09:29 You do Piccolo Playground Run.

09:31 And then you've got an example schema.

09:33 And then you can actually follow along with the documentation.

09:35 So whenever there's an example query in the Piccolo docs, you can actually run it in the

09:40 playground.

09:40 And you should see the results.

09:42 So yeah, I was quite excited about that.

09:43 I like it a lot.

09:44 And you're right that it is challenging.

09:45 You know, as we build up experience and we get used to it, it's like, oh, yeah, you just

09:49 fire up the database server and then you connect to it.

09:52 Then you do this thing.

09:53 But how do you get the database server installed?

09:56 What if you have the wrong version?

09:57 What if it requires authentication?

09:58 What if there's a firewall?

10:00 Just like there's layers and layers of places where people get sheared off.

10:03 Like, ah, this doesn't work for me.

10:05 All right.

10:05 And so keeping that simple, it's great.

10:07 And I think I just want to give a shout out to SQLite.

10:10 Because what you just said is such a good example of you're probably going to run this

10:14 against Postgres or something like that.

10:16 Yeah.

10:16 But for the little example, you don't need that.

10:18 You can just use SQLite.

10:19 And it's so nice that that's a serverless in the sense that it's just embedded.

10:24 Doesn't require a separate process to run or connect to.

10:26 It's already comes with Python, generally speaking.

10:29 It's really nice.

10:30 Yeah, I totally agree.

10:31 And that's the reason for SQLite support within Piccolo.

10:33 Because as you said, no one's going to run it in production.

10:35 But then it's just that frictionless setup, which is so great.

10:39 Like, using SQLite asynchronously doesn't really make a lot of sense.

10:43 Because you don't really have any network lag talking to the database.

10:46 But it's great to have it in there for convenience.

10:48 Yeah, it is.

10:49 You know, I suspect there may be some people who run it in production in like an early, early

10:53 prototype stage.

10:54 Yeah.

10:55 They just want to get something up.

10:56 And here, let's just put it up on, you know, on some hosting place and just check it out

11:00 real quick.

11:00 And then we'll go from there.

11:01 Yeah.

11:01 One thing that's quite nice is I used to do this in the design agency.

11:05 We'd use SQLite for the prototype.

11:06 And then you could R-sync it down to your local machine.

11:09 So you didn't have to do a database dump and reload it.

11:12 You could literally just R-sync the file.

11:13 Yeah.

11:14 So there are a bunch of conveniences with SQLite for sure.

11:17 Yeah.

11:17 So it gets to be too much data.

11:18 Or really, you want sort of too much concurrency or multi-machines scale out and all that sort

11:24 of stuff.

11:24 So it's not the final destination.

11:25 But I do think it's a really interesting and important starting ground there.

11:28 Yeah, for sure.

11:29 Yeah.

11:30 So I gave a shout out to two ORMs already, the Django one and SQLAlchemy.

11:36 And I feel like those are probably the two big hitters of the Python space, although there

11:41 are many, many more.

11:42 So why not just use those?

11:44 Why go and create Piccolo?

11:46 Yeah.

11:46 So when I started it, there was no async options for ORMs at all.

11:50 It was all very new.

11:52 And my day job involved async programming all of the time.

11:56 Because of chat apps, online games, that kind of stuff.

11:58 So that was really why I was like, well, nothing really served my needs at the time.

12:02 Right.

12:02 Because until recently, SQLAlchemy had no async capabilities.

12:07 Yeah.

12:08 And Django ORM more or less still doesn't, right?

12:10 That's the final part that they're trying to move to async.

12:13 Yeah.

12:13 So the workaround is to run it in a thread, but it's just not as performant as running

12:17 with asyncio directly.

12:18 So there's kind of this vacuum for a time with async ORMs.

12:21 But then it was also about just starting through a blank sheet of paper and thinking, look,

12:26 like we've got all these great tools to work with now in Python 3.6 and onwards.

12:30 How can I absolutely push them to the limit?

12:33 So the syntax, it uses a lot of direct references to objects rather than strings.

12:39 So for example, in Django, you do my table to objects and then dot filter and you name equals

12:46 Dan.

12:47 The problem with that is you can quite easily make a mistake as the code changes.

12:52 So you might end up having a column which no longer exists or so strings can be a little

12:58 bit fragile.

12:59 So with Piccolo, it's just object references everywhere.

13:02 So rather than name, it's in this example here, it's band dot name where band is the

13:07 name of the table.

13:08 Right.

13:08 So it's like many of the ORMs, it's sort of class OOP based, right?

13:13 So you create a class that maps to a table and database, right?

13:16 Yeah.

13:17 So there's one distinction I'd like to make quickly as well.

13:19 So there's ORMs and then there's query builders.

13:21 And in Python, there's not really too much of a distinction, but in other languages like

13:25 JavaScript, query builders are really popular as like a separate idea to ORMs.

13:30 And query builders, they kind of, you create a SQL query, you execute it on the database,

13:35 and then you tend to work more with dictionaries and lists rather than objects.

13:39 So I'd say that 90% of Piccolo is a query builder, but people are used to ORMs and Python and it

13:46 can actually lead you to some quite poor patterns, ORMs, because what the first thing people learn

13:51 with Django is they'll go my table dot objects dot get.

13:55 So it returns an object and then they'll change the attributes of the object and then they'll

13:59 call save.

14:00 But then that's actually two database queries because you have to get it and then you have

14:03 to update it.

14:04 Well, and it's not just two database queries.

14:05 It can be a lot of serialization.

14:07 Yeah.

14:08 Like the slowest part of all of my data stack is if I were to try to pull back 10,000 records

14:15 at a time, it's the deserialization of those 10,000 things that is actually the slowest part

14:19 of the entire process.

14:21 And here, if what you're talking about, like you're talking about the band, if the band

14:25 has tons of information and you just want to change the name, right?

14:29 You're pulling all that data back, converting it or whatever, then changing the thing and

14:33 then pushing it back down.

14:34 Yeah.

14:34 It's a lot of work.

14:35 And a lot of the time what will happen is you'll serialize it into an object and then

14:38 you'll deserialize it out into JSON anyway.

14:41 And it's kind of pointless when you think about it.

14:44 And there's also the problem with objects getting stale.

14:46 So you might pull an object into memory, but then some other user might manipulate all those

14:51 objects.

14:51 But then when you save it, are you going to overwrite those fields that have been updated

14:54 in the database?

14:55 So it's problematic in a way.

14:57 So it is an ORM, but then I'd really encourage people to look at it as a query builder too,

15:02 because in my own apps, I use the select method a lot.

15:06 So rather than returning the objects, it returns dictionaries.

15:09 And then it has this option, an output method.

15:12 So you can just literally deserialize it straight into JSON.

15:15 So this is what makes it fast because the query is going through asyncpg, which is super

15:20 fast.

15:21 And then it's coming back as a dictionary straight from asyncpg.

15:24 And then it's using all JSON to stick it straight into a string.

15:28 So you're kind of missing, you're skipping all this deserialization nonsense.

15:32 Yeah, yeah.

15:32 This is super interesting.

15:34 Now that you pointed out, I'm not used to seeing projections in ORMs.

15:39 What I'm used to seeing is give me back the classes that, in this case, a band, right?

15:44 Do a query like where the popularity is greater than 100 or whatever, and give me a whole bunch

15:47 of bands, band objects back, and then I'm going to work with them.

15:51 But sometimes like the one I work with most commonly is Mongo Engine.

15:55 I know it's not an ORM, it's an ODM, but close enough.

15:58 Does the serialization bit.

15:59 And you can say, I don't really want the other parts.

16:02 Just give me the name, like the website or something.

16:04 And it won't ship or deserialize those things.

16:07 But you just end up with the same objects that just have none everywhere else, right?

16:11 Where in your ORM, you can actually say band.select, band.name, band.url, or something like that.

16:19 And that would return the dictionary with those two things, right?

16:21 A list of dictionaries, probably?

16:23 A list of dictionaries, yeah.

16:24 And then it also has a feature, like Django has this feature, which is invaluable.

16:28 It's called values list.

16:30 And so if you just want one value back, it'll then condense it down to just a list of values.

16:35 Right.

16:35 Like I want all the IDs of the bands that play pop music or something.

16:39 Yeah, and it's so much more efficient than doing like in Django, you know, band.orl,

16:44 objects.all, and then looping through to get the IDs.

16:47 It's just these little tricks you learn.

16:49 Like if you use Django for ages, you just learn these little tricks.

16:53 Right.

16:53 Another one that stood out to me is the ability to do set-based operations.

16:58 Because when I think of ORMs, I just, for everyone listening, I adore ORMs.

17:02 I think they're really empowering for people.

17:04 I think they take a lot of the modern tooling that we love, like refactoring, and allow you

17:10 to apply that like over to your query.

17:12 Because if you wanted to like change the casing of band.name, you could refactor, rename that,

17:17 and it would affect your queries because that's still Python code, right?

17:20 That said, there are places where people either abuse it or it's just inappropriate.

17:24 So the places where it gets abused a lot would be the N plus one problem, right?

17:29 Where you've got a lazy reference to something else.

17:32 And you don't know that that's going to be a separate query for every time you touch one

17:36 of those.

17:36 And you get a list of objects back and you loop over them and you access like, in your example,

17:40 you've got band.manager.

17:42 For B and band, band.manager, right?

17:44 That could be 101 queries for what should have been one, right?

17:48 Yeah, that's a really good point.

17:49 And even experienced developers get this wrong because they might use serializers, which are

17:53 calling properties under the hood on the table, which are triggering SQL queries.

17:56 So this is another design intent behind Piccolo is whenever a query is run, it's very explicit.

18:02 You are literally calling .run or .runsync.

18:05 So there's none of this magic.

18:07 You can't accidentally create an N plus one query.

18:09 You might accidentally end up with a coroutine or something.

18:11 Yeah.

18:12 So yeah, it's a really good point because I think N plus one is kind of like the scourge of

18:16 developers with performance.

18:17 And also coming back to your point about ORMs, as a backend developer, we can spend hours a day

18:22 using ORMs.

18:23 It's kind of like one of the main tools in our tool belt.

18:26 So it's kind of quite nice to start from a blank sheet of paper and think, how can I make

18:30 that experience maybe like slightly better if I can?

18:32 This portion of Talk Python to Me is sponsored by Linode.

18:37 Visit talkpython.fm/Linode to see why Linode has been voted the top infrastructure

18:42 as a service provider by both G2 and TrustRadius.

18:46 From their award-winning support, which is offered 24, 7, 365 to every level of user,

18:51 to the ease of use and setup, it's clear why developers have been trusting Linode for projects

18:56 both big and small since 2003.

18:58 Deploy your entire application stack with Linode's one-click app marketplace, or build it all from

19:04 scratch and manage everything yourself with supported centralized tools like Terraform.

19:08 Linode offers the best price-to-performance value for all compute instances, including GPUs,

19:14 as well as block storage, Kubernetes, and their upcoming bare-metal release.

19:19 Linode makes cloud computing fast, simple, and affordable, allowing you to focus on your

19:24 projects, not your infrastructure.

19:26 Visit talkpython.fm/Linode and sign up with your Google account, your GitHub account,

19:32 or your email address, and you'll get $100 in credit.

19:35 That's talkpython.fm/Linode, or just click the link in your podcast player's show notes.

19:40 And thank them for supporting Talk Python.

19:42 The N plus one problem, I believe, is either there's some tool doing something behind the

19:49 scenes you don't know, but often it's just a lack of understanding.

19:51 Oh, that actually is a lazily loaded property, which is going to trigger a query, so I should

19:57 have put a join, and then I'd be in a better place.

20:00 That's a programmer pattern thing that you should pay attention to and work with.

20:04 The one where I don't know how to fix it is more like the serialization thing.

20:08 Like, what if I want to go through my database and go to 10,000 records and make some changes

20:14 to them?

20:14 So often it's do the query, loop over the 10,000 things, make a change, call save.

20:20 Maybe it's in one giant transaction that you finally push the changes back, but you're

20:25 pulling all the data back.

20:27 And one of the things I really like about your, or I'm here, is like this update section here,

20:33 where you can do set-based operations without pulling the records back.

20:37 Yeah.

20:37 So you can do stuff like, so this example here, wait, band.update, band.popularity, 10,000.

20:42 But then you can also do band.popularity is band.popularity plus 10.

20:47 And then in the database, it will then just add 10 to all of the numbers.

20:50 Oh, really?

20:51 That's awesome.

20:52 And then it's all just magic around, you know, Python magic methods.

20:56 It's just, as a library author, it gives you so much power.

20:59 It's one of the things I love about Python.

21:01 Yeah.

21:01 Because when you're building like query languages, like ORMs, I think very few languages can

21:06 really rival Python with its flexibility.

21:08 Yeah.

21:09 So that's really why a lot of this stuff's possible.

21:11 It's really neat.

21:11 And I think the ability to push these changes without actually, you're still programming

21:17 in this, the ORM classes and the models, but you're not actually pushing a whole bunch of

21:21 them back and forth to make the changes, but to do these set-based operations, like delete

21:25 them where, or make this update to this value where this is true, and then just push that,

21:30 make that happen in the best way you can with SQL, right?

21:33 Yeah, exactly.

21:34 I think coming back to your point around the M plus one, I think properties are something

21:39 that can be a little bit evil.

21:40 And I've really shy away from them in the Piccolo code because it can, you call a property

21:46 and you think you're getting a value back, but it could be doing any kind of magic.

21:49 And then once you've defined something as a property, you then can't add arguments to

21:55 it without breaking your API.

21:56 So yeah, I think that that's something I've tried to steer away from in general with Piccolo,

22:00 like properties.

22:01 Yeah.

22:01 A lot of hidden stuff happening there, right?

22:04 Yeah.

22:05 It's not entirely clear.

22:06 I think they're super useful, but certainly in something where I thought I was accessing

22:10 a field of the class, but what I actually did is make a network call.

22:13 Like that distinction is possibly too big of a bridge to just make that automatic a lot

22:18 of the times.

22:18 Yeah.

22:19 There's no async properties in Python as well.

22:22 So that's kind of one of the reasons why it doesn't use any async properties.

22:25 Maybe they'll add it.

22:27 If they add it, maybe I'll put a comment on the PEP saying, don't do it.

22:30 Yeah, exactly.

22:32 Another thing that's interesting here is on all this code, every code you've written

22:36 is await band.select or await band.delete and so on or update.

22:41 And then at the end, you say run.

22:43 This is the explicit part that you were talking about in your API.

22:46 Like I know here's where it's happening.

22:48 And it probably makes a lot of sense to do that as well, because on the flip side of it,

22:52 that's where you have to await it anyway, right?

22:54 Yeah.

22:54 So what happens is you build up this query, you just chain methods to it.

22:58 And then at any point you can print out that object and I'll give you the SQL.

23:02 And then until you actually await it, there's something under the hood that I don't really

23:07 publicize.

23:07 You don't need the dot run.

23:09 If you await it, it will run as a convenience because people forget.

23:12 But then it just makes it easier from a documentation perspective to say when it's async, use run.

23:17 And when it's synchronous, use run sync.

23:19 Right.

23:20 And then if you do run sync, then I've got like a bit of magic in there where it tries to

23:26 create an event loop to run it, or it tries to figure out if there's already an event

23:29 loop.

23:30 If there is, run it in there.

23:31 So you can use Piccolo in an old school WSGI app if you wanted to just synchronously.

23:37 Yeah.

23:38 Well, let's dive into that because that's one of the things that really stood out to me.

23:41 Many frameworks or APIs packages tell you, you're going to either have to go, you know,

23:47 you're going to take a fork in the road.

23:48 You're going to go down the async fork and you're going to use the async library like

23:51 HTTPX or you're going to go on another fork and you're going to use the request library

23:55 that has no async.

23:56 And you're going to go down that path and you choose and then you just go.

24:00 And with Piccolo, you can actually run, I guess the default behavior would be to be async and

24:07 await.

24:07 But it has this dot run sync, which will kind of cap where the asynchronous behaves and goes.

24:13 And it'll just that you could run it in a regular Flask app or Django app or whatever

24:18 and not worry about it being async at all.

24:20 Right.

24:21 Yeah, that's right.

24:21 And it's actually one of the design challenges with Piccolo is how do you create an API which

24:25 is synchronous and asynchronous?

24:27 And there's only really two ways of achieving it is with a method like run or run sync or

24:33 with context managers.

24:34 So some of them you'll create either an async context manager or synchronous, and then that

24:40 will then impact whether the underlying query is synchronous or not.

24:43 But then it adds a little bit more boilerplate if every time you're in a query, you need

24:47 a context manager to tell it to be async.

24:49 So this is kind of the best outcome I could think of was just have dot run or dot run sync.

24:55 I think this is great, especially since even if you forget the run, it'll still run async,

24:59 but there's a way to kind of cap it.

25:01 So something I wanted to talk about, it's driven me crazy ever since async and await were introduced

25:07 because I don't find it to be true, but I hear it all the time spoken about in the community

25:12 async and await, they're super neat, but they're like viruses.

25:16 And in the sense that soon as like one of your functions way, way at the bottom has to do

25:21 something async, well, then the thing that calls it has to be async and await it.

25:24 And the things that call that function now all have to await it.

25:27 And that percolates all the way to the top of your app.

25:30 And so now you've, by using any async library, you've turned your entire thing into this like

25:34 async vertical call stack.

25:36 Your example here shows that that's not, it doesn't have to be that way, right?

25:40 That's sort of the naive, I'm just going to like write the code without thinking about it.

25:43 But if you wanted to say, have your data access layer do three things, because it's got to pull

25:49 some stuff from different places.

25:51 You want that to be async.

25:52 It doesn't mean that function has to be async.

25:54 It could just start its own event loop, do the three things faster than without it,

25:57 and then return the answers, right?

25:59 You can kind of cap it at any level that you want.

26:01 And your run sync is kind of an example of that.

26:04 Like you can choose to not have it, just turn your entire app async.

26:08 You can jump between them.

26:09 So typically if people use an async, then it's like, the argument is, if you need async,

26:13 your whole app probably should be async because otherwise why use an async?

26:18 But then you can flip between them quite easily.

26:20 So if you've got a synchronous app and you want to call some async code,

26:23 there's asyncio.run.

26:24 And you can also do stuff like spin up an event loop in a different thread and then send

26:29 work to that.

26:30 Yeah, absolutely.

26:31 So it is quite fluid.

26:32 You can flip between them quite easily.

26:34 Yeah, I mean, just one example that comes to mind is what if I wanted to go web scrape

26:39 every page at a certain domain?

26:41 So I've got a function that gives me a domain.

26:43 I give it a domain and then I want it to return or store into the database all the pages, right?

26:49 That would be perfectly reasonable to have that thing go, okay, well, let's do a,

26:53 a request, figure out what all the pages are, and then just, you know, recursively sort of grab them

26:57 all asynchronously.

26:58 You would get a huge boost on getting every page off a site, even if that function blocked.

27:03 You know what I mean?

27:04 Because it itself could just go crazy against the server.

27:06 Maybe it shouldn't, but it could.

27:08 Yeah.

27:08 I'm a huge fan of asyncio.gather as well.

27:11 So that's a really beautiful API just for saying, do these 50 things now, please.

27:15 And let me know when you're done.

27:17 Yeah.

27:17 And block, right?

27:18 When you're done, give me a list of answers or errors.

27:21 Yeah.

27:22 Also out in the live stream, Chris May.

27:23 Hey, Chris.

27:24 It says, I'm so excited to use Piccolo with unsync.

27:27 I have a workflow that'd be nice to parallelize.

27:29 And yeah, so I think unsync is another really interesting library that wraps up asyncio plus

27:36 threading plus multi-processing.

27:38 But then gives you a nice way to cap it as well because you can go, like, be given a task that

27:43 comes back from there.

27:44 If you just ask for the result and it's not done, it'll just block like a regular thing.

27:48 And it does kind of what you're talking about.

27:50 It'll have a, it has a background thread with its own event loop and it just pushes all the

27:54 work over to there.

27:55 Yeah.

27:56 So, yeah.

27:56 That's a cool library.

27:58 Yeah, it is.

27:59 At least for the size, right?

28:00 Like 126 lines and it unifies those three APIs and adds some more stuff.

28:05 That's pretty big bang for the Python byte.

28:08 Yeah, that's impressive.

28:09 I wish Piccolo was that terse.

28:11 I think thousands, tens of thousands of lines by now.

28:14 Yeah.

28:15 Yeah.

28:15 Yeah.

28:16 So another one of the new Python 3, 6 onward type of things that's really cool is the type

28:22 annotations.

28:23 Yeah.

28:23 I love type annotations.

28:24 So part of my day job in the past was using Swift and Swift is almost like hyphens, like

28:30 brother or sister.

28:31 It was very heavily inspired by Python.

28:33 Right.

28:34 It's like if Python all of a sudden decided it was incredibly strict about typing,

28:37 and type definitions.

28:39 Yeah.

28:40 That would be a lot like Swift, right?

28:41 Inward compiled.

28:42 Yeah.

28:42 It's a combination of type annotations and the tooling to support it.

28:45 So in Python's case, it's VS Code.

28:47 And in Swift's case, it's X code.

28:48 And it just means that when you're refactoring, it just makes it so much more confident about

28:53 what's going on.

28:54 Yeah.

28:54 It provides documentation because previously people were putting in a doc string anyway.

28:58 So why not put it in your function definition and then you can introspect it.

29:02 And my Pi is incredibly powerful.

29:04 I honestly don't think I could have built Piccler without type annotations because it makes your

29:09 code so much more maintainable.

29:10 The click to go to VS Code as well is just a beautiful usability improvement.

29:16 And then kind of one of the hidden benefits is it makes tab completion so good.

29:20 So a lot of Python auto completions, they use a library called Jedi under the hood.

29:26 So when I was building Piccolo, I had to look at the source code to try and figure out how

29:30 it does its magic.

29:31 And if you give a type annotation to something like this is what this function returns, it's

29:35 a really strong indicator to Jedi that this is what's going to get returned.

29:40 I don't need to do any magic anymore.

29:41 Yeah.

29:41 I would say that half the time, that's why I do it is to make the editor better.

29:45 So both VS Code and PyCharm, like take a good look at what the type annotations are.

29:50 Yeah.

29:50 Right.

29:51 And you just say, oh, you're trying to pass a string and that's really supposed to be

29:54 an integer.

29:54 But then also, like you say, tab completion or autocomplete all over the place is fantastic.

29:59 Yeah.

29:59 I think there's a distinction as well, where I think if you're building an application,

30:02 let's say you're building a Django app or a Flask app, you don't need to care quite as

30:05 much.

30:06 Like I personally would still add type annotations, but for libraries, I think it's just absolutely

30:10 essential.

30:11 Like I don't think any new Python library should be written without it because you kind of,

30:15 shortchanging your users in a way.

30:18 Is there?

30:18 I totally agree.

30:19 Yeah.

30:20 Yeah.

30:20 In the Flask app, for example, that you've mentioned, you know, I would say on the boundaries,

30:25 right?

30:25 Like here's a data access layer, like put type annotations on those little bits there.

30:29 And then the rest of the app, usually the editors and the tools will just pick it up

30:33 and continue to tell you what you're working with.

30:35 Yeah.

30:36 But if you're doing a library, right?

30:37 You want every function or every class to be kind of standalone and know everything it

30:42 can.

30:42 Yeah, definitely.

30:43 And one more thing about type annotations is it's probably the greatest source of interview

30:47 questions ever made because you can ask people in an interview, what's the difference between

30:51 like a sequence and an iterable?

30:52 And like when you use type annotations, you really start to think about what's going on.

30:57 And it's a great learning experience too.

30:59 Yeah.

30:59 I want to pass a generator here, but it takes a list of things and it says that won't work.

31:03 And maybe you just need to relax your type annotations to an iterator.

31:07 Yeah.

31:08 A thing.

31:09 A quick question from Roller out there in the live stream.

31:11 Hey, Roller.

31:12 Can I just pick a little and play some Mongo engine or is it just for a relational stuff?

31:16 Yeah.

31:17 It's just relational and it's, you can use SQLite locally, but it's mostly Postgres.

31:21 It was really built to take advantage of Postgres because Postgres is, it's like the fastest

31:27 growing SQL database in the world, which is remarkable to think it's how old it is.

31:32 And I think I'll, you know, I think adding other SQL databases would be quite easy, but

31:37 adding something like Mongo would be a bit trickier.

31:40 I wouldn't say it was impossible, but a bit more work.

31:42 Yeah.

31:42 I would think so.

31:43 Yeah.

31:43 It's not, certainly not impossible, but like joins and stuff would get tricky.

31:47 What about SQL injection?

31:49 I mean, many of us have heard about the little Bobby tables, XKCD, which is delightful, you

31:55 know, you know, sort of schadenfreude sort of way.

31:58 We all kind of want to relish in somebody else suffering this, but I find that this is actually

32:04 one of the really nice things about ORMs most of the time is that they scrape off the ability

32:10 to do SQL injection because you're not building the SQL.

32:13 Yeah, definitely.

32:14 So your database adapter.

32:16 So something like asyncpg or psycopg in the synchronous world, what you want to do is you

32:21 want to pass it the query string with placeholders for any user submitted values.

32:25 And then you submit the values that separately, like in a list.

32:29 Right.

32:29 Like a parameterized query, basically.

32:32 Yeah.

32:32 And as long as you do that, you're safe.

32:34 But then for a library, when people are programmatically creating very complex SQL queries, and then you

32:40 need to try and make sure that you've got the right values that match the right placeholders

32:45 to pass to the adapter.

32:46 It is quite challenging.

32:48 There's some like recursive code where it has to.

32:51 So we use something called query strings internally within Piccolo.

32:53 So it never concatenates strings for SQL statements.

32:57 It just, it uses query strings and then it compiles them before sending it to the database adapter.

33:02 And it basically looks through all of the sub query strings it might have if it's a really

33:06 complex query.

33:07 And then it kind of passes it to the adapter.

33:10 But yeah, it's just one of the complex things about building ORMs for sure.

33:14 And also one of the most dangerous to get wrong.

33:16 Yeah.

33:16 Yeah, it absolutely is.

33:17 There's untold number of bad things that can happen with SQL injection.

33:22 And it's so easy.

33:23 All you have to do is put a single little tick to comment out stuff, semicolon to finish that

33:30 statement.

33:30 And then you can run arbitrary code.

33:32 And a lot of times you can even, some database engines will let you run sub process type things.

33:38 Which is even worse.

33:39 But yeah, it's not good.

33:41 So you definitely want to avoid it.

33:42 Yeah, it's a good argument for using ORMs and query builders because it'll make it less

33:46 likely.

33:46 I think for sure.

33:47 You know, another thing that I wanted to touch on a little bit here is the actual filtering

33:53 or projection statement type bits.

33:56 So I mentioned using Mongo Engine before, which I'm a big fan of.

33:59 And it's basically a Mongo equivalent of the Django ORM.

34:03 So in that regard, they're real similar.

34:05 And you do things like, if I wanted to say where the band popularity, or let's say the

34:10 band name is Pythonistas, right?

34:12 You would just say name equals Pythonistas as part of the filter.

34:16 And there's two things that are crummy about that.

34:19 One is you get no autocomplete that there's a column called name because it doesn't really

34:24 know what class, even though you started out like band.objects.

34:27 It doesn't, in the filter part, it no longer knows that the name came from the band, right?

34:31 That's not part of the language.

34:33 And then the other one is you're doing an equals or you've got like weird operators like in

34:38 the name, like name underscore underscore GT for greater than and stuff like that.

34:43 Whereas yours, you just write what you would put into an if statement or a loop or something

34:48 like that.

34:49 So you would say like band.popularity less than a thousand.

34:51 That's the thing you send in there.

34:53 Yeah, that's right.

34:53 So I've been caught up, tripped up so many times in the past with Django where I've had

34:58 something like name double underscore something else.

35:01 And then it can't really understand that's wrong, like a linter or when you, while you're

35:07 coding, it only really knows at a runtime and then you've got a 500 error.

35:09 So the idea here is a linter would be able to pick up these problems.

35:14 Exactly.

35:14 Because so much of the pieces are, they're just using star, star, kwrgs and then they

35:19 figure out how to generate a query out of like looking for special keywords in the key names

35:25 and then turning those to columns.

35:27 Also the refactoring thing, right?

35:28 The linters.

35:29 And if I want to do a refactoring to rename popularity, it's not going to check popularity

35:34 underscore GT as a keyword argument.

35:37 It has no idea those are related.

35:38 Yeah.

35:38 Yeah, definitely.

35:40 So the way it's implemented this, the double equals and all these operators is the amazing

35:45 things about Python is how you can just overload like fundamental things about the language.

35:50 So you can overload what addition means.

35:52 And when someone first tells you that, it sounds like the most mental thing in the world.

35:55 Cause why would you want one plus one to equal five?

35:57 But then it turns out when you're building an ORM, it's golden.

36:01 And this is one reason why I find Python just so compelling over and over again is because

36:06 as a library author, you can do this stuff that you can get closer to more of like a DSL than

36:12 like a normal programming language.

36:14 Specific language.

36:14 Yeah, absolutely.

36:15 Yeah.

36:16 So is this a be done with descriptors or what's the magic?

36:19 For the less than there's like done like double underscore LT and you can override that.

36:25 And then what happens is when you call that method, it returns a where object.

36:28 And then you can also, you could do in brackets, band dot popularity, less than a thousand and

36:34 then double and sign as well.

36:35 Band dot popularity greater than 500.

36:38 So you can combine them with and and all statements.

36:41 So the where statements in Piccolo can get like really powerful.

36:44 So you just have to teach the where clauses how to and and then structure it in a way that

36:49 Python will let it kind of go through, right?

36:51 Yeah.

36:52 Or you can do dot where, put some stuff and then another dot where statement.

36:55 And if you've got multiple where statements, it becomes like it's an and.

36:58 Yeah.

36:58 But yeah, it's just, it's just all Python magic, which is one of the reasons I love Python.

37:02 Yeah.

37:03 Speaking of overridden things, the thing that I think is the most insane, but I'm starting

37:08 to love, but took a while to get used to is the way the path object works for defining

37:13 paths.

37:14 Yeah.

37:14 So, you know, the forward slash often means like drive separation on the POSIX systems.

37:21 And, you know, it's close enough.

37:22 You could actually put forward slash in your strings and Python on Windows and it'll

37:25 still like, okay, fine.

37:26 Backslash is what you meant.

37:27 So they overrode the divide operator in code to allow you to concatenate strings and paths

37:34 together.

37:35 And that's just crazy.

37:36 Yeah.

37:37 The first time I saw it, I was very confused, but when you understand that it's okay, but

37:40 yeah, totally.

37:41 Yeah.

37:42 I'm, I've gotten okay with it as well.

37:45 And I start to use it and I really like it now, but I was like, I don't know if I can

37:47 get behind.

37:48 This is, this is a bridge too far.

37:49 This is, that's division.

37:51 What are we doing here?

37:51 No, it's cool.

37:52 It's cool.

37:53 Talking of magic to Piccolo, like all around, it uses meta classes a lot.

37:57 And there's something that got added in, I don't know if it's Python 3.7 or 3.6, but they

38:02 actually changed meta classes slightly.

38:04 So there's now like a Dunder magic method called init subclass.

38:07 And Piccolo uses this a lot.

38:08 And it's, it's actually an amazing hidden feature of Python where you can now add keyword

38:14 arguments to a class definition.

38:15 So if you had class foo, open brackets, inherits from bar, comma, and then you can start adding

38:21 keyword arguments to the class to customize its creation.

38:24 And that's kind of like a new layer of magic that's been added to Python recently.

38:29 And Piccolo uses it extensively, but I don't see many other libraries using it yet because

38:33 it's probably not so well known, but yeah, just kind of spread that bit of magic.

38:37 So hopefully people can use it too.

38:39 Nice.

38:39 Yeah.

38:39 That sounds awesome.

38:40 I can certainly see how I'm like trying to create the classes, like the band class or

38:44 whatever you say that it's going to be, would definitely use that.

38:47 So one of the things that you say is awesome about Piccolo is the batteries included.

38:52 Yeah.

38:52 So let's talk about some of the batteries.

38:54 So yeah.

38:56 So the main battery by far is the admin, because when I started it, I was working for a design

39:00 agency and admins are incredibly important for design agencies because you want to put

39:05 something in front of a customer that they like the look of and they're comfortable using.

39:08 So this is a huge part of the effort that's gone into Piccolo.

39:12 And so this is a, it's sister project called Piccolo admin.

39:15 And what happens is it's, it's an ASCII app.

39:17 So I can maybe go into more detail about ASCII later on, but all you do is you give it a

39:21 list of Piccolo tables and then it uses Pydantic.

39:25 So Pydantic is a serialization library and it basically creates a Pydantic model from the

39:30 Piccolo table.

39:31 And then Pydantic models have this really useful method where it's JSON schema and it creates

39:38 a JSON schema for the Pydantic model.

39:39 Right.

39:40 Because the Pydantic classes know this field is an int, this one is an optional date time

39:45 and so on.

39:45 Yeah.

39:46 So Piccolo has really good Pydantic support, but it's in a sister repo called Piccolo API.

39:50 And then that, so that creates the Pydantic model and it also has something called Piccolo

39:55 CRUD.

39:56 So you give it a Piccolo table and it creates another ASCII app, which has got all of the CRUD

40:02 operations for your database.

40:03 So you can programmatically create this huge API just by giving it a few tables.

40:07 And then the front end is written in Vue.js and it's completely decoupled from the back

40:12 end.

40:12 It's just all by API.

40:13 So yeah, I'm a huge fan of Vue.js because it's, it's very natural for Python developers

40:18 who are used to the template syntax in Django and Flask.

40:21 If you looked at the Vue templates, you'd be like, this looks very familiar.

40:24 So I'm a big fan of that.

40:26 Yeah, it's nice.

40:26 Yeah.

40:27 I think it's super close to Chameleon because of the attribute driven behavior as well.

40:31 Yeah.

40:31 Yeah.

40:32 But then like to make a working admin requires so much work because you've also got the security

40:36 side.

40:36 So Piccolo API has a bunch of really useful ASCII middleware and it has like session authentication,

40:42 CSRF protection and rate limiting as well because you don't want people to spam the login.

40:47 So like just to get a fairly simple admin is in, it's like a iceberg to do it properly.

40:54 So yeah, a lot of efforts gone into the admin, but I'm really proud of it.

40:58 And like, this is really what excites me more than anything about the future because as

41:02 we add support for post GIS and stuff like that, being able to create really interesting

41:06 widgets around data.

41:08 So, you know, how can I design a rectangle field for post GIS or a location field or.

41:14 I could see some really cool stuff that are sort of template extensions, you know, like

41:19 let's just pick Jinja, for example.

41:20 Like if you had one of these objects, you could pass it over and it knew, for example, here's

41:26 a daytime, you could just say, make a calendar picker here.

41:29 And it just, you know, as long as you have the JavaScript included and instead of just

41:33 putting the text, it gives you like a nice little Ajax-y widget or these, this list goes

41:39 on a map, drop in the map widget and off it goes.

41:42 Yeah.

41:42 So basically Piccolo admin, it's just, it's just turns Pydantic models into UI and it's

41:48 actually quite, it's actually quite interesting.

41:50 And I'd love to get it to the point where for a business app, you just use Piccolo admin,

41:53 you don't even have to build UI.

41:55 You just say, here are my tables.

41:56 Yeah.

41:57 And then, yeah.

41:58 Well, the truth is so often, like there's a lot of these little internal apps that people

42:02 build that are just like forms over data.

42:04 I just need to see the details, click on one, edit it, create a new one and delete one.

42:08 And like, that's the app I need for this table.

42:10 Could you build that?

42:11 Right?

42:12 Yeah, for sure.

42:13 It is a lot of it.

42:14 Cause I think with the approach I talked to Piccolo is you have a lot of Python libraries

42:18 and they kind of start from the outside in.

42:20 So they start from the URL layer and then the views and like middleware.

42:24 And then over time, they then add the data layer and the security.

42:27 But then with Piccolo, it's kind of from the inside out.

42:30 Like I started from the data layer and then have the admin and some middleware.

42:34 So it's quite a nice companion to have a ASCII app.

42:36 So just kind of pick the framework you like and then Piccolo kind of gives you the data

42:40 layer and the admin.

42:41 That's kind of.

42:41 Yeah.

42:42 Yeah.

42:42 Yeah.

42:43 Well, let's talk about the ASCII stuff a little bit.

42:44 Cause you did mention that there's some interesting support for those things.

42:49 And it's got like, to some degree, native FastAPI, Flask, and even Blacksheep, which is

42:56 an interesting one, support for those frameworks, right?

42:58 Yeah.

42:59 So I'm a huge fan of ASCII because I was a Django Channels power user and Andrew Godwin

43:04 created ASCII out of Django Channels.

43:06 It's really like a beautiful thing.

43:09 If you look, so Starlette was the one that really built the foundation.

43:12 So this is an async library, but also kind of like a framework.

43:17 Like you can build an app with Starlette or you can use it to build up frameworks.

43:20 What's amazing about ASCII is like every component in an ASCII framework is ASCII.

43:25 So ASCII is basically the spec where it's a function that accepts a scope, a send and

43:30 a receive.

43:31 And then if you look at the internals of Starlet, everything's ASCII, like the middleware's ASCII,

43:35 the endpoints are ASCII, like the whole thing.

43:37 And it's like super composable.

43:39 So you can say, I've got an ASCII app and you can mount other ASCII apps within it.

43:44 So this is what I love about ASCII as a spec.

43:48 So you can say, take a FastAPI app or a Starlette app and include Piccolo admin.

43:52 Same with Blacksheep.

43:54 Right. You could say slash catalog is actually handled by this other app written in FastAPI

44:00 where the main thing is written in Flask or something like that.

44:02 And you just kind of click them together in that cool way.

44:04 Maybe Core would have to be the one, but still.

44:06 Yeah. And I really love that.

44:07 It's quite exciting that you could kind of build an app with multiple frameworks and be like,

44:11 well, this part of the app will be better served by FastAPI.

44:14 But this bit, I just need Starlette or I want Blacksheep or whichever ASCII framework people

44:19 can dream up.

44:20 So I think it's really exciting for the Python community, the ASCII spec.

44:24 Yeah. And we did talk a little bit about the challenges and the cascading effect of async and await.

44:30 But if you're already running a framework that has async view methods, there's just nothing to it, right?

44:35 You just write your code and you just await the bits you got to await in the view method.

44:39 And it just like the server and the framework handled the vent loops and all that business.

44:44 Yeah. I think as well, what kind of happened is asyncio came out and it doesn't directly affect speed.

44:49 It's more about throughput, but it's like the Python community took it as a challenge to build faster frameworks.

44:55 And so there's a lot of them have really quite fast internals and they do feel quite cutting edge.

45:01 Yeah. Like UV loop and stuff like that.

45:03 They're like, how can we do this, but have the minimal overhead of adding this?

45:07 I mean, people do talk about, okay, async and await won't make your code go faster.

45:10 Well, it won't make CPU code go faster.

45:12 But so often what we're doing, especially in web apps is waiting.

45:15 I want to wait on a database and then wait on the network.

45:18 And then I want to wait on the database again.

45:19 And then I'm going to wait on an API and then I'm going to send back three lines of JSON, right?

45:24 Like 99.9% of that is just waiting on something else.

45:27 And when you're using async and await, like you can just do other things while you're doing that 99% of waiting.

45:32 Yeah. So usually if you do a database query, it takes a few milliseconds.

45:36 But then if you use the time it module on Python and you see how long basic operations take, they're more like microseconds.

45:42 So there's like orders of magnitude difference in how long a database query takes to basic Python stuff.

45:47 But this is why having stuff like UV loops are really important because if you had a really slow event loop, it kind of wouldn't make much difference.

45:53 But because the event loops fast as well.

45:55 And like a lot of the projects I did in the past, the throughput is really important because some apps, you won't have a lot of traffic.

46:01 And then all of a sudden you'll get a thousand users.

46:04 So I was doing like live events and you'd get a thousand people at once.

46:08 And in that situation, throughput is incredibly important.

46:11 Right. I mean, look at how the whole healthcare rollout in 2008 went.

46:15 Right. I just can't help but think there must have been more awaits available to those, that those frameworks and those web apps.

46:22 Because it just kept crashing and stuff was timing out.

46:24 And I'm sure it's just like, well, we're just going to wait on this other slow government API.

46:30 And we're going to do it for a lot of people.

46:31 And it's just going to overwhelm it.

46:32 Right. And it's just crazy.

46:34 It just feels natural because like in the web server world, you had Apache and a lot of people moved to Nginx.

46:39 That's very similar.

46:39 It's like event loop driven.

46:41 And we've kind of seen how beneficial Nginx was.

46:43 So it just makes perfect sense to build your backend in the same way.

46:47 Yeah, absolutely. Absolutely.

46:48 All right. A couple more things we got some time to talk about.

46:51 So over here, you've got a Django comparison page, which I guess also could be slightly a Mongo engine comparison page.

46:59 Because like I said, they're basically the same thing without the nesting.

47:01 So if somebody is familiar with Django and they're like, I would like to consider using this for my framework or for part of my code or whatever, just like want to use it.

47:10 But they already know how to do stuff in Django.

47:12 You have like, well, here's how you would create an object and save it in the different frameworks.

47:16 Here's how you would update an object and make changes and so on.

47:20 And you can just go through one at a time and just sort of compare the different pieces, right?

47:24 Yeah.

47:25 So it's quite heavily inspired by Django.

47:27 But then I think Django in its ORM, it's more Pythonic.

47:30 So rather than using where it uses filter.

47:33 But then with Piccolo, it's meant to be very, very close to SQL.

47:36 Because the theory is, if you know SQL, it'll be super easy to learn.

47:40 And when you do need to drop down to a SQL query, there's no like mismatch.

47:44 You're just like, well, I'm always sort of working in a SQL mindset.

47:47 But there are a lot of similarities with Django still.

47:50 I think people could pick it up quite quickly.

47:52 Yeah, I agree.

47:53 Like instead of object that values list, you have a select projection.

47:57 Or instead of filter, you have a where.

47:59 But it's honestly not a huge mental jump to make.

48:02 Yeah.

48:02 And Piccolo also takes like huge inspiration from Django migrations, which I think is kind of like the gold standard of migrations in any language.

48:10 So a lot of effort's gone into that.

48:13 Yeah.

48:13 That's another one of the batteries that you were kind of touching on, right?

48:16 Is the migrations bit.

48:18 Yeah.

48:18 But it's migrations are incredibly hard to do right.

48:21 I think the Django way is.

48:23 I can only imagine.

48:24 I don't even want to try to imagine writing that because it seems really hard.

48:28 Yeah.

48:28 So the way Django does it is it looks at your tables and then it creates a migration file.

48:33 It then adds up the migration files to build a picture of what the schema looks like.

48:38 And then that's how it then creates subsequent migrations by doing a diff.

48:41 Right.

48:42 That's what Piccolo does.

48:42 I've got to go from this level to that level.

48:44 So here's the five migrations to basically use in order.

48:47 Yeah.

48:48 And then you've got to do code generation as well.

48:50 So like with Piccolo, it has to actually create a Python file.

48:53 And that's harder than what it seems actually writing a Python file.

48:56 But if you look at the Piccolo migrations, they're actually really, they look like quite beautiful

49:00 Python code.

49:01 And there's a little trick I use internally.

49:03 I use like the black formatter on it before I write out the auto-generated code.

49:07 Oh, that's cool.

49:08 So your generated code is pep8 and all the goodness.

49:11 Yeah.

49:11 So if you look at it, you're like, oh, that's quite nice.

49:14 That's clever, actually.

49:16 Yeah, that's really clever.

49:17 Yeah, it's cool.

49:18 Black.

49:18 Yeah.

49:19 Yeah.

49:19 I feel like other frameworks, like for example, the cookie cutter stuff, you know, you're just

49:24 generating code files like crazy.

49:26 I feel like you could apply that same technique as after we inject all the user entered values,

49:31 let's just do a quick format on them and then drop them.

49:33 Yeah.

49:33 It makes sense because otherwise you'll run your linters on your project and they'll fail because

49:38 your migrations aren't correctly formatted.

49:40 Yeah.

49:41 Yeah.

49:41 Yeah.

49:42 No, that's cool.

49:42 A quick question from Teddy out in the live stream.

49:45 It says, I don't use ORMs much in my day-to-day.

49:48 What are good use cases besides web apps for them and where does Piccolo perform better?

49:52 So two questions.

49:53 So I think that data science is obviously a big bit.

49:57 So another reason for building Piccolo is data science is so much on the ascendancy in the

50:02 Python world and people are just, you know, still dealing with databases on a day-to-day

50:06 basis.

50:06 So you can use it in a script if you like, but there's maybe a couple of examples in the docs

50:11 where, you know, you might be scraping some data from a website and then you just need

50:15 to stick into Postgres.

50:16 So that would be another good example for using Piccolo.

50:19 Yeah.

50:19 That is a good example.

50:20 And then where it performs better.

50:22 It's really where you need the async or you might still want a web app component, even if

50:28 it's just like a data science script, you still might want an admin screen to view that data.

50:32 Yeah.

50:32 I think that performs better.

50:34 You could maybe break into two pieces.

50:36 Where does Piccolo perform better?

50:37 And I think the async stuff is really important there, like you say.

50:40 And then where does an ORM perform better?

50:43 You know, when you talk about performance and getting stuff done, like sometimes it's how

50:47 fast is the code run, but sometimes it's how fast do I get the final thing built.

50:51 Right.

50:52 And I think ORMs, even if they're not always the most efficient way, sometimes they're really

50:56 efficient, but not always.

50:57 But, you know, they could help you safely get to the other side, especially if you don't

51:02 know SQL super well.

51:03 Yeah.

51:03 They hold your hand a little bit.

51:04 Yeah.

51:05 I created this while working at a design agency and speed's really important at a design

51:09 agency, not really execution speed in terms of SQL queries, but in terms of scaffolding

51:13 an app and being productive.

51:15 So there's, so Piccolo has something called Piccolo asking you, and then to use that command,

51:19 it'll basically scaffold you a web app.

51:21 So it supports FastAPI, Starlet, Blacksheep.

51:24 Yeah, that's really cool.

51:24 So yeah, it's, you just kind of create your starter code and your starter structure from that,

51:28 right?

51:29 Yeah.

51:29 So a bit like with Django where you, you know, you create a project on the command line,

51:33 but with Piccolo, you get an option of different ASCII frameworks.

51:36 And over time, I'd like to add, you know, way more because there's, there's, there's

51:40 many more exciting ASCII frameworks like Quart, Sanic, Django itself is actually an ASCII

51:45 app, so it could support Django.

51:46 Yeah.

51:47 Django has come along there.

51:48 So if people were out there listening and their favorite framework wasn't listed in one

51:54 of those three or wasn't one of those three, PRs are accepted, I guess, and they could

51:59 integrate, you know, their, their favorite Sanic or whatever they're after.

52:03 Yeah, definitely.

52:03 Like any feedback's really appreciated.

52:05 So like the community has definitely helped me a lot with Piccolo, just, just as much as

52:09 trying it and giving feedback or, you know, obviously pull requests are also like really

52:13 valued.

52:13 Even if you just want to raise an issue to say, well, how did you do this?

52:16 Like, you know, that's still welcome.

52:19 Yeah.

52:19 Awesome.

52:20 Well, well, maybe that's a good place to talk about where things are going in the future

52:23 and kind of wrap up our conversation a bit.

52:26 Yeah.

52:26 So I feel like I'll never be bored with Piccolo because Postgres is continuously developing

52:31 and adding new features.

52:32 And I almost feel like Postgres is almost like an operating system in a way.

52:36 Like the amount it does is kind of insane.

52:38 So it even has like a PubSub built in.

52:41 You can do that.

52:41 Listen, notify.

52:42 Oh, wow.

52:43 I'd like to do post GIS support.

52:44 Timescale DBs are really up and coming extension as well for Postgres for time series data.

52:49 But then a lot of the stuff I'm excited about is like on the admin side.

52:54 So as I mentioned before, Piccolo admin effectively turns Pydantic models into UI.

53:00 So the next thing I want to add is you can basically give it arbitrary Pydantic models and it will

53:06 render them as forms in the admin.

53:08 So if you have, for example, you want to send an email, you'll just create send email model,

53:13 give it to Piccolo admin and it'll generate a form.

53:15 That's stuff I'm really excited about as well, just to increase the utility of Piccolo admin.

53:19 So a backend developer could build a functional app for a business without actually writing any code.

53:24 That's kind of the dream is to build like a really, really great admin.

53:27 Yeah.

53:28 Yeah.

53:28 These self-serve, like once you create the app and hand it off, how far can people go before they have to hire your design agency again or something like that?

53:38 The more that they can just run with it, the better, I suspect.

53:40 Yeah.

53:41 It's such a huge benefit from Django, like having that admin.

53:44 So I just want to kind of see what I can do with the latest technologies to build a really great one.

53:49 Yeah.

53:49 What's the story with Django and Piccolo?

53:52 Is there a reasonable way to click them together or is it really not so much so far?

53:57 I think you can.

53:58 I haven't really tried it much, but it's very configurable, Piccolo.

54:01 So, you know, and none of the names, they deliberately don't clash with Django.

54:04 So Django has a settings.py, Piccolo has a piccolo underscore conf.py.

54:09 And then Django has a migrations folder, but Piccolo has piccolo underscore migrations.

54:14 So there's no clash there.

54:16 So in theory, they would work together.

54:17 There's no like compatibility layer between the admins.

54:20 So you'd have like two separate admins or, but I'd like to add support for Django as it is an ASGII app

54:25 and it's the originator of the ASGII standard.

54:27 And I still think Django is one of the great kind of masterpieces in the framework world.

54:32 It's lasted so long and it's still such a rock solid choice.

54:35 I would like to see what I can do there.

54:38 Yeah.

54:38 The closer those could be, I think, right?

54:41 Like this having the genesis and so many similar ideas to Django, it seems like they should be somehow working together, which is great.

54:47 Yeah, that'd be cool.

54:48 All right.

54:48 Well, Dan, I think that might be about it for time that we got.

54:51 Let me ask you the final two questions that I always ask.

54:55 If you're going to write some code, you can work on Piccolo.

54:56 What editor to use?

54:58 I think I caught a hint of it earlier, but go ahead.

55:00 Yeah.

55:01 VS Code all the way.

55:02 I was a huge Sublime Text and TextMate user.

55:04 And I was like, I'll try out this VS Code, see what all the hype's about.

55:07 And after five minutes, I was never going back.

55:10 I just think it's such a great gift to the world from Microsoft.

55:13 It just gets better and better as well.

55:16 Yeah.

55:16 Love VS Code.

55:17 Right on.

55:17 And then notable PyPI package you want to give a shout out to?

55:21 So I'm going to cheat and pick two.

55:22 So Pydantic, because I think it's such a nice serialization library.

55:26 And I think it could almost be in the standard library.

55:28 It feels so Pyphonic and natural.

55:30 And then Starlet, because I think it's just a beautiful foundation for the ASCII world.

55:37 And I'd really encourage people to look at the code to see the power of ASCII, how it

55:41 is this like turtles all the way down.

55:43 Everything's ASCII.

55:44 It is quite interesting.

55:45 So those would be my two shout outs.

55:48 Yeah.

55:48 Very cool.

55:48 You know, FastAPI is so popular now, but FastAPI is kind of like an opinionated view on top of

55:53 Starlette to a large degree.

55:55 Yeah.

55:55 FastAPI is great as well.

55:57 Yeah.

55:57 Well, I mean, it takes the two things you said, Pydantic and Starlet, and puts them together

56:01 in like a nice way, which I think is pretty neat.

56:03 Yeah.

56:03 It's got great taste.

56:05 Yeah, for sure.

56:06 I'd just like to say one thing really quick, and just thanks to everyone who's contributed

56:09 to Piccolo, because there's been people who've been contributing for several years by this

56:13 point and have put a lot of work in.

56:15 So yeah, just a shout out to anyone in the Piccolo community.

56:17 Yeah.

56:18 And, you know, Final Call to Action, people are interested in using this.

56:22 It's good to go.

56:23 It's ready for production, web apps, and all that kind of stuff.

56:26 I didn't really want to promote it before it was ready.

56:28 And I use it in production.

56:29 I have done for years.

56:29 And I am quite conservative about pushing stuff out there, unless I think it's, you know, solid.

56:34 It's got 100 unit tests.

56:35 You know, it's solid.

56:37 I'm not saying there's not some edge case I haven't discovered yet in some version of,

56:42 you know, Postgres or something.

56:43 But I use it in production every single day.

56:45 Well, congrats on building a really cool Pythonic ORM.

56:49 I really like the way that you put things together.

56:51 And yeah, it looks great.

56:53 It's got a lot of nice modern Python features.

56:56 And people should definitely check it out.

56:57 Cool.

56:58 Yeah.

56:58 Thanks a lot, Michael.

56:59 Yeah.

56:59 You bet.

56:59 See you later.

57:00 Yes.

57:00 Bye.

57:02 This has been another episode of Talk Python to Me.

57:04 Our guest on this episode was Daniel Townsend.

57:07 And it's been brought to you by Linode and us over at Talk Python Training.

57:10 And the transcripts are brought to you by Assembly AI.

57:13 Simplify your infrastructure and cut your cloud bills in half with Linode's Linux virtual machines.

57:18 Develop, deploy, and scale your modern applications faster and easier.

57:22 Visit talkpython.fm/Linode.

57:24 And click the create free account button to get started.

57:28 Transcripts for this and all of our episodes are brought to you by Assembly AI.

57:31 Do you need a great automatic speech-to-text API?

57:34 Get human-level accuracy in just a few lines of code.

57:36 Visit talkpython.fm/assembly AI.

57:39 Want to level up your Python?

57:41 We have one of the largest catalogs of Python video courses over at Talk Python.

57:45 Our content ranges from true beginners to deeply advanced topics like memory and async.

57:50 And best of all, there's not a subscription in sight.

57:53 Check it out for yourself at training.talkpython.fm.

57:56 Be sure to subscribe to the show.

57:58 Open your favorite podcast app and search for Python.

58:01 We should be right at the top.

58:02 You can also find the iTunes feed at /itunes, the Google Play feed at /play,

58:07 and the direct RSS feed at /rss on talkpython.fm.

58:11 We're live streaming most of our recordings these days.

58:15 If you want to be part of the show and have your comments featured on the air,

58:18 be sure to subscribe to our YouTube channel at talkpython.fm/youtube.

58:23 This is your host, Michael Kennedy.

58:25 Thanks so much for listening.

58:26 I really appreciate it.

58:27 Now get out there and write some Python code.

58:29 Thank you.

58:29 Bye.

58:30 Bye.

58:31 Bye.

58:32 Bye.

58:33 Bye.

58:34 Bye.

58:34 Bye.

58:35 Bye.

58:36 Bye.

58:36 Bye.

58:36 Bye.

58:36 Bye.

58:37 Bye.

58:37 Bye.

58:38 Bye.

58:38 Bye.

58:38 Bye.

58:38 Bye.

58:39 Bye.

58:39 Bye.

58:39 Bye.

58:40 Bye.

58:40 Bye.

58:41 Bye.

58:42 Bye.

58:42 Bye.

58:42 Bye.

58:43 Bye.

58:44 Bye.

58:44 Bye.

58:45 Bye.

58:46 Bye.

58:46 Bye.

58:47 Thank you.

58:49 Thank you.