Dev Corner

Why you should think twice before writing custom SQL

A database is often the single point of failure in an application. If the database goes down or becomes unavailable, the entire application can become unusable. This can happen a couple of ways, but it often involves an unchecked or unoptimized query locking a high traffic table or causing high and sustained CPU usage. At Q2, this is a worst-case scenario as it can impact multiple financial institutions. While we have solutions and monitoring to manage this, those solutions are reactive, and not proactive. Therefore, we are vigilant in reviewing any SQL before putting it in our databases. Our first line of defense is the code review, and we want to be sure that not only is performance being considered, but there is some thought to data security.

Recently, we have seen an uptick in custom SQL in submitted code and have made some adjustments to our review policies to strengthen our first line of defense. First, we want to discourage custom SQL as a go-to solution. Custom SQL should only be used if you cannot implement your functionality using the built-in SDK DBObjects or other SDK functions. DBObjects have been built with performance and security in mind and they have been thoroughly reviewed by our internal database experts. The inclusion of custom SQL will add time to reviews and deployments because extra time is needed to get sign-off from other relevant teams. We encourage anyone who is considering this solution to reach out to the SDK team with questions and consultation. We also reserve the option to block deployments that include SQL where there are other solutions already offered by the SDK. We are serious about the safety of the technology stack, especially for this single point of failure.

Please reach out to the team with any questions. We have also published a list of things we are looking for in our SQL reviews (https://docs.q2developer.com/guides/database/custom_sql.html#reviewing-custom-sql).