Bug: Sql file loader does not resolve paths correctly on Windows

[summersz]

Description

On Windows the sql file loader is unable to find sql file paths.

MCVE

from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from pathlib import Path

root_path = Path(__file__).resolve().parent
tmp_path = root_path / "sql"
sql_file = tmp_path / "query.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()

Logs

C:\Users\SummersRA\src>sql_file_loader.py
Traceback (most recent call last):
  File "sqlspec\storage\errors.py", line 91, in execute_sync_storage_operation
    return func()
  File "C:\Users\SummersRA\AppData\Local\Programs\Python\Python314\Lib\pathlib\__init__.py", line 777, in read_bytes
    with self.open(mode='rb', buffering=0) as f:
         ~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SummersRA\AppData\Local\Programs\Python\Python314\Lib\pathlib\__init__.py", line 771, in open
    return io.open(self, mode, buffering, encoding, errors, newline)
           ~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\SummersRA\\src\\query.sql'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "sqlspec\storage\backends\local.py", line 95, in read_bytes_sync
    return execute_sync_storage_operation(
  File "sqlspec\storage\errors.py", line 93, in execute_sync_storage_operation
  File "sqlspec\storage\errors.py", line 83, in raise_storage_error
    raise FileNotFoundInStorageError(normalized.message) from error
sqlspec.exceptions.FileNotFoundInStorageError: local read_bytes failed for C:\Users\SummersRA\src\query.sql

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "sqlspec\loader.py", line 343, in _read_file_content
    return backend.read_text_sync(Path(path_str).name, encoding=self.encoding)
  File "sqlspec\storage\backends\local.py", line 114, in read_text_sync
  File "sqlspec\storage\backends\local.py", line 99, in read_bytes_sync
    raise FileNotFoundError(str(resolved)) from error
FileNotFoundError: C:\Users\SummersRA\src\query.sql

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\SummersRA\src\sql_file_loader.py", line 12, in <module>
    spec.load_sql_files(sql_file)
    ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
  File "sqlspec\base.py", line 508, in load_sql_files
  File "sqlspec\loader.py", line 588, in load_sql
  File "sqlspec\loader.py", line 682, in _load_single_file
    loaded_statements = self._load_uncached_file(file_path, namespace)
  File "sqlspec\loader.py", line 712, in _load_uncached_file
    content = self._read_file_content(file_path)
  File "sqlspec\loader.py", line 351, in _read_file_content
    raise SQLFileNotFoundError(path_str) from e
sqlspec.exceptions.SQLFileNotFoundError: SQL file 'C:\Users\SummersRA\src\sql\query.sql' not found

Package Version

0.58.3

Platform

• Linux
• Mac
• Windows
• Other (Please specify in the description above)