5 ms·

"Let’s dispel with the myth that ORMs make code cleaner. Join the embedded-SQL movement and discover a much more readable, much more straightforward way to quer

by zzzeek 8y ago

"Let’s dispel with the myth that ORMs make code cleaner. Join the embedded-SQL movement and discover a much more readable, much more straightforward way to query databases." Any argument whatsoever for this? Would love to see an example of how when you do away with the whole object persistence layer, and you just have a SQL string (which ORMs allow you to do anyway), how do you get your parameters over, how do you get your rows back, how do you manage your transactional scope, how do you get your rows in and out of your objects? all of which has nothing to do with a SQL string. Well let's read on: "Erik Bernhardsson... is the CTO at Better, which is a startup changing how mortgages are done. I write a lot of code, some of which ends up being open sourced, such as Luigi and Annoy. " Open source stuff! Let's go there and see an example of this "write raw SQL and don't use any persistence libraries and your code is readable and straightforward", because nobody ever seems to actually want to illustrate this and how they don't end up writing their own ORM anyway, and we see, oh https://github.com/spotify/luigi/blob/master/luigi/db_task_history.py#L48 https://github.com/spotify/luigi/blob/master/luigi/db_task_h..., it's SQLAlchemy ORM. Foiled again in my search to see this elusive super clean and simple raw SQL with no ORM that doesn't reinvent an ORM anyway. Which is the real "myth" ?

TimJYoung 8y ago
"Would love to see an example..." Delphi has always had a default non-ORM dataset layer (there are ORMs for it), and it works like this for queries: 1) There's a base TDataSet class (Object Pascal uses "T" to denote "Type") that handles all abstract column and row storage and manipulations, with no pre-disposed notion of how to actually load such rows. Dataset columns are abstracted away also, and provide a lot of automatic type conversions and functionality like BLOB column stream access. Having this base class allows disparate descendant classes to easily interact with each other without knowing the details of how the data got there. All query objects descend from this base class, and fill in all of the functionality for actually interacting with the database server/engine. 2) For query objects, the SQL string gets assigned to a query object property (typically called "SQL"). Parameters are specified as named parameters by using colon notation (:Parameter) instead of "?". During this string assignment, a property setter method parses the SQL string and automatically sets up the parameters as a collection that is another property of the query object. These parameters do not have any type information assigned to them at this point. The developer can choose to just manually assign values to the parameters using type-specific parameter properties at this point. Any type differences are managed by the underlying object, so assigning an integer to a string parameter will result in an automatic conversion. 3) If you manually prepare the query object using a Prepare method, then the SQL is sent over to the database server to be prepared and any type information about the parameters is sent back from the database server to be assigned to the parameters collection (depends upon the database server). If you don't manually prepare the query, then it is automatically prepared during the query execution (4). 4) The SQL is executed using an Execute or Open method. If the SQL is a SELECT statement, the query object is automatically populated with the rows and the rows are managed using the base TDataSet functionality. There are methods for navigation, updating, etc. and updates of non-directly-updateable result set cursors are managed by the query object. Query objects are linked to database objects, and the database objects manage the transactional scope for updates. You can also use such database objects to execute one-off SQL statements where the only thing you're interested in is the result set row count or the affected row count. The interesting part is how Delphi handles static-typing of result set columns in your application. In the IDE, you can right-click on any query object and have it automatically populate "persistent" column objects whose class type reflects the underlying result set column type. This means that if you try to access or assign the column object's raw Value property, this access/assignment is subject to type enforcement by the compiler. However, you are free to use other properties that perform type conversion, in which case you'll get a runtime exception if the type conversion fails for any reason. You can also assign names to these column objects, allowing you to write easy-to-read code like this: OrderCustomerID.Value := 1000; So, you can get a lot of the benefits of an ORM using an architecture like this without having to sacrifice the flexibility of manually-constructed SQL statements. A lot of this depends upon the peculiarities of the Delphi component library and IDE, though, so how transferrable this to other environments is an open question.
- TimJYoung 8y ago
  I forgot to mention: you can also do cool stuff like link query objects together so that when you navigate in a parent query, the parameterized child query object is re-executed with the new parent query column values. This way you avoid N+1 queries by only executing queries when you actually need to see the rows for the child query object.
bognition 8y ago
Check out Rosetta. Its what we use at HubSpot. https://github.com/HubSpot/Rosetta https://github.com/HubSpot/Rosetta Its by far the best Object <--> Sql layer I've ever used. You manage your objects in Java like you normally would and then Rosetta maps your objects to columns in your SQL DB using Jackson. This means you can write plain ole SQL for very clean simple code. When it comes time to get data out of the DB it again uses Jackson to map the results back to objects in Java. I was super skeptical at first, but it makes reading/writing data out of a SQL database super simple, while encouraging very simple explicit sql.
christophilus 8y ago
I’ve used Clojure’s hugsql[0] and C#’s dapper and petapoco[1]. I rather liked the result. You don’t generally build dynamic strings (though sometimes you do). You generally write parameterized SQL in a fairly straightforward way with these. Honestly, SQL + Clojure is a great combo. Datalog + Clojure is even nicer. [0] https://github.com/layerware/hugsql https://github.com/layerware/hugsql [1] https://github.com/CollaboratingPlatypus/PetaPoco https://github.com/CollaboratingPlatypus/PetaPoco
gregmac 8y ago
I use Dapper [1] at work, along with lots of hand written SQL. All the SQL is in a single data access library, which is easy to both mock and test in isolation (there are several tests that run against a real database mainly for some of the more complex queries). You write regular SQL, can easily parametrize everything, and get collections of objects back. And it's very fast. [1] https://github.com/StackExchange/Dapper https://github.com/StackExchange/Dapper
- manigandham 8y ago
  That's an ORM.
daveFNbuck 8y ago
To be fair, he didn't write the code you're linking to. Luigi has a lot of contributors. I was a pretty heavy contributor/user for years and I was never able to get the task history feature to work.
- zzzeek 8y ago
  sure. but seriously. "Can we just dispel with this myth about ORMs?" What myth? can you provide any evidence or argument of any kind? How about if I said, "Can we just dispel with this myth that Erik Bernhardsson has any idea what he is talking about"? (to make it clear, this would not be a fair statement either because i know nothing about this person) and just ended the blog post there. Because I actually looked at his own open source projects for evidence of this deep wisdom he has and found none. not a very high quality post.
  - daveFNbuck 8y ago
    He provided an argument. It's the 4th paragraph of the blog post, ending with "and I end up with bloated higher level data classes rather than easy to understand tuples or dicts that contain the data in a dumb simple format that is trivial to introspect."
    - zzzeek 8y ago
      That is in fact not an argument about ORMs and especially not about DSLs, that would be an argument against object oriented programming overall. Where again, let's see the examples. When you write enough tuple business logic in a non-FP language you get into a new set of problems with code organization. And you still have to marshal those tuples in and out of SQL statements in a way that makes sense vs your transactional scope and there's a lot to think about there.
      - daveFNbuck 8y ago
        It's about how ORMs result in less clean code by forcing a clunky generic OOP paradigm on any DB interaction. You may not agree with this argument, but it is an argument.
ccleve 8y ago
Try Norm, https://github.com/dieselpoint/norm https://github.com/dieselpoint/norm. I wrote it to solve one problem: get rid of all the boilerplate that JDBC requires. Beyond that, it maps tables to pojos, or to Maps and Lists, if you prefer. Minimalism is the way to go: https://blog.dieselpoint.com/a-minimalist-good-enough-approach-to-object-relational-mapping-64df9798b276 https://blog.dieselpoint.com/a-minimalist-good-enough-approa...

by zzzeek 8y ago

TimJYoung 8y ago

"Would love to see an example..." Delphi has always had a default non-ORM dataset layer (there are ORMs for it), and it works like this for queries: 1) There's a base TDataSet class (Object Pascal uses "T" to denote "Type") that handles all abstract column and row storage and manipulations, with no pre-disposed notion of how to actually load such rows. Dataset columns are abstracted away also, and provide a lot of automatic type conversions and functionality like BLOB column stream access. Having this base class allows disparate descendant classes to easily interact with each other without knowing the details of how the data got there. All query objects descend from this base class, and fill in all of the functionality for actually interacting with the database server/engine. 2) For query objects, the SQL string gets assigned to a query object property (typically called "SQL"). Parameters are specified as named parameters by using colon notation (:Parameter) instead of "?". During this string assignment, a property setter method parses the SQL string and automatically sets up the parameters as a collection that is another property of the query object. These parameters do not have any type information assigned to them at this point. The developer can choose to just manually assign values to the parameters using type-specific parameter properties at this point. Any type differences are managed by the underlying object, so assigning an integer to a string parameter will result in an automatic conversion. 3) If you manually prepare the query object using a Prepare method, then the SQL is sent over to the database server to be prepared and any type information about the parameters is sent back from the database server to be assigned to the parameters collection (depends upon the database server). If you don't manually prepare the query, then it is automatically prepared during the query execution (4). 4) The SQL is executed using an Execute or Open method. If the SQL is a SELECT statement, the query object is automatically populated with the rows and the rows are managed using the base TDataSet functionality. There are methods for navigation, updating, etc. and updates of non-directly-updateable result set cursors are managed by the query object. Query objects are linked to database objects, and the database objects manage the transactional scope for updates. You can also use such database objects to execute one-off SQL statements where the only thing you're interested in is the result set row count or the affected row count. The interesting part is how Delphi handles static-typing of result set columns in your application. In the IDE, you can right-click on any query object and have it automatically populate "persistent" column objects whose class type reflects the underlying result set column type. This means that if you try to access or assign the column object's raw Value property, this access/assignment is subject to type enforcement by the compiler. However, you are free to use other properties that perform type conversion, in which case you'll get a runtime exception if the type conversion fails for any reason. You can also assign names to these column objects, allowing you to write easy-to-read code like this: OrderCustomerID.Value := 1000; So, you can get a lot of the benefits of an ORM using an architecture like this without having to sacrifice the flexibility of manually-constructed SQL statements. A lot of this depends upon the peculiarities of the Delphi component library and IDE, though, so how transferrable this to other environments is an open question.

TimJYoung 8y ago
I forgot to mention: you can also do cool stuff like link query objects together so that when you navigate in a parent query, the parameterized child query object is re-executed with the new parent query column values. This way you avoid N+1 queries by only executing queries when you actually need to see the rows for the child query object.

bognition 8y ago

Check out Rosetta. Its what we use at HubSpot. https://github.com/HubSpot/Rosetta https://github.com/HubSpot/Rosetta Its by far the best Object <--> Sql layer I've ever used. You manage your objects in Java like you normally would and then Rosetta maps your objects to columns in your SQL DB using Jackson. This means you can write plain ole SQL for very clean simple code. When it comes time to get data out of the DB it again uses Jackson to map the results back to objects in Java. I was super skeptical at first, but it makes reading/writing data out of a SQL database super simple, while encouraging very simple explicit sql.

christophilus 8y ago

I’ve used Clojure’s hugsql[0] and C#’s dapper and petapoco[1]. I rather liked the result. You don’t generally build dynamic strings (though sometimes you do). You generally write parameterized SQL in a fairly straightforward way with these. Honestly, SQL + Clojure is a great combo. Datalog + Clojure is even nicer. [0] https://github.com/layerware/hugsql https://github.com/layerware/hugsql [1] https://github.com/CollaboratingPlatypus/PetaPoco https://github.com/CollaboratingPlatypus/PetaPoco

gregmac 8y ago

I use Dapper [1] at work, along with lots of hand written SQL. All the SQL is in a single data access library, which is easy to both mock and test in isolation (there are several tests that run against a real database mainly for some of the more complex queries). You write regular SQL, can easily parametrize everything, and get collections of objects back. And it's very fast. [1] https://github.com/StackExchange/Dapper https://github.com/StackExchange/Dapper

manigandham 8y ago
That's an ORM.

daveFNbuck 8y ago

To be fair, he didn't write the code you're linking to. Luigi has a lot of contributors. I was a pretty heavy contributor/user for years and I was never able to get the task history feature to work.

zzzeek 8y ago
sure. but seriously. "Can we just dispel with this myth about ORMs?" What myth? can you provide any evidence or argument of any kind? How about if I said, "Can we just dispel with this myth that Erik Bernhardsson has any idea what he is talking about"? (to make it clear, this would not be a fair statement either because i know nothing about this person) and just ended the blog post there. Because I actually looked at his own open source projects for evidence of this deep wisdom he has and found none. not a very high quality post.
- daveFNbuck 8y ago
  He provided an argument. It's the 4th paragraph of the blog post, ending with "and I end up with bloated higher level data classes rather than easy to understand tuples or dicts that contain the data in a dumb simple format that is trivial to introspect."
  - zzzeek 8y ago
    That is in fact not an argument about ORMs and especially not about DSLs, that would be an argument against object oriented programming overall. Where again, let's see the examples. When you write enough tuple business logic in a non-FP language you get into a new set of problems with code organization. And you still have to marshal those tuples in and out of SQL statements in a way that makes sense vs your transactional scope and there's a lot to think about there.
    - daveFNbuck 8y ago
      It's about how ORMs result in less clean code by forcing a clunky generic OOP paradigm on any DB interaction. You may not agree with this argument, but it is an argument.

ccleve 8y ago

Try Norm, https://github.com/dieselpoint/norm https://github.com/dieselpoint/norm. I wrote it to solve one problem: get rid of all the boilerplate that JDBC requires. Beyond that, it maps tables to pojos, or to Maps and Lists, if you prefer. Minimalism is the way to go: https://blog.dieselpoint.com/a-minimalist-good-enough-approach-to-object-relational-mapping-64df9798b276 https://blog.dieselpoint.com/a-minimalist-good-enough-approa...