Glossary

SQL

SQL is a language for working with relational data, not a database. PostgreSQL is a specific system; Doctrine DBAL and ORM are PHP layers that use or generate SQL.

Short definition

You describe the data you need; the database chooses how to retrieve it.

SQL is used to create tables, read, insert, update, and delete data, perform aggregations, and manage transactions. Declarative means that a query describes the desired result, such as a customer’s orders with their item totals. The database planner then determines whether to use an index or another plan.

There is an SQL standard, but PostgreSQL, MySQL, and SQLite have their own dialects and functions. Portable design therefore distinguishes the general principle from specific syntax. Parameterised values separate data from query structure; manually inserting untrusted input into SQL leads to SQL injection.

The problem it solves

Reading, modifying, and defining relational data

SQL makes it possible to work with data directly and precisely, including where a general ORM abstraction is not suitable.

SELECT for lists, details, filters, and aggregations
INSERT, UPDATE, and DELETE for modifying data
CREATE TABLE and ALTER TABLE for database structure
JOIN for combining related tables
GROUP BY for totals, counts, and reports
COMMIT and ROLLBACK for committing or reverting changes

SQL example

PostgreSQL query with JOIN and aggregation

This example is for PostgreSQL; $1 is not an interpolated string but a parameter passed by the driver.

SELECT
    o.id,
    o.order_number,
    COUNT(oi.id) AS item_count,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_amount
FROM orders AS o
LEFT JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.customer_id = $1
GROUP BY o.id, o.order_number, o.created_at
ORDER BY o.created_at DESC;

How it works

From data model to query result

The exact execution plan depends on the specific database and data, not just the length of the SQL statement.

Model Tables, types, keys, and constraints define which data can be stored safely.
Query and parameters The application prepares the SQL structure and passes values separately to the database driver as parameters.
Validation The database checks syntax, permissions, data types, and integrity rules.
Plan The planner chooses an execution method based on statistics, conditions, joins, and available indexes.
Result or change The application reads the rows or commits a local change in a transaction.

Important concepts

Statements, conditions, and dialects.

SQL combines operations on data with structure, rules, and transactions.

SELECT and WHERE

SELECT chooses columns or expressions. WHERE filters rows by conditions; values should be passed safely as parameters.

INSERT, UPDATE, and DELETE

These statements modify data. In a critical operation, they usually belong in a short database transaction together with related writes.

JOIN, GROUP BY, and aggregations

JOIN connects tables through relationships; GROUP BY and functions such as COUNT or SUM produce summaries.

DDL and DML

CREATE TABLE and ALTER TABLE define the schema. SELECT, INSERT, UPDATE, and DELETE work with specific data.

Dialect and parameter

Functions, types, and placeholders can differ. A parameter protects a value, but column names and sort directions require an allowlist.

Benefits and limitations

Direct access requires an understanding of the model.

Benefits

precise queries, aggregations, and joins over relational data
use of database types, constraints, and capabilities
a suitable tool for reporting and bulk changes
the ability to evaluate the actual query plan and performance

Common mistakes

confusing SQL with a specific database or ORM
manually inserting untrusted input into a string
assuming a more complex query is automatically worse than many small ones
ignoring the resulting plan, indexes, and data volume
relying on unverified vendor-specific syntax outside the target dialect

Practical example

A customer’s orders with item counts

The following example uses the PostgreSQL placeholder $1; the database driver passes the actual value as a parameter. The query reads one customer’s orders, joins their items, and counts them. Its performance depends on the data model, volume, and suitable indexes.

Complexity should not be judged solely by the number of lines of SQL. One readable aggregate query may be more appropriate than repeatedly loading related data in a loop.

What to keep in mind

Security and performance are part of the same design.

SQL should be readable, parameterised, and tested against real data.

pass values as parameters instead of concatenating them into an SQL string
use a specific allowlist for dynamic identifiers
inspect SQL generated by the ORM in performance-sensitive areas
check EXPLAIN and real data volumes before adding indexes
distinguish a local transaction from an external API or queue

Common questions

SQL in an application

Is SQL the same as PostgreSQL?

No. SQL is a language and a standardised approach; PostgreSQL is a specific database management system with its own dialect and functions.

Do I need to know SQL if I use an ORM?

An ORM simplifies many tasks, but it still works with relational data and generates SQL. Understanding queries helps with performance, transactions, and troubleshooting.

Does a parameterised query protect against SQL injection?

It protects against inserting a value into the query structure when used correctly. Dynamic table names, column names, or SQL keywords usually cannot be parameterised and require an allowlist.

Is one complex query always worse than several simple ones?

No. Suitability depends on the data model, volume, data transfers, and resulting plan. The decision should be based on measurement, not the number of lines of SQL.

How I apply this principle in practice

I use SQL where a direct query provides clear value.

Alongside an ORM, I use explicit SQL for reports, imports, and performance-sensitive views; I always validate the design against the model and resulting plan.

How I work with databases Discuss your project without obligation