In software development, when developers want to limit how much potential access netizens, pranksters, or malignant actors have to a database, we leverage things like stored procedures, parameterized queries, parameterized stored procedures, DTOs (Data Transfer Objects), ViewModels, e.t.c.

Today we're going to look at something that feels like a DTO or a ViewModel, yet resides more in the database than in the code. Today we'll look at SQL views. It's important to recall that DTOs and ViewModels exist to prevent overposting or potentially exposing more information than is necessary. With DTOs and ViewModels, the developer can write code that cherry-picks data from one or more data sources (let's just assume they're tables). What this does is protect sensitive data from being exposed when netizens need data from a particular table.

A relatable explanation

For example, when you log into Hashnode, you're allowed to see content posted by people you follow, connect with or content that is liked or commented on by people you follow and/connect with. This means that in a way, you more or less have access to rows of data that is particular to other users. If Hashnode's data protection is weak, a malignant hacker can access the login details of every user that he/she connects with. This is because content that users interact with is somewhat linked to their profile, and their profile is a table on the database that has every detail, including passwords.

Before you get worried, it's highly unlikely one can successfully hack users this way. This is because at no point are you exposed to the total information of a user, things like DTO makes it possible to select only as much as you need from multiple sources and create a closed data table. It's generally accepted that implementations and optimizations at the database level make applications faster by comparison than implementation within the application. Calls to a database are expensive, however, the database can perform manipulations faster than having those same manipulations within the application.

Similarities between DTOs, ViewModels and SQL Views

SQL Views are like DTOs and ViewModels, the only difference is that SQL Views are done at the database level which saves the application time and resources that would have been used in making multiple calls to the database. SQL Views don't duplicate data, in the real sense, an SQL view table doesn't exist in terms of storage space, it's a virtual table that simply exists and can perform a lot of normal SQL table operations without exposing all the data from the real SQL table.

Finally

Some believe SQL Views provide an added layer of security, be that as it may, it's agreed there's a speed advantage and reduction in the number of complex processes applications performed by an application while hiding a lot of sensitive information. To read more about how to create SQL Views click here.