4 ms·

I had a job interview at Zalando a while ago, and apart from the huge bunch of bananas at the entrance to the developer's den, the one thing I remember most abo

by mhd 11y ago

I had a job interview at Zalando a while ago, and apart from the huge bunch of bananas at the entrance to the developer's den, the one thing I remember most about was the fact that apparently they're using stored procedures for basically any database transaction. Which is probably a more unorthodox use of databases these days, at least for Postgres (I've heard it was more common for SQL Server and I once had the questionable joy of debugging a petri net solver in Oracle).

kaufland 11y ago
It's quite common for startups to have 1 or 2 sexy (and well-enough engineered) projects involving your favorite technology X, to run up the flagpole at conferences or in job ads, etc. But like as not, 80% of what they do (and of what you'll be doing as a developer there) is the same half-baked, post-"blitzkrieg" mop-up work you'll be doing pretty much anywhere else.
- mhd 11y ago
  They were pretty straight about that: Java, ExtJS, stored procedures. Not exactly candidates for the sexiest development tools alive. They were pretty honest about that, no "this will look awesome on your resume" tomfoolery. It was a pretty great interview, and I almost took them up on their offer. So don't get me wrong, I neither want to praise their stack nor put it down, I just thought that their ubiquitous use of stored procedures was an interesting fact (and Postgres with its pluggable languages supports this pretty well, these days you can even put your js/v8 code in it, if you really want to JavaScript all the thigns). They've got some code about it on github: https://github.com/zalando/java-sproc-wrapper https://github.com/zalando/java-sproc-wrapper
  - kaufland 11y ago
    Alles klar. Thanks for clarifying.
ma2rten 11y ago
Even during the talk it was even mentioned. "Stored procedures are the core our success" He also mentioned that Zelando does 100 data model (schema?) changes per week, and that senior dev make code changes without code review.
- valgog 11y ago
  Yes, 100 data model changes are schema changes (that can be one or more table structure change)
dragonwriter 11y ago
"Stored procedures for everything" is a bit unorthodox, but its probably a better practice that "base tables for everything" (which seems to be the common trend these days), and is pretty much the closest real practice to the "views for everything" that was long the ideal for decoupling decoupling the interface that each component using the DB saw from the underlying data model (an ideal that was, when it was made, often unachievable because of each RDBMS had different limitations on what you could do with views, particularly on the update side.) Though, with modern PostgreSQL, views-for-everything would probably work at least as well as stored-procs-for-everything.
- ams6110 11y ago
  I don't think it's unorthodox. Perhaps uncommon these days. The application logic needs to live somewhere. I like doing in stored procedures because you are close to the data and that leaves your client code free to just implement the user interface. It's much easier to support different client platforms without having to rewrite all your core transactions. It's actually my favorite way to architect an application. I like views for the same reason, especially for use with 3rd party reporting/BI tools.
spacecowboy_lon 11y ago
Isn't That how your supposed to do it using Sprocs is more time consuming but it does have several advantages from security and performance.
- gdulli 11y ago
  There's no way you're "supposed to" do it. There are tradeoffs and you pick the approach you like most given those tradeoffs.
  - spacecowboy_lon 11y ago
    Not sure id agree 100% with you for standard LOB CRUD applications the use of Sprocs is best practice. Obviously if your just writing a tool that will be used 2 or 3 times that - you can make trade offs - but you should never use $sql = "SELECT * FROM tblBobbins"
mgkimsal 11y ago
I've seen this pattern used a few places (sprocs for every single interaction). Seems like a lot of development overhead, but at at least one place I saw them in use at, the db admin and one of his appointees were the only people allowed to even write these things. Devs were just given connection privileges to run those sprocs, nothing else. Things worked, but the process didn't seem terribly ... productive. Apparently it was partially in reaction to some previous developer who'd done all the SQL by hand, and no one else could understand it. They didn't seem to realize they'd just shifted the problem from a developer to a dba.
angrybits 11y ago
I use the holy hell out of stored procs and UDFs, and they have saved my bacon plenty of times. Once you get past building todo lists, you'll end up with complicated transactions that do many different data mutations, usually along with some simple logic. You can either maintain your virtue and pay the latency price for each of those, or just code a proc that implements the whole thing. It might offend your software dogma, but it'll get shit done so you can get home to the wife in time for dinner. These days, that is the only deadline I care about.

by mhd 11y ago

kaufland 11y ago

It's quite common for startups to have 1 or 2 sexy (and well-enough engineered) projects involving your favorite technology X, to run up the flagpole at conferences or in job ads, etc. But like as not, 80% of what they do (and of what you'll be doing as a developer there) is the same half-baked, post-"blitzkrieg" mop-up work you'll be doing pretty much anywhere else.

mhd 11y ago
They were pretty straight about that: Java, ExtJS, stored procedures. Not exactly candidates for the sexiest development tools alive. They were pretty honest about that, no "this will look awesome on your resume" tomfoolery. It was a pretty great interview, and I almost took them up on their offer. So don't get me wrong, I neither want to praise their stack nor put it down, I just thought that their ubiquitous use of stored procedures was an interesting fact (and Postgres with its pluggable languages supports this pretty well, these days you can even put your js/v8 code in it, if you really want to JavaScript all the thigns). They've got some code about it on github: https://github.com/zalando/java-sproc-wrapper https://github.com/zalando/java-sproc-wrapper
- kaufland 11y ago
  Alles klar. Thanks for clarifying.

ma2rten 11y ago

Even during the talk it was even mentioned. "Stored procedures are the core our success" He also mentioned that Zelando does 100 data model (schema?) changes per week, and that senior dev make code changes without code review.

valgog 11y ago
Yes, 100 data model changes are schema changes (that can be one or more table structure change)

dragonwriter 11y ago

"Stored procedures for everything" is a bit unorthodox, but its probably a better practice that "base tables for everything" (which seems to be the common trend these days), and is pretty much the closest real practice to the "views for everything" that was long the ideal for decoupling decoupling the interface that each component using the DB saw from the underlying data model (an ideal that was, when it was made, often unachievable because of each RDBMS had different limitations on what you could do with views, particularly on the update side.) Though, with modern PostgreSQL, views-for-everything would probably work at least as well as stored-procs-for-everything.

ams6110 11y ago
I don't think it's unorthodox. Perhaps uncommon these days. The application logic needs to live somewhere. I like doing in stored procedures because you are close to the data and that leaves your client code free to just implement the user interface. It's much easier to support different client platforms without having to rewrite all your core transactions. It's actually my favorite way to architect an application. I like views for the same reason, especially for use with 3rd party reporting/BI tools.

spacecowboy_lon 11y ago

Isn't That how your supposed to do it using Sprocs is more time consuming but it does have several advantages from security and performance.

gdulli 11y ago
There's no way you're "supposed to" do it. There are tradeoffs and you pick the approach you like most given those tradeoffs.
- spacecowboy_lon 11y ago
  Not sure id agree 100% with you for standard LOB CRUD applications the use of Sprocs is best practice. Obviously if your just writing a tool that will be used 2 or 3 times that - you can make trade offs - but you should never use $sql = "SELECT * FROM tblBobbins"

mgkimsal 11y ago

I've seen this pattern used a few places (sprocs for every single interaction). Seems like a lot of development overhead, but at at least one place I saw them in use at, the db admin and one of his appointees were the only people allowed to even write these things. Devs were just given connection privileges to run those sprocs, nothing else. Things worked, but the process didn't seem terribly ... productive. Apparently it was partially in reaction to some previous developer who'd done all the SQL by hand, and no one else could understand it. They didn't seem to realize they'd just shifted the problem from a developer to a dba.

angrybits 11y ago

I use the holy hell out of stored procs and UDFs, and they have saved my bacon plenty of times. Once you get past building todo lists, you'll end up with complicated transactions that do many different data mutations, usually along with some simple logic. You can either maintain your virtue and pay the latency price for each of those, or just code a proc that implements the whole thing. It might offend your software dogma, but it'll get shit done so you can get home to the wife in time for dinner. These days, that is the only deadline I care about.