When interacting with Databases, modern JS frameworks have kind of made it standard to work with ORMs.

ORMs like Knex.js, Lucid in AdonisJS.

These ORMs are very helpful with schema type safety, migrations, clean code, abstractions.

But are these abstractions always the best; especially when the cost is learning actual SQL.

Knowledge of advanced SQL helps with optimizing your db performance, when these ORMs reach their limits.

SQL Features like:

• Subqueries

• Virtual tables

• User-defined Functions

• Stored procedures…

are what you turn to when you have to optimize your DB for scale and performance.

SUBQUERIES:

These are inner queries placed within an outer query.

The inner query executes first; then its results are passed to the outer query.

Subqueries can be useful in scenarios where performing a JOIN is not optimal.

eg.

SELECT full_name, sex 
FROM teacher_tb 
WHERE unique_id = (
    SELECT teacher_id 
    FROM assign_form_teacher_to_class 
    WHERE unique_id = 'EN4522922970'
)

VIRTUAL TABLES(VIEWS):

These are tables containing specific data, abstracted from one or more tables.

They can help to focus on a subset of a table's data; or returning specific data from different tables without running multiple queries.

So instead of providing users with the whole unnecessary data in a table, you can provide just the subset needed; and you can even compile the data from multiple tables while providing specific data for the task needed.

Views also help with optimization by performing the complex queries in the background, so the client can just query it without running the complex queries themselves.

Eg.

A scenario where you have two tables, a teachers_table and an assign_subject_to_teacher_table( where you attach teachers to a subject).

With the query below, you can create a virtual table that returns just the teachers and their respective subjects immediately.

CREATE VIEW teachers_subjects AS

SELECT teacher_tb.unique_id, assign_subject_to_teacher.subject_id

FROM teacher_tb

INNER JOIN assign_subject_to_teacher

ON teacher_tb.unique_id = assign_subject_to_teacher.teacher_id

With this virtual table created, your application won't need to run queries across the two tables to get the subjects that a teacher is teaching.

When you do something like this in an ORM

let teachers_subjects = assign_subject_to_teacher -> teacher_tb

Where it returns the subjects and teacher attached to it, it possibly runs a JOIN in the background. Or something of the nature above.

USER-DEFINED FUNCTIONS (UDF):

UDFs allow developers to write custom logic for their databases.

You may need to perform a calculation across different columns frequently, if the operation cant be done simply with an arithmetic operator, then UDFs might be the way to go.

You can write aggregation functions in them, reuse queries or logic in them etc..

Eg.

A scenario could be where you want to apply a discount to users' orders.

This can easily be done on the application level in your controller.

But as databases get larger, it might become optimal to perform this calculation on the database level.

CREATE FUNCTION GetDiscountCost(Cost DECIMALS(5,2))

RETURNS DECIMAL(5,2) DETERMINISTIC

BEGIN

   IF(Cost >= 100 AND COST < 500)

   THEN SET Cost = Cost - (Cost * 0.1);

   ELSE IF (Cost >= 500)

   THEN SET Cost  = Cost - (Cost * 0.2);

   END IF;

RETURN (Cost);

The SQL code above will apply a 10% and 20% discount respectively to the cost of the orders within the range in the condition above.

> SELECT GetDiscountCost;

> DROP FUNCTION GetDiscountCost;

The SELECT command will help you call the UDF into action.

The DROP command deletes it when it's no longer needed.