Applications connect via drivers (Python psycopg, PHP PDO, Node pg). Parameterized queries prevent SQL injection; connection pools handle concurrency; ORMs generate SQL you should still understand.
Parameterized query pattern
-- Never concatenate user input:
-- "SELECT * FROM users WHERE email = '" + input + "'"
-- Safe: placeholder bound by driver
SELECT id, name FROM customers WHERE email = ?;Placeholders (?, $1, :email) differ by driver—always bind parameters.
N+1 problem
Loading 100 orders then 100 separate customer queries is an N+1 antipattern. Fix with JOIN or ORM select_related / eager loading—see Django docs.
Read vs write paths
-- Read replica routing (conceptual)
SELECT * FROM orders WHERE customer_id = ?;
-- Writes go to primary
INSERT INTO orders (customer_id, total) VALUES (?, ?);Analytics may query replicas; transactional writes hit primary. Python data scripts often use read-only connections for reports.
Important interview questions and answers
- Q: SQL injection fix?
A: Parameterized queries—never string-concatenate untrusted input. - Q: ORM replaces SQL?
A: No—ORM emits SQL; complex reports and tuning need raw queries.
Self-check
- Why use query placeholders?
- What is the N+1 query problem?
Pitfall: Never concatenate user input into SQL—always bind parameters.
Interview prep
- SQL injection fix?
Parameterized queries with bound values.
- N+1 problem?
Loop triggers one query per row instead of batch JOIN.