As developers, we can often be dogmatic about our choice and usage of programming languages. Even if there may be a better tool suited for the specific task at hand, we can sometimes get carried away with wanting to use our favorite language for everything.
One instance in which this can occur is when you’re working with a SQL Database for your application.. While it’s entirely possible to access the data from a database, and then perform manipulations on it using the programming language you’re working with; a much better approach is to leverage SQL’s ability to perform those manipulations whenever it’s possible to do so.
Some examples of when you may want to use SQL directly for processing data, rather than the programming language you’re writing your application in, are:
- Summing data
- Calculating the average of a set of data
- Sorting data with a custom formula
- String manipulation, such as concatenation
- For example, getting the full name of a user from the FIRST_NAME and LAST_NAME properties
For example, if you were using SQL to concatenate the full name of a user using their FIRST_NAME and LAST_NAME entries in a database, you could do so using this query:
Doing this gets you the data that you need to present to the rest of your application directly from the SQL database, without having to do extra data manipulation on formatting with the programming language that you’re using to write your app.
Using SQL when you’re able instead of writing code accomplishes a couple of things. First, it ensures that the data will be presented to the application in a consistent format regardless of the programming language being used. Let’s say, for example, that a web app is being rewritten from a Django app to a React app. If the operations for manipulating the SQL data are all written in Python using the Django framework, then the rewriting of the app to using React in JavaScript will take longer if the manipulations are written in one programming language and have to be rewritten to another one.
Leveraging SQL where you’re able to has benefits if you’re writing a cross-platform application, too. Let’s say your application needs to work in the web browser, native iOS, and native Android. If the operations for interacting with SQL databases are written in SQL, then they only have to be written once - you won’t have to rewrite them using JavaScript for the web, Swift for iOS, or Java for Android. This cuts down on the number of possible points of failure in an application, since these operations are written once; rather than three times.
If these data manipulation operations are all written using SQL itself, then the data will be in a correct and consistent format regardless of the language that is being used to access that data. You won’t have to rewrite these operations in a different programming language, because they will be in SQL from the start.
Using SQL directly to access and manipulate the data you need from your database to present to your application ensures that the query logic will be consistent across future application rewrites, will work across multiple platforms, and will always be presented in a consistent format.
