atelier-python-sqlalchemy_skill

Overview

This skill provides modern SQLAlchemy 2.0+ ORM patterns for Python database access, focused on model definition, querying, session management, relationships, JSON columns, and upserts. It presents practical code patterns to define typed models, run efficient queries, and perform conflict-safe inserts for PostgreSQL. The goal is to standardize common database tasks with clear, maintainable examples.

How this skill works

The skill shows typed DeclarativeBase models with Mapped annotations and mapped_column helpers to create safe, self-documenting schemas. It demonstrates sessionmaker-based session lifecycle management for synchronous code, common query patterns (select, filter, get, count), and relationship wiring with back_populates. For PostgreSQL it includes dialect-specific upsert via insert().on_conflict_do_update and JSON column querying using the JSON type.

When to use it

• Defining typed models with SQLAlchemy 2.0+ and Python type hints
• Implementing CRUD operations and efficient query patterns
• Managing session lifecycle in web apps or scripts
• Performing PostgreSQL upserts (conflict-safe inserts/updates)
• Modeling and querying JSON columns and nested data

Best practices

• Use DeclarativeBase with Mapped and mapped_column to keep models type-checked and IDE-friendly
• Create a single sessionmaker (SessionLocal) and always close sessions in finally blocks or dependency-managed contexts
• Prefer select() with session.execute(...).scalars() for modern, explicit queries instead of legacy query() when possible
• Use dialect-specific constructs (e.g., postgresql.insert().on_conflict_do_update) only where the backend supports them
• Define relationships with back_populates and explicit ForeignKey references to keep bi-directional navigation clear

Example use cases

• Define Product, User, Order, and Config models with typed fields and defaults for a commerce app
• Query all in-stock products or count records for metrics and pagination
• Perform an upsert for product inventory to atomically insert or update on id conflict
• Store user preferences in a JSON column and filter rows by nested keys (e.g., settings['theme'] == 'dark')
• Wire user→orders relationships so ORMs can load and persist cascaded changes easily