Database_fundamentals
Chapter 3: Database Fundamentals & SQL
Section titled “Chapter 3: Database Fundamentals & SQL”Essential Database Concepts for TypeORM
Section titled “Essential Database Concepts for TypeORM”3.1 Relational Database Overview
Section titled “3.1 Relational Database Overview”Understanding relational databases is crucial for working with TypeORM effectively.
Relational Database Architecture ================================================================================
Database Server +-----------------------------------+ | | | +-----------------------+ | | | Database | | | | (my_app_db) | | | | | | | | +-----------------+ | | | | | Schema | | | | | | (public) | | | | | | | | | | | | +-----------+ | | | | | | | Table 1 | | | | | | | | (users) | | | | | | | +-----------+ | | | | | | +-----------+ | | | | | | | Table 2 | | | | | | | | (posts) | | | | | | | +-----------+ | | | | | | +-----------+ | | | | | | | Table 3 | | | | | | | |(comments)| | | | | | | +-----------+ | | | | | | | | | | | +-----------------+ | | | | | | | +-----------------------+ | | | +-----------------------------------+
================================================================================3.2 Table Structure
Section titled “3.2 Table Structure”A table is a collection of related data stored in rows and columns.
Table Structure +------------------------------------------------------------------+ | | | users Table | | +--------+------------+-------------------+---------------+ | | | id | name | email | created_at | | | +--------+------------+-------------------+---------------+ | | | 1 | John Doe | john@example.com | 2024-01-15 | | | +--------+------------+-------------------+---------------+ | | | 2 | Jane Smith | jane@example.com | 2024-01-16 | | | +--------+------------+-------------------+---------------+ | | | 3 | Bob Wilson | bob@example.com | 2024-01-17 | | | +--------+------------+-------------------+---------------+ | | | | Terms: | | - Column: Vertical field (id, name, email) | | - Row: Horizontal record (one user) | | - Primary Key: Unique identifier (id) | | - Foreign Key: Reference to another table | | | +------------------------------------------------------------------+Column Data Types
Section titled “Column Data Types” Common SQL Data Types +------------------------------------------------------------------+ | | | Category | PostgreSQL | MySQL | TypeORM | | ----------------|-----------------|------------------|---------| | Integer | INTEGER, SERIAL | INT, AUTO_INC | number | | String (short) | VARCHAR(n) | VARCHAR(n) | string | | String (long) | TEXT | TEXT | string | | Boolean | BOOLEAN | TINYINT(1) | boolean | | Date | DATE | DATE | Date | | DateTime | TIMESTAMP | DATETIME | Date | | Decimal | DECIMAL(p,s) | DECIMAL(p,s) | number | | JSON | JSON, JSONB | JSON | object | | UUID | UUID | CHAR(36) | string | | Array | TEXT[], INT[] | - | array | | | +------------------------------------------------------------------+3.3 Primary Keys & Indexes
Section titled “3.3 Primary Keys & Indexes”Primary Key
Section titled “Primary Key” Primary Key Concept +------------------------------------------------------------------+ | | | A Primary Key uniquely identifies each row in a table | | | | Options: | | 1. Auto-increment integer (1, 2, 3, ...) | | 2. UUID (universally unique identifier) | | 3. Composite key (multiple columns) | | | | +------------------------+ | | | Auto-increment | | | | id: 1, 2, 3, 4, ... | | | | Simple, sequential | | | | Good for internal use | | | +------------------------+ | | | | +------------------------+ | | | UUID | | | | id: a1b2c3d4-... | | | | Globally unique | | | | Good for distributed | | | +------------------------+ | | | +------------------------------------------------------------------+Indexes
Section titled “Indexes” Index Performance +------------------------------------------------------------------+ | | | WITHOUT INDEX: | | SELECT * FROM users WHERE email = 'john@example.com'; | | | | Database scans ALL rows (Full Table Scan) | | | | +--------+------------+-------------------+ | | | id | name | email | <-- Check each | | +--------+------------+-------------------+ | | | 1 | John Doe | john@example.com | <-- Match! | | +--------+------------+-------------------+ | | | 2 | Jane Smith | jane@example.com | | | +--------+------------+-------------------+ | | | ... | ... | ... | | | +--------+------------+-------------------+ | | | | Time: O(n) - Linear time | | | +------------------------------------------------------------------+ | | | WITH INDEX: | | CREATE INDEX idx_email ON users(email); | | | | Database uses B-Tree for fast lookup | | | | +-------------------+ | | | jane@example.com | | | +--------+----------+ | | | | | +------------+------------+ | | | | | | +-------v-------+ +-------v-------+ | | | bob@example.com| | john@example.com| | | +----------------+ +--------+--------+ | | | | | +-------v-------+ | | | Row Location | | | +---------------+ | | | | Time: O(log n) - Logarithmic time | | | +------------------------------------------------------------------+3.4 Relationships
Section titled “3.4 Relationships”One-to-One Relationship
Section titled “One-to-One Relationship” One-to-One Relationship +------------------------------------------------------------------+ | | | User has one Profile | | | | +----------------+ +----------------+ | | | users | | profiles | | | +----------------+ +----------------+ | | | id (PK) | 1:1 | id (PK) | | | | email |<-------->| user_id (FK) | | | | password | | bio | | | +----------------+ | avatar | | | +----------------+ | | | | Each user has exactly one profile | | Each profile belongs to exactly one user | | | +------------------------------------------------------------------+One-to-Many Relationship
Section titled “One-to-Many Relationship” One-to-Many Relationship +------------------------------------------------------------------+ | | | User has many Posts | | | | +----------------+ | | | users | | | +----------------+ | | | id (PK) | | | | email | | | +----------------+ | | | | | | 1 | | | | | v | | +----------------+ | | | posts | | | +----------------+ | | | id (PK) | | | | title | | | | content | | | | author_id (FK) | <-- Many posts can have same author_id | | +----------------+ | | | | One user can have many posts | | Each post belongs to one user | | | +------------------------------------------------------------------+Many-to-Many Relationship
Section titled “Many-to-Many Relationship” Many-to-Many Relationship +------------------------------------------------------------------+ | | | Students have many Courses, Courses have many Students | | | | +----------------+ +----------------+ | | | students | | courses | | | +----------------+ +----------------+ | | | id (PK) | | id (PK) | | | | name | | title | | | +----------------+ +----------------+ | | | | | | | M | M | | +-------------+----------------+ | | | | | v | | +----------------+ | | | enrollments | <-- Junction Table | | +----------------+ | | | student_id(FK) | <-- Composite Primary Key | | | course_id (FK) | <-- Composite Primary Key | | | grade | | | +----------------+ | | | +------------------------------------------------------------------+3.5 SQL Query Basics
Section titled “3.5 SQL Query Basics”SELECT Queries
Section titled “SELECT Queries” SELECT Query Structure +------------------------------------------------------------------+ | | | SELECT [columns] | | FROM [table] | | [JOIN joins] | | [WHERE conditions] | | [GROUP BY columns] | | [HAVING conditions] | | [ORDER BY columns] | | [LIMIT number] | | | | Execution Order: | | 1. FROM (get table) | | 2. JOIN (combine tables) | | 3. WHERE (filter rows) | | 4. GROUP BY (group rows) | | 5. HAVING (filter groups) | | 6. SELECT (choose columns) | | 7. ORDER BY (sort results) | | 8. LIMIT (limit count) | | | +------------------------------------------------------------------+JOIN Types
Section titled “JOIN Types” JOIN Types Visualized +------------------------------------------------------------------+ | | | Table A Table B | | +--------+ +--------+ | | | id | | id | | | +--------+ +--------+ | | | 1 | | 1 | | | +--------+ +--------+ | | | 2 | | 2 | | | +--------+ +--------+ | | | 3 | | 4 | | | +--------+ +--------+ | | | | INNER JOIN: Only matching rows | | +--------+ | | | A.id 1 | <-- Matches B.id 1 | | +--------+ | | | A.id 2 | <-- Matches B.id 2 | | +--------+ | | | | LEFT JOIN: All from A, matching from B | | +--------+--------+ | | | A.id 1 | B.id 1 | | | +--------+--------+ | | | A.id 2 | B.id 2 | | | +--------+--------+ | | | A.id 3 | NULL | <-- No match in B | | +--------+--------+ | | | | RIGHT JOIN: All from B, matching from A | | +--------+--------+ | | | A.id 1 | B.id 1 | | | +--------+--------+ | | | A.id 2 | B.id 2 | | | +--------+--------+ | | | NULL | B.id 4 | <-- No match in A | | +--------+--------+ | | | | FULL OUTER JOIN: All rows from both | | +--------+--------+ | | | A.id 1 | B.id 1 | | | +--------+--------+ | | | A.id 2 | B.id 2 | | | +--------+--------+ | | | A.id 3 | NULL | | | +--------+--------+ | | | NULL | B.id 4 | | | +--------+--------+ | | | +------------------------------------------------------------------+3.6 Transactions
Section titled “3.6 Transactions” Transaction ACID Properties +------------------------------------------------------------------+ | | | A - Atomicity | | All operations succeed or all fail | | | | +-------------------+ +-------------------+ | | | BEGIN TRANSACTION | | BEGIN TRANSACTION | | | | - Deduct $100 | | - Deduct $100 | | | | - Add $100 | | - Add $100 | | | | COMMIT | | ERROR! | | | +-------------------+ | ROLLBACK | | | | Success! | +-------------------+ | | +-------------------+ | Reverted! | | | +-------------------+ | | | | C - Consistency | | Database remains in valid state | | | | I - Isolation | | Concurrent transactions don't interfere | | | | D - Durability | | Committed changes are permanent | | | +------------------------------------------------------------------+Transaction Example
Section titled “Transaction Example”-- Bank transfer exampleBEGIN TRANSACTION;
-- Step 1: Deduct from senderUPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Step 2: Add to receiverUPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If both succeedCOMMIT;
-- If any step failsROLLBACK;3.7 Normalization
Section titled “3.7 Normalization” Database Normalization Forms +------------------------------------------------------------------+ | | | 1NF (First Normal Form) | | - Each column contains atomic values | | - No repeating groups | | | | Bad: | | +----+--------+------------------+ | | | id | name | phones | | | +----+--------+------------------+ | | | 1 | John | 123,456,789 | <-- Multiple values | | +----+--------+------------------+ | | | | Good: | | +----+--------+--------+ | | | id | name | phone | | | +----+--------+--------+ | | | 1 | John | 123 | | | +----+--------+--------+ | | | 1 | John | 456 | | | +----+--------+--------+ | | | 1 | John | 789 | | | +----+--------+--------+ | | | +------------------------------------------------------------------+ | | | 2NF (Second Normal Form) | | - In 1NF | | - No partial dependencies | | | | Bad: | | +--------+------------+-------------+ | | | stud_id| course_id | stud_name | <-- stud_name depends | | +--------+------------+-------------+ only on stud_id | | +--------+------------+-------------+ | | | | Good: Split into two tables | | +--------+-------------+ +------------+-----------+ | | | stud_id| stud_name | | course_id | course_name| | | +--------+-------------+ +------------+-----------+ | | | +------------------------------------------------------------------+ | | | 3NF (Third Normal Form) | | - In 2NF | | - No transitive dependencies | | | | Bad: | | +----+--------+-----------+-------------+ | | | id | name | dept_id | dept_name | | | +----+--------+-----------+-------------+ | | | dept_name depends on dept_id, not id directly | | | | Good: Split into two tables | | +----+--------+-----------+ +-----------+-------------+ | | | id | name | dept_id | | dept_id | dept_name | | | +----+--------+-----------+ +-----------+-------------+ | | | +------------------------------------------------------------------+3.8 SQL to TypeORM Mapping
Section titled “3.8 SQL to TypeORM Mapping” SQL to TypeORM Translation +------------------------------------------------------------------+ | | | SQL TypeORM | | ------------------------------- --------------------------- | | | | CREATE TABLE users ( @Entity() | | id SERIAL PRIMARY KEY, class User { | | name VARCHAR(100), @PrimaryGeneratedColumn() | | email VARCHAR(255) id: number; | | ); | | @Column({ length: 100 }) | | name: string; | | | | @Column() | | email: string; | | } | | | +------------------------------------------------------------------+ | | | SELECT * FROM users; const users = await | | userRepository.find(); | | | +------------------------------------------------------------------+ | | | SELECT * FROM users const user = await | | WHERE id = 1; userRepository.findOne({ | | where: { id: 1 } | | }); | | | +------------------------------------------------------------------+ | | | INSERT INTO users const user = userRepository | | (name, email) .create({ | | VALUES ('John', 'j@e.com'); name: 'John', | | email: 'j@e.com' | | }); | | await userRepository.save(user); | | | +------------------------------------------------------------------+ | | | UPDATE users const user = await | | SET name = 'Jane' userRepository.preload( | | WHERE id = 1; { id: 1, name: 'Jane' } | | ); | | await userRepository.save(user); | | | +------------------------------------------------------------------+ | | | DELETE FROM users await userRepository.delete(1); | | WHERE id = 1; | | | +------------------------------------------------------------------+3.9 Query Execution Flow
Section titled “3.9 Query Execution Flow” Query Execution Flow +------------------------------------------------------------------+ | | | 1. Parser | | - Parse SQL syntax | | - Check for errors | | | | 2. Planner | | - Create execution plan | | - Choose indexes to use | | - Decide join order | | | | 3. Executor | | - Execute the plan | | - Read from disk/memory | | - Apply filters | | | | 4. Result | | - Return rows | | - Send to client | | | | +--------+ +--------+ +--------+ +--------+ | | | Parser | -> | Planner| -> |Executor| -> | Result | | | +--------+ +--------+ +--------+ +--------+ | | | +------------------------------------------------------------------+3.10 Common SQL Patterns
Section titled “3.10 Common SQL Patterns”Pagination
Section titled “Pagination”-- Offset-based paginationSELECT * FROM usersORDER BY idLIMIT 10 OFFSET 20; -- Page 3 (skip 20, take 10)
-- Cursor-based pagination (more efficient)SELECT * FROM usersWHERE id > 20ORDER BY idLIMIT 10;Aggregation
Section titled “Aggregation”-- Count, Sum, AverageSELECT COUNT(*) as total_users, AVG(age) as average_age, SUM(balance) as total_balanceFROM users;
-- Group bySELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salaryFROM employeesGROUP BY departmentHAVING COUNT(*) > 5;Subqueries
Section titled “Subqueries”-- Subquery in WHERESELECT * FROM usersWHERE id IN ( SELECT user_id FROM orders WHERE total > 1000);
-- Subquery in SELECTSELECT name, (SELECT COUNT(*) FROM posts WHERE author_id = users.id) as post_countFROM users;3.11 Database Constraints
Section titled “3.11 Database Constraints” Database Constraints +------------------------------------------------------------------+ | | | Constraint | Description | | ------------------|----------------------------------------- | | NOT NULL | Column cannot be empty | | UNIQUE | All values must be different | | PRIMARY KEY | Unique identifier for each row | | FOREIGN KEY | Ensures referential integrity | | CHECK | Ensures value meets condition | | DEFAULT | Sets default value if not specified | | | +------------------------------------------------------------------+TypeORM Constraint Examples
Section titled “TypeORM Constraint Examples”@Entity('users')export class User { @PrimaryGeneratedColumn() id: number;
@Column({ unique: true }) // UNIQUE constraint email: string;
@Column({ nullable: false }) // NOT NULL constraint name: string;
@Column({ default: 'user' }) // DEFAULT constraint role: string;
@Column({ type: 'int', check: 'age >= 0' }) // CHECK constraint age: number;}3.12 Summary
Section titled “3.12 Summary” Key Database Concepts +------------------------------------------------------------------+ | | | Concept | Description | | -----------------|------------------------------------------- | | Table | Collection of related data | | Row | Single record in a table | | Column | Single field in a table | | Primary Key | Unique identifier for rows | | Foreign Key | Reference to another table's primary key | | Index | Data structure for fast lookups | | Relationship | Connection between tables | | Transaction | Atomic unit of work | | Normalization | Organizing data to reduce redundancy | | | +------------------------------------------------------------------+Next Chapter
Section titled “Next Chapter”Chapter 4: TypeORM Installation & Configuration
Last Updated: February 2026