Database

What is a Database?

A database is organized storage for data. It is where applications save and retrieve information - user accounts, product catalogs, order histories, messages, photos, transaction records. Every application that stores data uses a database.

Think of a database as a digital filing cabinet. Instead of physical folders and papers, you have tables, rows, and columns of structured data that can be queried and updated instantly.

Why Databases Exist

The Problem with Files: Before databases, applications stored data in files. This created issues:

• Hard to search (must read entire file to find specific data)
• No concurrent access (only one user can edit at a time)
• No data validation (nothing prevents invalid data)
• No relationships (linking related data is manual and error-prone)

Database Solution: Databases provide structured storage with search capabilities, concurrent access, data validation, and relationship management. They are optimized for speed, reliability, and scale.

Types of Databases

Relational Databases (SQL)

Data stored in tables with rows and columns. Tables can relate to each other through keys.

Popular SQL Databases:

• PostgreSQL: Open-source, powerful features, used by Instagram, Spotify
• MySQL: Open-source, widely used, powers WordPress, Facebook
• SQLite: Lightweight, embedded in applications, used in mobile apps
• Oracle: Enterprise-focused, expensive, used by banks and large corporations
• SQL Server: Microsoft is database, used in Windows environments

Example Structure:

Users Table:
| id | name     | email              | created_at |
|----|----------|--------------------|------------|
| 1  | Priya    | priya@example.com  | 2024-01-15 |
| 2  | Rohan    | rohan@example.com  | 2024-01-16 |

Orders Table:
| id | user_id | product    | amount |
|----|---------|------------|--------|
| 1  | 1       | Laptop     | 50000  |
| 2  | 1       | Mouse      | 500    |
| 3  | 2       | Keyboard   | 2000   |

Querying Data (SQL):

-- Get all orders for Priya
SELECT * FROM orders 
WHERE user_id = 1;

-- Get user names and their order totals
SELECT users.name, SUM(orders.amount) as total
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.name;

Strengths:

• Strong consistency and data integrity
• Complex queries and joins
• ACID transactions (Atomicity, Consistency, Isolation, Durability)
• Mature tooling and widespread knowledge

Use Cases: Banking systems, e-commerce, any application needing complex queries and guaranteed consistency.

NoSQL Databases

Non-relational databases with flexible schemas. Different types for different needs:

Document Databases (MongoDB, Firestore): Store data as JSON-like documents.

{
  "id": 1,
  "name": "Priya",
  "email": "priya@example.com",
  "orders": [
    {"product": "Laptop", "amount": 50000},
    {"product": "Mouse", "amount": 500}
  ]
}

Strengths: Flexible schema, easy to scale, good for hierarchical data Use Cases: Content management, user profiles, catalogs

Key-Value Stores (Redis, DynamoDB): Simple key-value pairs, extremely fast.

user:1 -> {"name": "Priya", "email": "priya@example.com"}
session:abc123 -> {"user_id": 1, "expires": 1234567890}

Strengths: Extremely fast reads/writes, simple model, great for caching Use Cases: Session storage, caching, real-time analytics

Graph Databases (Neo4j): Store data as nodes and relationships, optimized for connected data.

Strengths: Excellent for relationship-heavy data Use Cases: Social networks, recommendation engines, fraud detection

Column-Family Stores (Cassandra, HBase): Store data in columns instead of rows, optimized for write-heavy workloads.

Strengths: High write throughput, good for time-series data Use Cases: IoT data, log analytics, messaging systems

Database Operations (CRUD)

Create: Add new data

INSERT INTO users (name, email) VALUES ('Amit', 'amit@example.com');

Read: Retrieve data

SELECT * FROM users WHERE id = 1;

Update: Modify existing data

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

Delete: Remove data

DELETE FROM users WHERE id = 1;

Every database supports these four fundamental operations, though syntax varies.

Database Design Principles

Normalization: Organize data to reduce redundancy. Instead of storing customer address in every order, store it once in a customers table and reference it.

Indexes: Speed up queries by creating indexes on frequently searched columns. Like a book index that helps find topics quickly without reading every page.

Primary Keys: Unique identifier for each row. Usually an auto-incrementing integer or a UUID.

Foreign Keys: Link tables together. An order is user_id references the users table is id.

Constraints: Rules that enforce data validity. Email must be unique, age must be positive, required fields cannot be null.

Real-World Database Examples

Instagram: Uses PostgreSQL for user data and relationships. Cassandra for direct messages. Redis for caching feeds.

Netflix: Uses MySQL for billing and account data. Cassandra for viewing history and recommendations at scale.

Uber: Uses PostgreSQL for core data. Redis for real-time location tracking. Cassandra for trip history.

Large applications often use multiple databases, each optimized for different needs. This is called polyglot persistence.

Database Performance

Slow Query Problem: A database with millions of records becomes slow without optimization.

Indexing: Create indexes on columns used in WHERE, JOIN, ORDER BY clauses.

CREATE INDEX idx_user_email ON users(email);

Now searching by email is instant instead of scanning millions of rows.

Query Optimization: Write efficient queries. Avoid SELECT *, fetch only needed columns, use LIMIT for large result sets.

Caching: Use Redis or Memcached to cache frequently accessed data. Check cache before querying database.

Connection Pooling: Reuse database connections instead of creating new ones for every request. Opening connections is expensive.

Database Scaling

Vertical Scaling: Upgrade to a more powerful server (more CPU, RAM, storage). Simple but limited and expensive.

Horizontal Scaling (for reads): Add read replicas. Write to primary database, read from replicas.

Sharding (for writes): Split data across multiple databases. Users A-M on database 1, users N-Z on database 2.

Managed Services: Use cloud databases (AWS RDS, Google Cloud SQL, MongoDB Atlas) that handle scaling automatically.

Database Transactions

ACID Properties:

• Atomicity: All operations succeed or all fail (no partial updates)
• Consistency: Data remains valid after transactions
• Isolation: Concurrent transactions do not interfere
• Durability: Committed data persists even if system crashes

Example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If any step fails, the entire transaction rolls back. Money is never lost.

SQL vs NoSQL: Which to Choose?

Use SQL (Relational) When:

• Data has clear structure and relationships
• Need complex queries and joins
• Require strong consistency (banking, e-commerce)
• Your team knows SQL well

Use NoSQL When:

• Data structure is flexible or changes frequently
• Need to scale writes horizontally
• Working with large volumes of unstructured data
• Performance more important than absolute consistency

Reality: Many applications use both. SQL for core transactional data, NoSQL for caching, analytics, or flexible data.

Database Security

Authentication: Users must prove identity before accessing database.

Authorization: Control what each user can access. Admin can delete, regular user can only read.

Encryption: Encrypt data at rest (stored) and in transit (transferred over network).

SQL Injection Prevention: Never concatenate user input into queries. Use parameterized queries.

Backups: Regular backups protect against data loss from failures or attacks. Test restore procedures.

Database as a Service (DBaaS)

Cloud-Managed Databases:

• AWS RDS: Managed MySQL, PostgreSQL, Oracle, SQL Server
• MongoDB Atlas: Managed MongoDB
• Google Firestore: Serverless NoSQL database
• PlanetScale: Serverless MySQL
• Supabase: Open-source Firebase alternative with PostgreSQL

Benefits: No server management, automatic backups, easy scaling, built-in monitoring.

Trade-offs: Less control, vendor lock-in, potentially higher costs at scale.

Career Paths

Database Administrator (DBA): Manages database infrastructure, ensures uptime, optimizes performance, handles backups.

Data Engineer: Builds data pipelines, designs data warehouses, integrates systems.

Backend Developer: Uses databases in applications, writes queries, designs schemas.

Database Architect: Designs database systems for large organizations, plans scaling strategies.

The Bottom Line

Databases are the foundation of modern applications. Every feature that involves storing or retrieving data depends on a well-designed, properly maintained database. Understanding databases - how to design schemas, write efficient queries, and choose the right database type - is essential for any developer building real-world applications.

Whether you are building a side project or working at a large company, databases are where your application state lives. Treat them with care, optimize them thoughtfully, and back them up religiously.