Models

Defining models

By defining an ormar Model you get corresponding Pydantic model as well as Sqlalchemy table for free. They are being managed in the background and you do not have to create them on your own.

Model Class

To build an ormar model you simply need to inherit a ormar.Model class.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Defining Fields

Next assign one or more of the Fields as a class level variables.

Basic Field Types

Each table has to have a primary key column, which you specify by setting primary_key=True on selected field.

Only one primary key column is allowed.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Warning

Not assigning primary_key column or assigning more than one column per Model will raise ModelDefinitionError exception.

By default if you assign primary key to Integer field, the autoincrement option is set to true.

You can disable by passing autoincrement=False.

id: int = ormar.Integer(primary_key=True, autoincrement=False)

Non Database Fields

Note that if you need a normal pydantic field in your model (used to store value on model or pass around some value) you can define a field with parameter pydantic_only=True.

Fields created like this are added to the pydantic model fields -> so are subject to validation according to Field type, also appear in dict() and json() result.

The difference is that those fields are not saved in the database. So they won't be included in underlying sqlalchemy columns, or table variables (check Internals section below to see how you can access those if you need).

Subsequently pydantic_only fields won't be included in migrations or any database operation (like save, update etc.)

Fields like those can be passed around into payload in fastapi request and will be returned in fastapi response (of course only if you set their value somewhere in your code as the value is not fetched from the db. If you pass a value in fastapi request and return the same instance that fastapi constructs for you in request_model you should get back exactly same value in response.).

Warning

pydantic_only=True fields are always Optional and it cannot be changed (otherwise db load validation would fail)

Tip

pydantic_only=True fields are a good solution if you need to pass additional information from outside of your API (i.e. frontend). They are not stored in db but you can access them in your APIRoute code and they also have pydantic validation.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)
    non_db_field: str = ormar.String(max_length=100, pydantic_only=True)

If you combine pydantic_only=True field with default parameter and do not pass actual value in request you will always get default value. Since it can be a function you can set default=datetime.datetime.now and get current timestamp each time you call an endpoint etc.

Note

Note that both pydantic_only and property_field decorated field can be included/excluded in both dict() and fastapi response with include/exclude and response_model_include/response_model_exclude accordingly.

# <==related of code removed for clarity==>
class User(ormar.Model):
    class Meta:
        tablename: str = "users2"
        metadata = metadata
        database = database

    id: int = ormar.Integer(primary_key=True)
    email: str = ormar.String(max_length=255, nullable=False)
    password: str = ormar.String(max_length=255)
    first_name: str = ormar.String(max_length=255)
    last_name: str = ormar.String(max_length=255)
    category: str = ormar.String(max_length=255, nullable=True)
    timestamp: datetime.datetime = ormar.DateTime(
        pydantic_only=True, default=datetime.datetime.now
    )

# <==related of code removed for clarity==>
app =FastAPI()

@app.post("/users/")
async def create_user(user: User):
    return await user.save()

# <==related of code removed for clarity==>

def test_excluding_fields_in_endpoints():
    client = TestClient(app)
    with client as client:
        timestamp = datetime.datetime.now()

        user = {
            "email": "test@domain.com",
            "password": "^*^%A*DA*IAAA",
            "first_name": "John",
            "last_name": "Doe",
            "timestamp": str(timestamp),
        }
        response = client.post("/users/", json=user)
        assert list(response.json().keys()) == [
            "id",
            "email",
            "first_name",
            "last_name",
            "category",
            "timestamp",
        ]
        # returned is the same timestamp
        assert response.json().get("timestamp") == str(timestamp).replace(" ", "T")


# <==related of code removed for clarity==>

Property fields

Sometimes it's desirable to do some kind of calculation on the model instance. One of the most common examples can be concatenating two or more fields. Imagine you have first_name and last_name fields on your model, but would like to have full_name in the result of the fastapi query.

You can create a new pydantic model with a method that accepts only self (so like default python @property) and populate it in your code.

But it's so common that ormar has you covered. You can "materialize" a property_field on you Model.

Warning

property_field fields are always Optional and it cannot be changed (otherwise db load validation would fail)

import databases
import sqlalchemy

import ormar
from ormar import property_field

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

    @property_field
    def prefixed_name(self):
        return "custom_prefix__" + self.name

Warning

The decorated function has to accept only one parameter, and that parameter have to be self.

If you try to decorate a function with more parameters ormar will raise ModelDefinitionError.

Sample:

# will raise ModelDefinitionError
@property_field
def prefixed_name(self, prefix="prefix_"):
    return 'custom_prefix__' + self.name

# will raise ModelDefinitionError 
# (calling first param something else than 'self' is a bad practice anyway)
@property_field
def prefixed_name(instance):
    return 'custom_prefix__' + self.name

Note that property_field decorated methods do not go through verification (but that might change in future) and are only available in the response from fastapi and dict() and json() methods. You cannot pass a value for this field in the request (or rather you can but it will be discarded by ormar so really no point but no Exception will be raised).

Note

Note that both pydantic_only and property_field decorated field can be included/excluded in both dict() and fastapi response with include/exclude and response_model_include/response_model_exclude accordingly.

Tip

Note that @property_field decorator is designed to replace the python @property decorator, you do not have to combine them.

In theory you can cause ormar have a failsafe mechanism, but note that i.e. mypy will complain about re-decorating a property.

# valid and working but unnecessary and mypy will complain
@property_field
@property
def prefixed_name(self):
    return 'custom_prefix__' + self.name

# <==related of code removed for clarity==>
def gen_pass():  # note: NOT production ready 
    choices = string.ascii_letters + string.digits + "!@#$%^&*()"
    return "".join(random.choice(choices) for _ in range(20))

class RandomModel(ormar.Model):
    class Meta:
        tablename: str = "random_users"
        metadata = metadata
        database = database

        include_props_in_dict = True

    id: int = ormar.Integer(primary_key=True)
    password: str = ormar.String(max_length=255, default=gen_pass)
    first_name: str = ormar.String(max_length=255, default="John")
    last_name: str = ormar.String(max_length=255)
    created_date: datetime.datetime = ormar.DateTime(
        server_default=sqlalchemy.func.now()
    )

    @property_field
    def full_name(self) -> str:
        return " ".join([self.first_name, self.last_name])

# <==related of code removed for clarity==>
app =FastAPI()

# explicitly exclude property_field in this endpoint
@app.post("/random/", response_model=RandomModel, response_model_exclude={"full_name"})
async def create_user(user: RandomModel):
    return await user.save()

# <==related of code removed for clarity==>

def test_excluding_property_field_in_endpoints2():
    client = TestClient(app)
    with client as client:
        RandomModel.Meta.include_props_in_dict = True
        user3 = {"last_name": "Test"}
        response = client.post("/random3/", json=user3)
        assert list(response.json().keys()) == [
            "id",
            "password",
            "first_name",
            "last_name",
            "created_date",
        ]
        # despite being decorated with property_field if you explicitly exclude it it will be gone
        assert response.json().get("full_name") is None

# <==related of code removed for clarity==>

Fields names vs Column names

By default names of the fields will be used for both the underlying pydantic model and sqlalchemy table.

If for whatever reason you prefer to change the name in the database but keep the name in the model you can do this with specifying name parameter during Field declaration

Here you have a sample model with changed names

import databases
import sqlalchemy

import ormar

database = databases.Database("sqlite:///test.db", force_rollback=True)
metadata = sqlalchemy.MetaData()


class Child(ormar.Model):
    class Meta:
        tablename = "children"
        metadata = metadata
        database = database

    id: int = ormar.Integer(name="child_id", primary_key=True)
    first_name: str = ormar.String(name="fname", max_length=100)
    last_name: str = ormar.String(name="lname", max_length=100)
    born_year: int = ormar.Integer(name="year_born", nullable=True)

Note that you can also change the ForeignKey column name

from typing import Optional

import databases
import sqlalchemy

import ormar
from .docs010 import Artist  # previous example

database = databases.Database("sqlite:///test.db", force_rollback=True)
metadata = sqlalchemy.MetaData()


class Album(ormar.Model):
    class Meta:
        tablename = "music_albums"
        metadata = metadata
        database = database

    id: int = ormar.Integer(name="album_id", primary_key=True)
    name: str = ormar.String(name="album_name", max_length=100)
    artist: Optional[Artist] = ormar.ForeignKey(Artist, name="artist_id")

But for now you cannot change the ManyToMany column names as they go through other Model anyway.

import databases
import sqlalchemy

import ormar
from .docs008 import Child

database = databases.Database("sqlite:///test.db", force_rollback=True)
metadata = sqlalchemy.MetaData()


class ArtistChildren(ormar.Model):
    class Meta:
        tablename = "children_x_artists"
        metadata = metadata
        database = database


class Artist(ormar.Model):
    class Meta:
        tablename = "artists"
        metadata = metadata
        database = database

    id: int = ormar.Integer(name="artist_id", primary_key=True)
    first_name: str = ormar.String(name="fname", max_length=100)
    last_name: str = ormar.String(name="lname", max_length=100)
    born_year: int = ormar.Integer(name="year")
    children = ormar.ManyToMany(Child, through=ArtistChildren)

Overwriting the default QuerySet

If you want to customize the queries run by ormar you can define your own queryset class (that extends the ormar QuerySet) in your model class, default one is simply the QuerySet

You can provide a new class in Meta configuration of your class as queryset_class parameter.

import ormar
from ormar.queryset.queryset import QuerySet
from fastapi import HTTPException


class MyQuerySetClass(QuerySet):

    async def first_or_404(self, *args, **kwargs):
        entity = await self.get_or_none(*args, **kwargs) 
        if entity is None:
            # in fastapi or starlette
            raise HTTPException(404)


class Book(ormar.Model):

    class Meta(ormar.ModelMeta):
        metadata = metadata
        database = database
        tablename = "book"
        queryset_class = MyQuerySetClass

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


# when book not found, raise `404` in your view.
book = await Book.objects.first_or_404(name="123")

Type Hints & Legacy

Before version 0.4.0 ormar supported only one way of defining Fields on a Model using python type hints as pydantic.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: ormar.Integer(primary_key=True)
    name: ormar.String(max_length=100)
    completed: ormar.Boolean(default=False)

c1 = Course()

But that didn't play well with static type checkers like mypy and pydantic PyCharm plugin.

Therefore from version >=0.4.0 ormar switched to new notation.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Note that type hints are optional so perfectly valid ormar code can look like this:

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id = ormar.Integer(primary_key=True)
    name = ormar.String(max_length=100)
    completed = ormar.Boolean(default=False)

Warning

Even if you use type hints ormar does not use them to construct pydantic fields!

Type hints are there only to support static checkers and linting, ormar construct annotations used by pydantic from own fields.

Dependencies

Since ormar depends on databases and sqlalchemy-core for database connection and table creation you need to assign each Model with two special parameters.

Databases

One is Database instance created with your database url in sqlalchemy connection string format.

Created instance needs to be passed to every Model with Meta class database parameter.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Tip

You need to create the Database instance only once and use it for all models. You can create several ones if you want to use multiple databases.

Sqlalchemy

Second dependency is sqlalchemy MetaData instance.

Created instance needs to be passed to every Model with Meta class metadata parameter.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Tip

You need to create the MetaData instance only once and use it for all models. You can create several ones if you want to use multiple databases.

Best practice

Only thing that ormar expects is a class with name Meta and two class variables: metadata and databases.

So instead of providing the same parameters over and over again for all models you should creata a class and subclass it in all models.

from typing import Optional

import databases
import sqlalchemy

import ormar

database = databases.Database("sqlite:///test.db", force_rollback=True)
metadata = sqlalchemy.MetaData()


# note that you do not have to subclass ModelMeta,
# it's useful for type hints and code completion
class MainMeta(ormar.ModelMeta):
    metadata = metadata
    database = database


class Artist(ormar.Model):
    class Meta(MainMeta):
        # note that tablename is optional
        # if not provided ormar will user class.__name__.lower()+'s'
        # -> artists in this example
        pass

    id: int = ormar.Integer(primary_key=True)
    first_name: str = ormar.String(max_length=100)
    last_name: str = ormar.String(max_length=100)
    born_year: int = ormar.Integer(name="year")


class Album(ormar.Model):
    class Meta(MainMeta):
        pass

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    artist: Optional[Artist] = ormar.ForeignKey(Artist)

Warning

You need to subclass your MainMeta class in each Model class as those classes store configuration variables that otherwise would be overwritten by each Model.

Table Names

By default table name is created from Model class name as lowercase name plus 's'.

You can overwrite this parameter by providing Meta class tablename argument.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        # if you omit this parameter it will be created automatically
        # as class.__name__.lower()+'s' -> "courses" in this example
        tablename = "my_courses"
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Constraints

On a model level you can also set model-wise constraints on sql columns.

Right now only IndexColumns and UniqueColumns constraints are supported.

Note

Note that both constraints should be used only if you want to set a name on constraint or want to set the index on multiple columns, otherwise index and unique properties on ormar fields are preferred.

Tip

To read more about columns constraints like primary_key, unique, ForeignKey etc. visit fields.

UniqueColumns

You can set this parameter by providing Meta class constraints argument.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata
        # define your constraints in Meta class of the model
        # it's a list that can contain multiple constraints
        # hera a combination of name and column will have to be unique in db
        constraints = [ormar.UniqueColumns("name", "completed")]

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Note

Note that constraints are meant for combination of columns that should be unique. To set one column as unique use unique common parameter. Of course you can set many columns as unique with this param but each of them will be checked separately.

IndexColumns

You can set this parameter by providing Meta class constraints argument.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata
        # define your constraints in Meta class of the model
        # it's a list that can contain multiple constraints
        # hera a combination of name and column will have a compound index in the db
        constraints = [ormar.IndexColumns("name", "completed")]

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Note

Note that constraints are meant for combination of columns that should be in the index. To set one column index use unique common parameter. Of course, you can set many columns as indexes with this param but each of them will be a separate index.

CheckColumns

You can set this parameter by providing Meta class constraints argument.

import datetime
import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata
        # define your constraints in Meta class of the model
        # it's a list that can contain multiple constraints
        # hera a combination of name and column will have a level check in the db
        constraints = [
            ormar.CheckColumns("start_time < end_time", name="date_check"),
        ]

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    start_date: datetime.date = ormar.Date()
    end_date: datetime.date = ormar.Date()

Note

Note that some databases do not actively support check constraints such as MySQL.

Pydantic configuration

As each ormar.Model is also a pydantic model, you might want to tweak the settings of the pydantic configuration.

The way to do this in pydantic is to adjust the settings on the Config class provided to your model, and it works exactly the same for ormar models.

So in order to set your own preferences you need to provide not only the Meta class but also the Config class to your model.

Note

To read more about available settings visit the pydantic config page.

Note that if you do not provide your own configuration, ormar will do it for you. The default config provided is as follows:

class Config(pydantic.BaseConfig):
    orm_mode = True
    validate_assignment = True

So to overwrite setting or provide your own a sample model can look like following:

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    class Config:
        allow_mutation = False

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)

Extra fields in models

By default ormar forbids you to pass extra fields to Model.

If you try to do so the ModelError will be raised.

Since the extra fields cannot be saved in the database the default to disallow such fields seems a feasible option.

On the contrary in pydantic the default option is to ignore such extra fields, therefore ormar provides an Meta.extra setting to behave in the same way.

To ignore extra fields passed to ormar set this setting to Extra.ignore instead of default Extra.forbid.

Note that ormar does not allow accepting extra fields, you can only ignore them or forbid them (raise exception if present)

from ormar import Extra

class Child(ormar.Model):
    class Meta(ormar.ModelMeta):
        tablename = "children"
        metadata = metadata
        database = database
        extra = Extra.ignore  # set extra setting to prevent exceptions on extra fields presence

    id: int = ormar.Integer(name="child_id", primary_key=True)
    first_name: str = ormar.String(name="fname", max_length=100)
    last_name: str = ormar.String(name="lname", max_length=100)

To set the same setting on all model check the best practices and BaseMeta concept.

Model sort order

When querying the database with given model by default the Model is ordered by the primary_key column ascending. If you wish to change the default behaviour you can do it by providing orders_by parameter to model Meta class.

Sample default ordering:

database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()


class BaseMeta(ormar.ModelMeta):
    metadata = metadata
    database = database

# default sort by column id ascending
class Author(ormar.Model):
    class Meta(BaseMeta):
        tablename = "authors"

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

Modified

database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()


class BaseMeta(ormar.ModelMeta):
    metadata = metadata
    database = database

# now default sort by name descending
class Author(ormar.Model):
    class Meta(BaseMeta):
        tablename = "authors"
        orders_by = ["-name"]

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

Model Initialization

There are two ways to create and persist the Model instance in the database.

Tip

Use ipython to try this from the console, since it supports await.

If you plan to modify the instance in the later execution of your program you can initiate your Model as a normal class and later await a save() call.

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)


course = Course(name="Painting for dummies", completed=False)
await course.save()

await Course.objects.create(name="Painting for dummies", completed=False)

If you want to initiate your Model and at the same time save in in the database use a QuerySet's method create().

For creating multiple objects at once a bulk_create() QuerySet's method is available.

Each model has a QuerySet initialised as objects parameter

import databases
import sqlalchemy

import ormar

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    completed: bool = ormar.Boolean(default=False)


course = Course(name="Painting for dummies", completed=False)
await course.save()

await Course.objects.create(name="Painting for dummies", completed=False)

Info

To read more about QuerySets (including bulk operations) and available methods visit queries

`Model` save status

Each model instance is a separate python object and they do not know anything about each other.

track1 = await Track.objects.get(name='The Bird')
track2 = await Track.objects.get(name='The Bird')
assert track1 == track2 # True

track1.name = 'The Bird2'
await track1.save()
assert track1.name == track2.name # False
# track2 does not update and knows nothing about track1

The objects itself have a saved status, which is set as following:

Model is saved after save/update/load/upsert method on model
Model is saved after create/get/first/all/get_or_create/update_or_create method
Model is saved when passed to bulk_update and bulk_create
Model is saved after adding/removing ManyToMany related objects (through model instance auto saved/deleted)
Model is not saved after change of any own field (including pk as Model.pk alias)
Model is not saved after adding/removing ForeignKey related object (fk column not saved)
Model is not saved after instantiation with __init__ (w/o QuerySet.create or before calling save)

You can check if model is saved with ModelInstance.saved property

Models

Defining models

Model Class

Defining Fields

Basic Field Types

Non Database Fields

Property fields

Fields names vs Column names

Overwriting the default QuerySet

Type Hints & Legacy

Dependencies

Databases

Sqlalchemy

Best practice

Table Names

Constraints

UniqueColumns

IndexColumns

CheckColumns

Pydantic configuration

Extra fields in models

Model sort order

Model Initialization

Model save status

`Model` save status