Queries

Queries allow you to retrieve data from your database. Every query you build with ORM objects is a subclass of QueryBuilder at its base. This class provides a number of methods to help you construct your query and pass the correct types for downstream processing.

Shortcuts

We offer convenience functions for the most common SQL operations. This avoids the need to instantiate a QueryBuilder directly every time you want a new SQL query and typically results in cleaner code.

FUNCTIONiceaxe.queries.select

select

Creates a SELECT query to fetch data from the database. This is a shortcut function that creates and returns a new QueryBuilder instance.

Parameters

  • Name
    fields
    Type
    T | Type[T] | tuple[T | Type[T]] | tuple[T | Type[T], T2 | Type[T2]] | tuple[T | Type[T], T2 | Type[T2], T3 | Type[T3], *Ts]
    Description

    The fields to select. Can be: - A single field or model class (e.g., User.id or User) - A tuple of fields (e.g., (User.id, User.name)) - A tuple of model classes (e.g., (User, Post))

Code
# Select all fields from User
users = await conn.execute(select(User))

# Select specific fields
results = await conn.execute(select((User.id, User.name)))

# Select with conditions
active_users = await conn.execute(
    select(User)
    .where(User.is_active == True)
    .order_by(User.created_at, "DESC")
    .limit(10)
)
FUNCTIONiceaxe.queries.update

update

Creates an UPDATE query to modify existing records in the database. This is a shortcut function that creates and returns a new QueryBuilder instance.

Parameters

  • Name
    model
    Type
    Type[TableBase]
    Description

    The model class representing the table to update

Code
# Update all users' status
await conn.execute(
    update(User)
    .set(User.status, "inactive")
    .where(User.last_login < datetime.now() - timedelta(days=30))
)

# Update multiple fields with conditions
await conn.execute(
    update(User)
    .set(User.verified, True)
    .set(User.verification_date, datetime.now())
    .where(User.email_confirmed == True)
)
FUNCTIONiceaxe.queries.delete

delete

Creates a DELETE query to remove records from the database. This is a shortcut function that creates and returns a new QueryBuilder instance.

Parameters

  • Name
    model
    Type
    Type[TableBase]
    Description

    The model class representing the table to delete from

Code
# Delete inactive users
await conn.execute(
    delete(User)
    .where(User.is_active == False)
)

# Delete with complex conditions
await conn.execute(
    delete(User)
    .where(
        and_(
            User.created_at < datetime.now() - timedelta(days=90),
            User.email_confirmed == False
        )
    )
)

Raw Queries

CLASSiceaxe.queries.QueryBuilder

QueryBuilder

The QueryBuilder owns all construction of the SQL string given python method chaining. Each function call returns a reference to self, so you can construct as many queries as you want in a single line of code.

Internally we store most input-arguments as-is. We provide runtime value-checking to make sure the right objects are being passed in to query manipulation functions so our final build() will deterministically succeed if the query build was successful.

Note that this runtime check-checking validates different types than the static analysis. To satisfy Python logical operations (like join(ModelA.id == ModelB.id)) we have many overloaded operators that return objects at runtime but are masked to their Python types for the purposes of static analysis. This implementation detail should be transparent to the user but is noted in case you see different types through runtime inspection than you see during the typehints.

Class Methods

  • Name
    select
    Return type
    QueryBuilder[T, Literal['SELECT']] | QueryBuilder[tuple[T], Literal['SELECT']] | QueryBuilder[tuple[T, T2], Literal['SELECT']] | QueryBuilder[tuple[T, T2, T3], Literal['SELECT']] | QueryBuilder[tuple[T, T2, T3, *Ts], Literal['SELECT']]
    Description

    Creates a SELECT query to fetch data from the database.

  • Name
    update
    Return type
    QueryBuilder[None, Literal['UPDATE']]
    Description

    Creates a new update query for the given model. Returns the same QueryBuilder that is now flagged as an UPDATE query.

  • Name
    delete
    Return type
    QueryBuilder[None, Literal['DELETE']]
    Description

    Creates a new delete query for the given model. Returns the same QueryBuilder that is now flagged as a DELETE query.

  • Name
    where
    Return type
    Description

    Adds WHERE conditions to filter the query results. Multiple conditions are combined with AND. For OR conditions, use the or_ function.

  • Name
    order_by
    Return type
    Description

    Adds an ORDER BY clause to sort the query results.

  • Name
    join
    Return type
    Description

    Adds a JOIN clause to combine data from multiple tables.

  • Name
    set
    Return type
    Description

    Sets a column to a specific value in an update query.

  • Name
    limit
    Return type
    Description

    Limits the number of rows returned by the query.

  • Name
    offset
    Return type
    Description

    Skips the specified number of rows before returning results.

  • Name
    group_by
    Return type
    Description

    Groups the results by specified fields, typically used with aggregate functions.

  • Name
    having
    Return type
    Description

    Adds HAVING conditions to filter grouped results based on aggregate values.

  • Name
    distinct_on
    Return type
    Description

    Adds a DISTINCT ON clause to remove duplicate rows based on specified fields.

  • Name
    text
    Return type
    Description

    Uses a raw SQL query instead of the query builder.

  • Name
    for_update
    Return type
    QueryBuilder[P, QueryType]
    Description

    Adds FOR UPDATE clause to the query. This is useful for pessimistic locking. Multiple calls will be combined, with the most restrictive options taking precedence.

  • Name
    build
    Return type
    tuple[str, list[Any]]
    Description

    Builds and returns the final SQL query string and parameter values.

Code
# Basic SELECT query
query = (
    QueryBuilder()
    .select(User)
    .where(User.is_active == True)
    .order_by(User.created_at, "DESC")
)

# Complex query with joins and aggregates
query = (
    QueryBuilder()
    .select((User.name, func.count(Order.id)))
    .join(Order, Order.user_id == User.id)
    .where(Order.status == "completed")
    .group_by(User.name)
    .having(func.count(Order.id) > 5)
)
CLASSiceaxe.functions.FunctionMetadata

FunctionMetadata

Represents metadata for SQL aggregate functions and other SQL function operations. This class bridges the gap between Python function calls and their SQL representations, maintaining type information and original field references.

Class Constructor

  • Name
    literal
    Type
    QueryLiteral
    Description

    The SQL representation of the function call

  • Name
    original_field
    Type
    DBFieldClassDefinition
    Description

    The database field this function operates on

  • Name
    local_name
    Type
    str | None
    Description

    Default: None

    Optional alias for the function result in the query

Class Methods

  • Name
    to_query
    Return type
    Description

    Converts the function metadata to its SQL representation.

Code
# Internal representation of function calls:
metadata = FunctionMetadata(
    literal=QueryLiteral("count(users.id)"),
    original_field=User.id,
    local_name="user_count"
)
# Used in query: SELECT count(users.id) AS user_count
CLASSiceaxe.comparison.ComparisonBase

ComparisonBase

Abstract base class for database fields that can be used in comparisons. Provides standard comparison operators and methods for SQL query generation.

This class implements Python's comparison magic methods (eq, ne, etc.) to enable natural syntax for building SQL queries. It also provides additional methods for SQL-specific operations like IN, LIKE, and NULL comparisons.

Class Methods

  • Name
    in_
    Return type
    bool
    Description

    Implements SQL IN operator. Checks if the field's value is in a sequence of values.

  • Name
    not_in
    Return type
    bool
    Description

    Implements SQL NOT IN operator. Checks if the field's value is not in a sequence of values.

  • Name
    like
    Return type
    bool
    Description

    Implements SQL LIKE operator for pattern matching. Case-sensitive string pattern matching.

  • Name
    not_like
    Return type
    bool
    Description

    Implements SQL NOT LIKE operator. Case-sensitive string pattern non-matching.

  • Name
    ilike
    Return type
    bool
    Description

    Implements PostgreSQL ILIKE operator. Case-insensitive string pattern matching.

  • Name
    not_ilike
    Return type
    bool
    Description

    Implements PostgreSQL NOT ILIKE operator. Case-insensitive string pattern non-matching.

  • Name
    to_query
    Return type
    tuple[QueryLiteral, list[Any]]
    Description

    Abstract method to convert the field to its SQL representation. Must be implemented by subclasses.

Code
# ComparisonBase enables these operations on database fields:
User.age >= 21
User.status == "active"
User.name.like("%John%")
User.role.in_(["admin", "moderator"])
User.deleted_at.is_(None)

Functions

GLOBALiceaxe.functions.func

func

A global instance of FunctionBuilder that provides SQL function operations for use in queries. This instance offers a comprehensive set of SQL functions including aggregates, string operations, mathematical functions, date/time manipulations, and type conversions.

Available function categories:

  • Aggregate Functions: count, sum, avg, min, max, array_agg, string_agg
  • String Functions: lower, upper, length, trim, substring
  • Mathematical Functions: abs, round, ceil, floor, power, sqrt
  • Date/Time Functions: date_trunc, date_part, extract, age, date
  • Type Conversion: cast, to_char, to_number, to_timestamp
func = FunctionBuilder() 
Code
from iceaxe import func, select

# Aggregate functions
total_users = await conn.execute(select(func.count(User.id)))
avg_salary = await conn.execute(select(func.avg(Employee.salary)))
unique_statuses = await conn.execute(select(func.distinct(User.status)))

# String operations
users = await conn.execute(select((
    User.id,
    func.lower(User.name),
    func.upper(User.email),
    func.length(User.bio)
)))

# Date/time operations
monthly_stats = await conn.execute(select((
    func.date_trunc('month', Event.created_at),
    func.count(Event.id)
)).group_by(func.date_trunc('month', Event.created_at)))

# Mathematical operations
account_stats = await conn.execute(select((
    Account.id,
    func.abs(Account.balance),
    func.ceil(Account.interest_rate)
)))

# Type conversions
converted = await conn.execute(select((
    func.cast(User.string_id, int),
    func.to_char(User.created_at, 'YYYY-MM-DD'),
    func.cast(User.status_str, UserStatus)
)))

# Complex aggregations
department_stats = await conn.execute(
    select((
        Department.name,
        func.array_agg(Employee.name),
        func.string_agg(Employee.email, ','),
        func.sum(Employee.salary)
    )).group_by(Department.name)
)
CLASSiceaxe.functions.FunctionBuilder

FunctionBuilder

Builder class for SQL aggregate functions and other SQL operations. Provides a Pythonic interface for creating SQL function calls with proper type hints.

This class is typically accessed through the global func instance:

Class Methods

  • Name
    count
    Return type
    int
    Description

    Creates a COUNT aggregate function call.

  • Name
    distinct
    Return type
    T
    Description

    Creates a DISTINCT function call that removes duplicate values.

  • Name
    sum
    Return type
    T
    Description

    Creates a SUM aggregate function call.

  • Name
    avg
    Return type
    T
    Description

    Creates an AVG aggregate function call.

  • Name
    max
    Return type
    T
    Description

    Creates a MAX aggregate function call.

  • Name
    min
    Return type
    T
    Description

    Creates a MIN aggregate function call.

  • Name
    abs
    Return type
    T
    Description

    Creates an ABS function call to get the absolute value.

  • Name
    date_trunc
    Return type
    T
    Description

    Truncates a timestamp or interval value to specified precision.

  • Name
    date_part
    Return type
    float
    Description

    Extracts a subfield from a date/time value.

  • Name
    extract
    Return type
    int
    Description

    Extracts a subfield from a date/time value using SQL standard syntax.

  • Name
    age
    Return type
    T
    Description

    Calculates the difference between two timestamps. If reference is not provided, current_date is used.

  • Name
    date
    Return type
    T
    Description

    Converts a timestamp to a date by dropping the time component.

  • Name
    lower
    Return type
    T
    Description

    Converts string to lowercase.

  • Name
    upper
    Return type
    T
    Description

    Converts string to uppercase.

  • Name
    length
    Return type
    int
    Description

    Returns length of string.

  • Name
    trim
    Return type
    T
    Description

    Removes whitespace from both ends of string.

  • Name
    substring
    Return type
    T
    Description

    Extracts substring.

  • Name
    round
    Return type
    T
    Description

    Rounds to nearest integer.

  • Name
    ceil
    Return type
    T
    Description

    Rounds up to nearest integer.

  • Name
    floor
    Return type
    T
    Description

    Rounds down to nearest integer.

  • Name
    power
    Return type
    T
    Description

    Raises a number to the specified power.

  • Name
    sqrt
    Return type
    T
    Description

    Calculates square root.

  • Name
    array_agg
    Return type
    list[T]
    Description

    Collects values into an array.

  • Name
    string_agg
    Return type
    str
    Description

    Concatenates values with delimiter.

  • Name
    cast
    Return type
    T
    Description

    Converts value to specified type.

  • Name
    to_char
    Return type
    str
    Description

    Converts value to string with format.

  • Name
    to_number
    Return type
    float
    Description

    Converts string to number with format.

  • Name
    to_timestamp
    Return type
    datetime
    Description

    Converts string to timestamp with format.

Code
from iceaxe import func

# In a query:
query = select((
    User.name,
    func.count(User.id),
    func.max(User.age)
))

Conditions

If you want to add conditions to your query, you can use the where method. If you pass multiple conditions to this, they'll be chained together as an AND filter. To combine OR and AND predicates together, you can use and_ and or_.

FUNCTIONiceaxe.queries.and_

and_

Combines multiple conditions with logical AND. All conditions must be true for the group to be true.

Parameters

  • Name
    conditions
    Type
    bool
    Description

    Default: ()

    Variable number of conditions to combine

Code
query = select(User).where(
    and_(
        User.age >= 21,
        User.status == "active",
        User.role == "member"
    )
)
FUNCTIONiceaxe.queries.or_

or_

Combines multiple conditions with logical OR. At least one condition must be true for the group to be true.

Parameters

  • Name
    conditions
    Type
    bool
    Description

    Default: ()

    Variable number of conditions to combine

Code
query = select(User).where(
    or_(
        User.role == "admin",
        and_(
            User.role == "moderator",
            User.permissions.contains("manage_users")
        )
    )
)

Enums

GLOBALiceaxe.queries.JoinType

JoinType

JoinType = Literal['INNER', 'LEFT', 'RIGHT', 'FULL']
GLOBALiceaxe.queries.OrderDirection

OrderDirection

OrderDirection = Literal['ASC', 'DESC']