Module 2: JSONB with SQLAlchemy and usage patterns

In module 1 you mastered JSONB in pure SQL: operators (->, ->>, @>, ?), JSON Path queries, and GIN indexes with their two operator classes. You know how to write JSONB queries that the planner speeds up. You know how to choose between jsonb_ops and jsonb_path_ops with concrete criteria. But there's a practical problem: you don't write raw SQL in production. You write async SQLAlchemy 2.0 inside a FastAPI app.

This module closes that gap. You learn to express every JSONB operator and feature you already know, now from the ORM, with idiomatic Python code. And you learn the three canonical patterns where JSONB beats relational columns in a real app: dynamic configuration, extensible metadata, and polymorphic data.

By the end of the module, your Blog API will be able to store flexible metadata in its posts without migrations every time the marketing team asks for a new field, validate the payload with Pydantic before touching the database, and query nested fields as easily as you query flat columns today.

Where are we? Where are we going?

You're in the second module of Block 1: Deep JSONB of the Advanced PostgreSQL for Backend guide (#14 of the Backend Python with FastAPI path). The block has two consecutive modules because JSONB is the most used and most misunderstood feature of the modern PostgreSQL stack.

Block 1: Deep JSONB
├── Module 1: JSONB Operators and Indexing  ← done
└── Module 2: JSONB with SQLAlchemy + Patterns  ← you are here

Block 2: Search in PostgreSQL
└── Module 3: Full-Text Search + pg_trgm  ← next

Blocks 3-5: Scale, Concurrency, Recursive CTEs

Module 1 gave you the what and the why from the engine's perspective: how PostgreSQL stores, indexes, and queries JSONB. This module gives you the how from the Python backend dev's perspective: how to declare JSONB columns with Mapped[dict], how to express accessors with ORM syntax, how to do partial updates without rewriting the whole column, how to validate payloads with Pydantic and, above all, how to decide when JSONB is the right tool and when it's an anti-pattern disguised as flexibility.

Professional objective

By the end of this module you'll be able to:

Declare and populate JSONB columns from SQLAlchemy 2.0 models with correct type hints (Mapped[dict[str, Any]]) and safe defaults (default=dict, not default={}).
Access nested fields with ORM syntax: Post.metadata["seo"]["title"].astext or func.jsonb_extract_path_text.
Do partial updates without losing data to overwrites: func.jsonb_set for internal paths, MutableDict for in-place mutations.
Validate incoming and outgoing JSONB with Pydantic v2 using TypeAdapter and discriminated unions for polymorphic payloads.
Apply the three canonical patterns (dynamic config, extensible metadata, polymorphic data) with clear criteria for when JSONB wins and when a relational table is better.
Recognize the smell test of the critical anti-pattern: if you need JOINs or complex aggregations over the data, it isn't JSONB, it's a table.

Why does this module matter?

In your real work as a Python backend dev, JSONB isn't a curiosity feature — it's a schema component that shows up every week. Three concrete scenarios where you'll use it:

1. Marketing asks for a new field every sprint. "Add og_image_alt", "now twitter_card_type", "for Black Friday we need promo_banner_url". If each one is a new column, your Alembic has 40 migrations per quarter and your posts table has 80 columns, half of them nullable and empty for most rows. If you put them in a metadata JSONB column, the team adds fields without touching the schema.

2. An external integration returns a payload that changes between versions. Stripe, Twilio, Sendgrid: every webhook comes with a structure that evolves. Storing the complete payload in JSONB lets you query it later with flexible queries, without assuming a fixed schema that breaks in the next version.

3. Your app handles polymorphic data. An event can be a purchase with amount and currency, or a signup with referrer and source, or a view with page and duration. Three separate tables are over-engineering; one table with columns for everything is heavy and empty. An events table with a payload JSONB validated by a discriminator is the right pattern.

If you leave the module knowing how to apply JSONB with SQLAlchemy and how to tell when it wins from when it doesn't, you're the dev on the team the tech lead hands the ticket "evaluate whether this goes in columns or in JSONB." That's worth more than knowing 50 libraries.

A scenario that illustrates the module

Imagine you get to your team on Monday and the product manager assigns you a ticket: "the SEO team needs to store metadata per post (Open Graph, Twitter Card, structured data, a custom canonical URL) and it's going to change the fields every sprint without warning."

With what you'll learn in this module, your plan is:

Capsule 02: you declare metadata: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict) in the Post model. You generate an Alembic migration and run it.
Capsule 03: your first test writes post.metadata["seo"] = {"title": "..."} and then does await session.commit(). The test fails silently — it isn't persisted. You learn the MutableDict gotcha and fix it.
Capsule 04: the PATCH /posts/{id}/metadata endpoint receives a JSON payload. Instead of accepting dict[str, Any] (which is saying goodbye to validation), you define a Pydantic PostMetadata that still allows extensibility but validates the known fields. The SEO team can't put in og_image: 12345 by mistake.
Capsule 05: a product manager says "we want feature flags per tenant for A/B testing." You apply the dynamic configuration pattern with JSONB without adding 12 boolean columns.
Capsule 06: marketing says "we want custom fields per client." You apply the extensible metadata pattern without a migration.
Capsule 07: internal logging needs to record audit events with a payload that varies by action (login, password_change, delete_post). You apply polymorphic data with discriminated unions.
Capsule 08: you take everything you learned to the Blog API refactor: you add posts.metadata, migrate the old optional fields to the JSONB with an idempotent Alembic script, and deliver a PR with before/after benchmarks.

Each capsule is a concrete piece of the flow. You aren't learning theory — you're building the muscle of "when they ask me for this, my hand moves on its own."

Module map

Capsule	Topic	What you'll learn
02	`Mapped[dict]` and JSONB types in SQLAlchemy	Declaring JSONB columns with correct type hints, safe defaults, ORM accessors (`["k"]["k"].astext`), native functions (`func.jsonb_extract_path_text`, `JSONB.contains`, `JSONB.has_key`). Every example shows the SQL it generates.
03	Mutations and `MutableDict`	The most expensive gotcha of JSONB with an ORM: SQLAlchemy doesn't detect `obj.metadata["x"] = 1`. You learn to use `MutableDict.as_mutable(JSONB)` and to do partial updates with `func.jsonb_set` for production.
04	Validation with Pydantic v2 and JSONB	Validating incoming JSONB in FastAPI endpoints with `TypeAdapter`, nested schemas, discriminated unions for polymorphic payloads. How to keep JSONB from becoming a "bag of anything."
05	Pattern: dynamic configuration	Per-tenant settings, feature flags, and per-user customization in JSONB. Decision matrix: when JSONB wins and when a relational table with columns is better.
06	Pattern: extensible metadata	Custom fields, SEO metadata, structured tags. How to model fields that change without a migration. The anti-pattern smell test: when "metadata" is becoming a badly designed mini database.
07	Pattern: polymorphic data	Events with a payload per type, audit logs with variable detail. Discriminator + Pydantic discriminated union + queries with `@>` by discriminator.
08	Project: extend the Blog API with JSONB	A minimal refactor of the Blog API (from guide #8) adding `posts.metadata` with a two-phase Alembic migration, Pydantic validation, GIN-indexed queries, tests. PR-style delivery.

Connection with the integrative project

The guide's final project (module 8 of the complete workbook) is a big refactor of the Blog API. This module is the direct foundation of the "JSONB metadata in posts" component of that refactor.

The module 2 project (capsule 08) is the first step: adding JSONB to a single entity (posts) with a clean migration and validation. The final project extends that to multiple entities, combines it with FTS, partitioning, and materialized views, and requires documenting the architectural decisions.

If you do the mini-project of this module well, the JSONB component of the final project boils down to "I already have it, I just integrate it with the rest."

What is NOT covered in this module

GIN indexes on JSONB — covered in module 1 of this same guide. Here we reference them when designing queries but we don't re-explain them.
Full-Text Search over JSONB — covered in module 3. If you need free-text search over JSONB fields, that's FTS, not JSONB operators.
Partitioning — covered in module 4. If your JSONB table goes past 50M rows, partitioning comes in; but it isn't a JSONB topic in itself.
Materialized views over JSONB data for analytics — covered in module 5.
Multitenancy with Row-Level Security — covered in guide #13 (SQL Patterns for Production APIs). When we talk about "per-tenant config" in capsule 05, we assume isolation is already solved by RLS.
pgvector — it belongs to the AI Engineering path, not the Backend path. If your use case is embeddings, this isn't the guide.

Traps to avoid while taking the module

1. Assuming SQLAlchemy "takes care of" JSONB. The ORM isn't magic. There's an expensive gotcha (capsule 03) where mutating the dict in-place doesn't generate an UPDATE. If you skip that capsule because "you already understood how to declare the column," you're going to lose data in production and debug for hours.

2. Treating Pydantic as an obstacle. It's tempting to define the type as dict[str, Any] and forget about it. It works until a client sends {"og_image": null} and your app crashes while rendering the HTML. Capsule 04 teaches you to validate without sacrificing flexibility.

3. Using JSONB for data that should be tables. The #1 abuse of JSONB is storing tags as an array, comments as an array of objects, or categories as a nested object. If you need to JOIN or aggregate (COUNT comments where ...), it's a table, not JSONB. Capsule 06 develops the smell test.

4. Rewriting the whole column when updating one field. The naive pattern obj.metadata = {**obj.metadata, "x": 1} works but generates an UPDATE that rewrites the complete column. For concurrency and large JSONs, this is problematic. Capsule 03 teaches func.jsonb_set as the senior pattern.

5. Overlooking the Alembic migration. Migrating from optional columns (seo_title VARCHAR, seo_description VARCHAR) to a metadata JSONB column with copied data is a common and non-trivial operation. Capsule 08 shows the idempotent script with a rollback. Skipping it means delivering an incomplete project.

Self-assessment question

Before starting this module, try to answer mentally:

Do you know what SQLAlchemy generates when you write Post.metadata["seo"]["title"].astext == "x"? If not, you're going to have to go back to module 1 every two pages.
Do you know the difference between func.jsonb_set and simply reassigning the whole dict? If not, that's fine — it's what you're going to learn.
Do you know how to define a Pydantic model with a discriminator? If you've never seen it, open the official Pydantic v2 docs on discriminated unions before capsule 04.
Do you know when a new column is better than an entry in JSONB? If not, wait for capsule 05 — the decision matrix is there.

If you hesitated on more than two, don't worry — the module is designed to take you from "I've heard of it" to "I apply it well."

Evidence of success

By the end of the module you'll know you succeeded if:

You can declare a JSONB column with Mapped[dict[str, Any]] and correct defaults without googling.
You know that obj.metadata["x"] = 1 isn't persisted without MutableDict and you know the two patterns for fixing it.
You can write a FastAPI endpoint that receives JSONB validated by Pydantic and persists it with async SQLAlchemy 2.0.
Faced with a "we need to store field X" ticket, you know in 30 seconds whether it goes in a new column, in the existing JSONB, or in a new table.
You can present the anti-pattern smell test (do I need a JOIN or an aggregation?) and apply it to a real case.
Your mini-project PR passes your own code review: a clean Alembic migration, Pydantic validation, a GIN-indexed query, tests for the happy path and the edge cases.

We start in the next capsule

Capsule 02 starts with the most concrete thing: how you declare a JSONB column in a SQLAlchemy 2.0 model, which types you use, which defaults are safe, and which accessors the ORM gives you to query nested fields without writing raw SQL.

Before moving on, make sure you have:

PostgreSQL 16+ running locally (Docker or a native installation).
Python 3.11+ with an active virtual environment.
SQLAlchemy 2.0+ installed: pip install "sqlalchemy[asyncio]>=2.0" asyncpg.
Pydantic 2.6+ installed: pip install "pydantic>=2.6".
Module 1 of this guide completed, especially capsules 03 (operators) and 05 (GIN indexes).

Resources for the module

SQLAlchemy 2.0 — Working with JSON — the official reference for JSONB and its methods.
SQLAlchemy 2.0 — Mutation Tracking (MutableDict) — the gotcha covered in capsule 03, explained by the ORM's creator.
Pydantic v2 — Discriminated Unions — the basis for validating polymorphic payloads in capsule 07.
PostgreSQL 16 — JSON Functions and Operators — the reference for jsonb_set, jsonb_build_object, and company.
Bruce Momjian — JSONB tricks — practical patterns from the core team.
Mike Bayer — "Asynchronous I/O with SQLAlchemy" — the foundation of the async style we'll use.

Module 2 — Advanced PostgreSQL for Backend Guide

Next capsule: Mapped[dict] and JSONB types in SQLAlchemy — the concrete translation of what you learned in module 1 to the ORM you use every day.

← PreviousModule project: replicating the 4s → 12ms case with your own 5M rows Next →`Mapped[dict]` and JSONB types in SQLAlchemy 2.0