Databases

A Database is an organized collection of structured information or data, typically stored electronically in a computer system. Databases are managed by Database Management Systems (DBMS), which provide interfaces for users and applications to store, retrieve, and manipulate data efficiently.

Why Use Databases?

• Efficient storage and retrieval of large amounts of data
• Data persistence beyond application runtime
• Data integrity and consistency
• Multi-user access with transaction management
• Indexing and query optimization for performance

Types of Databases

1. Relational Databases (SQL)

• Data is organized into tables (rows and columns).
• Relationships between tables use foreign keys.
• Data integrity enforced by schema.
• Examples: MySQL, PostgreSQL, SQLite, Microsoft SQL Server.

Example: Table Definition (SQL)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Basic SQL Operations

-- Insert data
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- Query data
SELECT * FROM users WHERE username = 'alice';

-- Update data
UPDATE users SET email = 'alice123@example.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

2. NoSQL Databases

• Schema-less, flexible data models (documents, key-value pairs, wide-column stores, or graphs).
• High scalability and performance for unstructured or semi-structured data.
• Examples: MongoDB (Document Store), Redis (Key-Value Store), Cassandra (Wide-Column Store).

Example: MongoDB Document

{
    "_id": "507f1f77bcf86cd799439011",
    "username": "bob",
    "email": "bob@example.com",
    "createdAt": "2025-09-17T15:30:00Z"
}

Basic MongoDB Operations (JavaScript)

// Insert a document
db.users.insertOne({
    username: 'bob',
    email: 'bob@example.com',
    createdAt: new Date()
});

// Find a document
db.users.findOne({ username: 'bob' });

// Update a document
db.users.updateOne(
    { username: 'bob' },
    { $set: { email: 'bob123@example.com' } }
);

// Delete a document
db.users.deleteOne({ username: 'bob' });

SQL vs NoSQL: Key Differences

ACID Properties (SQL Databases)

1. Atomicity – All operations in a transaction succeed or none do.
2. Consistency – Data always remains in a valid state.
3. Isolation – Concurrent transactions do not interfere.
4. Durability – Committed data is saved permanently.

CAP Theorem (NoSQL)

In distributed systems, you can only guarantee two out of three at the same time:

• Consistency (C) – Every read receives the latest write.
• Availability (A) – Every request receives a response.
• Partition Tolerance (P) – System works despite network partitions.

Most NoSQL systems favor Availability and Partition Tolerance over strict consistency (eventual consistency model).

Connecting to Databases (Example: Node.js)

PostgreSQL Example

import { Client } from 'pg';

const client = new Client({
    user: 'youruser',
    host: 'localhost',
    database: 'testdb',
    password: 'password',
    port: 5432,
});

async function connectDB() {
    await client.connect();
    const res = await client.query('SELECT NOW()');
    console.log(res.rows[0]);
    await client.end();
}

connectDB();

MongoDB Example

import { MongoClient } from 'mongodb';

async function connectDB() {
    const uri = 'mongodb://localhost:27017';
    const client = new MongoClient(uri);

    try {
        await client.connect();
        const database = client.db('testdb');
        const collection = database.collection('users');
        const user = await collection.findOne({ username: 'alice' });
        console.log(user);
    } finally {
        await client.close();
    }
}

connectDB();

Best Practices

• Use appropriate indexing to speed up queries.
• Avoid storing large binary files in the database—use dedicated storage.
• Sanitize inputs to prevent injection attacks.
• Use transactions for multi-step data modifications.
• Monitor database performance and optimize queries.
• Backup your data regularly.