Day 2: Store it in Postgres

Python Backend Basics: Build the ShopStream API ~4 min read 30 min hands-on

Move the catalog into Postgres with migrations, watch a string-built query leak every row, and make a lookup 170x faster with an index.

Restart yesterday's API and every product you added is gone. Real data lives in a database. Today ShopStream's catalog moves into Postgres, and you meet the two mistakes that hurt beginners most: building SQL from user input, and searching a big table without an index. You'll see both fail, with numbers.

What you build

  • Postgres 16 in its own container, next to the API.

  • A connection pool (psycopg_pool): up to 20 open connections that requests borrow and return, instead of opening a new one per request.

  • Versioned migrations: migrations/001_products.sql, 002_products_name_index.sql. app/migrate.py applies each file once, in order, inside a transaction, records it in schema_migrations, and holds a lock so two instances starting together don't both run it.

  • A safe search β€” GET /products/search?q= β€” and, for this lesson only, an unsafe one built with string formatting, so you can watch it break.

Parameters, not string building

With the second version, a user who types a quote character can end your string and write their own SQL.

What we measured

SQL injection, same input to both endpoints:

The second input turns the WHERE clause into "always true" and comments out the rest. The third glues a second query onto the first and reads data the endpoint was never meant to return. The parameterised query searched for that exact text and found nothing.

Query time, looking up a product by name among 200,000 rows:

Read the full lesson free

This lesson is one of the first 3 in its course, free with an account β€” the full article, implementation guide and scripts. No card needed.

Already a member? Sign in