Node Virtual Table Practice ( View Table )

Introduction

This is a NodeJS application that uses Sequelize ORM to create a virtual table. The virtual table is created by joining the main quiz table with the multi-language (Ml) table.

Installation

1. Clone the repository
2. Install the required dependencies using npm
3. Create a .env file and add the following variables.

How to create a virtual table.

1. we can create a virtual table by using the following query.

CREATE OR REPLACE VIEW quizzes_view AS
SELECT 
    m.id AS id,
    q.id AS quiz_id,
    m.language_id,
    m.name,
    m.description,
    q.image,
    q.isFeatured,
    q.isPopular
FROM quizzes q
JOIN quizzes_ml m ON q.id = m.quiz_id;

This way we can create a virtual table that contains all the columns from the main quiz table and the multi-language (Ml) table. This query will run automatically when the application starts,if we add into root file index.js.

2. We can mannualy create a virtual table by using the following query.

CREATE VIEW quizzes_view AS
SELECT 
    m.id AS id,
    q.id AS quiz_id,
    m.language_id,
    m.name,
    m.description,
    q.image,
    q.isFeatured,
    q.isPopular
FROM quizzes q
JOIN quizzes_ml m ON q.id = m.quiz_id;

In workbench, select the database and click on button-i in right side of schema name and then click when open database info window we can see the tab of views.

3. Production approach

We use a Hybrid Initialization Strategy. Since we have multiple models, we don't use a global sequelize.sync() because it doesn't distinguish between physical tables and virtual views.

Instead, we implemented a flow that:

Filters the Models: We tagged our View models with an isVirtual flag.

Automates the Schema: The system automatically identifies and syncs all physical tables first to ensure the schema is up-to-date.

Injects the View Logic: Once the tables exist, the server executes a raw SQL CREATE OR REPLACE VIEW command.

---

A Virtual Table (View) is a saved SQL query that looks and acts like a real table but stores no data itself. It is a dynamic window into your physical tables.

The 3 Main Uses: Simplification (Abstraction): It hides complex JOIN logic. Instead of writing a 20-line query with 5 joins every time you need data, you just run SELECT * FROM QuizzView.

Data Security: It allows you to share specific data without giving access to the whole table. For example, you can create a view that shows a user's name and score but hides their password and email.

Consistency: It provides a "Single Source of Truth." If you change your underlying table structure (like splitting a table in two), you can just update the View definition, and the frontend code doesn't have to change at all.

In our specific case: The View acts as a "Translation Layer," merging our base Quiz data with the QuizMl (Multi-language) data so the app sees one clean object instead of two separate pieces.