Day 2: Store it in Postgres
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.pyapplies each file once, in order, inside a transaction, records it inschema_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