Databases lessons

Files vs databases

Flat files like CSV or JSON work for small datasets. But they have no built-in way to search, filter, or enforce rules on the data.

Databases add indexing for fast lookups, constraints to prevent bad data, and transactions to group changes safely. These features are why almost every real application uses one.

Comparison table: flat files versus databases.

Practice: Pick the right tool

Pick the best storage method for each scenario.

Tables, rows and columns

A relational database stores data in tables. Each table represents one type of thing, like users or orders.

Each row is a single record. Each column is a field with a name and a data type, such as TEXT, INTEGER, or BOOLEAN.

Styled users table with typed columns and rows.

Primary keys

Every table needs a way to uniquely identify each row. This is the primary key. It is usually a column called id that auto-increments with each new row.

No two rows can share the same primary key value. This guarantees you can always find exactly one record when you query by its key.

Products schema with the id column marked as primary key.

Practice: Identify the primary key

Choose the primary key column for each table.

SQL basics: SELECT

SQL (Structured Query Language) is how you talk to a relational database. The most common command is SELECT, which reads data from a table.

SELECT name, email FROM users;

Use SELECT * to get all columns, or list specific column names separated by commas.

Filtering with WHERE

The WHERE clause filters rows based on a condition. Only rows that match the condition are returned.

SELECT name, age FROM users
WHERE age >= 18;

Terminal WHERE query with matched and greyed-out rows.

Practice: Write a SELECT query

Write a SELECT query, validated by keyword.

Sorting and limiting

ORDER BY sorts results by a column. Add ASC for ascending or DESC for descending.

SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

LIMIT restricts how many rows come back. This is useful for pagination or finding the top N results.

Practice: Complete the query

Place the SQL clauses in the correct order.

INSERT, UPDATE, DELETE

INSERT adds a new row. UPDATE changes existing rows. DELETE removes rows.

INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25);

UPDATE users SET age = 26 WHERE name = 'Alice';

DELETE FROM users WHERE name = 'Alice';

CRUD cycle diagram around a central table.

Practice: Match the operation

Match each SQL command to what it does.

Relationships and foreign keys

Tables often relate to each other. A foreign key is a column in one table that references the primary key of another table.

For example, an orders table might have a user_id column that points to the id column in the users table.

ER diagram showing a one-to-many users-to-orders link.

Types of relationships

One-to-one: each row in table A links to exactly one row in table B. Example: a user has one profile.

One-to-many: one row in table A links to many rows in table B. Example: one user places many orders.

Many-to-many: rows in both tables link to multiple rows in the other. This requires a join table. Example: students enrol in many courses, and each course has many students.

Practice: Name the relationship

Name the relationship type for each scenario.

INNER JOIN

A JOIN combines rows from two tables based on a related column. An INNER JOIN returns only the rows where a match exists in both tables.

SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

If a user has no orders, they will not appear in the results. If an order has no matching user, it is also excluded.

LEFT and RIGHT JOIN

A LEFT JOIN returns all rows from the left table, even if there is no match in the right table. Unmatched columns are filled with NULL.

A RIGHT JOIN does the opposite: it keeps all rows from the right table.

Venn diagrams comparing INNER, LEFT and RIGHT joins.

Practice: Pick the right JOIN

Pick the JOIN that returns the rows described.

Practice: Predict the result

Fill in the LEFT JOIN result table, including NULLs.

SQL vs NoSQL

SQL databases (MySQL, PostgreSQL) store data in structured tables with a fixed schema. NoSQL databases (MongoDB, Redis) store data in flexible formats like JSON documents or key-value pairs.

SQL is best when your data has clear relationships and you need strong consistency. NoSQL is useful when your data shape varies or you need to scale horizontally across many servers.

Comparison table: SQL versus NoSQL.

Document databases

In a document database like MongoDB, data is stored as JSON-like documents. Instead of rows in a table, you have documents in a collection.

{
  "_id": "abc123",
  "name": "Alice",
  "email": "alice@example.com",
  "orders": [
    { "product": "Laptop", "total": 999 },
    { "product": "Mouse", "total": 25 }
  ]
}

Notice how orders are nested inside the user document. This avoids JOINs but can lead to duplicated data if the same information appears in multiple places.

Practice: SQL or NoSQL?

Decide whether each scenario suits SQL or NoSQL.

Schemas and data types

A schema defines the structure of your database: which tables exist, what columns they have, and what types those columns use. Common types include INTEGER, VARCHAR, BOOLEAN, and TIMESTAMP.

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2),
  in_stock BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Constraints like NOT NULL and DEFAULT enforce rules at the database level, preventing bad data before it ever reaches your application.

Migrations

A migration is a versioned change to your database schema. Instead of manually running SQL to add a column, you write a migration file that can be applied or rolled back.

This keeps your database changes tracked in version control, just like code. Teams can apply migrations in order so everyone has the same schema.

-- Migration: 001_add_email_verified
ALTER TABLE users
ADD COLUMN email_verified BOOLEAN DEFAULT false;

ORMs and Prisma

An ORM (Object-Relational Mapper) lets you interact with the database using your programming language instead of raw SQL. Prisma is a popular ORM for JavaScript and TypeScript.

// Prisma schema (schema.prisma)
model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String  @unique
  orders Order[]
}

// Query in TypeScript
const users = await prisma.user.findMany({
  where: { age: { gte: 18 } },
  include: { orders: true },
});

Prisma generates type-safe queries from your schema. This means your editor can autocomplete column names and catch mistakes before you run the code.

Practice: Match the layer

Match each layer term to its description.

Putting it all together

A real application typically has a schema that defines tables, migrations to evolve that schema over time, and an ORM or query builder to interact with the data from application code.

Layered flow: application code, ORM, and database.

Understanding these layers helps you choose the right tool for each job. You now have the foundations to build data-driven applications.