ormar

Overview

The ormar package is an async mini ORM for Python, with support for Postgres, MySQL, and SQLite.

The main benefits of using ormar are:

• getting an async ORM that can be used with async frameworks (fastapi, starlette etc.)
• getting just one model to maintain - you don't have to maintain pydantic and other orm models (sqlalchemy, peewee, gino etc.)

The goal was to create a simple ORM that can be used directly (as request and response models) with [fastapi][fastapi] that bases it's data validation on pydantic.

Ormar - apart from the obvious "ORM" in name - gets its name from ormar in Swedish which means snakes, and ormar in Croatian which means cabinet.

And what's a better name for python ORM than snakes cabinet :)

If you like ormar remember to star the repository in github!

The bigger community we build, the easier it will be to catch bugs and attract contributors ;)

Documentation

Check out the [documentation][documentation] for details.

Note that for brevity most of the documentation snippets omit the creation of the database and scheduling the execution of functions for asynchronous run.

If you want more real life examples than in the documentation you can see the [tests][tests] folder, since they actually have to create and connect to a database in most of the tests.

Yet remember that those are - well - tests and not all solutions are suitable to be used in real life applications.

Part of the fastapi ecosystem

As part of the fastapi ecosystem ormar is supported in libraries that somehow work with databases.

As of now ormar is supported by:

• fastapi-crudrouter
• fastapi-pagination

If you maintain or use a different library and would like it to support ormar let us know how we can help.

Dependencies

Ormar is built with:

• [sqlalchemy core][sqlalchemy-core] for query building.
• [sqlalchemy async][sqlalchemy-async] for cross-database async support.
• [pydantic][pydantic] for data validation.

License

ormar is built as open-sorce software and will remain completely free (MIT license).

As I write open-source code to solve everyday problems in my work or to promote and build strong python community you can say thank you and buy me a coffee or sponsor me with a monthly amount to help ensure my work remains free and maintained.

Sponsor - Github Sponsors

Migrating from sqlalchemy and existing databases

If you currently use sqlalchemy and would like to switch to ormar check out the auto-translation tool that can help you with translating existing sqlalchemy orm models so you do not have to do it manually.

Beta versions available at github: sqlalchemy-to-ormar or simply pip install sqlalchemy-to-ormar

sqlalchemy-to-ormar can be used in pair with sqlacodegen to auto-map/ generate ormar models from existing database, even if you don't use sqlalchemy for your project.

Migrations & Database creation

Because ormar is built on SQLAlchemy core, you can use [alembic][alembic] to provide database migrations (and you really should for production code).

For tests and basic applications the sqlalchemy is more than enough:

# note this is just a partial snippet full working example below
# 1. Imports
import sqlalchemy
import ormar
from ormar import DatabaseConnection

# 2. Initialization
DATABASE_URL = "sqlite+aiosqlite:///db.sqlite"
base_ormar_config = ormar.OrmarConfig(
    metadata=sqlalchemy.MetaData(),
    database=DatabaseConnection(DATABASE_URL),
)

# Define models here

# 3. Database creation and tables creation
engine = sqlalchemy.create_engine(DATABASE_URL.replace('+aiosqlite', ''))
base_ormar_config.metadata.create_all(engine)

For a sample configuration of alembic and more information regarding migrations and database creation visit [migrations][migrations] documentation section.

Package versions

ormar is still under development: We recommend pinning any dependencies (with i.e. ormar~=0.9.1)

ormar also follows the release numeration that breaking changes bump the major number, while other changes and fixes bump minor number, so with the latter you should be safe to update, yet always read the [releases][releases] docs before. example: (0.5.2 -> 0.6.0 - breaking, 0.5.2 -> 0.5.3 - non breaking).

Asynchronous Python

Note that ormar is an asynchronous ORM, which means that you have to await the calls to the methods, that are scheduled for execution in an event loop. Python has a builtin module [asyncio][asyncio] that allows you to do just that.

Note that most "normal" python interpreters do not allow execution of await outside of a function (because you actually schedule this function for delayed execution and don't get the result immediately).

In a modern web framework (like fastapi), the framework will handle this for you, but if you plan to do this on your own you need to perform this manually like described in the quick start below.

Quick Start

Note that you can find the same script in examples folder on github.

```python from typing import Optional

import ormar import pydantic import sqlalchemy

DATABASE_URL = "sqlite+aiosqlite:///db.sqlite"

note that this step is optional -> all ormar cares is an individual

OrmarConfig for each of the models, but this way you do not

have to repeat the same parameters if you use only one database

base_ormar_config = ormar.OrmarConfig( database=ormar.DatabaseConnection(DATABASE_URL), metadata=sqlalchemy.MetaData(), )

Note that all type hints are optional

below is a perfectly valid model declaration

class Author(ormar.Model):

ormar_config = base_ormar_config.copy(tablename="authors")

id = ormar.Integer(primary_key=True) # <= notice no field types

name = ormar.String(max_length=100)

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

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

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

id: int = ormar.Integer(primary_key=True)
author: Optional[Author] = ormar.ForeignKey(Author)
title: str = ormar.String(max_length=100)
year: int = ormar.Integer(nullable=True)

note - normally import should be at the beginning of the file

import asyncio

async def setup_database(): """Create all tables in the database.""" if not base_ormar_config.database.is_connected: await base_ormar_config.database.connect() async with base_ormar_config.database.engine.begin() as conn: await conn.run_sync(base_ormar_config.metadata.drop_all) await conn.run_sync(base_ormar_config.metadata.create_all) if base_ormar_config.database.is_connected: await base_ormar_config.database.disconnect()

create the database

note that in production you should use migrations

note that this is not required if you connect to existing database

just to be sure we clear the db before

asyncio.run(setup_database())

all functions below are divided into functionality categories

note how all functions are defined with async - hence can use await AND needs to

be awaited on their own

async def create(): # Create some records to work with through QuerySet.create method. # Note that queryset is exposed on each Model's class as objects tolkien = await Author.objects.create(name="J.R.R. Tolkien") await Book.objects.create(author=tolkien, title="The Hobbit", year=1937) await Book.objects.create(author=tolkien, title="The Lord of the Rings", year=1955) await Book.objects.create(author=tolkien, title="The Silmarillion", year=1977)

# alternative creation of object divided into 2 steps
sapkowski = Author(name="Andrzej Sapkowski")
# do some stuff
await sapkowski.save()

# or save() after initialization
await Book(author=sapkowski, title="The Witcher", year=1990).save()
await Book(author=sapkowski, title="The Tower of Fools", year=2002).save()

# to read more about inserting data into the database
# visit: https://collerek.github.io/ormar/queries/create/

async def read(): # Fetch an instance, without loading a foreign key relationship on it. # Django style book = await Book.objects.get(title="The Hobbit") # or python style book = await Book.objects.get(Book.title == "The Hobbit") book2 = await Book.objects.first()

# first() fetch the instance with lower primary key value
assert book == book2

# you can access all fields on loaded model
assert book.title == "The Hobbit"
assert book.year == 1937

# when no condition is passed to get()
# it behaves as last() based on primary key column
book3 = await Book.objects.get()
assert book3.title == "The Tower of Fools"

# When you have a relation, ormar always defines a related model for you
# even when all you loaded is a foreign key value like in this example
assert isinstance(book.author, Author)
# primary key is populated from foreign key stored in books table
assert book.author.pk == 1
# since the related model was not loaded all other fields are None
assert book.author.name is None

# Load the relationship from the database when you already have the related model
# alternatively see joins section below
await book.author.load()
assert book.author.name == "J.R.R. Tolkien"

# get all rows for given model
authors = await Author.objects.all()
assert len(authors) == 2

# to read more about reading data from the database
# visit: https://collerek.github.io/ormar/queries/read/

async def update(): # read existing row from db tolkien = await Author.objects.get(name="J.R.R. Tolkien") assert tolkien.name == "J.R.R. Tolkien" tolkien_id = tolkien.id

# change the selected property
tolkien.name = "John Ronald Reuel Tolkien"
# call update on a model instance
await tolkien.update()

# confirm that object was updated
tolkien = await Author.objects.get(name="John Ronald Reuel Tolkien")
assert tolkien.name == "John Ronald Reuel Tolkien"
assert tolkien.id == tolkien_id

# alternatively update data without loading
await Author.objects.filter(name__contains="Tolkien").update(name="J.R.R. Tolkien")

# to read more about updating data in the database
# visit: https://collerek.github.io/ormar/queries/update/

async def delete(): silmarillion = await Book.objects.get(year=1977) # call delete() on instance await silmarillion.delete()

# alternatively delete without loading
await Book.objects.delete(title="The Tower of Fools")

# note that when there is no record ormar raises NoMatch exception
try:
    await Book.objects.get(year=1977)
except ormar.NoMatch:
    print("No book from 1977!")

# to read more about deleting data from the database
# visit: https://collerek.github.io/ormar/queries/delete/

# note that despite the fact that record no longer exists in database
# the object above is still accessible and you can use it (and i.e. save()) again.
tolkien = silmarillion.author
await Book.objects.create(author=tolkien, title="The Silmarillion", year=1977)

async def joins(): # Tho join two models use select_related

# Django style
book = await Book.objects.select_related("author").get(title="The Hobbit")
# Python style
book = await Book.objects.select_related(Book.author).get(
    Book.title == "The Hobbit"
)

# now the author is already prefetched
assert book.author.name == "J.R.R. Tolkien"

# By default you also get a second side of the relation
# constructed as lowercase source model name +'s' (books in this case)
# you can also provide custom name with parameter related_name

# Django style
author = await Author.objects.select_related("books").all(name="J.R.R. Tolkien")
# Python style
author = await Author.objects.select_related(Author.books).all(
    Author.name == "J.R.R. Tolkien"
)
assert len(author[0].books) == 3

# for reverse and many to many relations you can also prefetch_related
# that executes a separate query for each of related models

# Django style
author = await Author.objects.prefetch_related("books").ge