foreign key

ForeignKey(to: Model, *, name: str = None, unique: bool = False, nullable: bool = True, related_name: str = None, virtual: bool = False, onupdate: Union[ReferentialAction, str] = None, ondelete: Union[ReferentialAction, str] = None, **kwargs: Any) Has the parameters required to adopt the target model class.

Sqlalchemy columns and types are automatically obtained from the target model.

• Sqlalchemy columns: classes of primary key columns of the target model
• Type (for pydantic): Type of target model

definition model

To define a relationship, add a foreign key field that points to the related model.

from typing import Optional

import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Department(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Department] = ormar.ForeignKey(Department)

inverse relationship

The foreign key field automatically registers the opposite side of the relationship.

By default, it is the child (source) model name + s, such as the lesson in the following code snippet:

import asyncio
from typing import Optional

import databases
import ormar
import sqlalchemy
from examples import create_drop_database

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL), metadata=sqlalchemy.MetaData()
)


class Department(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="departments")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar_base_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Department] = ormar.ForeignKey(Department)


@create_drop_database(base_config=ormar_base_config)
async def verify():
    department = await Department(name="Science").save()
    course = Course(name="Math", completed=False, department=department)
    print(department.courses[0])
    # Will produce:
    # Course(id=None,
    #        name='Math',
    #        completed=False,
    #        department=Department(id=None, name='Science'))
    await course.save()


asyncio.run(verify())

Reverse relationships expose APIs to manage related objects from the parent.

Skip the reverse relationship

If you are sure you do not want a reverse relationship, you can use the skip_reverse = True flag for the foreign key.

If you set the skip_reverse flag internally, the field is still registered at the other end of the relationship, so you can:

• Filter by relevant model fields in reverse model
• order_by Related model fields in reverse model

But you cannot:

• Use related_name to access related fields from the reverse model
• Even if you select correlation from the opposite side of the model, the returned model does not populate the reverse instance (connections are not blocked, so you can still filter and sort relationships)
• This relationship is not populated in model_dump() and model_dump_json() When* is populated from a dictionary or json (also via fastapi), you cannot pass nested related objects. Depending on the extra settings in the pydantic configuration, it will be ignored or raise an error.

Examples:

class Author(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    first_name: str = ormar.String(max_length=80)
    last_name: str = ormar.String(max_length=80)


class Post(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    author: Optional[Author] = ormar.ForeignKey(Author, skip_reverse=True)

# create sample data
author = Author(first_name="Test", last_name="Author")
post = Post(title="Test Post", author=author)

assert post.author == author  # ok
assert author.posts  # Attribute error!

# but still can use in order_by
authors = (
    await Author.objects.select_related("posts").order_by("posts__title").all()
)
assert authors[0].first_name == "Test"

# note that posts are not populated for author even if explicitly
# included in select_related - note no posts in model_dump()
assert author.model_dump(exclude={"id"}) == {"first_name": "Test", "last_name": "Author"}

# still can filter through fields of related model
authors = await Author.objects.filter(posts__title="Test Post").all()
assert authors[0].first_name == "Test"
assert len(authors) == 1

add

Adding a child model from the parent results in adding the related model to the currently loaded parent relationship, setting the child model foreign key value, and updating the model.

department = await Department(name="Science").save()
course = Course(name="Math", completed=False) # note - not saved

await department.courses.add(course)
assert course.pk is not None # child model was saved
# relation on child model is set and FK column saved in db
assert course.department == department
# relation on parent model is also set
assert department.courses[0] == course

!!!Warning If you want to add a child model to a related model, the primary key value of the parent model must exist in the database.

Otherwise ormar will raise `RelationshipInstanceError` as it cannot set child's ForeignKey column value 
if parent model has no primary key value.

That means that in example above the department has to be saved before you can call `department.courses.add()`.

!!!Warning This method does not work with many-to-many relationships-in which case both sides of the relationship must be saved before being added to the relationship.

eliminate

Delete relevant models one by one.

In a reverse relationship, calling remove() does not delete the child model, but sets its ForeignKey value to null.

# continuing from above
await department.courses.remove(course)
assert len(department.courses) == 0
# course still exists and was saved in remove
assert course.pk is not None
assert course.department is None

# to remove child from db
await course.delete()

But if you want to clear the relationship and delete the children at the same time, you can issue:

# this will not only clear the relation 
# but also delete related course from db
await department.courses.remove(course, keep_reversed=False)

eliminate

Delete all related models in one call.

As with deletion, by default clear() clears the ForeigKey columns on the submodel (all, whether they are loaded or not).

# nulls department column on all courses related to this department
await department.courses.clear()

Set keep_reversed=False if you want to remove children from the database completely

# deletes from db all courses related to this department 
await department.courses.clear(keep_reversed=False)

query set proxy

Reverse relationships expose the QuerysetProxy API, which allows you to query related models just like you would issue a normal query.

To find out which methods of QuerySet are available, read the querysetproxy below

relevant name

You can override the related model field name by providing the related_name parameter, as follows:

from typing import Optional

import databases
import ormar
import sqlalchemy

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL), metadata=sqlalchemy.MetaData()
)


class Department(ormar.Model):
    ormar_config = ormar_base_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar_base_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Department] = ormar.ForeignKey(
        Department, related_name="my_courses"
    )


department = Department(name="Science")
course = Course(name="Math", completed=False, department=department)

print(department.my_courses[0])
# Will produce:
# Course(id=None,
#        name='Math',
#        completed=False,
#        department=Department(id=None, name='Science'))

!!!The inverse relationship at tip access returns the wekref.proxy list to avoid circular references.

!!!warningWhen you supply multiple relationships to the same model, related_names will no longer be automatically generated for you. Therefore, in this case, you must supply related_names for all fields except one (one can be default and generated) or for all related fields.

Reference action

When the object referenced by ForeignKey changes (deletes or updates), ormar sets the SQL constraints specified by the ondelete and onupdate parameters.

Possible values for ondelete and onupdate can be found in ormar.ReferentialAction:

!!!note You can pass string values directly to these two parameters instead of ormar.ReferentialAction, but this is not recommended because it breaks integrity.

cascade

Whenever a row in the parent (reference) table is deleted (or updated), the corresponding row in the child (reference) table with matching foreign key columns is also deleted (or updated). This is called a cascading delete (or update).

impose restrictions on

You cannot update or delete a value in a referenced table when there is a row in the referencing table or child table that references the value.

Similarly, a row cannot be deleted as long as there is a reference to it in the reference table or subtable.

Set_NULL

Set ForeignKey to None; this is possible only if nullable is True.

set default values

Set ForeignKey to its default value; server_default for ForeignKey must be set.

!!!note Note that the default value is not allowed, you must do this via server_default, you can read about it in this section.

do nothing

No action taken; No action and restriction are very similar. The main difference between NO ACTION and RESTRICT is that, for NO ACTION, the referential integrity check is done after attempting to change the table. RESTRICT checks before attempting to execute an UPDATE or Delete statement. If the referential integrity check fails, both referential operations behave the same: UPDATE or Delete statements cause errors.

relationship settings

You can connect relationships in a variety of ways.

model instance

The most obvious one is to pass the relevant Model instance to the constructor.

from typing import Dict, Optional, Union

import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Department(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Union[Department, Dict]] = ormar.ForeignKey(Department)


department = Department(name="Science")

# set up a relation with actual Model instance
course = Course(name="Math", completed=False, department=department)

# set up  relation with only related model pk value
course2 = Course(name="Math II", completed=False, department=department.pk)

# set up a relation with dictionary corresponding to related model
course3 = Course(name="Math III", completed=False, department=department.model_dump())

# explicitly set up None
course4 = Course(name="Math III", completed=False, department=None)

primary key value

You can also set up relationships using only the pk column values of the related model.

from typing import Dict, Optional, Union

import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Department(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Union[Department, Dict]] = ormar.ForeignKey(Department)


department = Department(name="Science")

# set up a relation with actual Model instance
course = Course(name="Math", completed=False, department=department)

# set up  relation with only related model pk value
course2 = Course(name="Math II", completed=False, department=department.pk)

# set up a relation with dictionary corresponding to related model
course3 = Course(name="Math III", completed=False, department=department.model_dump())

# explicitly set up None
course4 = Course(name="Math III", completed=False, department=None)

dictionaries

The next option is to use a key-value dictionary for correlation models.

You can build your own dictionary or use the model_dump () method to get it from an existing model.

from typing import Dict, Optional, Union

import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Department(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Union[Department, Dict]] = ormar.ForeignKey(Department)


department = Department(name="Science")

# set up a relation with actual Model instance
course = Course(name="Math", completed=False, department=department)

# set up  relation with only related model pk value
course2 = Course(name="Math II", completed=False, department=department.pk)

# set up a relation with dictionary corresponding to related model
course3 = Course(name="Math III", completed=False, department=department.model_dump())

# explicitly set up None
course4 = Course(name="Math III", completed=False, department=None)

nothing

Finally, you can explicitly set it to None (default behavior if no value is passed).

from typing import Dict, Optional, Union

import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Department(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Course(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
    )

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    department: Optional[Union[Department, Dict]] = ormar.ForeignKey(Department)


department = Department(name="Science")

# set up a relation with actual Model instance
course = Course(name="Math", completed=False, department=department)

# set up  relation with only related model pk value
course2 = Course(name="Math II", completed=False, department=department.pk)

# set up a relation with dictionary corresponding to related model
course3 = Course(name="Math III", completed=False, department=department.model_dump())

# explicitly set up None
course4 = Course(name="Math III", completed=False, department=None)

!!!Warning In all non-None cases, the primary key value of the correlation model must exist in the database.

Otherwise an IntegrityError will be raised by your database driver library.