<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Johni Douglas Marangon on Medium]]></title>
        <description><![CDATA[Stories by Johni Douglas Marangon on Medium]]></description>
        <link>https://medium.com/@johnidouglasmarangon?source=rss-23c93e39f596------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*xs_YpaVtUXF_zxzqGGK9Lg.jpeg</url>
            <title>Stories by Johni Douglas Marangon on Medium</title>
            <link>https://medium.com/@johnidouglasmarangon?source=rss-23c93e39f596------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 14:23:34 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@johnidouglasmarangon/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Database Sharding Explained: A Hands-On Guide with Python, FastAPI, and SQLite]]></title>
            <link>https://medium.com/@johnidouglasmarangon/database-sharding-explained-a-hands-on-guide-with-python-fastapi-and-sqlite-bf3d145369ff?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/bf3d145369ff</guid>
            <category><![CDATA[sharding]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[design-systems]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Wed, 11 Nov 2026 18:16:56 GMT</pubDate>
            <atom:updated>2026-11-11T18:16:56.509Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/740/0*ZgzH5uYyxR8sBkYC" /></figure><p><em>Learn how sharding works, how to pick a shard key, and build a working sharded to-do API from scratch</em></p><p>I’ve been building backend systems for more than 18 years. Recently, I’ve been exploring some industry patterns to understand how they can help solve specific problems. One of the most interesting patterns I’ve come across is <strong>sharding</strong>.</p><p>I’ve never used sharding in a production environment, but I’m curious about how it works in real-world applications and what challenges come with implementing it.</p><p>In this post I’ll explain what database sharding is, when you actually need it, the main strategies, and the problems it brings. Then we’ll build a small sharded to-do API in Python with FastAPI, using several SQLite files as independent shards. Every piece of code here was run, and the output is real.</p><h2>Introduction to Database Sharding</h2><p><strong>Sharding</strong> means splitting one large dataset horizontally across several independent databases, called <strong>shards</strong>. Each shard has the same schema but holds only a subset of the rows. Together, the shards hold the whole dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W2Opmh0fZTpXb9JmdQS74w.jpeg" /></figure><p>Instead of one server doing all the work, <strong>every shard handles a fraction of the reads, writes, and storage</strong>. Want more capacity? Add more shards, at the cost of rebalancing data (more on that in Step 6).</p><h2>Why Do We Need Database Sharding?</h2><p>A single database server eventually hits limits that tuning can’t fix:</p><ul><li><strong>Write throughput.</strong> Read replicas scale reads, but every write still goes to one primary.</li><li><strong>Storage.</strong> Tables with billions of rows make indexes, backups, and migrations slow and risky.</li><li><strong>Memory.</strong> When the working set no longer fits in RAM, performance drops fast.</li><li><strong>Vertical scaling has a ceiling.</strong> Bigger machines cost more and more, and at some point there is no bigger machine.</li><li><strong>Blast radius.</strong> With a single database, one bad night affects 100% of your users.</li></ul><p>Sharding gives you <strong>horizontal scaling</strong> for writes and storage. It <em>can</em> also isolate failures: if shards run on separate hosts and one goes down, requests scoped to users on the other shards keep working. Cross-shard queries, though, need explicit handling for a missing shard, or they fail too.</p><h2>Sharding vs Partitioning</h2><p>People often use these words as if they meant the same thing, and some systems do: DynamoDB and Cassandra, for example, call their distributed units &quot;partitions.&quot; In this post, I use them in a narrower sense.</p><figure><img alt="Embedded image" src="https://cdn-images-1.medium.com/proxy/1*mPYNFF9rk2FUEJcAlTLV5Q.png" /></figure><p>Here, <strong>partitioning</strong> means splitting a table into pieces inside one database server (like PostgreSQL’s declarative partitioning), and <strong>sharding</strong> means horizontal partitioning across independent database servers. Partitioning in this sense stays inside one server. Sharding crosses the network, and that is where the hard problems come from.</p><h2>How Database Sharding Works</h2><p>Every sharded system has three parts:</p><ol><li><strong>A shard key.</strong> A column (or set of columns) whose value decides where a row lives, for example user_id.</li><li><strong>A routing function.</strong> Maps a shard key to a shard: shard = f(shard_key).</li><li><strong>A router.</strong> The code that runs that function on every query. It can live in your application, in a proxy (like Vitess’s VTGate), or in the database itself (like MongoDB’s mongos).</li></ol><p>When a query filters on a single, complete shard-key value (user_id = &#39;alice&#39;), the router can send it to exactly <strong>one</strong> shard. When it filters on a range or a partial key, the router may have to ask a <strong>subset</strong> of shards, depending on the strategy. When it doesn’t include the shard key at all, the router has to ask <strong>all</strong> shards and merge the results. This is called <strong>scatter-gather</strong> (or fan-out). Keeping most queries in the first case is the whole game.</p><h2>Sharding Strategies</h2><p>Sharding strategies distribute data across multiple database instances to improve scalability, performance, and availability as an application grows.</p><h4>Range-based sharding</h4><p>Each shard owns a continuous range of keys.</p><pre>shard_0: user_id         1 - 1,000,000<br>shard_1: user_id 1,000,001 - 2,000,000<br>shard_2: user_id 2,000,001 - ...</pre><ul><li><strong>Pros:</strong> simple, and range queries <strong>on the shard key</strong> (WHERE user_id BETWEEN ... here, or WHERE created_at BETWEEN ... if you range-shard by time) touch few shards. A range on any other column still hits every shard.</li><li><strong>Cons:</strong> hot spots. If keys grow over time (auto-increment IDs, timestamps), all new writes land on the last shard.</li></ul><h4>Hash-based sharding</h4><p>Hash the key and take the remainder: shard = hash(key) % N.</p><ul><li><strong>Pros:</strong> spreads data and load evenly, no hot spot from increasing keys.</li><li><strong>Cons:</strong> range queries must hit every shard, and changing N moves most keys (we&#39;ll measure this later).</li></ul><h4><strong>Directory-based sharding</strong></h4><p>A lookup table maps each key (or tenant) to a shard.</p><pre>tenant_id | shard<br>----------+--------<br>acme      | shard_0<br>globex    | shard_2<br>initech   | shard_1</pre><ul><li><strong>Pros:</strong> full flexibility. You can move one big tenant to its own shard.</li><li><strong>Cons:</strong> the directory is an extra lookup, must be cached, and becomes a critical dependency.</li></ul><h4>Geographic sharding</h4><p>Data is placed by region: Brazilian users in sa-east-1, European users in eu-west-1.</p><ul><li><strong>Pros:</strong> lower latency for users, and it helps with data residency laws like GDPR and Brazil’s LGPD.</li><li><strong>Cons:</strong> uneven load across regions, and users who travel or move need special handling.</li></ul><p>In practice, many systems combine these. For example, geographic at the top level, then hash inside each region.</p><h2>Choosing a Shard Key</h2><p>The shard key is the most important decision you’ll make and the hardest to change later. A good shard key has:</p><ul><li><strong>High cardinality.</strong> Many distinct values. country with 5 values, can&#39;t spread data across 64 shards.</li><li><strong>Even distribution.</strong> No single value should own a big share of rows or traffic.</li><li><strong>Query alignment.</strong> Most queries should include it, so they hit one shard. If 90% of queries are “everything for user X”, shard by user_id. That is exactly what the to-do API later in this post does.</li><li><strong>Stability.</strong> It shouldn’t change. Changing a shard key value means moving the row to another shard.</li></ul><p>Common mistakes:</p><ul><li><strong>Sharding by a timestamp or auto-increment ID with range sharding</strong>, which sends all new writes to one shard.</li><li><strong>Picking a low-cardinality key</strong> like status or country.</li><li><strong>Ignoring “celebrity” keys.</strong> One huge customer, or one account with millions of followers, can overload a shard on its own.</li><li><strong>Splitting related data.</strong> If orders and order items are sharded by different keys, every order page becomes a cross-shard query. Shard related tables by the same key (for example: customer_id) so they live together.</li></ul><h2>Challenges of Database Sharding</h2><p><strong>Cross-shard queries.</strong> Anything without the shard key becomes scatter-gather. Unless your database or proxy does it for you, joins, ORDER BY, LIMIT, COUNT, and pagination now happen in your application. Latency is set by the slowest shard.</p><p><strong>Transactions.</strong> A transaction inside one shard is easy. A transaction across shards needs two-phase commit, the saga pattern, or a design that avoids it. They are not equivalent: two-phase commit gives atomicity but adds a coordinator and can block while it waits; a saga is a sequence of local transactions with compensating actions, so it is eventually consistent, not atomic or isolated. Most teams pick the last option and choose shard keys so business operations stay on one shard.</p><p><strong>Rebalancing.</strong> Shards grow unevenly, and eventually you need more of them. Moving data while serving traffic, without losing writes, is one of the hardest operations in this space. Your routing function decides how much data has to move.</p><p><strong>Data consistency.</strong> Global unique constraints, foreign keys across shards, and consistent snapshots for reports are no longer free. You need globally unique IDs (UUIDs, Snowflake-style IDs) and often a separate analytics store.</p><p><strong>Operational overhead.</strong> N shards means N backups, N schema migrations, N things to monitor. Tools exist to help, but the complexity is real.</p><h2>Hands-on: A Sharded To-Do API with Python, FastAPI, and SQLite</h2><p>Now let’s build it. We’ll create a small <strong>to-do API</strong> where users are spread across 4 SQLite files. The logged-in user is the shard key: we hash the user id to pick a shard, so <strong>all of a user’s to-dos live together on one shard</strong>.</p><p>That’s the same choice real multi-tenant SaaS products make. Almost every query in a to-do app is “show me <em>my</em> to-dos”, so sharding by user means almost every request touches exactly one database.</p><p>Setup (Python 3.10 or newer; I validated the code with Python 3.13, FastAPI 0.162, and Pydantic 2.14):</p><pre>python3 -m venv .venv &amp;&amp; source .venv/bin/activate<br>pip install &quot;fastapi[standard]&quot;</pre><p>Each SQLite file plays the role of an independent database. Keep in mind what this demo does and doesn’t show: all four files sit on one machine and one disk, so they demonstrate the <strong>routing logic</strong>, not extra hardware capacity or failure isolation. With real PostgreSQL or MySQL instances on separate hosts, the routing logic stays the same, but the data-access code would change: the driver, the SQL placeholder style (? vs %s), connection pooling, transaction settings, and error handling.</p><h2>Step 1: A stable hash and a shard router</h2><p>The first trap: <strong>don’t use Python’s built-in </strong><strong>hash() for routing.</strong> For strings, it is randomized per process (see PYTHONHASHSEED), so two workers, or the same app after a restart, would send the same user to different shards. We use SHA-256 instead, which gives the same result on every machine and in every language.</p><p>Create a shards.py file:</p><pre># shards.py<br>import hashlib, sqlite3<br>from contextlib import closing, contextmanager<br>from pathlib import Path<br><br>SCHEMA = &quot;&quot;&quot;CREATE TABLE IF NOT EXISTS todos (<br>    id TEXT PRIMARY KEY,<br>    user_id TEXT NOT NULL,<br>    title TEXT NOT NULL,<br>    done INTEGER NOT NULL DEFAULT 0)&quot;&quot;&quot;<br>INDEX = &quot;CREATE INDEX IF NOT EXISTS idx_todos_user ON todos (user_id)&quot;<br><br><br>def stable_hash(key: str) -&gt; int:<br>    # Python&#39;s hash() is randomized per process; sha256 is the same everywhere.<br>    return int.from_bytes(hashlib.sha256(key.encode()).digest()[:8], &quot;big&quot;)<br><br><br>class ShardRouter:<br>    def __init__(self, data_dir: str, num_shards: int = 4):<br>        self.paths = [Path(data_dir) / f&quot;shard_{i}.db&quot; for i in range(num_shards)]<br>        for p in self.paths:<br>            p.parent.mkdir(parents=True, exist_ok=True)<br>            # sqlite3&#39;s &quot;with conn&quot; only commits; closing() actually closes it.<br>            with closing(sqlite3.connect(p)) as conn, conn:<br>                conn.execute(SCHEMA)<br>                conn.execute(INDEX)<br><br>    def shard_for(self, user_id: str) -&gt; int:<br>        return stable_hash(user_id) % len(self.paths)<br><br>    @contextmanager<br>    def connect(self, shard: int):<br>        conn = sqlite3.connect(self.paths[shard])<br>        conn.row_factory = sqlite3.Row<br>        try:<br>            yield conn<br>            conn.commit()<br>        finally:<br>            conn.close()</pre><p>shard_for() is the whole routing function: hash the user id, take modulo the number of shards. Every shard has the same todos table, with an index on user_id because every user-facing query filters by it.</p><h2>Step 2: The logged-in user picks the shard</h2><p>In a real application, the user id comes from a validated session or JWT. To keep the example focused, we read it from an X-User request header. A FastAPI dependency does two jobs at once: it rejects requests without a user (401), and it resolves the user&#39;s shard <strong>once</strong>, at the edge of the request.</p><blockquote><strong>Note:</strong><em> </em>The X-User header is for example purposes only. Any client can set a header to any value, so never trust it in production. In a real application, get the user from your authentication layer instead, for example a validated JWT or OAuth2 access token, a server-side session cookie, or a user id injected by your API gateway after it authenticates the request. The sharding logic stays the same: whatever identifies the user becomes the shard key.</blockquote><p>Here is the dependency (an excerpt from app.py, shown in full in the next step):</p><pre># helpers.py<br>def current_user(x_user: Annotated[str | None, Header()] = None) -&gt; CurrentUser:<br>    if not x_user:<br>        raise HTTPException(401, &quot;Missing X-User header&quot;)<br><br>    return CurrentUser(id=x_user, shard=router.shard_for(x_user))</pre><p>From here on, every endpoint receives a CurrentUser that already knows where its data lives. The handlers never think about sharding again.</p><h2>Step 3: CRUD endpoints that hit one shard</h2><p>Here is the full API. Create the app.py file:</p><pre># app.py<br>import os, uuid<br>from typing import Annotated<br>from fastapi import Depends, FastAPI, Header, HTTPException<br>from pydantic import BaseModel<br>from shards import ShardRouter<br><br>app = FastAPI(title=&quot;Sharded To-Do API&quot;)<br># Don&#39;t change NUM_SHARDS once data exists: there is no migration (see Step 6).<br>router = ShardRouter(os.getenv(&quot;SHARD_DIR&quot;, &quot;data&quot;), int(os.getenv(&quot;NUM_SHARDS&quot;, &quot;4&quot;)))<br><br><br>class TodoIn(BaseModel):<br>    title: str<br>    done: bool = False<br><br><br>class TodoPatch(BaseModel):<br>    title: str | None = None<br>    done: bool | None = None<br><br><br>class CurrentUser(BaseModel):<br>    id: str<br>    shard: int<br><br><br>def current_user(x_user: Annotated[str | None, Header()] = None) -&gt; CurrentUser:<br>    # In a real app this comes from a validated token; here it&#39;s the X-User header.<br>    if not x_user:<br>        raise HTTPException(401, &quot;Missing X-User header&quot;)<br>    return CurrentUser(id=x_user, shard=router.shard_for(x_user))<br><br><br>User = Annotated[CurrentUser, Depends(current_user)]<br><br><br>def to_dict(row, shard: int) -&gt; dict:<br>    return {**dict(row), &quot;done&quot;: bool(row[&quot;done&quot;]), &quot;shard&quot;: shard}<br><br><br>def fetch(user: CurrentUser, todo_id: str) -&gt; dict:<br>    with router.connect(user.shard) as conn:<br>        row = conn.execute(<br>            &quot;SELECT * FROM todos WHERE id = ? AND user_id = ?&quot;, (todo_id, user.id)<br>        ).fetchone()<br>    if row is None:<br>        raise HTTPException(404, &quot;To-do not found&quot;)<br>    return to_dict(row, user.shard)<br><br><br>@app.post(&quot;/todos&quot;, status_code=201)<br>def create_todo(todo: TodoIn, user: User):<br>    todo_id = str(uuid.uuid4())<br>    with router.connect(user.shard) as conn:<br>        conn.execute(<br>            &quot;INSERT INTO todos (id, user_id, title, done) VALUES (?, ?, ?, ?)&quot;,<br>            (todo_id, user.id, todo.title, int(todo.done)),<br>        )<br>    return fetch(user, todo_id)<br><br><br>@app.get(&quot;/todos&quot;)<br>def list_todos(user: User, done: bool | None = None):<br>    sql, params = &quot;SELECT * FROM todos WHERE user_id = ?&quot;, [user.id]<br>    if done is not None:<br>        sql += &quot; AND done = ?&quot;<br>        params.append(int(done))<br>    with router.connect(user.shard) as conn:<br>        rows = conn.execute(sql + &quot; ORDER BY rowid&quot;, params).fetchall()<br>    return [to_dict(r, user.shard) for r in rows]<br><br><br>@app.get(&quot;/todos/{todo_id}&quot;)<br>def get_todo(todo_id: str, user: User):<br>    return fetch(user, todo_id)<br><br><br>@app.put(&quot;/todos/{todo_id}&quot;)<br>def replace_todo(todo_id: str, todo: TodoIn, user: User):<br>    fetch(user, todo_id)<br>    with router.connect(user.shard) as conn:<br>        conn.execute(<br>            &quot;UPDATE todos SET title = ?, done = ? WHERE id = ? AND user_id = ?&quot;,<br>            (todo.title, int(todo.done), todo_id, user.id),<br>        )<br>    return fetch(user, todo_id)<br><br><br>@app.patch(&quot;/todos/{todo_id}&quot;)<br>def update_todo(todo_id: str, patch: TodoPatch, user: User):<br>    fetch(user, todo_id)<br>    # exclude_none: an explicit null means &quot;leave this field unchanged&quot;,<br>    # instead of writing NULL into a NOT NULL column (which would be a 500).<br>    changes = patch.model_dump(exclude_unset=True, exclude_none=True)<br>    if changes:<br>        cols = &quot;, &quot;.join(<br>            f&quot;{c} = ?&quot; for c in changes<br>        )  # keys come from the Pydantic model<br>        with router.connect(user.shard) as conn:<br>            conn.execute(<br>                f&quot;UPDATE todos SET {cols} WHERE id = ? AND user_id = ?&quot;,<br>                (*changes.values(), todo_id, user.id),<br>            )<br>    return fetch(user, todo_id)<br><br><br>@app.delete(&quot;/todos/{todo_id}&quot;, status_code=204)<br>def delete_todo(todo_id: str, user: User):<br>    fetch(user, todo_id)<br>    with router.connect(user.shard) as conn:<br>        conn.execute(<br>            &quot;DELETE FROM todos WHERE id = ? AND user_id = ?&quot;, (todo_id, user.id)<br>        )<br><br><br>@app.get(&quot;/admin/stats&quot;)<br>def stats():<br>    # Scatter-gather: no shard key, so ask every shard and merge.<br>    result = {}<br>    for i in range(len(router.paths)):<br>        with router.connect(i) as conn:<br>            users, todos = conn.execute(<br>                &quot;SELECT COUNT(DISTINCT user_id), COUNT(*) FROM todos&quot;<br>            ).fetchone()<br>        result[f&quot;shard_{i}&quot;] = {&quot;users&quot;: users, &quot;todos&quot;: todos}<br>    return result</pre><h4>One dependency, resolved before every handler</h4><p>Every endpoint that needs a user takes one parameter, user: User. User is just an alias:</p><pre>User = Annotated[CurrentUser, Depends(current_user)]</pre><p>The Depends(current_user) part tells FastAPI: before you run my handler, call current_user first and pass me what it returns. So the user resolves once per request, before any line of the handler body runs.</p><p>current_user does two things:</p><ol><li>Reads the X-User header. If it is missing, it raises 401 and the handler never runs.</li><li>Otherwise, it returns a CurrentUser holding id (the header value) and shard (router.shard_for(id) — the SQLite file that owns this user&#39;s rows).</li></ol><p>By the time a handler runs, user.id and user.shard are already set. The shard is computed once, here, and reused for every database call in that request; no handler recomputes it.</p><p>A few things to note:</p><ul><li><strong>Every user-facing query filters by </strong><strong>user_id</strong>, not just by the to-do id. Routing gets you to the right database, but the filter is what keeps users apart. Without it, a user who happens to share a shard with Alice could read her to-dos by guessing an id. The one deliberate exception is the admin endpoint below, which counts across all users.</li><li>GET /todos lists the current user&#39;s to-dos with a <strong>single-shard query</strong>. No fan-out, no merge. This is the payoff of a good shard key.</li><li>A to-do that belongs to someone else returns 404, not 403, so the API doesn&#39;t even confirm it exists.</li><li>In PATCH, an explicit null is ignored (exclude_none=True), so {&quot;title&quot;: null} leaves the title unchanged. If you&#39;d rather reject it, add a validator that returns 422; either way, never let NULL reach a NOT NULL column.</li><li>I return the shard field in the responses so you can see the routing. You wouldn&#39;t expose this in production.</li></ul><h2>Step 4: A scatter-gather admin endpoint</h2><p>GET /admin/stats has no shard key: it wants numbers from <em>every</em> shard. So it asks all of them and merges the results in the application. That&#39;s scatter-gather, and it&#39;s fine for an occasional admin view. Note that if any one shard is unavailable, this whole endpoint fails; a real system would need to decide whether to return partial results. If a user-facing feature needed it on every request (for example &quot;search all public to-dos&quot;), that would be a sign the shard key doesn&#39;t match your queries. In production this endpoint would also need real admin authorization.</p><h2>Step 5: Run it</h2><pre>fastapi dev</pre><p>To-do ids are random UUIDs, so yours will differ from mine. The commands below print each response and save the id into a shell variable, so you can copy and paste them as-is. Shard numbers are deterministic, so those <em>will</em> match. In the outputs, &lt;alice-1&gt;, &lt;alice-2&gt;, and &lt;bob-1&gt; stand for the ids your server returns.</p><p>Alice creates two to-dos:</p><pre>json_id() { python3 -c &#39;import sys, json; print(json.load(sys.stdin)[&quot;id&quot;])&#39;; }<br> <br>RESP=$(curl -s -X POST localhost:8000/todos -H &#39;X-User: alice&#39; \<br>  -H &#39;Content-Type: application/json&#39; -d &#39;{&quot;title&quot;:&quot;Write the sharding post&quot;}&#39;)<br>echo &quot;$RESP&quot;; ALICE_1=$(echo &quot;$RESP&quot; | json_id)<br> <br>RESP=$(curl -s -X POST localhost:8000/todos -H &#39;X-User: alice&#39; \<br>  -H &#39;Content-Type: application/json&#39; -d &#39;{&quot;title&quot;:&quot;Review pull requests&quot;}&#39;)<br>echo &quot;$RESP&quot;; ALICE_2=$(echo &quot;$RESP&quot; | json_id)</pre><pre>{&quot;id&quot;:&quot;&lt;alice-1&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Write the sharding post&quot;,&quot;done&quot;:false,&quot;shard&quot;:3}<br>{&quot;id&quot;:&quot;&lt;alice-2&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Review pull requests&quot;,&quot;done&quot;:false,&quot;shard&quot;:3}</pre><p>Both landed on shard_3. Now Bob:</p><pre>curl -s -X POST localhost:8000/todos -H &#39;X-User: bob&#39; \<br>  -H &#39;Content-Type: application/json&#39; -d &#39;{&quot;title&quot;:&quot;Plan the sprint&quot;}&#39;</pre><pre>{&quot;id&quot;:&quot;&lt;bob-1&gt;&quot;,&quot;user_id&quot;:&quot;bob&quot;,&quot;title&quot;:&quot;Plan the sprint&quot;,&quot;done&quot;:false,&quot;shard&quot;:2}</pre><p>Bob lives on shard_2. Because the hash is stable, alice maps to shard 3 and bob to shard 2 every time, on every machine, so your shard numbers will match mine.</p><p>Alice marks her first to-do as done with PATCH:</p><pre>curl -s -X PATCH localhost:8000/todos/$ALICE_1 \<br>  -H &#39;X-User: alice&#39; -H &#39;Content-Type: application/json&#39; -d &#39;{&quot;done&quot;:true}&#39;</pre><pre>{&quot;id&quot;:&quot;&lt;alice-1&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Write the sharding post&quot;,&quot;done&quot;:true,&quot;shard&quot;:3}</pre><p>An explicit null is ignored, so the to-do comes back unchanged instead of failing:</p><pre>curl -s -X PATCH localhost:8000/todos/$ALICE_1 \<br>  -H &#39;X-User: alice&#39; -H &#39;Content-Type: application/json&#39; -d &#39;{&quot;title&quot;:null}&#39;</pre><pre>{&quot;id&quot;:&quot;&lt;alice-1&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Write the sharding post&quot;,&quot;done&quot;:true,&quot;shard&quot;:3}</pre><p>Each user lists only their own to-dos, from their own shard:</p><pre>curl -s localhost:8000/todos -H &#39;X-User: alice&#39;<br>curl -s localhost:8000/todos -H &#39;X-User: bob&#39;</pre><pre>[{&quot;id&quot;:&quot;&lt;alice-1&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Write the sharding post&quot;,&quot;done&quot;:true,&quot;shard&quot;:3},{&quot;id&quot;:&quot;&lt;alice-2&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Review pull requests&quot;,&quot;done&quot;:false,&quot;shard&quot;:3}]<br>[{&quot;id&quot;:&quot;&lt;bob-1&gt;&quot;,&quot;user_id&quot;:&quot;bob&quot;,&quot;title&quot;:&quot;Plan the sprint&quot;,&quot;done&quot;:false,&quot;shard&quot;:2}]</pre><p>Bob can’t read Alice’s to-do, even with its id, and a request with no user is rejected:</p><pre>curl -s -w &#39; %{http_code}&#39; localhost:8000/todos/$ALICE_1 -H &#39;X-User: bob&#39;<br>curl -s -w &#39; %{http_code}&#39; localhost:8000/todos</pre><pre>{&quot;detail&quot;:&quot;To-do not found&quot;} 404<br>{&quot;detail&quot;:&quot;Missing X-User header&quot;} 401</pre><p>Carol, Dave, and Erin each add one to-do:</p><pre>for u in carol dave erin; do<br>  curl -s -X POST localhost:8000/todos -H &quot;X-User: $u&quot; \<br>    -H &#39;Content-Type: application/json&#39; -d &#39;{&quot;title&quot;:&quot;Try the sharded API&quot;}&#39;; echo<br>done</pre><pre>{&quot;id&quot;:&quot;&lt;carol-1&gt;&quot;,&quot;user_id&quot;:&quot;carol&quot;,&quot;title&quot;:&quot;Try the sharded API&quot;,&quot;done&quot;:false,&quot;shard&quot;:2}<br>{&quot;id&quot;:&quot;&lt;dave-1&gt;&quot;,&quot;user_id&quot;:&quot;dave&quot;,&quot;title&quot;:&quot;Try the sharded API&quot;,&quot;done&quot;:false,&quot;shard&quot;:3}<br>{&quot;id&quot;:&quot;&lt;erin-1&gt;&quot;,&quot;user_id&quot;:&quot;erin&quot;,&quot;title&quot;:&quot;Try the sharded API&quot;,&quot;done&quot;:false,&quot;shard&quot;:3}</pre><p>Now the admin endpoint gathers counts from every shard:</p><pre>curl -s localhost:8000/admin/stats</pre><pre>{&quot;shard_0&quot;:{&quot;users&quot;:0,&quot;todos&quot;:0},&quot;shard_1&quot;:{&quot;users&quot;:0,&quot;todos&quot;:0},&quot;shard_2&quot;:{&quot;users&quot;:2,&quot;todos&quot;:2},&quot;shard_3&quot;:{&quot;users&quot;:3,&quot;todos&quot;:4}}</pre><p>Look at that: with only 5 users, two shards are empty and shard_3 has most of the data. Hashing only balances well with <strong>many</strong> keys. With thousands of users the counts even out, but this is a good reminder that small samples, and a few very active users, can still create hot shards.</p><p>Finally, replace Alice’s first to-do with PUT, then delete it:</p><pre>curl -s -X PUT localhost:8000/todos/$ALICE_1 \<br>  -H &#39;X-User: alice&#39; -H &#39;Content-Type: application/json&#39; \<br>  -d &#39;{&quot;title&quot;:&quot;Publish the sharding post&quot;,&quot;done&quot;:false}&#39;<br>echo<br>curl -s -o /dev/null -w &#39;%{http_code}\n&#39; -X DELETE \<br>  localhost:8000/todos/$ALICE_1 -H &#39;X-User: alice&#39;<br>curl -s localhost:8000/admin/stats</pre><pre>{&quot;id&quot;:&quot;&lt;alice-1&gt;&quot;,&quot;user_id&quot;:&quot;alice&quot;,&quot;title&quot;:&quot;Publish the sharding post&quot;,&quot;done&quot;:false,&quot;shard&quot;:3}<br>204<br>{&quot;shard_0&quot;:{&quot;users&quot;:0,&quot;todos&quot;:0},&quot;shard_1&quot;:{&quot;users&quot;:0,&quot;todos&quot;:0},&quot;shard_2&quot;:{&quot;users&quot;:2,&quot;todos&quot;:2},&quot;shard_3&quot;:{&quot;users&quot;:3,&quot;todos&quot;:3}}</pre><p>shard_3 now holds 3 to-dos: Alice’s second one, plus Dave’s and Erin’s.</p><h2>Step 6: Rebalancing and consistent hashing</h2><p>What happens when 4 shards aren’t enough and we go to 5? With hash(key) % N, changing N changes the shard for most keys. Almost all your data would have to move.</p><blockquote><strong>Warning:</strong><em> </em>Don’t change NUM_SHARDS in the API on existing data. There is no migration, so users would be routed to shards that don&#39;t hold their rows, and their to-dos would seem to vanish (with 5 shards, alice, bob, dave, and erin all move). A real rebalance copies the data, keeps both locations in sync (or briefly pauses writes) during the move, then switches routing.</blockquote><p><strong>Consistent hashing</strong> reduces how much has to move. Shards are placed at many points on a hash ring (virtual nodes), and each key belongs to the next shard clockwise. If you have N shards and add one more, the new shard takes over keys from its neighbors: ideally about 1/(N+1) of the data, its fair share of the new total.</p><p>The ring below is a <strong>standalone measurement</strong>. It is not wired into the to-do API, and it doesn’t move any real rows; it only counts which keys <em>would</em> move.</p><p>Create a consistent_hash.py file:</p><pre># consistent_hash.py<br>import bisect<br>from shards import stable_hash<br><br><br>class ConsistentHashRing:<br>    def __init__(self, nodes: list[str], vnodes: int = 100):<br>        self.vnodes, self.ring = vnodes, []  # sorted list of (hash, node)<br>        for n in nodes:<br>            self.add_node(n)<br><br>    def add_node(self, node: str):<br>        for v in range(self.vnodes):<br>            bisect.insort(self.ring, (stable_hash(f&quot;{node}#{v}&quot;), node))<br><br>    def get_node(self, key: str) -&gt; str:<br>        i = bisect.bisect(self.ring, (stable_hash(key), &quot;&quot;)) % len(self.ring)<br>        return self.ring[i][1]</pre><p>Let’s measure it with 10,000 keys:</p><pre># compare_rebalance.py<br>from consistent_hash import ConsistentHashRing<br>from shards import stable_hash<br><br>keys = [f&quot;user-{i}&quot; for i in range(10_000)]<br>moved_mod = sum(stable_hash(k) % 4 != stable_hash(k) % 5 for k in keys)<br><br>ring = ConsistentHashRing([f&quot;shard_{i}&quot; for i in range(4)])<br>before = {k: ring.get_node(k) for k in keys}<br>ring.add_node(&quot;shard_4&quot;)<br>moved_ring = sum(before[k] != ring.get_node(k) for k in keys)<br><br>print(f&quot;hash % N   (4 -&gt; 5 shards): {moved_mod / len(keys):.1%} of keys moved&quot;)<br>print(f&quot;consistent (4 -&gt; 5 shards): {moved_ring / len(keys):.1%} of keys moved&quot;)</pre><pre>$ python compare_rebalance.py<br>hash % N   (4 -&gt; 5 shards): 79.6% of keys moved<br>consistent (4 -&gt; 5 shards): 18.1% of keys moved</pre><p>Close to the theory. With modulo, about 80% of the data moves. With consistent hashing, N = 4, so the new shard’s fair share is 1/(N+1) = 1/5 = 20%; we measured 18.1%, and the exact figure varies with the number of virtual nodes. Cassandra, whose design was inspired by Amazon’s Dynamo paper, uses consistent hashing with a token ring and virtual nodes. (Amazon DynamoDB, the managed service, distributes items across partitions by hashing the partition key.) Others, like Vitess and Citus, pre-split data into many more logical shards than physical servers and move whole logical shards around. Both ideas aim for the same thing: never rehash everything.</p><h2>When Should You Use Database Sharding?</h2><p>Here is my honest advice: <strong>shard last.</strong> Sharding solves real problems, but it adds complexity forever. Before you shard, try these:</p><ul><li><strong>Vertical scaling.</strong> A bigger instance is the cheapest engineering time you’ll ever buy. Modern servers with hundreds of GB of RAM and fast NVMe go a long way.</li><li><strong>Read replicas.</strong> If most of your load is reads, replicas scale them without touching the write path.</li><li><strong>Caching.</strong> Redis or Memcached in front of hot queries can remove a large share of database load.</li><li><strong>Partitioning.</strong> Native table partitioning (by date, for example) keeps huge tables manageable on one server and makes dropping old data trivial.</li><li><strong>Query and schema work.</strong> Missing indexes, N+1 queries, and oversized rows cause more “we need to shard” conversations than real scale does.</li><li><strong>Distributed databases.</strong> Citus, Vitess, CockroachDB, YugabyteDB, or Aurora Limitless can shard for you, either self-hosted or as a managed service depending on the product, so your app keeps talking to what looks like one database. Cross-shard queries and transactions still cost more, and what each engine supports differs.</li></ul><p>Shard when:</p><ul><li>Write throughput or data size has outgrown the largest practical single node.</li><li>You have a clear shard key that most queries already include (tenant, user, account).</li><li>You need data in specific regions for latency or compliance.</li><li>You need failure isolation between groups of customers.</li></ul><p><strong>Benefits:</strong> horizontal scaling of writes and storage, smaller and faster indexes per shard, smaller blast radius. <strong>Trade-offs:</strong> cross-shard queries and transactions, painful rebalancing, more operational work, and a shard key you’ll live with for years.</p><h2>Conclusion</h2><p>Sharding is simple to describe and hard to run well. The core is just a function, shard = f(key), but that function decides which queries are fast, which transactions are possible, and how painful your next scale-up will be. In this post we built the whole idea in a few files of Python: a stable hash, a router, a logged-in user that picks the shard, to-do CRUD endpoints that each hit one shard, a scatter-gather admin endpoint, and a standalone consistent hashing ring that cuts the share of keys that would move from about 80% to about 20%.</p><p>My recommendation: get the concepts right, pick your shard key with care, and use the alternatives first. When you really do need sharding, you’ll know why, and you’ll know where it will hurt.</p><p><em>If you found this useful, follow me here on </em><a href="https://medium.com/@johnidouglasmarangon"><em>Medium</em></a><em> for more hands-on posts on backend, cloud, and AI engineering. Have you sharded a database in production? Share what worked, and what hurt, in the comments.</em></p><h2>References</h2><ul><li><a href="https://vitess.io/docs/reference/features/sharding/">Vitess documentation: Sharding</a></li><li><a href="https://docs.citusdata.com/en/stable/sharding/data_modeling.html">Citus documentation: Choosing the distribution column</a></li><li><a href="https://www.mongodb.com/docs/manual/sharding/">MongoDB manual: Sharding</a></li><li><a href="https://www.mongodb.com/docs/manual/core/sharding-shard-key/">MongoDB manual: Shard keys</a></li><li><a href="https://www.mongodb.com/docs/manual/core/sharded-cluster-query-router/">MongoDB manual: mongos query routing (targeted vs broadcast operations)</a></li><li><a href="https://www.postgresql.org/docs/current/ddl-partitioning.html">PostgreSQL documentation: Table Partitioning</a></li><li><a href="https://cassandra.apache.org/doc/latest/cassandra/architecture/dynamo.html">Apache Cassandra documentation: Dynamo (consistent hashing and virtual nodes)</a></li><li><a href="https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.Partitions.html">Amazon DynamoDB documentation: Partitions and data distribution</a></li><li><a href="https://docs.python.org/3/reference/datamodel.html#object.__hash__">Python documentation: </a><a href="https://docs.python.org/3/reference/datamodel.html#object.__hash__">object.__hash__ and </a><a href="https://docs.python.org/3/reference/datamodel.html#object.__hash__">PYTHONHASHSEED</a></li><li><a href="https://docs.python.org/3/library/sqlite3.html">Python documentation: sqlite3 (connection context manager)</a></li><li><a href="https://docs.pydantic.dev/latest/concepts/models/">Pydantic documentation: Models and </a><a href="https://docs.pydantic.dev/latest/concepts/models/">model_dump</a></li><li>Karger et al., “Consistent Hashing and Random Trees” (1997)</li></ul><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bf3d145369ff" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Charge Once, Retry Forever: Idempotency Keys in Python with FastAPI and Redis]]></title>
            <link>https://medium.com/@johnidouglasmarangon/charge-once-retry-forever-idempotency-keys-in-python-with-fastapi-and-redis-8c4534137974?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c4534137974</guid>
            <category><![CDATA[fastapi]]></category>
            <category><![CDATA[system-design-interview]]></category>
            <category><![CDATA[system-design-concepts]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Tue, 10 Nov 2026 12:24:23 GMT</pubDate>
            <atom:updated>2026-11-10T12:24:23.670Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Iq4NDuPewZE9-jv6.jpg" /></figure><p><em>A hands-on guide to making your POST endpoints safe to retry (within a time window you choose)</em></p><p>Recently, while working on a mobile application with offline capabilities, I encountered an interesting bug. When users had an unstable internet connection and tried to save records, the API received the same request multiple times, resulting in duplicate rows in the database. The issue happened because the API successfully processed subsequent requests.</p><p>Nothing was broken. Every piece did its job. What we were missing was <strong>idempotency</strong>.</p><p>In this post, I’ll show you what idempotency keys are, how the Idempotency-Key header works, and how to build one in Python with FastAPI and Redis. We’ll build an endpoint that creates a support ticket, which has the same problem: a retried request must not open a second ticket. At the end, I’ll explain what changes when the side effect is a real payment.</p><h4>What is idempotency, and why should you care?</h4><p>An operation is <strong>idempotent</strong> when running it once has the same effect as running it many times. x = 5 is idempotent. x += 5 is not.</p><p>In distributed systems, you <strong>will</strong> get duplicate requests:</p><ul><li><strong>Retries after network timeouts.</strong> The client can’t tell “the server never got it” apart from “the server did it but the response got lost.”</li><li><strong>Double clicks.</strong> A user taps “Pay” twice because the button felt slow.</li><li><strong>At-least-once delivery.</strong> SQS standard queues, Kafka consumers, and most webhook providers can deliver the same message more than once.</li><li><strong>Proxies and client libraries</strong> that retry requests for you.</li></ul><p>You can’t stop duplicates from arriving. You <em>can</em> make them harmless.</p><h4>HTTP method idempotency vs idempotency keys</h4><p>RFC 9110 defines PUT, DELETE, and all the safe methods (GET, HEAD, OPTIONS, and TRACE) as idempotent. Idempotency is about the <strong>intended effect on the server</strong>, not about getting an identical response: a second DELETE /users/42 may return 404 instead of 204, and that’s fine.</p><p>POST and PATCH are <strong>not defined</strong> as idempotent. That doesn’t mean they can never be. A specific PATCH can be idempotent by design. But clients and proxies can’t assume it. POST /tickets usually creates a new ticket every time. That’s where the <strong>idempotency key</strong> comes in: the client attaches a unique ID to the <em>logical operation</em>, and the server uses it to recognize retries.</p><figure><img alt="Image unavailable" src="https://cdn-images-1.medium.com/proxy/1*ix2d5uq8YECBYSuR9bHzMw.png" /></figure><h4>How the Idempotency-Key header works</h4><p>Stripe made this pattern popular. For its v1 API, Stripe saves the status code and body of the first request with a given key <strong>once endpoint execution begins</strong>, and replays it on retries. That includes 500 responses. Requests that fail parameter validation or collide with a concurrent request aren’t saved, so you can retry them. Stripe also errors when a reused key comes with different parameters, and it may prune keys once they’re <strong>at least</strong> 24 hours old.</p><p>The IETF is standardizing the same idea in the Internet-Draft <a href="https://datatracker.ietf.org/doc/draft-ietf-httpapi-idempotency-key-header/"><strong>The Idempotency-Key HTTP Header Field</strong></a> (draft-08, dated 22 October 2026). It’s still a work in progress, so details may change, but the core contract is clear:</p><pre>POST /tickets HTTP/1.1<br>Idempotency-Key: &quot;8e03978e-40d5-43e8-bc93-6894a57f9324&quot;<br>Content-Type: application/json<br> <br>{&quot;title&quot;: &quot;Printer is on fire&quot;}</pre><ul><li>In the draft, the header is a <strong>Structured Field string</strong>, which is why the value is in double quotes. (The demo below simply accepts any non-empty value; more on that later.)</li><li>The client generates a unique key. A UUID or a similar random identifier is recommended.</li><li>The server MAY combine the key with a <strong>fingerprint</strong> of the request payload.</li><li><strong>Missing key</strong> on an endpoint that documents it as required → 400 Bad Request.</li><li><strong>Same key, original request still being processed</strong> → 409 Conflict.</li><li><strong>Same key, different payload</strong> → 422 Unprocessable Content.</li><li>The server defines and <strong>publishes</strong> how long keys are kept.</li></ul><p>Other errors (401, 429, 5xx, …) are left to your API’s documentation. The draft doesn’t say they’re all retryable.</p><p>Now let’s build it.</p><h3>Hands-on: FastAPI + Redis</h3><p>Start by installing the dependencies:</p><pre>python3 -m venv .venv &amp;&amp; source .venv/bin/activate<br>pip install &quot;fastapi[standard]&quot; uvicorn redis &quot;fakeredis[lua]&quot;</pre><p>I’m using fakeredis so you can run the demo without a Redis server. It lives inside one Python process, so it shows the logic but doesn’t protect several workers or machines. In production, every app instance must talk to the <strong>same</strong> Redis: redis.asyncio.from_url(https://medium.com/&quot;redis://localhost:6379&quot;,%20decode_responses=True). The [lua] extra lets fakeredis run the small Lua script we use to finish a request. Using NX and GET together in one SET needs a recent Redis; check the SET command history for your version.</p><h4>The design</h4><p>The classic recipe is “claim the key with SET NX, run the handler, save the response.” It looks atomic, but only the <strong>claim</strong> is atomic. Everything after it can go wrong:</p><ul><li>The worker crashes after the side effect and before it saves the response. The lock expires, the retry runs again, and you get a duplicate.</li><li>The handler takes longer than the lock TTL. A retry claims the key while the first request is still running.</li><li>The slow first request then finishes and overwrites (or deletes) the <em>new</em> owner’s record, unless every write checks a per-attempt owner token.</li><li>“Delete the key on any exception or 5xx so the client can retry” is only safe if you know <strong>nothing</strong> happened. A timeout from a provider usually means you don’t know.</li></ul><p>To keep the demo honest, the ticket lives <strong>inside the idempotency record itself</strong>. There’s one Redis key per user and idempotency key, holding one JSON value that goes through two states:</p><pre>pending    {&quot;status&quot;: &quot;pending&quot;,   &quot;fp&quot;: &quot;&lt;sha256&gt;&quot;, &quot;owner&quot;: &quot;&lt;random id&gt;&quot;}   TTL 30s<br>completed  {&quot;status&quot;: &quot;completed&quot;, &quot;fp&quot;: &quot;&lt;sha256&gt;&quot;, &quot;ticket&quot;: {...}}          TTL 24h</pre><p>The flow:</p><ol><li><strong>Claim</strong> with one command: SET key &lt;pending&gt; NX GET EX 30. NX stores our claim only if the key is new. GET returns what was already there, in the same step, so there’s no gap between “check” and “write”.</li><li>If someone else got there first, look at their record:</li></ol><ul><li>different fingerprint → <strong>422</strong></li><li>still pending → <strong>409</strong> (the original request is still running)</li><li>completed → <strong>replay the stored ticket</strong></li></ul><h4>A reusable dependency for the header</h4><p>Every endpoint that needs idempotency must require the header. In FastAPI, a <strong>dependency</strong> is the most reusable way to do that: you declare it in the endpoint signature with Depends(...), reuse it on any route, and can swap it with FastAPI’s dependency overrides. A decorator would have to work around FastAPI’s signature inspection.</p><p>Create an idempotency.py file:</p><pre># idempotency.py<br>from fastapi import Header, HTTPException<br><br><br>async def require_idempotency_key(<br>    idempotency_key: str | None = Header(default=None),<br>) -&gt; str:<br>    &quot;&quot;&quot;<br>    Reusable dependency: reject requests without an<br>    Idempotency-Key header.<br>    &quot;&quot;&quot;<br>    if idempotency_key is None or not idempotency_key.strip():<br>        raise HTTPException(400, &quot;Idempotency-Key header is required&quot;)<br>    return idempotency_key.strip()</pre><p>A missing or blank header returns 400. Any other value is accepted as an opaque string: no UUID or quoted-string check. That’s a deliberate simplification for this post; the draft recommends that servers publish a key format and validate it. Because the header is declared optional (so we can return our own 400 instead of FastAPI’s 422), the OpenAPI docs will show it as optional. Mention the requirement in your API docs.</p><h4>The ticket endpoint</h4><p>Create an app.py file:</p><pre># app.py<br>import asyncio, hashlib, json, uuid<br><br>import fakeredis<br>from fastapi import Depends, FastAPI, Header, HTTPException<br>from pydantic import BaseModel, Field<br>from redis.exceptions import RedisError<br><br>from idempotency import require_idempotency_key<br><br>app = FastAPI()<br>redis = fakeredis.FakeAsyncRedis(decode_responses=True)  # demo only, see below<br><br>PENDING_TTL = 30  # how long a claim may stay &quot;pending&quot;<br>DONE_TTL = 24 * 3600  # how long the finished ticket (and its key) is kept<br><br># Finish only if the record is still exactly OUR pending claim. One write.<br>FINISH = &quot;&quot;&quot;<br>if redis.call(&#39;GET&#39;, KEYS[1]) == ARGV[1] then<br>  redis.call(&#39;SET&#39;, KEYS[1], ARGV[2], &#39;EX&#39;, ARGV[3])<br>  return 1<br>end<br>return 0<br>&quot;&quot;&quot;<br>finish = redis.register_script(FINISH)<br><br><br>class TicketIn(BaseModel):<br>    title: str = Field(min_length=1, max_length=200)<br><br><br>@app.post(&quot;/tickets&quot;, status_code=201)<br>async def create_ticket(<br>    ticket: TicketIn,<br>    idempotency_key: str = Depends(require_idempotency_key),<br>    x_user_id: str = Header(default=&quot;anonymous&quot;),  # demo: trust the header<br>):<br>    payload = ticket.model_dump()<br>    fp = hashlib.sha256(json.dumps(payload, sort_keys=True).encode()).hexdigest()<br>    # A JSON list keeps the parts apart, even if they contain &quot;:&quot;.<br>    redis_key = &quot;idem:&quot; + json.dumps([x_user_id, &quot;POST&quot;, &quot;/tickets&quot;, idempotency_key])<br>    claim = json.dumps({&quot;status&quot;: &quot;pending&quot;, &quot;fp&quot;: fp, &quot;owner&quot;: str(uuid.uuid4())})<br><br>    try:<br>        # 1. Claim the key, or read whoever got there first, in ONE command.<br>        existing = await redis.set(redis_key, claim, nx=True, get=True, ex=PENDING_TTL)<br>    except RedisError:<br>        raise HTTPException(503, &quot;Outcome unknown; retry with the same Idempotency-Key&quot;)<br><br>    if existing is not None:<br>        record = json.loads(existing)<br>        if record[&quot;fp&quot;] != fp:<br>            raise HTTPException(<br>                422, &quot;Idempotency-Key was already used with a different payload&quot;<br>            )<br>        if record[&quot;status&quot;] == &quot;pending&quot;:<br>            raise HTTPException(<br>                409, &quot;A request with this Idempotency-Key is in progress&quot;<br>            )<br>        return {**record[&quot;ticket&quot;], &quot;replayed&quot;: True}<br><br>    # 2. We own the claim. Simulated slow work so you can see a 409 (demo only).<br>    await asyncio.sleep(1)<br>    new_ticket = {&quot;id&quot;: str(uuid.uuid4()), **payload}<br>    done = json.dumps({&quot;status&quot;: &quot;completed&quot;, &quot;fp&quot;: fp, &quot;ticket&quot;: new_ticket})<br><br>    # 3. The ticket exists only once this conditional write succeeds.<br>    try:<br>        finished = await finish(keys=[redis_key], args=[claim, done, DONE_TTL])<br>    except RedisError:<br>        finished = 0<br>    if not finished:<br>        raise HTTPException(503, &quot;Outcome unknown; retry with the same Idempotency-Key&quot;)<br>    return {**new_ticket, &quot;replayed&quot;: False}</pre><p>The await asyncio.sleep(1) is there <strong>only</strong> so you can send two requests at the same time and see the 409. Remove it in real code.</p><p>Run it:</p><pre>fastapi dev</pre><p>A few details worth pointing out:</p><ul><li><strong>The dependency only checks the header.</strong> The idempotency logic (claim, compare, finish) stays in the endpoint, next to the business code it protects.</li><li><strong>Keys are scoped by user, method, and route.</strong> If Alice and Bob both send ticket-42, they get separate tickets. If query parameters changed what the operation does, they’d belong in the key or the fingerprint too.</li><li><strong>The Redis key is a JSON list.</strong> Since any key value is accepted, a plain user:key string could collide (user a:b with key c vs user a with key b:c). json.dumps([...]) keeps the parts apart.</li><li><strong>The user comes straight from </strong><strong>X-User-Id</strong>, with anonymous as a demo default. Nothing validates it. In a real app, take the identity from your existing authentication middleware, never from a header the client can set.</li><li><strong>The fingerprint uses the validated, canonical payload.</strong> We hash ticket.model_dump() with sorted keys, so whitespace or key order doesn’t cause false 422s.</li><li><strong>Invalid bodies never reach the handler.</strong> FastAPI and Pydantic return 422 for a bad body before our code runs, so nothing is claimed. Note the two different 422s: a validation error, and a key reused with a different payload.</li><li><strong>503 means “we don’t know,” not “it failed.”</strong> The client should retry with the <strong>same</strong> key.</li><li><strong>A stuck claim answers </strong><strong>409 until it expires.</strong> If the process crashes or the request is cancelled after claiming, the pending record stays for up to 30 seconds. After that, a retry can claim the key again.</li><li><strong>A replay isn’t byte-for-byte identical.</strong> We add &quot;replayed&quot;: true so you can see it while debugging (Stripe sends an Idempotent-Replayed response header for the same reason). Clients should rely on the ticket id, not on identical bytes.</li></ul><h3>Trying it with curl</h3><p>The most important rule for clients: <strong>generate the key once per logical operation, and reuse it on every retry.</strong> A new key on each retry defeats the whole thing.</p><p>First, send two requests with the same key <strong>at the same time</strong>. The trailing &amp; runs them in parallel, like a double tap:</p><pre>for i in 1 2; do<br>  curl -s -w &#39; %{http_code}\n&#39; -X POST localhost:8000/tickets \<br>    -H &quot;X-User-Id: alice&quot; \<br>    -H &quot;Idempotency-Key: ticket-42&quot; \<br>    -H &quot;Content-Type: application/json&quot; \<br>    -d &#39;{&quot;title&quot;: &quot;Printer is on fire&quot;}&#39; &amp;<br>done; wait</pre><p>One wins, the other is told the request is still in progress (the order may vary):</p><pre>{&quot;detail&quot;:&quot;A request with this Idempotency-Key is in progress&quot;} 409<br>{&quot;id&quot;:&quot;279acdb3-692e-4bdc-adc7-3338320f3d9c&quot;,&quot;title&quot;:&quot;Printer is on fire&quot;,&quot;replayed&quot;:false} 201</pre><p>Now retry after the first request has finished. This is what a retry after a timeout looks like:</p><pre>curl -s -w &#39; %{http_code}\n&#39; -X POST localhost:8000/tickets \<br>  -H &quot;X-User-Id: alice&quot; \<br>  -H &quot;Idempotency-Key: ticket-42&quot; \<br>  -H &quot;Content-Type: application/json&quot; \<br>  -d &#39;{&quot;title&quot;: &quot;Printer is on fire&quot;}&#39;</pre><pre>{&quot;id&quot;:&quot;279acdb3-692e-4bdc-adc7-3338320f3d9c&quot;,&quot;title&quot;:&quot;Printer is on fire&quot;,&quot;replayed&quot;:true} 201</pre><p>Same ticket. Send the same request as X-User-Id: bob and Bob gets his <strong>own</strong> new ticket, because keys are scoped per user. Reuse Alice’s key with a different title and you get 422; drop the Idempotency-Key header and you get 400. These outputs came from a local run; your UUIDs will be different.</p><p>When the client gets a 409, or no response at all, it should back off and retry with the same key. A 409 means “still working,” not “the operation failed.”</p><h3>Pitfalls I’ve learned the hard way</h3><p><strong>1. Scope keys per user (or tenant).</strong> A global key namespace lets one client replay another client’s response, which is a data leak. The demo trusts X-User-Id to stay short; in production, use the authenticated identity your middleware provides, never a header the client controls.</p><p><strong>2. Decide which outcomes are final.</strong> Validation errors and business rejections are usually final. A 5xx or a timeout is only retryable if you know the side effect didn’t happen. If you don’t know, keep the key, and resolve the outcome (for example, by asking the provider) before allowing a new attempt. Don’t delete the record on every exception.</p><p><strong>3. Deduplication lasts only as long as the TTL.</strong> After the key expires, the same request is treated as new. “Retry forever” in the title is a figure of speech. In practice, make the TTL longer than your clients’ longest realistic retry window, and document it. Stripe’s “at least 24 hours” is a good reference.</p><p><strong>4. Know what Redis does and doesn’t promise.</strong> Each command (and each script) runs atomically on one Redis primary. It doesn’t survive things Redis itself can lose:</p><p><strong>5. Money needs a transactional source of truth.</strong> The demo works because the ticket exists only inside the idempotency record, written by one owner-checked step. A real charge happens in a payment provider, and no Redis or database transaction can include an external API call. For payments:</p><ul><li>Store the idempotency record <strong>in the same database transaction</strong> as your business write, for example, a table with a unique constraint on (user_id, key), holding the request status.</li><li><strong>Pass an idempotency key down to the provider</strong>, so its retries are deduplicated too.</li><li>Treat provider timeouts as <strong>unknown</strong>, and reconcile them before charging again.</li></ul><p>Redis is great as a fast first line of defense. It shouldn’t be the only thing preventing a double charge.</p><p><strong>6. A pending claim is a lease, with lease problems.</strong> The owner check stops a stale request from overwriting a newer claim, but it can’t undo work done outside Redis. Keep the pending TTL longer than your slowest handler (or renew it), and have a recovery plan for records stuck “pending.” For external side effects, that plan usually means a database and the provider’s own idempotency key.</p><h3>Conclusion</h3><p>Duplicate requests aren’t an edge case. They’re how networks behave. Idempotency keys give you a simple contract: <em>the client names the operation, and the server promises that retries of it take effect at most once while the key is remembered.</em></p><p>Start with your most sensitive endpoints (payments, orders, anything that sends email), and your future self on call will thank you.</p><p>If you found this useful, follow me here on <a href="https://medium.com/@johnidouglasmarangon">Medium</a> for more hands-on posts on backend, cloud, and AI engineering. Got a war story about double charges? Tell me in the comments!</p><h3>References</h3><ul><li><a href="https://datatracker.ietf.org/doc/draft-ietf-httpapi-idempotency-key-header/">The Idempotency-Key HTTP Header Field, IETF Internet-Draft (draft-ietf-httpapi-idempotency-key-header)</a></li><li><a href="https://docs.stripe.com/api/idempotent_requests">Stripe API: Idempotent requests</a></li><li><a href="https://www.rfc-editor.org/rfc/rfc9110#name-idempotent-methods">RFC 9110: HTTP Semantics, Idempotent Methods</a></li><li><a href="https://www.rfc-editor.org/rfc/rfc9651">RFC 9651: Structured Field Values for HTTP</a></li><li><a href="https://redis.io/docs/latest/commands/set/">Redis: SET command (NX, GET, EX)</a></li><li><a href="https://redis.io/docs/latest/develop/programmability/eval-intro/">Redis: Scripting with Lua</a></li><li><a href="https://redis.io/docs/latest/develop/reference/eviction/">Redis: Key eviction</a></li><li><a href="https://redis.io/docs/latest/operate/oss_and_stack/management/persistence/">Redis: Persistence</a></li><li><a href="https://redis.io/docs/latest/operate/oss_and_stack/management/replication/">Redis: Replication</a></li><li><a href="https://fastapi.tiangolo.com/tutorial/header-params/">FastAPI: Header parameters</a></li><li><a href="https://fastapi.tiangolo.com/tutorial/dependencies/">FastAPI: Dependencies</a></li><li><a href="https://fakeredis.readthedocs.io/">fakeredis documentation</a></li></ul><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c4534137974" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Sessions: Common Table Expressions, from Basics to Recursion]]></title>
            <link>https://medium.com/@johnidouglasmarangon/sql-sessions-common-table-expressions-from-basics-to-recursion-f383b8a716e7?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/f383b8a716e7</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[cte]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Mon, 09 Nov 2026 13:55:33 GMT</pubDate>
            <atom:updated>2026-11-09T13:55:33.918Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/800/0*ykDASB5Tg2wfhRpk.jpeg" /></figure><p>I was working on a project where we needed to build non-trivial SQL queries involving nested subqueries, complex joins between tables, and repeated GROUP BY operations because the same results were needed in multiple places. Before long, the entire query turned into a wall of parentheses that was difficult to read, understand, and maintain.</p><p>A Common Table Expression (CTE) is an SQL feature that helps untangle exactly this kind of complexity. It allows you to define a named query and then reference it as if it were a table.</p><p>In this post, I’ll share a few practical examples of how to use CTEs with SQLite. We’ll build a small online store database, identify its biggest customers, compare monthly revenue, and work our way up to recursive queries that traverse an organizational chart.</p><p>The SQL below was executed statement by statement in the <strong>SQLite 3.46.1 CLI</strong>. Transcripts show the shell’s real prompts and output.</p><h3>Get the SQLite CLI ready</h3><p>You need the <strong>command-line program</strong>, not just the SQLite library that happens to come with Python. Use a current SQLite release; the complete walkthrough needs <strong>SQLite 3.25.0 or later</strong> because the analysis section uses window functions. CTEs themselves, including recursion, arrived in 3.8.3.</p><p>Choose the installation instructions for your system. These are OS terminal commands, not commands to paste at a sqlite&gt; prompt.</p><p><strong>Ubuntu / Debian:</strong></p><pre>sudo apt update<br>sudo apt install sqlite3</pre><p><strong>Fedora:</strong></p><pre>sudo dnf install sqlite</pre><p><strong>macOS:</strong> First check whether sqlite3 is already available. If it is missing or too old, install SQLite with <a href="https://formulae.brew.sh/formula/sqlite">Homebrew</a>:</p><pre>brew install sqlite</pre><p>Homebrew’s package may not replace Apple’s system executable on your PATH. To use the Homebrew version explicitly, launch it with &quot;$(brew --prefix sqlite)/bin/sqlite3&quot; store.db rather than the shorter command below.</p><p><strong>Windows:</strong> Download the <strong>sqlite-tools</strong> ZIP for your architecture from the <a href="https://sqlite.org/download.html">official SQLite download page</a>. Extract it into a folder such as C:\sqlite, then add the folder containing sqlite3.exe to your user PATH and reopen PowerShell. Get the tools bundle, not just the DLL. If you prefer not to change PATH, use the executable&#39;s full path; PowerShell also needs &amp; before a quoted executable path.</p><p>On any platform, check the executable you will actually run:</p><pre>sqlite3 --version</pre><p>The captured session here uses <strong>SQLite 3.46.1</strong>. That’s a pinned test version, not a claim that it is the newest release. Installation commands for the other platforms are instructions, not installation transcripts from those systems.</p><h4>Open a persistent database</h4><p>Work in a new, empty practice directory. From your OS terminal, start:</p><pre>sqlite3 store.db</pre><p>The filename matters: store.db is persistent. With no filename, SQLite normally<strong> opens a transient in-memory database</strong> instead. SQLite creates the named database as needed; there is no database server to start.</p><p>Here is the actual opening of the tested session, followed by three CLI settings entered separately:</p><pre>SQLite version 3.46.1 2024-08-13 09:16:08<br>Enter &quot;.help&quot; for usage hints.<br><br>sqlite&gt; .headers on<br>sqlite&gt; .mode column<br>sqlite&gt; .nullvalue NULL</pre><p>.headers on prints column names. .mode column aligns the results into columns. .nullvalue NULL makes a missing value visible instead of printing an empty field. These are <strong>shell dot-commands</strong>, not SQL, so don&#39;t add semicolons to them.</p><p>A couple of REPL details will save you unnecessary debugging:</p><ul><li>sqlite&gt; means the shell is ready for a new statement. The continuation prompt ends in ...&gt; and means it is still collecting the current statement.</li><li>This CLI version also shows parenthesis depth, such as (x1...&gt;. That is a real continuation prompt, not an error. Newer versions may use different prompts or spacing.</li><li>SQL finishes at the terminating semicolon. A multi-line WITH query, including its final SELECT, is <strong>one statement</strong>. Don&#39;t put a semicolon between the CTE and the outer query.</li><li>In a <strong>transcript</strong>, everything after the prompt is input. When a prompt-free SQL block is supplied, copy that block; don’t paste the prompts or the result rows. A SQL-only block and its matching transcript are two views of the <strong>same execution</strong>, not instructions to run it twice.</li></ul><h3>What a CTE actually is</h3><p>A CTE is a named query result scoped to a single statement. Define it with WITH, give it a name and optionally column names, and reference it from the query that follows—in FROM, in a JOIN, or from another CTE.</p><p>This is the basic shape. <strong>Illustrative pseudocode only: </strong><strong>SELECT ... is a placeholder, not runnable SQL. Do not paste this sketch into the REPL.</strong></p><pre>-- NON-RUNNABLE SYNTAX SKETCH<br>WITH cte_name AS (<br>    SELECT ...<br>)<br>SELECT *<br>FROM cte_name;</pre><p>Read it as “name this result, then use it below.” That is a <strong>logical dependency and reading order</strong>, not a promise about physical execution. SQL is declarative: the planner may inline a CTE, push filters around where valid, or choose a temporary intermediate result. Naming a query doesn’t turn SQL into a Python script that executes top to bottom.</p><p>Three things are worth fixing in your mind early:</p><ul><li>The CTE name lives for <strong>one statement</strong>. The database file persists; that query-local name does not.</li><li>You can reference the same CTE more than once within that statement, without copying its definition.</li><li>A CTE does not create a persistent schema table. But its implementation may use memory or temporary disk storage, including a materialized result that spills to disk. “Not a permanent table” does not mean “never touches disk.”</li></ul><h3>Build the sample database</h3><p>Everything below uses three tables: customers, orders, and employees. Start in a fresh practice directory with a new store.db. We deliberately haven&#39;t hidden duplicate setup behind IF NOT EXISTS or conflict-ignoring inserts, so a second run of the same statements fails with “already exists” or primary-key errors.</p><h4>Prompt-free setup SQL</h4><p>Copy this entire block into the open sqlite&gt; session. It enables foreign keys, creates the three tables, loads the sample rows, and adds the manager_id index used later for hierarchy lookups:</p><pre>PRAGMA foreign_keys = ON;<br> <br>CREATE TABLE customers (<br>    customer_id INT PRIMARY KEY,<br>    name        TEXT NOT NULL,<br>    country     TEXT NOT NULL,<br>    signup_date DATE NOT NULL<br>);<br> <br>CREATE TABLE orders (<br>    order_id    INT PRIMARY KEY,<br>    customer_id INT NOT NULL REFERENCES customers(customer_id),<br>    order_date  DATE NOT NULL,<br>    amount      NUMERIC(10,2) NOT NULL,<br>    status      TEXT NOT NULL      -- &#39;paid&#39; or &#39;refunded&#39;<br>);<br> <br>CREATE TABLE employees (<br>    employee_id INT PRIMARY KEY,<br>    name        TEXT NOT NULL,<br>    manager_id  INT REFERENCES employees(employee_id),  -- NULL for the CEO<br>    department  TEXT NOT NULL,<br>    salary      NUMERIC(10,2) NOT NULL<br>);<br> <br>INSERT INTO customers (customer_id, name, country, signup_date) VALUES<br>(1, &#39;Ana Costa&#39;,    &#39;Brazil&#39;,   &#39;2025-11-03&#39;),<br>(2, &#39;Bruno Alves&#39;,  &#39;Brazil&#39;,   &#39;2026-01-15&#39;),<br>(3, &#39;Carla Dias&#39;,   &#39;Portugal&#39;, &#39;2025-09-20&#39;),<br>(4, &#39;Diego Mendes&#39;, &#39;Brazil&#39;,   &#39;2026-02-10&#39;),<br>(5, &#39;Elena Rossi&#39;,  &#39;Italy&#39;,    &#39;2025-12-01&#39;),<br>(6, &#39;Felipe Souza&#39;, &#39;Brazil&#39;,   &#39;2026-03-05&#39;);<br> <br>INSERT INTO orders (order_id, customer_id, order_date, amount, status) VALUES<br>(1,  1, &#39;2026-01-05&#39;, 120.00, &#39;paid&#39;),<br>(2,  1, &#39;2026-01-22&#39;, 260.00, &#39;paid&#39;),<br>(3,  3, &#39;2026-01-28&#39;, 540.00, &#39;paid&#39;),<br>(4,  2, &#39;2026-02-02&#39;,  90.00, &#39;paid&#39;),<br>(5,  1, &#39;2026-02-14&#39;, 310.00, &#39;paid&#39;),<br>(6,  3, &#39;2026-02-18&#39;, 220.00, &#39;refunded&#39;),<br>(7,  5, &#39;2026-02-25&#39;, 880.00, &#39;paid&#39;),<br>(8,  2, &#39;2026-03-03&#39;, 150.00, &#39;paid&#39;),<br>(9,  4, &#39;2026-03-11&#39;, 420.00, &#39;paid&#39;),<br>(10, 1, &#39;2026-03-19&#39;, 190.00, &#39;paid&#39;),<br>(11, 5, &#39;2026-03-27&#39;, 300.00, &#39;paid&#39;),<br>(12, 3, &#39;2026-03-30&#39;, 600.00, &#39;paid&#39;),<br>(13, 6, &#39;2026-03-31&#39;,  75.00, &#39;paid&#39;);<br> <br>INSERT INTO employees (employee_id, name, manager_id, department, salary) VALUES<br>(1, &#39;Alice Martins&#39;, NULL, &#39;Executive&#39;,   20000),<br>(2, &#39;Bruno Carter&#39;,  1,    &#39;Engineering&#39;, 12000),<br>(3, &#39;Carol Nunes&#39;,   1,    &#39;Sales&#39;,       11000),<br>(4, &#39;Dan Oliveira&#39;,  2,    &#39;Engineering&#39;,  8000),<br>(5, &#39;Erin Prado&#39;,    2,    &#39;Engineering&#39;,  8500),<br>(6, &#39;Frank Reis&#39;,    3,    &#39;Sales&#39;,        7000),<br>(7, &#39;Grace Teles&#39;,   4,    &#39;Engineering&#39;,  6000);<br> <br>CREATE INDEX idx_employees_manager_id ON employees(manager_id);</pre><p>After this block executes successfully, let’s inspect the database to verify that the tables and sample data were created correctly.</p><p>Run the following commands sequentially in the SQLite REPL and review the results:</p><pre>sqlite&gt; .tables<br><br>sqlite&gt; .schema</pre><pre>SELECT * FROM customers;</pre><p>Now we’re ready to get started!</p><h3>Your first CTE</h3><p>Here’s a common need: <strong>find customers who have spent more than 500 in paid orders</strong>. Without a CTE, you could write a derived table or use HAVING. With one, compute per-customer totals, name them customer_totals, then filter and join against that name.</p><p>Copyable SQL:</p><pre>WITH customer_totals AS (<br>    SELECT customer_id, SUM(amount) AS total_spent<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY customer_id<br>)<br>SELECT c.name, ct.total_spent<br>FROM customer_totals ct<br>JOIN customers c ON c.customer_id = ct.customer_id<br>WHERE ct.total_spent &gt; 500<br>ORDER BY ct.total_spent DESC;</pre><p>Actual REPL execution:</p><pre>sqlite&gt; WITH customer_totals AS (<br>(x1...&gt;     SELECT customer_id, SUM(amount) AS total_spent<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY customer_id<br>(x1...&gt; )<br>   ...&gt; SELECT c.name, ct.total_spent<br>   ...&gt; FROM customer_totals ct<br>   ...&gt; JOIN customers c ON c.customer_id = ct.customer_id<br>   ...&gt; WHERE ct.total_spent &gt; 500<br>   ...&gt; ORDER BY ct.total_spent DESC;<br>name         total_spent<br>-----------  -----------<br>Elena Rossi  1180       <br>Carla Dias   1140       <br>Ana Costa    880        <br>sqlite&gt;</pre><p>The CTE handles aggregation; the outer query handles joining customer names and filtering the totals. Each part does one job, and you can read the second part without keeping the details of the first in your head.</p><p>Carla is the detail to notice. Her orders are 540, 220, and 600, but her total is <strong>1140</strong>, not 1360. The WHERE status = &#39;paid&#39; inside the CTE removes the refunded 220 before aggregation, logically speaking. The business rule now lives in one obvious place instead of being threaded through a larger query.</p><p>The CTE name isn’t a new table in store.db. Once that semicolon finishes the statement, customer_totals is gone. Every later example that needs it defines it again.</p><h3>CTE vs. subquery vs. derived table</h3><p>A fair question at this point: isn’t this just a subquery with extra steps? For this example, these three formulations produce the same rows. Their readability differs.</p><p>A <strong>derived table</strong>, which is a subquery in the FROM clause, does the same work inline:</p><pre>SELECT c.name, ct.total_spent<br>FROM (<br>    SELECT customer_id, SUM(amount) AS total_spent<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY customer_id<br>) AS ct<br>JOIN customers c ON c.customer_id = ct.customer_id<br>WHERE ct.total_spent &gt; 500<br>ORDER BY ct.total_spent DESC;</pre><p>And the actual shell agrees:</p><pre>sqlite&gt; SELECT c.name, ct.total_spent<br>   ...&gt; FROM (<br>(x1...&gt;     SELECT customer_id, SUM(amount) AS total_spent<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY customer_id<br>(x1...&gt; ) AS ct<br>   ...&gt; JOIN customers c ON c.customer_id = ct.customer_id<br>   ...&gt; WHERE ct.total_spent &gt; 500<br>   ...&gt; ORDER BY ct.total_spent DESC;<br>name         total_spent<br>-----------  -----------<br>Elena Rossi  1180       <br>Carla Dias   1140       <br>Ana Costa    880        <br>sqlite&gt;</pre><p>A <strong>correlated scalar subquery</strong> refers to the current outer customer. Here the same aggregation is written once for display and once for filtering:</p><pre>SELECT c.name,<br>       (SELECT SUM(amount) FROM orders o<br>        WHERE o.customer_id = c.customer_id AND o.status = &#39;paid&#39;) AS total_spent<br>FROM customers c<br>WHERE (SELECT SUM(amount) FROM orders o<br>       WHERE o.customer_id = c.customer_id AND o.status = &#39;paid&#39;) &gt; 500<br>ORDER BY total_spent DESC;</pre><p>Run it as its own statement:</p><pre>sqlite&gt; SELECT c.name,<br>   ...&gt;        (SELECT SUM(amount) FROM orders o<br>(x1...&gt;         WHERE o.customer_id = c.customer_id AND o.status = &#39;paid&#39;) AS total_spent<br>   ...&gt; FROM customers c<br>   ...&gt; WHERE (SELECT SUM(amount) FROM orders o<br>(x1...&gt;        WHERE o.customer_id = c.customer_id AND o.status = &#39;paid&#39;) &gt; 500<br>   ...&gt; ORDER BY total_spent DESC;<br>name         total_spent<br>-----------  -----------<br>Elena Rossi  1180       <br>Carla Dias   1140       <br>Ana Costa    880        <br>sqlite&gt;</pre><p>Same customers, same totals. The correlated version describes a per-customer lookup, but don’t infer a fixed number of scans from the SQL text alone: optimizers can transform queries.</p><p>The CTE version wins for this walkthrough because it names a meaningful intermediate result: “totals, then customers above the threshold.” If a query needs that named result in multiple places, a CTE also avoids repeating the definition. Derived tables are subqueries too; we’re comparing ways to organize the same calculation, not three unrelated features.</p><p>The rule of thumb: <strong>reach for a CTE when a query has distinct stages</strong>, when you’d otherwise repeat a subquery, or when the nesting has gotten deep enough to hurt. <strong>For a one-line scalar lookup, a plain subquery is still fine</strong>. Neither spelling is automatically faster.</p><h3>Reusing a CTE within one statement</h3><p>Suppose you want customers who spent more than the average paid-order customer, with that average shown beside each row. The output and the comparison both need the per-customer totals. Define them once, and reference the CTE three times:</p><pre>WITH customer_totals AS (<br>    SELECT customer_id, SUM(amount) AS total_spent<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY customer_id<br>)<br>SELECT c.name,<br>       ct.total_spent,<br>       ROUND((SELECT AVG(total_spent) FROM customer_totals), 2) AS avg_spent<br>FROM customer_totals ct<br>JOIN customers c ON c.customer_id = ct.customer_id<br>WHERE ct.total_spent &gt; (SELECT AVG(total_spent) FROM customer_totals)<br>ORDER BY ct.total_spent DESC;</pre><p>Here’s the actual result:</p><pre>sqlite&gt; WITH customer_totals AS (<br>(x1...&gt;     SELECT customer_id, SUM(amount) AS total_spent<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY customer_id<br>(x1...&gt; )<br>   ...&gt; SELECT c.name,<br>   ...&gt;        ct.total_spent,<br>   ...&gt;        ROUND((SELECT AVG(total_spent) FROM customer_totals), 2) AS avg_spent<br>   ...&gt; FROM customer_totals ct<br>   ...&gt; JOIN customers c ON c.customer_id = ct.customer_id<br>   ...&gt; WHERE ct.total_spent &gt; (SELECT AVG(total_spent) FROM customer_totals)<br>   ...&gt; ORDER BY ct.total_spent DESC;<br>name         total_spent  avg_spent<br>-----------  -----------  ---------<br>Elena Rossi  1180         655.83   <br>Carla Dias   1140         655.83   <br>Ana Costa    880          655.83   <br>sqlite&gt;</pre><p>customer_totals appears in the outer FROM, the scalar subquery in the SELECT list, and the scalar subquery in WHERE. You didn&#39;t have to copy the aggregation three times. That is definition reuse, not a universal promise that the engine computes it once and caches it.</p><p>The population matters: <strong>655.83 is the rounded average across customers with at least one paid order</strong>. The CTE contains one row for each such customer. With this seed, that happens to be all six customers: 3935 divided by 6 is about 655.8333. Three customers clear the unrounded average; rounding is only for display.</p><p>If a customer had no orders, or only refunded ones, that customer would be absent from this CTE. To answer “average across every registered customer, counting non-buyers as zero,” build the population from customers, left-join the paid totals, and use COALESCE for the missing totals. That&#39;s a different business question, not merely a formatting adjustment.</p><h3>Multiple CTEs in one query</h3><p>One WITH can define several CTEs, separated by commas. Give each stage a useful name, then build on the earlier stages. Here we&#39;ll compute monthly paid revenue, compute the average observed month, and keep the months above that average:</p><pre>WITH <br>monthly AS (<br>    SELECT strftime(&#39;%Y-%m&#39;, order_date) AS month,<br>           SUM(amount) AS revenue<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY strftime(&#39;%Y-%m&#39;, order_date)<br>),<br>avg_month AS (<br>    SELECT AVG(revenue) AS avg_revenue<br>    FROM monthly          -- a CTE reading from the CTE above it<br>)<br>SELECT m.month,<br>       m.revenue,<br>       ROUND(a.avg_revenue, 2) AS avg_revenue<br>FROM monthly m<br>CROSS JOIN avg_month a<br>WHERE m.revenue &gt; a.avg_revenue<br>ORDER BY m.month;</pre><p>Actual execution:</p><pre>sqlite&gt; WITH monthly AS (<br>(x1...&gt;     SELECT strftime(&#39;%Y-%m&#39;, order_date) AS month,<br>(x1...&gt;            SUM(amount) AS revenue<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY strftime(&#39;%Y-%m&#39;, order_date)<br>(x1...&gt; ),<br>   ...&gt; avg_month AS (<br>(x1...&gt;     SELECT AVG(revenue) AS avg_revenue<br>(x1...&gt;     FROM monthly          -- a CTE reading from the CTE above it<br>(x1...&gt; )<br>   ...&gt; SELECT m.month,<br>   ...&gt;        m.revenue,<br>   ...&gt;        ROUND(a.avg_revenue, 2) AS avg_revenue<br>   ...&gt; FROM monthly m<br>   ...&gt; CROSS JOIN avg_month a<br>   ...&gt; WHERE m.revenue &gt; a.avg_revenue<br>   ...&gt; ORDER BY m.month;<br>month    revenue  avg_revenue<br>-------  -------  -----------<br>2026-03  1735     1311.67    <br>sqlite&gt;</pre><p>WITH appears once. A comma introduces avg_month, which reads from monthly. The CROSS JOIN attaches its one-row average to each monthly row so the final filter can compare them.</p><p>For portable, easy-to-follow dependency chains, put a CTE before the siblings that use it. Forward-reference rules differ by engine; there is no need to rely on them here. The comma-separated definitions still form <strong>one statement</strong>, not separate queries that leave intermediate tables behind.</p><p>The three observed monthly revenues are 920, 1280, and 1735. Their average rounds to 1311.67, so only March clears the line. Months with no paid orders don’t appear at all, and therefore aren’t counted as zero-revenue months in this average.</p><p>The month key is deliberately SQLite-native: strftime(&#39;%Y-%m&#39;, order_date). Our text dates use the ISO layout SQLite&#39;s date functions understand, and the resulting zero-padded year-month keys sort chronologically. PostgreSQL commonly uses TO_CHAR for this job, MySQL uses DATE_FORMAT, and SQL Server can use FORMAT. Those function names and format tokens are engine-specific; the SQLite statements here do not depend on TO_CHAR.</p><h3>Recursive CTEs</h3><p>Everything so far could be written with ordinary subqueries, sometimes more awkwardly. Recursion adds something a fixed nesting of subqueries doesn’t: the ability to keep following relationships to an unknown depth.</p><p>A recursive CTE refers to its own name. The usual structure has two parts:</p><ol><li>The <strong>anchor</strong> produces the starting row or rows.</li><li>The <strong>recursive member</strong> uses rows already reached to produce more rows, until there is nothing new to process.</li></ol><p>SQLite supports <strong>UNION ALL or </strong><strong>UNION</strong> between those parts. We&#39;ll use UNION ALL, which keeps every generated row. UNION discards duplicate complete rows; that can help terminate some graph traversals, but it adds duplicate-tracking work and isn&#39;t a general substitute for cycle detection.</p><h4>Start with a sequence</h4><p>Let’s generate the integers from 1 to 10:</p><pre>WITH RECURSIVE numbers(n) AS (<br>    SELECT 1                                -- anchor: start at 1<br>    UNION ALL<br>    SELECT n + 1 FROM numbers WHERE n &lt; 10  -- recursive: add 1 until 10<br>)<br>SELECT n FROM numbers ORDER BY n;</pre><p>Actual shell session:</p><pre>sqlite&gt; WITH RECURSIVE numbers(n) AS (<br>(x1...&gt;     SELECT 1                              -- anchor: start at 1<br>(x1...&gt;     UNION ALL<br>(x1...&gt;     SELECT n + 1 FROM numbers WHERE n &lt; 10  -- recursive: add 1 until 10<br>(x1...&gt; )<br>   ...&gt; SELECT n FROM numbers ORDER BY n;<br>n <br>--<br>1 <br>2 <br>3 <br>4 <br>5 <br>6 <br>7 <br>8 <br>9 <br>10<br>sqlite&gt;</pre><p>The anchor emits 1. The recursive member adds one while n &lt; 10: 1 produces 2, 2 produces 3, and so on. When it processes 10, the predicate fails and there is no next row.</p><p>In SQLite, a useful model is a work queue: add anchor rows, take one queued row, run the recursive member against it, and queue the generated rows. Don’t assume every engine uses exactly the same internal implementation. Also, don’t confuse traversal order with output order. The final ORDER BY n explicitly asks for ascending results.</p><p>The stop condition is not optional decoration. Without a terminating rule, a recursive query can keep producing work indefinitely. For a sequence, a numeric bound is straightforward. For a tree or graph, “there are no more children” may suffice only if the data really is acyclic; we’ll add a visited-path guard rather than trusting that assumption.</p><p>SQLite accepts recursive CTEs without the RECURSIVE keyword, but we&#39;ll keep it because it tells the reader what is happening. PostgreSQL and MySQL 8.0+ require it. SQL Server and Oracle do not use the keyword. Don&#39;t remove it from SQLite just because you can; explicit intent is useful.</p><h3>Walking a hierarchy</h3><p>The sequence is a warm-up. The real payoff is hierarchical data: org charts, category trees, threaded comments, bill-of-materials — anything where a row points to its parent. Our employees table does exactly that with manager_id.</p><p>To print the org chart, start from employees whose manager is NULL, then repeatedly find their direct reports. We&#39;ll carry two paths: a readable name path for display and a comma-delimited employee-ID path for cycle protection.</p><pre>WITH RECURSIVE org AS (<br>    SELECT employee_id, name, manager_id,<br>           0 AS level,<br>           name AS path,<br>           &#39;,&#39; || employee_id || &#39;,&#39; AS visited<br>    FROM employees<br>    WHERE manager_id IS NULL          -- anchor: the top of the tree<br>    UNION ALL<br>    SELECT e.employee_id, e.name, e.manager_id,<br>           o.level + 1,<br>           o.path || &#39; &gt; &#39; || e.name,<br>           o.visited || e.employee_id || &#39;,&#39;<br>    FROM employees e<br>    JOIN org o ON e.manager_id = o.employee_id<br>    WHERE instr(o.visited, &#39;,&#39; || e.employee_id || &#39;,&#39;) = 0<br>)<br>SELECT level, name, path<br>FROM org<br>ORDER BY path;</pre><p>The actual output is:</p><pre>sqlite&gt; WITH RECURSIVE org AS (<br>(x1...&gt;     SELECT employee_id, name, manager_id,<br>(x1...&gt;            0 AS level,<br>(x1...&gt;            name AS path,<br>(x1...&gt;            &#39;,&#39; || employee_id || &#39;,&#39; AS visited<br>(x1...&gt;     FROM employees<br>(x1...&gt;     WHERE manager_id IS NULL          -- anchor: the top of the tree<br>(x1...&gt;     UNION ALL<br>(x1...&gt;     SELECT e.employee_id, e.name, e.manager_id,<br>(x1...&gt;            o.level + 1,<br>(x1...&gt;            o.path || &#39; &gt; &#39; || e.name,<br>(x1...&gt;            o.visited || e.employee_id || &#39;,&#39;<br>(x1...&gt;     FROM employees e<br>(x1...&gt;     JOIN org o ON e.manager_id = o.employee_id<br>(x1...&gt;     WHERE instr(o.visited, &#39;,&#39; || e.employee_id || &#39;,&#39;) = 0<br>(x1...&gt; )<br>   ...&gt; SELECT level, name, path<br>   ...&gt; FROM org<br>   ...&gt; ORDER BY path;<br>level  name           path                                                     <br>-----  -------------  ---------------------------------------------------------<br>0      Alice Martins  Alice Martins                                            <br>1      Bruno Carter   Alice Martins &gt; Bruno Carter                             <br>2      Dan Oliveira   Alice Martins &gt; Bruno Carter &gt; Dan Oliveira              <br>3      Grace Teles    Alice Martins &gt; Bruno Carter &gt; Dan Oliveira &gt; Grace Teles<br>2      Erin Prado     Alice Martins &gt; Bruno Carter &gt; Erin Prado                <br>1      Carol Nunes    Alice Martins &gt; Carol Nunes                              <br>2      Frank Reis     Alice Martins &gt; Carol Nunes &gt; Frank Reis                 <br>sqlite&gt;</pre><p>Alice is level 0. Bruno and Carol are level 1. Their reports appear at level 2, and Grace is at level 3: three links below Alice, the fourth level if you count the root as the first.</p><p>|| concatenates strings in SQLite. The display path grows from Alice Martins into the full chain of command. Sorting by that path keeps each manager next to their descendants for these names. This sorts the <strong>result</strong>, not necessarily the engine&#39;s visitation order, and it isn&#39;t a custom organizational sort order. In a real application, use stable IDs or an explicit sibling ordering key when names aren&#39;t enough.</p><h4>Why keep a separate visited path?</h4><p>Names are for people; IDs are for identity. The visited column starts as ,1, for Alice, then becomes something like ,1,2,4, along a branch. Before adding a child, instr checks whether its delimited ID is already in that path. A zero means “not found,” so the child is safe to visit.</p><p>The delimiters matter: searching for ,2, must not mistake employee 12 for employee 2. This representation is suitable for the integer IDs in our sample. It isn&#39;t a universal escaping scheme for arbitrary text identifiers.</p><p>If the data contains a cycle, the guard prevents revisiting an employee on the <strong>current branch</strong>. It doesn’t reject the bad relationship when it’s written, repair the graph, or globally deduplicate every possible route through a multi-parent graph. Acyclicity is a separate integrity rule; a foreign key alone does not enforce it.</p><p>There’s another important limit: this query starts only at NULL roots. It finds the forest reachable from those roots, <strong>not every employee in every possible graph</strong>. A disconnected cycle has no root and stays invisible. Even perfect cycle protection doesn&#39;t make an unreachable component reachable. For an audit, compare reachable IDs with the complete employee population or choose anchors appropriate to the question.</p><p>Also, simply changing UNION ALL to UNION would not reliably protect this query. A return to the same employee at a different depth, with a longer path, produces a different complete row. Duplicate elimination would not recognize that as the same visit.</p><h4>Start from one manager instead</h4><p>The same pattern answers “<strong>give me everyone under Bruno.</strong>” Anchor on employee 2 and measure depth from there:</p><pre>WITH RECURSIVE reports AS (<br>    SELECT employee_id, name, manager_id, 0 AS depth,<br>           &#39;,&#39; || employee_id || &#39;,&#39; AS visited<br>    FROM employees<br>    WHERE employee_id = 2          -- anchor: Bruno Carter<br>    UNION ALL<br>    SELECT e.employee_id, e.name, e.manager_id, r.depth + 1,<br>           r.visited || e.employee_id || &#39;,&#39;<br>    FROM employees e<br>    JOIN reports r ON e.manager_id = r.employee_id<br>    WHERE instr(r.visited, &#39;,&#39; || e.employee_id || &#39;,&#39;) = 0<br>)<br>SELECT depth, name<br>FROM reports<br>ORDER BY depth, name;</pre><p>Actual result:</p><pre>sqlite&gt; WITH RECURSIVE reports AS (<br>(x1...&gt;     SELECT employee_id, name, manager_id, 0 AS depth,<br>(x1...&gt;            &#39;,&#39; || employee_id || &#39;,&#39; AS visited<br>(x1...&gt;     FROM employees<br>(x1...&gt;     WHERE employee_id = 2          -- anchor: Bruno Carter<br>(x1...&gt;     UNION ALL<br>(x1...&gt;     SELECT e.employee_id, e.name, e.manager_id, r.depth + 1,<br>(x1...&gt;            r.visited || e.employee_id || &#39;,&#39;<br>(x1...&gt;     FROM employees e<br>(x1...&gt;     JOIN reports r ON e.manager_id = r.employee_id<br>(x1...&gt;     WHERE instr(r.visited, &#39;,&#39; || e.employee_id || &#39;,&#39;) = 0<br>(x1...&gt; )<br>   ...&gt; SELECT depth, name<br>   ...&gt; FROM reports<br>   ...&gt; ORDER BY depth, name;<br>depth  name        <br>-----  ------------<br>0      Bruno Carter<br>1      Dan Oliveira<br>1      Erin Prado  <br>2      Grace Teles <br>sqlite&gt;</pre><p>That’s Bruno, his two direct reports, and Dan’s report Grace. The anchor includes Bruno himself at depth 0. If a report should list only descendants, filter out depth 0 in the final query; don’t remove the starting employee from the anchor or there will be nothing to expand.</p><p>The visited-ID guard stays in place. It matters especially when you start in the middle of malformed data: the traversal might enter a cycle even though a root-based query would never see that component. That robustness check is not an extra relationship in the supplied sample data, and you don’t need to perform it in this session.</p><p>For large or highly connected graphs, avoiding cycles still doesn’t guarantee a small result. Many valid paths can be expensive too. Set appropriate operational limits, and use a depth bound when it matches the business question rather than silently chopping a legitimate hierarchy at an arbitrary level.</p><h3>CTEs for data analysis</h3><p>CTEs and window functions are natural partners. A CTE gives the data a clean shape; the window function calculates across those shaped rows without collapsing them into another aggregate row.</p><p>Each query below restates its own monthly or customer_totals CTE. You can run any of them after setup without running the previous analytical query. The definitions aren&#39;t carried over between semicolons.</p><h4>A running total</h4><p>First aggregate paid orders by month, then sum those monthly rows in order:</p><pre>WITH monthly AS (<br>    SELECT strftime(&#39;%Y-%m&#39;, order_date) AS month,<br>           SUM(amount) AS revenue<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY strftime(&#39;%Y-%m&#39;, order_date)<br>)<br>SELECT month,<br>       revenue,<br>       SUM(revenue) OVER (<br>           ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<br>       ) AS running_total<br>FROM monthly<br>ORDER BY month;</pre><p>Actual execution:</p><pre>sqlite&gt; WITH monthly AS (<br>(x1...&gt;     SELECT strftime(&#39;%Y-%m&#39;, order_date) AS month,<br>(x1...&gt;            SUM(amount) AS revenue<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY strftime(&#39;%Y-%m&#39;, order_date)<br>(x1...&gt; )<br>   ...&gt; SELECT month,<br>   ...&gt;        revenue,<br>   ...&gt;        SUM(revenue) OVER (<br>(x1...&gt;            ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<br>(x1...&gt;        ) AS running_total<br>   ...&gt; FROM monthly<br>   ...&gt; ORDER BY month;<br>month    revenue  running_total<br>-------  -------  -------------<br>2026-01  920      920          <br>2026-02  1280     2200         <br>2026-03  1735     3935         <br>sqlite&gt;</pre><p>January starts at 920. February adds 1280, reaching 2200. March adds 1735, reaching the full 3935.</p><p>The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame says “all rows up to this row.” There is one row per month, so a simpler default frame would yield the same result here; spelling it out makes the cumulative intent clear. The window&#39;s ordering controls the calculation, while the outer ORDER BY controls presentation.</p><h4>Month-over-month growth</h4><p>Now chain another CTE that uses LAG to fetch the preceding observed month&#39;s revenue, and calculate percentage growth in the outer query:</p><pre>WITH monthly AS (<br>    SELECT strftime(&#39;%Y-%m&#39;, order_date) AS month,<br>           SUM(amount) AS revenue<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY strftime(&#39;%Y-%m&#39;, order_date)<br>),<br>with_prev AS (<br>    SELECT month,<br>           revenue,<br>           LAG(revenue) OVER (ORDER BY month) AS prev_revenue<br>    FROM monthly<br>)<br>SELECT month,<br>       revenue,<br>       prev_revenue,<br>       ROUND((revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 1) AS growth_pct<br>FROM with_prev<br>ORDER BY month;</pre><p>Actual execution:</p><pre>sqlite&gt; WITH monthly AS (<br>(x1...&gt;     SELECT strftime(&#39;%Y-%m&#39;, order_date) AS month,<br>(x1...&gt;            SUM(amount) AS revenue<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY strftime(&#39;%Y-%m&#39;, order_date)<br>(x1...&gt; ),<br>   ...&gt; with_prev AS (<br>(x1...&gt;     SELECT month,<br>(x1...&gt;            revenue,<br>(x1...&gt;            LAG(revenue) OVER (ORDER BY month) AS prev_revenue<br>(x1...&gt;     FROM monthly<br>(x1...&gt; )<br>   ...&gt; SELECT month,<br>   ...&gt;        revenue,<br>   ...&gt;        prev_revenue,<br>   ...&gt;        ROUND((revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 1) AS growth_pct<br>   ...&gt; FROM with_prev<br>   ...&gt; ORDER BY month;<br>month    revenue  prev_revenue  growth_pct<br>-------  -------  ------------  ----------<br>2026-01  920      NULL          NULL      <br>2026-02  1280     920           39.1      <br>2026-03  1735     1280          35.5      <br>sqlite&gt;</pre><p>January has no predecessor, so LAG returns NULL, and its growth is NULL too. That&#39;s correct, not a bug. February grows by about 39.1%, and March by about 35.5%.</p><p>Two details make this less fragile than it first looks:</p><ul><li><strong>LAG means the previous observed row, not necessarily the previous calendar month.</strong> Our three months are contiguous. If February had no paid orders, the CTE would omit it and March would compare with January. For true calendar-month comparisons, construct a calendar of months, left-join revenue onto it, and explicitly decide whether a missing month means zero or unknown.</li><li><strong>NULLIF(prev_revenue, 0) guards a zero denominator.</strong> A month with zero revenue is not the same as an absent month. Growth after a zero month is undefined under this formula, so the query returns NULL rather than manufacturing a percentage. The 100.0 keeps the arithmetic real-valued; multiplying by integer 100 could truncate division when both operands are integers in SQLite.</li></ul><p>Keep those assumptions visible when you turn a useful query into a dashboard. The SQL can be perfectly valid and still answer the wrong version of “month over month.”</p><h4>Ranking customers</h4><p>Wrap the familiar paid-order totals in RANK:</p><pre>WITH customer_totals AS (<br>    SELECT customer_id, SUM(amount) AS total_spent<br>    FROM orders<br>    WHERE status = &#39;paid&#39;<br>    GROUP BY customer_id<br>)<br>SELECT RANK() OVER (ORDER BY ct.total_spent DESC) AS rank,<br>       c.name,<br>       ct.total_spent<br>FROM customer_totals ct<br>JOIN customers c ON c.customer_id = ct.customer_id<br>ORDER BY rank, c.customer_id;</pre><p>Actual output:</p><pre>sqlite&gt; WITH customer_totals AS (<br>(x1...&gt;     SELECT customer_id, SUM(amount) AS total_spent<br>(x1...&gt;     FROM orders<br>(x1...&gt;     WHERE status = &#39;paid&#39;<br>(x1...&gt;     GROUP BY customer_id<br>(x1...&gt; )<br>   ...&gt; SELECT RANK() OVER (ORDER BY ct.total_spent DESC) AS rank,<br>   ...&gt;        c.name,<br>   ...&gt;        ct.total_spent<br>   ...&gt; FROM customer_totals ct<br>   ...&gt; JOIN customers c ON c.customer_id = ct.customer_id<br>   ...&gt; ORDER BY rank, c.customer_id;<br>rank  name          total_spent<br>----  ------------  -----------<br>1     Elena Rossi   1180       <br>2     Carla Dias    1140       <br>3     Ana Costa     880        <br>4     Diego Mendes  420        <br>5     Bruno Alves   240        <br>6     Felipe Souza  75         <br>sqlite&gt;</pre><p>RANK gives equal totals the same rank and leaves gaps afterward. We don&#39;t have ties in this seed; with ties, the outer customer-ID ordering stabilizes display without changing how ranks are assigned. Choose DENSE_RANK if you want ranks without gaps, or ROW_NUMBER if each row must get a different position.</p><p>The same customer_totals definition has now appeared in several parts of this walkthrough. That&#39;s the point: once the result&#39;s meaning is clear and correct, reuse the definition and vary what you do with it. Across separate statements you&#39;re reusing SQL text, not a stored CTE object.</p><h3>Limitations and performance</h3><p>CTEs are a readability tool first. A few things are worth knowing before you lean on them hard.</p><p><strong>Scope is one statement.</strong> Use a temporary table when you need stored intermediate rows across several statements. A view can give a query a reusable schema name, but an ordinary view does not promise stored or cached results either.</p><p><strong>Logical stages don’t dictate physical execution.</strong> In SQLite, the planner can flatten a suitable CTE into the surrounding query, materialize it into a temporary structure, or use a co-routine strategy where applicable. The result and SQL semantics are what you specify; an exact scan order or “execute exactly once” guarantee is not what the CTE name gives you.</p><p><strong>Materialization can use disk.</strong> A temporary intermediate result may live in memory or in a temporary file depending on the engine, plan, configuration, and workload. It is wrong to infer “no disk I/O” from “not a permanent table.” Materialization can avoid repeated work, but it can also consume memory, introduce temporary I/O, or block useful optimizations.</p><p>SQLite 3.35.0+ supports MATERIALIZED and NOT MATERIALIZED planner hints. In particular, SQLite&#39;s NOT MATERIALIZED does <strong>not</strong> prohibit materialization; it asks the planner to treat the CTE more like a normal view/subquery. Leave the choice to the planner unless measurement gives you a reason to intervene. No hint is required by this tutorial.</p><p><strong>A CTE is not automatically a cache or a speedup.</strong> Repeated references can interact with inlining and materialization differently across engines. Measure the actual query and data volume, inspect the plan, and consider a temporary table when an expensive intermediate result genuinely needs explicit storage and reuse. Thirteen orders are enough to explain correctness, not enough to benchmark a production workload.</p><p><strong>Recursion needs a brake.</strong> Finite sequences need a bound; graph walks need a termination strategy such as the visited-path guard above. UNION helps only when duplicate complete rows represent repeat visits. An outer row limit is not a universal safety guarantee, particularly when sorting or joining requires consuming the recursive result first.</p><p><strong>Indexes still matter.</strong> The recursive join searches employees by manager_id. The primary-key declarations support lookup by employee ID, but the REFERENCES declaration does <strong>not automatically index the referencing </strong><strong>manager_id column</strong> in SQLite or PostgreSQL. SQLite may choose an automatic query-time index; that&#39;s not the same as declaring a persistent one.</p><p>For a growing hierarchy, add an explicit index.</p><pre>CREATE INDEX idx_employees_manager_id ON employees(manager_id);</pre><p>With seven employees this isn’t a performance claim; it’s the relevant access path to consider as the table grows. Paid-order filtering and customer grouping may need their own indexes in a real application, based on measured queries and write costs.</p><h3>Close it, then prove the data stayed</h3><p>At the current sqlite&gt; prompt, enter .quit. That exits the CLI; it does not delete store.db. In the same practice directory, run the same OS command again:</p><pre>sqlite3 store.db</pre><p>The reopened test session starts this way (the display settings are connection/session setup, so we enter them again):</p><pre>SQLite version 3.46.1 2024-08-13 09:16:08<br>Enter &quot;.help&quot; for usage hints.<br>sqlite&gt; .headers on<br>sqlite&gt; .mode column<br>sqlite&gt; .nullvalue NULL<br>sqlite&gt;</pre><p>Run the same baseline check again:</p><pre>SELECT<br>    (SELECT COUNT(*) FROM customers) AS customers,<br>    (SELECT COUNT(*) FROM orders) AS orders,<br>    (SELECT COUNT(*) FROM employees) AS employees,<br>    (SELECT SUM(amount)<br>     FROM orders<br>     WHERE status = &#39;paid&#39;) AS paid_total;</pre><pre>sqlite&gt; SELECT (SELECT COUNT(*) FROM customers) AS customers,<br>   ...&gt;        (SELECT COUNT(*) FROM orders) AS orders,<br>   ...&gt;        (SELECT COUNT(*) FROM employees) AS employees,<br>   ...&gt;        (SELECT SUM(amount) FROM orders WHERE status = &#39;paid&#39;) AS paid_total;<br>customers  orders  employees  paid_total<br>---------  ------  ---------  ----------<br>6          13      7          3935      <br>sqlite&gt;</pre><p>The counts and paid total are still there. <strong>Do not rerun the table creation or inserts</strong> on reopening. Your base tables persisted; the CTE names from earlier statements did not. Exit with .quit when you&#39;re done.</p><p>If you want a clean restart, use a different empty practice directory and the filename store.db there. That avoids deleting anything or accidentally mixing the tutorial with an existing database. The analytical queries are read-only and repeatable after setup. Run the CREATE INDEX statement only once; a second run fails because the index already exists.</p><h3>Wrapping up</h3><ul><li>A CTE names an intermediate query so the logical stages are easier to read and the definition can be referenced more than once in one statement.</li><li>CTEs, derived tables, and correlated subqueries can express the same calculation. Prefer the form that makes intent clear; measure performance instead of guessing from indentation.</li><li>Multiple CTEs after one WITH, separated by commas, express a pipeline of dependencies.</li><li>Recursive CTEs combine an anchor, a recursive member, and a termination strategy. Sequences need bounds; graph walks need cycle-aware thinking.</li><li>Window functions pair naturally with CTEs for running totals, previous-row comparisons, and rankings.</li><li>Know your engine’s numeric behavior, materialization choices, and recursion rules. Readability is portable more often than syntax or execution plans.</li></ul><p>The fastest way to internalize this is to run each query, change one thing, and see what moves. Start with the refunded order or the spending threshold; then try a different manager anchor. Keep the question, the population, and the stopping rule explicit. The structure is useful whether you’re summing thirteen orders or working toward a much larger dataset.</p><h3>Reproducibility notes</h3><p>Tested statement by statement in the <strong>SQLite 3.46.1 CLI</strong>. Other-engine guidance was checked against documentation, not rerun on those databases.</p><h4>References</h4><ul><li><a href="https://sqlite.org/cli.html">SQLite command-line shell</a> and <a href="https://sqlite.org/download.html">official downloads</a></li><li><a href="https://sqlite.org/lang_with.html">SQLite WITH clause, recursive behavior, and materialization hints</a></li><li><a href="https://sqlite.org/datatype3.html">SQLite type affinity and numeric storage</a></li><li><a href="https://www.postgresql.org/docs/current/queries-with.html">PostgreSQL WITH queries</a> and <a href="https://www.postgresql.org/docs/12/release-12.html">PostgreSQL 12 release notes</a></li><li><a href="https://dev.mysql.com/doc/refman/8.4/en/derived-table-optimization.html">MySQL merging and materialization</a></li><li><a href="https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SELECT.html">Oracle SELECT: recursive subquery factoring</a></li></ul><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f383b8a716e7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Solving the N+1 Query Problem in Drizzle ORM: A Practical Guide]]></title>
            <link>https://medium.com/@johnidouglasmarangon/solving-the-n-1-query-problem-in-drizzle-orm-a-practical-guide-50d1c244d970?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/50d1c244d970</guid>
            <category><![CDATA[drizzle]]></category>
            <category><![CDATA[nextjs]]></category>
            <category><![CDATA[javascript]]></category>
            <category><![CDATA[orm]]></category>
            <category><![CDATA[typescript]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 16:09:07 GMT</pubDate>
            <atom:updated>2026-11-06T16:09:07.100Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*tHTuhf2KO27Xn6f9" /></figure><p>The N+1 query problem is one of the most common performance killers in database-driven applications. Your code looks clean, your tests pass, and everything is fast on a seed database with ten rows.</p><p>In this post, I’ll explain what the N+1 problem is, why it happens, and a few idiomatic ways to eliminate it in Drizzle, from the relational query builder to explicit joins and batch loading. Throughout, we’ll use a single running example: <strong>users and their orders</strong>.</p><p>I encountered this issue in several queries in a service built with Next.js and Drizzle ORM.</p><p>The snippets are illustrative. They assume you already have users and orders tables in ./schema and an existing node-postgres pool, and they use Drizzle 0.x’s relations() API (Drizzle 1.0, currently in beta, replaces it with defineRelations()).</p><h4>What is the N+1 problem?</h4><p>The N+1 problem occurs when your application runs <strong>one query</strong> to fetch a list of records, then <strong>N additional queries</strong> — one per record — to fetch related data.</p><p>Here’s the anti-pattern in its purest form:</p><pre>// 1 query to get all users<br>const allUsers = await db.select().from(users);<br> <br>// N queries — one per user to get their orders<br>for (const user of allUsers) {<br>  const userOrders = await db<br>    .select()<br>    .from(orders)<br>    .where(eq(orders.userId, user.id));<br>}<br>// N + 1</pre><p>With 100 users, that loop makes <strong>101 database round trips</strong>. Each trip carries network latency and per-query overhead, and the total scales linearly with your data. Double your users, and you double your queries. This is the shape of a page that feels instant in development and falls over the moment real data arrives.</p><h4>Why this happens</h4><p>Many ORMs default to <strong>lazy loading</strong>: related data is fetched automatically the first time you access it. It reads beautifully — user.orders just works — but it hides the cost. Every property access can silently become another database query, and the N+1 problem creeps in without a single suspicious-looking line of code.</p><p>Drizzle takes the opposite stance. It does <strong>no implicit lazy loading</strong>. If you want a user’s orders, you have to ask for them. You can still cause N+1 by accident, as the loop above shows, but the queries are explicit in your code instead of hidden behind a property access. That transparency is the feature: the loop above is visibly a loop of queries, so it’s easy to spot and fix.</p><p>The rest of this guide is about how to ask for related data the right way.</p><h4>Solution 1: Relational queries with with</h4><p>Drizzle’s relational query builder is the cleanest, most idiomatic fix. You declare your relations once, then fetch nested data eagerly with the <a href="https://orm.drizzle.team/docs/rqb">with</a> key.</p><p><strong>Step 1: Define the relations.</strong> Relations are metadata for the query API; they don’t create foreign keys in your database, they just tell Drizzle how your tables connect. Export them from the same schema.ts module as the tables:</p><pre>// schema.ts (alongside the users and orders tables)<br>import { relations } from &#39;drizzle-orm&#39;;<br> <br>export const usersRelations = relations(users, ({ many }) =&gt; ({<br>  orders: many(orders),<br>}));<br> <br>export const ordersRelations = relations(orders, ({ one }) =&gt; ({<br>  user: one(users, {<br>    fields: [orders.userId],<br>    references: [users.id],<br>  }),<br>}));</pre><p><strong>Step 2: Register the schema on the client.</strong> The db.query API only works when you pass your tables <em>and</em> relations to drizzle(). Because both are exported from schema.ts, one namespace import covers them:</p><pre>import { drizzle } from &#39;drizzle-orm/node-postgres&#39;;<br>import * as schema from &#39;./schema&#39;;<br> <br>// `pool` is your existing node-postgres Pool<br>const db = drizzle(pool, { schema });<br> <br>// If the relations live in their own file, pass both explicitly:<br>// const db = drizzle(pool, { schema: { ...schema, ...relationsModule } });</pre><p>If the relations aren’t registered, with fails at runtime even though the code compiles.</p><p><strong>Step 3: Query with eager loading.</strong></p><pre>const result = await db.query.users.findMany({<br>  with: {<br>    orders: true,<br>  },<br>});<br>     <br>// Result: [{ id: 1, name: &#39;Alice&#39;, orders: [...] }, ...]</pre><p>The with key tells Drizzle to fetch each user&#39;s orders alongside the users. No loop, no extra round trips.</p><p><strong>What actually runs and why it’s not a plain JOIN.</strong> It’s tempting to assume with expands into a LEFT JOIN that returns one row per order, but it doesn&#39;t. With Drizzle 0.x on PostgreSQL, this query is sent as <strong>one SQL statement</strong> that uses a LEFT JOIN LATERAL subquery with json_agg. Instead of a wide, flat result set, the nested array is assembled <em>inside the database</em>, and each user comes back as one row with its orders array attached. Other dialects and Drizzle versions can generate different SQL, so check your query log if you’re elsewhere.</p><p>This matters for two reasons. First, it’s one round trip, so the N+1 problem is gone. Second, a plain one-to-many join repeats the parent columns on every child row: a user with 50 orders comes back as 50 rows carrying the same user data, which you then have to regroup. Aggregating children as JSON avoids that duplication.</p><h4>Solution 2: Explicit joins</h4><p>When you need precise control over the query shape, specific columns, aggregations, or complex filtering, drop down to Drizzle’s SQL-like builder and write the join yourself.</p><pre>import { eq } from &#39;drizzle-orm&#39;;<br> <br>const result = await db<br>  .select({<br>    userId: users.id,<br>    userName: users.name,<br>    orderId: orders.id,<br>    orderTotal: orders.total,<br>  })<br>  .from(users)<br>  .leftJoin(orders, eq(users.id, orders.userId));<br> <br>// One query, flat rows — aggregate in memory if you need nesting</pre><p>This is still a single query, so it also solves N+1, but the result is <strong>flat</strong>: one row per user-order pair, with user columns repeated (and null order columns for users without orders). That’s exactly what you want for reporting, custom projections, or feeding a query straight into an aggregation. If you need the nested object shape, you group the rows in memory afterward — or go back to with, which does that grouping for you.</p><p>A rule of thumb: reach for with when you want nested objects, and for explicit joins when you want a precise, flat result set or database-side aggregation.</p><h4>Solution 3: Batch loading with inArray</h4><p>Sometimes you genuinely need to load the related data in a <em>separate</em> step — crossing a service boundary, populating a cache, or enriching data that came from somewhere other than the first query. The fix is to make that second step a single batched query instead of one query per record. inArray is the tool.</p><pre>import { inArray } from &#39;drizzle-orm&#39;;<br> <br>type Order = typeof orders.$inferSelect;<br> <br>// Step 1: Fetch users<br>const allUsers = await db.select().from(users);<br> <br>// Step 2: Fetch ALL their orders in one query (skip it if there are no users)<br>const userIds = allUsers.map((u) =&gt; u.id);<br>const allOrders: Order[] = userIds.length === 0<br>  ? []<br>  : await db.select().from(orders).where(inArray(orders.userId, userIds));<br> <br>// Step 3: Group in memory<br>const ordersByUser = new Map&lt;number, Order[]&gt;();<br>for (const order of allOrders) {<br>  const bucket = ordersByUser.get(order.userId) ?? [];<br>  bucket.push(order);<br>  ordersByUser.set(order.userId, bucket);<br>}<br> <br>// Step 4: Build enriched objects without any extra queries<br>const usersWithOrders = allUsers.map((user) =&gt; ({<br>  ...user,<br>  orders: ordersByUser.get(user.id) ?? [],<br>}));</pre><p>No matter how many users you have, this is <strong>two queries total</strong> — one for users, one for every order at once — and just one when there are no users. Step 4 builds new objects rather than assigning user.orders, because Drizzle’s inferred row type has no orders property and strict TypeScript rejects the assignment. The key move is turning “a query per ID” into “one query for all IDs,” which is the same idea DataLoader automates below. (If userIds can grow into the tens of thousands, chunk it to stay under your database&#39;s parameter limits; that adds one query per chunk.)</p><h4>Quick comparison</h4><ul><li><strong>with relational queries:</strong> best for nested object shapes. 1 query. Low complexity.</li><li><strong>Explicit joins:</strong> best for custom projections and aggregations. 1 query, flat rows. Medium complexity.</li><li><strong>inArray batching:</strong> best for service layers, caching, and cross-boundary loads. 2 queries (1 with no users, more if chunked). Medium complexity.</li></ul><h4>How to Identify and Find N+1 Query Problems</h4><p>Fixing N+1 is easy once you can <em>see</em> it. Watch for a list endpoint whose query count climbs with result size. <strong>Illustrative only (not measured):</strong> for this article’s users-and-orders example, that might look like 11 queries for 10 users or 101 for 100, while a healthy version of <em>that same</em> load stays around one or two round trips (with or a single inArray batch)—not a promise every healthy endpoint stays that low.</p><p><strong>Watch the SQL: repeated parameterized statements</strong></p><p>In development, turn on <a href="https://orm.drizzle.team/docs/goodies">Drizzle’s query logger</a> and hit the endpoint once:</p><pre>// Assumes an existing node-postgres `pool` and `schema` (tables + relations).<br>const db = drizzle(pool, { schema, logger: true });</pre><p>If the same select ... from &quot;orders&quot; where &quot;orders&quot;.&quot;user_id&quot; = $1 prints over and over with different bind values during one request, that is a <strong>strong sign</strong> of N+1—correlate it with that one request’s query list (or a trace waterfall) to confirm before you rewrite. Group by the <em>normalized</em> statement (placeholders kept, literal IDs stripped). One-off slow queries need EXPLAIN; repeating the same shape once per row is the N+1 smell.</p><p>In production, APM tools can instrument the Node.js database drivers used by Drizzle rather than Drizzle itself. For example, <a href="https://docs.datadoghq.com/tracing/trace_collection/dd_libraries/nodejs/">Datadog’s Node.js tracer (</a><a href="https://docs.datadoghq.com/tracing/trace_collection/dd_libraries/nodejs/">dd-trace)</a> instruments libraries such as pg when initialized before other imports. It can also <a href="https://docs.datadoghq.com/database_monitoring/connect_dbm_and_apm/">correlate APM traces with Database Monitoring</a>, allowing you to inspect repeated query samples in the context of a single request.</p><p>If you already use Sentry for performance monitoring, its <a href="https://docs.sentry.io/product/issues/issue-details/performance-issues/n-one-queries/">Performance Issues → N+1 Queries</a> feature can help identify repeated, similar database spans within a <a href="https://docs.sentry.io/platforms/javascript/guides/node/tracing/">transaction trace</a>.</p><p><strong>Count queries per request (dev-safe, request-scoped)</strong></p><p>logger: true is enough to eyeball a page. When you want a number, implement Drizzle’s <a href="https://orm.drizzle.team/docs/goodies">Logger</a> and keep the counter on the request, not on a module-level let—concurrent requests would race a shared mutable total. Node’s <a href="https://nodejs.org/api/async_context.html">AsyncLocalStorage</a> is the usual fix:</p><pre>import { AsyncLocalStorage } from &#39;node:async_hooks&#39;;<br>import type { Logger } from &#39;drizzle-orm/logger&#39;;<br>import { drizzle } from &#39;drizzle-orm/node-postgres&#39;;<br> <br>// Integration example: assumes an existing `pool`, `schema`, and request handler.<br>type QueryBag = { count: number };<br>const queryBag = new AsyncLocalStorage&lt;QueryBag&gt;();<br> <br>class CountingLogger implements Logger {<br>  logQuery(query: string, _params: unknown[]): void {<br>    // Counts Drizzle logger invocations only—not every DB statement<br>    // (raw `pool.query`, other clients, and migrations are invisible here).<br>    // Prefer the SQL shape only; params can carry emails, tokens, or other PII.<br>    const bag = queryBag.getStore();<br>    if (bag) bag.count += 1;<br>    if (process.env.NODE_ENV !== &#39;production&#39;) {<br>      console.debug(query);<br>    }<br>  }<br>}<br> <br>const db = drizzle(pool, { schema, logger: new CountingLogger() });<br> <br>// In your HTTP middleware / handler:<br>await queryBag.run({ count: 0 }, async () =&gt; {<br>  await handleUsersWithOrders(/* ... */);<br>  const { count } = queryBag.getStore()!;<br>  console.log(`Drizzle queries this request: ${count}`);<br>});</pre><p>A scoped counter in a single-threaded test (no overlapping requests) works for a unit check; under a real server, prefer AsyncLocalStorage.</p><h4>Key takeaways</h4><ul><li><strong>Drizzle doesn’t hide lazy loading.</strong> Every query is explicit in your code, so an N+1 loop is visible, even though you can still write one by accident.</li><li><strong>Use </strong><strong>with for nested data.</strong> It&#39;s the most idiomatic solution; with Drizzle 0.x on PostgreSQL it compiles to a single query that aggregates children as JSON instead of repeating parent rows.</li><li><strong>Use explicit joins</strong> when you want a flat, precisely-shaped result or database-side aggregation.</li><li><strong>Use </strong><strong>inArray </strong>when the related load has to happen in a separate step or inside GraphQL resolvers.</li><li><strong>Always profile.</strong> Query logging shows how many queries a request makes; EXPLAIN shows how each one runs.</li></ul><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=50d1c244d970" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Testing GitHub Actions Locally with act: Stop Committing Just to Debug]]></title>
            <link>https://medium.com/@johnidouglasmarangon/testing-github-actions-locally-with-act-stop-committing-just-to-debug-b0b2de59e212?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/b0b2de59e212</guid>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[continuous-integration]]></category>
            <category><![CDATA[github-actions]]></category>
            <category><![CDATA[github]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 14:25:19 GMT</pubDate>
            <atom:updated>2026-11-06T14:25:19.136Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/780/0*q1DOMV6c1ZxxiOwI" /></figure><p>A few weeks ago, I came across a tool that allows you to run GitHub Actions locally, making it easier to develop and test workflows before pushing them to GitHub. The tool is <a href="https://github.com/nektos/act">act</a>.</p><p>act runs your GitHub Actions workflows on your own machine, in Docker, so you can iterate on a pipeline the same way you iterate on code: edit, run, read the error, repeat in seconds, with nothing pushed until it actually works.</p><p>This post explains what act is, how it works under the hood and then walks through a complete hands-on setup you can follow on a real project. We&#39;ll finish with the real-world scenarios (secrets, events, variables) and the gotchas that trip people up.</p><h4>What is act?</h4><p>act is a command-line tool that reads the workflow files in your .github/workflows/ directory and executes them locally using Docker. Instead of GitHub&#39;s hosted runners, each job runs inside a container on your laptop.</p><p>That’s the whole idea: your CI pipeline, running in your terminal, with no commit and no push required. The same YAML that GitHub executes remotely, act executes locally.</p><p>Two things make it genuinely useful rather than just a novelty:</p><ul><li><strong>It’s the same workflow file.</strong> You’re not writing a separate local script that <em>approximates</em> your CI. You’re running the exact ci.yml that ships in your repo, so what passes locally is a strong signal it&#39;ll pass remotely — with the usual caveat that local runner images are an emulation, not a clone, of GitHub’s hosts.</li><li><strong>It’s fast to iterate.</strong> No hosted-runner queue or provisioning delay. Image pulls, action downloads, and dependency installs can still need the network, but repeat runs can be much quicker.</li></ul><h4>How act actually works</h4><p>It helps to have a mental model before you run it, because a couple of the gotchas later make sense only once you understand the mechanism.</p><p>When you invoke act, it does roughly this:</p><ol><li><strong>Parses your workflow files.</strong> It reads everything under .github/workflows/ and figures out which jobs should run for the event you&#39;re simulating (a plain act default to the push event).</li><li><strong>Builds an execution plan.</strong> Using the needs: dependencies between jobs, it determines the order — which jobs can run, and which must wait for others to finish first. This is the same dependency graph GitHub builds remotely.</li><li><strong>Prepares Docker images.</strong> For each job’s runs-on, it pulls (or builds) a Docker image that stands in for the GitHub-hosted runner.</li><li><strong>Runs each job in a container.</strong> It starts a container per job, wires up the environment variables and a filesystem that mimic what GitHub provides, then executes your steps inside it.</li></ol><p>That last point is the key insight: <strong>runs-on: ubuntu-latest doesn&#39;t mean &quot;an Ubuntu VM&quot; locally — it means &quot;a Docker image that plays the role of the Ubuntu runner.&quot;</strong> Those images are not identical to GitHub&#39;s real runners, and choosing the right one is the single most important setup decision you&#39;ll make. More on that in a moment.</p><h4>Prerequisites</h4><p>You need two things:</p><ul><li><strong>Docker</strong>, installed and running. act is a thin orchestrator on top of the Docker API; without a running Docker daemon, it has nothing to run containers on. Docker Desktop on macOS/Windows, or Docker Engine on Linux, both work.</li><li><strong>A repository with at least one workflow</strong> in .github/workflows/. If you don&#39;t have one handy, the hands-on section below creates a tiny one from scratch.</li></ul><p>Confirm Docker is up before you start:</p><pre>docker info</pre><p>If that prints daemon details rather than an error, you’re ready.</p><h4>Hands-on: your first local run</h4><p>Let’s build a minimal but real project, add a workflow, and run it with act.</p><h4>Step 1 — Install act</h4><p>Prefer a package manager so the binary lands on your PATH:</p><pre># macOS / Linux (Homebrew)<br>brew install act</pre><pre># Windows (winget)<br>winget install nektos.act</pre><pre># Windows (Chocolatey)<br>choco install act-cli</pre><pre># Windows (Scoop)<br>scoop install act</pre><p>If you already use the GitHub CLI, you can install act as an extension instead:</p><pre>gh extension install nektos/gh-act</pre><p>With the extension, the command is <strong>gh act</strong>, not act. Everywhere this post writes act, run gh act the same way — for example gh act --version, gh act -l, gh act pull_request. The rest of the examples keep the shorter act form for the standalone install.</p><p>Verify the binary you installed:</p><pre>act --version<br># or, with the extension: gh act --version</pre><h4>Step 2 — Create a sample project</h4><p>We’ll make a tiny Node.js project with a test, and a workflow that installs dependencies and runs it. Node’s built-in test runner keeps this dependency-free, so the whole thing is copy-paste runnable.</p><pre>mkdir act-demo &amp;&amp; cd act-demo<br>git init -b main<br>npm init -y</pre><p>git init -b main gives act a real repository on the same default branch the workflow targets. You don’t need throwaway commits just to debug — act copies your working tree into the job container (and typically skips actions/checkout for that reason). Init is enough for cleaner local git metadata; commit when <em>you</em> want a snapshot, not because the tool demands a push cycle.</p><p>Add a trivial module and a test. Create math.js:</p><pre>function add(a, b) {<br>  return a + b;<br>}<br> <br>module.exports = { add };</pre><p>And math.test.js:</p><pre>const { test } = require(&#39;node:test&#39;);<br>const assert = require(&#39;node:assert&#39;);<br>const { add } = require(&#39;./math&#39;);<br> <br>test(&#39;adds two numbers&#39;, () =&gt; {<br>  assert.strictEqual(add(1, 1), 2);<br>});</pre><p>Wire up the test script in package.json:</p><pre>{<br>  &quot;name&quot;: &quot;act-demo&quot;,<br>  &quot;version&quot;: &quot;1.0.0&quot;,<br>  &quot;scripts&quot;: {<br>    &quot;test&quot;: &quot;node --test&quot;<br>  }<br>}</pre><h4>Step 3 — Add a workflow</h4><p>Create .github/workflows/ci.yml:</p><pre>name: CI<br> <br>on:<br>  push:<br>    branches: [main]<br>  pull_request:<br> <br>jobs:<br>  build:<br>    runs-on: ubuntu-latest<br>    steps:<br>      - name: Check out the code<br>        uses: actions/checkout@v4<br> <br>      - name: Set up Node<br>        uses: actions/setup-node@v4<br>        with:<br>          node-version: 24<br> <br>      - name: Install dependencies<br>        run: npm install<br> <br>      - name: Run tests<br>        run: npm test</pre><p>In a real project you’d use npm ci instead of npm install to get reproducible installs from your lockfile. We use npm install here so the sample runs without a committed package-lock.json.</p><h4>Step 4 — Run it</h4><p>From the project root, just run:</p><pre>act</pre><p>The first time you ever run act, it asks which runner image size to use as your default — <strong>Micro</strong>, <strong>Medium</strong>, or <strong>Large</strong> — and saves your answer in an actrc file (commonly ~/.config/act/actrc on Linux, sometimes ~/.actrc, and platform-specific XDG locations on macOS/Windows) so it never asks again. For almost everyone the right answer is <strong>Medium</strong>. Here&#39;s the tradeoff:</p><ul><li><strong>Micro</strong> — a slim Node image (under ~200MB). Use it only for basic shell/Node steps when you want the smallest download. It’s missing a lot of tooling GitHub’s real runners ship with.</li><li><strong>Medium</strong> — catthehacker/ubuntu:act-latest, a reasonable stand-in for the real runner (~500MB). <strong>The default pick for most projects.</strong></li><li><strong>Large</strong> — a near-full mirror of GitHub’s runner image (multi-tens of GB once you count download and storage). Use it when you depend on preinstalled tools that only the full runner ships.</li></ul><p>The Micro images are intentionally stripped down — steps that assume git, build tools, or common CLIs will fail in confusing ways. Medium is the sweet spot: close enough to the real thing for the vast majority of workflows, without a multi-gigabyte download.</p><p>Once the image is pulled, you’ll see act walk through your job step by step, printing each one as it runs and ending with a success or failure summary. Because the <strong>fourth</strong> step in our workflow actually runs your tests inside the container, a green result here is a real signal — not a simulation.</p><h4>Step 5 — Drive it like a pro</h4><p>A handful of flags cover most of what you’ll do day to day:</p><pre># List the jobs that would run for the default (push) event<br>act -l<br> <br># List jobs for a specific event<br>act -l pull_request<br> <br># Run a specific event instead of push<br>act pull_request<br> <br># Run a single job by its id<br>act -j build<br> <br># Dry run: limited validation without creating containers<br>act -n<br> <br># Verbose output — your best friend when debugging<br>act -v</pre><p>Use act -l when you want to see <em>which</em> jobs match an event before you spend time on a full run. act -n is a dry-run mode: it aims to validate workflow correctness without creating containers. It is <strong>not</strong> a pure “print the plan and touch nothing” switch; it can still talk to the Docker API, so keep Docker available even for dry runs, and reach for -l when all you need is the job list. And act -v is the first thing to reach for when something fails and the normal output isn&#39;t telling you why.</p><h4>Step 6 — If you’re on Apple Silicon</h4><p>If you’re on an M-series Mac (or any ARM machine), many action and runner images are built for amd64. Tell act to run containers under emulation:</p><pre>act --container-architecture linux/amd64</pre><p>Rather than typing that every time, put it in your config, which is the perfect excuse to introduce .actrc.</p><h4>Making it stick: the .actrc file</h4><p>Any flag you’d pass on the command line can live in an .actrc file instead, so your whole team gets the same defaults. act reads configuration from several places, in order: an XDG config file (for example ~/.config/act/actrc), ~/.actrc in your home directory, a project-level .actrc in the repo root, then CLI arguments.</p><p>A sensible project .actrc:</p><pre>-P ubuntu-latest=catthehacker/ubuntu:act-latest<br>--container-architecture linux/amd64</pre><p>The -P line sets which Docker image stands in for ubuntu-latest for this project, so teammates aren’t guessing from whatever they picked on first launch. Treat it as a <strong>shared default mapping</strong>, not an immutable pin: tags like act-latest still move when the image publisher updates them. Commit the file so the <em>choice of image family</em> is consistent across machines.</p><h4>Real-world scenarios</h4><p>The toy workflow above has no secrets, no external events, and no configuration. Real pipelines do. Here’s how to handle the common cases.</p><h4>Passing secrets</h4><p>Never hardcode secrets into a workflow. Prefer a gitignored file or act’s secure prompt over putting secret <em>values</em> on the command line.</p><p>act reads secrets from a .secrets file (that’s the default --secret-file) that looks like a .env:</p><pre># .secrets<br>SUPABASE_KEY=local-dev-value<br>STRIPE_SECRET=sk_test_123</pre><pre>act --secret-file .secrets<br># equivalent when the file is named .secrets:<br>act</pre><p><strong>Add </strong><strong>.secrets to your </strong><strong>.gitignore immediately</strong> — this is exactly the kind of file you don&#39;t want committed.</p><p>For a one-off, pass the secret <em>name</em> and let act prompt you (nothing sensitive needs to sit in the command line):</p><pre>act -s MY_TOKEN</pre><p>A special case worth knowing: many workflows reference GITHUB_TOKEN, and some actions call the GitHub API during a run. act does not mint GitHub’s hosted token for you. If you have the GitHub CLI installed and authenticated, you can supply a token interactively the same way:</p><pre>act -s GITHUB_TOKEN</pre><p>Avoid act -s MY_TOKEN=literally-the-value on a shared machine or in recorded sessions. If you do use command substitution such as &quot;$(gh auth token)&quot;, ordinary shell history usually stores the <strong>literal command text</strong>, not the expanded token value — but the token still ends up in process arguments and environment for that run, so prefer the prompt or a gitignored file.</p><h4>Variables and environment</h4><p>Repository/organization <strong>variables</strong> (the vars context) and plain <strong>environment</strong> values are different knobs:</p><pre># Repository variable → ${{ vars.APP_ENV }}<br>act --var APP_ENV=test<br> <br># Bulk vars from a file (default --var-file is .vars)<br>act --var-file .vars<br> <br># Container environment → process env inside steps<br>act --env NODE_ENV=test<br> <br># Bulk env values from a file (default --env-file is .env)<br>act --env-file .env</pre><p>Use --var / --var-file when the workflow reads ${{ vars.* }}. Use --env / --env-file when a step expects a normal environment variable like NODE_ENV.</p><h4>Simulating a specific event payload</h4><p>Workflows often branch on the details of the event that triggered them — which branch a PR targets, who opened it, what label was added. act has basic event support and will infer what it can from your local repository, but that often isn’t enough. You can feed it a JSON payload that <em>partially</em> mimics a real event:</p><pre>{<br>  &quot;pull_request&quot;: {<br>    &quot;head&quot;: { &quot;ref&quot;: &quot;feature/new-login&quot; },<br>    &quot;base&quot;: { &quot;ref&quot;: &quot;main&quot; }<br>  }<br>}</pre><pre>act pull_request -e event.json</pre><p>Steps that read github.event.pull_request.base.ref can then see main, as far as the fields you supplied go. Missing fields still won’t match a full GitHub delivery, so treat this as a targeted simulation for event-conditional logic — not a byte-for-byte replay of production webhooks.</p><h4>Common pitfalls (and how to avoid them)</h4><p>act is excellent, but it&#39;s an <em>emulation</em> of GitHub&#39;s environment, not a clone of it. Knowing where the seams are saves you from chasing phantom bugs.</p><ul><li><strong>It’s not a byte-for-byte replica.</strong> The runner images differ from GitHub’s real ones, especially the smaller sizes. If a step passes on GitHub but fails locally (or vice versa), a tooling difference in the image is the usual culprit—try a larger image before assuming your workflow is broken.</li><li><strong>GitHub-hosted services aren’t all there.</strong> Features tied to GitHub’s infrastructure — things like OIDC token issuance — don’t exist locally by default. Steps that depend on them may need to be stubbed or skipped when running under act.</li><li><strong>Artifacts need opt-in; cache is different.</strong> actions/upload-artifact expects a server that act does <strong>not</strong> start until you pass something like --artifact-server-path /tmp/artifacts. By contrast, the local cache server for actions/cache is started by default; disable it with --no-cache-server if you need to.</li><li><strong>The default Micro image is a trap for real workflows.</strong> It’s genuinely minimal. If a step mysteriously can’t find a common command, you’re probably on Micro — switch to Medium.</li><li><strong>Actions that call the GitHub API need a token.</strong> Without GITHUB_TOKEN, those steps fail or hit rate limits. Supply one via the secure prompt shown above (or a gitignored secrets file).</li><li><strong>Docker-in-Docker is fiddly — and the socket is already there.</strong> act bind-mounts the host Docker socket into job containers by default (opt out with --container-daemon-socket -). Workflows that build or run containers often see your <strong>host</strong> daemon, which is powerful and easy to misuse. Expect extra care around permissions and cleanup, not a missing mount flag.</li><li><strong>Local runs are not a sandbox for untrusted code.</strong> Because job steps execute on your machine with that Docker access, only run workflows and actions you trust. Don’t treat act as an isolation boundary for reviewing arbitrary pull requests.</li></ul><p>The practical rule: use act to catch everyday failures such as typos, bad syntax, wrong paths, failing tests, and misconfigured steps — the things that waste the most round trips. For the handful of features that are inseparable from GitHub&#39;s platform, do a final verification on a real push. You&#39;ll still have saved yourself a dozen test commits getting there.</p><h4>Wrapping up</h4><ul><li>act runs your real .github/workflows/ files locally in Docker, turning a minutes-long push-and-wait loop into a seconds-long edit-and-rerun loop.</li><li>It reads your workflows, builds the same dependency graph GitHub does, and runs each job in a container that stands in for the hosted runner.</li><li>Choose the <strong>Medium</strong> runner image for most projects; Micro is too bare for real workflows and Large is overkill unless you need the full toolchain.</li><li>Put shared defaults — image mapping, architecture — in .actrc so your whole team starts from the same place (remember image tags can still move).</li><li>Handle secrets with a gitignored .secrets file or act -s NAME prompts; keep vars and env flags straight; simulate events with a JSON payload when local inference isn’t enough; reach for -l to list jobs and -v when something fails.</li><li>Remember it’s an emulation: let it catch the everyday failures fast, and keep one real push for the platform-specific bits.</li></ul><p>Install it, point it at a workflow you already have, and run act -l to see your pipeline laid out. The first time you fix a broken workflow without pushing anything, you won&#39;t want to go back.</p><h4>References</h4><ul><li><a href="https://github.com/nektos/act">nektos/act</a> — project overview and how act runs workflows locally with Docker.</li><li><a href="https://nektosact.com/installation/">Installation</a> — prerequisites (Docker) and package-manager install options.</li><li><a href="https://nektosact.com/installation/gh.html">Install as a GitHub CLI extension</a> — gh extension install and invoking workflows with gh act.</li><li><a href="https://github.com/nektos/gh-act">nektos/gh-act</a> — GitHub CLI extension repository.</li><li><a href="https://nektosact.com/usage/index.html">Usage guide</a> — events, -e payloads, .actrc load order, secrets, vars, env files, artifacts server, and related flags.</li><li><a href="https://nektosact.com/usage/runners.html">Runners</a> — Micro / Medium / Large image mappings for ubuntu-latest and friends.</li><li><a href="https://nodejs.org/en/about/previous-releases">Node.js releases</a> — current vs EOL Node lines (this sample uses Node 24).</li></ul><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b0b2de59e212" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building Real CLIs with Python’s argparse: From a Single Flag to Subcommands]]></title>
            <link>https://medium.com/@johnidouglasmarangon/building-real-clis-with-pythons-argparse-from-a-single-flag-to-subcommands-ef8f5a2513c6?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/ef8f5a2513c6</guid>
            <category><![CDATA[command-line-interface]]></category>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 01:41:04 GMT</pubDate>
            <atom:updated>2026-11-06T01:41:04.179Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_3SfzNmlm1mQ8Dzu" /></figure><p>In Python, we have some good libraries for building command-line interfaces. For example:</p><ul><li><a href="https://click.palletsprojects.com/en/stable/">Click</a> — one of the most popular CLI libraries. It uses decorators, keeps the code very clean, and handles prompts, validation, and colors.</li><li><a href="https://typer.tiangolo.com/">Typer</a> — built on top of Click, but uses type hints. Very intuitive and modern.</li><li><a href="https://docs.python.org/3/library/argparse.html">argparse</a> — part of the standard library, so there is nothing to install. Great for scripts and small to medium projects.</li></ul><p>Recently, I built a few scripts that work together as a crawler, and I needed a CLI to control how they behave: parse a spreadsheet, download files, check what is missing, and sync the results to an API. I chose <strong>argparse.</strong> It ships with every Python, and it turned out to be much more powerful than most people give it credit for.</p><p>In this post I’ll share what I learned. Instead of a list of disconnected snippets, we’ll build one CLI, a crawler, step by step. Each step adds a few argparse features, and every example shows the real terminal output, including help and the error messages. By the end, you’ll have a complete, production-style script with subcommands, shared options, custom validation, and secrets from environment variables.</p><p>All examples were run with Python 3.13. Each step is a complete script; save it asmain.pyand run it.</p><h4>Step 1: ArgumentParser basics</h4><p>Everything starts with an <a href="https://docs.python.org/3/library/argparse.html#argumentparser-objects">ArgumentParser</a>. A few constructor arguments make a big difference in how professional your CLI feels:</p><ul><li>prog : the program name shown in usage and errors (by default, the script file name).</li><li>description: text shown before the arguments.</li><li>epilog: text shown after the arguments. Good for examples.</li><li>formatter_class: controls how help is rendered. ArgumentDefaultsHelpFormatter adds (default: ...) to every option automatically, so your help never lies about defaults.</li></ul><p>There are two kinds of arguments:</p><ul><li>Positional arguments (input) are identified by their position and are required by default.</li><li>Optional arguments (-o, --output) start with a dash and are, as the name says, optional by default.</li></ul><pre>import argparse<br> <br>parser = argparse.ArgumentParser(<br>    prog=&quot;crawler&quot;,<br>    description=&quot;Download public documents listed in a spreadsheet.&quot;,<br>    epilog=&quot;Example: crawler items.xlsx --output data/&quot;,<br>    formatter_class=argparse.ArgumentDefaultsHelpFormatter,<br>)<br>parser.add_argument(&quot;input&quot;, help=&quot;path to the source spreadsheet&quot;)<br>parser.add_argument(&quot;-o&quot;, &quot;--output&quot;, default=&quot;data&quot;, help=&quot;directory for downloaded files&quot;)<br> <br>args = parser.parse_args()<br>print(args)</pre><pre>$ python main.py --help<br>usage: crawler [-h] [-o OUTPUT] input<br> <br>Download public documents listed in a spreadsheet.<br> <br>positional arguments:<br>  input                path to the source spreadsheet<br> <br>options:<br>  -h, --help           show this help message and exit<br>  -o, --output OUTPUT  directory for downloaded files (default: data)<br> <br>Example: crawler items.xlsx --output data/</pre><pre>$ python main.py items.xlsx -o downloads<br>Namespace(input=&#39;items.xlsx&#39;, output=&#39;downloads&#39;)</pre><p>parse_args()returns a Namespace object, and the option, outputs becomes the attribute args.output. If we forget the positional argument, argparse prints the usage and an error and exits with code 2:</p><pre>$ python crawler.py<br>usage: crawler [-h] [-o OUTPUT] input<br>crawler: error: the following arguments are required: input<br>$ echo $?<br>2</pre><p>That’s the first big win: you get help, validation, and consistent error messages for free.</p><h4>Step 2: Types, defaults, and actions</h4><p>Now let’s make the crawler configurable. This step covers the keyword arguments you’ll use in almost every add_argument() call. Four add_argument() calls are enough to show all of them:</p><ul><li>type: converts the string from the command line (int, float, or any function).</li><li>default : the value used when the option is not provided.</li><li>required=True : makes an optional argument mandatory. Use it sparingly.</li><li>help: the description displayed in --help.</li><li>metavar: the placeholder name shown in help (NAME instead of BUCKET).</li><li>dest: the attribute name in the Namespace (--log-name is stored as args.log_file).</li><li>choices: restricts the value to a fixed set of options.</li></ul><pre>import argparse<br> <br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;, formatter_class=argparse.ArgumentDefaultsHelpFormatter)<br>parser.add_argument(&quot;--bucket&quot;, required=True, metavar=&quot;NAME&quot;, help=&quot;S3 bucket for storage&quot;)<br>parser.add_argument(&quot;--retries&quot;, type=int, default=3, help=&quot;max attempts per file&quot;)<br>parser.add_argument(&quot;--log-name&quot;, dest=&quot;log_file&quot;, default=&quot;crawler.log&quot;, help=&quot;log file path&quot;)<br>parser.add_argument(&quot;--format&quot;, choices=[&quot;json&quot;, &quot;csv&quot;], default=&quot;json&quot;, help=&quot;output format&quot;)<br> <br>args = parser.parse_args()<br>print(vars(args))</pre><pre>$ python main.py --help<br>usage: crawler [-h] --bucket NAME [--retries RETRIES] [--log-name LOG_FILE]<br>               [--format {json,csv}]<br> <br>options:<br>  -h, --help           show this help message and exit<br>  --bucket NAME        S3 bucket for storage (default: None)<br>  --retries RETRIES    max attempts per file (default: 3)<br>  --log-name LOG_FILE  log file path (default: crawler.log)<br>  --format {json,csv}  output format (default: json)</pre><pre>$ python main.py --bucket docs --retries 5 --log-name run.log --format csv<br>{&#39;bucket&#39;: &#39;docs&#39;, &#39;retries&#39;: 5, &#39;log_file&#39;: &#39;run.log&#39;, &#39;format&#39;: &#39;csv&#39;}</pre><p>Notice: log-name became log_file because of dest. When an option name contains dashes and no dest is specified, argparse automatically replaces them with underscores (--dry-run becomes args.dry_run).</p><p>Here is what users see when they make mistakes. Each error is specific, and none of it required a single if in our code:</p><pre>$ python main.py --bucket docs --format xml<br>usage: crawler [-h] --bucket NAME [--retries RETRIES] [--log-name LOG_FILE]<br>               [--format {json,csv}]<br>crawler: error: argument --format: invalid choice: &#39;xml&#39; (choose from json, csv)<br>$ echo $?<br>2</pre><pre>$ python main.py --bucket docs --retries five<br>usage: crawler [-h] --bucket NAME [--retries RETRIES] [--log-name LOG_FILE]<br>               [--format {json,csv}]<br>crawler: error: argument --retries: invalid int value: &#39;five&#39;<br>$ echo $?<br>2</pre><pre>$ python main.py<br>usage: crawler [-h] --bucket NAME [--retries RETRIES] [--log-name LOG_FILE]<br>               [--format {json,csv}]<br>crawler: error: the following arguments are required: --bucket<br>$ echo $?<br>2</pre><h4>Actions</h4><p>The action parameter changes what argparse does when it sees an option:</p><ul><li>store_true / store_false: boolean flags. --no-verify with store_false and dest=&quot;verify&quot; gives you a positive args.verify that defaults to True.</li><li>count: counts repetitions: -v is 1, -vv is 2.</li><li>append: collects repeated options into a list.</li><li>version: prints the version and exits.</li></ul><pre>import argparse<br> <br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;)<br>parser.add_argument(&quot;--dry-run&quot;, action=&quot;store_true&quot;, help=&quot;show what would be done&quot;)<br>parser.add_argument(&quot;--no-verify&quot;, dest=&quot;verify&quot;, action=&quot;store_false&quot;, help=&quot;skip TLS verification&quot;)<br>parser.add_argument(&quot;-v&quot;, &quot;--verbose&quot;, action=&quot;count&quot;, default=0, help=&quot;more output (-vv for debug)&quot;)<br>parser.add_argument(&quot;--header&quot;, action=&quot;append&quot;, default=[], help=&quot;extra HTTP header (repeatable)&quot;)<br>parser.add_argument(&quot;--version&quot;, action=&quot;version&quot;, version=&quot;%(prog)s 1.4.0&quot;)<br> <br>args = parser.parse_args()<br>print(vars(args))</pre><pre>$ python main.py --dry-run --no-verify -vv --header &#39;Accept:text/html&#39; --header &#39;User-Agent:crawler&#39;<br>{&#39;dry_run&#39;: True, &#39;verify&#39;: False, &#39;verbose&#39;: 2, &#39;header&#39;: [&#39;Accept:text/html&#39;, &#39;User-Agent:crawler&#39;]}</pre><pre>$ python main.py<br>{&#39;dry_run&#39;: False, &#39;verify&#39;: True, &#39;verbose&#39;: 0, &#39;header&#39;: []}</pre><pre>$ python main.py --version<br>crawler 1.4.0</pre><p>One small tip: always pass default=[] together with action=&quot;append&quot;; otherwise, the attribute defaults to None when the option is never used.</p><h4>Step 3: nargs and the optional-value trick</h4><p>nargs controls how many values an argument consumes:</p><ul><li>?: zero or one value.</li><li>*: zero or more values, collected into a list.</li><li>+: one or more values (an error if none is provided).</li><li>An integer, like 2: exactly that many values.</li></ul><p>My favorite use of nargs=&quot;?&quot; is one I used in my crawler: a --reset-state option that works with or without a value. The trick is combining default and const:</p><ul><li>Option not provided: default (None, don&#39;t reset anything).</li><li>Option provided without a value: const (&quot;all&quot;, reset everything).</li><li>Option provided with a value: that value (reset only downloads, for example).</li></ul><pre>import argparse<br> <br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;)<br>parser.add_argument(&quot;inputs&quot;, nargs=&quot;+&quot;, help=&quot;one or more spreadsheets&quot;)<br>parser.add_argument(&quot;--only&quot;, nargs=&quot;*&quot;, default=[], metavar=&quot;ID&quot;, help=&quot;restrict to these item ids&quot;)<br>parser.add_argument(<br>    &quot;--reset-state&quot;,<br>    nargs=&quot;?&quot;,<br>    const=&quot;all&quot;,<br>    default=None,<br>    choices=[&quot;all&quot;, &quot;downloads&quot;],<br>    help=&quot;clear state before running (no value means &#39;all&#39;)&quot;,<br>)<br> <br>args = parser.parse_args()<br>print(vars(args))</pre><pre>$ python main.py a.xlsx b.xlsx<br>{&#39;inputs&#39;: [&#39;a.xlsx&#39;, &#39;b.xlsx&#39;], &#39;only&#39;: [], &#39;reset_state&#39;: None}</pre><pre>$ python main.py a.xlsx --reset-state<br>{&#39;inputs&#39;: [&#39;a.xlsx&#39;], &#39;only&#39;: [], &#39;reset_state&#39;: &#39;all&#39;}</pre><pre>$ python main.py a.xlsx --reset-state downloads --only 10 42<br>{&#39;inputs&#39;: [&#39;a.xlsx&#39;], &#39;only&#39;: [&#39;10&#39;, &#39;42&#39;], &#39;reset_state&#39;: &#39;downloads&#39;}</pre><p>One pitfall: an option with an optional value is greedy. If you put it before a positional argument, it will try to swallow it:</p><pre>$ python main.py --reset-state a.xlsx<br>usage: crawler [-h] [--only [ID ...]] [--reset-state [{all,downloads}]]<br>               inputs [inputs ...]<br>crawler: error: argument --reset-state: invalid choice: &#39;a.xlsx&#39; (choose from all, downloads)<br>$ echo $?<br>2</pre><p>Thanks to choices, mistakes are caught with a clear error message instead of silently resetting a state named a.xlsx. Put options with optional values at the end, or use the --reset-state=all syntax.</p><h4>Step 4: Custom types and validation</h4><p>type accepts any callable that takes a string as input. This is the cleanest place to handle validation. Raise argparse.ArgumentTypeError with a helpful message, and argparse will turn it into a proper CLI error. A plain ValueError also works, but it produces a generic &quot;invalid ... value&quot; message.</p><p>Two validators I use all the time: a positive integer and an existing file using pathlib:</p><pre>import argparse<br>from pathlib import Path<br> <br> <br>def positive_int(value: str) -&gt; int:<br>    number = int(value)  # a ValueError becomes &quot;invalid positive_int value&quot;<br>    if number &lt; 1:<br>        raise argparse.ArgumentTypeError(f&quot;must be &gt;= 1, got {number}&quot;)<br>    return number<br> <br> <br>def existing_file(value: str) -&gt; Path:<br>    path = Path(value)<br>    if not path.is_file():<br>        raise argparse.ArgumentTypeError(f&quot;file not found: {value}&quot;)<br>    return path<br> <br> <br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;)<br>parser.add_argument(&quot;--input&quot;, type=existing_file, required=True, help=&quot;source spreadsheet&quot;)<br>parser.add_argument(&quot;--retries&quot;, type=positive_int, default=3, help=&quot;max attempts per file&quot;)<br> <br>args = parser.parse_args()<br>print(vars(args))</pre><pre>$ python main.py --input items.xlsx --retries 5<br>{&#39;input&#39;: PosixPath(&#39;items.xlsx&#39;), &#39;retries&#39;: 5}</pre><p>Note that args.input is already a Path object, not a string. The rest of your code receives clean, validated values.</p><pre>$ python main.py --input main.py --retries 0<br>usage: crawler [-h] --input INPUT [--retries RETRIES]<br>crawler: error: argument --retries: must be &gt;= 1, got 0<br>$ echo $?<br>2</pre><pre>$ python main.py --input main.py --retries abc<br>usage: crawler [-h] --input INPUT [--retries RETRIES]<br>crawler: error: argument --retries: invalid positive_int value: &#39;abc&#39;<br>$ echo $?<br>2</pre><pre>$ python main.py --input missing.xlsx<br>usage: crawler [-h] --input INPUT [--retries RETRIES]<br>crawler: error: argument --input: file not found: missing.xlsx<br>$ echo $?<br>2</pre><h4>Step 5: Argument groups and mutually exclusive options</h4><p>As a CLI grows, a long flat list of options in help gets hard to read. Argument groups organize the help output into sections. They change only the display, not the parsing.</p><p>Mutually exclusive groups are different: they affect parsing. argparse rejects the command if more than one option from the group is used. -q (--quiet) and -v (--verbose) together make no sense, so let&#39;s forbid their use together.</p><pre>import argparse<br><br><br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;)<br> <br>storage = parser.add_argument_group(&quot;storage&quot;, &quot;where downloaded files go&quot;)<br>storage.add_argument(&quot;--bucket&quot;, default=&quot;docs&quot;, help=&quot;S3 bucket for storage&quot;)<br> <br>network = parser.add_argument_group(&quot;network&quot;)<br>network.add_argument(&quot;--retries&quot;, type=int, default=3, help=&quot;max attempts per file&quot;)<br> <br>output = parser.add_mutually_exclusive_group()<br>output.add_argument(&quot;-q&quot;, &quot;--quiet&quot;, action=&quot;store_true&quot;, help=&quot;print errors only&quot;)<br>output.add_argument(&quot;-v&quot;, &quot;--verbose&quot;, action=&quot;store_true&quot;, help=&quot;print debug messages&quot;)<br> <br>args = parser.parse_args()<br>print(vars(args))</pre><pre>$ python main.py --help<br>usage: crawler [-h] [--bucket BUCKET] [--retries RETRIES] [-q | -v]<br> <br>options:<br>  -h, --help         show this help message and exit<br>  -q, --quiet        print errors only<br>  -v, --verbose      print debug messages<br> <br>storage:<br>  where downloaded files go<br> <br>  --bucket BUCKET    S3 bucket for storage<br> <br>network:<br>  --retries RETRIES  max attempts per file</pre><p>The usage line shows [-q | -v], immediately telling users that these options cannot be combined. And if they try to use both:</p><pre>$ python main.py -q -v<br>usage: crawler [-h] [--bucket BUCKET] [--retries RETRIES] [-q | -v]<br>crawler: error: argument -v/--verbose: not allowed with argument -q/--quiet<br>$ echo $?<br>2</pre><h4>Step 6: Subcommands</h4><p>My crawler does several different jobs, like git has git commit and git push. Each job needs different options. Let’s start with two: download and sync. That’s what subparsers are for.</p><p>Two details matter here:</p><ul><li>dest=&quot;command&quot; stores the selected subcommand name in args.command.</li><li>required=True makes the subcommand mandatory. Without it, running the crawler without a command silently succeeds and does nothing.</li></ul><pre>import argparse<br> <br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;, description=&quot;Document crawler&quot;)<br>subparsers = parser.add_subparsers(dest=&quot;command&quot;, required=True, metavar=&quot;COMMAND&quot;)<br> <br>download_cmd = subparsers.add_parser(&quot;download&quot;, help=&quot;download the listed files&quot;)<br>download_cmd.add_argument(&quot;--retries&quot;, type=int, default=3, help=&quot;max attempts per file&quot;)<br> <br>sync_cmd = subparsers.add_parser(&quot;sync&quot;, help=&quot;push downloaded data to the API&quot;)<br>sync_cmd.add_argument(&quot;--api-url&quot;, required=True, help=&quot;API endpoint URL&quot;)<br> <br>args = parser.parse_args()<br>print(vars(args))</pre><pre>$ python main.py --help<br>usage: crawler [-h] COMMAND ...<br> <br>Document crawler<br> <br>positional arguments:<br>  COMMAND<br>    download  download the listed files<br>    sync      push downloaded data to the API<br> <br>options:<br>  -h, --help  show this help message and exit</pre><pre>$ python main.py download --retries 5<br>{&#39;command&#39;: &#39;download&#39;, &#39;retries&#39;: 5}</pre><p>Each subcommand has its own help:</p><pre>$ python main.py sync --help<br>usage: crawler sync [-h] --api-url API_URL<br> <br>options:<br>  -h, --help         show this help message and exit<br>  --api-url API_URL  API endpoint URL</pre><p>And without a command:</p><pre>$ python main.py<br>usage: crawler [-h] COMMAND ...<br>crawler: error: the following arguments are required: COMMAND<br>$ echo $?<br>2</pre><h4>Step 7: Parent parsers and set_defaults dispatch</h4><p>Here’s where argparse really shines when building a real-world tool. This is the pattern I use in my crawler.</p><p>Several subcommands need the same options: both download and sync write to a log and require a bucket. Copying add_argument() calls into each subcommand is a recipe for configuration drift. Instead, define the shared options once in a parent parser and pass it using parents=[...].</p><p>Parent parsers must use add_help=False. Otherwise, each parser adds its own -h / --help option, causing a conflict when they are combined.</p><p>The second trick is set_defaults(func=...). Each subcommand stores its handler function in the Namespace, so dispatch becomes a single line: args.func(args), without an if/elif chain checking the command name.</p><pre>import argparse<br> <br># Shared options. add_help=False avoids a duplicate -h in every child.<br>common = argparse.ArgumentParser(add_help=False)<br>common.add_argument(&quot;--log-file&quot;, default=&quot;crawler.log&quot;, help=&quot;log file path&quot;)<br>common.add_argument(&quot;--bucket&quot;, required=True, help=&quot;S3 bucket for storage&quot;)<br> <br> <br>def command_download(args):<br>    print(f&quot;download: into s3://{args.bucket}, {args.retries} retries, log={args.log_file}&quot;)<br> <br> <br>def command_sync(args):<br>    print(f&quot;sync: s3://{args.bucket} -&gt; {args.api_url}, log={args.log_file}&quot;)<br> <br> <br>parser = argparse.ArgumentParser(prog=&quot;crawler&quot;)<br>subparsers = parser.add_subparsers(dest=&quot;command&quot;, required=True, metavar=&quot;COMMAND&quot;)<br> <br>download_cmd = subparsers.add_parser(&quot;download&quot;, parents=[common], help=&quot;download files&quot;)<br>download_cmd.add_argument(&quot;--retries&quot;, type=int, default=3, help=&quot;max attempts per file&quot;)<br>download_cmd.set_defaults(func=command_download)<br> <br>sync_cmd = subparsers.add_parser(&quot;sync&quot;, parents=[common], help=&quot;push data to the API&quot;)<br>sync_cmd.add_argument(&quot;--api-url&quot;, required=True, help=&quot;API endpoint URL&quot;)<br>sync_cmd.set_defaults(func=command_sync)<br> <br>args = parser.parse_args()<br>args.func(args)</pre><pre>$ python main.py download --bucket docs --retries 5<br>download: into s3://docs, 5 retries, log=crawler.log</pre><pre>$ python main.py sync --bucket docs --api-url https://api.example.com/v1<br>sync: s3://docs -&gt; https://api.example.com/v1, log=crawler.log</pre><p>The sync help output includes the inherited options, as if we had defined them directly in the subcommand:</p><pre>$ python main.py sync --help<br>usage: crawler sync [-h] [--log-file LOG_FILE] --bucket BUCKET<br>                    --api-url API_URL<br> <br>options:<br>  -h, --help           show this help message and exit<br>  --log-file LOG_FILE  log file path<br>  --bucket BUCKET      S3 bucket for storage<br>  --api-url API_URL    API endpoint URL</pre><h4>Step 8: Secrets, cross-argument validation, files, and exit codes</h4><p>Before we put it all together, a few more features that make a CLI production-ready.</p><p><strong>Environment variables for secrets.</strong> Never force users to type an API key on the command line. It ends up in shell history and is visible to other users in ps. Use the environment as the default:</p><pre>import os<br> <br>parser.add_argument(&quot;--api-key&quot;, default=os.environ.get(&quot;CRAWLER_API_KEY&quot;),<br>                    help=&quot;API key (falls back to $CRAWLER_API_KEY)&quot;)</pre><p>Don’t combine this with required=True: argparse checks required arguments before considering defaults, so the environment variable would be ignored. Validate after parsing instead. Also note that os.environ.get() runs when the parser is built, so in tests, set CRAWLER_API_KEY before calling build_parser().</p><p>parser.error() for rules argparse can&#39;t express.</p><p>This option is required unless that environment variable is set is a rule that spans multiple arguments. Check it after parse_args() and call parser.error(). You get the same usage + error format and exit code 2 as built-in errors. In the final script, I store each subparser using set_defaults(cmd_parser=cmd) so the error displays the correct subcommand usage.</p><p>fromfile_prefix_chars=&quot;@&quot;</p><p>This lets users keep long argument lists in a file, one argument per line, and call crawler @args.txt. Handy for scheduled jobs.</p><p>parse_args(argv) for reuse and testing.</p><p>If you write main(argv=None) and call parser.parse_args(argv), argparse uses sys.argv[1:] when argv is None. But you can also call main([...]) from other Python code, a notebook, or a test.</p><p>Exit codes.</p><p>Return an integer from main() and call sys.exit(main()). By convention:</p><ul><li>0: success.</li><li>1: a failure in your logic (for example, a download that gave up).</li><li>2: argparse usage errors.</li></ul><p>Shell scripts, cron jobs, and CI pipelines rely on these exit codes.</p><h4>The complete script</h4><p>Here’s the final crawler.py. It stays intentionally compact and brings together the key concepts: two subcommands (download and sync), a parent parser for shared options, a custom type, the --reset-state trick, a mutually exclusive group, an environment-variable secret, reading arguments from a file, and set_defaults() for command dispatch.</p><p>The command functions only log what they would do. You can plug your actual implementation into them.</p><pre>import argparse<br>import logging<br>import os<br>import sys<br>from pathlib import Path<br> <br>__version__ = &quot;1.4.0&quot;<br> <br> <br>def existing_file(value: str) -&gt; Path:<br>    path = Path(value)<br>    if not path.is_file():<br>        raise argparse.ArgumentTypeError(f&quot;file not found: {value}&quot;)<br>    return path<br> <br> <br>def command_download(args) -&gt; int:<br>    logging.info(&quot;downloading items from %s into s3://%s&quot;, args.input, args.bucket)<br>    return 0<br> <br> <br>def command_sync(args) -&gt; int:<br>    logging.info(&quot;syncing s3://%s to %s&quot;, args.bucket, args.api_url)<br>    return 0<br> <br> <br>def build_parser() -&gt; argparse.ArgumentParser:<br>    common = argparse.ArgumentParser(add_help=False)<br>    common.add_argument(&quot;--bucket&quot;, required=True, help=&quot;S3 bucket&quot;)<br>    common.add_argument(&quot;--reset-state&quot;, nargs=&quot;?&quot;, const=&quot;all&quot;, choices=[&quot;all&quot;, &quot;downloads&quot;],<br>                        help=&quot;clear state first (no value means &#39;all&#39;)&quot;)<br>    <br>    verbosity = common.add_mutually_exclusive_group()<br>    verbosity.add_argument(&quot;-v&quot;, &quot;--verbose&quot;, action=&quot;store_true&quot;, help=&quot;debug output&quot;)<br>    verbosity.add_argument(&quot;-q&quot;, &quot;--quiet&quot;, action=&quot;store_true&quot;, help=&quot;errors only&quot;)<br> <br>    parser = argparse.ArgumentParser(<br>        prog=&quot;crawler&quot;,<br>        description=&quot;Download public documents and sync them to an API.&quot;,<br>        epilog=&quot;Arguments can also be read from a file: crawler @args.txt&quot;,<br>        fromfile_prefix_chars=&quot;@&quot;,<br>    )<br>    parser.add_argument(&quot;--version&quot;, action=&quot;version&quot;, version=f&quot;%(prog)s {__version__}&quot;)<br>    <br>    subparsers = parser.add_subparsers(dest=&quot;command&quot;, required=True, metavar=&quot;COMMAND&quot;)<br> <br>    download = subparsers.add_parser(&quot;download&quot;, parents=[common], help=&quot;download the listed files&quot;)<br>    download.add_argument(&quot;--input&quot;, type=existing_file, required=True, help=&quot;source spreadsheet&quot;)<br>    download.set_defaults(func=command_download, cmd_parser=download)<br> <br>    sync = subparsers.add_parser(&quot;sync&quot;, parents=[common], help=&quot;push downloaded data to the API&quot;)<br>    sync.add_argument(&quot;--api-url&quot;, required=True, help=&quot;API endpoint URL&quot;)<br>    sync.add_argument(&quot;--api-key&quot;, default=os.environ.get(&quot;CRAWLER_API_KEY&quot;),<br>                      help=&quot;API key (falls back to $CRAWLER_API_KEY)&quot;)<br>    sync.set_defaults(func=command_sync, cmd_parser=sync)<br>    return parser<br> <br> <br>def main(argv: list[str] | None = None) -&gt; int:<br>    args = build_parser().parse_args(argv)<br> <br>    # A rule across arguments and the environment: argparse can&#39;t express it alone<br>    if args.command == &quot;sync&quot; and not args.api_key:<br>        args.cmd_parser.error(&quot;--api-key is required (or set CRAWLER_API_KEY)&quot;)<br> <br>    level = logging.ERROR if args.quiet else logging.DEBUG if args.verbose else logging.INFO<br>    logging.basicConfig(level=level, format=&quot;%(levelname)s %(message)s&quot;)<br>    <br>    if args.reset_state:<br>        logging.info(&quot;resetting state: %s&quot;, args.reset_state)<br>    return args.func(args)<br> <br> <br>if __name__ == &quot;__main__&quot;:<br>    sys.exit(main())</pre><p>Let’s take it for a spin. Top-level help:</p><pre>$ python main.py --help<br>usage: crawler [-h] [--version] COMMAND ...<br> <br>Download public documents and sync them to an API.<br> <br>positional arguments:<br>  COMMAND<br>    download  download the listed files<br>    sync      push downloaded data to the API<br> <br>options:<br>  -h, --help  show this help message and exit<br>  --version   show program&#39;s version number and exit<br> <br>Arguments can also be read from a file: crawler @args.txt</pre><p>The sync help output, including the shared options inherited from the parent parser:</p><pre>$ python main.py sync --help<br>usage: crawler sync [-h] --bucket BUCKET [--reset-state [{all,downloads}]]<br>                    [-v | -q] --api-url API_URL [--api-key API_KEY]<br> <br>options:<br>  -h, --help            show this help message and exit<br>  --bucket BUCKET       S3 bucket<br>  --reset-state [{all,downloads}]<br>                        clear state first (no value means &#39;all&#39;)<br>  -v, --verbose         debug output<br>  -q, --quiet           errors only<br>  --api-url API_URL     API endpoint URL<br>  --api-key API_KEY     API key (falls back to $CRAWLER_API_KEY)</pre><p>A download command using the --reset-state option:</p><pre>$ python main.py download --input main.py --bucket docs --reset-state<br>INFO resetting state: all<br>INFO downloading items from items.xlsx into s3://docs</pre><p>The sync command fails when no API key is provided, displaying our custom parser.error() message:</p><pre>$ python main.py sync --bucket docs --api-url https://api.example.com/v1<br>usage: crawler sync [-h] --bucket BUCKET [--reset-state [{all,downloads}]]<br>                    [-v | -q] --api-url API_URL [--api-key API_KEY]<br>crawler sync: error: --api-key is required (or set CRAWLER_API_KEY)<br>$ echo $?<br>2</pre><p>With the key in the environment:</p><pre>$ CRAWLER_API_KEY=sk-test-123 python main.py sync --bucket docs --api-url https://api.example.com/v1<br>INFO syncing s3://docs to https://api.example.com/v1</pre><p>Reading arguments from a file. Given the following args.txt file:</p><pre>download<br>--input<br>items.xlsx<br>--bucket<br>docs<br>--verbose</pre><pre>$ python main.py @args.txt<br>INFO downloading items from items.xlsx into s3://docs</pre><p>The mutually exclusive group still works inside a subcommand, even though it comes from the parent parser:</p><pre>$ python main.py download --input main.py --bucket docs -q -v<br>usage: crawler download [-h] --bucket BUCKET [--reset-state [{all,downloads}]]<br>                        [-v | -q] --input INPUT<br>crawler download: error: argument -v/--verbose: not allowed with argument -q/--quiet<br>$ echo $?<br>2</pre><p>Because main() accepts argv and returns an exit code, you can also call it directly from Python:</p><pre>from main import main<br> <br>print(&quot;exit code:&quot;, main([&quot;download&quot;, &quot;--input&quot;, &quot;items.xlsx&quot;, &quot;--bucket&quot;, &quot;docs&quot;, &quot;-q&quot;]))</pre><pre>exit code: 0</pre><p>Keep in mind that argparse errors raise SystemExit, not a regular exception. If you call main() from other code, catch it explicitly:</p><pre>from main import main<br> <br>try:<br>    main([&quot;download&quot;])<br>except SystemExit as exc:<br>    print(&quot;SystemExit code:&quot;, exc.code)</pre><pre>usage: main download [-h] --bucket BUCKET [--reset-state [{all,downloads}]]<br>                        [-v | -q] --input INPUT<br>crawler download: error: the following arguments are required: --bucket, --input<br>SystemExit code: 2</pre><h4>When to choose Click or Typer instead</h4><p>argparse covers most needs, but it&#39;s not always the best choice:</p><ul><li><a href="https://typer.tiangolo.com/">Typer</a>: Ideal if you like type hints and want minimal boilerplate. Function signatures become the CLI interface.</li><li><a href="https://click.palletsprojects.com/en/stable/">Click</a>: Great for larger CLIs with nested command groups, plugins, interactive prompts, colored output, or progress bars. Click also provides built-in support for environment variables per option (envvar=) and a test runner.</li><li><a href="https://docs.python.org/3/library/argparse.html">argparse</a>: The right choice when you want zero dependencies, are writing a script that ships with a system Python, or prefer keeping everything explicit in one place.</li></ul><p>For my crawler, argparse was the right choice: a single file, no additional packages, and parent parsers to keep every subcommand consistent.</p><h4>Conclusion</h4><p>argparse is often seen as the &quot;old, verbose&quot; option, but with a few patterns, it scales surprisingly well to real-world tools: ArgumentDefaultsHelpFormatter for informative help messages, custom type functions for validation, nargs=&quot;?&quot; with const for flexible options, argument groups for readable help, and parent parsers with set_defaults(func=...) for clean subcommands.</p><p>Add environment variables for secrets, parser.error() for cross-argument validation, and a main(argv) -&gt; int entry point, and you have a CLI that works well for both humans and automation.</p><p>Next time you write a script that “just needs a couple of flags”, give it a proper CLI from the start. It takes only a few extra lines and saves you and your teammates a lot of guesswork later.</p><h4>References</h4><ul><li><a href="https://docs.python.org/3/library/argparse.html">argparse — Parser for command-line options, arguments and subcommands</a></li><li><a href="https://docs.python.org/3/howto/argparse.html">Argparse Tutorial (Python HOWTO)</a></li><li><a href="https://click.palletsprojects.com/">Click documentation</a></li><li><a href="https://typer.tiangolo.com/">Typer documentation</a></li></ul><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ef8f5a2513c6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using NGINX as an API Gateway]]></title>
            <link>https://medium.com/@johnidouglasmarangon/using-nginx-as-an-api-gateway-5e75ff6101ce?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/5e75ff6101ce</guid>
            <category><![CDATA[api-gateway]]></category>
            <category><![CDATA[reverse-proxy]]></category>
            <category><![CDATA[design-systems]]></category>
            <category><![CDATA[nginx]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Wed, 04 Nov 2026 14:30:26 GMT</pubDate>
            <atom:updated>2026-11-04T14:30:26.002Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/702/0*XIsz_BlgzvEqBv7g.jpeg" /></figure><p>API Gateway is one of the most common concepts in system design, especially when working with complex architectures involving multiple services. As communication between services becomes more challenging, an <strong>API Gateway provides a centralized entry point</strong> for routing requests, handling authentication, and managing traffic across backend services. It’s a fundamental concept in modern system design, particularly in microservices architectures.</p><p>This post builds a tiny system to make that idea concrete. We’ll stand up three independent HTTP services, put a single <a href="https://nginx.org/">NGINX</a> instance in front of them, and wire the whole thing together with Docker Compose. By the end you’ll have one address that fans requests out to three backends — and you’ll be able to read the few lines of config that make it happen.</p><p>You can explore some of the most popular solutions, such as <a href="https://konghq.com/">Kong</a>, <a href="https://aws.amazon.com/api-gateway/">Amazon API Gateway</a>, and <a href="https://traefik.io/">Traefik</a>, to better understand how they work and what each one offers.</p><h4>What an API Gateway is and why it’s useful</h4><p>An API Gateway is a <strong>single entry point</strong> that sits between clients and a set of backend services. Every request lands on the gateway first; the gateway looks at the request and forwards it to whichever service is responsible for that path, then relays the response back.</p><p>That one layer of indirection buys you a lot:</p><ul><li><strong>One address instead of many.</strong> Clients call a single host. They never learn that orders live on one service and users on another, so you’re free to split, merge, or move services without touching the client.</li><li><strong>A natural home for cross-cutting concerns.</strong> Rate limiting, TLS termination, authentication, caching, and logging all belong in one place rather than copied into every service.</li><li><strong>A seam you control.</strong> Because all traffic passes through it, the gateway is where you reshape URLs, add headers, or route a path to a new service during a migration.</li></ul><p>Think of it as a <strong>traffic controller for your backend architecture</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/654/1*eRWwQ4mFHViP9xji-kZkdg.png" /></figure><p>For example, clients can use a single domain:</p><pre>https://api.example.com</pre><p>The gateway routes requests based on their paths:</p><ul><li><strong>GET /users</strong> → Users Service</li><li><strong>POST /payments</strong> → Payments Service</li><li><strong>GET /orders</strong> → Orders Service</li></ul><p>Beyond routing, that’s where you’d add authentication, rate limiting, load balancing, logging, caching, and request transformation, so no single service has to implement them on its own.</p><h4>API Gateway vs. Reverse Proxy</h4><p>These concepts are related, but they aren’t exactly the same.</p><p><strong>Reverse Proxy</strong>: Primarily forwards client requests to backend servers. It can also handle SSL termination, caching, and load balancing. Its primary responsibility is to manage traffic between clients and backend infrastructure.</p><p><strong>API Gateway</strong>: Acts as a centralized entry point for API clients. It routes requests to backend services while applying API-specific policies and managing communication between clients and services.</p><p>NGINX can do both: it’s a reverse proxy at heart, and with a little configuration, it covers most of what an API Gateway does.</p><h4>What we’re going to build</h4><p>Three services, each a self-contained <a href="https://flask.palletsprojects.com/en/stable/">Flask</a> app that returns a little JSON:</p><ul><li>an <strong>orders</strong> service on port 3001</li><li>a <strong>users</strong> service on port 3002</li><li>a <strong>products</strong> service on port 3003</li></ul><p>In front of them, a single <strong>NGINX</strong> gateway listening on port 3000. A request to /orders reaches the orders service, /users reaches the users service, /products reaches the products service, but the caller only ever talks to port 3000.</p><p>Everything runs in containers, orchestrated by <a href="https://docs.docker.com/compose/">Docker Compose</a>, so the whole system comes up with one command and the services can find each other by name.</p><h4>Prerequisites</h4><p>Docker, with the Compose plugin. Recent versions of Docker Desktop and Docker Engine ship Compose as the docker compose subcommand.</p><p>Confirm both with:</p><pre>docker --version<br>docker compose version</pre><p>You don’t need Python or NGINX installed on your machine, they run inside the containers.</p><h4>Set up the project</h4><p>Create a folder for the project and move into it:</p><pre>mkdir nginx-api-gateway<br>cd nginx-api-gateway</pre><p>We’ll add six files here (a seventh, auth.py, comes later): three Flask services, one Dockerfile they share, the nginx.conf, and a compose.yml.</p><h4>Write the three services</h4><p>Each service is deliberately tiny, one route that returns a fixed list. The point isn’t the business logic; it’s having three <em>distinct</em> backends to route between.</p><p>Create orders.py:</p><pre>from flask import Flask<br> <br>app = Flask(__name__)<br> <br>@app.route(&quot;/orders&quot;)<br>def get_orders():<br>    return [<br>        {&quot;id&quot;: 1, &quot;user_id&quot;: 1, &quot;product_id&quot;: 1, &quot;quantity&quot;: 2},<br>        {&quot;id&quot;: 2, &quot;user_id&quot;: 1, &quot;product_id&quot;: 2, &quot;quantity&quot;: 1},<br>        {&quot;id&quot;: 3, &quot;user_id&quot;: 1, &quot;product_id&quot;: 3, &quot;quantity&quot;: 3},<br>    ]<br> <br>if __name__ == &quot;__main__&quot;:<br>    app.run(host=&quot;0.0.0.0&quot;, port=3001, debug=True)</pre><blockquote><em>debug=True is handy while you&#39;re experimenting, but never ship it to production: it enables the interactive debugger.</em></blockquote><p>Create users.py:</p><pre>from flask import Flask<br> <br>app = Flask(__name__)<br> <br>@app.route(&quot;/users&quot;)<br>def get_users():<br>    return [<br>        {&quot;id&quot;: 1, &quot;name&quot;: &quot;John Doe&quot;, &quot;email&quot;: &quot;john.doe@example.com&quot;},<br>        {&quot;id&quot;: 2, &quot;name&quot;: &quot;Jane Smith&quot;, &quot;email&quot;: &quot;jane.smith@example.com&quot;},<br>        {&quot;id&quot;: 3, &quot;name&quot;: &quot;Alice Johnson&quot;, &quot;email&quot;: &quot;alice.johnson@example.com&quot;},<br>    ]<br> <br>if __name__ == &quot;__main__&quot;:<br>    app.run(host=&quot;0.0.0.0&quot;, port=3002, debug=True)</pre><p>Create products.py:</p><pre>from flask import Flask<br> <br>app = Flask(__name__)<br> <br>@app.route(&quot;/products&quot;)<br>def get_products():<br>    return [<br>        {&quot;id&quot;: 1, &quot;name&quot;: &quot;Wireless Mechanical Keyboard&quot;},<br>        {&quot;id&quot;: 2, &quot;name&quot;: &quot;Retro Gamepad Keycap&quot;},<br>        {&quot;id&quot;: 3, &quot;name&quot;: &quot;Mechanical Linear Switch&quot;},<br>    ]<br> <br>if __name__ == &quot;__main__&quot;:<br>    app.run(host=&quot;0.0.0.0&quot;, port=3003, debug=True)</pre><p>Two details carry all the meaning:</p><ul><li>Each app binds to host=&quot;0.0.0.0&quot;, not the default 127.0.0.1. Inside a container, 127.0.0.1 would only accept connections from within that same container, the gateway would never reach it. Binding to 0.0.0.0 lets the service accept connections from other containers on the Docker network.</li><li>Each app listens on its own port. These ports are what the gateway’s upstream blocks will point at.</li></ul><h4>Containerize the services</h4><p>All three services are the same kind of thing, a Python app that needs Flask, so they share one Dockerfile:</p><pre>FROM python:3.12.3-alpine3.19<br><br>WORKDIR /app<br><br>RUN pip install --no-cache-dir Flask<br><br>COPY . /app<br><br>CMD [&quot;python&quot;, &quot;-m&quot;, &quot;http.server&quot;]</pre><p>A couple of choices worth calling out:</p><ul><li>The alpine base image keeps things small. Flask is the only dependency, and --no-cache-dir skips pip&#39;s download cache so the image doesn&#39;t carry it around.</li><li>The CMD here is only a default (it runs the built-in HTTP service). We copy all three service files into the image and then let Compose decide which one each container actually runs, so a <strong>single image backs all three services</strong>. The next step is where that happens.</li></ul><h4>Write the gateway config</h4><p>This is the heart of the article. Create nginx.conf:</p><pre>events { }<br> <br>http {<br>    upstream orders-service {<br>        server orders-api:3001;<br>    }<br>    upstream users-service {<br>        server users-api:3002;<br>    }<br>    upstream products-service {<br>        server products-api:3003;<br>    }<br>    server {<br>        listen 3000;<br>        server_name localhost;<br> <br>        # Headers every backend receives<br>        proxy_set_header Host              $host;<br>        proxy_set_header X-Real-IP         $remote_addr;<br>        proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;<br>        proxy_set_header X-Forwarded-Proto $scheme;<br> <br>        location /orders {<br>            proxy_pass http://orders-service;<br>        }<br>        location /users {<br>            proxy_pass http://users-service;<br>        }<br>        location /products {<br>            proxy_pass http://products-service;<br>        }<br>        location / {<br>            default_type application/json;<br>            return 404 &#39;{&quot;error&quot;:&quot;not found&quot;}\n&#39;;<br>        }<br>    }<br>}</pre><p>Read it top to bottom, and the gateway’s whole job is visible:</p><ul><li><strong>upstream blocks name the backends.</strong> Each upstream groups one (or more) backend servers under a name you choose. orders-api:3001 isn&#39;t an IP address — it&#39;s the <em>service name</em> from compose.yml plus the port the Flask app listens on. Docker&#39;s internal DNS resolves orders-api to the right container. If you later scaled a service to several containers, you&#39;d list them here and NGINX would load-balance across them.</li><li><strong>listen 3000 is the single front door.</strong> This server block is the only thing clients talk to. Everything behind it is private to the Docker network.</li><li><strong>location blocks are the routing table.</strong> Each one matches a URL prefix and hands the request to an upstream with proxy_pass. A request for /orders matches the first location and is proxied to the orders-service upstream; /users and /products work the same way. This mapping from path to backend is exactly what makes NGINX an API Gateway rather than a plain web server. (Prefix matching means /orders would also match /orders-archive; in a real setup you&#39;d use location /orders/ or an exact match.)</li><li><strong>proxy_set_header tells the backend who really called.</strong> Without it, every request a backend sees comes from the gateway. Host $host forwards the host the client asked for; X-Real-IP and X-Forwarded-For carry the client&#39;s IP; X-Forwarded-Proto says whether the client used HTTP or HTTPS. Declared once at the server level, they apply to every location below. (In Flask, werkzeug.middleware.proxy_fix.ProxyFix reads these headers into request.remote_addr.)</li><li><strong>location / is the fallback.</strong> Anything that matches no service gets a JSON 404 straight from the gateway, instead of NGINX&#39;s default HTML page.</li></ul><p>The events { } block is required by NGINX&#39;s config grammar even when empty; it&#39;s where connection-handling settings would go if we needed them.</p><h4>Wire it together with Compose</h4><p>Now the file that starts everything and lets the names resolve. Create compose.yml:</p><pre>services:<br>  nginx:<br>    image: nginx:alpine<br>    volumes:<br>      - ./nginx.conf:/etc/nginx/nginx.conf:ro<br>    ports:<br>      - 3000:3000<br>    restart: on-failure<br>    depends_on:<br>      - orders-api<br>      - users-api<br>      - products-api<br>      - auth-api<br>  orders-api:<br>    build:<br>      context: .<br>    command: python ./orders.py<br>    expose:<br>      - &quot;3001&quot;<br>  users-api:<br>    build:<br>      context: .<br>    command: python ./users.py<br>    expose:<br>      - &quot;3002&quot;<br>  products-api:<br>    build:<br>      context: .<br>    command: python ./products.py<br>    expose:<br>      - &quot;3003&quot;<br>  auth-api:<br>    build:<br>      context: .<br>    command: python ./auth.py<br>    expose:<br>      - &quot;3004&quot;</pre><blockquote><em>auth-api is the small authentication service we&#39;ll write in </em>Going further<em>. If you&#39;re following along step by step, leave these five lines (and </em><em>- auth-api under </em><em>depends_on) out until then.</em></blockquote><p>This is where the earlier pieces lock into place:</p><ul><li><strong>The service names are the hostnames.</strong> orders-api, users-api, and products-api are exactly the names the upstream blocks point at. Compose puts all services on one network and registers each name in Docker&#39;s DNS, so proxy_pass http://orders-service → orders-api:3001 → the right container, with no IP addresses anywhere.</li><li><strong>One image, three </strong><strong>commands.</strong> All three API services use build: context: . — the same Dockerfile — and override the image&#39;s default CMD with command: python ./orders.py (and ./users.py, ./products.py). That&#39;s how one image becomes three different services, and why the Dockerfile&#39;s own CMD didn&#39;t matter.</li><li><strong>Only the gateway publishes a port to the host.</strong> nginx maps 3000:3000, so localhost:3000 reaches it from your machine. The API services use expose instead of ports: their ports are open to other containers on the Docker network, but nothing is published on your machine. The only way in is through the gateway — which is the whole idea. (Watch out for ports: - 3001 with no host side: it doesn&#39;t keep the port private. Docker publishes it on a random host port such as 32770.)</li><li><strong>NGINX starts after its backends.</strong> NGINX resolves the upstream hostnames when it starts, and exits with host not found in upstream if they don&#39;t exist yet. depends_on makes Compose start the API containers first, but it doesn&#39;t wait for them to be ready. If NGINX still loses that race, restart: on-failure simply starts it again a moment later.</li><li><strong>The config is mounted read-only.</strong> ./nginx.conf:/etc/nginx/nginx.conf:ro drops our config into the stock nginx:alpine image. Editing the local file and running docker compose restart nginx is all it takes to change the routing.</li></ul><h4>Run it</h4><p>From the project folder:</p><pre>docker compose up --build</pre><p>Compose builds the shared image, starts all four containers, and streams their logs. Once NGINX is up, hit the gateway — note that every request goes to port 3000, never to 3001, 3002, or 3003:</p><pre>curl http://localhost:3000/orders<br>curl http://localhost:3000/users<br>curl http://localhost:3000/products</pre><p>Each one returns the JSON from its service:</p><pre>// GET /orders<br>[<br>  {&quot;id&quot;: 1, &quot;product_id&quot;: 1, &quot;quantity&quot;: 2, &quot;user_id&quot;: 1},<br>  {&quot;id&quot;: 2, &quot;product_id&quot;: 2, &quot;quantity&quot;: 1, &quot;user_id&quot;: 1},<br>  {&quot;id&quot;: 3, &quot;product_id&quot;: 3, &quot;quantity&quot;: 3, &quot;user_id&quot;: 1}<br>]<br> <br>// GET /users<br>[<br>  {&quot;email&quot;: &quot;john.doe@example.com&quot;, &quot;id&quot;: 1, &quot;name&quot;: &quot;John Doe&quot;},<br>  {&quot;email&quot;: &quot;jane.smith@example.com&quot;, &quot;id&quot;: 2, &quot;name&quot;: &quot;Jane Smith&quot;},<br>  {&quot;email&quot;: &quot;alice.johnson@example.com&quot;, &quot;id&quot;: 3, &quot;name&quot;: &quot;Alice Johnson&quot;}<br>]<br> <br>// GET /products<br>[<br>  {&quot;id&quot;: 1, &quot;name&quot;: &quot;Wireless Mechanical Keyboard&quot;},<br>  {&quot;id&quot;: 2, &quot;name&quot;: &quot;Retro Gamepad Keycap&quot;},<br>  {&quot;id&quot;: 3, &quot;name&quot;: &quot;Mechanical Linear Switch&quot;}<br>]</pre><p>Flask pretty-prints these over several lines and sorts the keys alphabetically. They’re condensed here.</p><p>A path no service owns is answered by the gateway itself:</p><pre>curl -i http://localhost:3000/unknown</pre><pre>HTTP/1.1 404 Not Found<br>Content-Type: application/json<br> <br>{&quot;error&quot;:&quot;not found&quot;}</pre><p>Now prove the backends really are private. Try to skip the gateway and call the orders service directly:</p><pre>curl http://localhost:3001/orders<br># curl: (7) Failed to connect to localhost port 3001 ... Connection refused</pre><pre>docker compose ps</pre><pre>SERVICE        PORTS<br>nginx          0.0.0.0:3000-&gt;3000/tcp<br>orders-api     3001/tcp<br>products-api   3003/tcp<br>users-api      3002/tcp</pre><p>Only nginx has a host mapping (0.0.0.0:3000-&gt;). The services show a bare 3001/tcp: open inside the Docker network, closed to the outside world.</p><p>Three backends, one address. The client asked localhost:3000 for /orders and got orders, with no idea that a separate service produced them. That indirection — a single front door quietly dispatching to many services — is an API Gateway in its simplest working form.</p><h4>Implementing logging</h4><p>With routing in place, the next place a gateway earns its keep is observability. Every request already passes through NGINX, which makes the gateway the one place that sees all of your API traffic, and therefore the natural place to log it.</p><p>A good access log answers the questions you actually ask during an incident: who called, what they asked for, which backend answered, how long it took, and whether it succeeded. NGINX lets you shape exactly those fields with the log_format directive, and structured JSON is the format worth reaching for; it drops straight into tools like the ELK stack, Loki, or CloudWatch without fragile regex parsing.</p><p>Open nginx.conf and add the following inside the http block:</p><pre># A structured JSON access log — one object per request.<br>log_format api_gateway escape=json<br>    &#39;{&#39;<br>        &#39;&quot;timestamp&quot;:&quot;$time_iso8601&quot;,&#39;                        # When the request completed (ISO 8601).<br>        &#39;&quot;client_ip&quot;:&quot;$remote_addr&quot;,&#39;                         # Who made the request.<br>        &#39;&quot;method&quot;:&quot;$request_method&quot;,&#39;                         # GET, POST, PUT, DELETE, …<br>        &#39;&quot;path&quot;:&quot;$uri&quot;,&#39;                                      # Requested path, query string stripped.<br>        &#39;&quot;status&quot;:$status,&#39;                                   # Status NGINX returned to the client.<br>        &#39;&quot;request_time&quot;:$request_time,&#39;                       # Total time to serve the request (seconds).<br>        &#39;&quot;upstream&quot;:&quot;$upstream_addr&quot;,&#39;                        # Which backend handled it.<br>        &#39;&quot;upstream_status&quot;:&quot;$upstream_status&quot;,&#39;               # Status the backend returned.<br>        &#39;&quot;upstream_response_time&quot;:&quot;$upstream_response_time&quot;,&#39; # How long the backend took (seconds).<br>        &#39;&quot;bytes_sent&quot;:$bytes_sent&#39;                            # Response size in bytes.<br>    &#39;}&#39;;<br> <br> <br># Use that format for the access log, and keep errors at warn and above.<br>access_log /var/log/nginx/access.log api_gateway;<br>error_log  /var/log/nginx/error.log warn;</pre><p>escape=json tells NGINX to escape each value for JSON, so a quote or backslash in a path can&#39;t break the surrounding object.</p><p>The pairing of client-side and upstream fields is what makes this log useful for a gateway specifically. status and request_time describe what the <em>client</em> experienced; upstream_status and upstream_response_time describe what the <em>backend</em> did. When they disagree — a 502 to the client with an empty upstream_status, or a fast gateway time wrapped around a slow upstream time — the log already tells you which side of the gateway the problem is on.</p><p>The official nginx:alpine image links /var/log/nginx/access.log and error.log to the container&#39;s stdout and stderr, so these JSON lines show up live in docker compose logs. That&#39;s exactly what we want here, and it&#39;s how most container platforms expect logs: written to stdout and collected by the platform, rather than kept as files inside the container.</p><p>No compose.yml change is needed. Restart the gateway so NGINX picks up the new config:</p><pre>docker compose restart nginx</pre><p>Send a few requests through the gateway, including one to a path that doesn’t exist so you can see a 404 recorded too:</p><pre>curl http://localhost:3000/orders<br>curl http://localhost:3000/users<br>curl http://localhost:3000/products<br>curl http://localhost:3000/unknown</pre><p>Then watch the gateway’s logs:</p><pre>docker compose logs -f nginx</pre><p>Each request becomes one JSON line:</p><pre>{&quot;timestamp&quot;:&quot;2026-11-02T03:38:05+00:00&quot;,&quot;client_ip&quot;:&quot;172.18.0.1&quot;,&quot;method&quot;:&quot;GET&quot;,&quot;path&quot;:&quot;/orders&quot;,&quot;status&quot;:200,&quot;request_time&quot;:0.002,&quot;upstream&quot;:&quot;172.18.0.4:3001&quot;,&quot;upstream_status&quot;:&quot;200&quot;,&quot;upstream_response_time&quot;:&quot;0.002&quot;,&quot;bytes_sent&quot;:395}<br>{&quot;timestamp&quot;:&quot;2026-11-02T03:38:05+00:00&quot;,&quot;client_ip&quot;:&quot;172.18.0.1&quot;,&quot;method&quot;:&quot;GET&quot;,&quot;path&quot;:&quot;/users&quot;,&quot;status&quot;:200,&quot;request_time&quot;:0.002,&quot;upstream&quot;:&quot;172.18.0.2:3002&quot;,&quot;upstream_status&quot;:&quot;200&quot;,&quot;upstream_response_time&quot;:&quot;0.002&quot;,&quot;bytes_sent&quot;:418}<br>{&quot;timestamp&quot;:&quot;2026-11-02T03:38:05+00:00&quot;,&quot;client_ip&quot;:&quot;172.18.0.1&quot;,&quot;method&quot;:&quot;GET&quot;,&quot;path&quot;:&quot;/products&quot;,&quot;status&quot;:200,&quot;request_time&quot;:0.001,&quot;upstream&quot;:&quot;172.18.0.3:3003&quot;,&quot;upstream_status&quot;:&quot;200&quot;,&quot;upstream_response_time&quot;:&quot;0.002&quot;,&quot;bytes_sent&quot;:341}<br>{&quot;timestamp&quot;:&quot;2026-11-02T03:38:05+00:00&quot;,&quot;client_ip&quot;:&quot;172.18.0.1&quot;,&quot;method&quot;:&quot;GET&quot;,&quot;path&quot;:&quot;/unknown&quot;,&quot;status&quot;:404,&quot;request_time&quot;:0.000,&quot;upstream&quot;:&quot;&quot;,&quot;upstream_status&quot;:&quot;&quot;,&quot;upstream_response_time&quot;:&quot;&quot;,&quot;bytes_sent&quot;:308}</pre><p>The three valid paths were proxied to three different containers (upstream shows each one&#39;s internal IP and port). /unknown was answered by NGINX itself, so its upstream fields are empty because no backend was ever involved. client_ip is the Docker network&#39;s gateway address, because your curl reaches the container through Docker&#39;s port mapping. Behind a real load balancer it&#39;s the load balancer&#39;s address, which is the problem the rate-limiting caution below deals with. Your IPs and timestamps will differ. Since the log is JSON, docker compose logs nginx --no-log-prefix | grep &#39;^{&#39; | jq . gives you a readable, filterable view.</p><p><strong>One architectural caution:</strong> never log sensitive data — passwords, Authorization headers, JWTs, or personal information — into these files. In production, pair logging with rotation, a retention policy, and shipping to a central store.</p><p>That last point is the real payoff. In a microservices system the gateway log is the one timeline every request appears in, so centralizing it gives you a single place to start when you’re trying to tell a traffic spike from a failing backend.</p><h4>Implementing rate limiting</h4><p>Routing and logging make the gateway useful; rate limiting makes it defensive. Because every request passes through NGINX before it reaches a backend, the gateway is the one place you can cap how fast any single client hits your APIs — protecting the services behind it from bursts, scrapers, and runaway retry loops without any of them having to implement a limiter of their own.</p><p>NGINX does this with the leaky-bucket algorithm, exposed through two directives: limit_req_zone defines <em>who</em> you’re counting and <em>how fast</em> they’re allowed, and limit_req switches that limit on where you want it enforced.</p><p>Open nginx.conf and add the zone definition inside the http block:</p><pre># Count requests per client IP; keep the counters in a 10 MB shared zone,<br># and allow a sustained rate of 5 requests per second per client.<br>limit_req_zone $binary_remote_addr zone=api_limit:10m rate=5r/s;<br> <br># Reject excess requests with 429 Too Many Requests (default is 503).<br>limit_req_status 429;</pre><p>Then turn the limit on in the server block, above the location blocks, so it covers every route at once:</p><pre>server {<br>    listen 3000;<br>    server_name localhost;<br> <br>    limit_req zone=api_limit burst=10 nodelay;  # .. add this line<br> <br>    location /orders {<br>        # … unchanged …<br>    }<br>    location /users {<br>        # … unchanged …<br>    }<br>    location /products {<br>        # … unchanged …<br>    }<br>}</pre><p>Each piece maps to one decision about the limiter:</p><ul><li>$binary_remote_addr is the key — the client’s IP in its compact binary form. Counting per IP means one noisy client can’t starve everyone else. The binary form matters because it’s small: a 10m zone holds state for roughly 160,000 distinct addresses.</li><li>rate=5r/s is the steady drain rate of the bucket: five requests per second per client, long-term. You can also express it per minute (rate=30r/m) for slower APIs.</li><li>burst=10 is the bucket’s size — a cushion of ten requests that absorbs a short spike instead of rejecting it outright. Real clients are bursty; this keeps a legitimate flurry from tripping the limit.</li><li>nodelay serves those burst requests immediately rather than queuing them to match the rate. Without it, NGINX would accept the burst but drip it out one every 200 ms, which hurts latency. With it, the burst goes through at once and only genuinely excess traffic gets the 429.</li><li>limit_req_status 429 returns the status clients actually expect for throttling, so their retry/backoff logic behaves. The NGINX default here is 503, which reads as a server fault rather than “slow down.”</li></ul><p>No compose.yml change is needed — this is pure config. Restart the gateway so NGINX reloads it.</p><h4>Run it</h4><pre>docker compose restart nginx</pre><p>Then hammer one route faster than the limit allows and print just the status codes:</p><pre>for i in $(seq 1 20); do<br>  curl -s -o /dev/null -w &quot;%{http_code}\n&quot; http://localhost:3000/orders<br>done</pre><p>Here’s what I got:</p><pre>200 200 200 200 200 200 200 200 200 200 200 429 429 429 429 429 429 429 429 429</pre><p>Eleven 200s: one for the rate itself plus the ten-request burst. After that, every request gets 429. Wait a couple of seconds and the bucket drains, so requests succeed again. The limit is per client, not per route: right after that loop, curl http://localhost:3000/users is throttled too, because the zone counts <em>your IP</em> across every location.</p><p>By default the 429 body is an NGINX HTML page. To make it API-friendly, add this to the server block:</p><pre>error_page 429 = @too_many_requests;<br>location @too_many_requests {<br>    default_type application/json;<br>    add_header Retry-After 1 always;<br>    return 429 &#39;{&quot;error&quot;:&quot;too many requests&quot;}\n&#39;;<br>}</pre><pre>HTTP/1.1 429 Too Many Requests<br>Retry-After: 1<br> <br>{&quot;error&quot;:&quot;too many requests&quot;}</pre><p>To test:</p><pre>docker compose restart nginx<br>for i in $(seq 1 20); do curl -s -i localhost:3000/orders | head -1; done</pre><pre>...<br>HTTP/1.1 200 OK<br>HTTP/1.1 200 OK<br>HTTP/1.1 429 Too Many Requests<br>HTTP/1.1 429 Too Many Requests<br>HTTP/1.1 429 Too Many Requests<br>...</pre><p>The Retry-After header tells a well-behaved client how long to back off. Because the JSON access log from the previous section records $status, every rejection shows up there too, which makes a limit that’s set too tight easy to spot in your logs rather than only in user complaints.</p><p><strong>One architectural caution:</strong> $binary_remote_addr is the IP NGINX sees directly. Put this gateway behind a cloud load balancer or another proxy and that becomes the proxy’s IP — so every client is counted as one, and you throttle all of them together. In that setup, configure the realip module (or key the zone on a trusted X-Forwarded-For value) so the limiter counts the true client. NGINX also offers a sibling directive, limit_conn, for capping <em>concurrent</em> connections rather than request rate, when that’s the resource you need to protect.</p><p>A single front door is also a single choke point you control. With one limit_req_zone and one limit_req, every service behind the gateway inherits the same protection for free — and you can still tighten a specific route by dropping a stricter limit_req inside its own location block.</p><h4>Implementing caching</h4><p>Because the gateway sees every response, it can keep copies of the ones that rarely change and serve repeats itself, sparing the backend entirely. Declare a cache store in http :</p><pre># Response cache: 10 MB of keys, up to 100 MB on disk, drop entries unused for 60 min.<br># Must live in the http block (not inside server).<br>proxy_cache_path /var/cache/nginx keys_zone=api_cache:10m max_size=100m inactive=60m;<br></pre><p>Declare an endpoint cache properties in location :</p><pre>location /products {<br>    proxy_cache api_cache;<br>    proxy_cache_valid 200 30s;                        # cache 200 responses for 30 seconds<br>    # Expose the cache result in the response so you can watch caching work:<br>    #   MISS    - nothing cached yet; fetched from products-api and stored (if 200)<br>    #   HIT     - fresh copy (&lt; 30s old) served from cache; backend not called<br>    #   EXPIRED - cached copy older than 30s; refetched from backend and re-cached<br>    # Note: only 200s are cached, and without &quot;always&quot; this header is not<br>    # added to error responses (4xx/5xx).<br>    add_header X-Cache-Status $upstream_cache_status;<br>    proxy_pass http://products-service;<br>}</pre><p>Restart NGINX and watch the header:</p><pre>for i in 1 2 3; do curl -s -o /dev/null -D - http://localhost:3000/products | grep X-Cache-Status; done</pre><pre>X-Cache-Status: MISS<br>X-Cache-Status: HIT<br>X-Cache-Status: HIT</pre><p>The first request fills the cache (MISS). For the next 30 seconds, identical requests come straight from NGINX (HIT) and never touch products-service. Run it again after 30 seconds and the first line reads EXPIRED, meaning NGINX fetched a fresh copy. Cache only safe, idempotent responses — read-only GETs — and never per-user or authenticated data.</p><h4>Implementing authentication</h4><p>The gateway is the right place to check credentials once, before any backend is involved. NGINX’s auth_request module (built into the official image) does this by asking another service: for every protected request it sends a small internal subrequest to an auth endpoint. If that endpoint answers 2xx, the request goes through. If it answers 401 or 403, the request stops at the gateway.</p><p>Let’s build that auth service in the same style as the others. Create auth.py:</p><pre>from flask import Flask, request<br> <br>app = Flask(__name__)<br> <br># Known API clients: key -&gt; client name<br>API_CLIENTS = {<br>    &quot;secret-key-1&quot;: &quot;mobile-app&quot;,<br>    &quot;secret-key-2&quot;: &quot;web-app&quot;,<br>}<br> <br>@app.route(&quot;/verify&quot;)<br>def verify():<br>    client = API_CLIENTS.get(request.headers.get(&quot;X-API-Key&quot;, &quot;&quot;))<br>    if client is None:<br>        return {&quot;error&quot;: &quot;missing or invalid API key&quot;}, 401<br>    return &quot;&quot;, 200, {&quot;X-API-Client&quot;: client}<br> <br>if __name__ == &quot;__main__&quot;:<br>    app.run(host=&quot;0.0.0.0&quot;, port=3004, debug=True)</pre><p>/verify doesn&#39;t care about the original request. It only looks at the X-API-Key header, finds the client it belongs to, and answers 200 with the client&#39;s name in an X-API-Client header, or 401 if the key is missing or unknown. In a real system this is where you&#39;d validate a JWT or look the key up in a database. The gateway contract stays the same.</p><p>Add it to compose.yml like the other services. It runs from the same image with its own command, and it&#39;s exposed only on the Docker network:</p><pre>auth-api:<br>    build:<br>      context: .<br>    command: python ./auth.py<br>    expose:<br>      - &quot;3004&quot;</pre><p>Also add - auth-api to the nginx service&#39;s depends_on.</p><p>Now wire it into nginx.conf. In the http block, add an upstream next to the others:</p><pre>upstream auth-service {<br>    server auth-api:3004;<br>}</pre><p>In the server block, add the internal auth location, a JSON 401, and protect /orders:</p><pre># next to the other proxy_set_header lines at the server level<br>proxy_set_header X-API-Client $api_client;   # set by auth_request_set; empty = not sent<br> <br>error_page 401 = @unauthorized;<br>location @unauthorized {<br>    default_type application/json;<br>    return 401 &#39;{&quot;error&quot;:&quot;missing or invalid API key&quot;}\n&#39;;<br>}<br> <br>location /orders {<br>    auth_request /_auth;                                       # ask auth-service first<br>    auth_request_set $api_client $upstream_http_x_api_client;  # read its X-API-Client header<br>    proxy_pass http://orders-service;<br>}<br> <br>location = /_auth {<br>    internal;                                      # unreachable from outside<br>    proxy_pass http://auth-service/verify;<br>    proxy_pass_request_body off;                   # auth only needs headers…<br>    proxy_set_header Content-Length &quot;&quot;;            # …so don&#39;t send a body<br>    proxy_set_header X-API-Key $http_x_api_key;    # the key we&#39;re checking<br>}</pre><p>What each piece does:</p><ul><li>auth_request /_auth runs the subrequest before proxying. Only a 2xx lets /orders reach orders-service.</li><li>location = /_auth is internal, so clients can&#39;t call it directly (curl localhost:3000/_auth returns 404). It forwards only headers. Dropping the body keeps the check cheap even for large POSTs.</li><li>auth_request_set copies the auth service&#39;s X-API-Client response header into $api_client, and proxy_set_header X-API-Client passes it on. The backend learns <em>who</em> called without ever seeing the key.</li><li>proxy_set_header X-API-Client sits at the server level for two reasons. First, an NGINX gotcha: a proxy_set_header inside a location makes that location drop <strong>all</strong> the server-level ones (Host, X-Forwarded-For, …). Second, it overwrites anything the client sends in that header. Where $api_client is empty, NGINX doesn&#39;t send the header at all, so a client can&#39;t spoof it.</li><li>error_page 401 turns the rejection into JSON, like our 404 and 429.</li></ul><p>To show that the backend really receives the client name, add one log line to orders.py:</p><pre>from flask import Flask, request<br>...<br>@app.route(&quot;/orders&quot;)<br>def get_orders():<br>    app.logger.info(&quot;request from API client: %s&quot;, request.headers.get(&quot;X-API-Client&quot;))<br>    return [ ... ]</pre><p>Optionally, record the client in the gateway’s access log too by adding &#39;&quot;api_client&quot;:&quot;$api_client&quot;,&#39; to the log_format.</p><p>Rebuild (we added a service and changed orders.py) and try it:</p><pre>docker compose up --build<br> <br>curl -i http://localhost:3000/orders<br># HTTP/1.1 401 Unauthorized<br># Content-Type: application/json<br># {&quot;error&quot;:&quot;missing or invalid API key&quot;}<br> <br>curl -i -H &quot;X-API-Key: wrong&quot; http://localhost:3000/orders<br># HTTP/1.1 401 Unauthorized<br># {&quot;error&quot;:&quot;missing or invalid API key&quot;}<br> <br>curl -i -H &quot;X-API-Key: secret-key-1&quot; http://localhost:3000/orders<br># HTTP/1.1 200 OK   (plus the orders JSON)</pre><p>The orders service logs who called, and it doesn’t trust a spoofed header. A request with -H &quot;X-API-Key: secret-key-1&quot; -H &quot;X-API-Client: admin&quot; still logs mobile-app:</p><pre>docker compose logs orders-api | grep &quot;API client&quot;</pre><pre>orders-api-1  | [2026-11-02 03:54:24,241] INFO in orders: request from API client: mobile-app<br>orders-api-1  | [2026-11-02 03:54:24,250] INFO in orders: request from API client: web-app</pre><p>And the gateway log tells the whole story. The rejected calls never got an upstream, and the accepted ones carry the client name:</p><pre>{&quot;timestamp&quot;:&quot;2026-11-02T03:54:24+00:00&quot;,&quot;client_ip&quot;:&quot;172.18.0.1&quot;,&quot;method&quot;:&quot;GET&quot;,&quot;path&quot;:&quot;/orders&quot;,&quot;status&quot;:401,&quot;request_time&quot;:0.003,&quot;upstream&quot;:&quot;&quot;,&quot;upstream_status&quot;:&quot;&quot;,&quot;upstream_response_time&quot;:&quot;&quot;,&quot;api_client&quot;:&quot;&quot;,&quot;cache&quot;:&quot;&quot;,&quot;bytes_sent&quot;:203}<br>{&quot;timestamp&quot;:&quot;2026-11-02T03:54:24+00:00&quot;,&quot;client_ip&quot;:&quot;172.18.0.1&quot;,&quot;method&quot;:&quot;GET&quot;,&quot;path&quot;:&quot;/orders&quot;,&quot;status&quot;:200,&quot;request_time&quot;:0.003,&quot;upstream&quot;:&quot;172.18.0.4:3001&quot;,&quot;upstream_status&quot;:&quot;200&quot;,&quot;upstream_response_time&quot;:&quot;0.002&quot;,&quot;api_client&quot;:&quot;mobile-app&quot;,&quot;cache&quot;:&quot;&quot;,&quot;bytes_sent&quot;:395}</pre><p>Because the check lives in the gateway, protecting another route is one line: add auth_request /_auth; (and the auth_request_set) to its location. Every subrequest is a network hop, so in production you&#39;d usually cache auth results briefly or validate signed tokens (JWTs) without a call at all. For a quick internal tool, the lighter options are auth_basic or a map of keys right in nginx.conf.</p><h4>Implementing load balancing</h4><p>You already have the mechanism: the upstream block. Give it more than one backend and NGINX spreads requests across them. The catch under Compose is that NGINX resolves orders-api once at startup, so containers you add later are invisible to it. Recent NGINX versions (1.27.3+, which nginx:alpine ships) fix that with resolve: point the upstream at Docker&#39;s DNS server and NGINX keeps re-resolving the name, add the code bellow in the http block:</p><pre># DNS server NGINX uses to look up hostnames at runtime.<br>#   127.0.0.11 - Docker&#39;s embedded DNS; resolves service names (orders-api, users-api, …)<br>#   valid=10s  - cache answers for 10s, so a restarted container&#39;s new IP is picked up quickly<br>#   ipv6=off   - only ask for IPv4 (A) records; Docker networks here are IPv4-only<br># Note: hostnames in &quot;upstream&quot; blocks are resolved once at startup. The resolver is<br># used for runtime lookups, e.g. proxy_pass with variables or &quot;server ... resolve&quot;.<br>resolver 127.0.0.11 valid=10s ipv6=off;<br> <br>upstream orders-service {<br>    zone orders-service 64k;              # shared memory, required by `resolve`<br>    server orders-api:3001 resolve;       # one name → every replica behind it<br>}</pre><p>Now scale the service. NGINX doesn’t need a restart:</p><pre>docker compose up --scale orders-api=3<br>for i in 1 2 3 4 5 6; do curl -s -H &quot;X-API-Key: secret-key-1&quot; http://localhost:3000/orders; done<br>docker compose logs nginx --no-log-prefix | grep &#39;&quot;path&quot;:&quot;/orders&quot;&#39; | tail -6</pre><p>The upstream field in the access log shows the requests rotating across three containers:</p><pre>&quot;upstream&quot;:&quot;172.18.0.3:3001&quot;<br>&quot;upstream&quot;:&quot;172.18.0.7:3001&quot;<br>&quot;upstream&quot;:&quot;172.18.0.6:3001&quot;<br>&quot;upstream&quot;:&quot;172.18.0.3:3001&quot;<br>&quot;upstream&quot;:&quot;172.18.0.7:3001&quot;<br>&quot;upstream&quot;:&quot;172.18.0.6:3001&quot;</pre><p>This works because the services use expose rather than fixed host ports. Three replicas can&#39;t all bind 3001:3001 on your machine. The default strategy is round-robin. Add least_conn; to the upstream for requests of uneven cost, or ip_hash; to pin each client to one backend when you need sticky sessions.</p><h4>The complete nginx.conf and compose.yml</h4><p>Here’s the final config with everything from this post in one place, ready to copy.</p><pre>events { }<br> <br>http {<br><br>    # DNS server NGINX uses to look up hostnames at runtime.<br>    #   127.0.0.11 - Docker&#39;s embedded DNS; resolves service names (orders-api, users-api, …)<br>    #   valid=10s  - cache answers for 10s, so a restarted container&#39;s new IP is picked up quickly<br>    #   ipv6=off   - only ask for IPv4 (A) records; Docker networks here are IPv4-only<br>    # Note: hostnames in &quot;upstream&quot; blocks are resolved once at startup. The resolver is<br>    # used for runtime lookups, e.g. proxy_pass with variables or &quot;server ... resolve&quot;.<br>    resolver 127.0.0.11 valid=10s ipv6=off;<br><br>    # Count requests per client IP; keep the counters in a 10 MB shared zone,<br>    # and allow a sustained rate of 5 requests per second per client.<br>    limit_req_zone $binary_remote_addr zone=api_limit:10m rate=5r/s;<br>    <br>    # Reject excess requests with 429 Too Many Requests (default is 503).<br>    limit_req_status 429;<br><br>    # A structured JSON access log - one object per request.<br>    log_format api_gateway escape=json<br>        &#39;{&#39;<br>            &#39;&quot;timestamp&quot;:&quot;$time_iso8601&quot;,&#39;                        # When the request completed (ISO 8601).<br>            &#39;&quot;client_ip&quot;:&quot;$remote_addr&quot;,&#39;                         # Who made the request.<br>            &#39;&quot;method&quot;:&quot;$request_method&quot;,&#39;                         # GET, POST, PUT, DELETE, …<br>            &#39;&quot;path&quot;:&quot;$uri&quot;,&#39;                                      # Requested path, query string stripped.<br>            &#39;&quot;status&quot;:$status,&#39;                                   # Status NGINX returned to the client.<br>            &#39;&quot;request_time&quot;:$request_time,&#39;                       # Total time to serve the request (seconds).<br>            &#39;&quot;upstream&quot;:&quot;$upstream_addr&quot;,&#39;                        # Which backend handled it.<br>            &#39;&quot;upstream_status&quot;:&quot;$upstream_status&quot;,&#39;               # Status the backend returned.<br>            &#39;&quot;upstream_response_time&quot;:&quot;$upstream_response_time&quot;,&#39; # How long the backend took (seconds).<br>            &#39;&quot;bytes_sent&quot;:$bytes_sent&#39;                            # Response size in bytes.<br>        &#39;}&#39;;<br>    <br>    <br>    # Use that format for the access log, and keep errors at warn and above.<br>    access_log /var/log/nginx/access.log api_gateway;<br>    error_log  /var/log/nginx/error.log warn;<br><br>    # Response cache: 10 MB of keys, up to 100 MB on disk, drop entries unused for 60 min.<br>    # Must live in the http block (not inside server).<br>    proxy_cache_path /var/cache/nginx keys_zone=api_cache:10m max_size=100m inactive=60m;<br><br>    upstream orders-service {<br>        zone orders-service 64k;              # shared memory, required by `resolve`<br>        server orders-api:3001 resolve;       # one name → every replica behind it<br>    }<br>        <br>    upstream users-service {<br>        server users-api:3002;<br>    }<br>    <br>    upstream products-service {<br>        server products-api:3003;<br>    }<br><br>    upstream auth-service {<br>        server auth-api:3004;<br>    }<br>    <br>    server {<br>        listen 3000;<br>        server_name localhost;<br><br>        limit_req zone=api_limit burst=10 nodelay;  # .. add this line<br><br>        # Return a JSON body (and a Retry-After hint) when the rate limit is hit.<br>        error_page 429 = @too_many_requests;<br>        location @too_many_requests {<br>            default_type application/json;<br>            add_header Retry-After 1 always;<br>            return 429 &#39;{&quot;error&quot;:&quot;too many requests&quot;}\n&#39;;<br>        }<br><br>        # Headers every backend receives<br>        proxy_set_header Host              $host;<br>        proxy_set_header X-Real-IP         $remote_addr;<br>        proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;<br>        proxy_set_header X-Forwarded-Proto $scheme;<br> <br>        # next to the other proxy_set_header lines at the server level<br>        proxy_set_header X-API-Client $api_client;   # set by auth_request_set; empty = not sent<br>        <br>        error_page 401 = @unauthorized;<br>        location @unauthorized {<br>            default_type application/json;<br>            return 401 &#39;{&quot;error&quot;:&quot;missing or invalid API key&quot;}\n&#39;;<br>        }<br><br>        location = /_auth {<br>            internal;                                      # unreachable from outside<br>            proxy_pass http://auth-service/verify;<br>            proxy_pass_request_body off;                   # auth only needs headers…<br>            proxy_set_header Content-Length &quot;&quot;;            # …so don&#39;t send a body<br>            proxy_set_header X-API-Key $http_x_api_key;    # the key we&#39;re checking<br>        }<br><br>        location /orders {<br>            auth_request /_auth;                                       # ask auth-service first<br>            auth_request_set $api_client $upstream_http_x_api_client;  # read its X-API-Client header<br>            proxy_pass http://orders-service;<br>        }<br><br>        location /users {<br>            proxy_pass http://users-service;<br>        }<br><br>        location /products {<br>            proxy_cache api_cache;<br>            proxy_cache_valid 200 30s;                        # cache 200 responses for 30 seconds<br>            # Expose the cache result in the response so you can watch caching work:<br>            #   MISS    - nothing cached yet; fetched from products-api and stored (if 200)<br>            #   HIT     - fresh copy (&lt; 30s old) served from cache; backend not called<br>            #   EXPIRED - cached copy older than 30s; refetched from backend and re-cached<br>            # Note: only 200s are cached, and without &quot;always&quot; this header is not<br>            # added to error responses (4xx/5xx).<br>            add_header X-Cache-Status $upstream_cache_status;<br>            proxy_pass http://products-service;<br>        }<br><br>        location / {<br>            default_type application/json;<br>            return 404 &#39;{&quot;error&quot;:&quot;not found&quot;}\n&#39;;<br>        }<br>    }<br>}</pre><p>And the final compose.yml:</p><pre>services:<br>  nginx:<br>    image: nginx:alpine<br>    volumes:<br>      - ./nginx.conf:/etc/nginx/nginx.conf:ro<br>    ports:<br>      - 3000:3000<br>    restart: on-failure<br>    depends_on:<br>      - orders-api<br>      - users-api<br>      - products-api<br>      - auth-api<br>  orders-api:<br>    build:<br>      context: .<br>    command: python ./orders.py<br>    expose:<br>      - &quot;3001&quot;<br>  users-api:<br>    build:<br>      context: .<br>    command: python ./users.py<br>    expose:<br>      - &quot;3002&quot;<br>  products-api:<br>    build:<br>      context: .<br>    command: python ./products.py<br>    expose:<br>      - &quot;3003&quot;<br>  auth-api:<br>    build:<br>      context: .<br>    command: python ./auth.py<br>    expose:<br>      - &quot;3004&quot;</pre><h4>Clean up</h4><p>When you’re done, stop and remove the containers and their network:</p><pre>docker compose down</pre><h4>Takeaway</h4><p>An API Gateway provides a single entry point to manage communication between clients and microservices, centralizing routing, security, rate limiting, and observability. With NGINX, you can implement these capabilities using simple configuration, without adding another application layer. By extending the gateway with caching, authentication, and load balancing, you can build a more secure, scalable, and maintainable backend architecture.</p><p>While a Reverse Proxy primarily focuses on managing network traffic, an API Gateway focuses on API management, security, and governance. Dedicated API Gateway solutions also offer advanced integrations, analytics, and policy management out of the box.</p><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5e75ff6101ce" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Solve the N+1 Query Problem in Django]]></title>
            <link>https://medium.com/@johnidouglasmarangon/how-to-solve-the-n-1-query-problem-in-django-cc1b6cebae1d?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/cc1b6cebae1d</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[django]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Tue, 03 Nov 2026 16:04:44 GMT</pubDate>
            <atom:updated>2026-11-03T16:04:44.595Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LzEDQHEDYWFwDcSX.jpg" /></figure><p>A few weeks after my team and I set up Sentry monitoring for a Python API service, Sentry flagged several endpoints making repeated database queries due to a classic N+1 pattern. This is the well-known <strong>N+1 query problem</strong>, one of the most common performance issues in Django applications.</p><p>This post builds a tiny Django project to make the problem visible, then fixes it. We’ll model blog posts, authors, categories, and tags, count the exact SQL queries Django runs, and watch that count collapse as we reach for select_related(), prefetch_related(), Prefetch, only()/defer(), and annotate(). By the end you&#39;ll be able to spot an N+1 in your own code and know which tool each situation calls for.</p><p>Everything here runs locally on SQLite with no server to install. The whole thing is about a hundred lines.</p><h4>What the N+1 query problem actually is</h4><p>Say you have 50 blog posts, each written by one author. You want to list every post with its author’s name:</p><pre>for post in Post.objects.all():           #  1 query: SELECT * FROM post<br>    print(post.title, post.author.name)   # +1 query EACH time, 50 total</pre><p>The first line runs one query to load the posts. But post.author is a foreign key Django hasn&#39;t fetched yet, so the first time you read it on each post, Django fires a separate query to get that author. Fifty posts means fifty extra queries. One query to get the list, N queries to get the related objects: 1 + N, hence &quot;N+1.&quot;</p><p>With 50 posts, it’s 51 queries. With 5,000 posts, it’s 5,001. The code looks identical, the cost scales linearly with your data.</p><h4>Why it’s so common</h4><p>The whole appeal of an ORM is that post.author looks like a plain attribute access. It reads like you&#39;re walking an object graph that&#39;s already in memory — and nothing in the syntax warns you that touching that attribute opens a database connection. Django&#39;s lazy loading is a feature: it means you only pay for the relations you actually use. But <strong>lazy loading inside a loop is exactly the trap</strong>. Each iteration looks cheap in isolation.</p><p>It shows up everywhere relational data gets rendered in a list: a feed of posts with authors, an order history with line items, a dashboard table with a computed count per row. <a href="https://www.django-rest-framework.org/">Django REST Framework</a> serializers are a classic amplifier: a nested serializer field walks a relation for every object in the response, so an endpoint returning 100 records can silently issue hundreds of queries.</p><h4>Why it hurts</h4><p>Each query is a round trip: your app sends SQL across the wire, the database plans and runs it, then ships the result back. Even a query that the database answers in under a millisecond carries fixed overhead: connection handling, query planning, and network latency that dwarfs the actual lookup when you do it hundreds of times. Fifty cheap queries in a tight loop routinely cost more wall-clock time than one slightly larger join.</p><p>And it compounds under load. A view that runs 200 queries for one user runs 20,000 for a hundred concurrent users, saturating your connection pool and starving every other request. The <strong>N+1 problem is a scalability problem</strong> as much as a latency one: it’s the kind of bug that passes every test with your 10-row fixture and falls over the week real traffic arrives.</p><p>The good news is that Django gives you precise tools to collapse those N+1 queries into one or two. First, let’s build something we can measure.</p><h4>Set up the project</h4><p>We’ll use the latest stable Django, which is 6.1 at the time of writing (6.1.2 is the current patch release). Django 6.1 requires Python 3.12 or newer, so confirm your Python first:</p><pre>python --version</pre><p>You should see 3.12.x or higher. Now create a project folder and a virtual environment, and install Django:</p><pre>mkdir django-nplus1 &amp;&amp; cd django-nplus1<br>python -m venv .venv<br>source .venv/bin/activate      # Windows: .venv\Scripts\activate<br>python -m pip install &quot;Django&gt;=6.1,&lt;6.2&quot;</pre><p>Create the project and an app:</p><pre>django-admin startproject config .<br>python manage.py startapp blog</pre><p>The trailing . in the startproject command keeps manage.py in the current folder instead of nesting another directory. Register the app in config/settings.py:</p><pre>INSTALLED_APPS = [<br>    &quot;django.contrib.admin&quot;,<br>    &quot;django.contrib.auth&quot;,<br>    &quot;django.contrib.contenttypes&quot;,<br>    &quot;django.contrib.sessions&quot;,<br>    &quot;django.contrib.messages&quot;,<br>    &quot;django.contrib.staticfiles&quot;,<br>    &quot;blog&quot;,                       # &lt;- add this<br>]</pre><p>The default DATABASES setting already points to SQLite, so there&#39;s nothing to configure. A file called db.sqlite3 will be created on the first migration. That&#39;s exactly what we want: zero setup and a real SQL database that generates real joins.</p><h4>Define the models</h4><p>We want one relationship of each shape, because each shape needs a different fix:</p><ul><li>Post → Author is a ForeignKey (many posts, one author). This is the select_related() case.</li><li>Post → Category is also a ForeignKey, so we can see two forward relations at once.</li><li>Post ↔ Tag is a ManyToManyField. This is the prefetch_related() case.</li><li>Author → Post is the reverse side of the foreign key (one author, many posts). Also a prefetch_related() case.</li></ul><p>Put this in blog/models.py:</p><pre>from django.db import models<br> <br> <br>class Author(models.Model):<br>    name = models.CharField(max_length=100)<br>    email = models.EmailField()<br>    bio = models.TextField(blank=True)<br> <br>    def __str__(self):<br>        return self.name<br> <br> <br>class Category(models.Model):<br>    name = models.CharField(max_length=100)<br> <br>    def __str__(self):<br>        return self.name<br> <br> <br>class Tag(models.Model):<br>    name = models.CharField(max_length=50)<br> <br>    def __str__(self):<br>        return self.name<br> <br> <br>class Post(models.Model):<br>    title = models.CharField(max_length=200)<br>    body = models.TextField()<br>    created = models.DateTimeField(auto_now_add=True)<br>    author = models.ForeignKey(<br>        Author, on_delete=models.CASCADE, related_name=&quot;posts&quot;<br>    )<br>    category = models.ForeignKey(<br>        Category, on_delete=models.CASCADE, related_name=&quot;posts&quot;<br>    )<br>    tags = models.ManyToManyField(Tag, related_name=&quot;posts&quot;, blank=True)<br> <br>    def __str__(self):<br>        return self.title</pre><p>Note related_name=&quot;posts&quot; on the foreign keys; that&#39;s what lets us write author.posts.all() for the reverse direction later. Create and apply the migrations:</p><pre>python manage.py makemigrations<br>python manage.py migrate</pre><h4>Populate some sample data</h4><p>Let’s create enough sample data to make the N+1 query problem easy to reproduce and observe.</p><p>The quickest way is to use Django’s shell. Run python manage.py shell and execute the following script (copy and paste it into the shell):</p><pre>import random<br>from blog.models import Author, Category, Tag, Post<br><br># Create sample authors<br>authors = [<br>    Author.objects.create(<br>        name=f&quot;Author {i}&quot;,<br>        email=f&quot;a{i}@example.com&quot;,<br>    )<br>    for i in range(10)<br>]<br># Create sample categories<br>categories = [<br>    Category.objects.create(name=name)<br>    for name in [&quot;Python&quot;, &quot;Django&quot;, &quot;Databases&quot;, &quot;DevOps&quot;]<br>]<br># Create sample tags<br>tags = [<br>    Tag.objects.create(name=name)<br>    for name in [&quot;orm&quot;, &quot;performance&quot;, &quot;sql&quot;, &quot;tutorial&quot;, &quot;tips&quot;]<br>]<br># Create 50 posts with random authors, categories, and tags<br>for i in range(50):<br>    post = Post.objects.create(<br>        title=f&quot;Post number {i}&quot;,<br>        body=&quot;Lorem ipsum dolor sit amet.&quot;,<br>        author=random.choice(authors),<br>        category=random.choice(categories),<br>    )<br>    post.tags.set(random.sample(tags, k=random.randint(1, 3)))<br> <br>print(Post.objects.count(), &quot;posts created&quot;)   # -&gt; 50 posts created</pre><p>This script creates 10 authors, 4 categories, 5 tags, and 50 posts. Each post is assigned a random author, category, and between one and three tags.</p><p>With the sample data in place, we’re ready to reproduce the N+1 query problem. <strong>Stay in the Django shell</strong> in the next section, we’ll execute a query and inspect how many SQL statements Django generates.</p><h4>See the problem: counting queries</h4><p>The key skill is measuring, not guessing. Django ships a context manager, CaptureQueriesContext, that records every query run inside a block. The connection also exposes connection.queries, but only when DEBUG = True; the context manager works regardless and is cleaner. Let&#39;s wrap our naive loop in it. Still in the Django shell:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br> <br> <br>with CaptureQueriesContext(connection) as ctx:<br>    for post in Post.objects.all():<br>        _ = post.author.name          # touch the related object<br> <br>print(len(ctx), &quot;queries&quot;)     # -&gt; 51 queries</pre><p>There it is 51 queries for 50 posts. One to load the posts, fifty to load each author on demand.</p><p>To print the raw query, use the code below:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br> <br> <br>with CaptureQueriesContext(connection) as ctx:<br>    for post in Post.objects.all():<br>        _ = post.author.name<br> <br>for query in ctx.captured_queries:   # print the raw query<br>    print(query[&quot;sql&quot;])</pre><p>If you print the captured SQL, the shape of the problem is obvious. The first query loads every post:</p><pre>SELECT &quot;blog_post&quot;.&quot;id&quot;, &quot;blog_post&quot;.&quot;title&quot;, &quot;blog_post&quot;.&quot;body&quot;, ...<br>FROM &quot;blog_post&quot;</pre><p>And then, fifty times over, nearly identical single-row lookups, one per post:</p><pre>SELECT &quot;blog_author&quot;.&quot;id&quot;, &quot;blog_author&quot;.&quot;name&quot;, ...<br>FROM &quot;blog_author&quot;<br>WHERE &quot;blog_author&quot;.&quot;id&quot; = 5<br>LIMIT 21<br> <br>SELECT &quot;blog_author&quot;.&quot;id&quot;, &quot;blog_author&quot;.&quot;name&quot;, ...<br>FROM &quot;blog_author&quot;<br>WHERE &quot;blog_author&quot;.&quot;id&quot; = 10<br>LIMIT 21</pre><p>Notice that we only have 10 authors, yet Django ran 50 author lookups. It doesn’t cache authors across posts: every post.author access on a fresh instance triggers its own WHERE id = ?, even when the same author was fetched a moment ago for a previous post. That&#39;s the N+1, caught in the act. Now let&#39;s fix it.</p><blockquote><strong><em>Tip:</em></strong><em> In a real view rather than the shell, the </em><a href="https://django-debug-toolbar.readthedocs.io/en/latest/"><em>Django Debug Toolbar</em></a><em> shows this query count (and the duplicate queries) right in the browser. In tests, </em><em>self.assertNumQueries(2) fails loudly if a change reintroduces an N+1 — a great regression guard once you&#39;ve fixed one.</em></blockquote><h4>Fix 1: select_related() for foreign keys</h4><p>select_related() is for forward ForeignKey and OneToOneField relationships. The &quot;many-to-one&quot; direction, where each object points at exactly one related row. It tells Django to fetch the related object in the same query using SQL, so there&#39;s nothing left to load lazily.</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br><br>with CaptureQueriesContext(connection) as ctx:<br>    for post in Post.objects.select_related(&quot;author&quot;):<br>        _ = post.author.name<br><br>print(len(ctx), &quot;queries&quot;)     # -&gt; 1 query</pre><p>From 51 queries to 1 query. Django folded the author lookup into a single join:</p><pre>SELECT &quot;blog_post&quot;.&quot;id&quot;, &quot;blog_post&quot;.&quot;title&quot;, &quot;blog_post&quot;.&quot;author_id&quot;, ...,<br>       &quot;blog_author&quot;.&quot;id&quot;, &quot;blog_author&quot;.&quot;name&quot;, &quot;blog_author&quot;.&quot;email&quot;, ...<br>FROM &quot;blog_post&quot;<br>INNER JOIN &quot;blog_author&quot; ON (&quot;blog_post&quot;.&quot;author_id&quot; = &quot;blog_author&quot;.&quot;id&quot;)</pre><p>One query, one join, every author already attached to its post. You can follow multiple foreign keys at once, and even span relations with the double-underscore syntax:</p><pre># Both foreign keys in one query:<br>Post.objects.select_related(&quot;author&quot;, &quot;category&quot;)<br> <br># Follow a chain - e.g. if Author had a ForeignKey to a Team:<br>Post.objects.select_related(&quot;author__team&quot;)</pre><p>Each argument adds another join to the same statement. Because a forward foreign key always points at one row, a join can never multiply your result set here, which is exactly why select_related is safe and efficient for this direction.</p><h4>Fix 2: prefetch_related() for many-to-many and reverse relations</h4><p>A join doesn’t work for a ManyToManyField or a reverse foreign key, because one post can have many tags and one author can have many posts. Joining would duplicate the parent row once per child (a post with 3 tags would come back 3 times), and you&#39;d have to de-duplicate in Python. So Django takes a different approach.</p><p>prefetch_related() runs a separate query for the related objects, then stitches the two result sets together in Python. That&#39;s two queries total no matter how many parent rows there are, not one, but a constant, which is the whole point.</p><p>Watch the tags case go from N+1 to 2. First, without prefetching:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br><br># Without prefetch — one query for posts, one per post for its tags:<br>with CaptureQueriesContext(connection) as ctx:<br>    for post in Post.objects.all():<br>        _ = list(post.tags.all())<br><br>print(len(ctx), &quot;queries&quot;)     # -&gt; 51</pre><p>Now the same loop with prefetch_related(&quot;tags&quot;):</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br><br># With prefetch - one for posts, one for ALL their tags:<br>with CaptureQueriesContext(connection) as ctx:<br>    for post in Post.objects.prefetch_related(&quot;tags&quot;):<br>        _ = list(post.tags.all())<br><br>print(len(ctx), &quot;queries&quot;)     # -&gt; 2</pre><p>The second query gathers every tag for the whole batch at once, filtering on the collected post IDs through the many-to-many join table:</p><pre>SELECT (&quot;blog_post_tags&quot;.&quot;post_id&quot;) AS &quot;_prefetch_related_val_post_id&quot;,<br>       &quot;blog_tag&quot;.&quot;id&quot;, &quot;blog_tag&quot;.&quot;name&quot;<br>FROM &quot;blog_tag&quot;<br>INNER JOIN &quot;blog_post_tags&quot; ON (&quot;blog_tag&quot;.&quot;id&quot; = &quot;blog_post_tags&quot;.&quot;tag_id&quot;)<br>WHERE &quot;blog_post_tags&quot;.&quot;post_id&quot; IN (1, 2, 3, ..., 50)</pre><p>The reverse foreign key, authors and their posts, works the same way:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Author<br> <br> <br>with CaptureQueriesContext(connection) as ctx:<br>    for author in Author.objects.prefetch_related(&quot;posts&quot;):<br>        _ = list(author.posts.all())<br> <br>print(len(ctx), &quot;queries&quot;)     # -&gt; 2</pre><p>You can mix the two tools freely. select_related for the forward foreign keys, prefetch_related for the collections, all on one queryset:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br><br>with CaptureQueriesContext(connection) as ctx:<br>    posts = list(<br>        Post.objects<br>        .select_related(&quot;author&quot;, &quot;category&quot;)<br>        .prefetch_related(&quot;tags&quot;)<br>    )<br><br>    for post in posts:<br>        _ = post.author.name<br>        _ = post.category.name<br>        _ = list(post.tags.all())<br><br>print(len(ctx), &quot;queries&quot;)     # -&gt; 2<br><br>for i, query in enumerate(ctx.captured_queries, start=1):<br>    print(f&quot;\nQuery {i}:&quot;)<br>    print(query[&quot;sql&quot;])</pre><p>This combination, select_related for to-one, prefetch_related for to-many, is the everyday fix for the vast majority of N+1 problems.</p><h4>Fix 3: Prefetch for customizing the related query</h4><p>Plain prefetch_related(&quot;tags&quot;) fetches all related objects. When you need to filter, order, or otherwise shape that second query, or control exactly where the results land, reach for the Prefetch object.</p><p>Suppose you want each author with only their Django-category posts, newest first:</p><pre>from django.db.models import Prefetch<br>from blog.models import Author, Post<br><br>authors = Author.objects.prefetch_related(<br>    Prefetch(<br>        &quot;posts&quot;,<br>        queryset=Post.objects.filter(category__name=&quot;Django&quot;).order_by(&quot;-created&quot;),<br>        to_attr=&quot;django_posts&quot;,      # results land in author.django_posts<br>    )<br>)<br><br>for author in authors:<br>    print(author.name, &quot;-&gt;&quot;, [p.title for p in author.django_posts])</pre><p>Still just two queries, but now the prefetch query carries your WHERE and ORDER BY, and the filtered results are attached to a clean django_posts list instead of overwriting the default posts manager. The to_attr argument is especially handy when you need both the filtered and unfiltered sets, or want a name that reads clearly in templates.</p><p>Prefetch also lets you optimize the inner query. If those prefetched posts will themselves display their category, give the inner queryset its own select_related so the nested access doesn&#39;t reintroduce an N+1:</p><pre>Prefetch(<br>    &quot;posts&quot;,<br>    queryset=Post.objects.select_related(&quot;category&quot;),<br>)</pre><h4>Fix 4: only() and defer() for trimming columns</h4><p>The query-count tools above control how many queries run. only() and defer() control how much each query pulls back. They&#39;re about column width, not query count. They matter when a table has heavy columns (large TextFields, JSON blobs) that the given view doesn&#39;t need.</p><ul><li>only(&quot;a&quot;, &quot;b&quot;) loads only those fields (plus the primary key); everything else becomes deferred.</li><li>defer(&quot;x&quot;, &quot;y&quot;) loads everything except those fields.</li></ul><p>For a list view that shows just titles and author names, there’s no reason to drag every post’s full body across the wire:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Post<br><br>with CaptureQueriesContext(connection) as ctx:<br>    posts = (<br>        Post.objects<br>        .select_related(&quot;author&quot;)<br>        .only(&quot;title&quot;, &quot;author__name&quot;)<br>    )<br>    for post in posts:<br>        print(f&quot;Post: {post.title}, Author: {post.author.name}&quot;)<br> <br>print(len(ctx), &quot;queries&quot;)     # -&gt; 1<br> <br>for i, query in enumerate(ctx.captured_queries, start=1):<br>    print(f&quot;\nQuery {i}:&quot;)<br>    print(query[&quot;sql&quot;])</pre><p>The generated SQL selects a narrow set of columns instead of SELECT *:</p><pre>SELECT &quot;blog_post&quot;.&quot;id&quot;, &quot;blog_post&quot;.&quot;title&quot;, &quot;blog_post&quot;.&quot;author_id&quot;,<br>       &quot;blog_author&quot;.&quot;id&quot;, &quot;blog_author&quot;.&quot;name&quot;<br>FROM &quot;blog_post&quot;<br>INNER JOIN &quot;blog_author&quot; ON (&quot;blog_post&quot;.&quot;author_id&quot; = &quot;blog_author&quot;.&quot;id&quot;)</pre><p>The catch, and it’s a sharp one: a deferred field isn’t gone, it’s lazy. Touch one later, and Django fetches it with another query per object. So only() used carelessly can create an N+1 of its own:</p><pre>from blog.models import Post<br><br>posts = Post.objects.only(&quot;title&quot;)       # body is deferred<br>for post in posts:<br>    print(post.body)                      # +1 query PER post — 51 total, a brand-new N+1!</pre><p>Use only()/defer() when you&#39;re confident which fields the code path touches. They&#39;re a real win for wide tables, but they trade a smaller payload for the risk of accidental lazy loads; reach for them after you&#39;ve fixed the relationship-level N+1s, not before.</p><h4>Fix 5: annotate() for counts and aggregates</h4><p>Here’s an N+1 that select_related and prefetch_related don&#39;t touch, computing an aggregate per row. Say you want each author alongside how many posts they&#39;ve written. The obvious code calls .count() in the loop:</p><pre>from django.db import connection<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Author<br><br>with CaptureQueriesContext(connection) as ctx:<br>    for author in Author.objects.all():<br>        print(author.name, author.posts.count())<br><br>print(len(ctx), &quot;queries&quot;)     # -&gt; 11  (1 + 10 authors)<br><br>for i, query in enumerate(ctx.captured_queries, start=1):<br>    print(f&quot;\nQuery {i}:&quot;)<br>    print(query[&quot;sql&quot;])</pre><p>Each author.posts.count() is its own SELECT COUNT(*) round trip. Push the counting into the database with annotate() and it becomes a single grouped query:</p><pre>from django.db import connection<br>from django.db.models import Count<br>from django.test.utils import CaptureQueriesContext<br>from blog.models import Author<br> <br>with CaptureQueriesContext(connection) as ctx:<br>    authors = Author.objects.annotate(post_count=Count(&quot;posts&quot;))<br>    for author in authors:<br>        print(author.name, author.post_count)       # attribute, not a query<br> <br>print(len(ctx), &quot;queries&quot;)     # -&gt; 1</pre><p>The database does the grouping in one statement:</p><pre>SELECT &quot;blog_author&quot;.&quot;id&quot;, &quot;blog_author&quot;.&quot;name&quot;, ...,<br>       COUNT(&quot;blog_post&quot;.&quot;id&quot;) AS &quot;post_count&quot;<br>FROM &quot;blog_author&quot;<br>LEFT OUTER JOIN &quot;blog_post&quot; ON (&quot;blog_author&quot;.&quot;id&quot; = &quot;blog_post&quot;.&quot;author_id&quot;)<br>GROUP BY &quot;blog_author&quot;.&quot;id&quot;, &quot;blog_author&quot;.&quot;name&quot;, ...</pre><p>annotate() works for any aggregate — Count, Sum, Avg, Max, Min — and the result rides along as a normal attribute on each object. Whenever you catch yourself calling .count(), .sum(), or looping to total something up per row, that&#39;s the signal to annotate instead.</p><h4>Choosing the right tool</h4><p>Each fix maps to a shape of relationship, so once you can name the shape you can name the fix:</p><ul><li><strong>Forward </strong><strong>ForeignKey / </strong><strong>OneToOneField (to-one):</strong> select_related() — folds into 1 query (JOIN).</li><li><strong>ManyToManyField or reverse FK (to-many):</strong> prefetch_related() — +1 query per relation.</li><li><strong>A prefetch that needs filtering/ordering:</strong> Prefetch(...) — +1 query per relation.</li><li><strong>Per-row count or aggregate:</strong> annotate() — folds into 1 query (GROUP BY).</li><li><strong>Trimming heavy columns off a wide query:</strong> only() / defer() — same query count, smaller rows.</li></ul><p>The mental model: select_related joins, prefetch_related batches, annotate aggregates. The first two attack the N+1 directly; annotate attacks its close cousin, the per-row aggregate; only/defer are a payload optimization you layer on once the query count is already under control.</p><h4>Wrapping up</h4><p>The N+1 query problem is one of those performance issues that can hide in perfectly readable Django code. A simple attribute access or .count() inside a loop can silently trigger dozens or hundreds of database queries.</p><p>The key is to understand your relationship types and choose the right ORM tool:</p><ul><li>select_related(): Use SQL JOINs to fetch related objects through ForeignKey and OneToOneField relationships.</li><li>prefetch_related(): Fetch ManyToMany and reverse ForeignKey relationships in separate, batched queries.</li><li>Prefetch(): Customize prefetch queries with filtering, ordering, and to_attr.</li><li>annotate(): Move per-object counts and aggregations into the database instead of executing queries inside loops.</li><li>only() / defer(): Reduce the number of columns retrieved when you don&#39;t need the entire model, but be careful not to introduce new lazy-loading queries.</li></ul><p>One important lesson: don’t guess where the performance bottleneck is. Measure it. Tools like CaptureQueriesContext, Django Debug Toolbar, and assertNumQueries() help you identify excessive queries, verify optimizations, and prevent regressions.</p><p>You don’t need to optimize every queryset prematurely. Start by identifying the actual problem, understand which relationships your code accesses, and apply the appropriate optimization.</p><p>A few carefully chosen ORM methods can turn hundreds of database queries into just one or two, making your Django application faster, more efficient, and ready to scale.</p><h3>About me</h3><p>I’m a Senior Backend Engineer with experience building software across different industries<strong> since 2008</strong>. I specialize in building complex software from the ground up, designing backend architectures, and developing cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cc1b6cebae1d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Simplest Way to Build a REST API in .NET — Minimal API]]></title>
            <link>https://medium.com/@johnidouglasmarangon/the-simplest-way-to-build-a-rest-api-in-net-minimal-api-54bb8064f66a?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/54bb8064f66a</guid>
            <category><![CDATA[rest-api]]></category>
            <category><![CDATA[dotnet]]></category>
            <category><![CDATA[backend-development]]></category>
            <category><![CDATA[csharp]]></category>
            <category><![CDATA[minimal-api]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Mon, 02 Nov 2026 13:52:25 GMT</pubDate>
            <atom:updated>2026-11-02T13:52:25.969Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*96Uk64WlDp_VeAyd.jpeg" /></figure><p>Java and .NET are well-known platforms for building complete REST API projects, offering a rich set of built-in features (authorization, database connectivity, design patterns, and more) compared to tools like Python or Node.js. Building even a minimal REST API in .NET traditionally required creating multiple files, classes, and supporting artifacts.</p><p>Introduced in .NET 6, <a href="https://learn.microsoft.com/en-us/aspnet/core/fundamentals/minimal-apis?view=aspnetcore-10.0">Minimal APIs</a> let you spin up a small REST API with just a Program.cs and no other files.</p><p>.NET 7 and .NET 8 then expanded Minimal APIs significantly, adding things like route groups (MapGroup), typed results (TypedResults), better filters, and improved OpenAPI support. So if you&#39;re aiming for &quot;production-ready&quot; minimal APIs, it&#39;s worth knowing that the feature matured a lot after its initial .NET 6 release.</p><p>This post builds a tiny .NET 10 API for a to-do list. We’ll wire up the four HTTP methods that cover basic CRUD — GET, POST, PUT, DELETE — plus the new QUERY method for searches, all against an in-memory list, with no database and no boilerplate.</p><p>By the end, you’ll have a working API you can hit from the browser or curl, and a clear picture of what each line is doing.</p><h4>What we’re going to build</h4><p>A single resource: a Todo, with an Id, a Title, and an IsDone flag. We’ll expose six endpoints over it:</p><ul><li>GET /todos — list every to-do.</li><li>GET /todos/{id} — fetch one by id.</li><li>POST /todos — create a new one.</li><li>PUT /todos/{id} — update an existing one.</li><li>DELETE /todos/{id} — remove one.</li><li>QUERY /todos — search with filters in the request body.</li></ul><p>That’s a complete CRUD surface, plus a search. We’ll keep the data in a plain List&lt;Todo&gt; so the focus stays on the API itself, not on persistence.</p><h4>A bonus verb: QUERY for safe searches</h4><p>Standardized in June 2026 as <a href="https://www.rfc-editor.org/info/rfc10008/">RFC 10008</a>, QUERY fills the gap between GET and POST. It supports complex search requests with a body while remaining safe and idempotent, making caching and automatic retries possible. It’s a cleaner way to handle read-only operations when filters become too complex to fit in a URL.</p><h4>Prerequisites</h4><p>The .NET 10 SDK. Version 10.0 is the current Long-Term Support release, so it’s a good baseline for new work. Confirm your install with:</p><pre>dotnet --version</pre><p>You should see 10.0.x.</p><blockquote><em>This post needs .NET 10, because the </em><em>QUERY endpoint uses </em><em>HttpMethods.Query. On .NET 9 you can follow along if you drop that endpoint; </em><em>Guid.CreateVersion7() needs .NET 9 or later.</em></blockquote><h4>Create the project</h4><pre>dotnet new web -o MinimalTodoApi --framework net10.0<br>cd MinimalTodoApi</pre><p>The web template is the empty ASP.NET Core starter — no MVC, no controllers, just the minimal host. It gives you a Program.cs with a couple of lines in it and nothing else. That file is the entire app.</p><h4>The whole API</h4><p>Open Program.cs and replace its contents with this:</p><pre>var builder = WebApplication.CreateBuilder(args);<br>var app = builder.Build();<br> <br>var todos = new List&lt;Todo&gt;<br>{<br>    new(Guid.Parse(&quot;019a3a1c-5e00-7000-8000-000000000001&quot;), <br>        &quot;Write the tutorial&quot;, false),<br>    new(Guid.Parse(&quot;019a3a1c-5e00-7000-8000-000000000002&quot;), <br>        &quot;Publish the draft&quot;, false),<br>    new(Guid.Parse(&quot;019a3a1c-5e00-7000-8000-000000000003&quot;), <br>        &quot;Respond to the comments&quot;, true)<br>};<br> <br>// List all<br>app.MapMethods(&quot;/todos&quot;, [HttpMethods.Get], () =&gt; Results.Ok(todos));<br> <br>// Get one<br>app.MapMethods(&quot;/todos/{id}&quot;, [HttpMethods.Get], (Guid id) =&gt;<br>    todos.FirstOrDefault(t =&gt; t.Id == id) is { } todo<br>        ? Results.Ok(todo)<br>        : Results.NotFound());<br> <br>// Create<br>app.MapMethods(&quot;/todos&quot;, [HttpMethods.Post], (TodoInput input) =&gt;<br>{<br>    var todo = new Todo(Guid.CreateVersion7(), input.Title, input.IsDone);<br>    todos.Add(todo);<br>    return Results.Created($&quot;/todos/{todo.Id}&quot;, todo);<br>});<br> <br>// Update<br>app.MapMethods(&quot;/todos/{id}&quot;, [HttpMethods.Put], (Guid id, TodoInput input) =&gt;<br>{<br>    var index = todos.FindIndex(t =&gt; t.Id == id);<br>    if (index == -1) return Results.NotFound();<br>    todos[index] = new Todo(id, input.Title, input.IsDone);<br>    return Results.NoContent();<br>});<br> <br>// Delete<br>app.MapMethods(&quot;/todos/{id}&quot;, [HttpMethods.Delete], (Guid id) =&gt;<br>    todos.RemoveAll(t =&gt; t.Id == id) &gt; 0<br>        ? Results.NoContent()<br>        : Results.NotFound());<br> <br>// Search<br>app.MapMethods(&quot;/todos&quot;, [HttpMethods.Query], (TodoFilter filter) =&gt;<br>{<br>    var matches = todos.Where(t =&gt;<br>        (filter.TitleContains is null ||<br>            t.Title.Contains(filter.TitleContains, StringComparison.OrdinalIgnoreCase))<br>        &amp;&amp; (filter.IsDone is null || t.IsDone == filter.IsDone));<br> <br>    return Results.Ok(matches);<br>});<br> <br>app.Run();<br> <br>record Todo(Guid Id, string Title, bool IsDone);<br>record TodoInput(string Title, bool IsDone);<br>record TodoFilter(string? TitleContains, bool? IsDone);</pre><p>That’s the complete application. Run it:</p><pre>dotnet run --urls http://localhost:5000</pre><p>The --urls flag pins the app to port 5000 so the examples below work as written. Without it, the template picks a random port from Properties/launchSettings.json, and you’d need to use the URL the console prints instead. Once you see Now listening on: http://localhost:5000, the API is live.</p><h4>What each piece is doing</h4><p>Two lines of setup, and the rest is endpoints.</p><ul><li>WebApplication.CreateBuilder(args) configures the host; builder.Build() produces the app you map routes onto. In a minimal API these are the only two lines of plumbing you need.</li><li>MapMethods connects a route and one or more HTTP methods to a handler. The handler is just a lambda, and whatever it returns is sent back as the response.</li></ul><blockquote><em>You’ll usually see </em><em>MapGet, </em><em>MapPost, </em><em>MapPut, and </em><em>MapDelete in Minimal API code, and they work exactly the same here. There’s no </em><em>MapQuery shortcut, though, so </em><em>QUERY has to go through </em><em>MapMethods. I used </em><em>MapMethods for every endpoint to keep them all in one consistent style.</em></blockquote><p>The handler parameters carry all the meaning, and ASP.NET Core fills them in for you by name:</p><ul><li><strong>(Guid id)</strong> matches the {id} in the route. A request to /todos/019a3a1c-5e00-7000-8000-000000000001 arrives with id already parsed into a Guid. If the value isn&#39;t a valid GUID, the framework returns 400 Bad Request before your handler runs.</li><li><strong>(TodoInput input)</strong> has no matching segment in the route, so the framework reads it from the JSON request body. You don&#39;t need a [FromBody] attribute or any manual deserialization.</li><li><strong>(TodoFilter filter)</strong> works the same way on the QUERY endpoint.</li></ul><p>Return values are just as automatic. Hand back a Todo and it’s serialized to JSON with a 200 OK. Hand back a Results.* value when you want to control the status code:</p><ul><li>Results.Ok(todo) → 200 with a body.</li><li>Results.Created(uri, todo) → 201, the correct response for a successful POST, with a Location header pointing at the new resource.</li><li>Results.NoContent() → 204, the usual answer to a successful PUT or DELETE where there’s nothing to send back.</li><li>Results.NotFound() → 404, for an id that isn’t there.</li></ul><p>Using the right status code is most of what makes an API “RESTful” — the verb says what you’re doing, the status says how it went.</p><p>About Guid.CreateVersion7() in the Create handler: a UUIDv7 is a UUID that begins with a timestamp. RFC 9562 standardized it in 2024. Its first 48 bits hold the number of milliseconds since the Unix epoch, and almost all of the remaining bits are random. Starting with .NET 9, you can generate one with Guid.CreateVersion7().</p><blockquote>The<em> </em><em>*is { } todo* </em>in the<em> </em><em>*GET /todos/{id}* </em>handler is a C# pattern-matching shortcut: it’s true when<em> </em><em>*FirstOrDefault* </em>finds a match (i.e. the result isn’t null), and binds that match to<em> </em><em>*todo* </em>in one step. It saves a null check and a separate variable.</blockquote><h4>Try it out</h4><p>With the app running, list the seeded to-dos in your browser by visiting http://localhost:5000/todos, or from the terminal.</p><p>List all todos:</p><pre>curl -i http://localhost:5000/todos</pre><p>Get one todo:</p><pre>curl -i http://localhost:5000/todos/019a3a1c-5e00-7000-8000-000000000001</pre><p>Create one todo:</p><pre>curl -i -X POST http://localhost:5000/todos \<br>  -H &quot;Content-Type: application/json&quot; \<br>  -d &#39;{&quot;title&quot;:&quot;Learn minimal APIs&quot;,&quot;isDone&quot;:false}&#39;</pre><p>Update a todo:</p><pre>curl -i -X PUT http://localhost:5000/todos/019a3a1c-5e00-7000-8000-000000000001 \<br>  -H &quot;Content-Type: application/json&quot; \<br>  -d &#39;{&quot;title&quot;: &quot;Write the tutorial&quot;, &quot;isDone&quot;: true}&#39;</pre><p>Search a todo using JSON body:</p><pre>curl -i -X QUERY http://localhost:5000/todos \<br>  -H &quot;Content-Type: application/json&quot; \<br>  -d &#39;{&quot;titleContains&quot;: &quot;tutorial&quot;, &quot;isDone&quot;: true}&#39;</pre><p>Remove a todo:</p><pre>curl -i -X DELETE http://localhost:5000/todos/019a3a1c-5e00-7000-8000-000000000001</pre><p>Each call hits one of the six endpoints, returns the matching status code, and leaves the in-memory list in its new state.</p><h4>Wrapping up</h4><p>With Minimal APIs, you can build a functional REST API with just a few lines of C# and a single Program.cs file. ASP.NET Core handles much of the plumbing for you, from route mapping and parameter binding to JSON serialization and HTTP responses, letting you focus on the application logic.</p><p>We covered the four core CRUD operations (GET, POST, PUT, and DELETE), plus the newer QUERY method for more complex, safe search operations. We also used UUIDv7 to generate time-ordered identifiers without adding external dependencies.</p><p>Of course, an in-memory list is only a starting point. For a production application, you’ll likely want to introduce persistent storage, dependency injection, validation, authentication, logging, and proper error handling. (A plain List&lt;T&gt; also isn’t thread-safe, so it’s fine for a demo but not for real traffic.) Minimal APIs support all of these features without forcing you into a controller-based architecture.</p><p>The whole thing is about sixty lines. Run dotnet new web, paste the handlers in, and you have a working CRUD API in the time it takes to read this.</p><p>Minimal doesn’t mean limited. You can start with a single file, keep your API straightforward, and gradually introduce the structure and features your application needs as it grows.</p><h3>About me</h3><p>I am a senior backend engineer with 18+ years of experience building software across different industries. I specialize in building complex software from scratch, backend architecture, and cloud-based systems, with a growing focus on AI-powered solutions.</p><p>He enjoys turning complex technical challenges into pragmatic, scalable solutions. <strong>Always building, learning, and exploring new ideas. 🚀</strong></p><p><strong>Connect with me:</strong> <a href="https://www.linkedin.com/in/johnidouglas"><strong>LinkedIn</strong></a> 🚀</p><p><strong>Learn more about the:</strong> <a href="https://ace.oracle.com/"><strong>Oracle ACE Program</strong></a> 💡⭐️</p><p><strong>Oracle ACE Associate:</strong> <a href="https://ace.oracle.com/ords/ace/profile/johnidouglas"><strong>Johni Douglas</strong></a> 🏅💻</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=54bb8064f66a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Getting a list of available proxies to use with Selenium and Requests in Python]]></title>
            <link>https://medium.com/@johnidouglasmarangon/getting-a-list-of-available-proxies-to-use-with-selenium-and-requests-in-python-7a71f16dee32?source=rss-23c93e39f596------2</link>
            <guid isPermaLink="false">https://medium.com/p/7a71f16dee32</guid>
            <category><![CDATA[selenium]]></category>
            <category><![CDATA[proxy]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Johni Douglas Marangon]]></dc:creator>
            <pubDate>Wed, 28 Oct 2026 18:43:50 GMT</pubDate>
            <atom:updated>2026-10-28T18:43:50.390Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*qf9I-JzKkk8AXXDlFRk2WA.jpeg" /></figure><p>Recently, I was part of a project where my team and I needed to run multiple crawlers on the same websites. We faced several challenges, but two were particularly common: <strong>rate limiting and IP blocking.</strong></p><p>One solution we adopted was routing requests through a pool of proxies to distribute traffic across multiple IP addresses. It’s one of the simplest ways to help keep a web scraper running reliably.</p><p>In this article, I’ll share one part of our pipeline that:</p><ol><li>scrapes free proxy lists from a couple of public sources.</li><li>tests each proxy and keeps only the ones that actually respond, and</li><li>plugs a working proxy into both requests and Selenium.</li></ol><p>It leans on a handful of libraries — requests, lxml, selenium, and selenium-wire — and the whole thing fits in a single notebook.</p><h3>What is a proxy?</h3><p>A proxy server sits between your program and the website you’re trying to reach. Instead of connecting directly, your request goes to the proxy, the proxy forwards it to the target, and the response travels back the same way.</p><p>The useful side effect is that the target sees the proxy’s IP address, not yours. Point your requests at enough different proxies, and a single machine can appear to be reaching out from dozens of locations at once.</p><h3>Why use proxies when crawling?</h3><p>When you’re collecting data at any scale, proxies solve a few recurring problems:</p><ul><li><strong>Avoiding bans and rate limits.</strong> Most sites track how often a given IP hits them. Spread the same volume of requests across many addresses, and you stay under the per-IP thresholds that trigger blocks.</li><li><strong>Resilience.</strong> If one proxy gets blocked or dies mid-run, you rotate to the next one instead of grinding to a halt.</li><li><strong>Geo access.</strong> Some content varies by country. A proxy in the right region lets you see what a local visitor sees.</li><li><strong>Anonymity.</strong> The target never sees your real address.</li></ul><p>One honest caveat up front: free proxies—the kind we’re about to scrape—are the least reliable option out there. They’re perfect for learning and small jobs, but they go offline constantly and shouldn’t be trusted with anything sensitive. More on that later.</p><h3>Scraping free proxy lists</h3><p>We’ll pull proxies from two public sources and normalize both into the same ip:port string format.</p><p>The first source is <a href="https://free-proxy-list.net/">free-proxy-list.net</a>, which publishes an HTML table. We fetch the page and parse it with lxml, walking each table row and grabbing the IP (column 1) and port (column 2). The XPath condition on column 7 keeps only the rows that look like real entries — that column holds the yes/no HTTPS flag.</p><pre>import requests<br>from lxml.html import fromstring<br> <br> <br>def get_proxies_from_freeproxylist():<br>    response = requests.get(&quot;https://free-proxy-list.net/&quot;)<br>    parser = fromstring(response.text)<br>    proxies = []<br>    for row in parser.xpath(&quot;//tbody/tr&quot;):<br>        if row.xpath(&#39;.//td[7][contains(text(), &quot;yes&quot;) or contains(text(), &quot;no&quot;)]&#39;):<br>            ip = row.xpath(&quot;.//td[1]/text()&quot;)[0]<br>            port = row.xpath(&quot;.//td[2]/text()&quot;)[0]<br>            proxies.append(f&quot;{ip}:{port}&quot;)<br>    return proxies</pre><p>The second source is <a href="https://proxyscrape.com/">ProxyScrape</a>, which exposes a plain-text API — one ip:port per line — so there&#39;s nothing to parse beyond splitting on whitespace.</p><pre>def get_proxies_from_proxyscrape():<br>    url = (<br>        &quot;https://api.proxyscrape.com/v2/&quot;<br>        &quot;?request=displayproxies&amp;protocol=http&quot;<br>        &quot;&amp;timeout=10000&amp;country=all&amp;ssl=all&amp;anonymity=all&quot;<br>    )<br>    response = requests.get(url)<br>    return response.text.split()</pre><h3>Combining the sources</h3><p>Both functions hand back lists of ip:port strings, so merging them is just concatenation:</p><pre>proxies = get_proxies_from_freeproxylist() + get_proxies_from_proxyscrape()<br>print(f&quot;Collected {len(proxies)} proxies&quot;)</pre><p>At this point proxies holds hundreds of candidates — but most of them won&#39;t work. Free proxies come and go by the minute, so the next step is to throw out the dead ones.</p><h3>Filtering for working proxies</h3><p>To find the live ones, we send a quick request through each proxy and keep whichever answer. <a href="https://api.myip.com/">api.myip.com</a> makes a cheap test target — it just echoes back the IP address it saw.</p><p>Checking proxies one at a time would take forever, because every dead proxy has to hit its timeout before we give up on it. So we run the checks concurrently with a thread pool. A tight one-second timeout stops slow proxies from holding up the batch, and a broad except clause swallows the many ways a bad proxy can fail.</p><pre>import concurrent.futures<br> <br>from requests.exceptions import (<br>    ChunkedEncodingError,<br>    ConnectionError,<br>    ConnectTimeout,<br>    HTTPError,<br>    ProxyError,<br>    ReadTimeout,<br>    SSLError,<br>)<br> <br>url = &quot;https://api.myip.com/&quot;<br>headers = {<br>    &quot;User-Agent&quot;: (<br>        &quot;Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:80.0) &quot;<br>        &quot;Gecko/20100101 Firefox/80.0&quot;<br>    )<br>}<br> <br> <br>def check(proxy):<br>    try:<br>        response = requests.get(<br>            url,<br>            headers=headers,<br>            proxies={&quot;http&quot;: proxy, &quot;https&quot;: proxy},<br>            timeout=1,<br>        )<br>        response.raise_for_status()<br>        return proxy<br>    except (<br>        ReadTimeout,<br>        HTTPError,<br>        ProxyError,<br>        ConnectTimeout,<br>        SSLError,<br>        ConnectionError,<br>        ChunkedEncodingError,<br>    ):<br>        return None<br> <br> <br>with concurrent.futures.ThreadPoolExecutor(max_workers=50) as executor:<br>    results = executor.map(check, proxies)<br> <br>available_proxies = list(filter(None, results))<br>print(f&quot;{len(available_proxies)} proxies are alive&quot;)</pre><p>check() returns the proxy string on success and None on any failure. filter(None, results) drops the Nones, and wrapping it in list() materializes the survivors so we can reuse them. Don&#39;t skip that list() call: executor.map hands back a lazy, one-shot iterator, and we&#39;ll want to reach into this collection more than once.</p><h3>Free vs. commercial proxies</h3><p>It’s worth being honest about what we just built. Free proxy lists are convenient and cost nothing, but they come with real trade-offs:</p><ul><li><strong>They die fast.</strong> A proxy that passed the check a minute ago may already be gone by the time you use it. Expect to re-scrape and re-filter often.</li><li><strong>They’re slow and crowded.</strong> You’re sharing them with everyone else who scraped the same list.</li><li><strong>They’re not private.</strong> You have no idea who runs a free proxy or what they do with the traffic passing through it — so never send credentials or anything sensitive over one.</li></ul><p>For anything beyond experiments and small one-off jobs, a paid proxy service is usually worth the money. Commercial providers give you pools that are faster, far more stable, and actually accountable — typically with built-in rotation, so you don’t have to manage the list yourself.</p><h3>Using a proxy with Selenium</h3><p>requests is enough for static pages, but plenty of sites render their content with JavaScript, and for those you need a real browser. Selenium drives one — and with the selenium-wire extension, pointing that browser at a proxy is straightforward.</p><p>First, install the browser driver and the libraries. These ! commands are written for a notebook such as Google Colab; drop the ! if you&#39;re running them in a shell.</p><pre>!apt install chromium-chromedriver -q<br>!pip install selenium selenium-wire -q</pre><p>Pick one of the proxies that survived the filter. Choosing at random on each run spreads your requests around:</p><pre>import random<br> <br>proxy = random.choice(available_proxies)<br>print(f&quot;Using proxy: {proxy}&quot;)</pre><p>Now configure selenium-wire to route traffic through it. The proxy settings go in a seleniumwire_options dict, while the usual headless flags stay in the regular Chrome options. Running headless with --no-sandbox and --disable-dev-shm-usage keeps Chrome happy inside containers and notebooks.</p><pre>from seleniumwire import webdriver<br> <br>seleniumwire_options = {<br>    &quot;proxy&quot;: {<br>        &quot;http&quot;: f&quot;http://{proxy}&quot;,<br>        &quot;https&quot;: f&quot;https://{proxy}&quot;,<br>        &quot;no_proxy&quot;: &quot;localhost,127.0.0.1&quot;,<br>    }<br>}<br> <br>chrome_options = webdriver.ChromeOptions()<br>chrome_options.add_argument(&quot;--headless&quot;)<br>chrome_options.add_argument(&quot;--no-sandbox&quot;)<br>chrome_options.add_argument(&quot;--disable-dev-shm-usage&quot;)<br>chrome_options.add_argument(&quot;--ignore-certificate-errors&quot;)<br> <br>driver = webdriver.Chrome(<br>    seleniumwire_options=seleniumwire_options,<br>    options=chrome_options,<br>)<br> <br>driver.get(&quot;https://api.myip.com/&quot;)<br>body = driver.execute_script(&quot;return document.body.innerHTML;&quot;)<br>driver.quit()<br> <br>print(f&quot;Response via Selenium: {body}&quot;)</pre><p>If everything’s wired up correctly, the IP in that response will be the proxy’s — not yours.</p><h3>Wrapping up</h3><p>That’s the whole pipeline: scrape free proxies from a couple of sources, concurrently test them down to the ones that actually respond, and feed a live proxy into either requests or Selenium.</p><p>A few directions to take it further:</p><ul><li><strong>Rotate per request</strong> instead of per run, so one proxy going down doesn’t sink a long job.</li><li><strong>Re-validate on the fly</strong>, dropping a proxy from the pool the moment it fails mid-crawl.</li><li><strong>Cache working proxies</strong> to disk so you’re not re-scraping from scratch every time.</li><li><strong>Graduate to a paid pool</strong> once you outgrow the free lists — the surrounding code barely changes.</li></ul><p>Start with the free version to get the shape of it, then upgrade the proxy source once reliability starts to matter.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7a71f16dee32" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>