Raw SQL vs. ORM: choose by workload

Neither raw SQL nor an object-relational mapper (ORM) wins every workload. An ORM maps application objects to database tables and generates SQL. My default is an ORM for relationship-heavy create, read, update, and delete (CRUD) work. I prefer raw SQL for bulk processing and reporting.

Whatever you choose, make queries visible. Define transactions, control migrations, measure performance, protect data, monitor production, and name an owner.

TL;DR

Choose a database access method for each workload.
Treat ORM-generated SQL as production code that needs an owner.
Define transactions, migrations, security, and monitoring for every path.
Use both ORM and raw SQL when workloads differ.
Test the real workload before choosing a winner.

Match the tool to the workload

A service can run transactions, bulk jobs, and reports against the same database. Each workload has different risks. One service can need more than one database access method.

Transactions: start with the business operation

An ORM is often a good starting point when one operation changes a small group of related records. It can track those records and save their changes together.

The Jakarta Persistence specification defines a persistence context. This is the ORM's working set of loaded application records, called entities. Hibernate can track changes in that set and send SQL to the database at commit time or before an affected query.

This convenience can hide database work. Changing an object does not always send SQL at that line of code. Loading related data only when code first uses it can also turn one operation into many queries.

Use an ORM for transactional CRUD when the team can answer:

Where does the business transaction start and end?
When does the ORM send pending changes?
Which related data does the operation load?
How many SQL statements should one request execute?
Which database isolation and locking rules protect the data?
Which failures can the application safely retry?

Use explicit SQL when the exact query, lock behavior, or a database feature matters more than ORM convenience. Do not rewrite unrelated CRUD code only to make every path use the same tool.

Bulk work: process rows as a group

Bulk work is not ordinary CRUD repeated many times.

Loading every row as an object can use too much memory and produce too many database calls. Hibernate supports batches and stateless sessions, which avoid some costs of tracking each object.

For bulk ingestion or transformation, compare:

One INSERT, UPDATE, DELETE, or MERGE that handles a group of rows.
Prepared statement batches that reuse one command with different values.
The ORM's batch or stateless API.
A loading tool provided by the database.

PostgreSQL COPY, for example, moves data between a client or file and a table. Its permissions, validation rules, error handling, and row access controls differ from ordinary inserts. Check those differences in the design.

Choose the simplest path that keeps the data correct, handles failures, and produces the required audit record at the target volume.

Reports: let the database shape the result

Reports often combine rows, calculate totals, and rank results. They usually do not need the ORM to build and track a graph of objects.

Explicit SQL is a strong default when the database can produce the final result directly. It shows the selected columns, joins, filters, groups, and sort order in one place. You can review that SQL with the database execution plan, which shows how the database intends to run it.

An ORM query API is still reasonable when it expresses the report clearly. The generated SQL must also remain stable and easy to inspect. The team must be able to explain and operate the query.

Make every query visible

Use an abstraction only when the team can see what reaches the database.

During development, inspect generated SQL and count statements for each operation. In production, connect an application operation to:

The executed SQL, or the same query shape with sensitive values removed.
The number of statements and rows involved.
The database execution plan.
Response time, errors, retries, and time spent waiting for locks.
The service and code path that owns the query.

PostgreSQL EXPLAIN shows the execution plan. EXPLAIN ANALYZE runs the statement and reports actual rows and timing. Use it carefully with statements that change data.

Generated SQL deserves the same review as handwritten SQL. A query that nobody owns is not safer because a library generated it.

Define transaction behavior

An ORM does not replace the database transaction model. Raw SQL does not make transaction behavior correct automatically.

Java Database Connectivity (JDBC) connections begin in auto-commit mode by default. Each statement commits as its own transaction unless the application turns off auto-commit. The Connection API exposes commit, rollback, and isolation controls.

An ORM adds its own timing. Changes can remain in memory until a flush sends them to the database. Jakarta Persistence defines flush and optimistic locking, which rejects an update when another transaction changed the same record. The database still controls isolation, locks, and the effects of concurrent work.

For each operation, record:

Where the transaction starts and ends.
What concurrent transactions can see.
Which records the application or database locks.
How timeouts and cancellation work.
Which failures are safe to retry.
How the operation avoids duplicate effects after a retry.
Which changes must commit or roll back together.

PostgreSQL's transaction-isolation documentation notes that serializable transactions can fail and require application retries. Serializable is its strictest isolation level. It does not remove the need for a designed retry path.

Control database migrations

ORM mappings describe how application objects relate to the database schema, which is the structure of tables, columns, and other database objects. These mappings are not a complete production migration plan.

Schema changes need:

Migration scripts in version control.
A fixed order and no edits to migrations that have already run.
Tests against realistic data volume and schema.
Compatibility with old and new application versions during deployment.
A plan to fill and validate existing rows.
A safe way to continue forward after failure.
Separate permissions for migrations and normal application work.
A named deployment owner.

Flyway's versioned migration documentation describes ordered migrations and stored checksums that detect later changes. Its transaction documentation explains that rollback support depends on the database and statements.

For a risky change, add the new structure first. Deploy code that works with old and new forms. Fill and validate existing data, move reads, then remove the old structure.

The owner of an entity class should not be able to make an unreviewed production schema change merely by changing an annotation.

Measure performance by workload

"Raw SQL is faster" and "ORM performance is good enough" are both incomplete claims.

An ORM can run the same SQL as handwritten code with little application overhead. It can also fetch unused data, run one extra query for every result row, delay many writes until one large flush, or miss a database bulk feature.

Handwritten SQL can also be slow. It can use a poor execution plan, miss an index that would speed lookup, return unused columns, or hold locks too long. It can also make repeated database calls when one statement would work.

Measure the complete operation:

Typical and slowest response times.
Work completed under the expected number of concurrent users.
Application CPU and memory use.
Database CPU, storage work, temporary data, and lock waits.
Query count and rows transferred.
Retries, timeouts, and incorrect results.
Use of the pool of reusable database connections.
Time needed to diagnose a failure.

Set acceptable limits from the production goal before the test. Do not choose a limit after seeing which implementation won.

Monitor the application and database together

An application trace records the path of a request through the service. Database statistics show what the database did. You need both views.

The OpenTelemetry database conventions define common fields for database work in traces. They also warn against recording sensitive or highly variable values.

PostgreSQL provides pg_stat_activity to show current activity. It provides pg_stat_statements to group similar statements and report planning and execution statistics.

For ORM and raw SQL paths:

Connect each application operation to its group of similar queries.
Record query counts, duration, errors, and lock waits.
Identify the owning service and code path.
Alert on the user-facing goal, not only database averages.
Keep parameter values and sensitive data out of logs and traces.

If the ORM hides database work from the trace, monitoring is incomplete.

Apply the same security rules

OWASP recommends prepared statements with parameter binding as the primary defense against SQL injection. The same guidance applies to raw SQL and ORM query languages. Parameter binding sends values separately from the SQL command instead of joining untrusted text into it.

Use the OWASP SQL Injection Prevention Cheat Sheet as a minimum standard:

Bind values instead of joining them into query text.
Allow only approved table names, column names, and sort directions.
Test native SQL, query builders, and ORM query languages.
Give the application only the permissions it needs.
Use separate credentials for runtime, migrations, and reporting.

An ORM can make the safe path easier. It cannot fix excessive database permissions or unsafe queries built from text.

Name an owner for every path

The final question is simple: who owns the consequences?

A workable model assigns clear responsibility:

The service team owns transaction correctness and application behavior.
The query author owns the SQL, query count, and performance evidence.
The schema owner reviews compatibility and migration order.
The platform or database team provides standards, telemetry, and guardrails.
The security owner defines credential boundaries and review requirements.
The on-call team can connect an alert to a query and deployment.

Shared standards should make safe delivery easier. They should not require a specialist team to approve every query change.

If nobody can explain a generated query, change its migration, or diagnose it during an incident, nobody truly owns that access path.

Record the decision

For each important database access path, record:

Workload: transactional CRUD, bulk processing, reporting, or a mix.
Correctness rule: what must remain true during concurrent work and failure.
Chosen path: tracked ORM objects, ORM batches, prepared SQL, or a database operation.
Query evidence: example SQL, expected query count, and execution plan.
Transaction policy: start and end, isolation, locks, timeout, and retries.
Migration plan: compatibility period, data fill, validation, and recovery.
Security: parameter binding, permissions, and sensitive data handling.
Monitoring: trace, metrics, logs, and alert.
Ownership: code, schema, migration, and incident owners.
Change trigger: evidence that would justify another access method.

Use a synthetic lab for a fair test

This section describes a test protocol, not benchmark results. Every record, value, workload, and data size is synthetic.

Use fixed versions of Java, PostgreSQL, Hibernate, the JDBC driver, and the connection pool. Publish the software and hardware settings, migrations, data generator, implementations, test scripts, checks, raw results, execution plans, and report code.

Use a fictional order system at three data sizes. Generate all values with a fixed random seed. Do not use names, identifiers, distributions, or values from a real organization.

Compare equivalent versions of each workload:

Normal stateful Hibernate.
Tuned Hibernate with explicit data loading and batching.
Prepared JDBC SQL.
PostgreSQL-specific SQL where it fits, such as COPY.

Do not compare careless ORM code with heavily tuned SQL. Keep the schema, indexes, validation rules, connection pool, transaction, result, and failure behavior the same.

Test transactions, bulk loads, reports, and a compatible schema migration. For each implementation and data size:

Start from the same database snapshot.
Test low, medium, and high concurrency.
Repeat each case and randomize the implementation order.
Capture application, connection pool, database, and host measurements.
Run correctness assertions after every case.
Publish every run, including failures and outliers.

Report the range and variation, not only averages. Separate database time from ORM mapping time and total response time. Set acceptance limits before running the lab.

What I would do

Use an ORM by default where it makes transactional CRUD clearer.
Use explicit SQL when it makes bulk processing or reporting clearer.
Allow both inside one service when workloads differ.
Apply the same transaction, migration, security, monitoring, and ownership standards to both.
Inspect generated SQL before production.
Change a path when evidence crosses a limit set in advance.

Choose the method your team can explain, test, secure, and operate.