Friday, January 26, 2018

Do you treat SQL code as a first class citizen?

Too many times I have seen developers treating SQL code and the database itself as a second class citizen. They would go into a production database and make stored procedure or table structural changes directly in production. The database was an afterthought and not carefully crafted and discussed the way that their parts of the product coded in their language of choice was, be it C#, Java, C++, etc. In my opinion this is wrong, it leads to bugs and technical debt and for medium to large systems it often leads to performance issues. It is similar in some ways to treating test code as a second class citizen, where one then spends an inordinate amount of time looking for bugs in production code that are actually in the test code because the same care wasn't taken with the test code as the production code, but that is a soap box for another post.

What do I mean as SQL code as a first or second class citizen? What I mean by that is that you give it the same level of care and attention as the rest of the code in your system.

If you treat SQL code as a first class citizen, I expect to see the following:
  • Just as you treat the source code in source control as the source of truth you treat the database files in source control as the source of truth. It is very telling when a team treats a database as the source of truth. When this is done in most cases, the team cannot recreate the database from scratch, which makes integration and system testing more difficult. When a team is constantly adjusting the files in the source control to match a database that is an indication that the source control is not being treated as the source of truth. Would you adjust binaries and then go make the source code match?
  • Just as source code for the other languages you code in are stored in source control, so are the SQL files. This is regardless of if you deploy the database as a model or via files, you ensure that the artifacts needed to deploy the database are stored in source control. These may be a model or individual SQL file.
  • Just as the team takes the time to properly chose algorithms and data structures in their primary language, they make sure to do the same in SQL code. They make sure that the correct key and constraints are in place, using the 
  • Just as you author a deployment system for your code, you do the same for the database, in some case they can be part of the same deployment process.
  • Just as your team has coding standards for your primary language(s), you also have codding standards for SQL. (You do have standards right?) These standards should all be at the same level of detail.
  • Just as your team code reviews their other code, they review SQL and apply the same rigor to those reviews.
I am sure there are more comparisons, but as you can see it is simply a matter of treating the database in all aspects with as much care as we treat our other languages, from source code to design to deployment.

Please feel free to share your thoughts. 



No comments:

Post a Comment

The 2024 State of DevOps Report and the Importance of Internal Development Platforms

On the State of DevOps Report The State of DevOps Report, published annually by the DevOps Research and Assessment (DORA) team, has been a c...