6 ms·

Even though I think it's important for developers to have some understanding of the underlying SQL, I still believe a good ORM can save time and make code more

by ptype 12y ago

Even though I think it's important for developers to have some understanding of the underlying SQL, I still believe a good ORM can save time and make code more readable. I think SQLAlchemy does a good job here - and it's close enough to SQL such that you are typically not surprised by the SQL it produces. For more complicated queries, I often hand write them in SQL first and then translate them into SQLAlchemy's methods. It's still worth it, since it's both easier to build dynamic queries (e.g. dynamic WHERE clauses) and it makes the parameterisation trivial. My problem with stored procedures is that you can end up with a lot of business code in the db.

marcosdumay 12y ago
Why is it that the DB is the correct place for business data, but not for business code? Of course, even for practical reasons, it's important to keep the DB lean. But some code does really belong toghether with the data.
- astine 12y ago
  Because then it's not in version control.
  - brlewis 12y ago
    Stored procedures can and should be deployed from files that are under version control.
    - astine 12y ago
      Then you have to keep your database and those files in sync. There are tools that help with this, but it's no longer as simple as redeploying your code.
      - knodi123 12y ago
        it's certainly that simple in my deploy script.
      - thirsteh 12y ago
        Not really. The program can define the stored procedures on startup.
        NickNameNick 12y ago
        That tends to hurt a lot when you have multiple front ends, and in general, I don't like the front end to have that much access to the database.
        spacemanmatt 12y ago
        Organizations I've been with have always preferred to keep the revenue app out of the schema-maintenance business.
      - brlewis 12y ago
        You keep them in sync by only deploying from those files, exactly the same best practice for deploying any software.
      - NoMoreNicksLeft 12y ago
        A couple of weeks ago I asked the Sequelize people why they couldn't also copy their validators into the database. The validation would still happen on the node app side of things, but when it generates the create table query (as it must) it could easily also put in the equivalent check constraint. Waiting on an official answer.
  - spacemanmatt 12y ago
    Is that for the same reason that stored procedures, column constraints, indexes, and performance are not tested the same way any other code is? When I write a schema, I plan for testing it with dummy data. If it's PostgreSQL I write a test using PGTap. It's code. You test it. I don't get why everyone doesn't get that.
- threeseed 12y ago
  Sorry but putting business code in databases is a terrible, terrible idea. (a) Oracle, Microsoft, Teradata etc all charge through the roof for scaling out your database. Which of course you're going to need to do if you're adding more and more business code. Scaling out normal code ? Cheap. (b) What happens if you exceed the capabilities of your database (yes it happens). Then you have a major project on your hands migrating both data and code. (c) The platform for developing/executing your business code in the database is like going back to the 1980s compared to the new flexible, microservices world we live in today. Really stored procedures over NodeJS, Scala, Go etc ? Performance isn't that important in businesses where most processes are batch orientated. (d) Databases in businesses are as tightly controlled as they get. As a developer would you want to have to go through laborious change management processes every time you push a commit ? It would cripple software development teams. There is a reason why "data lakes" are all the rage right now. Because people need cheap places to store and process their data and then use their SQL database simply for querying and reporting.
  - brlewis 12y ago
    (a) http://aws.amazon.com/rds/postgresql/ http://aws.amazon.com/rds/postgresql/ (b) Can you give an example? (c) I don't understand what you're criticizing here. (d) This depends on the organization. But generally business logic that would be at home in the database is business logic that should be controlled as tightly as the data is.
  - marcosdumay 12y ago
    (a) Yes, databases scale badly. You don't want to run your entire application there. Yet, code that reads a huge volume of data, calculates something based on parameters, and write it back to the DB will put more weight on your DB servers if it's run from the application layer; code that enforces the consistency of the data needs a strongly enforced policy if you keep it in any other place, but it works completely transparently if you place it in the database; code that denormalize the data will be much easier to use if you can run directly on your queries... (b) Been there, done that. Migrating code is EASY. Data is what's hard. (c) Is that a joke? Honestly, I can't tell. (d) That's more a reason to integrate those teams than to choose one place over the other. The programmers can disrupt the database on several ways without putting code there, and don't get any extra power by running their code in a different server. By the way, the data that was famously leaking recently was mostly from email and file servers...
wallyhs 12y ago
If you are primarily using stored procedures, you are probably viewing the database as a "persistence API" with its own encapsulated logic.

by ptype 12y ago

marcosdumay 12y ago

Why is it that the DB is the correct place for business data, but not for business code? Of course, even for practical reasons, it's important to keep the DB lean. But some code does really belong toghether with the data.

astine 12y ago
Because then it's not in version control.
- brlewis 12y ago
  Stored procedures can and should be deployed from files that are under version control.
  - astine 12y ago
    Then you have to keep your database and those files in sync. There are tools that help with this, but it's no longer as simple as redeploying your code.
    - knodi123 12y ago
      it's certainly that simple in my deploy script.
    - thirsteh 12y ago
      Not really. The program can define the stored procedures on startup.
      - NickNameNick 12y ago
        That tends to hurt a lot when you have multiple front ends, and in general, I don't like the front end to have that much access to the database.
      - spacemanmatt 12y ago
        Organizations I've been with have always preferred to keep the revenue app out of the schema-maintenance business.
    - brlewis 12y ago
      You keep them in sync by only deploying from those files, exactly the same best practice for deploying any software.
    - NoMoreNicksLeft 12y ago
      A couple of weeks ago I asked the Sequelize people why they couldn't also copy their validators into the database. The validation would still happen on the node app side of things, but when it generates the create table query (as it must) it could easily also put in the equivalent check constraint. Waiting on an official answer.
- spacemanmatt 12y ago
  Is that for the same reason that stored procedures, column constraints, indexes, and performance are not tested the same way any other code is? When I write a schema, I plan for testing it with dummy data. If it's PostgreSQL I write a test using PGTap. It's code. You test it. I don't get why everyone doesn't get that.
threeseed 12y ago
Sorry but putting business code in databases is a terrible, terrible idea. (a) Oracle, Microsoft, Teradata etc all charge through the roof for scaling out your database. Which of course you're going to need to do if you're adding more and more business code. Scaling out normal code ? Cheap. (b) What happens if you exceed the capabilities of your database (yes it happens). Then you have a major project on your hands migrating both data and code. (c) The platform for developing/executing your business code in the database is like going back to the 1980s compared to the new flexible, microservices world we live in today. Really stored procedures over NodeJS, Scala, Go etc ? Performance isn't that important in businesses where most processes are batch orientated. (d) Databases in businesses are as tightly controlled as they get. As a developer would you want to have to go through laborious change management processes every time you push a commit ? It would cripple software development teams. There is a reason why "data lakes" are all the rage right now. Because people need cheap places to store and process their data and then use their SQL database simply for querying and reporting.
- brlewis 12y ago
  (a) http://aws.amazon.com/rds/postgresql/ http://aws.amazon.com/rds/postgresql/ (b) Can you give an example? (c) I don't understand what you're criticizing here. (d) This depends on the organization. But generally business logic that would be at home in the database is business logic that should be controlled as tightly as the data is.
- marcosdumay 12y ago
  (a) Yes, databases scale badly. You don't want to run your entire application there. Yet, code that reads a huge volume of data, calculates something based on parameters, and write it back to the DB will put more weight on your DB servers if it's run from the application layer; code that enforces the consistency of the data needs a strongly enforced policy if you keep it in any other place, but it works completely transparently if you place it in the database; code that denormalize the data will be much easier to use if you can run directly on your queries... (b) Been there, done that. Migrating code is EASY. Data is what's hard. (c) Is that a joke? Honestly, I can't tell. (d) That's more a reason to integrate those teams than to choose one place over the other. The programmers can disrupt the database on several ways without putting code there, and don't get any extra power by running their code in a different server. By the way, the data that was famously leaking recently was mostly from email and file servers...

wallyhs 12y ago

If you are primarily using stored procedures, you are probably viewing the database as a "persistence API" with its own encapsulated logic.