Databases

The lesson Where data lives showed the three places where a site keeps data: the company's database, the visitor's browser, and outside services. This lesson opens the first one.

Picture a music shop that sells online. Customers place orders on the site, staff change the stock in the shop, and the accountant reads the invoices, all at the same moment, on the same data. A database does four jobs for them.

It lets many people and programs write at the same moment without losing or doubling anything. It checks rules before saving: a price must be a number, every order needs a customer, two accounts can't share one email address. It finds one record among millions in a fraction of a second, thanks to indexes, which work like the index at the back of a book. And it decides who may read or change what. It also has to be backed up: the lesson No way back explains what makes a backup work.

A spreadsheet or a file can do some of this, for one person at a time.

Here is the same order at the music shop, kept in two ways.

A table database, such as PostgreSQL or MySQL, or Oracle and SQL Server in many large companies, keeps each kind of thing in its own table: one for customers, one for orders, one for the items in each order. The columns of each table are fixed in advance. Each fact is kept once: Léa's name lives in one row of the customers table, and order 1042 simply points to customer number 7. If she changes her name, it changes in one place and is right everywhere. The database also checks the links: it refuses an order for a customer who doesn't exist.

A document database, such as MongoDB or Firestore, keeps order 1042 as one document, with its customer and its items inside. It's read in one go, and two documents can have different fields. The price of that convenience: Léa's name is copied into each of her orders, so if it changes, every copy must change, and the code must remember to do it.

The example below shows a question in SQL on the left, and two documents on the right.

SQL has been the language of table databases since the 1970s, and it reads almost like English. SELECT name, price says which columns to return, FROM products which table, WHERE price < 20 which rows, and ORDER BY price puts the cheapest first. The answer is three rows: a small table again. It's the question that the four frameworks in Languages and frameworks sent to the database.

On the right, the same kind of products kept as documents. The guitar strings have a gauge, and the tuner has a battery and a clip: each product keeps only the details that make sense for it. In a table, the shop would need a column for every possible detail, empty for most products.

The line between the two kinds is blurring: PostgreSQL can also store documents next to its tables. And nearly every reporting and analysis tool speaks SQL.

Tables suit linked records that must add up and follow strict rules: invoices, payments and stock, where the totals must be right to the cent. Table databases also offer transactions: several changes saved together, or none of them. A bank transfer is the classic case. The money must leave one account and arrive in the other, or do neither, even if the server stops halfway.

Documents suit records whose shape varies from one to the next and that are read as a whole: a catalogue where each instrument has its own details, a user's settings, a record of events.

Table databases are by far the most common. In Stack Overflow's 2025 survey, about 56% of the developers who answered had used PostgreSQL in the past year, about 41% MySQL, about 24% MongoDB and about 6% Firestore. Among the respondents in France, by our count from the survey's public answers, about two thirds had used PostgreSQL.

Both kinds are mature, so don't choose on fashion. Choose on the data, and early: changing database later means moving all the data and rewriting the code that reads it.

A database usually outlives most of the code around it, so ask these questions early.

Ask what kind of database will hold your data, and why. A good answer talks about your data: whether it must add up, whether each record has its own shape. Ask which rules the database checks by itself, and which ones live only in the code, where they're easier to miss.

Ask whether you can export all your data in a standard format, such as SQL, CSV or JSON, so that you can move to another tool or provider one day. Ask where the database is hosted, and in which country, since personal data comes with GDPR duties. And ask how it's backed up, and when someone last restored a backup.