4 ms·

ORMs are great for quickly standing up a database, but if you need advanced features you should probably just use raw SQL. Most ORMs even make this very simple

ORMs are great for quickly standing up a database, but if you need advanced features you should probably just use raw SQL. Most ORMs even make this very simple and safe.

derefr 8y ago
Simple to say in a language with dynamic typing; but part of the reason I use an ORM (when I do, which isn't always) is to be able to guarantee that there is never a mismatch between DB types, and business-layer types. Usually this means that I have a set of proxy types in the business layer that directly represent the DB types—table row types, table composite-primary-key types, view row types, stored procedure input-tuple and output-tuple types, etc. Most ORMs support this sort of mirrored-schema modelling between the DB and business layer, with the ability to declare things like reference relationships where the ORM will throw an error if you get back a type you weren't expecting. But when you drop to "raw SQL" in pretty much any ORM, you lose the ability to specify the business-layer-side input and output types for your expressions, and instead have to deal with sending and receiving the ORM's AST-like abstraction (usually plain arrays of scalars to map to bindings in the SQL statement.) ORMs that provide a LINQ-like abstraction are a bit different, in that you can use SQL fragments within the fluent syntax to do things like call a stored procedure; and in those cases, they usually allow you to pass in typed arguments, and to specify the output type of the expression (for the rest of the fluent-syntax chain to use in interpreting it.) While LINQ-like features are pretty nice, they still don't cover everything, because ORMs pretty much always execute their expressions within a transaction, and some SQL features (DDL statements specifically) can't be executed in a transaction. There are also certain types of SQL statements that most RDBMSes refuse to prepare with bindings—e.g. you can't make "which table you're querying from" a dynamic part of the query, even if you're willing to cast the result to a static "base" row type [as in PG's table inheritance.]

ORMs are great for quickly standing up a database, but if you need advanced features you should probably just use raw SQL. Most ORMs even make this very simple and safe.

derefr 8y ago

Simple to say in a language with dynamic typing; but part of the reason I use an ORM (when I do, which isn't always) is to be able to guarantee that there is never a mismatch between DB types, and business-layer types. Usually this means that I have a set of proxy types in the business layer that directly represent the DB types—table row types, table composite-primary-key types, view row types, stored procedure input-tuple and output-tuple types, etc. Most ORMs support this sort of mirrored-schema modelling between the DB and business layer, with the ability to declare things like reference relationships where the ORM will throw an error if you get back a type you weren't expecting. But when you drop to "raw SQL" in pretty much any ORM, you lose the ability to specify the business-layer-side input and output types for your expressions, and instead have to deal with sending and receiving the ORM's AST-like abstraction (usually plain arrays of scalars to map to bindings in the SQL statement.) ORMs that provide a LINQ-like abstraction are a bit different, in that you can use SQL fragments within the fluent syntax to do things like call a stored procedure; and in those cases, they usually allow you to pass in typed arguments, and to specify the output type of the expression (for the rest of the fluent-syntax chain to use in interpreting it.) While LINQ-like features are pretty nice, they still don't cover everything, because ORMs pretty much always execute their expressions within a transaction, and some SQL features (DDL statements specifically) can't be executed in a transaction. There are also certain types of SQL statements that most RDBMSes refuse to prepare with bindings—e.g. you can't make "which table you're querying from" a dynamic part of the query, even if you're willing to cast the result to a static "base" row type [as in PG's table inheritance.]