SQL File Loader¶

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()

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>).