4 ms·

The thing with an abstraction later is that it has to accommodate for the simplest component. In this case (the no need to specify the len on your varchars) is

The thing with an abstraction later is that it has to accommodate for the simplest component. In this case (the no need to specify the len on your varchars) is an implementation detail of Postgres, but the ORM still has to deal with weaker components. Also for those interested, there’s no difference between varchar and text in Postgres, I just use text fields now, since all length checking is done at the application level, and Postgres is the only DB I need to support

derefr 8y ago
There are two ways to built abstractions: either by defining an interface that represents the lowest-common-denominator of functionality such that all the underlying implementations already implement it; or by defining the interface that you want to have, where some implementations can implement it directly, and others require a shim/polyfill implementation, or where the implementation “adapter” will just plain throw a NotImplementedError at your attempt to use the advanced features of the interface. Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found. Have you ever seen an ORM that allows you to fluently deal with errors raised by triggers? DB-side computed columns [by storing to a table but querying from a related view]? Where the ORM’s “objects” expose an OOP interface to calling DB-side stored procedures that take that row-type as an input? (Or, even more crazily, where you could define a stored procedure in an abstract fluent AST against the object, and have it registered with the database? Or how about exposing DB-side BLOB handles as native-to-your-runtime IO streams, that could be fed into any runtime function that expects such? There’s really a lot you could do, that’s still abstract to the SQL level (rather than specific to a particular database) if you were okay with some implementations of your interface not supporting every feature.
- blattimwind 8y ago
  > Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found. sqlalchemy
  - gigatexal 8y ago
    Avoid like the plague.
    - saarni 8y ago
      At a risk of this being too far removed from the original subject, It'd be interesting to hear your reasoning behind this statement. I've used SQLAlchemy in some projects and did not find it troublesome, but that's just my experience, and I might've not pushed it enough.
      - gigatexal 8y ago
        We use it on a large monolithic application. It’s too brittle. It abstracts away the benefits of the underlying database all for the ability to support a handful of databases? Meh. The default queries or mode of constructing queries is so generic that it makes no sense to use: I miss the scalpel that is crafting my own SQL queries taking into account indexes and such running and tuning queries before committing them to code. Look I was being hyperbolic but SQLAlchemy is great for prototyping but it’s then it’s too easy to just keep building on that crutch.
- lukeschlather 8y ago
  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.]

derefr 8y ago

There are two ways to built abstractions: either by defining an interface that represents the lowest-common-denominator of functionality such that all the underlying implementations already implement it; or by defining the interface that you want to have, where some implementations can implement it directly, and others require a shim/polyfill implementation, or where the implementation “adapter” will just plain throw a NotImplementedError at your attempt to use the advanced features of the interface. Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found. Have you ever seen an ORM that allows you to fluently deal with errors raised by triggers? DB-side computed columns [by storing to a table but querying from a related view]? Where the ORM’s “objects” expose an OOP interface to calling DB-side stored procedures that take that row-type as an input? (Or, even more crazily, where you could define a stored procedure in an abstract fluent AST against the object, and have it registered with the database? Or how about exposing DB-side BLOB handles as native-to-your-runtime IO streams, that could be fed into any runtime function that expects such? There’s really a lot you could do, that’s still abstract to the SQL level (rather than specific to a particular database) if you were okay with some implementations of your interface not supporting every feature.

blattimwind 8y ago
> Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found. sqlalchemy
- gigatexal 8y ago
  Avoid like the plague.
  - saarni 8y ago
    At a risk of this being too far removed from the original subject, It'd be interesting to hear your reasoning behind this statement. I've used SQLAlchemy in some projects and did not find it troublesome, but that's just my experience, and I might've not pushed it enough.
    - gigatexal 8y ago
      We use it on a large monolithic application. It’s too brittle. It abstracts away the benefits of the underlying database all for the ability to support a handful of databases? Meh. The default queries or mode of constructing queries is so generic that it makes no sense to use: I miss the scalpel that is crafting my own SQL queries taking into account indexes and such running and tuning queries before committing them to code. Look I was being hyperbolic but SQLAlchemy is great for prototyping but it’s then it’s too easy to just keep building on that crutch.
lukeschlather 8y ago
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.]