Is it a best practise for a database to allow apps do CRUD operations always through SPs?

Question

I've heard reasoning behind this and I was curious if others think of this as a best practise/good idea.

One reasoning is, that putting restrictions on direct access to database tables, and forcing apps/users to use SPs (Stored Procedures) to do CRUD operations will allow DBAs

1. fine grained control to approve/review SPs before moving them to production
2. avoid random queries written by developers which might cause server load/other server issues

This means the developer can not write Linq queries on tables (although Linq queries involving SPs are possible) This also means the developer has to forget about compile-time checks as well as being in total control of the data, rather principally use another language(SQL) to work with data.

I am not objecting to this, neither think this is a great idea. I am just curious to know what others think. What are any other advantages and disadvantages of this approach.

Update: As mentioned by Thomas in his answer, I tend to think that SPs are logic and using 'database programming in SQL' to do logic is okay as long as it's not 'Business logic'. Business logic needs separation, compile-time check and integration checkpoints and what not.

Answer 1

It depends massively on your data and your environment.

If you're making your database widely available to disparate readers and writers, use SPROCs: that way you maintain absolute control.

If you're in more of a closed shop where you trust the developers, use LINQ. It allows much greater flexibility of access to the data and makes future changes to the database a breeze. (E.g. if you add a field to a table you just have to regenerate your LINQ data access partial classes and you're done; otherwise you'll have to rewrite a bunch of SPROCs, as well as altering your data access layer in your application.)

Answer 2

Looks like you don't trust the developers to write well-behaved queries.

This is probably from experience.

I would suggest that if bad developers are writing crap sql code, they are also writing bad application code, and that means management is doing a poor job.

The problem isn't the dynamic SQL (even if parameterized) versus the stored procedures: it's poor management.

That's not going to be solved by forcing db access through stored procedures.

Anyway, even if access to the DB is only provided through stored procedures, bad coders will still be able to stress the database.

The reality is that if you make data hard to get to in the database, developers will use other techniques to not put the data in the database, or perverting the database. For example, say you have a blog field to store pdfs or something, what's to stop them from serializing objects and storing them in the blob, to be retrieved and de-serialized in code?

I would be careful on that.

Make sure that the stored procedures end up in source control, and that deployment of stored procedures to the appropriate database environment can be performed automatically by the build tool (continuous or daily)...

Answer 3

Well another obvious benefit is performance, once the SP's are compiled they will execute quicker than SQL in code. You have already stated a major benefit of SP's for larger organisations with an eye on security and control. It makes it easier for your DBA to run an eye over the SQL running against the DB in times of performance issues also as its all in the DB.

One disadvantage is portability, if you are writing a commercial application and a customer insists you adapt to their existing DB you'll have a bit more work to do with SP's than for in-line SQL.

Answer 4

To use your list:

It means the developer doesn't need to write Linq queries on tables. It also means the developer can forget about compile-time checks on database access code, and let someone who knows the database be in total control of the data. And the develoer doesn't need to use another language (SQL or Linq) to work with data.

In other words, it depends on your working context.

Answer 5

I work on a team that employs this approach.

There are other benefits, such as

1. Security - if you don't have direct access to the base table, it can be part of a defense in depth approach.
2. Flexibility in your PDM - you can make largescale changes to your PDM for performance reasons, and as long as you preserve your 'contracts' (SPs, Views), the application layer is unaware.
3. Futureproofing - You're well positioned for another application, possibly written in another language, to come along and access your database.

It works best when you have a dedicated database person or team.

I am watching this area with interest, as it definitely seems to run against the techniques required when using ORMs.

Answer 6

Personally, I've never found procs flexible enough for the 'R' in CRUD. I usually use views. But accessing the database through views & procs also allows you to abstract away the database tables giving the data model greater flexibility in the future ... almost like working against an interface.

Answer 7

Yes, particularly in Oracle.

In Oracle, triggers and foreign keys require many SQL/PLSQL context switches.

I personally develop databases that require large CRUD operations in the following way:

1. I use neither triggers nor foreign keys.
2. No modify permission on tables are given to the users.
3. All DML in the database are performed this way:
   1. Data to be inserted/updated/deleted are loaded into the TEMP table
   2. A stored procedure is called, in which:
      1. Data are checked for consistency as in BEFORE I/U/D triggers and foreign keys
      2. Data are MERGE'd into the table
      3. Data are checked for consistency as in AFTER I/U/D triggers and foreign keys

In the database I have a service table that holds these foreign key-like relations between fields.

The stubs for the procedures are generated automatically from this table, and need to be recreated every time the table changes.

All checks are performed by a single query that processes thousands of rows, instead of calling the trigger in a loop thousand times.

This is a huge increase to modification performance.

If you need to UPDATE, say, 50,000 rows in a 10,000,000 rows table, this may take 10 seconds instead of 200 seconds of even more.

---

Of course, it pays for itself only if you need really fast response to really large operations.

It's very difficult to design a database this way, and it's prone to errors if you're not used to it.

Answer 8

The database is a bucket, once you start treating as a part of your business logic you will end up in a very tight and uncomfortable place and create MUD instead of SW...

Separate logic and data. SPs are logic...!

Evil tongues (me that is) claim that SPs are database vendors techniques to create lock-in technologies which will make it harder to create abstractions that makes it easy to change database vendor...

SPs are in 99.9% of their uses an ANTI-design pattern...!

Answer 9

Depends entirely on your environment. The answer to the question really isn't a coding problem, but a business decision.

If your database supports just one application, and is reasonably tightly integrated with it, then it's better, for reasons of flexibility, to place your logic inside your application program. Under these circumstances handling the database simply as a plain data repository using common functionality looses you little and gains flexibility - with vendors, implementation, deployment and much else.

On the other hand if your are handling a corporate database then it is highly advisable to screw down the security as far as you can. At the very least all appropriate constraints should enabled, and if possible access to the data should be through views and procedures only. Whining programmers should be ignored in these cases as...

1. With a corporate database the asset is valuable and invalid data or actions can have business-threatening consequences. In these circumstances your primary concern is the business, not how convenient access is for your coders.
2. Such databases are by definition accessed by more than one application. You need to use the abstraction that stored procs offer so the database can be changed when application A is upgraded and you don't have the resource to upgrade application B.
3. Similarly the encapsulation of business logic in SPs rather than in application code allows changes to such logic to be implemented across the business more easily and reliably than if such logic is embedded in application code. For example if a tax calculation changes it's less work, and more robust, if the calculation has to be changed in one SP than multiple applications. The rule of thumb here is that the business rule should be implemented at the closest point to the data where it is unique - so if you have a specialist application then the logic for that app can be implemented in that app, but logic more widely applicable to the business should be implemented in SPs.

Answer 10

Using stored procs (and ones which do not use dynamic SQL) allows the database people to performance tune and more importantly it limits access to database tables and views so that no one can change them except the dba. This is VERY important if you have a financial application and want to guard against internal fraud.

Answer 11

This blog article presents a very good counterpoint to the "Stored Procs are always better" crowd.

http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx

Personally, I am on the fence and lean towards using them.

Answer 12

As others have noted, this depends very much on your environment.

However, even in a corporate situation, where you want to protect your mission critical data, using stored procedures for CRUD is not necessarily mandated. As noted, using a stored procedure to implement database insertion assumes the stored procedure is going to be smarter than the applications programmer. Stored procedures are logic, so the skill of writing them is the skills of a programmer. So, if you happen to have a DBA who is also a better programmer than your programmers, then, by all means get them to write stored procedures to keep your programmers honest. This might be the case some places. It is worth keeping in mind Thomas Hansen's point that using a stored procedures can result in the entire organization being locked into the logic of the given stored procedure. Again, some organization might want or need this.

Another approach is to use some stored procedures to guarantee consistency but still allow ad-hoc insertions. Depending on the database, this can allow a cleaner interface and more consistency.

Stored procedures might be a bit faster but that is unlikely to be a main consideration for a large organization's main database (and a small organization probably wouldn't want that many tiers).

If you are going to add large amounts data, things like batch files and turning transactions off are useful. But to my mind, CRUD does not imply a large amounts of data being created but rather small instances of creating, reading, editing and deleting data as is done by most user-level applications.