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: Mapping[str, PostgresEngine] | None = 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")