API draft design.

[proofit404]

SELECT "id", "name", "age" FROM "users" WHERE "age" > {{ age }};

from pathlib import Path
from sqlite3 import connect
from typing import NewType
from typing import TypeAlias
from typing import TypedDict

from typedsql import Sql

UserId = NewType("UserId", int)

UserName = NewType("UserName", str)

UserAge = NewType("UserAge", int)

class User(TypedDict):
    id: UserId
    name: UserName
    age: UserAge

Users: TypeAlias = list[User]

Args = TypedDict("Args", {"age": UserAge})

class UsersAboveAge(Sql[Args, Users]):
    query = Path("users-above-age.sql")

db = connect(":memory:")

users_above_age = UsersAboveAge(db)

for user in users_above_age({"age": UserAge(21)}):
    print(user)

from typing import Callable
from typing import Generic
from typing import TypeVar

from pathlib import Path
from sqlite3 import Connection

A = TypeVar("A")
R = TypeVar("R")

class Sql(Generic[A, R]):
    query: Path

    def __init__(self, connection: Connection) -> None: ...

    def __call__(self, args: A) -> R: ...

• validate jinja variables are the same as argument
• return namedtyple or typeddict
• deny total false for typeddict

[proofit404]

https://mypy.readthedocs.io/en/stable/command_line.html#enabling-incomplete-experimental-features

class UsersAboveAge(Sql[{"age": UserAge}, Users]):
    query = """
    SELECT "id", "name", "age" FROM "users" WHERE "age" > {{ age }};
    """

[proofit404]

• define query as inline string

[proofit404]

• separate migrations folder
• build internal schema from migrations folder
• validate we pass correct arguments to correct fields

[proofit404]

• transaction management with context manager api

[proofit404]

Since select statement fields should match result dictionary type, we could generate part of the query.

class UsersAboveAge(Sql[{"age": UserAge}, Users]):
    query = """
    SELECT {{ fields }} FROM "users" WHERE "age" > {{ age }};
    """

In that case if we change User signature with additional fields, all of them would be automatically added to the query.