Practical introduction to relational databases

Fetch data with where clauses

When you have an application that has a sizable amount of users each generating some amount of data, you will quickly end up in a situation where grabbing all the data is not going to be good enough.

For example if you have a blog and you want the posts written by a particular author, you could go two ways about it

  1. Fetch everything, but only show what you want
  2. Only fetch what you want, but show all of it

Fetching everything, filtering client side

illustration showing fetching all results in a database with only some of the results highlighted

This approach depends on your use case and the data you hold. For example if you have a products page with interactive filters, this might be a good approach. Fetch all the products, but depending on the current state of the filters, only display those that are in stock, and come in colour green.

In those cases there's really no reason to keep fetching essentially the same data with different parameters when the requirements might change.

This however is wholly unsuitable when it comes to data you want to protect in some way, and DEFINITELY when we're talking about data that's Personally Identifiable Information (for the Americans), or Personal Data (for the EU folks re GDPR).

Only fetching what we need

illustration showing fetching only data that we need

This delegates most of the work of finding the right data to the database itself. Depending on how the indexes are set up (which you can read about here to skip ahead), the operation may be super fast too. From a network point of view, returning a small amount of data is always preferable to returning large amounts of data, not to mention the memory requirements of dealing with lots of unnecessary data in the browser.

Oh, and if you don't have the data in the browser, there's no way someone can look at the source code / inspect the javascript on the front end and figure out what you haven't shown them.

All javascript / html / css is public

Though data security is not the focus of this here info site, it is very important to mention that once any data has been sent to the front end, that data is public on that computer / client.

You may think, "but hey, it's encrypted", but in order to make sense of encrypted data on the front end, you have to decrypt it. To do that, you need a key. That key needs to be available to the front end, and as such, needs to be reachable from the browser, which means your user can also figure out what it is.

Treat any and all code you send to the client as publicly available.

Okay, on to the last part about fetching data: ordering things, grouping things, and assorted bits.