The Data Stack, Demystified

SQL

SQL is a language, not a program. It's the way you talk to a relational database — a database that stores data in tables of rows and columns, like a set of linked spreadsheets.

Almost every relational database understands SQL. You use it to ask four basic kinds of questions, often called CRUD: Create, Read, Update, Delete.

-- Read: get all customers in Michigan
SELECT name, city FROM customers
WHERE state = 'MI';

-- Create: add a new row
INSERT INTO customers (name, state) VALUES ('Ada', 'MI');

SQLite

SQLite is a real SQL database that lives entirely in one file on your disk. There's no separate server to start or manage — your program opens the file and reads/writes it directly.

It is the most widely deployed database in the world, precisely because it's invisible: it's inside your phone, your browser, countless apps, and embedded devices.

PostgreSQL

PostgreSQL is a full database server — a program that runs continuously in the background and that many clients can connect to over the network at the same time.

It's known for being robust, standards-compliant, and remarkably capable. Beyond ordinary tables it handles JSON documents, full-text search, geographic data, custom data types, and serious concurrency — which is why it's a default choice for production web applications.

Docker

Docker packages software together with everything it needs to run into a self-contained unit called a container. That container then runs identically on any machine that has Docker.

It exists to kill the phrase "but it works on my machine." Instead of installing PostgreSQL directly onto your laptop — fiddling with versions and settings — you run a Postgres container that comes pre-packaged and isolated. Start it, stop it, throw it away, all in seconds.

# Spin up a throwaway Postgres in one command
docker run --name pg -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 -d postgres:16

SQLAlchemy

SQLAlchemy lets you work with a SQL database using Python objects instead of hand-written SQL strings. It's the bridge between your application code and whatever database sits underneath.

It comes in two layers. Core is a SQL toolkit — you still think in tables, but build queries in Python. The ORM (Object-Relational Mapper) is the popular higher layer: it maps a table to a Python class, so each row becomes an object you can read and modify naturally.

# Define a table as a Python class
class User(Base):
    __tablename__ = "users"
    id   = Column(Integer, primary_key=True)
    name = Column(String)

# Query it without writing SQL by hand
user = session.query(User).filter_by(name="Ada").first()
print(user.id)

Your first 10 minutes

Don't install everything at once. Add each tool only when you actually need it. Here's the order that wastes the least time.

1. Python (you likely have it) Verify with python3 --version. SQLite comes bundled — no separate install. That alone is enough to start practicing SQL.
2. A way to view databases Install the free DB Browser for SQLite or the SQLite VS Code extension so you can see your tables, not just imagine them.
3. SQLAlchemy When you want Python objects instead of raw SQL: pip install sqlalchemy. Add psycopg2-binary later if you connect to Postgres.
4. Docker Desktop Only when you're ready for Postgres. Install from docker.com, then verify with docker --version and docker run hello-world.
5. PostgreSQL (via Docker) You don't install Postgres directly — you run the official image. Verify the container is alive with docker ps.

SQLite or PostgreSQL?

When you're unsure, start with SQLite. You can almost always migrate to Postgres later, and with SQLAlchemy that move is mostly a one-line change.

Connection strings, decoded

A connection string (or "database URL") packs every detail needed to reach a database into one line. Once you can read its parts, half the mystery disappears.

SQLite's version is much simpler because there's no server, user, or port — just a file path:

# Relative file in the current folder
sqlite:///app.db

# Absolute path (note the FOUR slashes)
sqlite:////home/majid/data/app.db

# Pure in-memory, vanishes when the program exits — great for tests
sqlite:///:memory:

End-to-end walkthrough

This is the whole stack in one go: run Postgres in Docker, connect with SQLAlchemy, create a table, insert and read rows. Then we flip a single line to use SQLite instead.

Step 1 — Start PostgreSQL in Docker. One command gives you a real database server running in the background.

docker run --name learn-pg \
  -e POSTGRES_USER=majid \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=vhm_db \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

The -v pgdata:... part is a volume — it keeps your data even if the container is deleted. Skipping it is gotcha #6.

Step 2 — Install the Python pieces.

pip install sqlalchemy psycopg2-binary

Step 3 — Write one script that does everything.

# app.py
from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

# 1. The connection string decides WHERE data goes.
#    Swap this one line for SQLite and nothing else changes.
DB_URL = "postgresql+psycopg2://majid:secret@localhost:5432/vhm_db"
engine = create_engine(DB_URL, echo=True)  # echo prints the SQL it runs

# 2. Describe a table as a Python class.
class Base(DeclarativeBase): pass

class Sensor(Base):
    __tablename__ = "sensors"
    id:   Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    unit: Mapped[str] = mapped_column(String(10))

# 3. Create the table in the database (CREATE TABLE for you).
Base.metadata.create_all(engine)

# 4. Insert rows.
with Session(engine) as s:
    s.add_all([
        Sensor(name="battery_temp", unit="C"),
        Sensor(name="motor_rpm",   unit="rpm"),
    ])
    s.commit()  # <-- without this, nothing is saved (gotcha #1)

# 5. Read them back.
with Session(engine) as s:
    for row in s.scalars(select(Sensor)):
        print(row.id, row.name, row.unit)

Step 4 — Run it.

python app.py

DB_URL = "sqlite:///vhm_local.db"

Two tables & a JOIN

Real data is connected: a sensor produces many readings; a reading belongs to one sensor. You express that link with a foreign key, and you combine the tables with a JOIN.

In raw SQL, a foreign key and a join look like this:

-- readings.sensor_id points back to sensors.id
CREATE TABLE readings (
  id        SERIAL PRIMARY KEY,
  sensor_id INTEGER REFERENCES sensors(id),
  value     REAL
);

-- JOIN: stitch each reading to the sensor it belongs to
SELECT s.name, r.value
FROM readings r
JOIN sensors s ON s.id = r.sensor_id
WHERE s.unit = 'C';

SQLAlchemy lets you declare the relationship once, then walk it like normal Python attributes — no JOIN by hand:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Reading(Base):
    __tablename__ = "readings"
    id:        Mapped[int]   = mapped_column(primary_key=True)
    sensor_id: Mapped[int]   = mapped_column(ForeignKey("sensors.id"))
    value:     Mapped[float]
    sensor:    Mapped["Sensor"] = relationship(back_populates="readings")

# On the Sensor class, add the other side:
#   readings: Mapped[list["Reading"]] = relationship(back_populates="sensor")

# Now traversal is just dot-access:
reading.sensor.name        # the parent sensor
sensor.readings            # all child readings

Migrations

create_all() is fine for a fresh database, but it won't alter a table that already exists. Once real data lives in your tables, you change their structure with migrations — versioned, repeatable scripts.

The standard tool with SQLAlchemy is Alembic. The idea: each change (add a column, rename a table) becomes a small file checked into git, so every teammate and every server applies the exact same steps in the exact same order.

pip install alembic
alembic init migrations          # one-time setup

# after you edit your models:
alembic revision --autogenerate -m "add status column"
alembic upgrade head             # apply pending migrations

Cheat sheets

Common mistakes & gotchas

BAD  f"SELECT * FROM users WHERE name = '{name}'"
GOOD use parameters — SQLAlchemy does this for you, or:
     cursor.execute("... WHERE name = ?", (name,))

Troubleshooting

Glossary

Primary key

A column whose value uniquely identifies each row (usually id).

Foreign key

A column that points to another table's primary key, creating a link between them.

Index

A lookup structure that makes searches on a column fast — like a book's index. Costs a little write speed for a lot of read speed.

Schema

The shape of your database: which tables exist and what columns they have.

Migration

A versioned script that changes the schema in a safe, repeatable way.

Transaction

A group of changes that either all succeed or all roll back — never half-done.

ACID

The four guarantees of a reliable transaction: Atomicity, Consistency, Isolation, Durability.

ORM

Object-Relational Mapper — maps database rows to objects in your programming language (the role SQLAlchemy plays).

Connection pool

A set of reusable open connections, so your app doesn't pay to open a new one for every query.

Session

In SQLAlchemy, a workspace that tracks your pending changes until you commit them.

Volume

Docker storage that lives outside a container, so data survives when the container is removed.

Image vs container

An image is the frozen template; a container is a running instance launched from it.

Vectors, pgvector & vector databases

A relational database is built to match exact values: WHERE state = 'MI'. A vector database answers a different question — "what is most similar in meaning?" — which is the engine behind semantic search and RAG (retrieval-augmented generation).

When you pass text through an embedding model, it comes back as a long list of numbers — a vector — that captures the text's meaning. Texts about similar ideas produce vectors that sit close together in space. So "find related documents" becomes a geometry problem: find the stored vectors nearest to the query's vector.

So the question juniors ask: can I just use Postgres or SQLite for this, or do I need a special database? Three honest answers:

pgvector — Postgres learns vectors

pgvector is an open-source extension that teaches PostgreSQL a vector column type plus distance operators for nearest-neighbor search. By default it does exact search; you can add an index (HNSW or IVFFlat) to trade a little accuracy for a lot of speed.

The easiest start is the prebuilt Docker image — same workflow as Section 09, just a different image:

docker run --name vec-pg \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 -d pgvector/pgvector:pg16

# then enable the extension, once per database
CREATE EXTENSION IF NOT EXISTS vector;

With SQLAlchemy, a vector is just another column type. Define it, store embeddings, and order by distance to find the closest matches:

from pgvector.sqlalchemy import Vector
from sqlalchemy import select
from sqlalchemy.orm import Mapped, mapped_column

class Doc(Base):
    __tablename__ = "docs"
    id:        Mapped[int] = mapped_column(primary_key=True)
    content:   Mapped[str]
    embedding = mapped_column(Vector(1536))  # 1536 = model's output size

# Find the 5 docs closest in meaning to a query vector
q = [0.21, -0.44, 0.83, ...]            # from your embedding model
stmt = select(Doc).order_by(
    Doc.embedding.cosine_distance(q)    # also: l2_distance, max_inner_product
).limit(5)
results = session.scalars(stmt).all()

Install the helper with pip install pgvector. The vector's dimension (here 1536) must match whatever embedding model you use.

pgvector or a dedicated vector DB?