PostgresEngine

Configuration

# piccolo_conf.py
from piccolo.engine.postgres import PostgresEngine


DB = PostgresEngine(config={
    'host': 'localhost',
    'database': 'my_app',
    'user': 'postgres',
    'password': ''
})

config

The config dictionary is passed directly to the underlying database adapter, asyncpg. See the asyncpg docs to learn more.

Connection pool

To use a connection pool, you need to first initialise it. The best place to do this is in the startup event handler of whichever web framework you are using.

Here’s an example using Starlette. Notice that we also close the connection pool in the shutdown event handler.

from piccolo.engine import engine_finder
from starlette.applications import Starlette


app = Starlette()


@app.on_event('startup')
async def open_database_connection_pool():
    engine = engine_finder()
    await engine.start_connection_pool()


@app.on_event('shutdown')
async def close_database_connection_pool():
    engine = engine_finder()
    await engine.close_connection_pool()

Hint

Using a connection pool helps with performance, since connections are reused instead of being created for each query.

Once a connection pool has been started, the engine will use it for making queries.

Hint

If you’re running several instances of an app on the same server, you may prefer an external connection pooler - like pgbouncer.

Configuration

The connection pool uses the same configuration as your engine. You can also pass in additional parameters, which are passed to the underlying database adapter. Here’s an example:

# To increase the number of connections available:
await engine.start_connection_pool(max_size=20)

Source

class piccolo.engine.postgres.PostgresEngine(config: Dict[str, Any], extensions: Sequence[str] = ('uuid-ossp',), log_queries: bool = False, log_responses: bool = False, extra_nodes: Optional[Mapping[str, PostgresEngine]] = None)

Used to connect to PostgreSQL.

Parameters:

  • config –

    The config dictionary is passed to the underlying database adapter, asyncpg. Common arguments you’re likely to need are:

    • host

    • port

    • user

    • password

    • database

    For example, {'host': 'localhost', 'port': 5432}.

    See the asyncpg docs for all available options.

  • extensions – When the engine starts, it will try and create these extensions in Postgres. If you’re using a read only database, set this value to an empty tuple ().

  • log_queries – If True, all SQL and DDL statements are printed out before being run. Useful for debugging.

  • log_responses – If True, the raw response from each query is printed out. Useful for debugging.

  • extra_nodes –

    If you have additional database nodes (e.g. read replicas) for the server, you can specify them here. It’s a mapping of a memorable name to a PostgresEngine instance. For example:

    DB = PostgresEngine(
    config={'database': 'main_db'},
    extra_nodes={
        'read_replica_1': PostgresEngine(
            config={
                'database': 'main_db',
                host: 'read_replicate.my_db.com'
            },
            extensions=()
        )
    }
)

    Note how we set extensions=(), because it’s a read only database.

    When executing a query, you can specify one of these nodes instead of the main database. For example:

    >>> await MyTable.select().run(node="read_replica_1")