Skip to content

SQL_ORM_Deep_Dive.md

Latest commit

 

History

History
69 lines (58 loc) · 4.82 KB

SQL_ORM_Deep_Dive.md

File metadata and controls

69 lines (58 loc) · 4.82 KB
Raw

SQL & SQLAlchemy Deep Dive

One consolidated reference for relational design, advanced querying, and SQLAlchemy 2.0 patterns. Combines SQL DDL/Advanced Queries and SQLAlchemy CRUD/Relationships/Advanced Patterns content.

Table of Contents

  1. Relational Foundations (DDL)
  2. Querying Patterns (SQL)
  3. SQLAlchemy 2.0 Model Design
  4. CRUD Patterns
  5. Relationships & Loading Strategies
  6. Advanced ORM Patterns
  7. Testing Database Code
  8. Performance & Troubleshooting

1) Relational Foundations (DDL)

  • Core objects: databases, schemas, tables, views, indexes, sequences.
  • Column design: types (INT, TEXT, UUID, JSONB), nullability, defaults, check constraints.
  • Keys: primary keys (single or surrogate), unique constraints, foreign keys with ON DELETE/UPDATE behavior.
  • Migrations: forward-only scripts; avoid destructive changes without backups; add columns with defaults carefully; manage enum changes explicitly.

2) Querying Patterns (SQL)

  • Filtering: WHERE, IN, BETWEEN, ILIKE, null-safe comparisons (IS NULL).
  • Aggregation: GROUP BY, HAVING, window functions (ROW_NUMBER, LAG, SUM OVER).
  • Joins: INNER/LEFT/RIGHT/FULL; prefer explicit join conditions; ensure selective predicates to avoid blowups.
  • Pagination: LIMIT/OFFSET for basics; keyset pagination for large data.
  • Common patterns: EXISTS vs IN; CASE WHEN; CTEs for readability; materialized views for heavy joins.

3) SQLAlchemy 2.0 Model Design

  • Typed models: Mapped[T] + mapped_column; dataclasses optional; __tablename__ explicit.
  • Defaults: server_default vs python default; timestamps via func.now(); Enum with validate_strings=True.
  • Metadata: naming conventions for constraints to keep Alembic diffs stable.
  • Base patterns: declarative base in models/__init__.py; import models in models/__all__.py to register for migrations.

4) CRUD Patterns

  • Create: instantiate model, add to session, flush to get PK, commit in service layer.
  • Read: use select(Model).where(...); prefer scalars(); handle NoResultFound/MultipleResultsFound.
  • Update: load row, mutate fields, commit; for bulk, use update() with care (skips ORM events).
  • Delete: soft-delete column for business-critical data; otherwise session.delete(obj) with cascade awareness.
  • Transactions: context managers or explicit session.begin(); avoid nested commits in request handlers.

5) Relationships & Loading Strategies

  • Relationships: relationship with back_populates; specify cascade explicitly; secondary tables for many-to-many.
  • Loading: default to selectinload to avoid N+1; joinedload for small/1-1; lazy="raise" to catch accidental lazy loads in APIs.
  • Cardinality patterns: one-to-many via FK + backref; one-to-one via unique FK; many-to-many via association table or association object with extra columns.
  • Ordering: set order_by on relationships when deterministic ordering matters.

6) Advanced ORM Patterns

  • Domain services: keep business logic out of models; use services for workflows.
  • Repositories (optional): wrap session queries when you need abstraction; otherwise, pass session explicitly.
  • Soft deletes: deleted_at filters via query property or helper functions; ensure unique constraints account for soft deletes.
  • Versioning/audit: history tables or event listeners; include actor/context columns.
  • Bulk operations: session.execute(insert(Model).values(...)) for large imports; be aware of bypassed ORM events.
  • Schema evolution: feature flags around new columns; backfill jobs for non-nullable additions.

7) Testing Database Code

  • Fixtures: session fixture with rollback per test; use nested transactions or sessionmaker(bind=engine, expire_on_commit=False).
  • Factories: build test data with factory functions; avoid random data that hides determinism.
  • Isolation: mark tests that hit DB; avoid sharing sessions across tests; seed reference data per test module.
  • Assertions: verify both data and constraints (unique violations, FK errors); assert eager loading to avoid lazy access in APIs.
  • Contract with API tests: reuse the same factories in API layer tests for realistic payloads.

8) Performance & Troubleshooting

  • Explain plans: run EXPLAIN ANALYZE on heavy queries; add indexes for filter/join columns; composite indexes ordered by selectivity.
  • Connection management: pool sizing (gunicorn workers × expected concurrency); timeouts for long-running queries.
  • N+1 detection: enable lazy="raise" in tests; log SQL to spot chatter; use selectinload/joinedload.
  • Locks & contention: keep transactions short; avoid long-running migrations during peak.
  • Monitoring: metrics for query latency, errors, pool saturation; logs with query params stripped of PII.