Databases

Tortoise currently supports the following databases:

  • SQLite

  • PostgreSQL >= 9.4 (using asyncpg )

  • MySQL/MariaDB (using aiomysql )

To use, please ensure that asyncpg and/or aiomysql 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 :

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

mysql :

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

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 ( str ) – Dialect name of the DB Client driver.

  • daemon ( bool ) – Is the DB an external Daemon we connect to?

  • requires_limit ( bool ) – Indicates that this DB requires a LIMIT statement for an OFFSET statement to work.

  • inline_comment ( bool ) – Indicates that comments should be rendered in line with the DDL statement, and not as a separate statement.

  • supports_transactions ( bool ) – Indicates that this DB supports transactions.

  • support_for_update ( bool ) – Indicates that this DB supports SELECT … FOR UPDATE SQL statement.

  • support_index_hint ( bool ) – Support force index or use index.

  • support_update_limit_order_by ( bool ) – 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.

Similarily 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 Passing in custom SSL Certificates for more info.

In case any of user , password , host , port parameters is missing, we are letting asyncpg 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:pass@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)

no_delay (defaults to None ):

Set to True to set TCP NO_DELAY to disable Nagle’s algorithm on the socket.

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 Passing in custom SSL Certificates for more info.

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. Loot 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 , PoolConnectionWrapper ]

async close ( ) [source]

Closes the DB connection.

Return type

None

async create_connection ( with_db ) [source]

Establish a DB connection.

Parameters

with_db ( bool ) – 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 ( str ) – The SQL string, pre-parametrized for the target DB dialect.

  • values ( list ) – 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 ( str ) – The SQL string, pre-parametrized for the target DB dialect.

  • values ( List [ list ]) – 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 ( str ) – The SQL string, pre-parametrized for the target DB dialect.

  • values ( Optional [ list ]) – 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 ( str ) – The SQL string, pre-parametrized for the target DB dialect.

  • values ( Optional [ list ]) – 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 ( str ) – The SQL string, which will be passed on verbatim. Semicolons is supported here.

Return type

None