Skip to content

SQL engines#

Module: restalchemy.storage.sql.engines

This module contains the engine factory and concrete SQL engine implementations for MySQL and PostgreSQL.

AbstractEngine#

AbstractEngine defines the common behavior for all SQL engines:

  • Parses the database URL.
  • Exposes database name, host, port, username and password.
  • Holds the SQL dialect (mysql.MySQLDialect or pgsql.PgSQLDialect).
  • Provides a session_manager() context manager.

Key properties and methods:

  • URL_SCHEMA (abstract): expected URL schema, e.g. "mysql", "postgresql".
  • DEFAULT_PORT (abstract): port used if not specified in URL.
  • db_name, db_username, db_password, db_host, db_port.
  • dialect: the SQL dialect object.
  • query_cache: whether session-level query cache is enabled.
  • get_connection(): obtain a connection (implemented by subclasses).
  • get_session(): obtain a session object (implemented by subclasses).
  • session_manager(session=None): context manager that manages commit/rollback and closing the session.
  • get_session_storage(): returns the session storage (SessionThreadStorage).

Example:

from restalchemy.storage.sql import engines

engine = engines.engine_factory.get_engine()
print(engine.db_name)

PostgreSQL engine#

PgSQLEngine#

  • URL_SCHEMA = "postgresql".
  • DEFAULT_PORT is taken from restalchemy.common.constants.RA_POSTGRESQL_DB_PORT.
  • Uses psycopg_pool.ConnectionPool for connections.
  • Dialect: pgsql.PgSQLDialect().
  • Session type: sessions.PgSQLSession.

Constructor:

PgSQLEngine(db_url, config=None, query_cache=False)
  • db_url: PostgreSQL connection URL.
  • config: passed to psycopg_pool.ConnectionPool.
  • query_cache: enables query caching.

Methods:

  • get_session(): returns PgSQLSession(engine=self).
  • get_connection(): gets a connection from the pool.
  • close_connection(conn): returns the connection back to the pool.

The engine is created internally by EngineFactory.

Connection timeouts#

register_postgresql_db_opts() registers connection, server, and TCP timeout options. Durations are configured in seconds. An omitted option keeps the corresponding libpq, PostgreSQL, or operating-system setting. An explicit 0 is passed to the driver; for PostgreSQL server timeouts, it disables the timeout.

  • connection_connect_timeout: time allowed to establish a connection.
  • connection_statement_timeout: maximum statement execution time.
  • connection_transaction_timeout: maximum transaction duration; requires PostgreSQL 17 or newer.
  • connection_idle_in_transaction_session_timeout: maximum time a session may remain idle in a transaction.
  • connection_tcp_user_timeout: maximum time transmitted data may remain unacknowledged.
  • connection_keepalives_idle, connection_keepalives_interval, and connection_keepalives_count: TCP keepalive detection parameters.

For example, the following configuration keeps statement and idle-transaction waits below four minutes and bounds transactions and unacknowledged TCP data at five minutes:

[db]
connection_connect_timeout = 30
connection_statement_timeout = 240
connection_transaction_timeout = 300
connection_idle_in_transaction_session_timeout = 240
connection_tcp_user_timeout = 300
connection_keepalives_idle = 60
connection_keepalives_interval = 30
connection_keepalives_count = 5

MySQL engine#

MySQLEngine#

  • URL_SCHEMA = "mysql".
  • DEFAULT_PORT is taken from RA_MYSQL_DB_PORT.
  • Uses mysql.connector.pooling.MySQLConnectionPool.
  • Dialect: mysql.MySQLDialect().
  • Session type: sessions.MySQLSession.

Constructor:

MySQLEngine(db_url, config=None, query_cache=False)
  • db_url: MySQL connection URL.
  • config: pool configuration.
  • query_cache: enables query caching.

Methods:

  • get_connection(): returns a connection from the pool.
  • get_session(): returns MySQLSession(engine=self).

EngineFactory and engine_factory#

EngineFactory#

A singleton responsible for configuring and storing engine instances.

Important methods:

  • configure_factory(db_url, config=None, query_cache=False, name="default")
  • Creates an engine instance based on db_url and stores it under name.
  • Infers engine class from URL schema ("mysql", "postgresql").
  • configure_postgresql_factory(conf, section, name)
  • Helper for configuring PostgreSQL from a config object.
  • configure_mysql_factory(conf, section, name)
  • Helper for configuring MySQL from a config object.
  • get_engine(name="default")
  • Returns the configured engine instance.
  • destroy_engine(name="default") / destroy_all_engines()
  • Remove engines from the factory.

At module level:

engine_factory = EngineFactory()

Most applications use this singleton:

from restalchemy.storage.sql import engines

engines.engine_factory.configure_factory(db_url="mysql://...")
engine = engines.engine_factory.get_engine()

DBConnectionUrl#

DBConnectionUrl is a small helper that parses a DB URL and provides a censored string representation.

  • Stores the parsed URL.
  • url property returns the full URL string.
  • __repr__ hides the password by replacing it with :<censored>@.

This is mainly useful for logging and debugging.