Tortoise ORM

Tortoise ORM provides two primary ways to manage database transactions: the in_transaction() context manager and the atomic() decorator.

Both methods support nesting. When nested, inner blocks create transaction savepoints. If an exception is raised within a nested block and caught outside of it, the transaction rolls back to the state before that specific nested block was entered (the savepoint). The outermost block is responsible for the final commit.

Database Support for Savepoints:

• Postgres
• MySQL
• MSSQL
• SQLite

Note: For databases not listed above, it is recommended to propagate exceptions to the outermost block to ensure a full rollback.

# this block will commit changes on exit
async with in_transaction():
    await MyModel.create(name='foo')
    try:
        # this block will create a savepoint and rollback to it if an exception is raised
        async with in_transaction():
            await MyModel.create(name='bar')
            # this will rollback to the savepoint, meaning that
            # the 'bar' record will not be created, however,
            # the 'foo' record will be created
            raise Exception()
    except Exception:
        pass

You can enhance your debug logs by using pygments to add syntax highlighting to the SQL queries emitted by tortoise.db_client. This requires implementing a custom logging.Formatter that uses a lexer (like PostgresLexer) and a terminal formatter to highlight the message attribute when the logger name is tortoise.db_client and the level is DEBUG.

import logging
from pygments import highlight
from pygments.formatters.terminal import TerminalFormatter
from pygments.lexers.sql import PostgresLexer

postgres = PostgresLexer()
terminal_formatter = TerminalFormatter()

class PygmentsFormatter(logging.Formatter):
    def __init__(
        self,
        fmt="{asctime} - {name}:{lineno} - {levelname} - {message}",
        datefmt="%H:%M:%S",
    ):
        self.datefmt = datefmt
        self.fmt = fmt
        logging.Formatter.__init__(self, None, datefmt)

    def format(self, record: logging.LogRecord):
        """Format the logging record with slq's syntax coloration."""
        own_records = {
            attr: val
            for attr, val in record.__dict__.items()
            if not attr.startswith("_")
        }
        message = record.getMessage()
        name = record.name
        asctime = self.formatTime(record, self.datefmt)

        if name == "tortoise.db_client":
            if (
                record.levelname == "DEBUG"
                and not message.startswith("Created connection pool")
                and not message.startswith("Closed connection pool")
            ):
                message = highlight(message, postgres, terminal_formatter).rstrip()

        own_records.update(
            {
                "message": message,
                "name": name,
                "asctime": asctime,
            }
        )

        return self.fmt.format(**own_records)

# Usage example:
fmt = PygmentsFormatter(
    fmt="{asctime} - {name}:{lineno} - {levelname} - {message}",
    datefmt="%Y-%m-%d %H:%M:%S",
)