[n_are_q]

The difference at a high level is that sql has a syntax and set of capabilities that is quite unique, and every single database vendor has its own extensions or differences driven by their particular approach. To really replicate all of this in code you would have to go beyond the basic data structures and syntax of that programming language. And at that point might as well just have sql. It's a paradigm and an approach expressed through its own syntax, you can't easily copy all of it in a totally different programming language..

As for checking for type safety, I think frameworks that do sql-to-object mapping (with type safety), and also handle cache for you, are a very useful thing. Making raw calls on database connections is definitely too far "in the other direction" :).

[contextfree]

On the syntactic level SQL is just a poorly designed language. LINQ query expressions actually do a better job of expressing the semantics of the SQL-like set/collection operations, in a compositional manner. It's definitely true though that SQL databases currently have a lot of capabilities (like, errmm, DML) that at least the Microsoft ORMs don't support other than by dropping down to SQL. I don't think this is a problem with the LINQ IQueryable paradigm, though, but just a problem with the Microsoft ORMs being incomplete.

I don't have much experience with ORMs or mapping frameworks other than LINQ-based ones, but it seems like it would be pretty difficult to typecheck queries expressed as SQL strings, at least dynamic ones, at compile time. Do the frameworks you mention typecheck the actual query itself at compile time, or do they just check at runtime that the data returned from the query matches what you want?

[n_are_q]

Query results you will normally adapt to specific object properties, and at that point the only thing that can bite you is if your query starts returning columns of a different sql type, which of course you can't catch compile time anyway. If you wrap your query results with objects and maintain an interface into your update statements via method calls (which obviously have type checking for arguments), I don't see how you can run into serious trouble. In the dynamic language world you of course don't have compile time anything, but you can use pretty much the exact same techniques to ensure you don't pass something bad to your query. I guess this isn't very dynamic, but that's the idea - your data access logic lives in the database, you execute methods and get back objects. That's the no orm way.