Databases

Tortoise currently supports the following databases:

  • SQLite (using aiosqlite）

  • PostgreSQL >= 9.4 (using asyncpg or psycopg)

  • MySQL/MariaDB (using asyncmy or aiomysql)

  • Microsoft SQL Server (using asyncodbc)

To use, please ensure that corresponding asyncio driver is installed.

DB_URL

Tortoise supports specifying Database configuration in a URL form.

The form is:

DB_TYPE://USERNAME:PASSWORD@HOST:PORT/DB_NAME?PARAM1=value&PARAM2=value

If password contains special characters it need to be URL encoded:

>>> import urllib.parse
>>> urllib.parse.quote_plus("kx%jj5/g")
'kx%25jj5%2Fg'

The supported DB_TYPE:

sqlite:

Typically in the form of sqlite://DB_FILE So if the DB_FILE is “/data/db.sqlite3” then the string will be sqlite:///data/db.sqlite (note the three /’s)

postgres

Using asyncpg: Typically in the form of postgres://postgres:pass@db.host:5432/somedb

Or specifically asyncpg/psycopg using:

  • psycopg: psycopg://postgres:pass@db.host:5432/somedb

  • asyncpg: asyncpg://postgres:pass@db.host:5432/somedb

mysql:

Typically in the form of mysql://myuser:mypass@db.host:3306/somedb

mssql:

Typically in the form of mssql://myuser:mypass@db.host:1433/somedb?driver=the odbc driver

Capabilities

Since each database has a different set of features we have a Capabilities that is registered on each client. Primarily this is to work around larger-than SQL differences, or common issues.

class tortoise.backends.base.client.Capabilities(dialect, *, daemon=True, requires_limit=False, inline_comment=False, supports_transactions=True, support_for_update=True, support_index_hint=False, support_update_limit_order_by=True)[source]

DB Client Capabilities indicates the supported feature-set, and is also used to note common workarounds to deficiencies.

Defaults are set with the following standard:

  • Deficiencies: assume it is working right.

  • Features: assume it doesn’t have it.

Parameters:
dialect

Dialect name of the DB Client driver.

daemon=True

Is the DB an external Daemon we connect to?

requires_limit=False

Indicates that this DB requires a LIMIT statement for an OFFSET statement to work.

inline_comment=False

Indicates that comments should be rendered in line with the DDL statement, and not as a separate statement.

supports_transactions=True

Indicates that this DB supports transactions.

support_for_update=True

Indicates that this DB supports SELECT … FOR UPDATE SQL statement.

support_index_hint=False

Support force index or use index.

support_update_limit_order_by=True

support update/delete with limit and order by.

SQLite

SQLite is an embedded database, and can run on a file or in-memory. Good database for local development or testing of code logic, but not recommended for production use.

Caution

SQLite doesn’t support many of the common datatypes natively, although we do emulation where we can, not everything is perfect.

For example DecimalField has precision preserved by storing values as strings, except when doing aggregates/ordering on it. In those cases we have to cast to/from floating-point numbers.

Similarly case-insensitivity is only partially implemented.

DB URL is typically in the form of sqlite://DB_FILE So if the DB_FILE is “/data/db.sqlite3” then the string will be sqlite:///data/db.sqlite (note the three /’s)

Required Parameters

path:

Path to SQLite3 file. :memory: is a special path that indicates in-memory database.

Optional parameters:

SQLite optional parameters is basically any of the PRAGMA statements documented here.

journal_mode (defaults to WAL):

Specify SQLite journal mode.

journal_size_limit (defaults to 16384):

The journal size.

foreign_keys (defaults to ON)

Set to OFF to not enforce referential integrity.

PostgreSQL

DB URL is typically in the form of postgres://postgres:pass@db.host:5432/somedb, or, if connecting via Unix domain socket postgres:///somedb.

Required Parameters

user:

Username to connect with.

password:

Password for username.

host:

Network host that database is available at.

port:

Network port that database is available at. (defaults to 5432)

database:

Database to use.

Optional parameters:

PostgreSQL optional parameters are pass-though parameters to the driver, see here for more details.

minsize (defaults to 1):

Minimum connection pool size

maxsize (defaults to 5):

Maximum connection pool size

max_queries (defaults to 50000):

Maximum no of queries before a connection is closed and replaced.

max_inactive_connection_lifetime (defaults to 300.0):

Duration of inactive connection before assuming that it has gone stale, and force a re-connect.

schema (uses user’s default schema by default):

A specific schema to use by default.

ssl (defaults to ‘’False``):

Either True or a custom SSL context for self-signed certificates. See MSSQL/Oracle for more info.

In case any of user, password, host, port parameters is missing, we are letting asyncpg/psycopg retrieve it from default sources (standard PostgreSQL environment variables or default values).

MySQL/MariaDB

DB URL is typically in the form of mysql://myuser:mypass@db.host:3306/somedb

Required Parameters

user:

Username to connect with.

password:

Password for username.

host:

Network host that database is available at.

port:

Network port that database is available at. (defaults to 3306)

database:

Database to use.

Optional parameters:

MySQL optional parameters are pass-though parameters to the driver, see here for more details.

minsize (defaults to 1):

Minimum connection pool size

maxsize (defaults to 5):

Maximum connection pool size

connect_timeout (defaults to None):

Duration to wait for connection before throwing error.

echo (defaults to False):

Set to True` to echo SQL queries (debug only)

charset (defaults to utf8mb4):

Sets the character set in use

ssl (defaults to False):

Either True or a custom SSL context for self-signed certificates. See MSSQL/Oracle for more info.

MSSQL/Oracle

DB URL is typically in the form of mssql or oracle://myuser:mypass@db.host:1433/somedb?driver=the odbc driver

Required Parameters

user:

Username to connect with.

password:

Password for username.

host:

Network host that database is available at.

port:

Network port that database is available at. (defaults to 1433)

database:

Database to use.

driver:

The ODBC driver to use. Actual name of the ODBC driver in your odbcinst.ini file (you can find it’s location using odbcinst -j command). It requires unixodbc to be installed in your system.

Optional parameters:

MSSQL/Oracle optional parameters are pass-though parameters to the driver, see here for more details.

minsize (defaults to 1):

Minimum connection pool size

maxsize (defaults to 10):

Maximum connection pool size

pool_recycle (defaults to -1):

Pool recycle timeout in seconds.

echo (defaults to False):

Set to True to echo SQL queries (debug only)

Encoding in Oracle:

If you get ??? values in Varchar fields instead of your actual text (russian/chinese/etc), then set NLS_LANG variable in your client environment to support UTF8. For example, “American_America.UTF8”.

Passing in custom SSL Certificates

To pass in a custom SSL Cert, one has to use the verbose init structure as the URL parser can’t handle complex objects.

# Here we create a custom SSL context
import ssl
ctx = ssl.create_default_context()
# And in this example we disable validation...
# Please don't do this. Look at the official Python ``ssl`` module documentation
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

# Here we do a verbose init
await Tortoise.init(
    config={
        "connections": {
            "default": {
                "engine": "tortoise.backends.asyncpg",
                "credentials": {
                    "database": None,
                    "host": "127.0.0.1",
                    "password": "moo",
                    "port": 54321,
                    "user": "postgres",
                    "ssl": ctx  # Here we pass in the SSL context
                }
            }
        },
        "apps": {
            "models": {
                "models": ["some.models"],
                "default_connection": "default",
            }
        },
    }
)

Base DB client

The Base DB client interface is provided here, but should only be directly used as an advanced case.

class tortoise.backends.base.client.BaseDBAsyncClient(connection_name, fetch_inserted=True, **kwargs)[source]

Base class for containing a DB connection.

Parameters get passed as kwargs, and is mostly driver specific.

query_class Type[pypika.Query]

The PyPika Query dialect (low level dialect)

executor_class Type[BaseExecutor]

The executor dialect class (high level dialect)

schema_generator Type[BaseSchemaGenerator]

The DDL schema generator

capabilities Capabilities

Contains the connection capabilities

acquire_connection()[source]

Acquires a connection from the pool. Will return the current context connection if already in a transaction.

Return type:

Union[ConnectionWrapper[~T_conn], PoolConnectionWrapper[~T_conn]]

async close()[source]

Closes the DB connection.

Return type:

None

async create_connection(with_db)[source]

Establish a DB connection.

Parameters:
with_db

If True, then select the DB to use, else use default. Use case for this is to create/drop a database.

Return type:

None

async db_create()[source]

Created the database in the server. Typically only called by the test runner.

Need to have called create_connection()` with parameter with_db=False set to use the default connection instead of the configured one, else you would get errors indicating the database doesn’t exist.

Return type:

None

async db_delete()[source]

Delete the database from the Server. Typically only called by the test runner.

Need to have called create_connection()` with parameter with_db=False set to use the default connection instead of the configured one, else you would get errors indicating the database is in use.

Return type:

None

async execute_insert(query, values)[source]

Executes a RAW SQL insert statement, with provided parameters.

Parameters:
query

The SQL string, pre-parametrized for the target DB dialect.

values

A sequence of positional DB parameters.

Return type:

Any

Returns:

The primary key if it is generated by the DB. (Currently only integer autonumber PK’s)

async execute_many(query, values)[source]

Executes a RAW bulk insert statement, like execute_insert, but returns no data.

Parameters:
query

The SQL string, pre-parametrized for the target DB dialect.

values

A sequence of positional DB parameters.

Return type:

None

async execute_query(query, values=None)[source]

Executes a RAW SQL query statement, and returns the resultset.

Parameters:
query

The SQL string, pre-parametrized for the target DB dialect.

values=None

A sequence of positional DB parameters.

Return type:

Tuple[int, Sequence[dict]]

Returns:

A tuple of: (The number of rows affected, The resultset)

async execute_query_dict(query, values=None)[source]

Executes a RAW SQL query statement, and returns the resultset as a list of dicts.

Parameters:
query

The SQL string, pre-parametrized for the target DB dialect.

values=None

A sequence of positional DB parameters.

Return type:

List[dict]

async execute_script(query)[source]

Executes a RAW SQL script with multiple statements, and returns nothing.

Parameters:
query

The SQL string, which will be passed on verbatim. Semicolons is supported here.

Return type:

None