Hacker News Reader

Comment by ptype | Hacker News Reader

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.

View on HN

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.

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.

(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.

(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...

(a) http://aws.amazon.com/rds/postgresql/

(b) Can you give an example?

(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.

Because then it's not in version control.

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.

Stored procedures can and should be deployed from files that are under version control.

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.

You keep them in sync by only deploying from those files, exactly the same best practice for deploying any software.

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.

Not really. The program can define the stored procedures on startup.

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.

Organizations I've been with have always preferred to keep the revenue app out of the schema-maintenance business.

it's certainly that simple in my deploy script.

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

Reply on news.ycombinator.com