

Ask HN: How does one write clean database access code without an ORM? - superuser2

Many on HN have branded ORMs harmful, suitable only for toy projects where performance doesn&#x27;t matter and complexity is low. I understand that hand-crafted SELECTs can be more efficient for complex queries, and when things get hairy with an ORM I almost always use its find_by_sql (or similar) functionality.<p>But a lot of HNers say ORM should be avoided entirely, and I am inclined to believe there&#x27;s some truth to that. I&#x27;m fairly comfortable with SQL and know where to look to learn more, so that side&#x27;s not a problem for me. But I have a couple of questions:<p>1) Why shouldn&#x27;t I use my ORM for the simple CRUD parts of an application, and only reach for raw SQL when doing complex SELECTs?<p>2) How does one write clean, efficient, maintainable code for dealing with results of SQL queries? Am I supposed to be iterating over row.next to fill an array and identifying columns by numerical array indices like when I first learned to write spaghetti PHP, or what? Should I be using these all through my business logic, or am I supposed to invent my own crude ORM for that?<p>3) Very smart programmers have asserted that relational data is not object-oriented and we shouldn&#x27;t try to make it object oriented. But there are so many cases where Django ORM and ActiveRecord idioms just seem perfectly natural, and anything involving the words Cursor and ResultSet don&#x27;t. I understand that raw SQL can shine in terms of specifying the query, but what is an example of a case where it&#x27;s cleaner to process database results manually?
======
bhauer
I prefer what I tend to call "micro ORMs," for lack of a more formal
classification. From my point of view, a micro ORM does not attempt to fully
replicate the entire breadth of a data model in code--that is, it does not
automatically traverse relationships; does not enforce the constraints of the
data model; does not require that your object model align with your data
model.

Often, a micro ORM will allow you to quickly construct a list of objects from
the resultset of an arbitrary SQL query and provide some programmatic way to
handle relationships.

I don't think the performance penalty of high-performance micro-ORMs is great
enough to justify avoiding their use on performance grounds [1] (see ORM
column at right of charts). In the linked test, several micro-ORMs fair quite
well, and incidentally, Wicket is using Hibernate, which I consider a full
ORM. Not my particular cup of tea, but just goes to show ORMs are not
necessarily a huge performance penalty.

Edit: Using our test implementation [2] as an example of what I mean, our in-
house micro ORM will fetch World objects from a World table as so:

    
    
        @Entity
        public class World
        {
          private int id;
          private int randomNumber;
    
          public int getId() { return id; }
          public void setId(int id) { this.id = id; }
      
          public int getRandomNumber() { return randomNumber; }
          public void setRandomNumber(int randomNumber) { this.randomNumber = randomNumber; }
        }
    
        public class HelloHandler 
        // ...
        {
          @PathSegment
          public void multipleQueries()
          {
            // ...
            for (int i = 0; i < queries; i++)
            {
              worlds[i] = store.get(World.class, random.nextInt(DB_ROWS) + 1);
            }
            // ...
          }
        }
    

I've seen similar approaches in other micro ORMs. "Put" and other operations
are generally provided, but composing joins using nothing by Java code? That's
what a full ORM does.

To answer your questions:

The chief advice I would provide for writing clean database access code
without an ORM is to use prepared statements as much as possible. Doing so
allows you to avoid the potentially disastrous outcome of not correctly
escaping user-provided data.

1\. I wouldn't agree with the premise of the question. I _would_ use an ORM
for simple CRUD and then use an ORM's "build objects from a resultset" feature
for complex SELECTs.

2\. I think you do want at least a micro ORM to deal with this scenario. To be
clear, a micro ORM would typically automate the work of constructing objects
from a resultset using reflection (or similar) but probably doesn't come with
much baggage (no external configuration files and no attempt to replicate the
full complexity of the data model).

3\. In my experience, cursors and read-write resultsets are not as high-
performance as one might expect. If you're considering using those features in
order to gain performance, I'd encourage you to do some simple tests to
confirm they yield the performance benefit you are being promised.

[1]
[http://www.techempower.com/benchmarks/#section=data-r6&hw=i7...](http://www.techempower.com/benchmarks/#section=data-r6&hw=i7&test=db)

[2]
[https://github.com/TechEmpower/FrameworkBenchmarks/blob/mast...](https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/gemini/Source/hello/home/handler/HelloHandler.java)

------
rpedela
You could try using a SQL builder which should remove a lot of the boilerplate
code for handling errors, SQL injection, etc. There are several and I do not
have any recommendations, but here is one for example:
[https://github.com/brianc/node-sql](https://github.com/brianc/node-sql). You
are still writing SQL, but the code should be a lot cleaner.

------
ibstudios
Here is a great post:[http://stackoverflow.com/questions/494816/using-an-orm-
or-pl...](http://stackoverflow.com/questions/494816/using-an-orm-or-plain-sql)

