4 ms·

To avoid obscure ORM syntax and resulting garbage queries, as well as cumbersome inline SQL blocks, just implement your complex queries as stored procedures / v

To avoid obscure ORM syntax and resulting garbage queries, as well as cumbersome inline SQL blocks, just implement your complex queries as stored procedures / views etc.

fmajid 8y ago
Amen. You can also declare those stored procedures SECURITY DEFINER and deny access to the underlying tables to the client role for much Better security. For example if you have a table of users with login and hashed password, a check_login stored procedure allows checking password but if(when?) the app server gets compromised, the attacker can't even dump the list of valid logins because they don't have SELECT privileges on the table.
- angersock 8y ago
  Careful, this advice is dangerously close to proper DBA knowledge.
SQL2219 8y ago
This was what I was thinking also.
scarface74 8y ago
Having a dependence on stored procedures is a disaster from a devops perspective. Having all of your logic in your code makes deployments, rollbacks, testing and everything else from a development standpoint much easier. Having one source of truth for your code - your repo is much better. I know that if I check out code from three weeks ago, it’s the same code. I don’t know that if the code is dependent on stored procedures.
- CuriouslyC 8y ago
  To call them a nightmare is an overreaction. Client-exposed stored procedures can be versioned either by name or by the schema they're attached to. Additionally, you're making sweeping generalizations based on a limited perspective. Loading up the application layer is the right choice when your data will only ever be consumed by that application, and multiple versions need to coexist. When you're talking about data that cuts across a business, and where a single application version lives at a time, loading up the application layer is infeasible and unnecessary.
  - scarface74 8y ago
    To call them a nightmare is an overreaction. Client-exposed stored procedures can be versioned either by name or by the schema they're attached to. So now we have something like CreateCustomer_1 CreateCustomer_2 CreateCustomer_3 And different apps are using different versions. If you have to make s change. You have to make a change to every version because you don’t know which app is dependent on which version. What changed between the stored procedures and what code commit did they go with? Who changed it? (Usually this is solved very badly by having headers with comments that go back years). I can do a “git blame” on code, diffs, etc. Loading up the application layer is the right choice when your data will only ever be consumed by that application, Ideally, it should be one service per domain that is responsible for one set of data. That can be either a microservice or a module in a mono repo. Of course reporting would be separate and would cross cut multiple domains and would usually have intimate knowledge of the tables anyway. It’s also much easier/cheaper/faster to scale app servers than DB servers.
    - CuriouslyC 8y ago
      You can keep your sql in version control and use a script to reconcile the current database schema with the contents of a repository easily enough. Then you can track, git blame and in some cases, easily revert. Check out Alembic on this front, it's pretty useful. If you're going back and changing old versions of a stored procedure, you're doing it wrong. The only case where this would be true is a breaking schema change, and then application layer code would break too, so the point is moot. Siloing your data makes it less useful (or rather, it increases the cost of using it) and less discoverable. Additionally, if you have some sort of security model in place on your data that becomes exponentially harder to manage in the silo'd case. As for scaling - read scaling databases is REALLY easy. I admit that write scaling is more challenging, but stored procedures are read-dominant in general. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings. Siloing and thick application layers are great for some use cases. Data aggregation and thick database layers are great for others. Insistence on one model in all cases is only going to limit you as an engineer.

To avoid obscure ORM syntax and resulting garbage queries, as well as cumbersome inline SQL blocks, just implement your complex queries as stored procedures / views etc.

fmajid 8y ago

Amen. You can also declare those stored procedures SECURITY DEFINER and deny access to the underlying tables to the client role for much Better security. For example if you have a table of users with login and hashed password, a check_login stored procedure allows checking password but if(when?) the app server gets compromised, the attacker can't even dump the list of valid logins because they don't have SELECT privileges on the table.

angersock 8y ago
Careful, this advice is dangerously close to proper DBA knowledge.

SQL2219 8y ago

This was what I was thinking also.

scarface74 8y ago

Having a dependence on stored procedures is a disaster from a devops perspective. Having all of your logic in your code makes deployments, rollbacks, testing and everything else from a development standpoint much easier. Having one source of truth for your code - your repo is much better. I know that if I check out code from three weeks ago, it’s the same code. I don’t know that if the code is dependent on stored procedures.

CuriouslyC 8y ago
To call them a nightmare is an overreaction. Client-exposed stored procedures can be versioned either by name or by the schema they're attached to. Additionally, you're making sweeping generalizations based on a limited perspective. Loading up the application layer is the right choice when your data will only ever be consumed by that application, and multiple versions need to coexist. When you're talking about data that cuts across a business, and where a single application version lives at a time, loading up the application layer is infeasible and unnecessary.
- scarface74 8y ago
  To call them a nightmare is an overreaction. Client-exposed stored procedures can be versioned either by name or by the schema they're attached to. So now we have something like CreateCustomer_1 CreateCustomer_2 CreateCustomer_3 And different apps are using different versions. If you have to make s change. You have to make a change to every version because you don’t know which app is dependent on which version. What changed between the stored procedures and what code commit did they go with? Who changed it? (Usually this is solved very badly by having headers with comments that go back years). I can do a “git blame” on code, diffs, etc. Loading up the application layer is the right choice when your data will only ever be consumed by that application, Ideally, it should be one service per domain that is responsible for one set of data. That can be either a microservice or a module in a mono repo. Of course reporting would be separate and would cross cut multiple domains and would usually have intimate knowledge of the tables anyway. It’s also much easier/cheaper/faster to scale app servers than DB servers.
  - CuriouslyC 8y ago
    You can keep your sql in version control and use a script to reconcile the current database schema with the contents of a repository easily enough. Then you can track, git blame and in some cases, easily revert. Check out Alembic on this front, it's pretty useful. If you're going back and changing old versions of a stored procedure, you're doing it wrong. The only case where this would be true is a breaking schema change, and then application layer code would break too, so the point is moot. Siloing your data makes it less useful (or rather, it increases the cost of using it) and less discoverable. Additionally, if you have some sort of security model in place on your data that becomes exponentially harder to manage in the silo'd case. As for scaling - read scaling databases is REALLY easy. I admit that write scaling is more challenging, but stored procedures are read-dominant in general. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings. Siloing and thick application layers are great for some use cases. Data aggregation and thick database layers are great for others. Insistence on one model in all cases is only going to limit you as an engineer.