Skip to main content

Week 4

What is an ORM?

Last week, we covered SQL and PostgreSQL. However, in many applications, developers don't want to write raw SQL for any usage of the database. An ORM, Object Relation Mapper, helps bridge objects and database tables by representing tables as classes and rows as objects.

image.png

Why an ORM?

An ORM gives us a code friendly way of working with data in tables with rows, columns, primary keys, and foreign keys. This is helpful because most backend applications aren't written in SQL, but it allows us to use whatever language we want, Python or Typescript, for example, while being able to use Postgres as the database.

 

What is SQLAlchemy?

SQLAlchemy is a Python ORM that we can import as a library. This lets us describe our database tables as a Python class and its columns as class attributes.

Models

In SQLAlchemy, a model is a Python class that represents a database table. Each model class has a __tablename__ value and attributes that define columns, such as id, name, or email

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class Resource(Base):
    __tablename__ = "resources"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    category = Column(String, nullable=False)
    address = Column(String)
    contact_email = Column(String)

In this example, the Resource class maps to a table named resources, and each class attribute maps to one column in that table. 

Columns and Data Types

Just like in PostgreSQL, each column in a SQLAlchemy model has a type (Integer, String, etc). You can also add constraints in the model definition. For example, primary_key=True marks a column as the primary key, and nullable=False means that the column is required and should not be left empty.

Primary Keys and Foreign Keys

A primary key identifies a row in a table, and a foreign key references the primary key of another table, which we learned last week. Here is what that looks like in SQLAlchemy

from sqlalchemy import Column, Integer, String, ForeignKey, Date
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class Referral(Base):
    __tablename__ = "referrals"

    id = Column(Integer, primary_key=True)
    family_name = Column(String, nullable=False)
    resource_id = Column(Integer, ForeignKey("resources.id"))
    referral_date = Column(Date)
    notes = Column(String)

Here, resource_id is a foreign key that points to resources.id. That means that each referral can be linked to an existing resource, like we setup last week.

Relationships

Foreign keys connect tables at the database level, but SQLAlchemy can also define relationships at the Python level. Relationships make it easier to move between related objects in code, such as going from a resource to all of its referrals

from sqlalchemy.orm import relationship

class Resource(Base):
    __tablename__ = "resources"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    referrals = relationship("Referral", back_populates="resource")

class Referral(Base):
    __tablename__ = "referrals"

    id = Column(Integer, primary_key=True)
    resource_id = Column(Integer, ForeignKey("resources.id"))

    resource = relationship("Resource", back_populates="referrals")
Connecting to PostgreSQL

Before SQLAlchemy can do anything, we need to connect it to our database.

from sqlalchemy import create_engine

# If you are using a hosting provider, they will give you this
engine = create_engine("postgresql://user:password@localhost/dbname")
Creating Tables

After you define your models, SQLAlchemy can use the models you defined to create the tables in the database, using Base.metadata.create_all(engine). This is an example of why using an ORM is better. Instead of writing raw SQL, SQLAlchemy uses your Python models to create the matching tables

Querying Data

One of the main reasons to use an ORM is to query data using Python. SQLAlchemy sessions can retrieve all rows, filter rows, and follow relationships between models. For example

all_resources = session.query(Resource).all()
food_resources = session.query(Resource).filter(Resource.category == "Food").all()

The first line gets every row in the resources table, and the second line gets only rows where the category is "Food". SQLAlchemy turns those Python expressions into SQL queries behind the scenes.

You can also create and save new rows:

new_resource = Resource(
    name="City Food Bank",
    category="Food",
    address="123 Main St",
    contact_email="help@cityfoodbank.org"
)

session.add(new_resource)
session.commit()

Here, we create a Python object, add it to the seession, and commit the change so it is saved to PostgreSQl

Back to top