SQL

The application can read and write the database through an ORM, but important reports are slow and several code paths disagree about what a valid record is. SQL is already part of the product whether the developers write it directly or a library generates it.

SQL defines and queries relational data, enforces constraints and controls transactions. Its core ideas travel between PostgreSQL, MySQL, SQL Server and SQLite, while dialect, locking, types and operational behaviour differ. The useful decision is which data rules belong in the database and how generated and handwritten queries will be inspected.

We treat schema and queries as production code. Hiding SQL behind an abstraction does not remove its performance or correctness effects.

SQL is the executable interface to the data model

Tables, keys and relationships describe durable business facts. A customer, order or permission model that exists only in application classes can drift from the records every other tool and job reads.

We design from the domain and access patterns, not from one screen. Primary keys identify records; foreign keys preserve relationships; data types express valid representation. Naming should remain meaningful to analysts and future applications rather than mirror temporary interface labels.

Normalisation reduces duplicated facts and inconsistent updates. Deliberate denormalisation can improve a measured read path when the source, refresh and repair rules are clear. Copying fields for convenience without that ownership creates competing truth.

The database product still matters. PostgreSQL, MySQL, SQL Server and SQLite support different types, indexes and concurrency models. “It is SQL” does not make the schema portable automatically.

Constraints protect data across every writer

Application validation gives users useful feedback. Database constraints protect the invariant when an import, background job, admin script or second service writes the same table.

We use NOT NULL, unique, check and foreign-key constraints for rules the database can state accurately. A constraint name and handled error can still produce a clear application message.

Some business rules depend on time, external state or several records and may not fit a simple constraint. Transactions, triggers or application services can own them, but the boundary must be documented. Duplicating the rule in several places is acceptable only when one is authoritative and tests prove alignment.

Adding a constraint to old data needs discovery and repair. A migration that validates millions of existing rows can lock or scan a live table, so deployment strategy matters as much as the final declaration.

Transactions define which partial outcomes are acceptable

A transaction groups changes into an atomic unit from the database’s point of view. The PostgreSQL transaction tutorial shows commit, rollback and savepoints. Isolation determines which concurrent changes a transaction can observe.

We define the business unit first. Creating an order and reserving stock may belong together; sending an email cannot be rolled back by the database. An outbox or reconciled job can bridge durable database state to external side effects.

Long transactions retain locks and old row versions and can block other work. Interactive user think-time never belongs inside one. Batch jobs process bounded chunks when atomicity allows it.

Deadlocks are a normal possible outcome of concurrent locking, not proof the database is broken. Code acquires resources in consistent order and retries a failed transaction only when the entire operation is safe to repeat.

Query performance depends on plans and real data shape

SQL is declarative: the query states the result and the optimiser chooses an execution plan. Indexes, statistics, row counts, value distribution and parameter values influence that choice.

PostgreSQL’s EXPLAIN guide demonstrates estimated and actual plans. We use the relevant database’s plan tools with representative data and timings rather than judging a query by length.

An index speeds some reads while consuming storage and adding work to writes. Multi-column order and partial predicates follow actual filters and sorting. Creating indexes for every column can make updates slower without serving a real query.

Application behaviour matters too. An ORM loop issuing one query per record can dominate latency despite each query being fast. Query count, rows returned and round trips are measured at the customer route.

Pagination avoids unbounded results. Offset pagination can become expensive and unstable on rapidly changing large sets; keyset pagination may better match a known order.

Parameters separate values from executable SQL

Untrusted values must not be concatenated into SQL text. Prepared or parameterised queries pass values separately so quotes and operators inside the value do not change the statement structure.

OWASP’s SQL Injection Prevention Cheat Sheet recommends prepared statements and safely implemented stored procedures, with allow-list validation for structural parts that cannot be parameters.

Table names, sort directions and column identifiers usually cannot be bound as normal values. We map a narrow external choice to known SQL fragments rather than accepting arbitrary identifiers.

Database users receive least privilege. A public application account rarely needs schema modification or access to every table. Parameterisation limits injection; permissions limit impact when application logic or credentials fail.

Query logs and error messages avoid sensitive values. Debug visibility should not create a second database of personal data in telemetry.

SQL dialect features can be worth deliberate dependence

Portable core SQL makes skills and some queries transferable. Database-specific capabilities such as PostgreSQL JSON operators and partial indexes, SQL Server temporal tables or MySQL generated columns can solve real requirements more clearly and efficiently.

Avoiding every useful feature for hypothetical migration can create a private compatibility layer with its own cost. We use vendor features when the product value exceeds the documented exit work.

The dependency is recorded beside migrations and queries. Compatibility tests run against the exact production database, not an in-memory substitute with different types and locking. SQLite can be an excellent product database; it is not automatically a faithful test double for another engine.

If several databases must be supported as a product requirement, dialect queries, migration paths and behavioural tests are isolated explicitly.

Schema migrations are live application releases

Schema and application versions can overlap during rolling or reversible deployments. A migration that renames or drops a column before old application instances stop can turn a routine release into an outage.

We favour expand-and-contract changes: add compatible structure, deploy code that can use it, backfill and verify, then remove the old path in a later release. Large backfills are observable jobs with throttling and restart state rather than one opaque deployment command.

Migration tools record order, but they do not make every change safe or reversible. Lock duration, table rewrite, disk growth and replication effect are checked against the engine and data size.

Rollback may mean rolling the application forward to a compatible fix while leaving the new schema. A destructive down migration is often worse than the original error.

Backups and restore tests protect data; migrations protect change. Both are required.

ORMs, query builders and handwritten SQL can coexist

An ORM is productive for common record lifecycle and relationships and can keep application code consistent. It should expose generated SQL, query count and transaction control. Developers still need enough SQL to diagnose its output.

Query builders preserve parameterisation and composition while keeping SQL structure visible. Handwritten SQL is appropriate for complex reports, bulk changes and performance-sensitive paths where precise control improves clarity.

Stored procedures can keep data-intensive operations close to the database and offer a stable permission boundary. They also introduce another deployment and testing surface and can concentrate vendor dependence.

We select the route per workload and define where queries live. A team should not bypass an ORM casually in scattered controllers, nor force a difficult analytical query through an object abstraction for ideological consistency.

Questions to answer before changing the SQL layer

• “Which database is authoritative for each business fact?” Design schema around truth.
• “Which invariants must survive every writer?” Put enforceable rules in constraints.
• “What is the atomic business transaction?” Separate external side effects deliberately.
• “Which query and data volume represent production?” Inspect plans and round trips.
• “Where can external input alter SQL structure?” Parameterise values and allow-list identifiers.
• “Which dialect features are worth the exit cost?” Record the dependency.
• “Can old and new application versions overlap this migration?” Expand before contracting.

When direct SQL ownership is a strength

Every relational application benefits from developers who can read schemas, transactions and query plans, even when most access goes through an ORM. Direct SQL is especially valuable for reports, bulk work and critical routes where generated behaviour needs proof.

The decision is sound when schema changes, queries and permissions are reviewed and tested against production-like data on the actual engine.

When SQL is being used for the wrong job

Do not move every business workflow into triggers and procedures when the team cannot test, version and observe that layer. Keep orchestration in an application service where its dependencies are clearer.

Avoid dynamic SQL assembled from external strings, even for internal administration. Privileged tools deserve stronger input controls because their impact is larger.

And do not replace a relational model with another datastore solely to escape a slow query. First inspect query shape, indexes, round trips and data growth; the problem may be in how SQL is being used.