6 ms·

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

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.

phereford 10y ago
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.
- kofejnik 10y ago
  >> 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.
  - oelmekki 10y ago
    > 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 https://github.com/oelmekki/pgrebase EDIT: sqitch is also often mentioned in that field http://sqitch.org/ http://sqitch.org/
    - kofejnik 10y ago
      pgrebase seems very nice, thanks!
- josteink 10y ago
  > 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.
  - phereford 10y ago
    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.
    - kofejnik 10y ago
      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.
      - phereford 10y ago
        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.
        josteink 10y ago
        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.
      - 10y ago
dom0 10y ago
This sounds like the approach was taken but the necessariy development tools were not created. Hence the painful experience.
- kofejnik 10y ago
  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.
  - dom0 10y ago
    Well you didn't mention that part in the parent comment ;)
  - mst 10y ago
    > 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.
    - kofejnik 10y ago
      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?
      - mst 10y ago
        > 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.

phereford 10y ago

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.

kofejnik 10y ago
>> 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.
- oelmekki 10y ago
  > 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 https://github.com/oelmekki/pgrebase EDIT: sqitch is also often mentioned in that field http://sqitch.org/ http://sqitch.org/
  - kofejnik 10y ago
    pgrebase seems very nice, thanks!
josteink 10y ago
> 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.
- phereford 10y ago
  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.
  - kofejnik 10y ago
    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.
    - phereford 10y ago
      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.
      - josteink 10y ago
        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.
    - 10y ago

dom0 10y ago

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

kofejnik 10y ago
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.
- dom0 10y ago
  Well you didn't mention that part in the parent comment ;)
- mst 10y ago
  > 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.
  - kofejnik 10y ago
    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?
    - mst 10y ago
      > 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.