Skip to main content
The pyconfigs/connections.py file allows you to define database connections for your Squirrels project using Python. This provides greater flexibility than the YAML-based configuration in squirrels.yml, enabling dynamic connection strings, conditional logic, and access to resource files if needed.
Connections can also be defined in the squirrels.yml file. Connections defined in squirrels.yml are loaded first, then connections.py runs (if exists) and can add or override connections.
Always define the default connection name for data models or data source parameters that do not specify a connection name explicitly.The default connection name is default unless set differently by the environment variable SQRL_CONNECTIONS__DEFAULT_NAME_USED.

​
File structure

The connections.py file must define a main function with the following signature:
pyconfigs/connections.py
from typing import Any
from squirrels import arguments as args, connections as cn


def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    """
    Define database connections by adding them to the "connections" dictionary
    """
    pass

​
The ConnectionsArgs object

The sqrl argument is a ConnectionsArgs object that provides useful properties for building connection strings dynamically.
PropertyTypeDescription
project_pathstrAbsolute path to the Squirrels project directory
proj_varsdict[str, Any]Project variables from squirrels.yml
env_varsdict[str, str]Environment variables from .env files and system

​
Creating connections

Add connections by assigning ConnectionProperties objects to the connections dictionary:
pyconfigs/connections.py
from typing import Any
from squirrels import arguments as args, connections as cn


def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    # Get connection string from environment variable
    conn_str = sqrl.env_vars["DATABASE_URL"]
    
    # Create a connection with a unique name
    connections["default"] = cn.ConnectionProperties(
        label="Main Database",
        type=cn.ConnectionTypeEnum.SQLALCHEMY,
        uri=conn_str
    )
You can also store other values in the connections dictionary that are not ConnectionProperties objects, as long as the key is not used as the database connection name for data models or data source parameters.This can be useful for storing artifacts (such as ML models or API clients) in memory at server startup time. These can then be accessed in the context.py file or Python data models.

​
Connection types

Squirrels supports four connection types via ConnectionTypeEnum:
TypeDescriptionPlaceholder support
SQLALCHEMYStandard SQLAlchemy connections (default)Yes (:param_name)
DUCKDBNative DuckDB connectionsYes ($param_name)
CONNECTORXHigh-performance bulk data loadingNo
ADBCArrow Database ConnectivityNo

​
Examples

​
SQLite connection

pyconfigs/connections.py
def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    connections["sqlite_db"] = cn.ConnectionProperties(
        label="SQLite Database",
        type=cn.ConnectionTypeEnum.SQLALCHEMY,
        uri=f"sqlite:///{sqrl.project_path}/assets/database.db"
    )

​
PostgreSQL connection

pyconfigs/connections.py
def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    connections["postgres"] = cn.ConnectionProperties(
        label="Production Database",
        type=cn.ConnectionTypeEnum.SQLALCHEMY,
        uri=sqrl.env_vars["POSTGRES_URL"],
        sa_create_engine_args={
            "pool_size": 10,
            "pool_pre_ping": True
        }
    )

​
DuckDB connection

pyconfigs/connections.py
def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    # Connect to a DuckDB file
    connections["analytics"] = cn.ConnectionProperties(
        label="Analytics DuckDB",
        type=cn.ConnectionTypeEnum.DUCKDB,
        uri=f"{sqrl.project_path}/assets/analytics.duckdb"
    )
    
    # Connect to PostgreSQL through DuckDB
    connections["pg_via_duckdb"] = cn.ConnectionProperties(
        label="PostgreSQL via DuckDB",
        type=cn.ConnectionTypeEnum.DUCKDB,
        uri="postgres:dbname=mydb user=user password=pass host=localhost"
    )

​
Multiple connections with environment-based logic

pyconfigs/connections.py
def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    env = sqrl.env_vars.get("ENVIRONMENT", "development")
    
    if env == "production":
        connections["main"] = cn.ConnectionProperties(
            label="Production Database",
            type=cn.ConnectionTypeEnum.SQLALCHEMY,
            uri=sqrl.env_vars["PROD_DATABASE_URL"]
        )
    else:
        connections["main"] = cn.ConnectionProperties(
            label="Development Database",
            type=cn.ConnectionTypeEnum.SQLALCHEMY,
            uri=f"sqlite:///{sqrl.project_path}/assets/dev.db"
        )

​
High-performance connection with ConnectorX

pyconfigs/connections.py
def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    # ConnectorX for fast bulk data loading (no placeholder support)
    connections["bulk_loader"] = cn.ConnectionProperties(
        label="Bulk Data Loader",
        type=cn.ConnectionTypeEnum.CONNECTORX,
        uri="postgresql://user:pass@localhost:5432/warehouse"
    )

​
Custom connection objects

You can also add custom connection objects (such as pre-configured database clients) to the dictionary:
pyconfigs/connections.py
import joblib

def main(connections: dict[str, cn.ConnectionProperties | Any], sqrl: args.ConnectionsArgs) -> None:
    # Load a pre-trained scikit-learn model from file
    model_path = f"{sqrl.project_path}/assets/ml_model.joblib"
    model = joblib.load(model_path)
    connections["ml_model"] = model