Using SQLAlchemy and Postgres functions to produce JSON structures from joins | Hacker News Reader

Using SQLAlchemy and Postgres functions to produce JSON structures from joins

trvrm.github.io

I'm "eh" on ORMs, so if there are ORMers out there... one of the things I find hurts SQL and data-model comprehension is the compounded complexity of relationships. Maybe you have authors and books - but more likely your data-model is more complex, maybe your business needs group authors by family and you also track how many copies of books are in libraries. This is still a pretty simple situation but as things get more complicated the utilization of non-materialized views can help simplify expression - but they come at the cost of pushing some of your logic into the DB where normal developers can't see it.

Have people using SQLAlchemy and other ORMs found it helpful to create fake views using the ORM system (i.e. pre-define a commonly used join) so that it can be reused as a simpler black-box unit of code where needed?

It's relatively straightforward to map a materialized view to an ORM entity in SQLAlchemy, if that's what you want to do:

http://www.jeffwidman.com/blog/847/using-sqlalchemy-to-creat...

SQLAlchemy's ability to separate out the query API, metadata, data objects, and combine them all via mappers is very powerful. I've been using SQLA for many years, and I still regularly find some functionality that I was never aware of.

I more actually meant something closer to a non-materialized view, just a query segment definition that gets frequently reused - supporting it in a materialized form is all good and well but I'm more interested in how repetitive SQLA tends to come out, if a common set of tables is joined through in a wide variety of queries is there a way to reduce the number of times you can spell it out in an easy manner?

My biggest gripe with joining and aggregating to JSON is JSON itself. The project I'm working on is dealing with timestamp ranges which of course can't be serialized to JSON.

Although I'm not working with SQLAlchemy (or Python) I presume it has the power to serialize a TSRANGE into a native Python range (if it has that) as well.

I'm curious if anyone has ever found some way around that, I feel like it isn't possible UNLESS there's some data type than JSON available that I could aggregate with.

I think it is a good thing JSON is limited in what types it supports. Otherwise it would contain so much type bloat while at the same time still annoying someone somewhere because his type is not supported by JSON.

If you are able to pull your data from the database and process it in a programming language of your choice, you can write a custom serializer that takes a tsrange and spits out some JSON, e.g. a JSON array with two items [start, end], which you then can pass on to a client that understands your JSON format.

> I think it is a good thing JSON is limited in what types it supports. Otherwise it would contain so much type bloat while at the same time still annoying someone somewhere because his type is not supported by JSON.

I wish it had a few more capabilities, but since JS doesn't then JSON won't.

> If you are able to pull your data from the database and process it in a programming language of your choice, you can write a custom serializer that takes a tsrange and spits out some JSON, e.g. a JSON array with two items [start, end], which you then can pass on to a client that understands your JSON format.

I can definitely do that. My case though is aggregating and serializing into JSON in postgres and loosing type information due already on the query level.

> The project I'm working on is dealing with timestamp ranges which of course can't be serialized to JSON.

JSON is a low-level serialization format, on which you often need to build application-specific high-level formats. You can serialize a tsrange into JSON a number of ways, but your deserializer will.need to be aware of the higher-level format you create by your decisions on how to serialize unsupported types as composites of supported types.

Very fair to say that this logic ought to exist on the application level, I suppose.

I think this isn't really a failing as there will be custom data types as well, in our system we make use of interval ranges for describing age limitations tied to products we're working with (like [3 months,15 years]) this is already into the realm of non-standard postgres typing and asking JSON to support it would be silly - as other comments have mentioned this is really where you should be defining your own rules.

Reply on news.ycombinator.com