Hacker News Reader

Comment by nine_k | Hacker News Reader

Object-relational mapping is in many cases an excessively leaky abstraction. I try keep away from it.

A DSL for writing SQL in a nice, composable way is a useful thing.

Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.

SQLalchemy is kind of a pain in the ass to actually use, though. The DSL doesn't feel very Pythonic, it's weird and confusing. The way it traverses the Object graph when loading associations between models is magical and opaque and I could never predict when it was going to automatically work and when it wouldn't.

I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.

True, kinda. I love SQLalchemy and it does feel different to use than every other python package I've used, but I'm not sure if I could do it better.

It's pure magic.

The one that bit me with sqlalchemy is joins. You have to structure your sqlalchemy object in a specific way to do joins. Usually I write my sql query then spend half an hour trying to convert it to sqlalchemy.

Having PK relations defined was usually sufficient for me. I like SQLAlchemy for its straightforward mapping of SQL.

I don't usually work on the "model" level, though; I work on "table" level.

Yes, SQLAlchemy uses tons of magic.

But my very point was not to make it traverse object graphs at all. You can write nice SQL using it, with selects, joins, functions, etc. All these parts, SQL clauses, are composable, so you can factor out common parts.

Yes, I'm fine working with lists of tuples, not "model objects". Object graphs don't map all to well to the RDBMS model all too well. It's best done on case-by-case basis, if you care about performance at all.

If you aim to have a pythonic data layer, I've heard good things about pony orm[1]. I mean,you can't get much more pythonic then the example they have on their website. But I haven't used it my self.

[1](https://ponyorm.com/)

The DSL for your database is called SQL.

This is a quip that misses the parent's point. SQL isn't very composable, because its syntax requires infix notation, position-dependent phrases, separators, and the like. The abstract syntax tree of SQL is much more valuable than its syntax, which is essentially just a bad English serialization that resembles a natural language sentence.

A DSL which manipulates queries and then produces syntactically valid SQL is tremendously valuable.

SQL is composable if you use views, just create a view for anything that you want to reuse in multiple queries.

Doesn't work because it completely screws up query optimization.

Maybe in some crappy RDBMSs, but not in Postgres.

If you encounter such an issue, there are ways around it:

https://www.red-gate.com/simple-talk/sql/performance/control...

For that I'd need to dynamically create such views, which requires a SQL generator anyway :)

Views are only half the answer. Try factoring out a set of conditions and use them both in a `select` and in `update`.

Also, views tend to pollute the namespace a bit: you want a set of descriptive names, per application, and possibly per application version. Schemata help here, though.

Bare in mind that SQL was designed to allow non-technical business users to use it. In most offices, a vetern business user or BI person will know how to use SQL. So SQL is a DSL for relational deconstructing of data models that is conceptually simple enough for people to grasp.

Templating can absolutely save time and boilerplate, but adding a DSL over a DSL in the form or ORM may well be the problem.

Reply on news.ycombinator.com