SQL vs NoSQL: Database Type Comparison (2026)

-- SQL: relational schema design
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(20) DEFAULT 'member',
    created_at TIMESTAMP DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_name VARCHAR(200) NOT NULL,
    quantity INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Query with joins
SELECT u.name, o.id AS order_id, o.total, o.status
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.email = 'alice@example.com'
ORDER BY o.created_at DESC
LIMIT 5;

// NoSQL: flexible document design (MongoDB)
const { MongoClient } = require('mongodb');

async function setupDocuments() {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();
  const db = client.db('shop');

  // Flexible schema: each document can have different fields
  await db.collection('users').insertOne({
    email: 'alice@example.com',
    name: 'Alice',
    role: 'member',
    created_at: new Date(),
    // Embedded orders (denormalized for fast reads)
    orders: [
      {
        id: 1,
        total: 129.99,
        status: 'shipped',
        items: [
          { product: 'Laptop', quantity: 1, price: 999.99 },
          { product: 'Mouse', quantity: 2, price: 25.00 }
        ]
      }
    ]
  });

  // Query without joins
  const user = await db.collection('users').findOne(
    { email: 'alice@example.com' },
    { projection: { name: 1, orders: 1 } }
  );

  console.log('User:', user.name);
  console.log('Orders:', user.orders.length);
  await client.close();
}

setupDocuments();

# SQL: vertical scaling configuration
# PostgreSQL - allocate more resources
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';

# Restart to apply
# pg_ctl restart

# Read replicas for horizontal read scaling
# Create a replica:
# pg_basebackup -h primary -D /data/replica -P -R

// NoSQL: horizontal scaling (MongoDB sharding)
// Connect to mongos router
const { MongoClient } = require('mongodb');

async function setupSharding() {
  const adminDb = new MongoClient(
    'mongodb://mongos:27017'
  ).connect().then(c => c.db('admin'));

  // Enable sharding for database
  await adminDb.command({ enableSharding: 'shop' });

  // Shard collection on hashed key
  await adminDb.command({
    shardCollection: 'shop.users',
    key: { email: 'hashed' }
  });

  // Add shards
  await adminDb.command({ addShard: 'shard1/host1:27017' });
  await adminDb.command({ addShard: 'shard2/host2:27017' });

  console.log('Sharding configured for horizontal scaling');
}

-- SQL: ACID Transaction
BEGIN;

UPDATE accounts
SET balance = balance - 100.00
WHERE user_id = 1 AND balance >= 100.00;

-- If insufficient funds, this returns 0 rows
-- and we can ROLLBACK
UPDATE accounts
SET balance = balance + 100.00
WHERE user_id = 2;

COMMIT;
-- On failure: ROLLBACK guarantees atomicity

// NoSQL: MongoDB Transaction (multi-document)
const { MongoClient } = require('MongoDB');

async function transferFunds(fromId, toId, amount) {
  const client = new MongoClient('MongoDB://localhost:27017');
  await client.connect();
  const session = client.startSession();

  try {
    await session.withTransaction(async () => {
      const accounts = client.db('bank').collection('accounts');

      const fromAccount = await accounts.findOne(
        { user_id: fromId, balance: { $gte: amount } },
        { session }
      );

      if (!fromAccount) {
        throw new Error('Insufficient funds');
      }

      await accounts.updateOne(
        { user_id: fromId },
        { $inc: { balance: -amount } },
        { session }
      );
      await accounts.updateOne(
        { user_id: toId },
        { $inc: { balance: amount } },
        { session }
      );
    });

    console.log('Transfer completed successfully');
  } catch (error) {
    console.error('Transfer failed:', error.message);
    // Transaction automatically aborted
  } finally {
    await session.endSession();
    await client.close();
  }
}

transferFunds(1, 2, 100.00);