approck-sqlalchemy-utils 0.1.6

SQLAlchemy 2 helpers: declarative base, async/sync sessions, JSON and list column types, encrypted strings, Alembic revision helpers.

approck-sqlalchemy-utils

Small helpers for SQLAlchemy 2.x: configure async + sync database access in one step, plug a stable FastAPI dependency, and optionally reuse a compact Base, column types, timestamps mixin, and Alembic utilities. Use only the pieces you need — many projects start with sessions and keep their own declarative base.

Requirements

• Python 3.10+
• Runtime: SQLAlchemy 2.x, Alembic, cryptography (for encrypted column types)

Optional extras:

• postgres — psycopg2-binary and asyncpg for PostgreSQL sync/async drivers
• dev — pytest, ruff, mypy, pre-commit

Installation

uv add approck-sqlalchemy-utils

Or with pip:

pip install approck-sqlalchemy-utils

PostgreSQL (async + sync drivers for this library’s session helpers):

uv add "approck-sqlalchemy-utils[postgres]"

Getting started

1. Install the package

See Installation. For Postgres async URLs you typically want the postgres extra.

2. Call session.init() once when the app starts

Pass your async URL (postgresql+asyncpg://…). The library builds the async engine and a matching sync engine (same URL with +asyncpg removed for psycopg2). Pool and session defaults are set for you (pool_pre_ping, autoflush=False, expire_on_commit=False).

Call init from FastAPI lifespan, a factory, main before workers start, test conftest, etc. — wherever you already bootstrap configuration.

import os

import approck_sqlalchemy_utils.session as db

db.init(os.environ["DATABASE_URL"], pool_pre_ping=True)

FastAPI example with lifespan:

import os
from contextlib import asynccontextmanager

from fastapi import FastAPI

import approck_sqlalchemy_utils.session as db


@asynccontextmanager
async def lifespan(app: FastAPI):
    db.init(os.environ["DATABASE_URL"], pool_pre_ping=True)
    yield


app = FastAPI(lifespan=lifespan)

3. Use get_session from mocks in route handlers

Import get_session from approck_sqlalchemy_utils.mocks (not from session) so the symbol FastAPI’s Depends uses is the same one init() rebinds internally.

from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession

from approck_sqlalchemy_utils.mocks import get_session


@app.get("/items")
async def list_items(session: AsyncSession = Depends(get_session)):
    ...

What to read next

Goal	Section
Async async with session (scripts, workers, pytest-asyncio)	Sessions → context_session
Sync with session (create_all, CLI, blocking code)	Sessions → current_session
Optional Base, encrypted fields, timestamps	ORM example and What is included
Alembic numeric revisions, JSON/list types, etc.	What is included

What is included

Area	Module	Purpose
ORM base	approck_sqlalchemy_utils.model	Base with __tablename__ from class name (snake_case) and integer id primary key
Sessions	approck_sqlalchemy_utils.session	init(): dual async/sync engines; routes use get_session from mocks with Depends(get_session)
Timestamps	approck_sqlalchemy_utils.mixins.auto_now	MixinWithAutoNow — timezone-aware created_at / updated_at with server defaults
JSON column	approck_sqlalchemy_utils.types.json	JSONType — PostgreSQL json where available, otherwise text + JSON encode/decode
List in one column	approck_sqlalchemy_utils.types.scalar_list	ScalarListType — Python list ↔ delimiter-separated text
Encrypted text	approck_sqlalchemy_utils.types.encrypted.encrypted_type	StringEncryptedType and AES / AES-GCM / Fernet-style engines
Sorting helper	approck_sqlalchemy_utils.parsers.order_by	parse() — turn ["column:asc", ...] into SQLAlchemy text() fragments
Alembic	approck_sqlalchemy_utils.alembic.humanreadable	process_revision_directives for zero-padded numeric revision ids (0001, 0002, …)

ORM example

Optional — use your own DeclarativeBase if you prefer. This shows this package’s Base, encrypted column, and auto timestamps:

from sqlalchemy import ForeignKey, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from approck_sqlalchemy_utils.mixins.auto_now import MixinWithAutoNow
from approck_sqlalchemy_utils.model import Base
from approck_sqlalchemy_utils.types.encrypted.encrypted_type import StringEncryptedType

SECRET = "your-app-secret"  # use env / KMS in production


class Author(Base):
    first_name: Mapped[str | None] = mapped_column(String(100))
    last_name: Mapped[str | None] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(StringEncryptedType(String(255), SECRET), nullable=False)


class Book(Base, MixinWithAutoNow):
    slug: Mapped[str] = mapped_column(String(100), nullable=False)
    title: Mapped[str] = mapped_column(String(100), nullable=False)
    author_id: Mapped[int] = mapped_column(ForeignKey("author.id"))
    author: Mapped["Author"] = relationship("Author", lazy="selectin")
    description: Mapped[str | None] = mapped_column(Text())

Sessions

Most async services still need sync database access sometimes (create_all in tests, scripts, legacy code). Without helpers you duplicate engines, URLs, and sessionmaker settings.

session.init(url, **engine_kwargs) does that once: one async URL, shared options on both engines, and these entry points on approck_sqlalchemy_utils.session after init():

Callable	When to use it
get_session (from mocks)	FastAPI Depends(get_session) — see Getting started.
override_session	Same object as mocks.get_session after init(); for async for or internal use. Prefer mocks.get_session in route Depends.
context_session	async with context_session() as session: — async tests, tasks, scripts.
current_session	with current_session() as session: — sync ORM, create_all, CLI.

init() parameters

Extra keyword arguments are forwarded to both create_async_engine and create_engine (for example pool_recycle=600, pool_size=5).

import approck_sqlalchemy_utils.session as db

db.init(
    "postgresql+asyncpg://user:pass@localhost:5432/mydb",
    pool_pre_ping=True,
    pool_recycle=600,
)

Async code — context_session

from approck_sqlalchemy_utils.session import context_session

async def load_row():
    async with context_session() as session:
        result = await session.execute(...)
        return result.scalar_one_or_none()

Sync code — current_session

import approck_sqlalchemy_utils.session as db
from approck_sqlalchemy_utils.model import Base

with db.current_session() as session:
    Base.metadata.create_all(session.get_bind())

This repository’s tests/conftest.py shows init, context_session, and current_session together with pytest-asyncio.

Development

Clone the repository and install with dev dependencies:

uv sync --all-extras

Tests expect PostgreSQL on localhost:5432 with user/password postgres, database postgres (same URL as in tests/conftest.py). For example:

docker run -d --name approck-pg-test \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  postgres:16-alpine

Run tests:

uv run pytest

Lint:

uv run ruff check .
uv run ruff format --check .

Type-check (uses mypy.ini):

uv run mypy approck_sqlalchemy_utils

See CONTRIBUTING.md for pull requests and release notes.

Publishing to PyPI

Releases are built and uploaded by .github/workflows/release.yml when you push a version tag (for example 0.1.6 or v0.1.6). The version in pyproject.toml must match what you intend to ship.

One-time PyPI setup (trusted publishing)

1. On GitHub: Settings → Environments → New environment → name pypi (exactly this name unless you change both GitHub and PyPI).
2. On PyPI: add a trusted publisher for this project:
   • Owner: your GitHub user or organization
   • Repository name: adalekin/approck-sqlalchemy-utils (adjust if the repo path differs)
   • Workflow name: release.yml
   • Environment name: pypi
3. After the first successful upload, the PyPI project is created; for later releases, edit the project’s Publishing settings if the workflow or environment name changes.

The workflow uses OpenID Connect (id-token: write); a long-lived PyPI API token in GitHub Secrets is not required.

Release checklist

1. Bump version in pyproject.toml (and commit).
2. git tag 0.1.6 (or v0.1.6) and git push origin <tag>.

License

MIT — see LICENSE.

Repository URLs

PyPI metadata points to this GitHub repository. If you fork or move the project, update [project.urls] in pyproject.toml and the CI badge in this README.