Databases and SQL, the basics

Why is a text file not enough?

You can keep user records in a text file, and for a hundred rows it works fine. Three things break it, and all three show up in the first month.

First, concurrency: when two people sign up in the same instant, two programs try to write to one file and one of the two writes is lost. Second, search: finding the user with a given email in a file means reading the whole file, and that gets slower as the file grows. Third, correctness: nothing in a text file stops two users being recorded with the same email.

A database is a program that solves those three: locking for simultaneous writes, indexes for fast lookup, and constraints so invalid data never gets in. You do not talk to it in order to have a file saved; you talk to it to get those three guarantees.

And a point that gets said less often: a database does not replace files. Images, video and downloads usually stay on disk and only their address is recorded in a table. WordPress works that way too, which is why a database backup on its own is not a backup of the site.

Tables, rows, columns and that id column

A table looks like a spreadsheet sheet: the columns say what is stored and each row is one record. A users table has id, name and email columns and each row is one user. Nothing complicated so far.

The id column has a special role, though, and its name is the primary key: a value unique across the whole table that never changes. It exists because nothing else can be relied on; people share names, change their email and lose their phone number. That is why orders keep the customer's id rather than their name, and that reference is called a foreign key.

Now a real example that breaks a common assumption. The main table of this very site, which WordPress calls posts, had 784 rows on the day this lesson was checked, but those 784 rows were 24 different kinds of thing: 263 blog posts, 240 media files, 37 pages, and the rest projects, messages, listings and a few others.

The lesson in that number is useful to anyone: a table is a shape, not a subject. Anything that needs the same columns can sit in the same table and be separated from the rest by one more column. The practical consequence is that a SELECT on such a table with no condition hands you a mixture of four unrelated things.

The three commands that do most of the work

SQL is a language for requesting, not for giving step by step instructions. You do not say how to search; you say what you want, and the database chooses the route itself.

SELECT name, email FROM users WHERE city = 'Tehran';

INSERT INTO users (name, email, city) VALUES ('Maryam', 'm@example.com', 'Shiraz');

UPDATE users SET city = 'Isfahan' WHERE id = 42;

Three things in those three lines are worth seeing. SELECT says which columns, FROM says from which table and WHERE says which rows. If you do not know the columns, SELECT * brings all of them, which is fine for looking around and a bad habit inside program code, because the day a column is added your program receives something it did not expect.

The most dangerous word on this page is that WHERE, by being absent. UPDATE users SET city = 'Isfahan'; is not a syntax error; it is perfectly valid and changes the city of every user. The documentation of both MySQL and MariaDB says so plainly, and both even have a mode that refuses such a statement.

The habit that solves this is typing one more line: write every UPDATE or DELETE first as a SELECT with the same condition and see how many rows come back. If that number is not what you expected, your condition is wrong and you have just found out, rather than finding out after the data is gone.

Do not confuse the database with the cache

Two things run side by side on this very server and their jobs are entirely different. One is MariaDB, from the MySQL family, which holds all of the site's durable data. The other is Redis, an object cache, which keeps the results of questions just asked of the database in memory so the next request does not have to ask again.

The difference fits in one sentence: if we wipe the cache completely, nothing is lost and only the next few requests are slower, because everything is rebuilt from the database. If we lose the database, everything is gone.

From that one sentence comes a practical rule that serves you everywhere: never keep something only in the cache. A cache is entitled to be empty at any moment, whether from a restart or from memory filling up, and a program that relies on a value being in the cache fails on the day it is not. A cache is for speed, not for keeping.

And that same separation clarifies hosting decisions: a database needs fast disk and regular backups, a cache needs memory. On the WordPress hosting we set up ourselves both sit side by side, but only one of the two is backed up, and that is exactly how it should be.

What makes a query safe?

The most dangerous mistake in working with a database is building a query by gluing in text the user typed. When an SQL statement is made of two pieces, the user's text and your command, the database has no way to tell which part was data and which was instruction. That class of vulnerability is called SQL injection.

The fix is old and simple and the same everywhere: put a placeholder where the value goes in the query text and hand the value over separately. Then whatever the user wrote, even if it looks like an SQL statement itself, is read as nothing but a string. In WordPress the prepare method on the wpdb object does exactly this, and WordPress's own documentation treats it as required before any query carrying user input.

One practical note that buys you time right here: in most projects you do not write raw SQL at all. WordPress and frameworks have a layer that builds the query for you, and builds it safely. Knowing SQL is so that you can understand what that layer produced and why it is slow, not so that you write every query by hand.