Hacker News Reader

Comment by kofejnik | Hacker News Reader

I had to work on/with a rather large backend which used this approach. Most of the logic was in stored procedures, with python/sqlalchemy used as a glue and to process requests.

It was absolutely horrible to develop and a nightmare to test.

For one, there's nothing approaching ActiveRecord migrations in stability and ease of use, so schema changes on dev/test/deploy were a huge pain. Then, you can't really work in normal "auto-run tests on save" mode as you would with saner frameworks, syncing source from git to postgres is needed (the principal dev altogether preferred to edit directly in pgadmin and then sync). Then, SQL tends to be a lot harder to read than python/ruby, especially so if not well documented and with many join tables. And finally, you can't scale out the heavy stuff, you are limited by your master instance basically. 2/5, would not recommend.

View on HN

With the correct abstractions, you can definitely run tests on save.

SQL is rather readable these days. It's obviously not as easy to read as ruby or python and is slightly more verbose, but it is certainly readable.

Mileage will vary of course. There are cost/benefit trade offs with every architectural decision made.

> SQL is rather readable these days. It's obviously not as easy to read as ruby

As someone who can't read Ruby but can handle "any level" of SQL fluently, that's obviously not a very objective statement.

I made a very broad assumption based on my past experiences with engineers and I apologize if I offended you.

Most engineers I have met in my career have really depended on an ORM to do all of their heavy lifting. They can write some of the most eloquent ActiveRecord queries but would be unable to create the same functionality using raw SQL.

Again, deeply sorry if I offended. It was not my intention.

Wow, is this really the norm? I mean, not knowing SQL and working purely with ORM. This seems weird to me, as I learned SQL first.

Yes, that's definitely the norm. Having some experience with Rails, I'm pretty sure that 90% of Rails developers have never written any SQL. If they need to access the production database, most of them would use the rails console on the production app server instead of psql etc.

And I can sympathize. For example, I wouldn't even know how Rails maps a many-to-many relation to SQL tables, so I would have to use the rails console myself to traverse such a relation.

Although Rails is very reasonable with its mappings and they are easy to use directly, I prefer Rails for db manipulaton, it is usually much easier and faster to type 'User.find(1).favorites << Product.find(10)' than an equivalent SQL.

Very true, but I've found that it's easy for those ActiveRecord abstractions to kill performance (I did this on a dashboard that was performing some serious calculations across hundreds of millions of records, often joining other similarly sized tables)

I don't know if it's the norm or not, but leveraging an ORM without knowing SQL is a thing. ORMs have so many benefits (query parameterization just to name one). That and people can stay in their comfort zone language (Ruby, Python, Haskell, etc) and let the ORM do the heavy lifting instead of having to dip down into SQL.

Those that are really good with an ORM can generally go into SQL with ease.

The above is not meant to be objective, just my general observations.

To be clear I'm not against the use of ORMs.

They are a fine tool, but I find it weird that people are using them without knowing the underlying technology which they are based on.

And you also don't need ORMs to do query parameterization.

Also, think of the various boot camps (General Assembly, Launch Academy, etc). Those do not teach SQL, but do teach some ORM.

Seems to be, especially among younger developers that learned Rails, and even more so among boot-campers.

Yeah, well, not everything everyone says is objective.

>> With the correct abstractions, you can definitely run tests on save.

You definitely can, but you'll have to roll your own watcher and sync, there's no Guard equivalent that I'm aware of.

>> SQL is rather readable these days.

Used to think so as well. But then I had to read some pretty nasty sp's which calculated discounts depending on what user already has, and geolocation, and time of day, and day of month, and tons of other stuff, and the return value had to be a complex hierarchy of categories/products. So what would be 2 pages of python turns into 500 lines of SQL with some statements 80 lines long, plus it was still a pain to json-encode in python. The only positive thing about it was that I wasn't the one who had to write it.

> You definitely can, but you'll have to roll your own watcher and sync, there's no Guard equivalent that I'm aware of.

[shameless plug] here's mine :) https://github.com/oelmekki/pgrebase

EDIT: sqitch is also often mentioned in that field http://sqitch.org/

pgrebase seems very nice, thanks!

This sounds like the approach was taken but the necessariy development tools were not created. Hence the painful experience.

I don't quite agree it's the tools only, my bigger gripe was total lack of scalability. Basically, even for moderately complex calculations, you can cache a lot of slow-moving stuff (such as products, categories, etc) to redis and do most of stuff on web_worker_{nnn}, using potgres only where you need ACID guarantees (eg orders, payments, etc). When you do everything on your master instance, any heavy calculation can become a problem pretty quickly.

> When you do everything on your master instance, any heavy calculation can become a problem pretty quickly.

SELECT-only reporting code like this is normally run against a query slave, and with a bit of automation you can stand up one of those pretty much as easily as another webhead.

Then how is it better than simply using another webhead? In any case, we need to do heavy calculations AND transactionally update some state. It's possible to do this on slaves I guess with some trickery, but then what's the advantage over doing it in the app servers?

> Then how is it better than simply using another webhead?

Data locality.

> In any case, we need to do heavy calculations AND transactionally update some state.

In that case, I'd have the app server connect to both master and slave, run the heavy calculations on the slave the same way, and then send the updates to the master.

Well you didn't mention that part in the parent comment ;)

Reply on news.ycombinator.com