4 ms·

I am under the impression that ORM are not helpful. I am in particular not sure why people still use them. I think that part of the response lies in the fact th

I am under the impression that ORM are not helpful. I am in particular not sure why people still use them. I think that part of the response lies in the fact that people don't want to learn SQL, at least that was my position before reading about the subject. Now that I know SQL and knowing that correctly using an ORM can not be done without understanding the generated SQL, I am wondering why ORMs are very popular. ORM seems to me like a dangerous indirection. Nowdays I use aiohttp (a python asyncio http framework) with psycopg2 (via aiopg) on a rather large application with only a serialization/validation framework named Colander. I don't feel the need for an ORM. It does the job. What I am doing wrong?

PaulHoule 9y ago
I felt that ORM systems were negative 15 years ago. They have improved quite a bit since then. An increasing number of competitive frameworks have come out and the incumbent frameworks have improved. The situation is much better than when that "Vietnam" article came out. I think many people use a lightweight ORM such as Colander and if you are happy with that, that is great. These days though I am using SQL very differently than the old days. I used to use MySQL, PostgreSQL and sometimes Microsoft SQL Server but lately if I use SQL it is SQLite. Today I am much likely to use some kind of graph or document database such as DynamoDB or OpenLink Virtuoso, but it has been a long process to get confident enough in the new tools. What I see missing from most db tools is the ability to map functions from the host langage to the query language in back. For instance, if you write something like lambda x: 5x-2x*x+7 you should be able to compile that the SQL or SPARQL or whatever equivalent. Microsoft's LINQ framework approaches that with a lot of specific features built into the runtime and language. In either Python or Java you can run a (or the) compiler/decompiler on an expression, make an AST, convert that AST into a query AST and send it to the database engine.
- tome 9y ago
  Opaleye can do this https://hackage.haskell.org/package/opaleye https://hackage.haskell.org/package/opaleye (The generated code may not be the easiest to read, but I hope the point comes across) > let paulHoulesFunction = (\x -> 5 * x - 2 * x * x + 7); paulHoulesFunction :: Num a => a -> a > traverse_ putStrLn (showSqlForPostgres (queryTable table1)) SELECT "column10_1" as "result1_2", "column21_1" as "result2_2" FROM (SELECT * FROM (SELECT "column1" as "column10_1", "column2" as "column21_1" FROM "table1" as "T1") as "T1") as "T1" > traverse_ putStrLn (showSqlForPostgres ((fmap . fmap) paulHoulesFunction (queryTable table1))) SELECT "column10_1" as "result1_2", ((5 * ("column21_1")) - ((2 * ("column21_1")) * ("column21_1"))) + 7 as "result2_2" FROM (SELECT * FROM (SELECT "column1" as "column10_1", "column2" as "column21_1" FROM "table1" as "T1") as "T1") as "T1"
nine_k 9y ago
ORM are indeed, in my opinion, not very helpful, except in relatively narrow cases when operation only on single records matches the task well. OTOH what most ORMs offer behind the scenes is a DSL for SQL, which may be very useful. In better cases, that DSL can be accessed separately from the object-mapping proper. For instance, I greatly appreciate SQLAlchemy as a way to write composable, readable SQL that operates on Python tuples from the host language side. I did not use the object-mapping part of it for years, and don't miss it.
scarface74 9y ago
I'm usually working in an object oriented language anyway so either I use an ORM like Entity Framework, a lower overhead micro ORM like Dapper (used by Stack Overflow) or end up reinventing the wheel. The real power for me using Entity Framework with Linq is being able to test my repository containing Linq queries by mocking out the EF Context with an in memory list of objects when unit testing. https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113).aspx https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....
siddboots 9y ago
I've loved plain old SQL for years, but have recently started using sqlalchemy's declarative ORM extensively in my company's data analysis work-flow. It means I can provide a database as a python library, which is great when I'm trying to encourage more of our electrical and mechanical engineers to use notebooks for their data analysis tasks instead of excel. Not only can they get data out without learning SQL, but they can also have a look at the relevant models.py any time they get stuck, and see the entire schema and all of it's relationships clearly spelled out. Worth pointing out that the three databases that I have set up like this are all read-only, which certainly makes it easier to design an elegant API. YMMV

PaulHoule 9y ago

I felt that ORM systems were negative 15 years ago. They have improved quite a bit since then. An increasing number of competitive frameworks have come out and the incumbent frameworks have improved. The situation is much better than when that "Vietnam" article came out. I think many people use a lightweight ORM such as Colander and if you are happy with that, that is great. These days though I am using SQL very differently than the old days. I used to use MySQL, PostgreSQL and sometimes Microsoft SQL Server but lately if I use SQL it is SQLite. Today I am much likely to use some kind of graph or document database such as DynamoDB or OpenLink Virtuoso, but it has been a long process to get confident enough in the new tools. What I see missing from most db tools is the ability to map functions from the host langage to the query language in back. For instance, if you write something like lambda x: 5x-2x*x+7 you should be able to compile that the SQL or SPARQL or whatever equivalent. Microsoft's LINQ framework approaches that with a lot of specific features built into the runtime and language. In either Python or Java you can run a (or the) compiler/decompiler on an expression, make an AST, convert that AST into a query AST and send it to the database engine.

tome 9y ago
Opaleye can do this https://hackage.haskell.org/package/opaleye https://hackage.haskell.org/package/opaleye (The generated code may not be the easiest to read, but I hope the point comes across) > let paulHoulesFunction = (\x -> 5 * x - 2 * x * x + 7); paulHoulesFunction :: Num a => a -> a > traverse_ putStrLn (showSqlForPostgres (queryTable table1)) SELECT "column10_1" as "result1_2", "column21_1" as "result2_2" FROM (SELECT * FROM (SELECT "column1" as "column10_1", "column2" as "column21_1" FROM "table1" as "T1") as "T1") as "T1" > traverse_ putStrLn (showSqlForPostgres ((fmap . fmap) paulHoulesFunction (queryTable table1))) SELECT "column10_1" as "result1_2", ((5 * ("column21_1")) - ((2 * ("column21_1")) * ("column21_1"))) + 7 as "result2_2" FROM (SELECT * FROM (SELECT "column1" as "column10_1", "column2" as "column21_1" FROM "table1" as "T1") as "T1") as "T1"

nine_k 9y ago

ORM are indeed, in my opinion, not very helpful, except in relatively narrow cases when operation only on single records matches the task well. OTOH what most ORMs offer behind the scenes is a DSL for SQL, which may be very useful. In better cases, that DSL can be accessed separately from the object-mapping proper. For instance, I greatly appreciate SQLAlchemy as a way to write composable, readable SQL that operates on Python tuples from the host language side. I did not use the object-mapping part of it for years, and don't miss it.

scarface74 9y ago

I'm usually working in an object oriented language anyway so either I use an ORM like Entity Framework, a lower overhead micro ORM like Dapper (used by Stack Overflow) or end up reinventing the wheel. The real power for me using Entity Framework with Linq is being able to test my repository containing Linq queries by mocking out the EF Context with an in memory list of objects when unit testing. https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113).aspx https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....

siddboots 9y ago

I've loved plain old SQL for years, but have recently started using sqlalchemy's declarative ORM extensively in my company's data analysis work-flow. It means I can provide a database as a python library, which is great when I'm trying to encourage more of our electrical and mechanical engineers to use notebooks for their data analysis tasks instead of excel. Not only can they get data out without learning SQL, but they can also have a look at the relevant models.py any time they get stuck, and see the entire schema and all of it's relationships clearly spelled out. Worth pointing out that the three databases that I have set up like this are all read-only, which certainly makes it easier to design an elegant API. YMMV