So, you are an expert at SQL Server, PostgreSQL, Mongo or Oracle. Great! Do you have control of how the data is consumed? I am not speaking strictly with stored procedures or the database itself but how the data is used in your middleware or API. Guessing the answer is no. “That’s not my role” or “the developer does all that” is what I hear all the time. I ask Why?
The Repository part of development should be attached to the Database Professional role. We have a say in database design / modeling, creating the structure, the stored procedures and hand out permissions. We should also write the communication part of it too and expose the data to be consumed. Currently, we hand this off to the developer and then hope it is implemented correctly and is some cases fall short in delivery.
I have seen many applications and APIs perform slowly because of not understanding the complete picture when working with data. Random loops calling stored procedures when a User Defined Table Type could have been used or pulling way too much data only then to filter out on the application side. Performance is everyone’s concern and should not be taken lightly. We as a community need to step up, expand our horizons, dig deeper and make strides in overall application performance. Owning the repository will do this.
What is a Repository?
A Repository is an object that has interface mapping layers between the domain and data. Basically, the repository is like the middleman between application and database that controls how we retrieve data. Typically, in the enterprise environment, the repository would sit between the business layer and database.
Why do we need a Repository Layer?
Today’s applications need data and scalability. Following the practice of the Repository Design Pattern is general practice and highly recommended. As we stated earlier, the repository is the middleman between the application and database. We need this to control the data flow and build reusable code. One of the most important parts of this strategy is the separation between the database and data access logic from the application.
Example of a Stored Procedure Call
The following C# example calls a SQL Server stored procedure with three parameters. In this basic example, there is not a return value.
Having control over the database is just one part of the overall picture in data access. If the Database Professional owns the Repository, they can make better decisions on how the data is accessed while protecting the application performance and data flow. It may take a little time to learn C# or whatever your language is, but believe me, its well worth the journey.