Databases and SQL

A database stores information so it can be reliably retrieved, related, and updated. SQL is the language used to query it. These concepts are useful to every developer, even without being a specialist.

They complement Learning to code.

01 — Relational or not

Two families, two uses

02 — The building blocks

Tables, keys, relations

• A table = rows (records) and columns (fields).
• Primary key: uniquely identifies each row.
• Foreign key: links one table to another (an order belongs to a customer).

03 — Basic queries

Reading and combining

• SELECT … WHERE …: read rows matching a criterion.
• JOIN: combine related tables.
• GROUP BY … with aggregate functions: count, sum, average.
• ORDER BY, LIMIT: sort and paginate.

04 — Performance

Indexes, used wisely

An index speeds up reads on a frequently filtered column, at the cost of somewhat slower writes and disk space. You index the columns used in frequent JOINs and WHEREs, not everything.

05 — Security and backups

Two mistakes to never make

• Never build a query by pasting in user input: use parameterized queries (against SQL injection).
• Tested automatic backups: a database without a verified backup is a ticking time bomb.

06 — Frequently asked questions

Frequently Asked Questions

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

Yes. An ORM helps day to day, but understanding the SQL it generates is essential for debugging and optimizing.

Which database should you choose to start?

A proven relational database (PostgreSQL, MySQL/MariaDB, SQLite for learning). NoSQL comes in for specific needs.

How do you prevent SQL injection?

Systematic parameterized queries, input validation, and minimal privileges for the application account.

07 — Resources & links

Where to go, concretely