Every developer has a different opinion of stored procedures. If you’ve worked on a few projects that involve databases, you’ll inevitably come across the as part of a project.
I’ve personally had a love/hate relationship with them. They require maintenance scripts like your regular table structure, but they aren’t structure. Generally, databases don’t give you good introspection ability for stored procedures either - fire-and-forget management.
Classically, stored procedures have been a bastardized version of the database’s native SQL. Take the basic SQL, add in some cursors, looping constructs and function invocation - that’s your stored procedure language. Depending on your database vendor, be prepared to deal with different syntaxes, punctuation and functionality.
No matter what, you’ll be writing your application in a different language, making the number of languages involved in any project with stored procs N + 1. If you choose to evaluate a different database vendor for a different project, or version 2.0 of your current project, your previous stored procedure may or may not translate.
Over the last decade and a bit, database vendors have been adding native language bindings to the database itself, allowing developers to write code in languages they are comfortable in, regardless of the database storage technology behind the scenes.
I’ll step back for a bit here - the role of application storage has been changing recently, pushed by a number of factors:
- Data-binding frameworks, like Hibernate, SQLAlchemy, Ruby on Rails and a number of others have been creating an abstraction layer over different database brands, as well as managing the flow of data between the live objects in the system.
- SQL-free large stores like Google’s BigTable, CouchDB and others have been pushing complexity out of the datastore and into the application itself.
- Languages with dynamic typing are in-vogue. Python, Ruby and PHP are popular, Java will be getting dynamic dispatch soon.
- New accessible structured storage formats, like Apache Thrift and Google’s Protobuf are allowing developers to create highly versionable “blobs” that are easy to write to, but hard to index and query.
Databases are going to have to evolve over the near future to suit the way that applications and hardware are developing today. Memory is cheap, disks are huge and time-to-market is one of the primary driving forces for developers.
I believe that the ideal role for a database is to be a high-powered host for serialization framework stubs. Instead of having to map application objects from database tables using clumsy SQL generation techniques and reconstructing objects from deep JOINs, the serialization frameworks should be injecting small stubs next to the database process itself. These stubs can coordinate data retrieval with the database itself - managing, grooming and traversing indexes, altering storage as needed for client-side data and sending appropriately structured data to the application.
Drizzle is a large step in the right direction. Instead of providing the smörgåsbord of features like the other DB vendors on the market, it provides a light-weight, modular architecture that allows you to swap and re-plug components like you would your application itself.
Here’s my plea to database developers: Let me compose my database server like I do my Java code, from appropriately tailored components optimized for my use. Let me run my Java/Python/Ruby code on the database server, right next to the data itself, in a language that I’m familiar and comfortable with - running on both sides of the database connection TCP connection.Read full post