SQL Alchemy relationships

You're familiar with SQLAlchemy, a library that is used as an Object Relational Mapper (ORM) tool. ORM allows converting data from an object-oriented programming language into a format used by a relational database and vice versa. It helps match and synchronize the data between two systems. One essential part of ORM is relationships. Relationship is a connection between tables, one of which has a foreign key that references to the primary key of another table. This allows a database to split and store data in different tables, and at the same time link them together. This is a fundamental feature of relational databases. It differentiates them from any other types of databases and makes relational databases such powerful and efficient for information storage, so that's why it is important to understand the idea of relationships.

There are four basic types of relationships in SQLAlchemy: one-to-one, one-to-many, many-to-one and many-to-many. Let's clarify each of them.

One-to-one

One-to-one relationship is a type of relationship when an object from the first table is connected with another single object of another table and vice-versa. For example, passport-citizen (one person can have only one passport, and a passport belongs to only one person).

Suppose, that we have two tables in our database: citizen with columns id, name, and a table passport with columns id, passport_id. For linking them, we will need the relationship() function.

from sqlalchemy import Column, ForeignKey, Integer, Table
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class Citizen(Base):

    __tablename__ = "citizen"

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

    # Relationships
    passport = relationship("Passport")

class Passport(Base):

    __tablename__ = "passport"

    id = Column(Integer, primary_key=True)
    citizen_id = Column(Integer, ForeignKey("citizen.id"))
    passport_id = Column(String(25), unique=True, nullable=False)

    # Relationships
    citizen = relationship("Citizen")

While creating models, set a foreign key that says that this particular column is relational between tables: the most common items of one table "belong" to the items of another table, like when citizens "own" passports. In our example, we're saying that each passport has an owner (citizen), and we specified it in the following way:

citizen_id = Column(Integer, ForeignKey("citizen.id"))

We have linked data between our citizen and passport tables so that fetching one will allow us to get information about the other.

Here, the relationships complement the foreign keys and tell our application (not our database) that we are building a connection between two models. In the example below, the value of the foreign key is citizen.id, which is the table name for our citizen table. And also we pass the value Citizen to our relationship, which is the class name of the data model (not the table name).

Foreign keys tell SQL which connection you want; the relationships tell the same, but to the app. We need to do both. So, relationships create a connection between models and foreign keys between tables.

One-to-many

One-to-many relationship connects an object of the first (parent) table with one or more objects of the second (child) table, but not vice versa. Any object of the second table can be associated with only one object of the first table. For example, class-student (in one class, there can be many students, but each student belongs to only one group) or customer-order (a customer can have several orders, but each order can have only one customer). One-to-many relationships are the most common types of database relationships.

Let's see an example in the context of users and their posts (one user can have many posts). Suppose, that we have two tables: user and post.

from sqlalchemy.orm import relationship


class User(Base):

    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    username = Column(String(255), unique=True, nullable=False)
    first_name = Column(String(255))
    last_name = Column(String(255))
    bio = Column(Text)

  


class Post(Base):
 

    __tablename__ = "post"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("user.id")) 
    title = Column(String(255), nullable=False)
    body = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now())

    # Relationships
    user = relationship("User")

   

Here User is a parent table and Post is a child. In our example, we are saying that users "own" posts, and each post has a user in the following way:

author_id = Column(Integer, ForeignKey("user.id"))

We have made a connection between user and post tables. For this, we needed to assign a value user.id, which is the name of the user table, and pass the value User to our relationship, the class name of the data model (not the table name).

Many-to-one

A many-to-one relationship associates many children with a single parent. Actually, It is the same as the previous types of relationships, but with many-to-one, we place a foreign key in the parent table referencing the child, relationship()is also declared on the parent.

Many-to-many

Many-to-many relationship means that any object of the first table can be associated with one or more objects of another table and vice versa. For example, student-discipline (each student can study many disciplines, and each discipline can be taken by many students).

Many-to-many relationships are defined like the one-to-many relationships, but you also need to define an association table and add it to the secondary argument in relationship() in the following way:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


association_table = Table(
    "StudentDiscipline",
    Base.metadata,
    Column("discipline_id", Integer, ForeignKey("discipline.id")),
    Column("student_id", Integer, ForeignKey("student.id")),
)


class Discipline(Base):
    

    __tablename__ = "discipline"
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Student(Base):
  

    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    disciplines = relationship("Discipline", secondary="StudentDiscipline")

In the association table, the names of the columns are defined in the format tablename_columnname. Each column must have a foreign key defined in the format tablename.columnname.

Conclusion

In this topic, we've discussed relationships in relational databases. Let's sum up everything:

• A relationship is a connection between tables in a database, one of which has a foreign key;

• Foreign keys create a connection only between tables, when relationship creates a connection between models as well;

There are four basic types of relationships in SQLAlchemy:

• One-to-one relationship creates a connection between one object of the first table and another single object of another table and vice-versa (citizen-passport);

• One-to-many relationship creates a connection between an object of the first table and one or more objects of the second table, but not vice versa (customer-orders);

• Many-to-one relationship creates a connection between many objects of one table and one object of another table (students-class);

• Many-to-many relationship creates a connection between any object of the first table and one or more objects of another table (students-subjects).

Now, let's go to practice!