how about writing a reusable database wrapper class?
you can generalize queries : query(sql, args[]):
and for specific purposes
addName("name"): query('insert into table ..., "name") .. other functions(..) : query('..','')
What ORMs have taught me: just learn SQL
141–150 of 245 posts
Re: What ORMs have taught me: just learn SQL
#142In Django, for me the killer feature of the ORM is that it's (mostly) database agnostic, which means that you can use Postgres in production and in-memory sqlite when testing, which makes testing a gajillion times faster. If you start writing custom SQL you have to introduce horrible bodges to work with whatever database is in use.
Have you run your (mostly) database agnostic app at a large scale? When your app is running at scale, did you require any custom SQL or non-standard to the ORM stuff to get it running well? In my experience with Rails, the "database agnostic" features are for prototyping, and by the time you're a real app moving towards some idea of scale, there is nothing "agnostic" about your ORM and database code...
I've combined Postgres views and set returning functions in rails and had a lot of success with good performance and good maintainability, too. No custom SQL required in the application itself.
Re: What ORMs have taught me: just learn SQL
#143Here's a thought experiment. Lets say we lived in a world without SQL and the default way to talk to DB's was through an ORM.... And then someone came and said: "I created this concise and super flexible language for querying data." Would people want it? I think they would, and we'd see tons of articles about vast forests of objects being replaced by small snippets of SQL.
When I look at SQL through the lenses of hindsight I see a language that's not amenable to IDEs (it's harder to autocomplete columns if you must write those before the table name, as an example), and has questionable and verbose syntax. While straight relational algebra is actually quite readable, despite all the efforts of most the anti-ORM crowd, at the end of the day the business logic that works on business objec…
I question the claim that reporting is a small part of what interactions with the business domain is about. For many shops, I'd wager the opposite claim is true, honestly.
Of course, there is a very thin line between "reporting" and "transforming to better deal with explorational algorithm." That is to say, my assertion is that it is when folks try and come up with "one true model" of their data that thing start to suck. Whether in ORM land or straight SQL land. Or just flat out "in memory" land, honestly.
Re: What ORMs have taught me: just learn SQL
#144Hibernate can almost be used as the definition of pernicious http://www.merriam-webster.com/dictionary/pernicious "causing great harm or damage often in a way that is not easily seen or noticed" The most pernicious thing about Hibernate is the "caching feature" (read as "time-bomb") layer that doesn't write objects right away when modifying an object. So instead of immediately seeing changes in your database, they gr…
So true!
With Hibernate there are a lot of things happen under the hood. And all you can do is either struggle with Hibernate, or hope that those under-the-hood-things won't have a strong impact on your app's performance.
Re: What ORMs have taught me: just learn SQL
#145Earlier quoted context omitted.
For the last three years or so, I've been telling anyone who asked that ORM is an antipattern, to be avoided at all costs. I've settled into wrapping all queries in classes, with any parameters exposed as public properties. The SQL is written inside the class, essentially in a template. When necessary, the generated query can change based on the values assigned to the properties. All the mechanics of how the query is…
> I NEVER want to go back to ORM Except that you just invented your own ORM. Think about it: you are encapsulating SQL data into classes, in other words, mapping relational data to objects. That's an ORM.
You've just broadened the definition of ORM so that any SQL abstraction layer in an OO language becomes an ORM.
That's a rather nonstandard usage.
Re: What ORMs have taught me: just learn SQL
#146Earlier quoted context omitted.
For the last three years or so, I've been telling anyone who asked that ORM is an antipattern, to be avoided at all costs. I've settled into wrapping all queries in classes, with any parameters exposed as public properties. The SQL is written inside the class, essentially in a template. When necessary, the generated query can change based on the values assigned to the properties. All the mechanics of how the query is…
> I NEVER want to go back to ORM Except that you just invented your own ORM. Think about it: you are encapsulating SQL data into classes, in other words, mapping relational data to objects. That's an ORM.
I think it's pretty clear that (s)he's talking about ActiveRecord.
Re: What ORMs have taught me: just learn SQL
#147Earlier quoted context omitted.
For the last three years or so, I've been telling anyone who asked that ORM is an antipattern, to be avoided at all costs. I've settled into wrapping all queries in classes, with any parameters exposed as public properties. The SQL is written inside the class, essentially in a template. When necessary, the generated query can change based on the values assigned to the properties. All the mechanics of how the query is…
> I NEVER want to go back to ORM Except that you just invented your own ORM. Think about it: you are encapsulating SQL data into classes, in other words, mapping relational data to objects. That's an ORM.
I believe the difference here is that one is an ORM framework, while the other is just objects bound to a database.
ORM frameworks usually allow you to drop down to SQL. But then you're stuck in that terrible world of depending on poorly-documented and soon-to-be-deprecated (or already deprecated) internals. Which are pretty much guaranteed to not fit what you really need anyway. Then you have to bridge your hack objects with the "proper" ORM objects. Your crufty hack objects will probably never be seen as first-class citizens in ORM land, forever banished to edge case hell.
Re: What ORMs have taught me: just learn SQL
#148For those of us on Java, I've grown to love http://jdbi.org/ . JDBI has a lot of features that are convenient (e.g. auto-mapping of columns to a POJO), but synthesizes DAOs for you from interfaces annotated with SQL queries, e.g. @RegisterMapperFactory(BeanMapperFactory.class) public interface TripDAO { @SqlQuery("SELECT trip_start AS start, MAX(timestamp) AS end FROM location_updates WHERE trip_start = :start GROUP…
I've been using JDBI for a new project for about a month or so and I like it a lot as well. I will say that the documentation is a bit sparse though, or at the least hard to find by digging through blog posts. When I figure out how to do what I'm trying to do however, I love it.
For example, it took me a while to find out that @CreateSqlObject was a thing and that it solved any hackery I was trying to do to have my DAOs reference one another. Or if you google "jdbi transactions," you aren't led to a page that actually shows you how to use @Transaction. I feel like the only thing holding back widespread use is some better documentation.
Re: What ORMs have taught me: just learn SQL
#149Earlier quoted context omitted.
Been using http://commons.apache.org/proper/commons-dbutils for the same purpose. Works well when I don't need the slede-hammer a full ORM-framework can be. Will look into JDBI as well next time.
I've found the nirvana with MyBatis https://code.google.com/p/mybatis/ I've benchmarked it and it adds roughly a 3% on top of raw JDBC, and allows different styles of usage. You can have your pojos annotated and get mapping for free, or (what I like) you can extract your SQL queries in XML files, name them and refer them from code with sql.insert("namedQuery", params); It is super smart when it comes to mapping/aggre…
I used mybatis at a former company. I liked it, but on the spectrum of ORM-like frameworks, it had just enough non-code configuration behind it that junior developers, or developers who aren't working in that type of code very often, struggle with how to get new data objects persisted. In my opinion, this stemmed from using XML files to define the mappers and queries. Most people just never knew where to look for that stuff, or exactly how it should be used.
In the end, I try to pick the option that is easiest to read and understand. I've had a terrible time with JPA/Hibernate (I hate that the ORM ends up affecting your schema at all), I had a good experience with Mybatis, and so far I'm really liking JDBI (although see my other comment here about documentation). I don't want raw JDBC, but JDBI seems to be the right tradeoff of power/ease of understanding. I think that the annotated use of mybatis would get you this as well, but I haven't used it.
Re: What ORMs have taught me: just learn SQL
#150Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…
What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries. It's actually quite good. I like it. There were some spots that things got hairy though, and the code was pretty hard to follow. I don't fault SQLAlchemy here, but I wrote the query in SQL and it was simpler to work with. SQLAlchemy is absolutely on the right track, but using the core doesn't diminish the fac…
So you didn't use SQLAlchemy ORM at all, yet you wrote a whole article about how ORMs "don't work", naming SQLAlchemy (strongly implying the ORM) as an example... if so, it would explain why all the complaints you have about ORMs seem to indicate a misunderstanding of the SQLAlchemy ORM ("attribute creep": query for individual attributes or use `load_only()`, `deferred()`, or other variants; "foreign keys": the ORM only selects from the relational model you've created, if your model has N number of foreign keys and the objects you're querying from span M of them, that's how many it will use, there is no "overuse" or "underuse" of foreign keys possible; "data retrieval": SQLAlchemy's Query object maps to SQL joins in a fully explicit fashion, no bending over necessary (see http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#quer... ); "Dual schema dangers" - use metadata.create_all() in one direction, or metadata.reflect() in the other, the "dual schema" problem is only in systems like Hibernate that don't offer such features (and actually it does, just not as easily); "Identities" - manual flushing and hand-association of primary key values to foreign keys is not necessary, use relationship(); "transactions"- ORMs don't create this problem and only help to solve it by providing good transactional patterns and abstractions).
I'd appreciate if you amend your article to clarify that you only used SQLAlchemy Core, if this is in fact the case. Your key point that one needs to know SQL in order to use an ORM is absolutely true. However, the value of the ORM is not that it hides awareness of relational databases and SQL; it is in that of automating the task of generating database-specific SQL as well as that of mapping SQL statement execution and result sets, specific to the database driver in use, to object-oriented application state, and keeping these two states in sync without the need for explicit and inconsistent boilerplate throughout the application. I discuss this in many of my talks (see http://www.sqlalchemy.org/library.html#talks).
If you worked in soda bottling company, you probably still know how to fill a bottle of soda by hand. It's the complex machinery that does this automatically which allows this task to scale upwards dramatically. Configuring and using this machinery wouldn't make much sense if you didn't understand its fundamental task of putting soda in bottles, however. The situation is similar when using an ORM to automate the task of generating SQL and mapping in-application data to the rows it represents. Removing the need for "knowledge" has nothing to do with it. The goal instead is to automate work that is tedious and repetitive on a manual scale.