Database connections (connections.py)

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

Hide arguments

dict[str, ConnectionProperties | Any]

required

A dictionary to populate with your database connections. The keys are connection names (strings) that you’ll reference in your data models, and the values are ConnectionProperties objects or any other connection-like object.This dictionary is pre-populated with any connections defined in squirrels.yml before the main function is called.

ConnectionsArgs

required

A ConnectionsArgs object providing access to project configuration and environment variables.

The ConnectionsArgs object

The sqrl argument is a ConnectionsArgs object that provides useful properties for building connection strings dynamically.

Property	Type	Description
`project_path`	`str`	Absolute path to the Squirrels project directory
`proj_vars`	`dict[str, Any]`	Project variables from `squirrels.yml`
`env_vars`	`dict[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:

Type	Description	Placeholder support
`SQLALCHEMY`	Standard SQLAlchemy connections (default)	Yes (`:param_name`)
`DUCKDB`	Native DuckDB connections	Yes (`$param_name`)
`CONNECTORX`	High-performance bulk data loading	No
`ADBC`	Arrow Database Connectivity	No

For details on URI formats for each connection type, see URI formats.

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="sqlite:////absolute/path/to/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="/absolute/path/to/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="sqlite:////absolute/path/to/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}/resources/ml_model.joblib"
    model = joblib.load(model_path)
    connections["ml_model"] = model

ConnectionsArgs - Properties available in the sqrl object argument
ConnectionProperties - Full reference for connection properties
ConnectionTypeEnum - Available connection types for ConnectionProperties
squirrels.yml connections - YAML-based connection configuration

Get started

Concepts

Project files

Database connections (connections.py)

File structure

The ConnectionsArgs object

Creating connections

Connection types

Examples

SQLite connection

PostgreSQL connection

DuckDB connection

Multiple connections with environment-based logic

High-performance connection with ConnectorX

Custom connection objects

​File structure

​The ConnectionsArgs object

​Creating connections

​Connection types

​Examples

​SQLite connection

​PostgreSQL connection

​DuckDB connection

​Multiple connections with environment-based logic

​High-performance connection with ConnectorX

​Custom connection objects

​Related pages

File structure

The ConnectionsArgs object

Creating connections

Connection types

Examples

SQLite connection

PostgreSQL connection

DuckDB connection

Multiple connections with environment-based logic

High-performance connection with ConnectorX

Custom connection objects

Related pages