3 ms·

True, if you modulo all the features like views, stored procedures, functions, foreign keys, triggers there is no reusability in SQL... On the serious side: It

True, if you modulo all the features like views, stored procedures, functions, foreign keys, triggers there is no reusability in SQL... On the serious side: It's not a bad idea to contain critical business logic in the database. It's shared by any app using the database. Foreign keys esp link and let you cascade changes with no extra code. Less overall code. Higher guarantees. Everyone agrees it's a good idea to use a `datetime` field for a `created` field instead of `varchar` and letting the application parse/extract it's own format in there. It's also a good idea to add triggers that do specific things to keep your db in a consistent state upon actions. Maybe adding/deleting a row needs to update a counter in a statistics table for quick access because count(*) on that table takes too long. You could do that it in your app - but what happens if someone changes the data without going through your app. Instead you add it as a trigger. Now you can be sure that the number in the stats table is always the same as the actual number.

sly010 8y ago
Not sure why are you being downvoted, I would like to at least hear the counter argument.
- da_chicken 8y ago
  I don't disagree with him (though I don't necessarily agree as strongly with respect to triggers), but the counter argument is that it's extremely easy to put up a web service that offers an API and interacts with JSON. Now all the complicated bits are in the API service, and the multiple apps don't need to know anything at all about the underlying data store. The counter to that is that it can be very difficult to get an API that is as comprehensive and flexible as SQL is, and eventually you'd find yourself re-implementing SQL if you ever tried. Certain reporting scenarios or mass data manipulation scenarios really require actual data store access in order to perform remotely well.
  - zepolen 8y ago
    Your counter counter argument on not being as flexible as SQL is spot on. A DAL that sits in front of storage mechanisms is not a bad idea though. I've found moderate success in doing the reverse and using communication channels from SQL => services either via NOTIFY or queues. ie. Dispatching an event that signals a non SQL service to do an action. eg saving a file to S3, then updating the database when the action is complete. This doesn't really work when retrieving data synchronously but in most cases if the data is stored elsewhere - then you probably want to access it via non db methods anyway (ie. for an S3 file, you'd use the url) WRT JSON, please for the love of god no. Someone made the same argument a month ago, my reply here: https://news.ycombinator.com/item?id=18870838 https://news.ycombinator.com/item?id=18870838
    - da_chicken 8y ago
      > WRT JSON, please for the love of god no. I don't like it either, but for most scenarios that this sort of thing works for a RESTful API or microservice or equivalent (or whatever you want to call it these days since I think the expiration date on those terms has elapsed) and not all data can be meaningfully coded without a structure like JSON or XML. Or, rather, you can, but, you're reinventing the wheel just like you would with an API trying to be as flexible as SQL. You may run into various system limits on URL length, too. Events and queues are great, but not all requests work that way. If you're USPS and you're providing an address and ZIP code resolver, you've got different requirements than getting data from one system to cascade across a series of systems with a dozen different asynchronous widgets. You'd have a burden of using a format that your customers would prefer, too.
      - zepolen 8y ago
        Having worked with three large JSON http microservice based projects in the past, I abhor the day they got popular. Relative productivity has definitely gone down due to the project overheads caused by them. Not saying microservices don't work, they are great for specific use cases, but 9/10 people just want to microservice everything just to say they use microservices. > If you're USPS and you're providing an address and ZIP code resolver Not sure I see your point with this example - isn't a resolver like this just a fixed database of entries?
- lenocinor 8y ago
  Database changes have been painful for me in every job I have worked at. They often took me longer to write, longer to test, and longer to fix when I introduced bugs. In my experience, doing things in the database is great for simple actions, transformations done in huge volumes, preprocessing large datasets, or logic that is unlikely to change. It's bad for doing things which don't have those properties, especially the last property due to database changes being painful.
- paulryanrogers 8y ago
  Not a down voter, but I imagine it could be because triggers are associated with write amplification and visibility concerns. They have their place, just like stored procedures.

sly010 8y ago

Not sure why are you being downvoted, I would like to at least hear the counter argument.

da_chicken 8y ago
I don't disagree with him (though I don't necessarily agree as strongly with respect to triggers), but the counter argument is that it's extremely easy to put up a web service that offers an API and interacts with JSON. Now all the complicated bits are in the API service, and the multiple apps don't need to know anything at all about the underlying data store. The counter to that is that it can be very difficult to get an API that is as comprehensive and flexible as SQL is, and eventually you'd find yourself re-implementing SQL if you ever tried. Certain reporting scenarios or mass data manipulation scenarios really require actual data store access in order to perform remotely well.
- zepolen 8y ago
  Your counter counter argument on not being as flexible as SQL is spot on. A DAL that sits in front of storage mechanisms is not a bad idea though. I've found moderate success in doing the reverse and using communication channels from SQL => services either via NOTIFY or queues. ie. Dispatching an event that signals a non SQL service to do an action. eg saving a file to S3, then updating the database when the action is complete. This doesn't really work when retrieving data synchronously but in most cases if the data is stored elsewhere - then you probably want to access it via non db methods anyway (ie. for an S3 file, you'd use the url) WRT JSON, please for the love of god no. Someone made the same argument a month ago, my reply here: https://news.ycombinator.com/item?id=18870838 https://news.ycombinator.com/item?id=18870838
  - da_chicken 8y ago
    > WRT JSON, please for the love of god no. I don't like it either, but for most scenarios that this sort of thing works for a RESTful API or microservice or equivalent (or whatever you want to call it these days since I think the expiration date on those terms has elapsed) and not all data can be meaningfully coded without a structure like JSON or XML. Or, rather, you can, but, you're reinventing the wheel just like you would with an API trying to be as flexible as SQL. You may run into various system limits on URL length, too. Events and queues are great, but not all requests work that way. If you're USPS and you're providing an address and ZIP code resolver, you've got different requirements than getting data from one system to cascade across a series of systems with a dozen different asynchronous widgets. You'd have a burden of using a format that your customers would prefer, too.
    - zepolen 8y ago
      Having worked with three large JSON http microservice based projects in the past, I abhor the day they got popular. Relative productivity has definitely gone down due to the project overheads caused by them. Not saying microservices don't work, they are great for specific use cases, but 9/10 people just want to microservice everything just to say they use microservices. > If you're USPS and you're providing an address and ZIP code resolver Not sure I see your point with this example - isn't a resolver like this just a fixed database of entries?
lenocinor 8y ago
Database changes have been painful for me in every job I have worked at. They often took me longer to write, longer to test, and longer to fix when I introduced bugs. In my experience, doing things in the database is great for simple actions, transformations done in huge volumes, preprocessing large datasets, or logic that is unlikely to change. It's bad for doing things which don't have those properties, especially the last property due to database changes being painful.
paulryanrogers 8y ago
Not a down voter, but I imagine it could be because triggers are associated with write amplification and visibility concerns. They have their place, just like stored procedures.