Profiles

Profiles in xorq provide a secure, portable way to manage database connection parameters. They encapsulate all the information needed to establish connections to various database backends, while ensuring sensitive information remains protected.

Profiles solve several key challenges:

Security: Store sensitive credentials as environment variable references rather than plain text
Portability: Easily share connection configurations across environments and team members
Persistence: Save and load connection settings without hardcoding credentials
Consistency: Maintain uniform connection parameters across application restarts
Expression Serialization: Enable serialized expressions to be stored and executed later by preserving the connection context needed to execute them

The Profile system consists of two main classes:

Profile: Individual connection specifications that can be created manually, from existing connections, saved to disk, and loaded later
Profiles: A collection manager that provides convenient access to saved profiles

Profiles maintain environment variable references throughout their lifecycle, so sensitive information like passwords and access tokens remain secured. When a connection is created from a profile, environment variables are resolved at that moment, but the profile itself never stores the actual sensitive values.

This approach allows you to securely share database connection configurations in code repositories, documentation, and across team members without exposing credentials. Profiles can be used anywhere you need database connectivity in your xorq applications, from interactive data exploration to production data pipelines.

Working with xorq Profiles: An Interactive Tutorial

Profiles in xorq provide a secure way to manage database connections. This tutorial demonstrates how to create, save, load, and use profiles while preserving environment variable references for security.

Setup

Let’s start by importing the necessary modules and setting up our environment variables:

import os
from xorq.vendor.ibis.backends.profiles import Profile, Profiles

First, we’ll set up the environment variables for our database connection:

# Set environment variables for a PostgreSQL database
os.environ["POSTGRES_DATABASE"] = "letsql"
os.environ["POSTGRES_HOST"] = "examples.letsql.com"
os.environ["POSTGRES_USER"] = "letsql"
os.environ["POSTGRES_PASSWORD"] = "letsql"
os.environ["POSTGRES_PORT"] = "5432"

Creating a Profile

Now, let’s create a profile using environment variable references instead of hardcoded values:

profile = Profile(
    con_name="postgres",
    kwargs_tuple=(
        ("host", "${POSTGRES_HOST}"),
        ("port", 5432),
        ("database", "postgres"),
        ("user", "${POSTGRES_USER}"),
        ("password", "${POSTGRES_PASSWORD}"),
    ),
)

print(profile)

Output:

Profile(
    con_name='postgres', 
    kwargs_tuple=(
        ('database', 'postgres'), 
        ('host', '${POSTGRES_HOST}'), 
        ('password', '${POSTGRES_PASSWORD}'), 
        ('port', 5432), 
        ('user', '${POSTGRES_USER}')
    ), 
    idx=0
)

Notice that the profile contains references to environment variables, not their actual values. This keeps sensitive information like passwords secure.

Saving a Profile

Let’s save our profile with an alias for easy reference later:

path = profile.save(alias="postgres_example", clobber=True)
print(f"Profile saved to: {path}")

Output:

Profile saved to: 
/home/user/.config/xorq/profiles/postgres_example.yaml

The clobber=True parameter tells xorq to overwrite any existing profile with the same alias.

Loading a Profile

We can load the profile we just saved using its alias:

loaded_profile = Profile.load("postgres_example")
print(loaded_profile)

Output:

Profile(
    con_name='postgres', 
    kwargs_tuple=(
        ('database', 'postgres'), 
        ('host', '${POSTGRES_HOST}'), 
        ('password', '${POSTGRES_PASSWORD}'), 
        ('port', 5432), 
        ('user', '${POSTGRES_USER}')
    ), 
    idx=0
)

The loaded profile still contains environment variable references, keeping credentials secure.

Creating a Connection from a Profile

Now, let’s use the profile to create a database connection:

connection = loaded_profile.get_con()
print("Connection successful!")

Output:

Connection successful!

At this point, the environment variables are resolved, but only within the connection object—not in the profile itself.

Testing the Connection

Let’s verify the connection works by listing some tables:

tables = connection.list_tables()
print(f"Found tables: {tables[:5]}")

Output:

Found tables: []

Our example database doesn’t have any tables yet, but the connection is working!

Examining the Connection’s Profile

Even though the connection is using the resolved values of our environment variables, let’s verify that the connection’s profile still maintains environment variable references:

conn_profile = connection._profile
print(conn_profile)

Output:

Profile(
    con_name='postgres', 
    kwargs_tuple=(
        ('database', 'postgres'), 
        ('host', '${POSTGRES_HOST}'), 
        ('password', '${POSTGRES_PASSWORD}'), 
        ('port', 5432), 
        ('schema', None), 
        ('user', '${POSTGRES_USER}')
    ), 
    idx=2
)

Notice that sensitive credentials are still stored as environment variable references.

Creating a Profile from a Connection

We can also go the other way, creating a profile from an existing connection:

from_conn_profile = Profile.from_con(connection)
print(from_conn_profile)

Output:

Profile(
    con_name='postgres', 
    kwargs_tuple=(
        ('database', 'postgres'), 
        ('host', '${POSTGRES_HOST}'), 
        ('password', '${POSTGRES_PASSWORD}'), 
        ('port', 5432), 
        ('schema', None), 
        ('user', '${POSTGRES_USER}')
    ), 
    idx=2
)

The profile extracted from the connection still keeps the environment variable references.

Saving a Profile from a Connection

Now let’s save this connection-derived profile:

from_conn_profile.save(alias="postgres_from_conn", clobber=True)

Output:

PosixPath(
    '/home/user/.config/xorq/profiles/postgres_from_conn.yaml'
)

Working with Multiple Profiles

Let’s see all the available profiles:

profiles = Profiles()
all_profiles = profiles.list()
print(f"Available profiles: {all_profiles}")

Output:

Available profiles: (
    'postgres_example', 
    'postgres_from_conn',
    ...
)

The output shows all profiles saved in your profile directory.

Cloning Profiles

Profiles can be cloned and modified, which is useful for creating variations:

cloned_profile = profile.clone(**{"connect_timeout": 10})
print(f"Original profile:\n{profile}")
print(f"Cloned profile:\n{cloned_profile}")

Output:

Original profile:
Profile(
    con_name='postgres', 
    kwargs_tuple=(
        ('database', 'postgres'), 
        ('host', '${POSTGRES_HOST}'), 
        ('password', '${POSTGRES_PASSWORD}'), 
        ('port', 5432), 
        ('user', '${POSTGRES_USER}')
    ), 
    idx=0
)

Cloned profile:
Profile(
    con_name='postgres', 
    kwargs_tuple=(
        ('connect_timeout', 10),
        ('database', 'postgres'), 
        ('host', '${POSTGRES_HOST}'), 
        ('password', '${POSTGRES_PASSWORD}'), 
        ('port', 5432), 
        ('user', '${POSTGRES_USER}')
    ), 
    idx=0
)

Notice how the cloned profile has a connect_timeout parameter but still maintains the environment variable references.

Saving a Cloned Profile

Let’s save our cloned profile with a new alias:

cloned_profile.save(alias="postgres_other_db", clobber=True)

Output:

PosixPath(
    '/home/user/.config/xorq/profiles/postgres_other_db.yaml'
)

Security Verification

Throughout this entire process, actual values of environment variables were never stored in profiles or exposed in output. This is a key security feature of xorq profiles.

Exploring Available Profiles

Finally, let’s explore all our available profiles:

profiles = Profiles()
for name in profiles.list():
    p = profiles.get(name)
    print(f"Profile: {name}")
    print(f"  - Profile: {p}")
    
    # We're also creating connections, but in production code
    # you might want to handle exceptions for invalid profiles
    connection = p.get_con()
    print(f"  - Connection: {connection}")

This will list all profiles and attempt to create connections from each one.

Summary

With xorq profiles, you can:

Create profiles with environment variable references
Save and load profiles securely
Create connections that use resolved environment variables
Extract profiles from existing connections
Clone and modify profiles while preserving security
Manage multiple profiles through the Profiles class

Profiles make it easy to work with database connections in a secure, maintainable way, ensuring sensitive credentials are never hardcoded in your code or configuration files.