SQL File Loader#

Load and cache SQL files with named statement support. SQL files can contain multiple named statements separated by -- name: comments.

SQLFileLoader#

class sqlspec.loader.SQLFileLoader[source]#

Bases: object

Loads and parses SQL files with named SQL queries.

Loads SQL files containing named queries (using -- name: syntax) and retrieves them by name.

__init__(*, encoding='utf-8', storage_registry=None, runtime=None, strict_parameter_annotations=False)[source]#

Initialize the SQL file loader.

Parameters:

encoding¶ (str) -- Text encoding for reading SQL files.
storage_registry¶ (StorageRegistry | None) -- Storage registry for handling file URIs.
runtime¶ (ObservabilityRuntime | None) -- Observability runtime for instrumentation.
strict_parameter_annotations¶ (bool) -- When True, a malformed -- param: directive raises instead of emitting a warning and skipping the line.

set_observability_runtime(runtime)[source]#

Attach an observability runtime used for instrumentation.

Return type:: None

load_sql(*paths)[source]#

Load SQL files and parse named queries.

Parameters:: *paths¶ (str | Path) -- One or more file paths or directory paths to load.
Return type:: None

add_named_sql(name, sql, dialect=None, parameters=None)[source]#

Add a named SQL query directly without loading from a file.

The SQL may contain /* include: name */ and /* slot: name */ markers; slots added this way have no defaults.

Parameters:

name¶ (str) -- Name for the SQL query.
sql¶ (str) -- Raw SQL content.
dialect¶ (str | None) -- Optional dialect for the SQL statement.
parameters¶ (Sequence[ParameterDeclaration] | None) -- Optional declared parameter metadata for the query.

Raises:

ValueError -- If query name already exists.

Return type:

None

add_fragment(name, sql)[source]#

Add a reusable SQL fragment directly without loading from a file.

Parameters:

name¶ (str) -- Name for the fragment, referenced by /* include: name */ markers.
sql¶ (str) -- Fragment SQL text; may contain include and slot markers.

Raises:

ValueError -- If the fragment name already exists.

Return type:

None

has_fragment(name)[source]#

Check if a fragment exists.

Parameters:: name¶ (str) -- Fragment name to check.
Return type:: bool
Returns:: True if the fragment exists.

list_fragments()[source]#

List all available fragment names.

Return type:: list[str]
Returns:: Sorted list of fragment names.

get_fragment_text(name)[source]#

Get a fragment's SQL text with its includes resolved.

Slot markers are left in place.

Parameters:

name¶ (str) -- Fragment name.

Return type:

str

Returns:

Fragment SQL text with /* include: */ markers replaced.

Raises:

SQLFragmentNotFoundError -- If the fragment or an included fragment does not exist.
SQLFileParseError -- If the includes form a cycle.

get_query_slots(name)[source]#

Get the slots of a query, including slots contributed by included fragments.

Slots declared with -- slot: come first in declaration order, then undeclared (required) markers in the query's own text, then markers contributed by included fragments; markers keep their order of appearance.

Parameters:

name¶ (str) -- Query name (hyphens are converted to underscores).

Return type:

tuple[SlotDeclaration, ...]

Returns:

Tuple of slot declarations; empty if the query has none.

Raises:

SQLStatementNotFoundError -- If the query or an included fragment does not exist.
SQLFileParseError -- If a declared slot has no marker or the includes form a cycle.

get_query_parameters(name)[source]#

Get declared parameter metadata for a query.

Parameters:: name¶ (str) -- Query name (hyphens are converted to underscores).
Return type:: tuple[ParameterDeclaration, ...]
Returns:: Tuple of declared parameters; empty if the query declares none.
Raises:: SQLStatementNotFoundError -- If the query does not exist.

get_file(path)[source]#

Get a loaded SQLFile object by path.

Parameters:: path¶ (str | Path) -- Path of the file.
Return type:: SQLFile | None
Returns:: SQLFile object if loaded, None otherwise.

get_file_for_query(name)[source]#

Get the SQLFile object containing a query.

Parameters:: name¶ (str) -- Query name (hyphens are converted to underscores).
Return type:: SQLFile | None
Returns:: SQLFile object if query exists, None otherwise.

list_queries()[source]#

List all available query names.

Return type:: list[str]
Returns:: Sorted list of query names.

list_files()[source]#

List all loaded file paths.

Return type:: list[str]
Returns:: Sorted list of file paths.

has_query(name)[source]#

Check if a query exists.

Parameters:: name¶ (str) -- Query name to check.
Return type:: bool
Returns:: True if query exists.

clear_cache()[source]#

Clear all cached files and queries.

Return type:: None

clear_file_cache()[source]#

Clear the file cache only, keeping loaded queries.

Return type:: None

get_query_text(name)[source]#

Get raw SQL text for a query.

Includes are resolved; slot markers are left in place.

Parameters:

name¶ (str) -- Query name.

Return type:

str

Returns:

Raw SQL text.

Raises:

SQLStatementNotFoundError -- If the query or an included fragment does not exist.
SQLFileParseError -- If a declared slot has no marker or the includes form a cycle.

get_sql(name, **slots)[source]#

Get a SQL object by statement name, filling its slots.

Each /* slot: name */ marker is replaced by the matching keyword value, or by the slot's -- slot: default when no value is given. A value may be a str (spliced verbatim), a sqlglot expression (rendered with the statement's dialect), or a SQL object (its text is spliced and its named parameters are bound on the returned statement). Slot values are SQL, not data: pass user input as parameters of a SQL value.

The statement is cached only when no slot values are given.

Parameters:

name¶ (str) -- Name of the statement (from -- name: in SQL file). Hyphens in names are converted to underscores.
**slots¶ (Any) -- Values for the statement's slots, keyed by slot name.

Return type:

SQL

Returns:

SQL object ready for execution.

Raises:

SQLSlotError -- If a required slot is missing, a slot name is unknown, a SQL value uses positional parameters, or slot parameter names collide with each other or with the statement's placeholders.
TypeError -- If a slot value is not a str, sqlglot expression, or SQL.
SQLFileParseError -- If declared parameters do not match the filled SQL or the SQL cannot be compiled.

SQLFile#

class sqlspec.loader.SQLFile[source]#

Bases: object

Represents a loaded SQL file with metadata.

Contains SQL content and associated metadata including file location, timestamps, and content hash.

__init__(content, path, metadata=None, loaded_at=None)[source]#

Initialize SQLFile.

Parameters:

content¶ (str) -- Raw SQL content from the file.
path¶ (str) -- Path where the SQL file was loaded from.
metadata¶ (dict[str, typing.Any] | None) -- Optional metadata associated with the SQL file.
loaded_at¶ (datetime | None) -- Timestamp when the file was loaded.

NamedStatement#

class sqlspec.loader.NamedStatement[source]#

Bases: object

Represents a parsed SQL statement with metadata.

Contains individual SQL statements extracted from files with their normalized names, SQL content, optional dialect specifications, line position for error reporting, slot declarations from the statement's own text, and whether the text contains include markers.

__init__(name, sql, dialect=None, start_line=0, parameters=(), slots=(), has_includes=False)[source]#

Fragments and Slots#

SQL files can declare reusable -- fragment: sections, splice them into queries with /* include: name */, and mark fill points with /* slot: name */. See Fragments and Slots for the syntax and the get_sql(name, **slots) call.

class sqlspec.loader.SlotDeclaration[source]#

Bases: object

A fill point declared by a /* slot: name */ marker.

A slot with a default comes from a -- slot: name = default directive; a slot whose default is None must be supplied by the caller.

__init__(name, default=None)[source]#

class sqlspec.exceptions.SQLSlotError[source]#

Bases: SQLSpecError

Raised when a SQL statement slot is missing, unknown, or conflicts.

__init__(statement, message)[source]#

Initialize the error.

Parameters:

statement¶ (str) -- Name of the SQL statement whose slots could not be filled.
message¶ (str) -- Description of the slot problem.

class sqlspec.exceptions.SQLFragmentNotFoundError[source]#

Bases: SQLStatementNotFoundError

Raised when a SQL fragment named by an include or lookup is not loaded.

__init__(name, normalized_name, fragment_count)[source]#

Initialize the error.

Parameters:

name¶ (str) -- Fragment name requested by the caller or include marker.
normalized_name¶ (str) -- Normalized fragment name used for lookup.
fragment_count¶ (int) -- Number of SQL fragments loaded in the registry.

SQLFileCacheEntry#

class sqlspec.loader.SQLFileCacheEntry[source]#

Bases: object

Cached SQL file with parsed statements and fragments.

Stored in the file cache to avoid re-parsing SQL files when their content hasn't changed.

__init__(sql_file, parsed_statements, parsed_fragments=None)[source]#

Initialize cached SQL file.

Parameters:

sql_file¶ (SQLFile) -- Original SQLFile with content and metadata.
parsed_statements¶ (dict[str, NamedStatement]) -- Named statements from the file.
parsed_fragments¶ (dict[str, SQLFragment] | None) -- Fragments from the file.

Declared Parameters#

Parameters declared in SQL files via -- param: directives are exposed as ParameterDeclaration objects. See Declared Parameters for the grammar and validation behavior.

class sqlspec.ParameterDeclaration[source]

Bases: object

A single parameter declared in a SQL file header.

__init__(name, type_str, description=None, *, required=True)[source]

sqlspec.register_param_type(name, py_type)[source]#

Parameters:

name¶ (str) -- The declared type string as written in -- param: (case-insensitive).
py_type¶ (type | tuple[type, ...] | Callable[[object], bool]) -- The Python type, tuple of types, or predicate used for validation.

Return type:

None

sqlspec.resolve_param_type(type_str)[source]#

Resolve a declared type string to a matcher, or None if unknown.

Unknown type strings are documentation-only and skipped during validation. The declared string is looked up, never evaluated. Parameterized containers (list[int]) resolve to their origin type (list).

Parameters:: type_str¶ (str) -- The declared type string from a -- param: directive.
Return type:: type | tuple[type, ...] | Callable[[object], bool] | None
Returns:: The resolved matcher, or None when not in the registry.