#87: PonyORM: The most Pythonic ORM yet? Transcript

00:00 If you could have any API you wanted to access data from Python, what would it look like?

00:04 What would make it Pythonic? This week, you'll hear about Pony ORM. Pony is a Python ORM with

00:11 beautiful query syntax that lets you write your database queries using Python generators and

00:16 Lambdas. Join me in a conversation with one of Pony ORM's founders, Alexei Milošković.

00:21 This is Talk Python to Me, episode 87, recorded November 3rd, 2016.

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

00:56 ecosystem, and the personalities. This is your host, Michael Kennedy. Follow me on Twitter

01:00 where I'm @mkennedy. Keep up with the show and listen to past episodes at talkpython.fm

01:05 and follow the show on Twitter via at Talk Python.

01:08 This episode has been sponsored by Rollbar and GoCD. Thank them both for supporting the podcast

01:15 by checking out what they're offering during their segments.

01:18 Alexei, welcome to Talk Python.

01:20 Hi, Michael. Thank you.

01:21 Yeah, it's great to have you here. Thanks for coming. I'm super excited to talk about your

01:26 ORM and that you built this, what I'm saying might be the most Pythonic ORM yet, which because

01:33 of the really cool query syntax that you use and a couple of other highly Pythonic techniques

01:39 and I'm very impressed with it. But before we get into the details of Pony ORM, let's talk

01:44 about your story. How'd you get into programming in Python?

01:46 Well, I was always interested in programming, actually. When I was a teenager, I had a Sinclair

01:51 ZX Spectrum computer, played computer games and started to program BASIC. And actually,

01:58 it was the reason for me to get a master's degree in computer science later. I was programming

02:04 while studying in the university, but what I really liked was computer networks. So after

02:09 graduating, I started to work as a network engineer in an IT department of an airline company. And at

02:17 that time, it was 2001 and 2002, airline agencies had to use dedicated list lines for connecting

02:26 to airline database system. And I figured that using internet for that would be like 100% cheaper.

02:35 And then I got that crazy idea of developing a client server solution, which works over internet.

02:42 So I developed a prototype and it consisted of an app written in Delphi and a Linux server. And

02:50 then I presented it to the airline management team. And they were impressed and offered me

02:55 to form a development team. So from that point in time, I continued as a developer.

03:02 Yeah, that's think of the times back when you had to have a leased line or back when you had

03:08 BBSs, you had to dial up to get online. Like it's just such a different world, right? But

03:13 that's really cool to help that company like emerge out of that world.

03:17 Right. So now you cannot imagine that not using internet, right?

03:21 Exactly.

03:22 Yeah. So and then several years later, I started to work for another company. It was a fintech startup.

03:32 It was a New York based company and they outsourced the development to St. Petersburg, Russia,

03:38 this city where I'm originally from. And I was developing the backend in C++ and Java. And when

03:47 the business took off, they decided to form a team, a development team in New York. And they

03:53 invited me for this role. So I moved to New York and formed a development team there. And it was

04:01 building software for seven years there. And during that time, actually, my friend, Alexander

04:10 Kozlovsky, we studied together in the university. And he was really, really interested in databases.

04:17 And he was actually a senior lecturer. So he taught people databases. And he feared that most

04:25 people actually don't get relational algebra, you know, and there should be a better way to

04:32 build software. He suggested an idea to build a web framework. And I love the idea because working

04:43 for a financial company, I saw that it is a very fast environment. And I actually missed a tool like Delphia,

04:53 rapid application development tool. And I thought that if I would have one, I would use one. And this is how we started to work on Pony. And it was a weekend project, actually, initially.

05:09 And it was a template engine. It was routing. It was ORM. And then we found that probably we shouldn't do all those things because it's too much.

05:21 Yeah.

05:22 And by that time, by that time, Circle Alchemy appeared. Actually, we started the same year, 2006. It was the first year when we started to work together. And actually, by that time, Alexander already tried to build a mapper. He tried in C++ in Delphi. And Python was a choice which he came to understanding that it should be a dynamic language.

05:50 Yeah, I'm sure it was much harder in C++ to pull something like this off, especially with the really rich language level query syntax. Like, I'm not really sure how that works in C++.

06:01 Right. And then we started to work together on this. And he told that it was a SQL object, such a mapper. And in one of email lists, he suggested that why not to use generator syntax for writing queries. And I love the idea, too, because it was natural syntax for queries.

06:24 It's a completely natural syntax. Like, I think of generator expressions and list comprehensions as kind of like in-memory database operations, just working on your objects, obviously not indexed or anything like that. So if you're going to do that in your Python code, but somehow project that down into the database, that's perfect, I think.

06:44 Yeah, exactly. And what we liked as well, that it is so natural for someone who just starts working with Python, that you don't need to actually learn another language such as SQL.

06:59 Yeah, I think you probably could get away with just being able to read but not write SQL with Pony.

07:04 Yeah.

07:04 Yeah. Or SQLAlchemy or some of the other ORMs, right? Like, if you can understand the debug output, you may be okay. Who knows? It depends on how complex I suppose it gets. But that's a cool goal anyway.

07:18 Exactly. And I worked with people who actually can program different languages, but it's hard for them to understand SQL pretty well.

07:29 And that's why we decided that if we could provide such a tool, it will be very helpful.

07:35 And I could see how it can speed up making prototypes.

07:40 And, you know, when you build a prototype, you never know if it will be an application later and how it will evolve.

07:48 Yeah, absolutely. That means... I wonder how many applications there are in production in the world that were just supposed to be prototypes.

07:56 I bet it's a really high percentage.

07:58 Yeah, I agree. You need to have a tool which allows you to start quickly.

08:03 And then when you actually evolve with this application evolves, you want it to allow you to build this as complex as you want.

08:13 Well, yeah. And also the ability to recreate the project quickly rather than manage some huge data access layer with some kind of ORM that's really nice and clean.

08:24 Like Pony ORM is a really good thing to have so you can say, no, no, this is a prototype.

08:30 We're going to start again. But it only takes like this morning and then we'll have it back to where it is.

08:34 But proper. Something like that, right?

08:36 That's cool.

08:37 An app that created using a simple tool can reach a point where the further development is too difficult or even impossible.

08:45 And having reached this borderline of complexity, it's often no time actually to start from scratch.

08:54 Yeah, it's usually you're already down that path and it's too late.

08:57 How interesting.

08:58 Okay, so let's talk about Pony ORM a bit.

09:01 So Pony ORM is a data access layer.

09:05 It's an ORM, object relational mapper.

09:08 How would you describe it if somebody asked you what is Pony ORM?

09:11 Well, it is an advanced object relational mapper and it provides a very easy interface.

09:20 And even a newbie can start working with this.

09:23 That is one thing I was struck with when I was working with it is I was almost thinking like, well, how do I connect back this object to the entities?

09:34 Like, where do I create the entity base class from?

09:37 And how do I, you know, why don't I have to call add to insert objects?

09:42 And why don't I have to call commit to commit them?

09:44 And all of those answers are because you're using some really interesting Pythonic concepts that sort of simplify a couple of the steps that you might have found in other ORMs.

09:56 Right.

09:56 And Pony, it's a small horse, right?

09:59 It's small, smart, and powerful.

10:02 So we believe that these features we put into our mapper.

10:08 I think so, too.

10:09 I think it's really, it is really nice.

10:12 And I'm looking forward to using it properly on some projects now that I know a little bit more about it.

10:16 So I want to ask you what's Pythonic about it.

10:20 But I think maybe the way to uncover the various Pythonic elements is to actually work through an example.

10:27 But before we get to that, let me ask you this other question.

10:30 So there's these two styles or two design patterns that typically are in play at ORMs.

10:37 And that's whether or not the ORM acts as an active record where you sort of work with individual elements.

10:46 So like Ruby on Rails or I think the Django ORM is like this.

10:51 Or it's a unit of work, which is like SQLAlchemy, where you create a bunch of objects and make some changes, inserts, updates, deletes, and so on.

11:00 And when you're finally ready, you commit that unit of work and it applies all of those.

11:04 Which style is Pony?

11:05 Yeah, absolutely.

11:07 There are two basic patterns.

11:09 And with active record, you have an object which represents a line in the database.

11:16 And the thing is that if you select the same object twice using active record, you will have two instances of the same object in the memory.

11:27 And it is a huge problem because now you have two objects which are actually the same.

11:35 And when you start to change one object.

11:37 And then change another one, you can have lost updates.

11:40 And another thing that you need to always remember to call safe on each object.

11:47 Yeah, exactly.

11:47 And you don't really do that with unit of work.

11:50 You just kind of work with your data.

11:51 And when you're ready, you commit it, right?

11:54 Every time you read a record from the database, we first check in the identity map if there is such a record already loaded into the memory.

12:04 And this allows us to return an object from the cache from the memory instead of querying the database.

12:15 Yeah, so that's cool in that it gives you better performance because you're not round-tripping to the database as often.

12:20 But it's also maybe even more important that it means if you somehow get the same object twice, it's still the same object in memory, right?

12:29 Exactly.

12:29 So it allows us to avoid excessive database calls.

12:34 And at the same time, we always work with the same copy of the object.

12:40 And another option that it provides us with is to manage transactions.

12:46 So we can see when the scope of a function or a context manager was left.

12:52 And at this point, we can commit all the changes to the database.

12:57 It is very convenient.

12:58 Yeah, that's very convenient.

12:59 I love your use of context managers in this project.

13:02 So maybe that's a good place to jump into talking about the code.

13:06 And we have to be a little careful on audio format talking about code.

13:10 But maybe we could just kind of roughly walk through some of the steps involved.

13:15 And we could talk about the Pythonic concepts as we get to them.

13:19 Okay.

13:20 So obviously, yeah.

13:21 So obviously, to get started, you have to import Pony, right?

13:25 So there's a number of things in there that you got to work with.

13:29 So pip install Pony, import Pony, and so on.

13:32 And then you create a database.

13:35 So a database is just an object, right?

13:38 But this database has a couple of features that go on to be super important.

13:43 Like on that object, you have a base class.

13:48 And all of your entities have to derive from it, right?

13:50 Exactly.

13:52 So when you create a database, at this point, it is just an object.

13:55 And later, when you create entities, you inherit it from db.entity, which is an attribute.

14:03 And it is abstract class, which tells that it is an entity which is connected to this database.

14:12 And having such pattern, actually, you can create several databases.

14:17 You can simultaneously work with more than one database.

14:20 Right.

14:21 Maybe you have like a core database that has your important stuff and then an analytics database for tracking how people are using your app.

14:28 Something like that, right?

14:29 Yeah.

14:29 It's very often used this way.

14:31 Yeah.

14:31 And then you create, like you said, you create this class that derives from db.entity.

14:36 What does it look like to define the columns?

14:38 The main difference of Pony from other mappers is that we work in terms of entities.

14:45 So in SQLAlchemy, you work with relations.

14:51 And here with Pony, we hire the level of abstraction and work in terms of entities, in terms of objects.

15:01 And we don't create columns.

15:04 We create attributes of the object.

15:06 Because this way, we figure that it is easier for a human being to think in terms of objects, not relations.

15:13 Yeah.

15:13 And probably Python as well, right?

15:15 We're used to working with hierarchical collections of objects anyway.

15:18 Yeah.

15:19 Yeah.

15:20 And then you create attributes.

15:21 And we provide a very concise way of declaring such entities.

15:27 Because from the first day of developing Pony, we actually had three goals.

15:32 We wanted this mapper would be easy to use.

15:36 It means having PyTonic API.

15:38 It should be very performant.

15:40 So high performance is the goal.

15:42 And the third is safety and reliability.

15:47 So we would like to avoid SQL injections and make it reliable.

15:52 Yeah.

15:53 You do some really nice stuff to avoid SQL injection attacks.

15:56 And I'll talk about those later as well.

15:57 Yeah.

15:58 So when you declare entities, it is as short as possible.

16:02 You just write the attribute name and then the type of the attribute.

16:07 So it could be a required attribute or optional.

16:11 Another two options is you can have a primary key or a relationship to another entity.

16:18 And then in creating the attribute, you specify the type of the attribute and some additional options like unique or auto generator.

16:31 When you want to have a sequence creating a new object, sometimes you want ID to be automatically generated.

16:41 Right.

16:41 And of course, you have default values as well for like created dates and things like that, right?

16:45 Right.

16:46 So we have API reference.

16:48 And there are about like probably 15 options that you can specify for attribute.

16:55 And you asked about columns.

16:57 You can also specify the name of the column.

17:00 If you would like to give a specific name to the database column or if you have a database already created and you would like to map these entities to the existing tables.

17:13 So you can specify the table name and the column name declaring the entity.

17:19 Yeah, that's excellent.

17:20 So let me try to give people a really quick example here.

17:22 So you've on your GitHub, you've got a nice example called eStore.

17:28 And you have relationships between like customers and products and stuff in a shopping cart and so on.

17:35 So you obviously have a customer class and a product class and so on.

17:40 But just to like talk about the simplicity.

17:42 So for a cart item, the whole thing is you have a cart item class that drives from db.entity.

17:48 It has a quantity, which is a required column, which is an integer.

17:52 And then you have a customer, which is a required customer object.

17:56 Just literally parentheses the class name, not even in quotes, just actually the Python class object.

18:01 And product required of product.

18:06 So here's an object that has two relationships back to customer and product.

18:12 And it's like the simplest thing you can imagine, right?

18:15 Right.

18:16 In order to make it even simpler, you can take a look at the diagram at our entity relationship diagram editor, which represents this database schema.

18:28 So you can visually see this.

18:30 Right.

18:30 So for people who are listening, if they want to find it and they don't want to go in the show notes, it's editor.ponyorm.com slash user slash pony slash E capital S store.

18:41 Of course, I'll link to that.

18:42 Yeah, that's really cool.

18:45 And you guys have this wonderful online designer that shows you the relationships that lets you model and rearrange them.

18:51 Even get the what would be the generated SQL, the generated Python classes, and so on.

18:56 Definitely, I want to come back and talk about this online editor because it looks really special.

19:01 So, yeah.

19:02 So very cool.

19:02 We create these entities and the relationships are really super simple to set up.

19:07 So that's wonderful.

19:08 And then another thing that you chose to do in your API is make the ID something that is optional.

19:16 Right.

19:17 Oftentimes, you don't have a natural primary key.

19:20 Yeah.

19:20 And in this case, you just create attributes that you want to have in the entity.

19:29 And if you don't declare primary key, pony will add it automatically.

19:33 And it will be integer type with sequence.

19:37 Yeah, the auto-incrementing primary key.

19:40 Exactly.

19:41 Yeah.

19:41 Yeah.

19:41 Yeah, that's perfect.

19:43 So then you go through the standard stuff.

19:44 You bind to a database connection and a database type.

19:47 What types of databases are supported?

19:50 So currently, we support four major databases, which are SQLite, Postgres, MySQL, and Oracle.

19:59 And currently, we are working on adding Microsoft SQL Server.

20:05 So we had users who asked us about it, and we actually added this type of database.

20:12 But the thing is that recently, we added a JSON type support.

20:18 So you can work with the database JSON type and make queries to those JSON types.

20:26 And this part, we didn't add to Microsoft Server yet.

20:30 But after we will add the JSON support, I think we will include it into the release, and we'll support five databases.

20:38 Oh, that sounds great.

20:39 I mean, those are basically the major ones.

20:42 Are there any that people are asking for that are asking for commonly that are missing?

20:48 Only a Microsoft SQL Server was something we were asking about.

20:52 Yeah, it seems to me like those five rounded out.

20:54 Tell me more about this JSON thing.

20:56 Is this something that is JSON stored in like Postgres or SQLite, or is it something else?

21:04 Right.

21:05 So we used the native database JSON type.

21:07 So it is JSON B, binary JSON for progress and for MySQL.

21:14 For Oracle, it is, I believe it is Club.

21:17 And for SQLite, normally it is stored as text by default.

21:23 But you always can install a module, which is called JSON 1.

21:29 And this way, your queries to SQLite will be faster.

21:33 Okay, so there's a module that I can extend.

21:36 I see.

21:37 That I can extend SQLite so that it can understand the internals of that JSON document so I could possibly query by some attribute deep down inside it.

21:46 Recently, we have added native JSON data type support.

21:50 And this way, we can actually combine NoSQL database with a relational database.

21:56 So it's like having best of two worlds.

21:59 In a relational database, you can declare an attribute of JSON type.

22:05 And in this attribute, you can keep the data structure, which can vary.

22:12 Yeah, this is really cool.

22:13 So basically, you have regular columns, but you can also have hierarchical columns that are stored as JSON, more or less.

22:21 That's right.

22:22 Yeah, it does very much seem like here's a MongoDB type thing going on as part of that table.

22:30 Exactly.

22:30 And you can actually do queries to the internals of this JSON structure, which is described in our documentation.

22:40 Yeah, yeah, that's cool.

22:41 Can you add indexes to the internals of that data structure?

22:45 I think not yet.

22:47 It's down to whether the database is supported, I'm sure.

22:50 Probably if the database supports that, you can...

22:54 Actually, it is just a native JSON type for the database.

22:59 If a database supports index, you can add an index.

23:02 Right.

23:03 Okay, that makes sense.

23:04 So it's really a question of, does MySQL support indexes on embedded JSON types, which I actually don't know the answer to.

23:10 So that's cool, though.

23:12 We have users who use this feature in production already.

23:16 Nice.

23:16 So yeah, speaking of production, can you speak of some of the notable deployments of PonyRM?

23:22 I mean, I know the vast majority of no visibility into, but...

23:25 Yeah, some of them asked us not share their details because they have PR departments that they need to approve it with.

23:35 And for those who we can share, I put testimonials to our site.

23:41 Oh, yeah, excellent.

23:42 I see at the bottom, there's a whole bunch of testimonials and people can come check them out.

23:45 I'm sure not everybody has permission to divulge whether they're, you know, speak on behalf of their company, but that's very cool.

23:53 So when I was working on the new version of the site, I sent this request to all our users.

24:01 And actually, those people paid for the license because initially we had Pony released under dual license.

24:10 So it was AGPL and it was commercial license.

24:14 And then we figured that AGPL actually doesn't really work with the community.

24:19 People don't like AGPL at all.

24:21 And although we were selling licenses, we decided that we probably need to change our model and we just need to release Pony under Apache 2 license.

24:34 And think about the possibility to monetize our editor because it really can be a tool which allows people to develop applications faster.

24:46 And we use the same model as GitHub.

24:50 So if you would like to share your data schema with everybody, make it public, you can use it for free.

24:57 And if you would like to have a private data schema, then we'll ask to pay some money.

25:03 Yeah, I think that's a great model.

25:04 And we'll definitely talk about it.

25:06 That's at editor.ponyorm.com.

25:09 Yeah, it's very cool.

25:10 This portion of Talk Python to Me has been brought to you by Rollbar.

25:28 One of the frustrating things about being a developer is dealing with errors.

25:31 Ah, relying on users to report errors, digging through log files, trying to debug issues, or a million alerts just flooding your inbox and ruining your day.

25:39 With Rollbar's full stack error monitoring, you'll get the context, insights, and control that you need to find and fix bugs faster.

25:47 It's easy to install.

25:48 You can start tracking production errors and deployments in eight minutes or even less.

25:53 Rollbar works with all the major languages and frameworks, including the Python ones, such as Django, Flask, Pyramid, as well as Ruby, JavaScript, Node, iOS, and Android.

26:02 You can integrate Rollbar into your existing workflow, send error alerts to Slack or HipChat, or even automatically create issues in Jira, Pivotal Tracker, and a whole bunch more.

26:11 Rollbar has put together a special offer for Talk Python to Me listeners.

26:14 Visit rollbar.com slash talkpython to me, sign up, and get the bootstrap plan free for 90 days.

26:20 That's 300,000 errors tracked all for free.

26:23 But hey, just between you and me, I really hope you don't encounter that many errors.

26:27 Loved by developers at awesome companies like Heroku, Twilio, Kayak, Instacart, Zendesk, Twitch, and more.

26:33 Give Rollbar a try today.

26:35 Go to rollbar.com slash talkpython to me.

26:46 Let's focus for a moment on what I think are really getting to the Pythonic concepts.

26:52 What we talked about so far with the way you work with Pony is it's very nice, but it's not super unique.

27:00 And it's just like a nicer version of what I feel like the other ones are doing.

27:03 But then you get to things like working with context managers and decorators and all sorts of really nice stuff.

27:10 Tell us, how do we like insert data?

27:13 Or how do I get like a database connection and commit it and go through like that whole set of steps there?

27:19 Well, when you bind entities to the database, the next step, you need to generate mapping.

27:24 And this way we just connect objects in the program entities to the database tables.

27:34 And then you can start working with the database.

27:38 You can select entity objects or create new objects.

27:43 And all that interconnection should be done within the database session because it is a transaction, right?

27:52 Right, this whole unit of work thing, right?

27:53 Yeah, unit of work, exactly.

27:55 So you need to either create a function and decorate it with DB session.

28:00 Or you need to use the context manager and work with the database within this context manager.

28:09 Yeah, so let's focus on that for a sec because I think that's great.

28:12 So if I want to make a bunch of CRUD operations, inserts, updates, deletes, and so on, one way to do that is write a function that changes the data, creates objects, it updates existing ones.

28:25 And all I have to do is put a decorator at DB session on that method.

28:30 And it will handle both the positive outcome and the negative outcome.

28:36 So if that method runs to completion without errors, without exceptions, it will commit the transaction.

28:43 But if for some reason there's an exception in there, it will roll back the transaction just by virtue of having the decorator, right?

28:49 Exactly.

28:51 So when you wrap your code, which works with entities with DB session, it actually does the following.

28:59 When you start working with the database, it doesn't start the transaction from the first moment.

29:07 Because if it is just read, you don't need to open the transaction.

29:12 You can read from the database without opening it.

29:17 And when you start to change, the new transaction is created.

29:21 And then when you leave this scope, if no exception happened, then Pony automatically will commit the changes to the database.

29:31 If an exception happened, then it will be rolled back.

29:36 And you can specify parameters to this decorator.

29:40 For example, if you would like to retry several times, you can do that specifying parameters to the decorator.

29:48 That might be helpful when you have more than one process working with the same table with the same row.

29:55 And Pony has a concept of optimistic checking.

29:59 So this way we avoid a situation when you can lose updates.

30:05 When Pony saves something in the database, it actually adds checks that the attribute values keep the same.

30:15 Because when you work with objects, Pony tracks which objects you read and which objects you write.

30:23 So if you, for example, in your code, read an attribute and then do something and save this object into the database,

30:32 Pony will make sure that attributes that you read keep the same.

30:38 Because if it don't, it means that some other process changed the database.

30:43 And in this case, it cannot be saved in the database.

30:46 Yeah, this is very common in the sort of disconnected unit of work styles of ORMs.

30:52 But I really like this model.

30:54 I mean, the alternative is pessimistic concurrency, which is to say, lock the database.

30:58 Nobody can do a thing until we're done with this whole part, right?

31:02 And everybody get in line.

31:04 And I'm going to do a bunch of changes.

31:05 And then you can go make your queries or other operations on the database and not see inconsistent data.

31:11 And that's great.

31:13 But it also kills concurrency and all sorts of things.

31:17 With this model, the optimistic concurrency, it's like, well, probably nobody changed this record also.

31:23 So it'll just do all the work.

31:25 And then it'll say more or less something to the effect of, update person with ID this where the name is the old name.

31:32 Set the name to the new name.

31:33 Something like that, right?

31:34 So the goal of Pony is provide consistent API and ability to work with the database,

31:43 even for people who don't know much about transactions and lost updates.

31:49 So we would like Pony to provide such an easy way to work with the data reliably.

31:56 Yeah.

31:56 And I think the retrying thing is really interesting.

31:58 A really interesting addition there.

32:00 Sometimes you don't want to make an entire method, basically a transaction in a sense.

32:05 So you can use another Pythonic concept of context managers, right?

32:10 So you can just say with DB session, do you work in that little suite?

32:13 And then when you're out of there, it follows the same rules.

32:16 No exceptions, commit.

32:18 Exception, rollback, right?

32:19 Right.

32:19 And at the same time, this concept of optimistic checking, it works by default in Pony.

32:26 But if you need to log the database, do for update query, you can do that too.

32:33 There's a method for entity has a method for update, and a query has such a method for update.

32:40 So this way you can log the database if you need.

32:44 Right.

32:45 Okay.

32:46 Very cool.

32:46 So the next thing, once you have data in the database, of course, you want to write a query.

32:50 And this is where, this is what got my attention in Pony in the first place, is the way that

32:55 I would, like, say, do a query on, like, a bunch of people we inserted to the database

33:01 at the person class would be to write a generator that would do the in-memory query.

33:08 So you'd say something like, select P for P in person if P dot age is greater than 20.

33:12 And, like, that's literally what you write.

33:13 Yeah?

33:14 Yeah.

33:14 You write this generator and pass it as an argument to the select function.

33:21 And what Pony does?

33:22 And what Pony does, it gets the bytecode of the generator.

33:26 It decompiles it in order to extract the abstract syntax tree.

33:31 And then it translates this abstract syntax tree into SQL.

33:37 And made a presentation on the EuroPython in 2014.

33:42 And it is available on YouTube where I explain the whole process, how we actually do this translation.

33:52 Because before, a lot of people actually asking if this translation is fast enough.

33:59 And I should tell that the whole thing works really fast.

34:04 And the thing is that you need to translate each generator only once.

34:09 In the whole program, you need to translate the generator bytecode only once.

34:15 And then it will be cached.

34:16 And the result of the translation to SQL will be cached as well.

34:22 And after that, we just have the SQL query where we just put parameters and send it to the database.

34:30 That is really awesome.

34:31 I'm definitely going to link to your 2014 talk.

34:34 You could also do, like, projections.

34:36 So, like, in a generator expression, I could say p.name, p.age for da-da-da-da.

34:41 You support basically doing projections at the database level in the same manner, right?

34:46 Right.

34:47 So, basically, you don't even need to think in terms of tables.

34:53 You just work with objects.

34:56 And you can select a whole object or you can select a specific attribute.

35:02 Or you can even do aggregation.

35:05 So, you select a tuple.

35:07 For example, you select p.name.

35:10 And then, for example, if we have some...

35:13 Let me give you this example.

35:15 For example, you work with a customer.

35:18 And this customer has some orders which has the attribute total amount.

35:26 So, you can do aggregation and return from this query a tuple where you have the customer name and then aggregated amount of his or her orders.

35:40 That's really cool.

35:41 Do you have some good examples of that on your docs?

35:43 Yeah.

35:43 Okay.

35:44 I'll be sure to link to them.

35:45 Yeah.

35:45 Send me the link.

35:46 And finally, if for some reason this syntax doesn't work for you, you're just like, you know, I just need to write a SQL query and just have something happen.

35:55 You can do that as well.

35:57 And so, you could just go to your entity which represents the table and just say select by SQL and give it some kind of SQL statement.

36:05 You can write a row SQL, but what is more interesting is that you can insert a row parts of SQL query into a generator.

36:16 You know, we have such a function, row SQL.

36:20 And, for example, there is a function in the database which is not supported in Python, for example.

36:28 Then you can insert this piece of row SQL into a declarative query written in the form of a generator.

36:37 That is really awesome.

36:38 And I also like how you automatically capture in your SQL text local variables as names.

36:48 So, like, if I had X defined locally, in my SQL statement, I could say $X and you would translate that to a parameter, right?

36:54 Yeah.

36:55 And one second, let me send you the link for a row SQL.

36:59 Oh, yeah.

37:00 That's really amazing.

37:00 So, you can just say raw SQL as part of either a Lambda or a generator expression and it just, it's going to execute on the database, right?

37:11 Exactly.

37:11 So, if you insert this part of a row SQL into the translated query.

37:17 Now, one thing that's interesting is you have both Lambda expressions and generator expressions.

37:22 Right.

37:23 When do I use, and I can pass those as the select clause.

37:26 So, when do I use which?

37:28 If you would like to get an object as the result, you can use Lambda or generator.

37:35 So, they will actually be translated into the same SQL query.

37:40 But if you would like to aggregate the aggregation or return just a tuple of attribute names, then in this case, you use a generator because it allows you to put any expression in the beginning of the generator.

37:57 Right.

37:58 And it will be returned as a list of such tuples, for example.

38:03 Yeah.

38:03 I really love that aspect of Pony.

38:06 That's super cool.

38:07 So, let's talk about parameters because it's super important for the safety of your app that you don't write just straight up raw SQL with concatenated strings, right?

38:17 Like, if anybody has a doubt, just Google little Bobby tables.

38:20 So, the parameters here are really great.

38:23 Like, tell us about that.

38:24 Pony allows you to avoid SQL injections absolutely because all the parameters that we pass into the SQL query, they are passed as database parameters.

38:36 So, this way, there is no way to get a SQL injection.

38:40 And also, Pony allows you to pass not only parameters but expressions as well.

38:47 So, you can find that part described in the documentation.

38:52 When you pass the parameter, you can specify the whole expression inside the generator query.

39:00 That's really great that you can pass expressions as well.

39:02 I think there's always tradeoffs when you use something, some kind of ORM versus just writing raw SQL entirely.

39:09 I would, personally, the safety from SQL injection attacks, the fact that you might forget to use a parameter somewhere, almost alone makes it worth using ORMs.

39:20 Not to mention all the cool benefits of everything else.

39:23 So, yeah, I think it's really great that you have all these levels of safety, especially for web apps.

39:29 Yeah.

39:29 So, let's talk about the team.

39:31 You talked about your co-founder just a bit, but maybe mention him again.

39:35 And how many people are working on the project now?

39:37 Currently, we have four people.

39:39 So, Alexander Kozlovsky is my co-founder.

39:42 And we started to work with him together.

39:45 And at some point, I decided to leave my job and concentrate on Pony full-time.

39:53 Congratulations on that.

39:54 That's awesome.

39:55 Yeah, thank you.

39:55 Although, we found another project later.

39:59 So, currently, we work on Pony.

40:02 We have four people.

40:04 We have two more developers.

40:06 So, one of them works on backend and another on frontend.

40:10 So, his role is to help us build our editor.

40:15 We have big plans on that.

40:18 And currently, we actually, yeah, we work on the editor as well.

40:23 Nice.

40:23 Yeah.

40:24 So, tell us where are you going?

40:26 What features are you adding?

40:27 Currently, you can use the editor in order to create entity relationship diagram.

40:33 And then get either a Python classes or SQL create table statements.

40:41 And this is actually used a lot by users who actually don't know anything about Pony.

40:49 And when I write them that, do you use Pony?

40:52 Why not you just start opening the tab with Python classes?

40:57 They actually, often, they don't know a thing about Pony.

41:01 And when they discover this possibility, they love this feature and start using Pony as well.

41:08 Yeah, that's great.

41:09 Yeah.

41:09 So, let's talk about your editor a little bit.

41:11 So, I really like it.

41:12 Like I said, editor.ponyorm.com.

41:14 And the idea is it's basically a database diagram development tool, right?

41:20 So, you can see your tables.

41:21 You can see the relationships and so on, which all that is really cool.

41:27 And I can, like, add a new entity.

41:30 And it gives you a whole nice designer.

41:32 It shows you a bunch of stuff that maybe you didn't realize was something that you could model,

41:37 like the volatility of attributes or things like this.

41:40 But then once you get your model built together, you can actually go and say, show me the Python code.

41:46 Yeah, show me the Python code.

41:53 For the various databases and so on, you know, you can create this.

42:00 So, you can create a tool that you can create a tool that allows them to do the whole cycle of development.

42:12 So, when they can create a diagram.

42:13 So, when they can create a diagram and then get the Python code and work on the project.

42:18 And when they need to change something, they can just change it in the editor and get their migration applied to the database.

42:27 And keep those, the log of the changes in the editor.

42:31 Nice.

42:32 Yeah, okay.

42:32 So, that's really neat.

42:34 That brings us to migrations a little bit.

42:36 What's the story of migrations there?

42:38 This point is the feature which is most asked.

42:44 And at this point, we are working on migrations and we developed the prototype.

42:50 And it works great.

42:53 But what we would like to add, an ability to write comments inside the migration file the same way as in Django.

43:03 So, when you just write operations like add attribute or add relationship, I believe that it will be released within the following month.

43:14 But it is not that fast.

43:17 The development of migrations took longer than we initially suggested.

43:22 Yeah, it seems like it might take a while.

43:24 It's a non-trivial thing.

43:26 That's right.

43:27 But we are very close to the release at this point.

43:30 Nice.

43:30 Yeah, what are you doing after you get the migrations done?

43:33 Some time ago, we started to work on a project which is called Fine Art Biblio.

43:37 So, it is a Miami-based company.

43:40 And the goal is to provide Wikipedia for modern art.

43:44 I formed a team for this project.

43:47 And from day one, we started to use Pony.

43:50 It was actually our goal to find a company where we can use Pony in order to build the back end and see how we can make it better.

44:01 And when we were working on this project, we found that when we need to build a front end, we actually need to have the same models as the front end.

44:12 And we thought, why not to provide a way to have the same identity map, the same models as the front end.

44:23 And this way was Pony.js.

44:26 This way, Pony.js appeared.

44:28 So, Pony.js is a don to Pony, which is not released yet.

44:33 Why, I will tell a little bit later.

44:37 And this way, you can get the objects which are extracted in the back end.

44:43 You can have the same graph of connected objects at the front end and work with them in JavaScript.

44:52 Nice.

44:52 So, I can just basically expose my models from my back end as some kind of service that Pony.js can consume.

45:00 And then they're like replicated over?

45:02 Or how does that work?

45:03 When you extracted objects from the database, then you call the to.json method.

45:09 And it returns a set of objects which will be sent to the front end.

45:15 And the front end, we have a library, Pony.js, which puts it into an identity map and allows you to work with objects the same way you do it on the back end.

45:29 So, you can traverse attributes.

45:31 You can traverse relationships.

45:34 You can create new objects and send them back to the back end.

45:38 That's cool.

45:39 And for allowing this, for making this work, we created a layer of permissions and security.

45:48 So, it is also a declarative language which allows you to tell which operations are allowed.

45:55 Create, delete, update, or modify.

45:59 But when we were working on this project, we found that Facebook released GraphQL.

46:06 And we thought that probably it is the same thing that we are working on.

46:12 And we decided actually to try GraphQL.

46:17 And by this time, we had one more team member joined to our company.

46:22 And he developed GraphQL server.

46:26 And now we have actually three ways to work with the front end.

46:31 And after we release migrations, we believe that integration with the front end actually will be our major focus.

46:41 Yeah, you have your back end stuff pretty much dialed in.

46:44 So, extending that seems like a great idea.

47:00 This portion of Talk Python to Me is brought to you by GoCD from ThoughtWorks.

47:05 GoCD is the on-premise, open-source, continuous delivery server.

47:10 With GoCD's comprehensive pipeline and model, you can model complex workflows for multiple teams with ease.

47:16 And GoCD's value stream map lets you track changes from commit to deployment at a glance.

47:22 GoCD's real power is in the visibility it provides over your end-to-end workflow.

47:27 You get complete control of and visibility into your deployments across multiple teams.

47:32 Say goodbye to release day panic and hello to consistent, predictable deliveries.

47:37 Commercial support and enterprise add-ons, including disaster recovery, are available.

47:42 To learn more about GoCD, visit talkpython.fm/gocd for a free download.

47:48 That's talkpython.fm/gocd.

47:51 Check them out.

47:52 It helps support the show.

47:53 So, your docs are in Python 2.

48:02 I want to encourage you to write them in Python 3 and tell a story.

48:06 So, when people were looking at the adoption of Python 3, maybe a year ago or something like this,

48:12 and I think around that time, Django switched its documentation to Python 3.

48:18 They didn't do anything else.

48:19 They just said, look, Python 3 here.

48:21 This is the way you do it in Python 3.

48:22 And, oh, it also supports Python 2.

48:24 And the usage of Python 3 on PyPI went up significantly, like some number of percent total because of that.

48:33 But my real question is, does it support Python 3 as well as Python 2?

48:39 What's the story around on the various versions?

48:41 Yes, it does.

48:42 Nice.

48:43 Yeah.

48:43 So, all the stuff that I've done with it worked totally fine, and I was playing with it in Python 3.

48:48 So, that's great.

48:49 So, I'm really fascinated by people working on successful open source projects that have businesses around them.

48:57 And so, can you talk just really briefly about your project and the editor and the business model around it?

49:05 We think that the editor could be helpful for startup companies and for companies which develop new projects.

49:15 Those people who we were talking to who would like to use Pony in their projects, we found that what they like in Pony is simplicity and that Pony allows them to save resources.

49:29 So, they can start fast.

49:31 They don't need dedicated backend engineer sometimes.

49:35 And they just can easily return to the project which was developed like a year ago and do some changes without actually looking through the manuals.

49:48 Because the query language is really simple and it allows to maintain projects with less resources.

49:59 We think that for such people, for such companies, having an editor would help them to build prototypes and to work on applications even faster.

50:09 Especially if they would be able to work on the same data schema together.

50:17 And this is one of the features we would like to add to our editor.

50:22 So, the idea is that it is software as a service.

50:26 And if you would like to create a diagram and share it with everyone, it is free.

50:33 And if you would like to keep it as a closed source diagram, then there is some price for this.

50:43 And it depends on how many diagrams you would like to have.

50:47 Sure.

50:47 You know, one thing I think is really interesting about this.

50:50 You talk about people getting started more quickly and helping new companies or new applications get up to speed.

50:57 You have an explore section here.

50:59 So, I can drop in and find things like you've got a corporate directory already modeled.

51:05 And you've got an e-commerce store modeled and a university modeled and things like that.

51:09 So, if I know that there's something in your explore area.

51:13 Some of these are created by you guys, but many of them are by others.

51:16 And I'm like, well, how exactly would I model this?

51:19 Let me just look and see what other people have done.

51:21 Right?

51:21 I could go and just start from there, right?

51:24 And copy that.

51:25 Yeah, exactly.

51:25 And maybe at some point, we will add an ability to deploy the schema to a cloud database and write logic right there.

51:37 Yeah, that's pretty interesting.

51:38 Is there a way to take and reverse the thing?

51:43 So, I've got the diagram which generates my Python.

51:46 Could I take my code that I've been working on for six months after starting with one of these and throw the Python in and get the diagram out?

51:55 Probably we should add that.

51:57 But at this point, the most people are asking about a reflection tool for the database.

52:03 So, when you can just get the log of the database, create table statements, and create a diagram based on that.

52:12 Yeah, that makes sense.

52:13 Which, I guess, is probably the most important one is you want to be exactly like your database and give me the Python classes to make that happen, right?

52:20 Right.

52:20 So, someone would like to work using Pony with a database, and they just don't want to create those classes or a diagram.

52:29 Yeah, if you've got a thousand tables, you probably don't want to spend time trying to model them again.

52:33 Just push the button and have that pop out, right?

52:36 That's cool.

52:36 All right.

52:37 Well, we're getting near the end of the show.

52:39 This has been really interesting.

52:40 I'm really fascinated, especially with the Pythonic style of Pony.

52:44 So, thanks for telling us all about it and giving us the backstory.

52:48 It's great.

52:49 Thank you, Michael, for having me.

52:50 Yeah, you bet.

52:51 So, before I let you go, though, I have a couple of questions for you.

52:54 There's over 90,000 packages on PyPI these days.

52:59 And you probably come across some that not everybody knows about.

53:01 What one would you like to recommend to audience?

53:03 Well, I love Flask.

53:04 Flask is definitely nice and great for building apps, especially for building APIs, I think.

53:10 Right.

53:10 We use it in our projects, and it's simple, and you can develop very fast using Flask.

53:18 So, that's why I like it.

53:19 All right.

53:19 Flask.

53:19 Great recommendation.

53:20 And if you're going to write some Python code, what editor do you open up?

53:23 PyCharm.

53:24 Nice.

53:26 Yeah.

53:26 A lot of the PyCharm team is based in St. Petersburg, along with you.

53:29 Yeah.

53:29 So, do you know some of the guys there?

53:31 Yeah, I do.

53:32 Actually, we met with them, and we were asking them to add support for Pony.

53:38 And we agree that when enough people will be asking about it, they will start working on

53:44 this.

53:44 All right.

53:45 Well, maybe this show will give them a little nudge, huh?

53:47 That's cool.

53:47 Any final call to actions for everyone?

53:49 Like, how do they get started with Pony and your project?

53:52 Please go to pony.rm.com and read the documentation.

53:56 Try Pony and give us feedback.

53:58 We have a Telegram group where people can discuss Pony features and ask questions or stack overflow.

54:07 And please give us some feedback so that we can make Pony even better.

54:13 All right, Alexei.

54:14 Thank you so much for being on the show.

54:15 It's been fun to talk to you.

54:16 Thank you, Michael.

54:17 It was a pleasure.

54:17 Yeah.

54:18 Bye.

54:18 This has been another episode of Talk Python to Me.

54:22 Today's guest has been Alexei Milashkovic.

54:25 And this episode has been sponsored by Rollbar and GoCD.

54:28 Thank you both for supporting the show.

54:30 Rollbar takes the pain out of errors.

54:33 They give you the context and insight you need to quickly locate and fix errors that might have

54:38 gone unnoticed until your users complain, of course.

54:41 As Talk Python to Me listeners, track a ridiculous number of errors for free at

54:45 rollbar.com slash Talk Python to Me.

54:48 GoCD is the on-premise, open-source, continuous delivery server.

54:53 Want to improve your deployment workflow but keep your code and builds in-house?

54:57 Check out GoCD at talkpython.fm/gocd and take control over your process.

55:03 Are you or a colleague trying to learn Python?

55:06 Have you tried books and videos that just left you bored by covering topics point by point?

55:10 Well, check out my online course, Python Jumpstart by Building 10 Apps at talkpython.fm/course

55:16 to experience a more engaging way to learn Python.

55:19 And if you're looking for something a little more advanced, try my Write Pythonic Code course at

55:24 talkpython.fm/pythonic.

55:27 Be sure to subscribe to the show.

55:29 Open your favorite podcatcher and search for Python.

55:31 We should be right at the top.

55:32 You can also find the iTunes feed at /itunes, Google Play feed at /play, and direct

55:38 RSS feed at /rss on talkpython.fm.

55:42 Our theme music is Developers, Developers, Developers by Corey Smith, who goes by Smix.

55:46 Corey just recently started selling his tracks on iTunes, so I recommend you check it out

55:51 at talkpython.fm/music.

55:53 You can browse his tracks he has for sale on iTunes and listen to the full-length version

55:57 of the theme song.

55:58 This is your host, Michael Kennedy.

56:00 Thanks so much for listening.

56:02 I really appreciate it.

56:03 Smix, let's get out of here.

56:07 with my voice.

56:08 There's no norm that I can feel within.

56:09 Haven't been sleeping.

56:11 I've been using lots of rest.

56:12 I'll pass the mic back to who rocked it best.

56:15 Developers, Developers, Developers, Developers, Developers, Developers, Developers, Developers,

56:27 .

56:27 you you