Database inspection is often the fastest way to disprove an application assumption before release. It is also one of the fastest ways to damage important state when the target, authority, or query is ambiguous.
A safe workflow makes read-only behavior the default, identifies the connection continuously, bounds every result, and treats write access as a separate operational mode.
Name the target before connecting
Do not rely on color alone to distinguish local, staging, and production. Display a durable identity containing provider, account or project, database name, region, and access mode.
Before running a query, answer:
- Which environment is this?
- Which database identifier is selected?
- Is the connection read-only?
- Which account or token provides authority?
- When was the last backup or export?
- Is the schema version compatible with the application under review?
If any answer is unclear, stop. A connection string hidden behind a friendly label is not enough evidence.
Store credentials in a deployment secret manager or operating-system credential vault. Do not place them in repository files, browser storage, shared query history, screenshots, or shell arguments.
Start with schema and constraints
Inspect tables, columns, indexes, foreign keys, uniqueness, checks, and defaults before reading application rows. The schema defines which application claims the database can enforce.
For a subscription system, verify that provider subscription identifiers are unique and each account has the intended provider cardinality. For rotating sessions, verify unique refresh-token hashes and indexes supporting account and family revocation. For analytics, verify the event ID is unique so retried batches remain idempotent.
Constraints convert concurrency bugs into controlled conflicts. Application checks alone cannot prevent two simultaneous requests from observing the same absence.
With SQLite-compatible systems, explicitly enable and verify foreign-key behavior in the relevant execution environment. Do not assume a pragma from one connection applies universally.
Use read-only access by default
The normal database tool mode should reject writes before they reach the provider. Classification is not perfect SQL security, but it prevents accidental execution and makes authority visible.
Allow read operations such as SELECT, schema inspection, and query plans. Treat data-changing common table expressions, provider-specific pragmas, and multi-statement input carefully. A robust implementation should use a parser or provider-enforced read-only credentials rather than a prefix check alone.
Read-only results still contain sensitive data. Mask or omit credentials, password hashes, encrypted payloads, tokens, personal content, and private source material. Export must be a separate explicit action with a visible destination.
Bound every exploratory query
Add a result limit and execution timeout. Avoid SELECT * on large or sensitive tables.
SELECT id, status, updated_at
FROM subscriptions
ORDER BY updated_at DESC
LIMIT 100;
Use stable ordering for pagination. Offset pagination can become inconsistent while rows change; cursor pagination based on an indexed unique tuple is safer for operational lists.
Inspect query plans before releasing a new high-frequency query:
EXPLAIN QUERY PLAN
SELECT id, status
FROM subscriptions
WHERE account_id = ? AND provider = ?;
Confirm that predicates match index order and that the expected row count is bounded. An index that exists but starts with a different column may not serve the query efficiently.
Review migrations as compatibility changes
A migration is correct only in relation to old data and deployment order.
Check:
- It applies to a database at the currently deployed schema version.
- Existing rows receive valid values.
- New constraints do not reject historical state unexpectedly.
- Index creation has acceptable cost.
- The old application can run while the new schema is present when rollout overlaps.
- The new application can tolerate the pre-migration schema if it can start early.
- Partial application is detectable.
- Recovery is documented.
Prefer expand-and-contract for breaking transitions. Add the new structure, write compatible data, backfill, switch reads, then remove old structure in a later release. Trying to rename, rewrite, and enforce in one deployment creates a narrow rollback window.
Cloudflare D1 migrations should be tested against D1 locally and in an isolated remote database. SQLite syntax compatibility does not guarantee identical operational behavior across every execution path.
Validate representative old data
New installations are not the difficult case. Build fixtures for historical rows, missing optional relationships, legacy enum values, canceled subscriptions, redeemed lifetime licenses, and partially completed records.
Run the migration, start the new application, and exercise reads and writes against those fixtures. Verify dates, nullability, unique constraints, and foreign-key actions.
Do not migrate old analytics when the product decision is to start clean. Encode that decision in migration and deployment instructions so an operator does not copy event tables by habit.
Separate write mode from browsing
When a write is necessary, require an explicit transition that repeats the database identity and explains the authority change. Time-limit the mode where practical.
Before execution:
- Parse and show the statement type.
- Display the target database.
- Preview affected rows with a read query when possible.
- Require a reason for high-risk administrative changes.
- Use a transaction for related statements.
- Set a maximum affected-row expectation.
- Record sanitized audit metadata.
Avoid logging raw query text when it can contain personal data or credentials. Store a safe operation category, table identifiers, actor, timestamp, reason, and affected-row count.
If the provider cannot give a transaction with the required scope, design an idempotent, resumable operation instead of pretending multiple network requests are atomic.
Verify application data flows
Trace one critical workflow from API to rows. For authentication, confirm password hashes use the intended algorithm, refresh credentials are hashes rather than raw values, rotated sessions change state, and reuse revokes the family.
For lifetime licenses, confirm the lookup uses a keyed HMAC, stored material is authenticated encryption, public lists expose only the last four characters, and full recovery occurs only through an audited, reauthenticated path or a transactional email to the purchase address.
For analytics, inspect a sample event and confirm source code, diffs, request bodies, SQL contents, credentials, and full referrer URLs are absent.
Prepare backup and recovery evidence
Before a risky migration or repair:
- Create a provider-supported export or backup.
- Record its timestamp, database identity, and storage location.
- Verify the backup is non-empty and restorable in an isolated target.
- Define the restore owner and stop condition.
- Preserve the pre-change schema version.
A backup that has never been restored is an assumption.
For large datasets, understand whether export captures a consistent point in time. Coordinate writes or use provider snapshot semantics as required.
Close the database release gate
The database portion of a release is ready when the target is explicit, reads are bounded, queries use expected indexes, migrations succeed against representative history, deployment remains compatible, sensitive fields are protected, and recovery has been tested.
Database tooling should make the safe path the shortest path. Read-only by default, visible identity, deliberate writes, and inspectable evidence turn database access from a risky convenience into a reliable release practice.