SQL vs NoSQL: глубокий разбор

<?php

declare(strict_types=1);

/**
 * Relational model: users, orders, order_items — three normalized tables.
 * Pros: constraints enforce integrity; rich queries; multi-row ACID.
 * Cons: reading the whole order = 3 JOINs.
 */
final class OrderRepositorySql
{
    public function __construct(private readonly \PDO $db) {}

    public function create(int $userId, array $items): int
    {
        $this->db->beginTransaction();
        try {
            $this->db->prepare(
                'INSERT INTO orders(user_id, status, created_at) VALUES (:u, :s, NOW())'
            )->execute(['u' => $userId, 's' => 'new']);
            $orderId = (int) $this->db->lastInsertId();

            $stmt = $this->db->prepare(
                'INSERT INTO order_items(order_id, sku, qty, price) VALUES (:o, :s, :q, :p)'
            );
            foreach ($items as $item) {
                $stmt->execute([
                    'o' => $orderId,
                    's' => $item['sku'],
                    'q' => $item['qty'],
                    'p' => $item['price'],
                ]);
            }
            $this->db->commit();
            return $orderId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function getFull(int $orderId): array
    {
        $sql = 'SELECT o.id, o.status, u.email,
                       i.sku, i.qty, i.price
                FROM orders o
                JOIN users u ON u.id = o.user_id
                JOIN order_items i ON i.order_id = o.id
                WHERE o.id = :id';
        $stmt = $this->db->prepare($sql);
        $stmt->execute(['id' => $orderId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}

package orders

import (
	"context"

	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
)

// Document model: order embeds items and denormalized user snapshot.
// Pros: single-doc read, no JOINs, flexible schema.
// Cons: data duplication; updating user email requires fan-out update.
type Order struct {
	ID     string     `bson:"_id"`
	User   UserSnap   `bson:"user"`
	Items  []Item     `bson:"items"`
	Status string     `bson:"status"`
}

type UserSnap struct {
	ID    string `bson:"id"`
	Email string `bson:"email"` // Denormalized snapshot.
}

type Item struct {
	SKU   string  `bson:"sku"`
	Qty   int     `bson:"qty"`
	Price float64 `bson:"price"`
}

type Repo struct {
	col *mongo.Collection
}

func (r *Repo) Create(ctx context.Context, o Order) error {
	// Single insert, atomic at document level.
	_, err := r.col.InsertOne(ctx, o)
	return err
}

func (r *Repo) Get(ctx context.Context, id string) (Order, error) {
	var out Order
	err := r.col.FindOne(ctx, bson.M{"_id": id}).Decode(&out)
	return out, err
}

using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;

// ---- Relational model (PostgreSQL): three normalized tables ----
// Pros: constraints enforce integrity, rich queries, multi-row ACID.
// Cons: reading the whole order costs 2 JOINs.
public sealed class OrderRepositorySql(IDbConnection db)
{
    public async Task<long> CreateAsync(long userId, IReadOnlyList<OrderItemDto> items)
    {
        using var tx = db.BeginTransaction();
        try
        {
            var orderId = await db.ExecuteScalarAsync<long>(
                "INSERT INTO orders(user_id, status, created_at) VALUES (@UserId, 'new', NOW()) RETURNING id",
                new { UserId = userId },
                tx);

            // Dapper expands the list into a multi-row insert.
            await db.ExecuteAsync(
                "INSERT INTO order_items(order_id, sku, qty, price) VALUES (@OrderId, @Sku, @Qty, @Price)",
                items.Select(i => new { OrderId = orderId, i.Sku, i.Qty, i.Price }),
                tx);

            tx.Commit();
            return orderId;
        }
        catch
        {
            tx.Rollback();
            throw;
        }
    }

    public async Task<IReadOnlyList<dynamic>> GetFullAsync(long orderId) =>
        (List<dynamic>)await db.QueryAsync(
            """
            SELECT o.id, o.status, u.email, i.sku, i.qty, i.price
            FROM orders o
            JOIN users u       ON u.id = o.user_id
            JOIN order_items i ON i.order_id = o.id
            WHERE o.id = @Id
            """,
            new { Id = orderId });
}

public readonly record struct OrderItemDto(string Sku, int Qty, decimal Price);

// ---- Document model (MongoDB): order embeds items and a user snapshot ----
// Pros: single-document read, no JOINs, flexible schema.
// Cons: duplication; changing a user email needs a fan-out update.
public sealed class Order
{
    [BsonId] public required string Id { get; init; }
    public required UserSnapshot User { get; init; }
    public required List<OrderItem> Items { get; init; }
    public required string Status { get; init; }
}

public sealed record UserSnapshot(string Id, string Email); // Email is a denormalized snapshot.

public sealed record OrderItem(string Sku, int Qty, decimal Price);

public sealed class OrderRepositoryMongo(IMongoCollection<Order> collection)
{
    // Single insert, atomic at document level.
    public Task CreateAsync(Order order) => collection.InsertOneAsync(order);

    public Task<Order> GetAsync(string id) =>
        collection.Find(o => o.Id == id).FirstOrDefaultAsync();
}

from __future__ import annotations

from dataclasses import asdict, dataclass, field
from decimal import Decimal

import psycopg
from motor.motor_asyncio import AsyncIOMotorCollection
from psycopg.rows import dict_row

@dataclass(frozen=True, slots=True)
class OrderItem:
    sku: str
    qty: int
    price: Decimal

# ---- Relational model (PostgreSQL): three normalized tables ----
# Pros: constraints enforce integrity, rich queries, multi-row ACID.
# Cons: reading the whole order costs 2 JOINs.
class OrderRepositorySql:
    def __init__(self, conn: psycopg.Connection) -> None:
        self._conn = conn

    def create(self, user_id: int, items: list[OrderItem]) -> int:
        # `with conn` commits on success and rolls back on any exception
        with self._conn, self._conn.cursor() as cur:
            cur.execute(
                "INSERT INTO orders(user_id, status, created_at)"
                " VALUES (%s, 'new', NOW()) RETURNING id",
                (user_id,),
            )
            order_id = cur.fetchone()[0]

            cur.executemany(
                "INSERT INTO order_items(order_id, sku, qty, price) VALUES (%s, %s, %s, %s)",
                [(order_id, i.sku, i.qty, i.price) for i in items],
            )

        return order_id

    def get_full(self, order_id: int) -> list[dict]:
        with self._conn.cursor(row_factory=dict_row) as cur:
            cur.execute(
                """
                SELECT o.id, o.status, u.email, i.sku, i.qty, i.price
                FROM orders o
                JOIN users u       ON u.id = o.user_id
                JOIN order_items i ON i.order_id = o.id
                WHERE o.id = %s
                """,
                (order_id,),
            )
            return cur.fetchall()

# ---- Document model (MongoDB): order embeds items and a user snapshot ----
# Pros: single-document read, no JOINs, flexible schema.
# Cons: duplication; changing a user email needs a fan-out update.
@dataclass(frozen=True, slots=True)
class UserSnapshot:
    id: str
    email: str  # denormalized snapshot

@dataclass(slots=True)
class Order:
    id: str
    user: UserSnapshot
    status: str
    items: list[OrderItem] = field(default_factory=list)

class OrderRepositoryMongo:
    def __init__(self, collection: AsyncIOMotorCollection) -> None:
        self._collection = collection

    async def create(self, order: Order) -> None:
        # Single insert, atomic at document level
        doc = asdict(order)
        doc["_id"] = doc.pop("id")
        await self._collection.insert_one(doc)

    async def get(self, order_id: str) -> dict | None:
        return await self._collection.find_one({"_id": order_id})