Reasons to use an ORM
Benefits of an ORM
A good ORM should make a developer's life easier. It should take care of the tedious things, like escaping values. An ORM can also have a more compact syntax than SQL. This is most obvious with joins.
query = Band.select(Band.name, Band.genre.name) query.__str__() SELECT name, genre.name FROM band JOIN genre ON band.genre = genre.id
One of the most useful things an ORM comes bundled with is migrations. Handling migrations manually can be tedious and error prone.
ORMs often include other tools and features which make a developer's life easier. Examples include test runners, data fixtures etc.
Passing around partial queries
With Piccolo, you can pass around queries, and keep on chaining methods onto it.
query = Band.select(Band.name) if rock == True: query = query.where(Band.genre == 'rock') results = await query.run()
Doing this with raw SQL strings quickly becomes unmanageable.
Downsides of an ORM
ORMs aren't without their issues.
When you use an ORM, there is inevitable extra overhead in generating the SQL.
You'll sometimes hear people complain that an ORM generates inefficient SQL. This is usually only for very complex queries.
There's nothing wrong with writing raw SQL, but with Piccolo it means you don't have to write it 90% of the time.
Piccolo also makes it easy to see the SQL being executed - just print any query.
Can be tedious to learn
A lot of ORMs have their own terminology which doesn't match closely to SQL.
Over time, learning an ORM can feel tedious - you know SQL, but you're having to re-learn concepts over and over for each ORM you use.
Some database features aren't available
The Piccolo ORM covers the most common interactions an app will have to make with a database.
In cases where it's not possible, you can just drop down into raw SQL.
Trying to encapsulate every possible database features within an ORM is very challenging, and can lead to an unmanageable code base.
Posted on: 1 Oct 2018
Have any comments or feedback on this post? Chat with us on GitHub.