What is a database?

A database is a program, or a file managed by one, that stores data so that many people and programs can find it, change it and trust it. By the end of this page you can read a table, say what its primary key is, and tell a relational database from a key-value or document one.

Why not a spreadsheet?

A spreadsheet is a fine place for a list you look after yourself. It struggles as soon as a program, or several people, depend on the data:

You need	Spreadsheet	Database
Many people or programs writing at the same time	Last save wins, or the file is locked	Built for it: each change is applied safely
Rules such as "every customer has an email, and no two share one"	Anyone can type anything in any cell	The database refuses data that breaks the rules
Several changes that must all happen or none	Not possible	Transactions (see lesson 4)
Linking an order to a customer without copying the customer's details	Copy and paste, then keep the copies in step	Store the customer once and point to them
Millions of rows, found in milliseconds	Slow, or over the size limit	Indexes make lookups fast (see lesson 3)
A program asking questions	Awkward	A query language, usually SQL

Behind almost every app you use, from a shop's website to the contacts on your phone, there is a database.

Tables, rows and columns

The most common kind of database keeps data in tables. Here are the first rows of the customers table from the practice shop used in this track:

id	name	email	city	joined
1	Asha Raman	asha.raman@example.com	Chennai	2025-02-04
2	Ben Carter	ben.carter@example.com	Leeds	2025-03-07
3	Chloe Martin	chloe.martin@example.com	Chennai	2025-04-10

A table holds one kind of thing: customers, products, orders.
A row (also called a record) is one of those things: one customer.
A column (also called a field) is one fact about each thing: the city. Every row has the same columns.
The schema is the description of the tables and their columns. It is decided before the data goes in.

Types: what a column may hold

Each column has a type. The database uses it to store, compare and sort values correctly.

Type (SQLite name)	Holds	Example
`INTEGER`	Whole numbers	`2499`
`REAL`	Numbers with a fraction	`4.5`
`TEXT`	Text	`'Chennai'`
`BLOB`	Raw bytes, such as a small image	`x'89504E47'`

A missing value is NULL. It means "unknown" or "nothing here", which is not the same as 0 or empty text.

Other databases have more types with their own names, such as VARCHAR(100), BOOLEAN, DATE and TIMESTAMP in MySQL and PostgreSQL. The idea is the same.

Good to know

SQLite is relaxed about types: by default it lets a column declared INTEGER hold text. MySQL and PostgreSQL refuse a value of the wrong type. Do not rely on SQLite's tolerance; store each column's values in one type.

Prices in the practice shop are stored as whole cents in an INTEGER column (price_cents, 2499 for 24.99). Fractional numbers in a computer can pick up tiny rounding errors, and money must add up exactly, so whole cents are a common choice.

Primary keys: one row, one name

A primary key is a column, or a set of columns, whose value is different in every row and never empty. It is how you, and other tables, point at exactly one row.

In customers, the primary key is id. There may be two customers called Ben Carter one day, but there is only ever one customer with id 2.

text

"Change Ben's city"          → which Ben?
"Change customer 2's city"   → exactly one row

Good primary keys never change. A number the database hands out (1, 2, 3…) is the usual choice. Email addresses make poor keys, because people change them.

Three kinds of database, one example

Not every database uses tables. Here is the same small piece of data, customer 2 and two of their orders, stored three ways.

Relational: tables that point at each other

Relational databases (SQLite, MySQL, MariaDB, PostgreSQL) keep data in tables and link them through keys. You ask questions in SQL (Structured Query Language).

text

customers                          orders
┌────┬────────────┬───────┐        ┌──────┬─────────────┬───────────┐
│ id │ name       │ city  │        │ id   │ customer_id │ status    │
├────┼────────────┼───────┤        ├──────┼─────────────┼───────────┤
│  2 │ Ben Carter │ Leeds │◄───────│ 1087 │      2      │ delivered │
└────┴────────────┴───────┘   └────│ 1093 │      2      │ shipped   │
                                   └──────┴─────────────┴───────────┘

Each order stores only the customer's id. Ben's name lives in one place, so changing it changes it everywhere.

sql

SELECT * FROM orders WHERE customer_id = 2;

Key-value: a giant dictionary

A key-value store (LMDB is one) keeps pairs: a key you choose, and a value that the database treats as plain bytes. You can fetch a value by its key, or walk through the keys in sorted order.

text

key            value
customer:2     {"name": "Ben Carter", "city": "Leeds"}
order:1087     {"customer": 2, "status": "delivered"}
order:1093     {"customer": 2, "status": "shipped"}

Lookups by key are very fast, and the database is small and simple. But it cannot answer "which orders are shipped?" by itself: it does not look inside values. The program must read every order, or keep extra keys of its own to act as an index. A common trick is a key prefix such as order:, so one scan lists all orders.

Document: one record with everything inside

A document database (MongoDB is one) stores JSON-like documents in collections. A document can hold lists and nested objects, and two documents in the same collection may have different fields.

json

{
  "_id": 2,
  "name": "Ben Carter",
  "city": "Leeds",
  "orders": [
    { "id": 1087, "status": "delivered" },
    { "id": 1093, "status": "shipped" }
  ]
}

Reading a customer with their orders is one fetch. The database can search inside documents:

json

{"find": "customers", "filter": {"city": "Leeds"}}

The cost: data that is shared, such as a product's name inside many orders, may be copied into many documents and must be kept in step.

Which to use

Kind	Good at	Examples
Relational	Data with clear relationships, rules and questions you cannot predict	SQLite, MySQL, MariaDB, PostgreSQL
Key-value	Very fast lookups by a known key, small embedded stores	LMDB
Document	Records that are read whole and vary in shape	MongoDB

Most of this track is about relational databases and SQL, because they are the most common and the ideas carry over.

Files and servers

Databases also differ in where they run:

text

Embedded (SQLite, LMDB)              Client-server (MySQL, PostgreSQL, MongoDB)

┌──────────────┐                     ┌────────┐   network   ┌───────────────┐
│   your app   │                     │ app 1  │────────────►│               │
│ ┌──────────┐ │                     └────────┘             │   database    │
│ │ database │ │──► shop.sqlite      ┌────────┐             │    server     │──► data files
│ │ library  │ │    (one file)       │ app 2  │────────────►│ (port 5432)   │
│ └──────────┘ │                     └────────┘             └───────────────┘
└──────────────┘

An embedded database is a library inside the app. The data is a file (SQLite) or a folder (LMDB) on the same computer. There is no server and no password unless you add encryption.
A client-server database runs as its own program, often on another machine. Apps connect over the network with a host, a port, a user name and a password.

Where Dhiva Labs apps fit

Database Manager opens all five kinds named on this page: SQLite files, MySQL / MariaDB and PostgreSQL servers, MongoDB servers and LMDB folders. Its Query tab takes SQL, a MongoDB JSON command or an LMDB key prefix, matching each kind. See Connect to a database.
lmdb-lws-backup takes encrypted backups of an LMDB database while the program using it keeps running.
ApiWorkbench tests APIs. Most APIs store what you send in a database, so a tester often checks the database afterwards. See lesson 4.

Stay safe

A database file is the data. Anyone who gets a copy of shop.sqlite, or of an LMDB folder, can read everything in it. Treat database files and their backups like you treat passwords.
Practise on made-up data, like the shop in this track, not on a copy of real customers.