Intro to Databases & ORMs

CSCI 338: Software Engineering

Spring 2025

Announcements

• Project 1 graded: good job!
• We will start Lab 8 today in class and have some time to finish it on Thursday.
• You will need to understand databases and SQLAlchemy for Project 2
• Next week: User Interface considerations

Outline

• Introduction to databases
• Set up your database on Docker
• Introduction to SQL
• Introduction to Object-Relational Mappings
• Demo

Outline

• Introduction to databases
• Set up your database on Docker
• Introduction to SQL
• Introduction to Object-Relational Mappings
• Demo

What is a database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system.

​

How is this better than just storing data as a text file or in memory (e.g., https://meteor.unca.edu/registrar/class-schedules/api/v1/courses/2025/fall/) ?�

​

Why would you want to use a database?

• ​

Why would you want to use a database?

• Organizing your data: Strategies for grouping like entities together.
• Enforcing data validation and integrity rules
• Transaction management: if multiple people are altering the same data instance at the same time, the database will help organize these requests so that they don’t collide with each other
• Indexing: Databases also have infrastructure for building binary search trees to speed up queries (so that searches can be done in O(logn) time).
• SQL: Relational databases also understand SQL – a declarative language that allows a user to define the structure of a database, and to “CRUD” data.

Types of Databases

There are many different kinds of databases, but the two most common “families” of databases are:

1. Relational Databases
2. NoSQL Databases

Types of Databases: Relational (SQL) Databases

• Approach: Data is organized into tables with predefined schemas, where rows represent records and columns represent fields.
• Examples: PostgreSQL, Oracle, Microsoft’s SQL Server, MySQL, SQLite
• Strengths:
• Ensures data integrity with transactions
• Structured Query Language (SQL): Industry standard for querying data.
• Excellent at asking questions across multiple resources / collections. For instance: “which employees earned > $50K in revenue this week in under 40 hours?
• Weaknesses:
• Can be overkill for data that is fairly unstructured (e.g., storing chat messages, etc.)
• Scalability: Hard to scale for very large datasets

Types of Databases: NoSQL Databases

• Approach: A non-tabular database designed for flexibility, with different models (e.g., document, key-value, column-family, graph).
• Examples: MongoDB, Firebase, CouchDB (document stores), Redis (Key-Value Stores, Neo4j (Graph database)
• Strengths:
• Flexibility: “Schemaless” design allows storing unstructured and semi-structured data.
• Scalability: Designed for distributed architectures.
• Optimized for high-volume, low-latency operations.
• Weaknesses:
• Consistency: Availability over consistency
• Limited support for complex queries across documents / resources.

Outline

• Introduction to databases
• Set up your database on Docker
• Introduction to SQL
• Introduction to Object-Relational Mappings
• Demo

Activity: Set Up Your Database!

Read the Lab 8 instructions

What did we just set up?

����� Port 5433������Your Laptop communicates with port 5432�via DBeaver + Python client

PostgreSQL Database on Docker Container running on port 5432

Outline

• Introduction to databases
• Set up your database on Docker
• Introduction to SQL
• Introduction to Object-Relational Mappings
• Demo

What is SQL?

SQL is a declarative programming language

Meaning, you tell SQL what data operations you want it to execute, but the underlying database system figures out how to actually go about manipulating / retrieving the data.

In declarative languages, you specify the what, not the how

What other languages have you seen (recently) that are also declarative?

• HTML & CSS
• What else?

SQL is not only used to manipulate data, but also to define the structure and relationships of your data.

PostgreSQL Reference Documentation

• About PostgreSQL
• psql commands (command line)
• Querying a Table
• Joins Between Tables
• Aggregate Functions
• Inserts
• Updates
• Deletions

​

​

Let’s navigate to Docker…

Let’s hop onto your Docker-hosted bash terminal. Do you remember the commands?

​

ps -a # to get your process id

docker exec -it <pid> bash # to get on Docker command line

Now let’s access PostgreSQL

From the Docker terminal, jump onto the postgresql command line interface:

​

psql -U postgres # activate the psql CLI�

> \l # list all of the databases

> \du # list all of the database users

> \c dvdrentals # connect to the DVD database

psql administrative commands

Let’s look at your database…

First some basics:

1. Access your database using the the pql command line tool (demo).�
2. Note that there are data types and constraints on all of the tables. If you insert the wrong datatype, or you forget to include a required piece of data, the database will reject your request (and this is a GOOD THING). You want data integrity.

Quick Crash Course on Querying

• SELECT X from Y;
• SELECT functions (count, sum, etc.)
• INNER JOIN … (connect tables together)
• WHERE
1. Comparison operators: =, >, <, like
2. Logical operators: and, or, in, not
• GROUP BY
• ORDER BY

​

Let’s practice some queries (to be continued in lab)...

film_id | title

---------+---------------------

1 | Academy Dinosaur

2 | Ace Goldfinger

3 | Adaptation Holes

4 | Affair Prejudice

5 | African Egg

6 | Agent Truman

7 | Airplane Sierra

8 | Airport Pollock

9 | Alabama Devil

10 | Aladdin Calendar

11 | Alamo Videotape

12 | Alaska Phantom

13 | Ali Forever

14 | Alice Fantasia

15 | Alien Center

16 | Alley Evolution

17 | Alone Trip

18 | Alter Victory

19 | Amadeus Holy

20 | Amelie Hellfighters

film_id | category_id

---------+-------------

1 | 6

2 | 11

3 | 6

4 | 11

5 | 8

6 | 9

7 | 5

8 | 11

9 | 11

10 | 15

11 | 9

12 | 12

13 | 11

14 | 4

15 | 9

16 | 9

17 | 12

18 | 2

19 | 1

19 | 12

category_id | name

-------------+-------------

1 | Action

2 | Animation

3 | Children

4 | Classics

5 | Comedy

6 | Documentary

7 | Drama

8 | Family

9 | Foreign

10 | Games

11 | Horror

12 | Music

13 | New

14 | Sci-Fi

15 | Sports

16 | Travel

film table

film_category table

category table

There are a few different ways to join tables together….

title | name

---------------------+-------------

Academy Dinosaur | Documentary

Ace Goldfinger | Horror

Adaptation Holes | Documentary

Affair Prejudice | Horror

African Egg | Family

Agent Truman | Foreign

Airplane Sierra | Comedy

Airport Pollock | Horror

Alabama Devil | Horror

Aladdin Calendar | Sports

Alamo Videotape | Foreign

Alaska Phantom | Music

Ali Forever | Horror

Alice Fantasia | Classics

Alien Center | Foreign

Alley Evolution | Foreign

Alone Trip | Music

Alter Victory | Animation

Amadeus Holy | Action

Amelie Hellfighters | Music

SELECT film.title, category.name

FROM film

JOIN film_category

ON film.film_id = film_category.film_id

JOIN category

ON film_category.category_id = category.category_id

ORDER BY film.film_id

LIMIT 20;��

Green = First join connects film to film_category (joins on film_id column)

Yellow = Second join connects film_category to category (joins on category_id column)

Solution

Option 1. JOIN Syntax

title | name

---------------------+-------------

Academy Dinosaur | Documentary

Ace Goldfinger | Horror

Adaptation Holes | Documentary

Affair Prejudice | Horror

African Egg | Family

Agent Truman | Foreign

Airplane Sierra | Comedy

Airport Pollock | Horror

Alabama Devil | Horror

Aladdin Calendar | Sports

Alamo Videotape | Foreign

Alaska Phantom | Music

Ali Forever | Horror

Alice Fantasia | Classics

Alien Center | Foreign

Alley Evolution | Foreign

Alone Trip | Music

Alter Victory | Animation

Amadeus Holy | Action

Amelie Hellfighters | Music

SELECT film.title, category.name

FROM film, film_category, category

WHERE

film.film_id = film_category.film_id AND

film_category.category_id = category.category_id

ORDER BY film.film_id

LIMIT 20;��

Green = Which tables to pull from

Yellow = Connects columns together the tables.

Solution

Option 2. WHERE syntax

title | name

---------------------+-------------

Academy Dinosaur | Documentary

Ace Goldfinger | Horror

Adaptation Holes | Documentary

Affair Prejudice | Horror

African Egg | Family

Agent Truman | Foreign

Airplane Sierra | Comedy

Airport Pollock | Horror

Alabama Devil | Horror

Aladdin Calendar | Sports

Alamo Videotape | Foreign

Alaska Phantom | Music

Ali Forever | Horror

Alice Fantasia | Classics

Alien Center | Foreign

Alley Evolution | Foreign

Alone Trip | Music

Alter Victory | Animation

Amadeus Holy | Action

Amelie Hellfighters | Music

WITH film_table AS (

SELECT f.film_id, f.title, fc.category_id

FROM film AS f � JOIN film_category AS fc

ON f.film_id = fc.film_id

)

SELECT f.film_id, f.title, c.name

FROM film_table AS f�JOIN category AS c

ON f.category_id = c.category_id

ORDER BY f.film_id;

​

Defines a temporary result set that can be referenced within another SQL statement

Solution

Option 3. Common Table Expression (CTE) syntax – Thanks Connor :)

SQL: INSERT

INSERT INTO table_name(column1, column2, …)

VALUES (value1, value2, …);

​

SQL: UPDATE

UPDATE table_name

SET column1 = value1,

column2 = value2,

column3 = value3,

...

WHERE condition;

​

SQL: DELETE

DELETE FROM table_name

WHERE condition;

​

Intro to Object Relational Mapping

What is SQLAlchemy?

SQLAlchemy is a python abstraction that makes communication with databases “easier.”

• It’s database agnostic
• You can interact with the DB using raw SQL or you can use something called Object Relational Mapping
• You create some classes (usually called “models”) that represent each of your tables
• You access the data in your tables using instances of the classes you create.

Some Terminology

• Model
• Session
• AsyncIO also applies to database sessions

What is a Model?

• A Model class is a Python class that represents a database table or view. Think of it as the structure of the table, and how the table relates to other tables.
• A Model instance is a Python object that represents a single database record (or row).

This approach – known as “Object Relational Mapping” – allows a more convenient way to manipulate data via the Python language.

What is a database session?

• Acts as a middleman between your Python code and the database.
• Responsible for handling all the database interactions, like querying data, inserting new records, updating existing ones, and deleting rows.
• Think of it as a "conversation" with the database — you open the conversation, send requests (queries), get responses, and then close the conversation when you’re done.

A database session handles the following

1. Connection Management: Handles the connection to the database, so you don’t have to manually open and close connections every time.
2. Query Execution: When you run a query, the session sends that query to the database and returns the results.�
3. Transaction Handling: If you make multiple changes to the database, they’re only saved when you explicitly commit them.�
4. Object Tracking: If you modify an object, the session can automatically detect the changes and apply them to the database when you commit.

async with AsyncSession(engine) as session:

​

​

​

• AsyncSession is the async-friendly version of the session, which works nicely with async/await.
• async with ensures the session is properly opened and closed, even if an error occurs.

ORM Activity

Walkthrough of the orm_samples.py file.

How do we do the same kind of join using SQLAlchemy?!

film_id | title

---------+---------------------

1 | Academy Dinosaur

2 | Ace Goldfinger

3 | Adaptation Holes

4 | Affair Prejudice

5 | African Egg

6 | Agent Truman

7 | Airplane Sierra

8 | Airport Pollock

9 | Alabama Devil

10 | Aladdin Calendar

11 | Alamo Videotape

12 | Alaska Phantom

13 | Ali Forever

14 | Alice Fantasia

15 | Alien Center

16 | Alley Evolution

17 | Alone Trip

18 | Alter Victory

19 | Amadeus Holy

20 | Amelie Hellfighters

film_id | category_id

---------+-------------

1 | 6

2 | 11

3 | 6

4 | 11

5 | 8

6 | 9

7 | 5

8 | 11

9 | 11

10 | 15

11 | 9

12 | 12

13 | 11

14 | 4

15 | 9

16 | 9

17 | 12

18 | 2

19 | 1

20 | 12

category_id | name

-------------+-------------

1 | Action

2 | Animation

3 | Children

4 | Classics

5 | Comedy

6 | Documentary

7 | Drama

8 | Family

9 | Foreign

10 | Games

11 | Horror

12 | Music

13 | New

14 | Sci-Fi

15 | Sports

16 | Travel

film table

film_category table

category table

We create models that define relationships…

class Film(Base):

__tablename__ = 'film'

film_id = Column(Integer, primary_key=True)

title = Column(String(255), nullable=False)

….

language = relationship('Language', back_populates='films')

actors = relationship('Actor', secondary='film_actor', back_populates='films')

categories = relationship('Category', secondary='film_category', back_populates='films')

inventories = relationship('Inventory', back_populates='film')

We create models that define relationships…

class FilmCategory(Base):

__tablename__ = 'film_category'

film_id = Column(Integer, ForeignKey('film.film_id'), primary_key=True)

category_id = Column(Integer, ForeignKey('category.category_id'), primary_key=True)

last_update = Column(TIMESTAMP, nullable=False, default=datetime.utcnow)

We create models that define relationships…

class Category(Base):

__tablename__ = 'category'

category_id = Column(Integer, primary_key=True)

name = Column(String(25), nullable=False)

last_update = Column(TIMESTAMP, nullable=False, default=datetime.utcnow)

films = relationship('Film', secondary='film_category', back_populates='categories')

…making the query easier

result = await session.execute(

select(Film.title, Category.name)

.join(Film.categories)

.order_by(Film.title)

)