Tortoise-RM-asynchronous ORM

Tortoise-ORM: A Complete Guide to Asynchronous Database Work in Python

Introduction

Modern Python development increasingly demands an asynchronous approach, especially when building high‑performance web applications and APIs. Traditional ORM solutions often lack built‑in async support, creating challenges for developers working with modern frameworks like FastAPI, Starlette, or Quart.

Tortoise-ORM solves this problem by providing a powerful asynchronous ORM library that natively supports async/await patterns. Inspired by Django ORM, it is easy to pick up for developers familiar with Django, yet it is specifically designed for asynchronous applications.

What Is Tortoise-ORM and Why You Need It

ORM Basics and the Benefits of Asynchrony

ORM (Object‑Relational Mapping) is a programming technique that lets you describe database structures as classes and objects in code. Instead of writing raw SQL, developers can work with data through convenient Python objects.

Asynchrony in an ORM is critical for modern applications because it allows:

• Processing thousands of concurrent requests without blocking
• Efficient server resource utilization
• Building scalable, high‑performance APIs
• Avoiding bottlenecks during long‑running database operations

Key Advantages of Tortoise-ORM

Tortoise-ORM offers several core benefits:

Native asynchrony: The library is built from the ground up for async/await, delivering maximum performance.

Familiar syntax: The API mirrors Django ORM, simplifying migration and learning.

Broad DBMS support: Works with PostgreSQL, MySQL, SQLite via asynchronous drivers.

Integration with modern frameworks: Easy to integrate with FastAPI, Starlette, and other ASGI applications.

Use Cases for Tortoise-ORM

Web Development and APIs

Tortoise-ORM is ideal for building RESTful APIs and GraphQL services on FastAPI or Starlette. Its asynchronous nature enables handling many simultaneous database queries without performance degradation.

Microservice Architecture

In microservice environments where request speed and resource efficiency are paramount, Tortoise-ORM provides the required performance and scalability.

High‑Load Applications

For applications with heavy traffic that need to manage a large number of concurrent database connections, Tortoise-ORM’s async capabilities become essential.

Installation and Initial Configuration

Basic Installation

pip install tortoise-orm

Installation with Specific DBMS Support

For PostgreSQL:

pip install tortoise-orm[asyncpg]

For MySQL:

pip install tortoise-orm[aiomysql]

For SQLite:

pip install tortoise-orm[aiosqlite]

Full Installation with All Dependencies

pip install tortoise-orm[asyncpg,aiomysql,aiosqlite]

Creating and Configuring Models

Defining Basic Models

Models in Tortoise-ORM are defined by inheriting from the base Model class:

from tortoise import fields
from tortoise.models import Model

class Author(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=100)
    birth_date = fields.DateField(null=True)
    biography = fields.TextField(null=True)
    is_active = fields.BooleanField(default=True)
    created_at = fields.DatetimeField(auto_now_add=True)
    updated_at = fields.DatetimeField(auto_now=True)
    
    class Meta:
        table = "authors"
        ordering = ["name"]

Advanced Field Types

Tortoise-ORM supports a wide range of field types for various needs:

class Book(Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=200)
    isbn = fields.CharField(max_length=13, unique=True)
    page_count = fields.IntField()
    price = fields.DecimalField(max_digits=10, decimal_places=2)
    publication_date = fields.DateField()
    description = fields.TextField()
    cover_image = fields.CharField(max_length=500, null=True)
    metadata = fields.JSONField(default=dict)
    author = fields.ForeignKeyField('models.Author', related_name='books')
    genres = fields.ManyToManyField('models.Genre', related_name='books')
    
    class Meta:
        table = "books"
        unique_together = (("title", "author"),)

Initializing and Connecting to the Database

Basic Initialization

from tortoise import Tortoise

async def init_db():
    await Tortoise.init(
        db_url='sqlite://db.sqlite3',
        modules={'models': ['models']}
    )
    await Tortoise.generate_schemas()

async def close_db():
    await Tortoise.close_connections()

Configuration for Different DBMS

# PostgreSQL
DATABASE_URL = "postgres://user:password@localhost:5432/database"

# MySQL
DATABASE_URL = "mysql://user:password@localhost:3306/database"

# SQLite
DATABASE_URL = "sqlite://./database.sqlite3"

# Dictionary‑based configuration
TORTOISE_CONFIG = {
    "connections": {
        "default": {
            "engine": "tortoise.backends.asyncpg",
            "credentials": {
                "host": "localhost",
                "port": "5432",
                "user": "postgres",
                "password": "password",
                "database": "mydb",
            }
        }
    },
    "apps": {
        "models": {
            "models": ["app.models", "aerich.models"],
            "default_connection": "default",
        }
    },
}

Core Data Operations

Creating Records

# Create a single record
author = await Author.create(
    name='Fyodor Dostoevsky',
    birth_date=date(1821, 11, 11),
    biography='Great Russian writer'
)

# Bulk create multiple records
authors = await Author.bulk_create([
    Author(name='Leo Tolstoy', birth_date=date(1828, 9, 9)),
    Author(name='Anton Chekhov', birth_date=date(1860, 1, 29)),
])

Retrieving Data

# Get a single record
author = await Author.get(name='Fyodor Dostoevsky')

# Get with condition or raise exception
try:
    author = await Author.get(id=999)
except DoesNotExist:
    print("Author not found")

# Get or create
author, created = await Author.get_or_create(
    name='New Author',
    defaults={'birth_date': date(1900, 1, 1)}
)

# Retrieve all records
all_authors = await Author.all()

# First / last records
first_author = await Author.first()
last_author = await Author.last()

Filtering and Searching

# Simple filter
active_authors = await Author.filter(is_active=True)

# Complex filter
authors = await Author.filter(
    birth_date__gte=date(1800, 1, 1),
    birth_date__lt=date(1900, 1, 1)
)

# Text contains (case‑insensitive)
authors = await Author.filter(name__icontains='tolstoy')

# Exclude records
authors = await Author.exclude(birth_date__isnull=True)

# Combined conditions
from tortoise.queryset import Q
authors = await Author.filter(
    Q(name__icontains='tolstoy') | Q(name__icontains='dostoev')
)

Updating Data

# Update a single record
author = await Author.get(id=1)
author.name = 'F. M. Dostoevsky'
await author.save()

# Update from dict
await author.update_from_dict({'name': 'New Name', 'is_active': False})
await author.save()

# Bulk update
await Author.filter(birth_date__lt=date(1850, 1, 1)).update(is_active=False)

Deleting Data

# Delete a single record
author = await Author.get(id=1)
await author.delete()

# Bulk delete
await Author.filter(is_active=False).delete()

Working with Model Relationships

Defining Relationships

class Genre(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=50, unique=True)
    
class Publisher(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=100)
    country = fields.CharField(max_length=50)

class Book(Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=200)
    # One‑to‑many
    author = fields.ForeignKeyField('models.Author', related_name='books')
    publisher = fields.ForeignKeyField('models.Publisher', related_name='books')
    # Many‑to‑many
    genres = fields.ManyToManyField('models.Genre', related_name='books')
    
class AuthorProfile(Model):
    id = fields.IntField(pk=True)
    website = fields.CharField(max_length=200, null=True)
    social_media = fields.JSONField(default=dict)
    # One‑to‑one
    author = fields.OneToOneField('models.Author', related_name='profile')

Working with ForeignKey

# Create with relation
author = await Author.create(name='Pushkin')
book = await Book.create(
    title='Eugene Onegin',
    author=author
)

# Access related data
book = await Book.get(id=1)
author = await book.author  # Async access

# Reverse relation
author = await Author.get(id=1)
books = await author.books.all()

Working with ManyToMany

# Create and add relations
book = await Book.create(title='New Book', author=author)
genre1 = await Genre.create(name='Novel')
genre2 = await Genre.create(name='Classic')

await book.genres.add(genre1, genre2)

# Retrieve related records
genres = await book.genres.all()

# Remove relations
await book.genres.remove(genre1)
await book.genres.clear()  # Delete all relations

Query Optimization

Select Related and Prefetch Related

# select_related for ForeignKey (JOIN)
books = await Book.all().select_related('author', 'publisher')

# prefetch_related for ManyToMany and reverse ForeignKey
authors = await Author.all().prefetch_related('books', 'books__genres')

# Combined usage
books = await Book.all().select_related('author').prefetch_related('genres')

Aggregation and Annotations

from tortoise.functions import Count, Sum, Avg

# Count books per author
authors = await Author.all().annotate(
    book_count=Count('books')
).filter(book_count__gt=0)

# Average book price per genre
genres = await Genre.all().annotate(
    avg_price=Avg('books__price')
)

Transactions and Data Integrity

Using Transactions

from tortoise.transactions import in_transaction

async def create_book_with_author():
    async with in_transaction():
        author = await Author.create(name='New Author')
        book = await Book.create(title='New Book', author=author)
        return book

# Error handling in transactions
async def safe_create():
    try:
        async with in_transaction():
            # Database operations
            pass
    except Exception as e:
        # Transaction is rolled back automatically
        print(f"Error: {e}")

Atomic Operations

from tortoise.transactions import atomic

@atomic()
async def update_book_info(book_id: int, new_title: str):
    book = await Book.get(id=book_id)
    book.title = new_title
    await book.save()
    # Operation runs atomically

Migration System with Aerich

Installing and Configuring Aerich

pip install aerich

Initializing Migrations

aerich init -t settings.TORTOISE_ORM
aerich init-db

Creating and Applying Migrations

# Create a migration
aerich migrate --name "add_new_fields"

# Apply migrations
aerich upgrade

# Roll back migrations
aerich downgrade

# View migration history
aerich history

Aerich Configuration

# settings.py
TORTOISE_ORM = {
    "connections": {"default": "sqlite://db.sqlite3"},
    "apps": {
        "models": {
            "models": ["app.models", "aerich.models"],
            "default_connection": "default",
        },
    },
}

Integration with FastAPI

Basic Setup

from fastapi import FastAPI
from tortoise.contrib.fastapi import register_tortoise

app = FastAPI()

register_tortoise(
    app,
    db_url="sqlite://./test.db",
    modules={"models": ["app.models"]},
    generate_schemas=True,
    add_exception_handlers=True,
)

Creating Pydantic Schemas

from tortoise.contrib.pydantic import pydantic_model_creator

# Generate schemas for the API
Author_Pydantic = pydantic_model_creator(Author, name="Author")
AuthorIn_Pydantic = pydantic_model_creator(
    Author, name="AuthorIn", exclude_readonly=True
)

# Use in endpoints
@app.post("/authors/", response_model=Author_Pydantic)
async def create_author(author: AuthorIn_Pydantic):
    author_obj = await Author.create(**author.dict())
    return await Author_Pydantic.from_tortoise_orm(author_obj)

Advanced Integration

from contextlib import asynccontextmanager

@asynccontextmanager
async def lifespan(app: FastAPI):
    # Startup initialization
    await Tortoise.init(
        db_url="sqlite://./test.db",
        modules={"models": ["app.models"]}
    )
    await Tortoise.generate_schemas()
    yield
    # Shutdown cleanup
    await Tortoise.close_connections()

app = FastAPI(lifespan=lifespan)

Supported Database Management Systems

PostgreSQL

# PostgreSQL configuration
TORTOISE_CONFIG = {
    "connections": {
        "default": {
            "engine": "tortoise.backends.asyncpg",
            "credentials": {
                "host": "localhost",
                "port": "5432",
                "user": "postgres",
                "password": "password",
                "database": "mydb",
                "minsize": 1,
                "maxsize": 10,
            }
        }
    }
}

MySQL

# MySQL configuration
TORTOISE_CONFIG = {
    "connections": {
        "default": {
            "engine": "tortoise.backends.mysql",
            "credentials": {
                "host": "localhost",
                "port": "3306",
                "user": "root",
                "password": "password",
                "database": "mydb",
                "minsize": 1,
                "maxsize": 10,
            }
        }
    }
}

SQLite

# SQLite configuration
TORTOISE_CONFIG = {
    "connections": {
        "default": "sqlite://./database.sqlite3"
    }
}

Testing Applications with Tortoise-ORM

Setting Up a Test Environment

import pytest
from tortoise.contrib.test import finalizer, initializer

@pytest.fixture(scope="session", autouse=True)
async def initialize_tests():
    await initializer(
        ["app.models"],
        db_url="sqlite://:memory:",
        app_label="models",
    )
    yield
    await finalizer()

# Isolated test example
@pytest.mark.asyncio
async def test_create_author():
    author = await Author.create(name="Test Author")
    assert author.name == "Test Author"
    assert author.id is not None

Using Transactions in Tests

from tortoise.transactions import in_transaction

@pytest.mark.asyncio
async def test_with_transaction():
    async with in_transaction():
        author = await Author.create(name="Test")
        book = await Book.create(title="Test Book", author=author)
        assert await Book.filter(author=author).count() == 1

Performance and Optimization

Indexes and Query Tuning

class OptimizedModel(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=100, index=True)
    email = fields.CharField(max_length=100, unique=True)
    created_at = fields.DatetimeField(auto_now_add=True, index=True)
    
    class Meta:
        table = "optimized_model"
        indexes = [
            ["name", "created_at"],  # Composite index
        ]

Bulk Operations

# Bulk create
authors = await Author.bulk_create([
    Author(name=f"Author {i}") for i in range(100)
])

# Bulk update
await Author.filter(is_active=True).update(updated_at=datetime.now())

Query Caching

from tortoise.queryset import QuerySet

class CachedQuerySet(QuerySet):
    def __init__(self, model, using_db=None):
        super().__init__(model, using_db)
        self._cache = {}
    
    async def get(self, *args, **kwargs):
        cache_key = str(kwargs)
        if cache_key in self._cache:
            return self._cache[cache_key]
        
        result = await super().get(*args, **kwargs)
        self._cache[cache_key] = result
        return result

Reference of Tortoise-ORM Methods and Functions

Main Model Methods

Filtering Operators

Field Types

Comparison with Other ORMs

Comparison Table of Popular ORMs

Best Practices and Recommendations

Project Structure

project/
├── app/
│   ├── __init__.py
│   ├── models/
│   │   ├── __init__.py
│   │   ├── author.py
│   │   ├── book.py
│   │   └── base.py
│   ├── schemas/
│   │   ├── __init__.py
│   │   ├── author.py
│   │   └── book.py
│   ├── services/
│   │   ├── __init__.py
│   │   ├── author_service.py
│   │   └── book_service.py
│   └── config.py
├── migrations/
├── tests/
└── main.py

Using a Service Layer

# services/author_service.py
class AuthorService:
    @staticmethod
    async def create_author(name: str, birth_date: date = None) -> Author:
        return await Author.create(name=name, birth_date=birth_date)
    
    @staticmethod
    async def get_author_with_books(author_id: int) -> Author:
        return await Author.get(id=author_id).prefetch_related('books')
    
    @staticmethod
    async def get_popular_authors(min_books: int = 5) -> List[Author]:
        return await Author.all().annotate(
            book_count=Count('books')
        ).filter(book_count__gte=min_books)

Error Handling

from tortoise.exceptions import DoesNotExist, IntegrityError

async def safe_get_author(author_id: int) -> Optional[Author]:
    try:
        return await Author.get(id=author_id)
    except DoesNotExist:
        return None
    except IntegrityError as e:
        logger.error(f"Integrity error: {e}")
        raise

Frequently Asked Questions

What is Tortoise-ORM and how does it differ from other ORMs?

Tortoise-ORM is an asynchronous ORM library for Python, built specifically for async/await patterns. Its main distinction from traditional ORMs is native async support, making it ideal for modern web apps built with FastAPI, Starlette, and other ASGI frameworks.

Does Tortoise-ORM support all major database management systems?

Yes. Tortoise-ORM works with PostgreSQL, MySQL, and SQLite via the respective async drivers (asyncpg, aiomysql, aiosqlite). Each DBMS has specific features, but the core API remains consistent.

How are migrations handled in Tortoise-ORM?

Tortoise-ORM does not include a built‑in migration system, but it integrates tightly with the Aerich tool. Aerich enables creating, applying, and rolling back migrations, as well as tracking schema changes.

Can Tortoise-ORM be used in production?

Absolutely. Tortoise-ORM is used in production environments, is stable, well‑tested, and delivers high performance. Proper connection pooling and query optimizations are essential for production readiness.

How do I integrate Tortoise-ORM with Pydantic in FastAPI?

Tortoise-ORM provides built‑in support for generating Pydantic schemas via pydantic_model_creator. This automatically creates validation schemas for your API based on the database models.

Does Tortoise-ORM support transactions?

Yes. Tortoise-ORM fully supports transactions through the in_transaction() context manager and the @atomic() decorator, ensuring data integrity for grouped operations.

How can I optimize query performance?

Use select_related() for foreign‑key relationships, prefetch_related() for many‑to‑many and reverse foreign keys, create indexes on frequently queried fields, and leverage bulk operations for mass updates.

Is Tortoise-ORM compatible with frameworks other than FastAPI?

Yes. Tortoise-ORM works with any ASGI framework, including Starlette, Quart, Sanic, and others. The key requirement is asynchronous support at the framework level.

Useful Resources and Documentation

Official Resources

• Official Documentation: https://tortoise-orm.readthedocs.io/
• GitHub Repository: //https://github.com/tortoise/tortoise-orm
• PyPI Page: https://pypi.org/project/tortoise-orm/

Additional Tools

• Aerich (migrations): https://github.com/tortoise/aerich
• Tortoise‑CLI (command‑line utilities): https://github.com/tortoise/tortoise-cli
• FastAPI Integration: https://github.com/tortoise/fastapi-tortoise

Community and Support

• Discord Server: Active developer community
• GitHub Issues: Bug tracking and feature requests
• Stack Overflow: Tag tortoise-orm for questions

Tortoise-ORM is a powerful, modern solution for asynchronous database work in Python. With its simple API, high performance, and excellent integration with contemporary frameworks, it is an ideal choice for building scalable web applications and APIs.