Relational data & SQL
Persist jobs with constraints that defend the model even when application code makes a mistake.
BY THE END, YOU CAN
- Model entities, keys, and relationships
- Use parameterized queries
- Explain how an index helps reads and costs writes
Diagram description: jobs → users (belongs to); INDEX → jobs (fast reads)
Make identity and constraints explicit
A relational table describes records with named columns.
A primary key identifies each row; foreign keys can connect jobs to an owning project or user. NOT NULL, UNIQUE, and CHECK constraints enforce rules close to the data. Application validation improves error messages, while database constraints defend against every writer, including scripts and concurrent requests.
For Relay, a jobs table needs a durable identifier, a status, an attempt count, and timestamps with documented meaning. Store structured fields you actually query rather than hiding every decision inside an opaque blob.
Query with values, not string assembly
SQL selects rows declaratively: describe the result you need and let the database choose an execution plan.
Bind user-provided values as parameters through the database driver. Formatting those values into SQL text can change the query's meaning and create injection vulnerabilities. Parameter syntax varies by driver, so follow its documented interface.
Also specify ordering when order matters; a query without ORDER BY does not promise a stable row order. Limit returned rows and paginate large histories instead of loading the entire table.
# Python sqlite3 parameter binding
cursor.execute(
"SELECT id, status FROM jobs WHERE owner_id = ? ORDER BY id LIMIT ?",
(owner_id, 50),
)An index is a maintained shortcut
An index can help locate rows without scanning the entire table, but every relevant write must maintain it.
Choose indexes from real filters and ordering requirements. A worker looking for pending jobs by due time may benefit from an index designed for that access pattern. Use the database's plan inspection tools and representative data to check assumptions.
More indexes are not automatically better: they use storage, slow writes, and may be ignored by the planner. Measure the workload instead of indexing every column by habit.
COMMON FAILURE MODE
An application-only uniqueness check can race with another request. Use a database uniqueness constraint for the final guarantee.
TRY IT YOURSELF
Design a jobs table with identity, owner, status, attempts, and due time. Choose one constraint and one index, and explain the failure each prevents or operation it accelerates.
Make the call in the laboratory.
The two-question checkpoint, your notes, and your saved progress are in the laboratory. Free, with no account.
Open in the laboratory ↗CONNECT IT TO RELAY
Persist Relay jobs in SQLite using parameterized queries and database constraints. Document the query used to find runnable jobs.