SQL File Loader

See More

SQLSpec includes a SQL file loader that keeps your queries in .sql files and exposes them through the registry. Load directories or individual files, then execute named queries with spec.get_sql().

Load SQL Files

load SQL files
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

sql_file = tmp_path / "queries.sql"
sql_file.write_text("-- name: list_teams\nselect id, name from teams order by id;\n")

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))
spec.load_sql_files(sql_file)

with spec.provide_session(config) as session:
    session.execute("create table teams (id integer primary key, name text)")
    session.execute("insert into teams (name) values ('Litestar'), ('SQLSpec')")
    result = session.execute(spec.get_sql("list_teams"))
    rows = result.all()

Named Queries

named SQL
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))
spec.add_named_sql("find_team", "select id, name from teams where name = :name")

with spec.provide_session(config) as session:
    session.execute("create table teams (id integer primary key, name text)")
    session.execute("insert into teams (name) values ('Litestar'), ('SQLSpec')")
    result = session.execute(spec.get_sql("find_team"), {"name": "SQLSpec"})
    row = result.one()

Dynamic WHERE Chaining

SQL objects returned by get_sql() support .where() chaining. This lets you start with a base query and add conditions dynamically without string concatenation.

dynamic where chaining
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))

# Register a base query
spec.add_named_sql("list_users", "select id, name, status from users")

with spec.provide_session(config) as session:
    session.execute("create table users (id integer primary key, name text, status text)")
    session.execute(
        "insert into users (name, status) values ('Alice', 'active'), ('Bob', 'inactive'), ('Charlie', 'active')"
    )

    # Get the base SQL object and chain .where() calls
    base_query = spec.get_sql("list_users")

    # Add a WHERE clause dynamically
    active_query = base_query.where("status = 'active'")
    active_users = session.select(active_query)
    print(active_users)  # [{"id": 1, ...}, {"id": 3, ...}]

    # Use typed where helpers
    filtered = base_query.where_eq("name", "Bob")
    bob = session.select(filtered)
    print(bob)  # [{"id": 2, "name": "Bob", "status": "inactive"}]

    # Chain multiple where conditions (AND)
    specific = base_query.where_eq("status", "active").where_like("name", "A%")
    result = session.select(specific)
    print(result)  # [{"id": 1, "name": "Alice", "status": "active"}]

Available where helpers:

  • .where(condition) – raw SQL condition string

  • .where_eq(column, value) – equality

  • .where_neq(column, value) – inequality

  • .where_lt(column, value) / .where_lte(column, value) – less than

  • .where_gt(column, value) / .where_gte(column, value) – greater than

  • .where_like(column, pattern) / .where_ilike(column, pattern) – pattern matching

  • .where_in(column, values) / .where_not_in(column, values) – set membership

  • .where_is_null(column) / .where_is_not_null(column) – null checks

  • .where_between(column, low, high) – range

Each call returns a new SQL object (immutable chaining).

How Query Names Work

  • Name queries with -- name: query_name comments.

  • SQLSpec normalizes names to snake_case for Python access.

  • Add -- dialect: postgres on the first line of a block to bind SQL to a dialect.

  • Directory structures become namespaces when you load directories (reports/daily.sql -> reports.<query>).