#328: Piccolo: A fast, async ORM for Python (updated) Transcript
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.