If you like ORM's check out SQLAlchemy, it is pretty damn awesome.
But I feel it does what a framework should : solves 95% of the cases well, and gives a backdoor for the other 5% (the "extra" query). I might be heavily influenced on the generally good experiences I had with the models too though.
Thanks for the recommendation, I'll check it out.
>City("Cambridge","New Zealand").save()
I had no idea that New Zealand was a city! Can I ask how the cities of Cambridge and New Zealand are related, though? Is one like the billing city and the other the shipping city?
>City(name="Cambridge", country="New Zealand").save()
Doesn't change the point about SQL.
So now you have me going and editing tested, working code just for my personal preference and digging through code that I may or may not be authorized to view. Do you have any other best practices to share?
Spoken like someone who never had to deal with recordsets of 40 different values, accessed by position, passed around various functions... ORMs can be a problem, in the sense that complex queries may be harder or impossible to express, and there can be a cost in terms of performance. Snark over keyword arguments, which are completely orthogonal to ORMs, is both rude and misplaced.
Besides, keyword arguments do solve the problem. Also, correctly named variables ( City(city_name,country_name).save() ) and , if need be, some comments all solve this issue.
Furthermore, ORM queries a much more readable, and the OP's example is bad; Django ORM queries use keyword arguments all the time.
I'm using one called Activate (http://activate-framework.org/) in a few of my latest projects and it's really simplified things. There's also Squeryl, ReactiveMongo, Slick...
People that hate leaky abstractions.
People that understand that having the full power of SQL is better than a mediating layer -- as your needs get more evolved than inserting a simple row.
People that are not afraid to learn SQL, and know that it's exactly a DSL built for relational data.
People that trust an industry standard DSL more than some ad-hoc DSL built on top of it.
People that know they can reuse the very same SQL across many different contexts, from their code in multiple languages to the rdbms command line client itself, whereas code for an ORM not so much.
SQL is already a leaky abstraction over the DBMS system's particular optimizations, or haven't you heard about people rewriting queries for Postgres or Oracle because of the engine's peculiarities?
> People that understand that having the full power of SQL is better than a mediating layer -- as your needs get more evolved than inserting a simple row.
There's nothing incompatible between using SQL for that 2% of cases and an ORM for the rest
> People that are not afraid to learn SQL, and know that it's exactly a DSL built for relational data.
I don't think anyone's afraid of SQL, but I sure as hell would be afraid of maintaining hundreds of plaintext SQL queries every time I add a new column.
> People that trust an industry standard DSL more than some ad-hoc DSL built on top of it.
All SQL implementations have ad-hoc additions that are platform-specific, and most ORMs work pretty damn well.
> People that know they can reuse the very same SQL across many different contexts, from their code in multiple languages to the rdbms command line client itself, whereas code for an ORM not so much.
This is BS. I have to touch a substantially larger amount of code when refactoring SQL than when touch ORM queries.
Seriously, have you even used a decent ORM?
Which doesn't detract from my argument. Since leaky abstractions are bad why on earth would you double or triple them, building another abstraction (ORM) on the first one (SQL)?
>There's nothing incompatible between using SQL for that 2% of cases and an ORM for the rest
For serious development you'll find it's not just "2% of cases". YMMV. And again, there is something incompatible: certain ORMS make it difficult or prevent it altogether, and even if they allow it you end with a hybrid ORM/SQL codebase.
>I don't think anyone's afraid of SQL
I've met plenty of people who do. Some even think it's alien, the same way some people wouldn't touch LISP syntax with a bargepole.
>but I sure as hell would be afraid of maintaining hundreds of plaintext SQL queries every time I add a new column
If you need to do that, then you're doing it wrong.
>All SQL implementations have ad-hoc additions that are platform-specific, and most ORMs work pretty damn well.
"Pretty damn well"? As in generating slow ass queries, needing 300 pages of documentation (I'm thinking of Hibernate) for stuff you can do in 5 minutes in SQL, etc?
Here's a gem from Django's 1.6 release notes (that somebody praised its ORM in the comments above): "The Model.save() method now tries to directly UPDATE the database if the instance has a primary key value. Previously SELECT was performed to determine if UPDATE or INSERT were needed".
>This is BS. I have to touch a substantially larger amount of code when refactoring SQL than when touch ORM queries.
Have you even tried to understand what it says? You can take your SQL and use it in another language, or in the DB CLI etc. Your ORM code is tied to your original codebase. No using SQL Alchemy if you switch to Java.
>Seriously, have you even used a decent ORM?
I've used SQL Alchemy, Hibernate and Django's ORM.
Never found a decent one.
But I know that connect-the-dots, Visual Basic style developers swear by them.
I see nothing wrong with this; almost all tables have automatic sequences for PK insertion. The proper idiom for creating a new instances is the model.objects.create() method, and save() supports the force_insert argument. This is a valid optimization and reflects the use case of people who are using the framework.
I've been developing web apps for over half a decade, some of them pretty massive, most for regular businesses. The number of times I have needed to drop to SQL I can count with my hands outside of the limited domain of reporting.
If you need to use SQL for regular old CRUD behavior, either your models are not being properly normalized or you're using a shitty ORM, and neither SQLAlchemy nor Django are bad ORMs. If your problem is that your domain object is spread across 15 tables and you actually need all those joins, there's always methods that invoke the necessary SQL and return the encapsulated objects, but like I said, you have bigger problems there than worrying about your query abstractions.
RDB managment is the biggest snorefest in all of software development. There are some times where you have to figure out some crazy select or whatever, but for the most part it's annoying, easy to break, and it takes forever. Django lets me not think about it at all (granted, I'm not writing crazy search things either).
When I started trying out Play Framework (for Scala, because I love type checking) I was absolutely shocked at the lack of an ORM. DB managment is the most boilerplate-y aspect of writing some CRUD, and Play decides not to help you with it.
Their arguments are about "control" and how somehow "SQL is the best DSL" (http://www.playframework.com/documentation/2.0.1/ScalaAnorm).
>val id: Int = SQL("insert into City(name, country) values ({name}, {country}") .on("Cambridge", "New Zealand").executeInsert()
Who could possibly think this "DSL" is 'better' than something like
>City("Cambridge","New Zealand").save()
/endrant
I really like ORMs, and django has the distinction of having the best ORM in my book. I sincerely hope Django would never be reduced to Flask, because I care a lot more about having DB management work without me ripping my hair out than having routes or being able to put everything in one file.
EDIT: for context, I don't ever have to do complicated things with databases, mainly tedious ones, which is probably why my vision of the world might be slightly different than yours.