We can define a framework in two simple ways:
- An environment with tools to work with.
- A scaffold around which we can build an application.
These definitions sound simple in an abstract way, but what is a SQL framework? How will it help me, and do I have to use one?
For the questions “what is a SQL framework?” and “how will it help me?”, we’ll use a specific framework built around specific features. But first I’ll answer the last question, “do I have to use one?”
Software Frameworks
Software frameworks have distinguishing features that differentiate them from libraries or normal user applications, such as:
- Inversion of control. In a framework, unlike in libraries or normal user applications, the framework dictates overall program flow of control.
- Default behavior. A framework has a default behavior. This default behavior must be some useful behavior and not a series of no-ops.
- Extensibility. A framework can usually be extended by the user by selective overriding or specialized by user code providing specific functionality.
- Non-modifiable Framework Code. The framework code, in general, cannot be modified. Users can extend the framework, but not modify its code.
Technically, you don’t need a framework. If you’re making a really simple site (think of the web circa 1992), you can just do it all with hard-coded HTML and some CSS. And if you want to make a modern webapp, you don’t actually need to use a framework for that, either.
You can instead choose to write the logic you need yourself, every time. Write your own data-persistence/storage layer, or — if you’re too busy — just write custom SQL for every single database access. Write your own authentication and session handling layers, template rendering logic, exception-handling logic, security functions, the unit test framework to make sure it all works fine, and your own…well, the list goes on for awhile.
The Benefits of a Framework
If you do use a framework, you’ll benefit from the good, usually peer-reviewed and well-tested work of dozens (if not hundreds) of other developers, who may well be better than you. You can rapidly build what you want, without spending time building or worrying too much about the infrastructure items above. You can get more done in less time, and know that the framework code you’re using or extending is very likely better than what you could produce yourself.
And the cost of this? It’s an upfront investment of time learning the framework. But — as virtually every web dev out there will attest — it’s an investment that will pay off many times as you realize massive (really: massive) benefits from using whatever framework you choose.
If I told you to cut a piece of paper with dimensions 5cm by 5cm, you could easily do it with a couple measurements and scissors. But if I ask you to cut 1,000 pieces of paper of the same dimensions, you won’t do the measuring 1,000 times. You’d save time and make a frame of 5cm by 5cm and cut 1,000 pieces of paper in less time.
In that example, you made a framework, a template of sorts, which would do a specific type of repetitive task. So it is with a SQL framework: instead of manually performing the same task again and again, create a framework with those facilities in one nice packet. The framework provides the abstraction for your application and, more importantly, many applications.
Simplify Repetitive Tasks
In general, a framework is a real or conceptual structure that supports or simplifies a process. Suppose you have to make tea on a daily basis with several ingredients like sugar, tea leaves, other spices, water, etc. When you begin, it’s a chore to add the ingredients in the right ratio every time, opening containers one by one. And then one morning you have the big idea: mix all the ingredients in one jar at the correct ratio. That way, every spoonful has the right ratio for the tea in one step (except for the milk and water, of course).
This jar is your framework, which saves time and also provides the correct components in your applications, every time. Fast and easy.
Are there any mundane tasks that are repeated multiple times over in the SQL world in the realm of T-SQL coding? Troubleshooting and testing code come to mind immediately. It’s done every single time you write a line of code. For me, Error Handling, logging and auditing are huge time sinks.
When I start a project, it takes a little while to get dialed in and focused on all the tasks for the total solution. Once I have shut everything else out and start creating objects, I am not putting in the error handling and logging that should be part of an enterprise solution. I usually have to go back and put all that in once I get a good proof of concept achieved.
In my experience, error handling and logging are the perfect set of tasks that should be offloaded to a SQL framework. This enables developers to work within the confines of a reliable mechanism to achieve enterprise robustness of the tedious process. It also helps me rapidly lay down efficient code to solve problems.
Creating a Template
In this multi-part series, we are not only going to examine frameworks, we are going to build one. We’ll combine the repetitive tasks, with a few other features, and develop the objects. The finished product will be a template we can use every time we create a stored procedure.
More specifically, we are going to create a framework that will do our error handling and logging. Plus, we will create a batching mechanism that will provide additional multi-functional logging. This framework addresses the specific tasks mentioned above to give us something functional and ready by the end of the series.
The framework we create will be in a single database. Our code (stored procedures) will be in this database and not in the user/application databases. We do not want to duplicate this code in every database. We could but that is not efficient and goes against the purest tendencies of the Boyce-Codd fundamentalist in me. There are some advantages to this: We will have to use three-part naming schemes throughout our code ([Database Name].[Object Owner].[Object Name] IE [master].[sys].[databases]).
While this may seem like extra work, it is a best practice to use three-part naming. It also cuts down on troubleshooting because you are being more surgical with naming the source object.
Your code is in one place, which means one connection string for developers, reports, client applications, etc. If one had any security concerns, we could encrypt the stored procedures. If anyone had access, they would only have access to an empty database with just code and base data for data-driven queries against the framework.
Don’t Miss Part Two
In the next post in this series, we will create the framework database and the objects needed to support our first feature, the Error Handling processes. We will create a dynamic error process that will be less cryptic than some of the error codes native in SQL Server. We do this by customizing the error event at runtime and producing clean messages. These will not only alert us to a problem but point us exactly where we need to start looking to troubleshoot the issue.
