Custom SQL Best Practices

Slate is designed to be used and administered by non-technical users who have no previous experience with Structured Query Language (SQL). In most circumstances, custom SQL is not necessary. In others, we can create a new tool that obviates the need for custom SQL.

SQL is the language used to communicate with a relational database. Every time you run a query or access a record, SQL is used to read data from the database. When you make changes to records, through a data import, form registration, or even directly on the record, SQL is used to write data to the database. Slate has tools that generate the necessary SQL for these processes, without the need for users to know how to read or write SQL. There are several features throughout Slate that can accept a custom SQL query or script, however, that will change the behaviors of these reads and writes. These features include Queries, Query Filters/Exports, Rules, Portals, and Teasers. It is these areas, where you are creating and directly typing custom SQL, that are discussed in this article.

Reasons to avoid the use of custom SQL

  • Using standard Slate functionality increases transparency in your processes. When custom SQL is used, it immediately restricts and limits the number of Technolutions resources who will be able to assist you with processes these processes. It also prevents any colleagues who do not have extensive SQL knowledge from understanding and administering the process.
  • Standard Slate functionality, including exports and filters in the Slate Template Library, is optimized for performance and tested to perform reliably even under heavy load. The custom SQL query that runs in 30 seconds today could take 30 minutes or more on an application deadline, which could cause lock contention, which in turn can cause errors when applicants are attempting to submit applications or when readers are attempting to submit review forms.
  • As your process changes, or you prepare for a new year, many items require small tweaks. This is especially true of items that use custom SQL. Since only certain users are knowledgeable enough to make these updates, having custom SQL code in your instance creates an administrative and technical bottleneck.
  • Updates to Slate are published regularly throughout the year, and while changes to standard functionality are thoroughly tested, these tests are unable to account for custom SQL that may exist in partner databases. As such, the use of custom SQL can increase risk and make your processes very fragile.
Was this article helpful?
2 out of 12 found this helpful