import os
from xorq.vendor.ibis.backends.profiles import Profile, Profiles
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:
First, we’ll set up the environment variables for our database connection:
# Set environment variables for a PostgreSQL database
"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" os.environ[
Creating a Profile
Now, let’s create a profile using environment variable references instead of hardcoded values:
= Profile(
profile ="postgres",
con_name=(
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:
= profile.save(alias="postgres_example", clobber=True)
path 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:
= Profile.load("postgres_example")
loaded_profile 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:
= loaded_profile.get_con()
connection 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:
= connection.list_tables()
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:
= connection._profile
conn_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:
= Profile.from_con(connection)
from_conn_profile 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:
="postgres_from_conn", clobber=True) from_conn_profile.save(alias
Output:
PosixPath(
'/home/user/.config/xorq/profiles/postgres_from_conn.yaml'
)
Working with Multiple Profiles
Let’s see all the available profiles:
= Profiles()
profiles = profiles.list()
all_profiles 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:
= profile.clone(**{"connect_timeout": 10})
cloned_profile 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:
="postgres_other_db", clobber=True) cloned_profile.save(alias
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():
= profiles.get(name)
p 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
= p.get_con()
connection 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.