SQLAlchemy is the most widely used ORM, Object Relational Mapper, for Python developers.
It's been around since February 2006, but we might just be in for the most significant
release since the first one, SQLAlchemy 2.0.
This version adds async and await support, new context manager-friendly features everywhere,
and a unified query syntax.
Mike Bayer is back to give us a glimpse of what's coming and why Python's database story
is just getting stronger.
This is Talk Python to Me, episode 344, recorded November 10th, 2021.
Welcome to Talk Python to Me, a weekly podcast on Python.
This is your host, Michael Kennedy.
Follow me on Twitter, where I'm @mkennedy, and keep up with the show and listen to past
episodes at talkpython.fm.
And follow the show on Twitter via at Talk Python.
We've started streaming most of our episodes live on YouTube.
Subscribe to our YouTube channel over at talkpython.fm/youtube to get notified about upcoming
shows and be part of that episode.
Mike, welcome back to Talk Python to Me.
It's been a little while.
Hi, how are you doing?
Hey, I'm doing really well.
When we last spoke, we did speak about SQLAlchemy, of course, but it was in April of 2015.
That's been a while.
So was that live version 1.1?
Something like that.
Yeah, yeah, yeah.
And that was episode five of the podcast that's been going for six years.
So really early days.
I appreciate you helping me kick off the show.
And can you believe it's still going?
That's crazy, huh?
Yeah.
Doing great.
I'm glad Python's popular enough that it can have its own podcast.
I'm pretty psyched about that.
I'm super psyched about that as well.
Maybe let's sort of start with that, actually.
Back then, Python was already popular, right?
2015, we'd already sort of hit that hockey stick growth curve that started around 2012.
But even so, does Python's popularity and growth and appeal surprise you these days?
No, it doesn't surprise me.
I'm pleased that it's growing in popularity.
I think the first thing that I saw about Python when I first got into it in the early 2000s
was it's really clear.
It's really unambiguous compared to everything else that I've used.
And I always wanted to be involved in a language that a quote-unquote regular people can get
involved with and people at a low level, high level can do things with it.
I didn't want to be focusing on a computer language that would only be very high level
math whizzes because I'm not a math person at all.
That's why I don't really do Haskell.
I wanted there to be because I worked in different jobs and I always would be, not always, but
often someone that was writing all the frameworks and the architectures and then all the other
people were using my frameworks.
But a lot of those people were not at the level that I was.
And I wanted to make sure that the stuff that I did was approachable.
Python's very approachable.
And the fact that now that the academic world and the media news world is getting very data
oriented, surprise, they're all using Python.
Yeah, yeah.
Surprise, surprise.
Yeah.
I missed exactly what I would have expected.
And of course, it might not have gone that way, but it was a pretty good bet.
So it's cool that there's a data science profession now and Python is at the center of it.
So I think that's really great.
It makes a lot of sense that it's at the center of it.
I do think it has this special appeal to people.
And I think it's made up of a couple of parts.
One is it's, you can be very effective with a partial understanding of Python.
Like you could not even understand what a class is and you could still do meaningful stuff,
right?
Which a lot of languages don't have that feature.
It has the packages, right?
All the, I haven't even checked.
Let me check it out.
How many packages are on PyPI these days?
338,000.
That's a few things to just grab and, you know, build with like Lego block style, some of which
you've contributed to up there.
So that's pretty awesome.
Some of the popular ones.
Yeah.
I think it's, I think it's fantastic.
So good to see people continuing to embrace it.
And you mentioned making it more accessible, right?
Like there's different things of accessibility.
There's, well, I can write four loops and do a list comprehension.
And that's pretty easy to understand.
There's another, like, I need to talk to a Postgres cluster and make it do things, right?
It doesn't matter how simple Python is.
When you talk to external systems, you to some degree take on the complexity of that external
system.
Right.
And I do think ORMs and in the NoSQL world, ODMs, if you will, really democratize that as
well.
You can write the code in the language you already know, and it does the database stuff,
including the slight mismatch of language features in SQL, like at parameter name versus
question mark type of variations, right?
Yeah.
What's interesting, that particular variation is not even necessary.
That's a quirk of the Python DB API that they decided to have different parameter styles.
So yeah, we wanted to make it so you wouldn't have to worry about that.
Yes.
As far as democratizing, you know, SQLAlchemy was always aiming to democratize as much as
it could to make things that are trivial and you shouldn't have to worry about like question
merge versus colon name.
You don't have to worry about that stuff.
But at the same time, I always came from all these different database shops where we had
to use every feature possible.
Yeah.
And it's always about exposing the database, the SQL.
And SQLAlchemy remains pretty different than most tools.
Any tool I've seen in Python, more or less, and probably tools in other languages that is
not trying to create this abstracted way.
You can't really know that there's some vaguely relational object store thing.
We really want you to be in SQL and you just happen to be able to write it in terms of Python
constructs where you can also write SQL strings.
And we're going to try to get that to have as much flexibility as possible.
There's a couple of interesting philosophies around SQLAlchemy or philosophies of SQLAlchemy
that you've imbued upon it.
And one of them is not to hide the database, right?
Because you often end up in this leak abstraction where it's like, oh, you can just forget there's
no database.
Oh, wait, is it too slow?
Well, now you're into some weird world where it's not a great fit, right?
So maybe we could talk a bit about the philosophies a bit before we get into what's new and where
things are going.
Yeah.
The basic philosophy has always been, and I think some years into it, I read some articles
that gave me some better terminology.
One of automation of working with a SQL database.
Like if you're going to write an application that talks to a database and you're going to
have 20 tables and say you're not using any ORM, you're just going to write
where you get the rush SQL.
You're going to find yourself writing the same insert statement, typing it out over and
over again.
Like I know that my table has 10 columns.
I'm going to type this insert statement.
That's what we call boilerplate.
Any program that anyone writes using nothing at all, they're going to write some function
to generate that insert statement, right?
Like people usually talk about select statements as like the ones they want to have more control
over.
But there's just the boring ones.
There's updates, inserts, and deletes, which are really boring.
There's all the DDL for creating tables and stuff.
That's very redundant.
Nobody needs to, you know, if you want to learn how to do that stuff, you should learn how to
do that stuff.
But if you have a hundred classes, you don't want to type in a hundred create table statements.
You don't want to have to do that.
It should be automated.
So the idea is that when you're working in this automated environment, you still know
everything that's what a create table statement is, what an insert is, you know what crud somewhat
is.
SQL, you would know it ideally, not to say, but ideally you would know how to write this
whole program without using anything.
Like you would know how to use raw SQL.
Right.
I would say a, probably a good rule of thumb for I've studied enough SQL.
I feel like I'm, I'm not hiding too much from myself by using an ORM would be if you could
get the ORM to print out its statement.
So in SQLAlchemy on the engine, if you set echo equals true, for example, and you know what
it does when you read the statements, you're like, okay, I'm not sure I would have exactly
seen to write the join that way, but okay, that makes sense.
I see why it's doing, I see what this update statement, these parameters mean.
Okay.
Like now let's just, let's be productive.
Right.
What do you think about it as a rule of thumb?
Echo equals true was when I first wrote SQLAlchemy, whenever I did for the first two weeks,
echo equals true was right there.
That was like the very first thing, like you will see the SQL.
Cause I had come from using, I think I'd probably use Hibernate for a while for Java.
Now it's 20 years later or 25, Hibernate I'm sure is very different, but at that time,
I didn't know how to see what the heck it was sending to the database.
I had no idea.
I'm sure there was a way to do it, but it wasn't like obvious.
It was like, you have to set up lockers and this and that and Java, this and Java, that.
And the idea with Hibernate and other, it was like, you shouldn't have to know that.
We do the SQL for you.
Why should you know that?
And yeah.
Yeah.
Use us correctly and we'll solve the problem.
Right.
I want to really one-to-one thing where you're writing this Python select dot where dot
whatever.
And it's like what you can one-to-one, if I had more videography skills and also a lot of
time, I'd write like, I'd make some kind of cartoon that shows boom where this, how the,
how it lines up at the statement.
Absolutely.
It was a, it's meant to not, it's not meant to hide anything.
It's meant to automate.
So if you had, it was just this term, the idea of a soda bottle company, like a soda company,
like you're selling soda, which not, soda's not good for you.
But if you had to bottle 4,000 bottles of soda, you would use a machine to do it.
It's not to say that you don't know how to pour soda into a bottle yourself, but you
need to scale it up.
Yeah.
It's about scaling up something that is very repetitive to be typing by hand.
And be consistent and repetitive and whatnot.
Absolutely.
Yep.
A couple of comments from the audience real quick.
I just want to throw out there.
D Varazo says, hello from.
Danielle.
Yeah.
From Psycho PG.
Thank you for your kind wrapping.
And Jay Lee is just cheerleading for Python, which is awesome.
Yay for PyTox.
Awesome.
Hello, everyone out there.
So yeah, I think this is really a powerful thing to think about.
Just the reproducibility.
And also in terms of linking into the tooling, I think one thing people often miss about the
advantages.
Because sometimes you'll hear people say, you should never use an RM.
It's hiding too much from you.
Yeah.
Sure.
Yeah.
Sure.
Go.
I'm going to go do something.
You keep working on your strings.
So one of the things I can see that sometimes can easily get missed is refactoring tools.
Yeah.
Error checking.
Right.
If you write some kind of select statement on a class and you want to change that field
or that column, you change the field, which obviously maps over to the column.
But that also changes your entire code.
If you're using a proper editor that understands refactoring, like VS Code or PyCharm or something
like that, right?
Yeah.
A big thing with the refactoring and one of the things that we had to adapt to as it came
was PEP44, which is, I call it mypy, but it's not.
mypy is a tool that checks Python annotations.
And we call it the mypy thing.
It's really PEP44, which is that new thing where they're trying to have a kind of a layer
of typing, like a strongly typed, static typed, sorry, statically typed layer on top of your
Python script.
So if you're a library of Python, you have to work with the system now because people expect
it and the IDEs, I use VS Code now with, so that I keep forgetting the name of the engine,
but that's.
PyLance.
You know what it is?
Yeah, PyLance.
I can never remember that name, PyLance.
PyLance.
Think of a Lance.
PyLance.
PyLance uses the annotations a lot.
And I actually am seeing where it works and where it doesn't work.
And I think the refactoring tools are greatly improved by the fact that there is this concept
of static type annotations.
I think there's a lot of shortcomings in PEP44.
There's a lot of things that's like, eh, it's not great.
I'm not 100% optimistic on it, but I definitely, it's way better than nothing.
And there's some, also some features that are coming that are in PEPs that have not been
implemented.
This thing called variadic types that would allow us to actually be able to type your result
set coming back.
If you give it a select, we'll know the types in your result row.
I see.
Is that something like a generic or template type?
It's a generic that works like the tuple type.
Because you might notice in PEP44, you can't make your own tuple type.
The typing that's applied to tuple is actually, if you look at the mypy source, it's hard coded.
If equals tuple, then all these new special things.
So things like that, they need to fix.
Yeah, because rows from a database are essentially tuples.
So that's how we want to type them.
I do have a question.
Since we're talking about the typing, I pulled up this super simple example here off of the
SQLAlchemy documentation.
It doesn't really matter.
It's just a class that drives from declare to base, has a table name, and then it has the
stuff that people probably know from many ORMs.
These descriptors, right?
So ID equals a column, which is of type integer, primary key equals true, name equals column, which
is a string and so on.
I'm going to tell you what I've been doing with this stuff lately.
And you tell me whether this is a good idea or a bad idea or whatever.
So what I've been doing is I've been writing this code as like this name one.
I would say name colon string equals column string.
So the Python code believes it's the type that's backed by the database, the column type.
So name is a string, ID colon, and even though it's really set to a descriptor of column integer
primary key equals true.
Have you seen this?
Is this a good idea?
Should I be doing this?
Yes.
I have done a lot of work on this concept.
And what you're doing is not incorrect, but there's a lot of complexities to it that require
a little more going on.
Right.
And to be clear, I'm not doing this for mypy.
I'm doing this just so my editor is more approachable to me on autocomplete.
Yes.
For the editor, I don't have the link handy.
I'd have to go searching on my computer.
So first of all, we do have a mypy plugin where you are able to use annotations like name colon
string, and it will automatically work them into the correct kind of thing to be recognized
also at the query level.
It doesn't work with PyLance.
And I have a new approach that I worked out with some other people for PyLance type stuff
where we won't need a plugin, where you won't be able to make...
We're not totally sure how we want to do it.
The point is that you would say name colon.
You wouldn't say stir.
You would use this other construct called mapping or maps rather and maps.
And then the map type is stir.
The reason you do that is because if you use the user class in a query, not as an instance,
it also has behaviors at the class level.
Right.
Like .desk for a descending source.
Yeah.
Like that kind of stuff.
Something like that, right?
Right.
So I'm trying to work out the best way to do this where you're not typing the same...
You're not repeating yourself because right now it's a little bit squirrely.
The class works completely in PyLance.
Everything that's expected both at the class level and at the instance level will work out.
It probably requires that when you do the declarative class, you would use a slightly different API.
The current proposal is instead of using uppercase column, it's this new thing called m.column.
We can always change the names and how it looks.
One of the flaws with that declarative model you see there is that when I came up with that
declarative idea many years ago, those column objects are not Python descriptor compatible at all.
They actually get replaced when you map the class.
It's totally...
Okay.
...like a seal.
It's not that clean.
So I have ideas to make declarative cleaner and do more of what it's supposed to do without breaking the rules.
That's the thing about Python is that as the years have gone by, everyone, even newcomers, were all much more into not into being clean about code and being more verbose.
And typing is more verbose and asyncio is more verbose.
When we were doing Python in 06, 07, everything was like, done.
Just magic object.
Boom.
Magic object.
Nobody wanted to type anything, which was bad.
But we did totally because it was scripting language.
But nowadays, people are way more tolerant of more verbosity, more clarity.
And I'm trying to keep SQLAlchemy and my team, we're trying to keep it going along with that.
So the PEP 44 thing, the AsyncIO stuff, that's my knowledge dump for the typing thing.
That's fantastic.
That's a great insight.
It's a work in progress.
Do you think that people are more willing to accept a little more structure in the code now because the tooling is more there to help you write it?
Like I write my maybe C tab.
I don't write column of whatever, right?
Right.
Maybe it's the tooling because nowadays people use Python data classes and they want to use Python.
We have some support for Python data classes now.
And the syntaxes that they want me to implement, which we've done, are pretty verbose.
Because they want to have a data class where you have all your data class fields.
And then within the field, they want to have the mapping information.
And people are like, this is awesome.
And I'm like, great.
Because it's actually more verbose than I would prefer.
But people are way more tolerant of that.
So yeah, I guess their IDEs are spitting things down.
Or just people, the values are different now.
Kids these days.
This portion of Talk Python to Me is brought to you by TopTal.
Are you looking to hire a developer to work on your latest project?
Do you need some help with rounding out that app you just can't seem to get finished?
Maybe you're even looking to do a little consulting work of your own.
You should give TopTal a try.
You may know that we have mobile apps for our courses over at Talk Python on iOS and Android.
I actually used TopTal to hire a solid developer at a fair rate to help create those mobile apps.
It was a great experience and I can totally recommend working with them.
I met with a specialist who helped figure out my goals and technical skills that were required for the project.
Then they did all the work to find just the right person.
I had short interviews with two folks.
I hired the second one and we released our apps just two months later.
If you'd like to do something similar, please visit talkpython.fm/TopTal and click that hire top talent button.
It really helps support the show.
Is the goal with the data classes to try to have a more pure entity model that doesn't have column information and key index information?
There's some other layer that puts those together?
I haven't really played with that aspect.
Data classes.
I haven't really had the Kool-Aid with the data classes yet.
I think the idea is that it's this very clean data encapsulating object that has kind of prefab constructors and Reaper and validation.
It does have that kind of stuff.
That's right.
Comparison.
Yeah.
I might be thinking that Pydantic takes a little further.
Some murky stuff between data classes, Pydantic and SQLAlchemy.
Yeah.
They blend together in my mind a little as well.
It's all weird.
Yeah.
And there's a new product called SQL Model that seems to be pretty popular in that regard.
Yeah.
Built on SQLAlchemy.
Yeah.
Which is great.
It's totally awesome.
It's actually a more opinionated overlay of the ORM, which is also using Pydantic.
So that's good.
Yeah.
The thing you've got on the screen there is another way of, yeah, that's called a...
Yeah.
So it's a data class, but it also has the mapper registry.mapped on it, which is interesting.
It has some of its table metadata has not just the table name, but all the column information as well.
It's interesting.
It does have the same type declaration that I was trying to impose upon the traditional SQLAlchemy models.
Yeah.
That's one form of the data class model.
If you scroll down, the more inline embedded one is there.
Let me see if that's what it is.
Yeah.
That's the one that's even crazier, which the data class fields.
So you're fully a data class as far as what your IDE sees.
But then you have the SQLAlchemy mapping stuff inside.
So all of this is...
Well, time will tell which approach becomes the popular one.
Yeah.
Or people just use SQL model.
Because the people that like data classes tend to be using SQL model at Pydantic anyway.
Right.
Maybe they'll just...
They'll be there.
I don't really know.
Yeah.
I'm going to talk to Sebastian Ramirez maybe in December or January about SQL model.
Yeah.
SQL model is interesting because it basically takes Pydantic models and SQLAlchemy and merges
them together.
And Sebastian has a good feel for what a nice API looks like.
So I'm optimistic for this.
I haven't done anything with it, but it does look pretty neat.
What he had to do was he had to modify Pydantic's base class.
Because when I first looked, man, maybe like in this last past year about how Pydantic do
SQLAlchemy without having two separate models, I was like, okay, this base class has got a
whole thing, a lot of things going on that don't work with Python descriptors.
So it'd have to change.
And I think Sebastian did that, basically.
Okay.
Went in and changed how the init works so that it's compatible.
Yeah.
More power to him.
Yeah, more power to him.
Awesome.
I have enough to work on.
I can't.
This is like too much for me to also work on Pydantic.
So I'm glad someone else did it.
Yeah, absolutely.
Absolutely.
One more philosophy thing.
That is not it.
This is the one.
One more philosophy thing that I want to ask about or let you just speak to, because this
is a key element of how SQLAlchemy works.
There's other ORMs that work this way.
And then there's also this one, I think it's called Django.
People might have used the Django ORM before.
No.
That one is more of a traditional Ruby on Rails active record where I have a thing and I call
save on it.
Sure.
You went with this thing called unit of work, which is a little more transactional.
Like I'm going to do a bunch of stuff and then all together it happens.
Yeah.
Maybe just speak to that real quick before we talk about it to you.
When I was going to do SQLAlchemy, the main thing that I had worked on a lot at work
was a lot of fancy selects.
I hadn't done much work with the persistence side, but I read this book by Martin Fowler
called Patterns and Enterprise Architecture.
I read it as well.
It was quite an interesting book back then.
Yeah.
And I had actually never, even though I had used Hibernate, I had never heard of unit of
work.
And I was like, wow, that looks cool.
I'm going to write that.
Because one of the philosophies was like, insert update deletes are really boring.
And even saying update.save is really boring.
You're going to, because I, we worked, I was working on a content management system for Major
League Baseball.
And when you work with CMS, you have a lot of tree-based hierarchical structures with lots
of self-referential stuff.
When you persist self-referential structures in the database with auto-incommented primary
keys, you've got to get one row, get the primary key back, put it there, put it in there.
Everything's got to be done in a very specific order.
Yeah.
Like I tried to call save on this, but that didn't work because I needed to call save first
there.
And it's like, ah, this is crazy, right?
So it's like, why would I want to do, why?
I shouldn't have to do that either.
I should just have this thing that like, here's everything in a transaction, just push it.
And it, you know, took a very long time to get it right.
It has a couple of little chinks in the armor, a couple of cases that you might have to drop
it to call it flush explicitly, but we never get bugs with the unit of work stuff.
If you look at unitofwork.py, it hasn't changed in years.
Yeah.
It took a long time in the beginning.
And then I wrote it a few times and we thought it was really bad early on.
It was very hard to get it right.
Yeah.
A lot of edge cases, right?
I can imagine.
People can do any sequence of things and then you have to make it right.
Yeah.
Mostly that we support these joint table inheritance models were very hard for me to get my head
around how to persist that.
And then just took a long, it just took a long time because it grew organically.
You know, you're coding.
You're not, you're doing.
I'm glad you wrote that instead of me.
So thank you.
Yeah.
I wouldn't be able to do it today.
You got to be deep in it, right?
Yeah.
Comment from Brandon Brenner.
Hey, Brandon.
Because I use SQL model in a demo project and it was so easy to use.
Very nice.
All right.
So I think probably the big news over here is 2.0 or as you like to put it at your recent
talk, the 1.4 inning.
The 1.4 inning.
Very early stages are on the site.
In the library tab is a development thing where development docs are.
Nice.
And the current release is a 1.4 or whatever.
1.4.
It has a lot of these features in there already, right?
Yes.
All of the, just about everything in SQL 2.0 internally is available in 1.4.
We did the whole of the internals first and added all the stuff so that 1.4 could be a
transitional release so that all this, all the behind the scenes would be getting tested
and people use the new APIs and transition over.
Is 2.0 going to drop some of the older APIs hard?
Many.
Yeah.
Okay.
Yeah.
So people who have looked, 1.4 has this big migration thing where if you're
on 1.3, which is pretty common, you can go to 1.4 pretty easily without much problems.
But right.
When you're on this thing, we have this whole thing that is inspired by the Python 2.3 process
and at the same time tries to not make some of the, what I thought were mistakes of the
Python 2.3 process.
So what this is based on is that 1.4 has a environment variable you can turn on in your
console or whatever.
A called SQL can be worn 2.0, something like that.
And when you turn that on, you're in now, you're in now in warnings mode.
You will get all kinds of warnings about all kinds of APIs that either have changed or are
going away or use this one or that one.
So a lot has happened.
But at the same time, the reason that's maybe not as scary as you might think is that all
the APIs that are being deprecated are APIs that I've already taken out of the docs years
ago.
Like the APIs that I've been telling people for years, don't do that anymore.
We don't, we're not going to do that.
So it's a lot of old stuff that is not what we've featured in the docs.
Like basically the things we're taking on 2.0 are things that have annoyed me for years.
And there's a few changes in 2.0 that are a little more boom.
Like we changed how the engine is auto begin instead of auto commit, but it's 2.0 is basically
going to be, I think, better.
It's in line with this whole notion of people are, are appreciate more explicitness when things
are clear.
So 2.0 is going to remove a lot of implicit stuff, a lot of five ways to do the same thing
patterns.
It's going to narrow you down into one or two possible patterns for things.
Yeah.
That hasn't been a thing with SQLAlchemy, right?
There's a lot of ways to accomplish stuff.
Yeah.
Because I came from Perl.
I can't, I'm sorry.
I was doing Perl.
It's still the case that there's more than one way to do that.
You can't really get away from that totally.
Well, there's often there's the, I want the super simple, easy way, just call an ORM method
and then, oh, we got to rewrite the select statement to use the store procedure type.
There's usually a backdoor type of thing in a database world that has to happen at some
point, right?
Yeah.
Store procedure is a pretty dramatic example.
But yeah, these days the way is that when you write your code, it's going to be clear
where the SQL is being executed, where the IO happens.
In many ways, it actually was inspired by AsyncIO, where I had been tweeting a lot with people
into AsyncIO, which I was a little skeptical of it.
At the same time, what people appreciate about it is that it can show, you're very aware of
where the actual message to the database is happening.
This is where the message gets sent down.
Here's where it comes back.
When you say await, now.
Yeah.
Now it's happening.
Now, I think that's a little heavy handed, but at the same time, I appreciated that notion.
So with 2.0, I tried to work with that idea that we want to make it very clear.
Here's where you're making a SQL save it.
Here's where it's executing.
Here's where the results are.
And then also, here's where the transaction is.
Those are actually the biggest changes that you're going to know that you're in a transaction.
You're going to know when your transaction ended.
You're going to know if you committed it or if it just rolled back.
Yeah.
And you'll know.
And the code is going to be a little more verbose than SQL Alchemist here at .1.
But people are already writing code that way.
Because now that I've seen many years of people writing code, you know.
They're like, I really want to know when this happens.
So I'm going to be super explicit about it.
People don't want, they don't want excess bells and switches that don't seem to have any purpose.
But if your code is clear, basically being able to read the code and the intent is as clear as you can get it.
Without, this just does that.
We just know it does that.
That's where I'm trying to go.
And I'm somewhat freed by the fact that I know people are more tolerant of step A, step B, step C.
Sure.
A little less magic, yeah.
Yeah.
People are okay with it.
Now, they weren't okay with that tiptoe 12 years ago.
People are like, that's too much typing.
Sure.
So it's good.
I want to talk about this migration thing, like what a person does to go from a 1.3 application onward.
But I think that's getting a little bit ahead of ourselves.
What about we talk a little bit about, you know, what is, what are the major features coming in 2.0?
First of all, obviously, we're finally to Python 3 only.
Yeah.
What is it?
3.6?
That you're, has a minimum version?
We're going to make it 3.7.
3.7.
Okay.
3.7.
Yeah.
3.6 is EOL.
So.
Yeah.
In a case of a month or two.
Yeah.
No point to do that.
Yeah.
Just today, or I didn't merge it yet, but like we're ready to merge this gigantic Garrett
review that's going to take all the Unicode conversion crap out.
That was all Python 2.
Python 3 having native Unicode is tremendous for us because all the DB APIs do the Unicode now.
It was the new API.
So yeah, we, we have this new school caching system that I've talked about a bunch in the,
some of the talks I did recently.
Yeah.
Tell us about that a little bit.
Yeah.
That's in 1.4.
Is that like compiling the SQL statements and then like caching those results or what are
we talking about here?
It caches the, so when you run a SQLAlchemy statement, we have to take your Python code and
make a string out of it, which is the string we sent to the database.
We also have to look at that thing and figure out what kinds of results are we going to get
back?
Are we going to get strings and dates, injures and floats?
And then for those strings and states and floats and whatever, do we have to do any processing
on the rows?
We want to have that all set up because if you get 10,000 rows back, you want to make all
those decisions up front.
So that when you run 10,000 times, everything is as fast as possible.
Everything is already figured out.
When you do the ORM, that whole process becomes like way more complicated.
There's way more going on as eager loaders and there's more fancy kinds of types.
We might be taking columns and putting them into an object.
All of that stuff is time consuming.
And it all now in 1.4 lives behind what I call the cache wall, which means when the
caching is working, which it seems to work, it's only going to do that stuff once for a
statement that's been cached.
So when we run your statement, we're going to do a process that's still a little pricey,
which is to get a cache key from what you typed.
You've got to determine some sort of unique hash type of thing out of it.
It's a unique, it's actually a gigantic tuple right now.
And maybe it'll be a hash in some other release.
But for now, it's a giant tuple because Python's pretty good at that.
It's cheaper than running, than doing the whole compilation.
And then we get the whole compilation from a cache if it's there.
And it's LRU cache.
So if it's not, we make it.
And it allows us to put more bells and whistles into the compiler.
We have this thing where it will now detect if your statement will produce Cartesian product,
meaning you have joins between tables that are not linked together.
And we have all kinds of other fun things the ORM can do that take a little more time to compile.
But now they're behind a cache.
So the performance is not really impacted too much.
And so that's exciting stuff.
And we're going to be looking at 2.0 to start using Cython to speed this up even more.
Oh, fantastic.
Yeah, it has C-Extensions that we wrote as C code years ago.
We're going to migrate those to Cython so that we can more quickly add new Cythonized code
for different sections to speed up to how those cache keys get built.
Yeah, it's one thing to say we're going to write some big chunk in a C layer.
It's another to say just that loop.
Could that loop be C, right?
And with Cython, you can kind of just do that, right?
You can do that.
You could do it quicker and without having to worry about all the memory reference counters.
C code in Python is pretty tedious.
I'm not really a spectacular C programmer anyway.
Well, and also a lot of people who might want to come contribute
are probably not C developers.
They're Python developers first.
Yeah.
Yeah, Cython is pretty cool.
Cython is pretty cool.
Because you can really, if you just do Python, you can do it.
It's just like a strict Python.
Yeah.
It's pretty neat.
Yeah, it is.
And we found that some of the Cythonized versions of the functions
are actually faster than RC code.
Really?
Which seems strange.
That's pretty strange.
Yeah, faster.
Because Cython, the people that did Cython optimized the crap out of it.
They know all these very esoteric Python C API things that, like literally, we have RC code
is not a lot of codes.
That's like a few hundred lines.
We'll call it a tuple get item or whatever it's called.
Cython has some, if we do this, then it's faster.
And it actually is.
I can't give you detail on, because I don't know, but like we did benching and Cython
was actually faster than RC code.
Yeah.
I remember way, way back when hearing, you know, people used to say, well, if you want
it to be fast, you have to write an assembler.
And then you can use C if you're not, if it's not going to be that fast, you can just use C,
right?
That's a different kind of fast.
Right.
Well, what I was.
To be clear.
What turned, of course, but to be, well, the interesting thing to me was there was this
switch when the compilers got good enough that you probably were slower writing
assembly for most people.
Right.
And I feel like the thing you're describing here is kind of like that transformation like
this.
The compiler understands the whole system better than if you were to try to do it yourself
at the same level.
They found.
Yeah.
They've apparently.
Yeah.
I don't know the specifics, but in some cases they've had figured out ways to make it even
faster.
So there's no reason to not use Cython.
Yeah.
You think of what people are doing with Cython, right?
Like they're running on supercomputers doing huge calculations that take.
So they have a strong motivation to make it a little bit faster here and there.
Right.
Yeah.
Look, here's the thing about Python.
Python is a high level scripting language.
It's interpreted always.
So it's already not.
I don't want to say that, but if you're really writing high frequency training software, you
probably want to use something like Rust or something.
Python's not really that.
It's a lot of things, but it's not high frequency training speed demon type that it's just it's
right.
You can make it do that.
But once you start wanting it to be sub millisecond, you might want to start.
But other than that, you're probably fine.
Yeah.
It's not going to.
I might be wrong.
It's not going to control the rockets five, but I guess maybe it does.
I don't know.
Yeah.
It's have to be that.
It's so useful for so much stuff that.
Yeah, absolutely.
Okay.
So that's really cool.
So you're thinking about switching some of your C code over to Cython, which is really
interesting.
And I didn't didn't see that one coming.
That's awesome.
The cache layer is cool.
So that way, if I call a function and in that function, I do a query statement in the ORM.
The first time that's expensive.
The second time it's not free, but less expensive.
Right.
Cheap ish.
Much less expensive.
Much less.
Okay.
Yeah.
We haven't sped up yet.
And I've been speaking this for many years is when you fetch rows from the ORM to create
the Python objects that are your classes, that's still more expensive than we'd like
it to be.
Yeah.
That's super expensive in terms in ORMs and ODMs.
If you select 10,000 rows out of a database, it's probably the serialization or deserialization.
That's the cost, right?
Completely.
Okay.
And we've made, I've made the loading way.
I spent years and years making it way faster by doing that whole thing where we pre-calculate
everything up front.
Like every, every like loader, we're not going to like every time you get a row, it's
like zip, zip, zip, zip, zip.
We have these little callables set up.
But still just to make the object, just to make a new class in Python is expensive.
It's pricey.
So still a brick of progress.
But yeah, the caching will not speed that up.
It'll speed up the overhead per query.
Which is still like every, every area you can speed it up is great.
Danielle out there says, Cython is also a great choice to forget about ref count and
Psycho PG2.
It took a while to find all the smaller leaks.
Yeah.
Ref counts are tough.
Yeah.
I'm pretty proud of myself that I did figure them out to some degree.
They're not as hard as Malik and Free, but the ref, it's an esoteric set of rules to
ref counts.
While we're down in the internals, let me ask you this.
If you've thought about this or experimented with this, any.
One thing as I dug into Python's memory model that I found to be really interesting is when
you create, you know, you have GC, the GC module, you can say GC get thresholds or set thresholds.
And the thresholds tell you what will trigger a garbage collection, not reference counting,
but a cycle detection type of thing.
Right.
Yeah.
The defaults, at least last I'd looked, I haven't looked in 3.10, but in 3.9, it was
700, 10, 10, which means 700 allocations.
And then for every 10 Gen 0 collections, there's a Gen 1 and every 10 Gen 1, there's a Gen 2
collection.
That 700 means if you allocate more, 700 more classes or dictionaries or tuples or whatever
than have been freed, a GC will run.
Yeah.
So if you select 10,000 rows out of a database, how many GCs are running?
I mean, that's a lot, right?
That's like 14 GCs.
If you're taking the 10,000 rows of putting them in a gigantic list, then it's not GCing much
at all because you're putting it all in a gigantic buffer.
You're going to have the size of your process growth.
But the, I mean, you're not freeing any, but the, and you're not ref count freeing any either,
but you're, you're allocating 700 more.
Yeah.
I think that triggers, I think that might trigger 14 GCs, unless I'm understanding it wrong.
You would know, I don't know anything about, I wouldn't know.
I know, all I know about GC is that it's going to happen.
Yeah.
I mean, the reference counting stuff is super straightforward, but the GC side, so I've always thought
about this around the database.
Even if you're just getting dictionaries, not ORM classes back.
Yeah.
If you're selecting a whole bunch of stuff, not only are you doing that serialization layer,
you're also incurring a bunch of GC because it's trying to preemptively run around and look
for cycles that might've been forming.
One thing you can do is make the code so that you don't have as many cycles.
And we actually do that.
We've had people post issues related to this.
So I said, I don't know much about GC.
I do know that if you can reduce reference cycles, you will have less of these asynchronous
GC runs happening.
So we had someone specifically came to me with a whole lot of use cases where he showed when
you run this little code, like all these GCs would happen.
And we went in there and got rid of a lot of cycles.
So we actually have a test suite in test slash AA profiling test man usage called cycle test,
something like that.
Okay.
And they're all these little whiz bangs that run, make a session and close it, do this,
then that, do this and that.
And then it will runs it in a harness that actually counting what GC is doing.
And it's asserting that there's only five GC calls and not 20.
Right.
Yeah.
That's fantastic.
We have a lot of that happening.
Yeah.
Interesting.
Yeah.
It's cool that you're, you're thinking about it.
I mean, on one hand, this is an external thing.
So you can control your data structures, but you can't totally control what people set
their GC to do.
But anyway, it's just something that I always think about when, when you're creating lots
of these ORM things, because a lot of the signals to the GC are like, oh, I got to get
busy.
But yeah, you're not even like, that's a single call to SQLAlchemy and I'm just waiting.
There's nothing else I can do to get out of the way.
You know what I mean?
We've just been around for so many years and people have come to us.
So we have all kinds of man usage and C profile stuff at our test suite to make sure this
does the call care of Stoke Grove here to make sure we don't do too many GCs.
We try to work on that as much as we can.
We have a new course over at Talk Python, HTMX plus Flask, modern Python web apps hold the
JavaScript.
HTMX is one of the hottest properties in web development today.
And for good reason.
You might even remember all the stuff we talked about with Carson Gross back on episode 321.
HTMX, along with the libraries and techniques we introduced in our new course, will have you
writing the best Python web apps you've ever written?
Clean, fast, and interactive, all without that front-end overhead.
If you're a Python web developer that has wanted to build more dynamic, interactive apps, but
don't want to or can't write a significant portion of your app in rich front-end JavaScript frameworks,
you'll absolutely love HTMX.
Check it out over at talkpython.fm/HTMX or just click the link in your podcast player show notes.
I would say one of the things that makes me more excited than most of the other stuff here
is probably the async support that you have going on.
You want to talk a bit about that?
Yeah.
So when I was doing 2.0, I thought I wanted to be compatible with how async.io works because
I figured I would eventually do an async.io API for it.
But then I suddenly had this idea, why don't we just do the async.io thing?
And we do it doing this, we use this library called Greenlit.
The way the async.io works is that SQLAlchemy has async blocking API.
Yeah.
We call dbapi execute, it blocks, it comes back.
Usually the way you want to make that kind of thing work in an async non-blocking context
is you have to throw all the stuff into a thread pool.
You probably don't want to be in the wiki.
This is more old stuff.
Okay.
Whoops.
Yeah.
Like that one.
Click an async ORM at the top.
There we go.
This is the one I was looking for.
That's the most basic.
This is the one I wanted to show people.
Yes.
Not the session one, but the ORM.
Yeah.
This is ORM.
So what's important here is that the notion that we, 2.0 style has the thing where there's
a very specific place that execute happens.
Because with the query object that SQLAlchemy session.query, you could say query.all, query.one,
query.first.
I didn't want to have all this await this, await that, await everything.
With the new API, it's just, which is really the old API.
It's await execute.
And then you get your result back and then the result is buffered.
And the way this works that changed the whole asyncio equation for SQLAlchemy is that this
is not a rewrite of anything.
This is a layer on top of the completely blocking API stuff.
And it does not use threads.
It uses Greenlit.
Right.
When I went to look at what Greenlit actually does, Greenlit is compared to a thread, but
it's like a thread.
But when you see what it is, it doesn't really feel like a thread because it's like, it's
a little thing with code that you can context switch somewhere else.
More like, it's more like generators than it is like threads.
Like the weirdness of generators skipping around.
Yeah.
Exactly.
So the way Python asyncio has that await keyword, the await is your context switch in asyncio.
Now, I wanted the internals of SQLAlchemy to work in this context switching way, but to
integrate with await.
So we basically made a Greenlit wrapper that emulates await without actually using await.
So you're able to take SQLAlchemy's blocking internals, which ultimately go out to, when
you use SQLAlchemy asyncio, if you're going to use a DB API, actually it's not DB, it's
a database driver, that is also asyncio.
Currently, here goes the goal.
Async PG, AO SQLite, there's async my, and also there's one, oh, I can't remember the name
of it.
Wait, I can't remember the name, trying to get Danielle to, Psycho PG3.
I was teasing Danielle because he's on there.
There's going to be a Psycho PG3 that we're also going to support that does asyncio for
Postgres.
So you have asyncio driver, SQLAlchemy then adapts, it's the await calls into the synchronous
thing using the Greenlit thing.
And then on the outside, where you see on the screen there, is you have asyncio stuff.
And when I actually talked to some of the people that were involved with asyncio in Python,
they explained to me, you could do it the other way too.
You can have a synchronous API that calls into an async middle that goes back out to a
sync API.
And that's actually easier to do.
But since SQLAlchemy was already written in sync blocking style, we kept it that way.
Right.
It's a lot easier to wrap an async shell on something that's 15 years old and polished.
Right.
Then to say, we're going to do the inside all over again.
Yeah.
And the thing with async is that there were some SQLAlchemy based things on GitHub and
whatever that were basically taking our engine and connection, rewriting them as totally brand
new asyncio things.
Yeah.
And I always thought that's not, you can't maintain that.
It's protecting our code, taking about 30% of what it actually does and putting it up.
And I was like, that can't work.
There's got to be a way to get green.
Cause I had used G events and event lit a lot.
And I'm like, okay, I know if I'm going to do this.
I know it can context switch without using any way.
I know it can do it.
I just had to go in agreement and read the docs.
And I did that.
Yeah.
That's really cool.
I'm super excited to see this because it opens up using SQLAlchemy with some of the other
frameworks that really leverage it.
For example, FastAPI, but there's plenty of others as well.
Right.
Yeah.
Another cool thing about asyncio was that when I did the SQLAlchemy 1.4, which introduces
this new query ORM interface, but keeps the old one, which is not going to go away.
Cause there's just too much code in the old query interface.
But for asyncio, it's totally brand new greenfield development.
So I said with async, you've got to use our new API that automatically got a lot of people
to be testing the new way of working and got us to get people.
Cause the one thing that makes libraries really good is when a lot of people use them and finds
problems with them and add use cases and the library becomes mature.
So the asyncio thing has been enormously helpful to bring a lot of people to SQLAlchemy
who would not have been there.
A lot of people who were probably going to leave SQLAlchemy stayed.
Yeah.
And people using the new 2.0 thing.
SQL model probably wouldn't have been based on SQLAlchemy.
Not at all.
Yeah.
Yeah, exactly.
Because obviously, Sebastian needs that to integrate with FastAPI.
And the async is a core element there.
Audience, Daniel says, I wonder if other drivers are moving to expose native async interfaces
themselves.
Time to go back to DBC.
DBC, yeah.
That's the database special interest group.
Sure.
Daniel can go there.
I've not had much success when I go to the SAC, suggest things.
Yeah.
Let me talk about this async API a little bit for folks who are probably familiar with SQL
Alchemy's traditional API.
because there's a couple of things that stand out to me here.
One is, let's start simple here.
One is you have this concept of creating a select statement and then executing the statement
as opposed to like a query of a dot filter.
So there's this sort of this statement model that you build up.
Yeah.
You want to speak about that?
The original, the SQLAlchemy with query really is 95% the same statement model.
It's just the statement has the fetcher, the getter, the result fetching method stuck on
it.
And when we, when query came out, that was a historical thing.
The original idea of SQLAlchemy was that there was only going to be the select object.
And then you would use select object in with the session to get results.
And then it wasn't really very done very well.
It wasn't very flexible.
So people were coming to me with, why don't we use generative builder thing?
And that's what happened.
Cause I wasn't really, I was kind of, I was a little bit rudderless in those days.
So we had query and it took me quite a long time to realize, wait a second, query is basically
once query got much more fancy, it started as very simplistic, well, I could query for one
object and do this and that.
As I had to keep adding to it, it became clear that I was rebuilding select.
They met in the middle.
Yeah.
Then like, I'm just rebuilding select in exactly the same, but slightly different way.
And that bothered me for years.
So with 2.0, I finally took that on and did a really long, difficult refactoring to take
all of the guts of ORM query and put it into a different module called context that you don't
deal with.
And when you get the select object you see there, that indicates the exact same intent as query
a options filter.
And they both go to the same backend that does all the ORM figure it out stuff.
Right.
That's a little more elaborate to come up with the sequel that will be sent to the database.
One of the biggest refactoring was I've ever done.
And it was quite stressful when it seemed like I'm sitting dead ends is, oh, this is not going
to work.
Yeah.
I can imagine.
It was tough, but.
Yeah.
This is cool.
Yeah.
So basically the query of class dot that style is going away, right?
It's never going to go away because there's so much code in that style that it's basically
not, when you go to the website, it's not going to be in the tutorial.
It's going to, it'll be in reference docs.
So people that deal with that code could deal with it.
It'd be in some sort of middle ground where it's not fully deprecated, but it's not promoted
either.
Huh?
It's called legacy.
We've had different systems that stayed legacy for many years.
Makes sense.
It's very.
There's so much code written that way.
I totally agree.
Yeah.
I probably would never take it out.
So, but what I did do is when I do that, I try to make that code written such that it's
all by itself.
And it basically what query does now is it makes that select inside and sends it off.
So it's what you call eating your own dog food.
It means that the alternate API uses the real API internally.
So you're testing it either way.
Right.
Going back to our patterns talk, it's like an adapter for the select pattern.
Yeah.
Okay.
API.
Yeah.
Two other things that stand out here.
One is you create a session maker traditionally, and you create a session by calling it.
Here you have an async session maker, right?
To create them.
That's one of the differences.
That's not super different.
But the other one that is interesting to me is now these things are context managers, which
when I used to work with SQLAlchemy, traditional ones, I would create a class called a session
context just so I could do this in a with statement.
And here, obviously it's an async with statement, but that's just the async.
It's in the async aspect of it.
It's in the async aspect too.
Yeah.
I should mention that when SQLAlchemy in 2006, we were in Python 2.3, there were not context
managers.
They didn't come out until Python 2.5.
Right.
So you can't integrate with them if they don't exist in your API, right?
So I didn't.
So nothing was based on that.
SQL me 1.4 and 2.0, as much as possible, completely support the context manager model.
And the docs and the tutorials, if you look at the new 1.4 tutorial that's on the site, it's
all about context managers.
And all the try, accepts, and ready your own thing should go away.
You should use context managers now.
And what you see there with the async pattern, I've made it so that if you're using all the
new APIs, the context manager pattern with the engine and connection in core is mirrored to
the context manager pattern with the session and the session maker.
Nice.
And then it's also mirrored in the asyncio API.
So there's four different APIs.
There's core and ORM, sync and async.
And they all should have, as much as they can, the same, this means cone.
Yeah.
They have the same, as much as possible, they share the same context manager patterns.
And we want you to use context managers for everything now, for any kind of blocking.
And that's also how we can improve the transactional model so that you're really working the transaction
all the time with the core.
Because it's not a burden because you have context managers.
Things I see here.
I want to ask you about two bits of code and then take another question from the audience.
So you have a with session.begin and you do some inserts.
And then you don't say session.commit.
So the way this works, I'm presuming, tell me if I understand this right, is if it makes
it through the with block without an error, it commits.
If it makes it through the with block with an exception, it just doesn't commit and effectively
rolls it back.
That's what it does.
It does a rollback.
Yeah.
That's correct.
Yeah.
And we've had the begin context manager for a long time.
It's just, it wasn't totally consistent everywhere.
But it's always, we've had that for a long time.
Yeah.
But now I've made sure that all the opening and closing of a resource and the begin commit
of a resource are consistent.
And also there's always a way that you can open the resource and begin commit in one line.
Also the session maker has a begin on it now.
Super.
And you could do that.
The other thing I'm super familiar with execute, but then you also have stream, which looks
real similar.
What's the difference there?
Stream is asyncio only right now.
Stream is because it's important in many cases to get a result that's doing async streaming.
And when you're doing async streaming with a, you've got to have the await keyword.
So you can see the result object returned from session.stream is called an async result.
And all of the methods have the async annotation on them so that you see it's async for a in result.scalers.
Oh, I see.
So the things you interact with, the subsequent functions are themselves all async as well.
Okay.
Yeah.
That's cool.
Yes.
And what that will do is that will try to use a server-side cursor.
Yeah.
That's what I imagine.
That's cool.
You actually are.
Instead of trying to pull it all into memory, if you got a thousand rows back, you could start
pulling them more generator-like, right?
When you use a server-side cursor, you actually are genuinely not pulling everything into memory
first.
When you use the buffered result, you're getting everything into memory.
Yeah.
Which is normally okay.
Yeah.
Most of the time it's fine.
But if you want a whole bunch, oh, you're flowing it on.
Yeah.
Anyway, a lot of cool stuff there.
We'll get short on time.
Mr. Hypermagnetic out there asked, does SQLAlchemy support paginated results?
But that also leads us towards a little area I want to focus on just a couple of things as
well.
So yeah, maybe address that first.
Paginated results is one of those things that seems simple, but it's more of a quagmire
than you might expect.
There's different ways to do paginated results.
The way we all did it years ago was we used these functions that seem called limit and offset.
offset where limit will give you like 10 rows at a time and offset will start your result
set in the middle.
Once we work with bigger database, we all realize that offset is terrible because offset will
actually get the whole result set and scroll through it.
It does it on the server side, but it's still very slow.
So nowadays when you do pagination, you want to have some kind of approach where the query
you're doing is looking at some data in the data, some row, the data, some column in
the data you're querying.
So if you're paginating by date, page 10 would be where date is greater than this date
that from the previous page you just got.
That's one way.
Another way is to write SQL query that do this thing called window functions, where you can
figure out all the date or this whatever thing ahead of time.
Pagination implies, on that approach, implies that you have, or actually of any approach,
implies that you have an order by something.
So it's hard to make pagination be like this, like this result here, result.scalars to say
.paginate.
That's not really, you can make a very crappy version that does that, but it's not really,
it wouldn't really work that way.
There is, I should say there is a method in the result called partitions that will give
you chunks of a result at a time.
It's not quite the same as pagination because pagination is stateless, it's usually for web
applications where you're going to get a certain page of a result and then show them a web page
and then your whole SQL is over.
Right.
And then you're going to come back to that SQL later.
So we have features built in for partitioning of a single result set so that you get chunks
of that time, which is helpful.
Pagination is something you need to go look, there's some, I think maybe SQL Utils probably
has some helper functions for that.
We have some recipes in that wiki page that I told you to leave.
There's actually some recipes for pagination there.
I don't think pagination is like a turnkey.
It's got to be customized and people will write pagination frameworks and they're complicated
and they're hard to do.
So we leave those as an exercise for the community too, but we do have some recipes for that.
That's a great answer.
Okay.
Now for the last thing, I wanted to talk really quickly about this list from Dahlia called
Awesome SQLAlchemy.
It's one of these awesome lists that covers all these SQLAlchemy things like data structures
and types.
And I'm just throwing this at you and neither of us have a ton of experience with a lot of
these things, but I thought it'd be fun to just kind of go through them.
Some of the nice or interesting things that I've ran across here.
So it's a way to kind of wrap this up like some extras.
All right.
So one of them is SQLAlchemy Continuum, a versioning extension for SQLAlchemy.
I know what this is.
Yeah.
So it's probably based on one of the recipes that we have in the source distribution.
Version A, yeah.
It says the first line of SQLAlchemy already has a versioning extension.
The extension is very limited.
That's correct.
Everything he wrote there and everything they wrote there is correct.
I have some jobs in the banking industry where I was doing SQLAlchemy and we had some need
to version rows.
I need to know exactly audit this and when has it changed and how do I go back?
All that kind of stuff.
Right.
Yeah.
There's different models.
You can either take the rows as you get them and put them in an aircraft table, or you can
do this thing where you never update or delete a row.
You just insert a new row and you've got a temporal version scheme.
I have some recipes to do that in the example section because I did them at a job and I believe
Continuum builds upon those.
Fantastic.
To make a more robust, supported thing.
I don't know.
I've heard of Liquibase.
I don't know.
Yeah.
I forgot where Liquibase is.
I'm not a Java person either.
Yeah.
But yeah.
Okay.
Maybe.
It might be like that.
Here's another one that I ran across from that SQLAlchemy awesome list is SQLAlchemy
enum 3.4.
I'm guessing because it came from Python 3.4.
This package provides SQLAlchemy type to store.
I use a standard enum.enums.
Because a lot of times what happens is those things get turned into like numbers or other
weird things.
And here they get a little more type information, right?
Yeah.
This might be obsolete because we do support regular Python enums in our enum type.
Yeah.
We did it first.
This is looking like it's, well, seven months ago.
I don't know.
If someone's using this, they could stay because there are some enum features.
I've been notified that we don't do exactly the way.
There's some extra features that I forgot what they were, but there are some things that
enums do that we don't support.
Maybe it adds that on, yeah.
So maybe this probably does that better.
Yeah.
Enums are really a pain in the butt.
Yeah.
I know.
The data types.
I know.
All right.
The Python side's good, but the database side is.
Yeah.
It's always some sort of hack.
Yeah.
Alembic.
I mean, that's pretty well known to people, but that's obviously important because you've
got to keep your database and your models pretty closely in sync, right?
Yeah.
So we maintain Alembic.
I wrote Alembic years ago as SQLAlchemy Migrate was not really holding up to what we were
doing.
SQLAlchemy Migrate is part of OpenStack now.
And I actually have maintained that a little bit as well.
But Alembic was meant to be more of a bare bones, a straightforward, not too fancy tool.
It has become fancier.
And people that use it and get what it's about like it a lot.
It's never going to be as cool as South for Django.
Maybe an Alembic 3.
But that could happen.
It's not as automated as everybody would like.
It has a system that it will look at your models and look at the database and what we
call auto-generate your migrations.
But we don't guarantee those migrations are completely perfect.
You've got to go and look at them and fix them, which I still think it just did 95% of
the typing for you, of the work for you.
That's what it was meant to do.
Yeah, I think also it's helpful because it works in that realm of that's the area of
SQL I know less well, the DDL and that drop.
You know, how do I drop a table or a column and then re-add it under a different name without
losing data?
Like all that kind of stuff can be tricky.
Yeah, and a DDL is where a lot of the database vendor specific stuff is exposed.
Yeah.
Like all these crazy keywords and data types.
And it's less declarative and more imperative.
And so for Alembic, we just provide a model to create your migrations and it's been working
pretty well.
It's got a lot of features now, but it's again, a tool where if you don't know what DDL is,
you're going to have a bad time with Alembics.
Yeah, sure.
You should learn what database migrations look like in the alter table, alter column, and
know what that means.
And then Alembic will be pretty smooth.
And there actually are extensions for Alembic.
I saw it wasn't on that list.
There's an extension called Alembic Utils.
Oh, nice.
Which are extensions for Alembic for Postgres that also build upon some recipes that are
in these docs here.
And I recommend looking at that too.
All right.
There's a couple we can go quick on.
There's one for talking to Amazon Redshift as a provider.
That's pretty cool.
There's Form Alchemy, which...
Is this still maintained?
This is an old one.
Form Alchemy is 12 years old.
All right.
This is what it is.
It's old.
Maybe it still works, but let's leave that one alone.
It probably still works.
Yeah, probably.
It looks like it's had...
HTML hasn't changed since then.
Just style some CSS on it.
All right.
GeoAlchemy is pretty popular for people doing geospatial work.
Yep.
So there's GeoAlchemy.
There's GeoAlchemy 2.
I'm not sure of the relationship.
Which one is which?
Who works on which one?
GeoAlchemy seems like it might have...
It might not...
I'm not sure how much it's maintained.
I think it's maintained.
I would like it to be much more well-maintained.
I think it supports...
It's pretty Postgres-centric, but there are geo things for SQL Server and Oracle and MySQL.
I would like to see that stuff supported as well.
GeoAlchemy, I don't hear much about.
But I think it's a good project.
One for some growth, perhaps.
SQL tab.
SQL profiling and introspection for applications using SQLAlchemy.
Wow.
Have you seen this?
Cool.
I have not seen this either.
What is it doing?
So when you do a request, I think what it's doing, it stores what the page did.
So it says here there's 12 queries.
It spent like 20 milliseconds.
And then you click on each one and it'll show you the select statements and like the actual SQL.
So it's probably profiling from the client side.
Yeah.
Yeah, yeah, yeah.
I think so.
As far as the database.
Yeah, yeah.
That's nice.
If you're just timing it, yeah.
That's cool.
I wonder if it does things like explain.
I don't totally know, but it looks like it has a whiskey integration, which is cool.
This is another one related sort of that helps.
It's called N plus one.
Oh, yeah.
N plus one is the.
That's cool.
Yeah, it's a problem that many ORMs run into when people don't realize they're doing lazy loading and they don't do a join.
And it can be super indirect.
Like here I got a query of a list and I sent the list off to the HTML template.
And the HTML template did a loop and talked about some property on the thing.
And then there's a bunch of more database queries or something crazy, right?
Yeah.
Interesting that it seems to work for multiple lower ends.
Yeah, SQLAlchemy, PeeWee, and Django.
So I guess it's streaming and maybe just taps into the APIs of all those different tools.
Yeah, probably someone's added a layer for each.
Yeah, I guess you could do it in a kind of a distant way heuristically.
If you want to see the same query to the same table over and over again, maybe that's what it's looking for.
Yeah, perhaps.
Where it could do that without necessarily looking at SQLAlchemy and saying lazy logo called.
If it just looks at the SQL, there's probably ways to do it.
But that's an intricate problem.
But that seems like a really useful tool.
Yeah, it does.
And also I saw in there, we'll just close it out with this.
I saw in there, they mentioned the Pyramid debug toolbar, the Flask debug toolbar, and the Django debug toolbar.
And I can't speak to Django.
I don't think I've even run the Flask one.
But the Pyramid one has a, like, you can open it up and say, what were the SQLAlchemy queries of this page?
And actually see how many queries.
And if you're on a page and it says, look, there's 51 queries.
You're like, I thought I did one.
What just happened?
How did I get 51?
Like, well, you got 50 elements and an N plus one.
Debugtubo, I haven't really worked with it.
But yeah, we have a lot of people dealing with it.
Yeah, yeah.
We have to fix issues with it.
I don't actually write any web applications anymore.
I just do this.
So I don't get to do that.
I don't get to see that stuff.
Oh, really cool.
I also don't use them very much anymore either.
I find that I don't need that support as much as I did in the early days.
But I do remember them being quite valuable early on.
All right, Mike.
Well, there's more stuff we could go into.
But what a cool conversation.
And thank you so much for releasing the 2.0 stuff, for adding the async support.
It really opens up a lot more use cases.
I think that are going to be interesting for people.
So, yeah, it's fantastic.
Yeah, my pleasure.
The async worked out really well.
I'm really happy about that.
Now, before you get out of here, let me ask you the final two questions.
You kind of alluded to this already.
If you're going to write some code, what Python editor do you use?
I'm on VS Code right now.
I was on Sublime for a long time.
Years ago, I used TextMate.
I do use VI and Vim a lot.
But if I have lots of windows open, right now it's VS Code.
Fantastic.
And then notable IPI package.
Anything come to mind?
I mean, we kind of threw out a whole bunch.
Probably not.
Did I put anything?
I'm going to click to that.
I didn't put anything down.
How about your favorite out of the list that I threw up there?
Tell me, like, out of the awesome ones, which one stood out the most to you?
Oh, that N plus one thing looks really interesting.
All right.
Awesome.
So N plus one with the plus spelled out.
And the one also spelled out.
Yeah.
That's cool.
Yeah.
Very good.
All right.
Well, final call to action.
People have a bunch of SQLAlchemy code that they've written, but it's probably for the
older style.
It's probably not async and so on.
What do you tell them?
Yeah.
We have a brand new tutorial on the current website.
If you go to just docs.seqlalchemy.org.
On the left side, it'll have this 1.4 slash 2.0 tutorial that kind of represents SQLAlchemy
all over again using all the newest concepts.
I would look at that and just get to know it and also point out problems.
Yeah.
This is a new tutorial.
So this supersedes the old tutorials.
And it's going to talk about core and ORM at the same time.
This is a complete brand new rewrite from the ground up.
It took many weeks to do it.
And then there's also the migration guide.
But if you go through this tutorial, you'll really see what the new way of working is supposed
to look like and what the idea is what's supposed to be.
If you look at the, if you read the tutorial and assume you don't have any SQLAlchemy code,
assume you're just learning it from scratch, see what it's like.
See, wow, wow, this is different.
Or you might see, oh, I never knew that was like that.
Because it really tries to represent the library from the first principles, so to speak,
up to ORM stuff.
All right.
Well, I'm excited about all these new features.
Even the embracing of context managers everywhere.
It looks great to me.
So yeah, thanks for being here.
And thanks for sharing this with everyone.
Yeah, my pleasure.
Bye.
This has been another episode of Talk Python to Me.
Thank you to our sponsors.
Be sure to check out what they're offering.
It really helps support the show.
With TopTal, you get quality talent without the whole hiring process.
Start 80% closer to success by working with TopTal.
Just visit talkpython.fm/toptal to get started.
Do you need a great automatic speech-to-text API?
Get human-level accuracy in just a few lines of code.
Visit talkpython.fm/assemblyai.
Want to level up your Python?
We have one of the largest catalogs of Python video courses over at Talk Python.
Our content ranges from true beginners to deeply advanced topics like memory and async.
And best of all, there's not a subscription in sight.
Check it out for yourself at training.talkpython.fm.
Be sure to subscribe to the show.
Open your favorite podcast app and search for Python.
We should be right at the top.
You can also find the iTunes feed at /itunes, the Google Play feed at /play, and the
direct RSS feed at /rss on talkpython.fm.
We're live streaming most of our recordings these days.
If you want to be part of the show and have your comments featured on the air, be sure to
subscribe to our YouTube channel at talkpython.fm/youtube.
This is your host, Michael Kennedy.
Thanks so much for listening.
I really appreciate it.
Now get out there and write some Python code.
Bye.
you
Thank you.