Object-relational impedance mismatch
en.wikipedia.org
en.wikipedia.org
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?
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.
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"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.
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)....
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