Build with DEX · 32 reference entries
Write bounded SQL queries, measure index choices, preserve data integrity and plan database changes with recovery evidence.
The SQL examples use SQLite conventions, with Cloudflare D1 examples labelled by their env.DB API. Tables and fields are illustrative. Test changes against your own schema and data before applying them to a live database.
Choose an entry or use Find on page to look for a term. Each number identifies an educational reference; production checks need their own recorded result.
WEB-DB-001
Choose columns, types and constraints around the records the application actually stores. Keep identifiers stable and name the owner relationship.
CREATE TABLE notes (
id TEXT PRIMARY KEY NOT NULL,
owner_id TEXT NOT NULL,
title TEXT NOT NULL,
created_at TEXT NOT NULL
);Documentation: Database documentation
WEB-DB-002
Bind values to a prepared statement instead of building SQL from user input. Parameters represent data values, not SQL identifiers.
const note = await env.DB.prepare("SELECT id, title FROM notes WHERE id = ? AND owner_id = ?").bind(noteId, userId).first();Documentation: Database documentation
WEB-DB-003
Select only the columns required by the operation. This limits transfer and helps prevent future internal fields entering a public response.
SELECT id, title FROM notes WHERE owner_id = ? ORDER BY created_at DESC, id DESC LIMIT 25;Documentation: Database documentation
WEB-DB-004
Map a permitted sort choice to fixed SQL. A bound parameter cannot safely stand in for an arbitrary column name or direction.
const orders = { newest: "created_at DESC, id DESC", title: "title ASC, id ASC" };
const order = orders[sort] ?? orders.newest;Documentation: Database documentation
WEB-DB-005
Put a deliberate limit on listing queries and on client-requested page sizes. An unbounded response can exhaust memory even when the query is fast.
SELECT id, title FROM notes WHERE owner_id = ? ORDER BY id LIMIT ?;Documentation: Database documentation
WEB-DB-006
Use IS NULL or IS NOT NULL to test missing values. Equality comparisons with NULL do not behave like ordinary value comparisons.
SELECT id FROM notes WHERE archived_at IS NULL;Documentation: Database documentation
WEB-DB-007
Enforce business uniqueness in the database when duplicate records would be invalid. Application checks alone can race.
CREATE UNIQUE INDEX notes_owner_slug ON notes(owner_id, slug);Documentation: Database documentation
WEB-DB-008
Define and test relationships where records depend on one another. Choose deletion behaviour intentionally before introducing a constraint.
CREATE TABLE comments (
id TEXT PRIMARY KEY NOT NULL,
note_id TEXT NOT NULL REFERENCES notes(id),
body TEXT NOT NULL
);Documentation: Database documentation
WEB-DB-009
Use a check constraint for a rule the stored value must always satisfy. Keep validation messages in the application understandable as well.
CREATE TABLE ratings (
id TEXT PRIMARY KEY NOT NULL,
score INTEGER NOT NULL CHECK(score BETWEEN 1 AND 5)
);Documentation: Database documentation
WEB-DB-010
Use a conflict target that matches the intended unique key. Confirm which fields an update should preserve before replacing values.
INSERT INTO preferences(owner_id, theme) VALUES (?, ?)
ON CONFLICT(owner_id) DO UPDATE SET theme = excluded.theme;Documentation: Database documentation
WEB-DB-011
Inspect frequent slow queries and their plans. Add an index for a real predicate, join or ordering pattern, then measure its effect.
EXPLAIN QUERY PLAN SELECT id, title FROM notes WHERE owner_id = ?;Documentation: Database documentation
WEB-DB-012
A selective indexed predicate can avoid scanning every row. Indexes also add write and storage work, so keep a purpose for each one.
CREATE INDEX notes_owner ON notes(owner_id);Documentation: Database documentation
WEB-DB-013
Arrange a multi-column index around the query's equality filters and ordering needs. Verify the actual plan instead of relying on the column list alone.
CREATE INDEX notes_owner_created ON notes(owner_id, created_at DESC, id DESC);Documentation: Database documentation
WEB-DB-014
An index that contains the required search and output columns may avoid extra table lookups. Balance that gain against index size and write cost.
Candidate for measurement: an index on owner_id, created_at, id and title for a frequent compact listing.Documentation: Database documentation
WEB-DB-015
Keep an indexed column usable by the query planner. A range can be more suitable than transforming every stored value during filtering.
SELECT id FROM notes WHERE created_at >= ? AND created_at < ?;Documentation: Database documentation
WEB-DB-016
For a stable ordered collection, continue after the last returned sort key. Include a unique tie-breaker to avoid ambiguous page boundaries.
SELECT id, title FROM notes
WHERE owner_id = ? AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC LIMIT 25;Documentation: Database documentation
WEB-DB-017
Offset pagination is simple but skipping a large prefix can cost more work. Compare it with keyset pagination for deep or frequently changing collections.
SELECT id, title FROM notes ORDER BY id LIMIT 25 OFFSET 250;Documentation: Database documentation
WEB-DB-018
Look for a list query followed by one query per result. Fetch related data in an appropriate join or bounded batch and measure the plan.
SELECT comments.id, comments.body, notes.title
FROM comments JOIN notes ON notes.id = comments.note_id
WHERE notes.owner_id = ? LIMIT 100;Documentation: Database documentation
WEB-DB-019
Use an aggregate when only a count or total is needed. Avoid transferring every row solely to compute a simple result in application memory.
SELECT owner_id, COUNT(*) AS note_count FROM notes GROUP BY owner_id;Documentation: Database documentation
WEB-DB-020
Inspect rows read, rows written and timing metadata alongside latency. A small returned result can still require a large scan.
const result = await env.DB.prepare("SELECT id FROM notes WHERE owner_id = ? LIMIT 25").bind(userId).all();
const metrics = result.meta;Documentation: Database documentation
WEB-DB-021
Use a transaction when related changes must succeed together. In D1, batch executes prepared statements as a transactional sequence.
await env.DB.batch([
env.DB.prepare("INSERT INTO notes(id, owner_id, title, created_at) VALUES (?, ?, ?, ?)").bind(id, userId, title, createdAt),
env.DB.prepare("INSERT INTO note_events(note_id, event) VALUES (?, ?)").bind(id, "created")
]);Documentation: Database documentation
WEB-DB-022
Import in measured batches within the platform's current limits. Track progress so an interrupted import can resume without duplicating already accepted records.
Batch record: source identifier, input range, applied row count, completion state and retry key.Documentation: Database documentation
WEB-DB-023
A failed response may leave uncertainty about whether a write succeeded. Use a unique operation identifier or an equivalent verified contract before retrying writes.
Store a unique operation_id with the effect; a replay checks that record before applying the operation again.Documentation: Database documentation
WEB-DB-024
When read replication is used, choose the freshness needed for a session. First-primary starts from the primary; bookmarks carry a minimum database version.
const session = env.DB.withSession("first-primary");
const result = await session.prepare("SELECT id, title FROM notes WHERE owner_id = ? LIMIT 25").bind(userId).all();Documentation: Database documentation
WEB-DB-025
Detect an edit based on stale state when silently overwriting it would lose work. A version field can support an optimistic concurrency check.
UPDATE notes SET title = ?, version = version + 1 WHERE id = ? AND owner_id = ? AND version = ?;
-- Check whether a row was changed.Documentation: Database documentation
WEB-DB-026
Cache data only when its freshness and privacy requirements permit it. Include the scope that makes a response unique and define how updates invalidate it.
Public reference cache: route + content version
Private account data: dedicated private policyDocumentation: Database documentation
WEB-DB-027
Separate high request volume, expensive scans, large writes and upstream delay. A different cause requires a different fix.
Compare request rate, error rate, query duration, rows read, rows written and concurrent work.Documentation: Database documentation
WEB-DB-028
Check current per-query and platform limits before designing an import or large operation. Keep a bounded failure path rather than growing a batch indefinitely.
Document the platform limit, selected batch size, observed duration and recovery checkpoint.Documentation: Database documentation
WEB-DB-029
Version schema changes and test them against a representative copy. Plan compatibility between the old and new application versions.
Migration record: intended schema, data preservation checks, deployment order and recovery approach.Documentation: Database documentation
WEB-DB-030
A backup needs a tested restoration process. Check the available recovery window and verify important records after a restore in an appropriate environment.
Restore exercise: selected recovery point → isolated validation → record count and critical relationship checks.Documentation: Database documentation
WEB-DB-031
Apply an approved deletion rule in bounded batches and check the affected rows. Define recovery and retention requirements before executing the operation.
DELETE FROM notes WHERE id IN (SELECT id FROM notes WHERE archived_at < ? ORDER BY archived_at, id LIMIT 100);Documentation: Database documentation
WEB-DB-032
Verify query results and failure behaviour with representative data. A reference snippet cannot prove production capacity or data integrity.
Record query, dataset size, execution plan, latency, row counts, expected result and observed result.Documentation: Database documentation
Reviewed . Send a correction through contact and corrections, including the entry number and supporting documentation.
Explore offers, save a shortlist and buy with the retailer.
Confirm current prices and offer terms with the retailer. Device saves start in this browser. Account sync is optional.
Read what recorded checks establish and what remains unconfirmed.
Country labels do not confirm delivery or convert prices.
Prepare a report to review and send yourself. Preparing it does not send it.
Open the tour on the guide page when you choose. You can skip or close it at any time.