What a database is

Everyday storyThe kirana store's notebook

In a real productSwaad, a food delivery app

The ideaHow it works

SQL (say "S-Q-L" or "sequel") is the language you use to talk to a database. Before you write any, it helps to know what you are talking to.

Tables, rows and columns

A database keeps its data in tables. A table looks like one sheet of a spreadsheet: a grid with a name.

• Each row is one thing: one city, one customer, one order.
• Each column is one fact that every row has: a name, a price, a time.

Swaad has a table called cities. It is the smallest one, with 8 rows. Press Run to see all of it:

SELECT *
FROM cities;

You get 8 rows and 3 columns: id, name and state. Mumbai is city 1, Pune is city 6, Kolkata is city 8. The id column gives every row a number that is never repeated. That number is the row's primary key: the one sure way to point at exactly one row.

Two things make a table stricter than a spreadsheet sheet:

• Every column has a type. id holds whole numbers, name holds text. You can't type "hello" into a number column. This kind of value is the column's data type, and you'll learn the common ones later in this module.
• There is no "row 7". A spreadsheet has numbered lines and a fixed order. A table is more like a bag of rows. Rows have no position; you find them by what's in them ("the city whose id is 6"), not by where they sit.

The second point surprises people, and it matters later: when you ask for rows without saying how to sort them, the database may hand them back in any order.

Why not a spreadsheet or a file?

Plenty of small businesses run on spreadsheets, and for a list of 200 customers that's fine. Here is what goes wrong as an app grows.

Many people at once. A spreadsheet file is opened, edited and saved as a whole. When two people change it at the same moment, the last one to save usually wins, and the other person's change vanishes without a warning.

A database is built for concurrency, many users working at the same time. Thousands of people can place orders together and every single one is saved, in full, or not at all.

Rules. A spreadsheet lets you type anything anywhere: "two hundred" in a price cell, a delivery date before the order date, an order for a customer who doesn't exist. A database lets you write rules, called constraints, and checks every change against them. Break a rule and the change is refused.

Size. Spreadsheet programs slow down badly with a few lakh rows, and they load the whole file into memory to work on it. A database keeps data on disk in a form it can search without reading everything. Tables of crores of rows are routine.

Asking questions. In a spreadsheet, "how much did Pune customers spend on biryani in October?" means filters, copy-paste and a pivot table, by hand, every time. In a database you write the question once in SQL, and you can run it again tomorrow, or have the app run it a thousand times a second.

Safety. A database keeps a record of every change before it makes it, so a power cut in the middle of saving doesn't leave half an order behind. It can be backed up while it is being used. (The write-ahead log lesson shows how.)

What about plain files, like one text file per order? You would have to write all of the above yourself: locking, rules, searching, crash safety. Databases are decades of that work, already done and tested.

The database server and the app

When Swaad runs a database, it isn't a file on someone's laptop. It is a program called a database server, running on a machine in a data centre. The server owns the data files. Nobody touches them directly; everyone asks the server.

Your phone never talks to the database. It talks to Swaad's app server, which checks who you are and what you're allowed to do. The app server then sends SQL to the database server over a network connection, gets back the rows, and turns them into the screen you see. The reports tool used by the support team does the same: it is one more app sending SQL to the same server.

Each message the app sends is a query: a question ("show me order 2") or an instruction ("save this new order"). Here is what happens when you place an order:

The database server does real work for each query. It finds rows without reading every table from start to end, keeps different people's changes from clashing, checks the rules, and sends back only the rows that were asked for. The grid it sends back is called a result set.

Relational: tables that point at each other

Swaad's orders don't contain the customer's name, phone or city. They contain customer_id, a number. To know who placed an order, you look up that number in the customers table.

This is the core idea of a relational database: each fact is stored once, in the table it belongs to, and other tables point at it by its id. A column that points at another table's id is a foreign key.

Why go to this trouble? Imagine Ritu Shah has placed 14 orders and changes her phone number. If her phone were written on every order, you would have to change 14 rows and hope you found them all. With one row in customers, you change one value, and every order that points at her sees the new number at once. The relational idea keeps facts from drifting out of step.

The database can also enforce the pointers. Swaad's orders.customer_id is a foreign key to customers.id, so an order for a customer who doesn't exist is refused. You'll see that happen in a minute.

Postgres, and other databases

There are many relational databases, and all of them speak SQL. In these lessons you use Postgres (full name PostgreSQL), a free and open-source one that many companies run. The database on this page is real Postgres, compiled to run inside your browser.

SQL is a standard, so most of what you learn works in MySQL, SQL Server, Oracle and SQLite too. Each database also adds its own extras. When a lesson uses something Postgres-only, it says so.

You'll also hear about NoSQL databases, which store data as documents, key-value pairs or graphs rather than tables. They solve different problems, and the last module of this section covers them. Relational databases remain the default starting point for most apps, so that is where you start.

Meet Swaad's tables

Swaad has 8 tables. You can ask Postgres to list them:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

Here is what each one holds:

• cities: the 8 cities Swaad works in.
• customers: 2,000 people with a name, email, phone (some have none), city and the date they joined.
• restaurants: 120 restaurants, 15 per city, each with a cuisine and a star rating.
• menu_items: 10 dishes per restaurant, each with a price and whether it is vegetarian.
• delivery_partners: 300 riders, each with a city and a vehicle (bike, scooter or cycle).
• orders: 20,000 orders from 2025. Who ordered, from which restaurant, who delivered it, the status, the times, the total paid, the delivery fee and how they paid.
• order_items: what was in each order, about 50,000 lines. One row per dish per order, with the quantity and the price paid.
• reviews: about 5,400 star ratings, each tied to one order.

How many orders are there? Ask:

SELECT count(*)
FROM orders;

The answer is one row with one number: 20000. You didn't scroll through anything. The database counted for you.

Don't worry about every word of these queries yet; the next few lessons take them apart slowly. Reading with SELECT never changes data, so press Run as often as you like.

Step by stepFollow one order through the tables

SELECT *
FROM orders
WHERE id = 2;

 id | customer_id | restaurant_id | partner_id | status    | placed_at           | delivered_at        | total   | delivery_fee | payment_method | coupon_code
----+-------------+---------------+------------+-----------+---------------------+---------------------+---------+--------------+----------------+------------
  2 |        1289 |           116 |         40 | delivered | 2025-01-01 08:09:00 | 2025-01-01 08:48:00 | 1326.00 |         0.00 | upi            |

SELECT id, name, email, city_id
FROM customers
WHERE id = 1289;

SELECT id, name, cuisine, rating
FROM restaurants
WHERE id = 116;

SELECT *
FROM order_items
WHERE order_id = 2;

SELECT id, name, price
FROM menu_items
WHERE id IN (1152, 1156, 1159);

SELECT id, name, city_id, vehicle
FROM delivery_partners
WHERE id = 40;

-- Error: new row for relation "orders" violates check constraint "orders_status_check"
INSERT INTO orders (customer_id, restaurant_id, status, placed_at, total, delivery_fee, payment_method)
VALUES (1289, 116, 'lost', '2025-12-31 21:00', 250, 30, 'upi');

-- Error: insert or update on table "orders" violates foreign key constraint
INSERT INTO orders (customer_id, restaurant_id, status, placed_at, total, delivery_fee, payment_method)
VALUES (99999, 116, 'preparing', '2025-12-31 21:00', 250, 30, 'upi');

ScenariosYou're the engineer

Real situations you could face. Think about what you'd do before reading on.

What happens when…Situations

Real situations this idea runs into, and how well-built systems handle each one.

Watch outCommon mistakes

Trade-offsWhat it costs, and when not to use it

Interview angleIn an interview