skills.rest
bm629
bm629COMMUNITY
Explore all skills by this author →
GitHub

sqlalchemy

Build a portable SQLAlchemy 2.x ORM data layer across SQLite, PostgreSQL, and MySQL.

1|1|Updated May 24, 2026
Search Tags:#database#postgresql#python#sqlite#orm#mysql#sqlalchemy
One-click install
npx skills add https://github.com/bm629/agent-skills --skill sqlalchemy-bm629
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sqlalchemy
Source: https://github.com/bm629/agent-skills/tree/main/skills/sqlalchemy
Command: npx skills add https://github.com/bm629/agent-skills --skill sqlalchemy-bm629

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Code that works on SQLite often breaks on PostgreSQL or MySQL because row locking, upsert syntax, JSON handling, autoincrement, and transaction isolation defaults differ per engine. This Skill guides an agent to write one typed SQLAlchemy 2.x data layer that runs correctly on all three dialects. ## Core Features & Use Cases - Typed 2.x ORM models: Define models with DeclarativeBase, Mapped[...], and mapped_column, with nullability derived from type annotations. - Engine, pooling, and sessions: Create one engine per process with correct pool configuration, and use short-lived session-scoped transactions, sync-first with an async (AsyncSession) aside. - Cross-dialect gotcha matrix: Branch correctly for row locking (with_for_update / SKIP LOCKED), JSON vs JSONB, upsert (ON CONFLICT vs ON DUPLICATE KEY UPDATE), autoincrement, and isolation levels. - Use Case: You are building a service that must run locally on SQLite for tests but deploy to PostgreSQL or MySQL. Use this Skill to set up the engine, models, and per-dialect upsert and locking branches so nothing silently breaks when you switch engines. ## Quick Start Use the sqlalchemy skill to set up a typed SQLAlchemy 2.x data layer with models and sessions that runs on SQLite, PostgreSQL, and MySQL.

Frequently Asked Questions about sqlalchemy

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I define SQLAlchemy 2.x ORM models with type annotations?▼

Subclass DeclarativeBase and annotate columns with Mapped[...] plus mapped_column(...). A bare Mapped[str] is NOT NULL while Mapped[str | None] is nullable, and an integer primary key autoincrements on SQLite, PostgreSQL, and MySQL. Never use the legacy Column = or declarative_base() form.

How do I write an upsert that works on PostgreSQL, MySQL, and SQLite?▼

There is no portable upsert, so branch per dialect. PostgreSQL and SQLite use insert(...).on_conflict_do_update with stmt.excluded, while MySQL uses insert(...).on_duplicate_key_update with stmt.inserted. Import insert from the matching sqlalchemy.dialects module.

Does with_for_update row locking work on SQLite?▼

No. SQLite has no row-level locks, so with_for_update is silently accepted but does nothing. PostgreSQL supports it fully and MySQL requires 8.0+ InnoDB for SKIP LOCKED. A portable data layer cannot rely on row locking when SQLite is a target.

Which database driver should I use for async SQLAlchemy?▼

Use aiosqlite for SQLite, asyncpg for PostgreSQL, and aiomysql for MySQL, selected via the +driver segment of the connection URL. The sync defaults are sqlite3, psycopg v3, and PyMySQL. Reach for async only when the surrounding stack is already async.

Why does my async SQLAlchemy code raise errors after commit?▼

After an async commit, accessing an unloaded attribute triggers a lazy load, which requires implicit I/O that async forbids. Set expire_on_commit=False on the sessionmaker or eager-load relationships with selectinload before the commit.

When should I not use this SQLAlchemy skill?▼

Do not use it for schema migrations or ALTER TABLE changes, which belong to Alembic, or for job-queue and lease patterns, which belong to a dedicated job-queue skill. It also does not cover Pydantic validation, other ORMs like Django or SQLModel, or raw DBAPI usage.

Related Skills

Explore More Software Engineering Skills→
project-axproject-ax
5

ax-provider-database

Manage SQLite and PostgreSQL connections for AX applications via a unified factory.

Official
Intermediate
stars-endstars-end

context-database-schema

Manage PostgreSQL/Supabase schema migrations with Alembic and RLS policies.

Official
Advanced
the-perfect-developerthe-perfect-developer
11

pydantic

Implement Pydantic v2 data validation and serialization with ConfigDict.

Community
Intermediate
oornneryoornnery

sqlmodel

Coordinate SQLModel persistence with async sessions, relationships, and Alembic migrations.

Community
Advanced