The Developer Arguments for Stored Procedures

  • austin.mcguire wrote:

    In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL).  Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor...

    I'm not sure I understand your point about "having to deploy an additional  server".  I don't think this article is about if there should be a database or not, it is if the use of stored procedures is good.  There will still be a database, not using stored procedures does not reduce your server footprint.

  • Chris Harshman wrote:

    austin.mcguire wrote:

    In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL).  Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor...

    I'm not sure I understand your point about "having to deploy an additional  server".  I don't think this article is about if there should be a database or not, it is if the use of stored procedures is good.  There will still be a database, not using stored procedures does not reduce your server footprint.

    I think he means to support the database server, (SQL Server, Oracle, etc.) if your customer doesn't already have the platform.

  • I agree with other people's comments on encapsulation and security.  Here are my additional comments on why I believe it's better to use stored procedures than code queries directly within application code:

    • Easier refactoring

      If business logic changes, or a bug is found, it would be quite simple to find all the access points to the data if it's in stored procedures.  If done right, the changes would need to be done in fewer places to.  I've seen countless times where queries are hidden inside application code, or in some third party reporting tool, and get missed when changes are made, because people were focused on the 20 other places they needed to change something.

    • Less redundant logic

      The proper use of inline-table-valued functions, views, and stored procedures, will lead to an overall system with less code, that is easier to maintain.

    • Improved scalability

      While at first sight it may seem easier to scale out application servers that don't depend on data state, putting too much data logic within the application layers is more likely to require you to scale out sooner.  Some of the abstraction layers people use to access databases also produce poor quality queries that will hurt database performance.

  • Many excellent points being made here. It seems that the main points in favor of embedded SQL / ORM are:

    1. developer efficiency / convenience (size of the team, and if a DB-developer even exists to be a part of the team, are a large factor into exactly how much of a convenience)
    2. code portability for interacting with multiple RDMBS platforms (point made by "pianorayk" on Steve's blog version of this post ; mainly working in SaaS environments, this is not something I was ever concerned with)

    I am rather dismissive of the "don't hide business logic in the DB argument" as a not-well-thought-out argument due to:

    1. much of the time it isn't "business" logic so much as "data" logic, and the database is tuned for data manipulation whereas app code is not (well, unless the app is a database 😉 )
    2. the logic isn't hidden or buried in the first place, it's just in a different service. And we often enough call other services. This is different from the argument some make against triggers for hiding logic. In the case of triggers, there is no clear, traceable path to what is happening, whereas it's easy enough to see a stored proc name in app code and then go look there.
    3. as others have mentioned, we should do what makes more sense for the operation to be efficient and reliable, not adhere to a loosely defined ideal for the sake of idealism.

     

    That being said, I still prefer using a stored procedure approach for the following reasons:

    1. It provides a consistent data API (others have mentioned several benefits of this)
    2. Security!! A couple/few others have mentioned this, but it shouldn't be under-stated. Security is something that more developers (app and DB) should be concerned with. Using embedded SQL / ORMs means that the DB login needs to be given all necessary rights to perform all operations. This seems easy enough when the requested operations are straight-forward. But as soon as cross-database queries are needed, or there's a need to do something that only the table owner or "dbo" have permission to do (e.g. TRUNCATE TABLE, SET IDENTITY INSERT, etc), or there's a need for a high-level permission such as VIEW SERVER STATE (a common need, yet a far-reaching permission), then you are just making it easier and easier for hackers because the app logs in with enough permissions to do most anything. Sometimes, when folks can't figure out which permission to grant, or what other configuration is needed to get past a permission problem, just assign the login to the "sysadmin" fixed server role. Yet, with stored procedures, the ONLY permission you ever need to grant the app login is execute on the modules (stored procedures, functions, etc) needed. Module Signing allows for any other permission to be granted to the modules, not to any logins (and removes any need to use EXECUTE AS or enable TRUSTWORTHY on the DB)
    3. It allows for performance tuning. This is a concern that is too often dismissed at the beginning of a project when there is very little test data. Most RDBMS platforms and hardware these days are fast enough to work reasonably fast with small amounts of data, and thus hide many inefficiencies. Same issue even if the project is mature but devs test locally or in dev environment with minimal amounts of data (i.e. the "works on my box" problem).

    One thing nobody has mentioned yet (as far as I have seen) is the issue of scalability. This falls into the "performance tuning" benefit of stored procs area, but to be fair, is not an issue for all projects. Working on small projects might never expose the need for the ability to do performance tuning (the definition of "small" varies based on hardware, but let's just where no tables ever have more than a 2 million or so rows). On the other hand, I have worked on projects where OLTP tables had a few hundred million rows in them. That needed to be handled via stored procedures.

     

    Someone did mention that ORMs (at least Entity Framework -- EF) allows for using stored procedures. Excellent point. This does allow for a hybrid scenario. I remember the early stages of a project years ago where the dev was pushing really hard for EF and I was very resistant to that. Still, it was just the two of us and he didn't want to wait on me to write procs, etc. So, we compromised and agreed to use EF such that all initial development would rely on EF to dynamically generate the SQL, but any time we identified an area that was inefficient, if an index wouldn't solve the problem, then we would convert that operation into a stored procedure.

     

    Still, the hybrid approach does nothing to help security, and this was long-ago enough where I didn't know nearly as much about security as I do now, but I suppose the same agreement can be made: use EF to auto-gen T-SQL for simple operations, but any elevated permissions need to be handled via modules.

     

    Take care,

    Solomon...

     

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky wrote:

    Using embedded SQL / ORMs means that the DB login needs to be given all necessary rights to perform all operations.

    How is that different than not using an ORM? Your application needs to be granted appropriate permissions whether or not it's using an ORM doesn't it?

    Solomon Rutzky wrote:

    Someone did mention that ORMs (at least Entity Framework -- EF) allows for using stored procedures. Excellent point. This does allow for a hybrid scenario. 

    Yeah, that was me, but I wasn't talking about a "hybrid" scenario. When I'm using Entity Framework, I do all my access through views and stored procedures, and I don't allow my application access to anything but those views and procs. Nothing "hybrid" about that.

  • I'm a developer and at least in the past a DBA. And I love using stored procedures. However, where I now work there's a prejudice against them. Coincidentally, the topic came up today in our staff meeting. My boss's problem with stored procs is, "Stored procedures are blind to the environment invoking them."

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Rod at work wrote:

    My boss's problem with stored procs is, "Stored procedures are blind to the environment invoking them."

    Isn't that true of any method? I thought that was the whole point of encapsulation? In what way is it considered better to run the same code from outside the database?

    I've interviewed a number of developers and some will say that they are quite fluent in SQL Server, only to discover that they use linq or some other ORM and SQL Server is relegated to being simply a data storage mechanism. Some have gone so far as to not include any referential constraints, foreign keys, default values, or anything of the nature. It's treated as no more than a dumb data dump.

  • dmbaker wrote:

    Solomon Rutzky wrote:

    Using embedded SQL / ORMs means that the DB login needs to be given all necessary rights to perform all operations.

    How is that different than not using an ORM? Your application needs to be granted appropriate permissions whether or not it's using an ORM doesn't it?

     

    Here I was assuming the more common usage of ORMs in terms of letting them dynamically general the SQL (which is why I was saying that as well as non-ORM embedded SQL). Calling stored procedures (like you are doing) from an ORM is no different. The issue isn't ORM vs not-ORM, it's module stored in SQL Server vs SQL submitted from app (whether embedded by the developer or generated by an ORM, whether using prepared statements or not). Submitting a batch of T-SQL to be parsed and executed by SQL Server requires that ALL permissions needed for the operation be granted to the app login (unless using an App Role, but that merely shifts the mess to the App Role itself). If someone either manages to log in as the app login, OR if someone manages to inject their own SQL, then they have all of those same permissions.

    On the other hand, when putting the SQL into modules and using Module Signing, the farthest a hacker can get (at least in those two scenarios of attacking via the app login) is just calling the stored procedures that the app login has execute permission to. There are no database roles (db_datareader, db_datawriter, db_owner) being used. there are no SELECT or DML permissions granted to the app login. There is no VIEW SERVER STATE or any instance-level permissions granted to the app login. All the app login can do is:

    1. connect to instance
    2. access any DB on the instance that the login has been allowed to access
    3. execute the modules (most typically stored procedures) that the app login has been granted EXECUTE permission to.

    That's it. Nothing more. The hacker can use the given data API. And yes, someone can still do damage with even that much access, but they won't be able to do a single thing that is not part of that API.

    To illustrate: granting the app login the instance-level permission of VIEW SERVER STATE just so that they can see their own connection info in sys.dm_exec_connections, or see all sessions using sys.dm_exec_sessions, means that there are not restricted from ANY benefit or usage of that permission. BUT, using Module Signing to grant VIEW SERVER STATE to the proc means that only those who can execute that proc can make use of that permission, but not make use of it outside of what the code within the proc is doing with it. Meaning, if the proc is selecting from sys.dm_exec_connections WHERE [session_id] = @@SPID then that is as far as they will ever get with the VIEW SERVER STATE permission, because the login doesn't have that permission, only the code does, and the code only uses it for that one very limited purpose. Hence, highly granular permissions 🙂

    dmbaker wrote:

    Solomon Rutzky wrote:

    Someone did mention that ORMs (at least Entity Framework -- EF) allows for using stored procedures. Excellent point. This does allow for a hybrid scenario. 

    Yeah, that was me, but I wasn't talking about a "hybrid" scenario. When I'm using Entity Framework, I do all my access through views and stored procedures, and I don't allow my application access to anything but those views and procs. Nothing "hybrid" about that.

     

    Yes, I understand. I wasn't meaning to imply anything about your particular usage, sorry if it came off that way. I was merely wanting to not take credit for info already provided. I appreciate that someone (you) mentioned a lesser-known capability. And, I was using that ability as a jumping-off point for the specific usage that my coworker and I came up with.

     

    Take care,

    Solomon...

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance.  I'd expect more for a commercial application but there is a popular commercial application that not only creates all of the SQL dynamically, but it also creates tables and its own objects as configurations are changed in the app.

    The developers only deal with the API and are totally ignorant of the TSQL code being passed to the database until we show them the captured SQL from the performance monitoring app.

  • jarick 15608 wrote:

    It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance.

    I've dealt with applications like that. The developers are basically lazy and do not want to sort out proper security roles. Not only have I had databases requiring all users to use the SA account, but they wanted full admin rights to the server as well. Guess what didn't happen? :O

  • jarick 15608 wrote:

    It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance.

     

    Yes, this is exactly what I am talking about. SQL Server is a large and complex system, and thus even people who have been working with SQL Server for many years don't understand this security stuff. And there is very little info out there on this particular topic. This is why I started consolidating info on this topic into a Module Signing-specific site, and written several posts on the topic (and several more on the way), and even present about it at SQL Saturday (when possible). But, if SQL Server folks (in general) don't really understand how to not log in as dbo / sa / sysadmin, then there isn't much hope for non-SQL Server folks.

     

    Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Steve Collins wrote:

    "...from a developer point of view..."  Why is it "more difficult" to build with stored procedures:

      1. It makes your entire project dependent on the DBA.  Not as satisfying as being the hero dev who does it all.
      2. If you work with a bad DBA your projects are sunk.
      3. It means you'll probably be given deprivileged logins (as is it should be) which means you're hierarchically underneath whoever issues the credentials (probably the DBA)
      4. Using SP's imposes workflow and technical choices on the developer.  In order for a C# dev to use a procedure they have to either use ORM (to partially automate) or manually translate and re-code each input/output variable, create a method (1) to call Sql, create a method (2) to evaluate the result from sql and then translate the output into a format usable by the application.   It's more work.
      5. The manual option for running code ADO.NET is a mishmash of alternate methods some of which are not type safe.  The whole database/C# connection is not thread safe.  So there's specific knowledge about exception handling it's would be good to know.  Resilience and error logging are DIY.  For Sql to C# error logging there are no standard interfaces afaik.
      6. The appropriate C# SQL type libraries are relatively new and have little functionality compared to the base CLR types.  None of the ORM's including EF use the type libraries appropriately afaik.
      7. From a C# perspective it's not unit testable because there are persistence constraints.  "Non testable" means less manageable and less confidence to migrate.  Doesn't easily integrate with "modern" CI/CD pipelines.
      8. It can be very difficult to debug.  It sometimes tricky to know whether a bug is caused by SQL, ADO.NET, or C# or some combination.  "Linting" or console sink logging is DIY.
      9. To be really effective you have to know Sql well

    This may certainly be your experience, but this isn't necessary.

    1. Not true. Devs can write stored procedures
    2. A bad DBA or a bad Dev causes issues.
    3. security can be configured to allow devs to work in their own schema, sandbox, or other config. This isn't hard.
    4.  ORMS and LINQ can use stored procs - https://weblogs.asp.net/scottgu/linq-to-sql-part-6-retrieving-data-using-stored-procedures
    5. Not sure this impacts procs or no procs
    6. Again, there are facilities to use ORMs, C#, ADO.NET, OLEDB, etc.easily to call procs. A little, very little, knowledge is needed.
    7. You can unit test, either in the app or the db. Saying devs don't know how to do this or it's hard is a poor excuse. This was the testing excuse for years and the last 5-10 years, devs have adopted unit testing. It's not hard to do this.
    8. Debugging is a skill. It's no harder to debug a proc than a sql query. All you're changing is the interface to call the code in the db engine. Embedded SQL or a proc name.
    9. This doesn't change with procs or no procs. It should also be considered part of your job.
  • austin.mcguire wrote:

    In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL).  Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor.  Using DB abstraction tools like EF  make your code more DB platform agnostic, but they typically do not directly support SPs.  Like David Poole's Points 6 and 7, you have to know the requirements and if DB Platform Diversity is a requirement, that pretty much rules out SPs.

    I've worked in software across platforms. You can easily astract programmability modules across most platforms. Maybe not all, but most. Write once, suck everywhere isn't a good strategy, and too many vendors adopt this.

    Use procs, get abstraction, hire the SQL Server, Oracle, PostgreSQL people you  need.

  • Kevin Steffer [MCP] wrote:

    When working with SP inside a .net project feels like Blackbox development. The Code you write looks like it assumes too much and needs a magic trust to what data an SP will return.

    With Linq2sql you get compiler warnings when you cast types wrong or misspell fields etc. That is much faster to develop with Linq2Sql and less error prone.

    I'd argue this is something you should be able to deal with. There aren't that many types and you can scaffold this out easily. I would also argue that your speed of development is overwhelmed by the limitations of tight coupling. We use methods to abstract and enable refactoring. Views and procedures are part of this in RDBMS development.

  • GeorgeCopeland wrote:

    I am loving these excellent comments.

    I will point out that developers have a very short and predictable list of persistence requirements: load a grid, populate a form, save on click.

    If DBAs take care to lay down an architecture that facilitates standard functions, developers will use it.

    So I guess the upshot is: If you are cracking the whip on developers to implement standard procs, you are cracking on the wrong team.

    I gave all developers a proc that writes procs. Build a table, run the proc and you have insert/update/delete versions of access for your table, with security permissions attached.

Viewing 15 posts - 31 through 45 (of 71 total)

You must be logged in to reply to this topic. Login to reply