DEV Community: Blackwatch

How I Built TERMINAL47: Anonymous Real-Time Chat with Live Translation (Next.js + Socket.io + TypeScript + Express)

Blackwatch — Mon, 23 Feb 2026 15:59:48 +0000

Zero-friction chat app—no signup, link-only access, anonymous "Agent-X" names, set duration for room, auto room dispose once time is over, no data is saved/stored, pure anonymity, live language switching (history bulk-translates + new messages stream). Hacker terminal UI. V1 complete github

Live chat with users sidebar, countdown timer, translation status.

Hey devs!, I wanted to level up my skills, so I combined Socket.io + real-time multilingual chat + cyberpunk UI. Meet TERMINAL47.

The Flow: Share link → Join as anonymous agent → Chat → Switch English→Japanese mid-convo → All past messages instantly translate → New messages are translated in real time →Timer expires → Room erased forever.

🛠️ Tech Stack (Production Architecture)

Frontend: Next.js 16 + TypeScript 
Backend: Express + Socket.io + Lingo.dev SDK
State: useState (local) + localStorage (userName)
Storage: None (pure ephemeral)
Deployment: Local dev → GitHub

Step 1: Backend Setup (index.js)
Clean Express + Socket.io server with dedicated translation endpoints:

// index.js
import express from "express";
import { createServer } from "http";
import { Server } from "socket.io";
import socketEvent from "./socket/events.js"; // Room logic here
import languageTranslationBulk from "./utils/translationBulk.js";
import languageTranslationSingle from "./utils/translationSingle.js";

const app = express();
const server = createServer(app);
const io = new Server(server, { cors: { origin: process.env.CLIENT_URL }});

app.use(cors());
app.use(express.json());


app.post("/auth/translation/bulk", async (req, res) => {
  const { messages, currentLanguage, translateTo } = req.body;
  const languageTranslationFunction = await languageTranslationBulk(
    messages, currentLanguage, translateTo
  );
  res.json({ success: true, data: languageTranslationFunction });
});

app.post("/auth/translation/chunk", async (req, res) => {
  const { message, currentLanguage, translateTo } = req.body;
  const languageTranslationFunction = await languageTranslationSingle(
    message, currentLanguage, translateTo
  );
  res.json({ success: true, data: languageTranslationFunction });
});

socketEvent(io); // Room management

Step 2: Lingo.dev Integration
Bulk translation (history on language switch):

// utils/translationBulk.js -
import { LingoDotDevEngine } from "lingo.dev/sdk";

const languageTranslationBulk = async (messages, currentLanguage, translateTo) => {
  const lingoDotDev = new LingoDotDevEngine({ apiKey: process.env.LINGO_DEV_API_KEY });
  const userMessages = messages.filter((msg) => msg.text != "");

  const translated = await lingoDotDev.localizeText(userMessages, {
    sourceLocale: currentLanguage,
    targetLocale: translateTo,
  });
  return translated;
};

Step 3: Frontend Hook (useChat.ts)

// hooks/useChat.ts
export const useChat = () => {
  const [socket, setSocket] = useState<Socket | null>(null);
  const [roomStatus, setRoomStatus] = useState<boolean | null>(null);
  const [expiresAt, setExpiresAt] = useState<number | null>(null);

  const joinRoom = useCallback((roomId: string, userName: string) => {
    socket?.emit("join_room", { roomId, userName }, (result: any) => {
      if (result.success && result.room) {
        const expiry = Date.now() + result.room.timeRemaining;
        setExpiresAt(expiry);
      }
    });
  }, [socket]);

  const sendMessage = useCallback((roomId: string, message: string) => {
    socket?.emit("send_message", { roomId, message });
  }, [socket]);

  return { socket, joinRoom, sendMessage, roomStatus, expiresAt };
};

Step 4: 🔥 Live Translation Magic (Chat.tsx)
Hero feature—language switch + bulk/single translation:

useEffect(() => {
  if (!allMessages.length || language === lastLanguage) return;

  setIsTranslating(true);
  const translateChat = async () => {
    const response = await fetch(`${process.env.NEXT_PUBLIC_BACKEND_URL}/auth/translation/bulk`, {
      method: "POST",
      body: JSON.stringify({
        messages: allMessages,
        currentLanguage: lastLanguage,
        translateTo: language,
      }),
    });
    const data = await response.json();
    setAllMessages(data.data); // All history translated!
  };
  translateChat();
}, [language]);

Switch language → History bulk-translates + new messages auto-translate.

Step 5: SideNavBar (Users + Timer)

// SideNavBar.tsx - Key features
const SideNavBar = ({ userCount, expiresAt, language, setLanguage, isTranslating }) => {
  const [formattedTime, setFormattedTime] = useState("00:00");

  useEffect(() => {
    if (!expiresAt) return;
    const tick = () => {
      const remainingMs = expiresAt - Date.now();
      const totalSeconds = Math.max(0, Math.floor(remainingMs / 1000));
      const minutes = Math.floor(totalSeconds / 60);
      setFormattedTime(`${minutes.toString().padStart(2, '0')}:${(totalSeconds % 60).toString().padStart(2, '0')}`);
    };
    tick();
    const interval = setInterval(tick, 1000);
    return () => clearInterval(interval);
  }, [expiresAt]);

  return (
    <aside className="w-12 md:w-64 bg-secondaryBackground">
      <div>USERS- {userCount}</div>
      <LanguageSelection language={language} setLanguage={setLanguage} />
      <div className="timer">{formattedTime}</div> {/* Red border when <60s */}
    </aside>
  );
};

🚀 V1 Complete ✅
✅ Real-time chat (Socket.io)
✅ User presence + system messages
✅ Live translation (bulk + streaming)
✅ Room expiry countdown
✅ Hacker terminal UI
✅ Anonymous access (no auth)
✅ TypeScript everywhere

Next: Typing indicators with Redis V2? Clone the repo and try language switching—feels like magic! 🪄
Github

QUICK SQL REVISION

Blackwatch — Sun, 04 Jan 2026 14:33:01 +0000

While revising SQL, I realized that I keep making the same notes again and again. So I decided to document everything in one place for quick reference.

As I was writing, I thought—why not share it as an article? It might help others who are learning SQL or revising for interviews.

This article covers the core SQL concepts and is intended as a quick revision guide as well as an easy starting point for beginners.

SQL

What is SQL?
SQL stands for Structured Query Language and is the standard language used to interact with relational databases. It allows us to:

Create database structures like tables, views, and indexes.
Perform CRUD operations (Create, Read, Update, Delete) on the data stored in those tables.
Define permissions and control access to the data.
CRUD expands to:
C – Create
R – Read
U – Update
D – Delete

What is a database?
A database is a structured collection of data stored in a digital system so that it can be efficiently accessed, managed, and updated. It acts as the backbone of most applications because it persists user and system data; without it, most apps would lose their state and real-world usefulness.
Databases are broadly categorized into:

Relational databases
Non-relational (NoSQL) databases

Relational databases
In relational databases, data is stored in tables (rows and columns) with a predefined schema. This structure:

Makes it easier and faster to query data using relationships (joins) between tables.
Requires designing the schema up front, so a good understanding of the data model is needed before implementation.
Examples:
MySQL
PostgreSQL
SQL Server
Oracle

*Relational databases use SQL as their primary query language

Non-relational databases
Non-relational (NoSQL) databases do not rely on fixed tables and schemas like relational databases. Data can be stored as documents, key–value pairs, wide-column stores, or graphs. This:

Makes it easier and faster to get started because the schema can be more flexible or even schema-less.
Can become harder to reason about as the data model grows if there is no consistent structure.
Can scale very well horizontally for certain workloads, though query patterns and consistency guarantee differ from relational systems.
Examples:
MongoDB (document-based)
Redis (key–value)
Cassandra (wide-column)

Data Types
Before moving forward, it is important to understand the concept of data types in SQL. Like most programming languages, when creating a table, we must specify the type of each column so the database knows what kind of data it will store and how to validate it.
Data types help:

Enforce that data is stored in the correct format (numbers, text, dates, etc.).
Optimize storage and performance by choosing appropriate sizes and representations.

Most Common Data Types
SQL offers Different Data Types but here we will discuss about most common data types.

1. CHAR

CHAR(n) is a fixed-length character type used to store strings with a maximum length of n characters, for example CHAR (21).
The database reserves space for exactly n characters for each value; if the string is shorter, it may be padded with spaces internally.
If you try to insert a string longer than n, it will either be truncated or cause an error, depending on the database and settings. Use cases: values with fixed length such as country codes, status codes, or fixed-format identifiers.

2. VARCHAR

VARCHAR(n) is a variable-length character type used to store strings up to n characters.
Unlike CHAR, it only uses as much storage as needed for the actual string plus a small overhead, not the full declared length.
If a value exceeds the specified maximum length, the database will usually throw an error or truncate based on configuration. Use cases: general text fields like names, emails, addresses, titles, etc.

3. INT

INT is used to store whole numbers (integers).
Exact range depends on the database system and whether it is signed or unsigned, but it typically supports a large range of positive and negative values suitable for IDs, counts, and similar fields. Use cases: IDs, counts, quantities, ages, and other whole-number values

4. TINYINT

TINYINT is a smaller integer type with a limited range.
In MySQL, for example, TINYINT has a range of -128 to 127 when signed, and 0 to 255 when unsigned.
It is useful when you know values will always remain within a small range, which can save space. Use cases: small flags, status codes, or compact numeric values.

5. FLOAT

FLOAT is a floating-point data type used to store approximate decimal numbers.
It is called single-precision because it stores numbers using fewer bits, which means less precision compared to DOUBLE.
In most databases, FLOAT can accurately represent about 6–7 significant digits.
Because values are stored in binary form, some decimal numbers cannot be represented exactly, which may result in small rounding errors.
Due to this approximation, FLOAT should not be used for values where exact precision is required (such as money). Use cases: Measurements, percentages, averages, sensor data, or scientific values where minor rounding differences are acceptable.

6. DOUBLE

DOUBLE (or DOUBLE PRECISION) is a floating-point data type used to store approximate decimal numbers with higher precision than FLOAT.
It uses more bits to store each value, allowing for about 15–16 significant digits of precision in most systems.
DOUBLE supports a wider range and greater accuracy than FLOAT, making it suitable for more complex calculations.
Like FLOAT, it still stores values approximately, so exact precision is not guaranteed. Use Cases: Scientific computations, complex calculations, analytics, and scenarios where higher precision is needed but exact decimal accuracy is not mandatory.

7. Boolean

Conceptually, a Boolean represents two logical values: true and false.
Many SQL dialects have a BOOLEAN or BOOL type; however, the actual storage may be implemented internally as a small integer (for example, TINYINT(1) in MySQL).
In systems that represent Boolean as integers, the common convention is 0 for false and 1 for true. Use cases: flags like is_active, is_deleted, is_admin, etc.

8. DECIMAL / NUMERIC

DECIMAL (also called NUMERIC in many SQL dialects) is used to store exact decimal numbers.
Unlike FLOAT and DOUBLE, DECIMAL stores values in base-10, which means decimal numbers are stored exactly as written, without rounding errors.
DECIMAL (p, s) defines precision (total digits) and scale (digits after decimal).

Rule of thumb:
Use FLOAT/DOUBLE for measurements and calculations, but DECIMAL for money and financial data.

Use cases: Monetary values, prices, salaries, financial calculations, totals, taxes, and any scenario where precision must be exact.

Signed and unsigned integers
Sometimes you know a column will never store negative values, such as IDs, counts, or quantities.
• Signed integers can store both negative and positive values (for TINYINT in MySQL: -128 to 127).
• Unsigned integers only store non-negative values but shift the range upward (for TINYINT in MySQL: 0 to 255).
Example:

_id TINYINT UNSIGNED

Here, _id can only store values from 0 to 255, which is enough for a small set of teacher IDs and saves space compared to a larger integer type.
You can mention that more will be covered under constraints later when you discuss things like NOT NULL, UNIQUE, PRIMARY KEY, and CHECK.

Types of Commands in SQL
We know that SQL is a language which is used to interact with database, there are various types of commands with each having its own function, now these commands can be classified into 5 types, and these are –

1. DDL (Data Defining Language)
Purpose: Define and manage database structure (tables, schemas).
Examples:

CREATE - create tables/databases
ALTER - modify existing tables
DROP - delete tables/databases
TRUNCATE - delete all data (keep structure)
RENAME - rename objects

2. DQL (Data Query Language)
This is used to retrieve data from tables. We can apply conditions too, to get specific data.
Examples:

SELECT - fetch data with conditions, joins, etc.

3. DML (Data Manipulation Language)
This is used to manipulate the data present/stored inside the table. We can CRUD operations;
Examples:

INSERT - add new data
UPDATE - modify existing data
DELETE - remove data

4. DCL (Data Control Language)
This is used to grant and revoke permissions to users. As the data grows, we may also add sensitive data in it or there may be data which isn’t needed by one user. So, we can limit what the user is allowed to access to maintain security.
Examples:

GRANT - give permissions
REVOKE - remove permissions

5. TCL (Transaction Control Language)
One of the most important concepts, although it is used massively in applications. Where it ensures that whole operation is done as per the instructions other wise revert back to the last check point
Examples:

BEGIN/COMMIT - save changes
ROLLBACK - undo changes
SAVEPOINT - partial rollback points

Basic Command (Create table)
As we know that SQL is a language and everything is done using commands. So here is the most basic command. Which will help us to understand other concepts.

List all Databases

SHOW DATABASES;

Use a specific database

USE database_name

List all tables in current DB

SHOW TABLES;

View table structure

DESC table_name

DESC students;

Create table (DDL)

CREATE TABLE table_name(
   column_1 datatype constraint,
   column_2 datatype constraint,
   column_3 datatype constraint, 
);

Ex-

CREATE TABLE student(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   phone VARCHAR(15),
   age INT NOT NULL
);

NOTE-
Here PRIMARY KEY is a key
AUTO_INCREMENT, NOT NULL are constraints
We will learn about these in future.

DELETE DATABASE/TABLE

DROP DATABASE database_name;

DROP TABLE table_name;

KEYS
PRIMARY KEY (always unique)
A column (or set of columns) in a table that uniquely identifies each row (a unique id)
Only one Primary key can be there in a table and it can’t be null.

Ex-

CREATE TABLE students(
   id INT PRIMARY KEY,
   ……
);

FOREIGN KEY
It’s always a good idea to divide data into small tables, rather than keeping everything inside a single table which is a monolith architecture. Now as we divide the tables, we need to establish some sort of relation in between them which allows us to get the data. FOREIGN key in a table refers to the PRIMARY key of another table and helps in establishing that link between tables.
FOREIGN KEY References PRIMARY KEY of another table
A table can have multiple FKs.
Foreign keys may contain null or duplicate values.

Parent table = table with PRIMARY KEY
Child table = table with FOREIGN KEY

Example

A teacher table is in relation with department table.

CREATE TABLE dept(
   id INT PRIMARY KEY,
   name VARCHAR(50)
);  

CREATE TABLE teacher(
   id INT PRIMARY KEY,
   name VARCHAR(50),
   dept_id INT,

   FOREIGN KEY (dept_id) REFERENCES dept(id);
);

Here dept is the Parent table while teacher is the Child table (using Foreign Key) and dept_id in teacher table is the FOREIGN KEY, which establishes relation between these two tables.

CASCADE in FOREIGN key
FOREIGN key is used to set up relation between tables. Now there may be a need of change in both tables if anything is changed in parent table, for that we use CASCADING

So, any change(s) in parent table is automatically reflected in children table too.

ON UPDATE CASCADE → Parent ID changes → Child FK updates automatically
ON DELETE CASCADE → Parent row deleted → Child rows deleted automatically

CREATE TABLE teachers(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   Name VARCHAR(50),
   dept_id INT,

   FOREIGN KEY (dept_id) REFERENCES dept(id)

   ON UPDATE CASCADE
   ON DELETE CASCADE
);

CONSTRAINTS
Now let’s talk about CONSTRAINTS, while creating a table we can specify some rules for the columns in the table. Like the values can never be null, we can specify the length, allow duplicate values or not. All these rules are applied using CONSTRAINTS.

Some Commonly used CONSTRAINTS
1. NOT NULL – This is used to specify that the column can’t be null. There should be a value in it all the time.
2. UNIQUE – In case we want to have unique values in a column, then we use this.
3. DEFAULT – This is used to set a default value, in case no value is provided, then the set default value is used.
4. CHECK - This is used to check or set the permissible value in the column

Ex.

CREATE TABLE student (
   _id INT PRIMARY KEY,
   first_name VARCHAR (50) NOT NULL,
   last_name VARCHAR (50) NOT NULL,
   phone VARCHAR (15) NOT NULL UNIQUE,
   age INT CHECK (age>=18),
   city DEFAULT 'ABCD'
);

SELECT statement
Till now we learned about databases, their types, data types, types of commands, how to create a table, concept of keys and constraints. Now we will dive into the ocean of commands, how we can actually use the commands and play with data and to do so, first and foremost thing we have is the SELECT statement, it is used to retrieve data from the table and return it.

SELECT col1, col2, col3 FROM table_name;

SELECT * FROM student;

Here the above command will return whole table as the result, * means all.

Targeting specify column in a table.

SELECT id, name, city FROM students;

Above command will return a table with only id, name and city column.

Column aliases (AS):
In case we want to change “name” to “first_name” in the resultant table, then we can use AS command to do so.

SELECT id, name AS first_name, city FROM students;

In the resultant table, now the columns will be id, first_name, city. But this won’t affect the actual table in anyways.

CONDITIONS – WHERE clause
What if we need specific data from the table, which satisfies certain conditions. To do so, we use WHERE clause with the SELECT statement. WHERE clause allows us to set conditions.

SELECT col1, col2, col3, col4…   FROM table_name WHERE condition;

WHERE clause doesn’t work alone, conditions are set with the help of OPERATORS.

OPERATORS
OPERATORS are the backbone of SQL, as they allow us to actually set the conditions as per our requirements.
There are various OPERATORS to choose from, and these are the most commonly and important to know OPERATORS.

1. Arithmetic OPERATORS: *, -, +, /, %

SELECT name, age/2 AS age_divided_by_two FROM students;

SELECT *, age-4 as MINOR FROM students WHERE age-4<18;

2. Comparison OPERATORS: =, !=, >, >=, <, <=
These are used to do comparison
*Note: != means not equal to

SELECT * FROM students WHERE city='New York' AND age>21;

3. BITWISE OPERATORS: & (Bitwise AND), | (Bitwise OR)

4. Logical OPERATORS: AND, OR, NOT, IN, BETWEEN, ALL, LIKE.
These are logical in nature

AND operator: This checks if both (right and left) conditions are true, then only it satisfies the conditions.

SELECT * from students
WHERE name='John' AND age>26;

OR operator: Similar to AND operator but this checks if either of the conditions is true.

SELECT * FROM students
WHERE name='Wick' OR age>50;

BETWEEN operator: Used to set a range for the result.

SELECT * FROM students
WHERE marks BETWEEN 80 AND 92;

IN operator: It matches any value specified inside it.

SELECT * FROM students
WHERE city IN ('New York', 'Moscow', 'New Delhi')

This will only return the rows, where the city is New York, Moscow, or New Delhi only.

NOT: To negate the given condition

SELECT * FROM students
WHERE city NOT IN ('New York', 'Moscow', 'New Delhi')

This will return all the rows except the rows where the city is New York, Moscow, or New Delhi

LIMIT: Used to set the limit of returned rows of the table.

SELECT * FROM students LIMIT 5;

Now, the result will contain only 5 rows at a time, even if it contains 100 rows. It will only be returning 5 rows at a time.

OFFSET: This one is used to skip certain values and start after them.

LIMIT and OFFSET are used together and these two OPERATORS make pagination in web page possible.

ORDER BY: This gives us the facility to sort the result as per our choice. Either Descending or Ascending.

SELECT * FROM table_name 
ORDER BY col_name ASC

ASC= Ascending
DESC= Descending

SELECT * FROM students
WHERE city NOT IN ('Rome', 'Tokyo') 
ORDER BY name DESC
LIMIT 2 OFFSET 2;

AGGREGATE FUNCTIONS
These are prebuilt functions/methods which helps us by easing our work, these returns single value as a result.

COUNT() - Counts the number of entries
MAX() - Maximum value in column
MIN() - Minimum value in column
SUM() - Total sum of values
AVG() - Average of values.

Find the max age-

SELECT MAX(age) as MaxAge FROM students;

     OR

SELECT age FROM students ORDER BY age DESC LIMIT 1;

GROUP BY clause
This is used to group rows that have same values into summary rows.
Generally used with some aggregate function. Both of these goes by hand in hand.

SELECT SPID, SUM(amount) 
FROM sales 
GROUP BY SPID 
ORDER BY SPID ASC;

HAVING clause
Similar to WHERE clause but used when we want to apply condition after grouping.
It was introduced to address the limitations of WHERE clause, which cannot be used with aggregate functions.

SELECT city , COUNT(name)
FROM student
GROUP BY city 
HAVING max(marks)>90;

GENERAL SEQUENCE/ORDER of writing query

SELECT columns(s)
FROM   table_Name
WHERE condition
GROUP BY column(s)
HAVING condition
ORDER BY column(s) ASC/DESC

SELECT city
FROM   students
WHERE grade='A'
GROUP BY city
HAVING MAX(marks) > 90
ORDER BY city ASC/DESC

TABLE Related Queries
1. Create Table

CREATE TABLE table_name(
   column_1  datatype constraint,
   column_2  datatype constraint,
   column_3  datatype constraint, 
);

Ex-

CREATE TABLE student(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   phone VARCHAR(15),
   age INT NOT NULL 
);

2. Insert data

INSERT INTO tablename(col1,col2,col3,….) 
VALUES(val1,val2,val3,….);

INSERT INTO students(id,first_name,last_name,phone,age)
VALUES(1, 'John', 'Wick', '7878787878', 35);

In case you know the order then you may omit the column names

INSERT INTO students
VALUES(1, 'John', 'Wick', '7878787878', 35);

Notice that we have used AUTO_INCREMENT for id. Which means that the value will increase by its own. To do that, we have to use the columns name and omit the AUTO_INCREMENT column.

INSERT INTO students (first_name, last_name, phone, age) 
VALUES ('John', 'Wick', '7878787878', 35)

Here, id will be populated on its own.

Insert more than one value/row in table

INSERT INTO students(first_name, last_name, phone, age)
VALUES ('John','Wick', '7878787878', 35),
   ('Super', 'Man', '8456985',20),
   ('Bat', 'Man', '2234234',45),
   ('Iron', 'Man', '23584839', 55),
   ('Captain', 'America', '284738473/, 50);

3. Update table

UPDATE table_name 
SET col1=val1 
WHERE condition

UPDATE student 
SET age=44 
WHERE first_name='SUPER';

4. Delete data

DELETE FROM table_name
WHERE condition;

DELETE FROM students 
WHERE first_name= 'Delete';

5. ALTER TABLE

ADD column

ALTER TABLE table_name
ADD COLUMN col_name datatype constraint;

ALTER TABLE students
ADD COLUMN grades VARCHAR(2) NOT NULL;

DROP/DELETE Column

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE students
DROP COLUMN grades;

RENAME table

ALTER TABLE table_name
RENAME TO newtable_name;

ALTER TABLE students
RENAME TO newStudents;

CHANGE column

ALTER TABLE table_name
CHANGE COLUMN old_name new_name new_datatype new_constraint;

ALTER TABLE students 
CHANGE COLUMN last_name lastName VARCHAR(20) NOT NULL;

MODIFY column (modify datatype/constraint)

ALTER TABLE table_name
MODIFY col_name new_datatype new_constraint;

ALTER TABLE students
MODIFY phone VARCHAR(15) UNIQUE;

DELETE TABLE data (only table data, not whole table)

TRUNCATE table_name;

TRUNCATE students;

JOINS
One of the most important topics, these are used to combine rows from two or more tables, based on a related column between them.

Types

Inner Join
Outer Join

Left Join
Right Join
Full Join

1. INNER JOIN
Returns records that have matching values in both tables (only mutual data is returned)

SELECT column(s) FROM table_A 
INNER JOIN table_b 
ON tableA.col_name = tableB.col_name;

SELECT * FROM students
INNER JOIN course
ON student.student_id = course.student_id;

*This will return only the data which is present in both tables

OUTER JOINS
1. LEFT Join

Returns all records from left table and matched records from the right table.

SELECT column(s)
FROM tableA
LEFT JOIN tableB
ON tableA.col_name = tableb.col_name;

2. RIGHT Join
Same as LEFT join, only side changes. This return all records from right table and matched records from left table.

SELECT column(s) 
FROM tableA
RIGHT JOIN tableB
ON tableA.col_name = tableb.col_name;

3. FULL Join
Returns all records even if there is no relation.
By default, MySQL doesn't support FULL JOIN so we do it through another method and with using UNION.

UNION
Helps in combining data and returns unique values only.

FULL JOIN = LEFT JOIN _UNION _RIGHT JOIN

SELECT * FROM tableA as A
LEFT JOIN tableB as B
ON A.column = B.column

UNION

SELECT * FROM tableA as A
RIGHT JOIN tableB as B
ON a.column = b.column;

??Write SQL command to display the left exclusive join: -

SELECT * FROM tableA as A
LEFT JOIN tableB as B
ON A.col_name = b.col_name
WHERE b.id IS NULL;

*Similar for RIGHT exclusive Join.

SELF JOIN
A regular join but the table is joined with itself

SELECT column(s)
FROM table as A
JOIN table as B
ON A.col_name = B.col_name;

UNION
Used to combine the result-set of two or more SELECT statements.
Gives unique records.
*Columns must have similar data types.

SELECT column(s) FROM tableA
UNION
SELECT column(s) FROM tableB;

*We also have UNION all, this allows duplicates on top of UNION's data

SUBQUERY
Subquery or inner query or a nested query is a query within another SQL query. This allows us to do complex operations which isn't or would be hard to do otherwise.

SELECT column(s)
FROM table_name
WHERE col_name operator/condition(subquery)

SELECT name, marks
FROM student
WHERE marks > (SELECT AVG (marks) FROM student);

MySQL Views
It's a virtual table created from an actual/real table.
Imagine a student table with lots of columns. Now a teacher only needs students roll number, name, marks. Other data isn't needed to the teacher.

So, a view will be created from the student table which will contain only these columns.

Although it's a virtual table but it will act just like a real table, operations, select command etc.

CREATE VIEW

CREATE VIEW view_name AS 
SELECT column(s) 
FROM table_name;

CREATE VIEW view1 AS 
SELECT rollno, name, marks 
FROM student


SELECT * FROM view1;

DROP VIEW

DROP VIEW view_name;

DROP VIEW view1;

Wanna know the working of Video calling apps (zoom, google meet, microsoft teams) how they work, here is a quick article on it. What goes behind a video call, how things work, its architecture. Feel free to give it a try😁

Blackwatch — Thu, 17 Jul 2025 05:51:38 +0000

Blackwatch

Jul 16 '25

WebRTC, the Brain Behind Your Virtual Video Calls

#webdev #webrtc #programming #beginners

Comments

6 min read

WebRTC, the Brain Behind Your Virtual Video Calls

Blackwatch — Wed, 16 Jul 2025 14:38:39 +0000

Ever wondered what goes behind apps like Google Meet or Zoom? How do they make it so easy for us to communicate through video, even when we're sitting on opposite sides of the planet?

Yeah, I came across this while building an app for myself. That’s when I learned more about WebRTC—how it works, what goes on behind the scenes, what its architecture looks like, its drawbacks, and how to overcome them. In this article, I’ll give you a brief intro to WebRTC and its working principles. So stay tuned to dive deep into the world of video calling.

WebRTC—What Is This?

It’s a protocol used to connect users directly so they can communicate, share files, stream video, and more.

But aren’t there other protocols for that? What’s so special about this one?

Right, there are many protocols. But WebRTC shines because of its ease of use, and the fact that you don’t need a dedicated server to monitor or relay the entire communication. It’s based on the UDP protocol.

Yes, you heard it right—WebRTC doesn’t need a third-party or cloud-based server for actual media transfer. It establishes a direct peer-to-peer connection between users, which also makes it faster.

So Why Don’t We Use This Everywhere?

You might have the same question in your head. The reason is packet loss.

WebRTC is based on UDP, which is fast, but not reliable—it doesn’t guarantee that all data packets will reach the destination. That’s fine for things like Zoom meetings or online games, where a small data loss (a freeze or glitch) doesn’t affect much.

But for messaging apps, we can’t afford to lose even a single packet—missing words or characters can change the entire meaning of a message. So for these cases, we use TCP, which ensures reliable and ordered delivery of data.

In short:

Use UDP/WebRTC for real-time media (video/audio).
Use TCP when all data must be received correctly (text, files).

How It Works – Architecture

Before diving in, let’s imagine a situation:
You want to call your friend, family member, boss (to ask for leave 😁), or your wife (to plan a day out). To do that, you need their phone number. Only then can you call them.

Similarly, in WebRTC, you first need to know the IP address of the person you want to connect with. But before that, let’s understand public and private IP addresses.

Public and Private IP Addresses

Just like each SIM card has a unique number, every device needs a unique address on the internet to send and receive data.

With the rapid growth in internet users, assigning a unique public IP to every device isn't possible (due to IPv4 exhaustion). So instead, most devices are assigned private IP addresses by routers.

Public IP Address

This is what allows your device to access the internet.
It’s unique globally and exposed to the outer world.
Typically, only the router is assigned a public IP, and it communicates with the internet on behalf of all devices connected to it.

No two devices on the internet can have the same public IP.

Private IP Address

Assigned to devices inside a local network (by the router).
Can’t directly access the internet.
Managed through a process called NAT (Network Address Translation).

So how do you surf the internet with a private IP?

Here’s how:

When your device makes a request (e.g., opens a website), it goes to the router.
The router forwards this request using its public IP.
When the response comes back, it knows which device made the request and sends the data accordingly.

TURN/ICE Servers

Now that you know about private and public IPs, let’s talk about TURN and ICE servers.

After a device gets its private IP, it still doesn’t know its public IP, which is needed to establish a connection. That’s where these come in:

STUN/TURN servers are simple services that see the public IP of incoming requests and send it back to the client.
This way, the client becomes aware of its public IP address.

ICE (Interactive Connectivity Establishment) is a framework that uses STUN and TURN to find the best route between two peers.

Signaling Server

At this point, each user has the necessary information to establish a WebRTC connection—like codecs, IPs, ports, etc. This data is packaged in something called a Session Description Protocol (SDP).

But here's the catch:

You don’t have the other user’s SDP.
They don’t have yours.

So, we need a signaling server to exchange this information.
Don’t worry—it’s a simple server that:

Takes the SDP of User A and sends it to User B.
Takes the SDP of User B and sends it to User A.

Now both users have each other’s details and can establish a direct WebRTC connection. After that, the signaling server isn’t needed anymore.

Drawbacks

Since WebRTC is peer-to-peer, only two users can connect at a time by default. You can’t host large video conferences using just this architecture.

This is perfect for apps like Omegle, where only two users need to connect. But for more users, we need other methods.

Mesh Topology (Not Ideal)

In mesh, each user connects directly to every other user.

Example: In a 4-user call, each user maintains 3 separate connections.

This quickly becomes:

Bandwidth-heavy
Hard to scale
Difficult to keep all streams in sync

That’s why mesh is rarely used in production apps.

Solution – SFU (Selective Forwarding Unit) and Multipoint Control Units (MCUs)

To solve the issue of scalability in WebRTC for group calls, we use SFU (Selective Forwarding Unit) or MCU (Multipoint Control Unit) as per the requirement. In both cases, we introduce a server into the architecture.

In these setups, a server acts like a virtual client, and all the users are connected to this server using a P2P configuration. This server handles the media streams from all users. In case of SFU, it simply forwards the streams to other users. In MCU, it combines all the streams into one and sends it back. This makes the data easier to manage, keeps it in sync, and helps in scaling the system to support many users at once. This is how big platforms like Google Meet, Zoom, Discord, Microsoft Teams, etc., manage large group calls.

SFU (Selective Forwarding Unit)

In SFU, each user sends their video/audio stream to the server, and the server’s job is just to **forward those streams **to the other users. It doesn’t mix or edit the media—it just acts like a smart postman.

This way, each user only needs to upload their stream once, and the server takes care of distributing it to everyone else. Since the server isn’t doing any processing, this setup is lightweight and scalable.

But there’s a catch. Each user still receives multiple streams—one from each participant—so the client device has to handle that load. Still, it’s much better than mesh topology, where everyone connects with everyone directly.

That’s why SFU is the go-to choice for group video calls in real-world apps. It's efficient, requires less server power, and performs well even with 10, 20, or more users.

MCU (Multipoint Control Unit)

In this setup, things work a bit differently. All users send their streams to the server, just like in SFU. But here, the server does a lot more—it mixes all the streams into one combined video (usually a grid of faces) and sends that single stream back to every user.

This keeps everything perfectly in sync and makes life easier for the client. Each device only needs to handle one incoming stream. But this also means the server is doing all the heavy lifting—mixing, encoding, and streaming—which requires a lot more processing power and resources.

MCU setups are great for situations like webinars or classrooms, where it’s more important for everything to be in sync, and where users don’t interact much.

Conclusion

With that, this article comes to an end. I hope you learned something new—because this was new to me too, and I thought, why not share my understanding with others?

Thanks for reading, and I hope you enjoyed the article.

Till we meet again—seeya, have a great day, keep learning, peace out.

Signing off.