{"id":"c5975850-57f7-4736-b4c4-34ed44d9f833","shortId":"7Ky5LK","kind":"skill","title":"sqlalchemy","tagline":"Auto-activate for sqlalchemy imports, mapped_column, DeclarativeBase. Produces SQLAlchemy 2.0+ ORM models, async sessions, engine configurations, and query patterns. Use when: defining SQLAlchemy models, writing ORM queries, configuring engines/sessions, using select() statements","description":"# SQLAlchemy 2.0+ ORM & Core\n\nSQLAlchemy 2.0 uses `Mapped[]` type annotations, `mapped_column()`, and `select()` statements throughout. Legacy patterns (`Column()`, `session.query()`) are never used.\n\n## Quick Reference\n\n### Model Definition (DeclarativeBase + mapped_column)\n\n```python\nfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column\nfrom sqlalchemy import String, Text, DateTime, func\nfrom datetime import datetime\nfrom typing import Optional\n\nclass Base(DeclarativeBase):\n    pass\n\nclass User(Base):\n    __tablename__ = \"users\"\n\n    # Required column -- Mapped[type] (non-optional = NOT NULL)\n    id: Mapped[int] = mapped_column(primary_key=True)\n    name: Mapped[str] = mapped_column(String(100))\n\n    # Nullable column -- use Optional\n    bio: Mapped[Optional[str]] = mapped_column(Text)\n\n    # Server default\n    created_at: Mapped[datetime] = mapped_column(\n        DateTime(timezone=True), server_default=func.now()\n    )\n```\n\n### Session Factories\n\n```python\nfrom sqlalchemy import create_engine\nfrom sqlalchemy.orm import sessionmaker\n\nengine = create_engine(\"postgresql+psycopg://user:pass@localhost/db\")\nSessionFactory = sessionmaker(engine, expire_on_commit=False)\n\nwith SessionFactory() as session:\n    # auto-closed on exit\n    pass\n```\n\n### Async Session Factory\n\n```python\nfrom sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker\n\nasync_engine = create_async_engine(\n    \"postgresql+asyncpg://user:pass@localhost/db\",\n    pool_size=5, max_overflow=10, pool_pre_ping=True,\n)\n\nAsyncSessionFactory = async_sessionmaker(\n    async_engine, class_=AsyncSession, expire_on_commit=False,\n)\n\nasync with AsyncSessionFactory() as session:\n    result = await session.execute(select(User))\n    users = result.scalars().all()\n```\n\n### Key Query Patterns\n\n```python\nfrom sqlalchemy import select, and_, or_, func\nfrom sqlalchemy.orm import selectinload\n\n# Basic select\nstmt = select(User).where(User.name == \"alice\")\n\n# Multiple conditions (AND)\nstmt = select(User).where(and_(User.active == True, User.age > 18))\n\n# IN clause\nstmt = select(User).where(User.id.in_([1, 2, 3]))\n\n# Join with eager loading\nstmt = select(User).options(selectinload(User.posts)).where(User.active == True)\n\n# Aggregation\nstmt = select(func.count()).select_from(User).where(User.active == True)\n```\n\n<workflow>\n\n## Workflow\n\n### Step 1: Identify the Pattern\n\n| Need | Pattern | Key Import |\n| --- | --- | --- |\n| Define a model | `DeclarativeBase` + `mapped_column()` | `sqlalchemy.orm` |\n| Sync database access | `sessionmaker` factory | `sqlalchemy.orm` |\n| Async database access | `async_sessionmaker` factory | `sqlalchemy.ext.asyncio` |\n| Query data | `select()` + `where()` chain | `sqlalchemy` |\n| Eager load relations | `selectinload()` / `joinedload()` | `sqlalchemy.orm` |\n| Schema migration | Alembic autogenerate | `alembic` |\n\n### Step 2: Implement\n\n1. Define models using `Mapped[]` + `mapped_column()` -- never `Column()`\n2. Create engine and session factory at application startup\n3. Use context managers (`with` / `async with`) for session lifecycle\n4. Build queries with `select()` -- never `session.query()`\n5. Use `selectinload()` or `raiseload(\"*\")` in async contexts\n\n### Step 3: Validate\n\nRun through the validation checkpoint below before considering the work complete.\n\n</workflow>\n\n<guardrails>\n\n## Guardrails\n\n- **Always use 2.0-style**: `select(User).where(...)` not `session.query(User).filter(...)`\n- **Always use `mapped_column()`**: never `Column()` for ORM models\n- **Always use explicit typing**: `Mapped[int]`, `Mapped[Optional[str]]` -- no untyped columns\n- **Always use `sessionmaker` / `async_sessionmaker`**: never raw `Session()` calls in applications\n- **Always use `expire_on_commit=False`** in async session factories to avoid lazy-load errors\n- **Always use `selectinload()`** in async contexts -- lazy loading triggers `MissingGreenlet` errors\n- **Prefer `back_populates`** over `backref` for explicit bidirectional relationships\n- **Never mix sync and async engines** in the same application context\n\n</guardrails>\n\n<validation>\n\n### Validation Checkpoint\n\nBefore delivering SQLAlchemy code, verify:\n\n- [ ] All columns use `Mapped[]` + `mapped_column()` (no legacy `Column()`)\n- [ ] All queries use `select()` style (no `session.query()`)\n- [ ] Session factories use `expire_on_commit=False` for async\n- [ ] Relationships in async code have explicit eager loading (`selectinload`, `joinedload`)\n- [ ] String columns have explicit length: `String(100)`, not bare `String`\n- [ ] Connection URLs use the correct async driver (e.g., `asyncpg` not `psycopg` for async)\n\n</validation>\n\n<example>\n\n## Example\n\n**Task:** \"Create a User model with posts relationship, async session setup, and a query to fetch active users with their posts.\"\n\n```python\nfrom __future__ import annotations\n\nfrom datetime import datetime\nfrom typing import Optional\n\nfrom sqlalchemy import ForeignKey, String, Text, DateTime, func, select\nfrom sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker\nfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, selectinload\n\n\n# --- Models ---\n\nclass Base(DeclarativeBase):\n    pass\n\nclass User(Base):\n    __tablename__ = \"users\"\n\n    id: Mapped[int] = mapped_column(primary_key=True)\n    name: Mapped[str] = mapped_column(String(100))\n    email: Mapped[str] = mapped_column(String(255), unique=True)\n    active: Mapped[bool] = mapped_column(default=True)\n    created_at: Mapped[datetime] = mapped_column(\n        DateTime(timezone=True), server_default=func.now()\n    )\n\n    posts: Mapped[list[Post]] = relationship(back_populates=\"author\")\n\nclass Post(Base):\n    __tablename__ = \"posts\"\n\n    id: Mapped[int] = mapped_column(primary_key=True)\n    title: Mapped[str] = mapped_column(String(200))\n    body: Mapped[Optional[str]] = mapped_column(Text)\n    author_id: Mapped[int] = mapped_column(ForeignKey(\"users.id\"))\n\n    author: Mapped[User] = relationship(back_populates=\"posts\")\n\n\n# --- Async Engine & Session ---\n\nasync_engine = create_async_engine(\n    \"postgresql+asyncpg://user:pass@localhost:5432/mydb\",\n    pool_size=5, max_overflow=10, pool_pre_ping=True,\n)\n\nAsyncSessionFactory = async_sessionmaker(\n    async_engine, class_=AsyncSession, expire_on_commit=False,\n)\n\n\n# --- Query ---\n\nasync def get_active_users_with_posts() -> list[User]:\n    async with AsyncSessionFactory() as session:\n        stmt = (\n            select(User)\n            .options(selectinload(User.posts))\n            .where(User.active == True)\n            .order_by(User.name)\n        )\n        result = await session.execute(stmt)\n        return list(result.scalars().all())\n```\n\n</example>\n\n## References Index\n\nFor detailed guides and code examples, refer to the following documents in `references/`:\n\n- **[Models](references/models.md)** -- Declarative mapped classes, `Mapped[]` annotations, `mapped_column()`, mixins, inheritance, hybrid properties.\n- **[Relationships](references/relationships.md)** -- `relationship()` typing, one-to-many, many-to-many, loading strategies, cascades, self-referential.\n- **[Queries](references/queries.md)** -- `select()` statements, where clauses, joins, aggregations, subqueries, CTEs, bulk operations, result handling.\n- **[Engine](references/engine.md)** -- `create_engine()`, connection URLs, pooling, events, async engines, multi-engine patterns.\n- **[Sessions](references/sessions.md)** -- `Session`, `AsyncSession`, lifecycle, scoped sessions, merge, refresh, savepoints.\n- **[Async](references/async.md)** -- Async engine/session setup, `AsyncSession` patterns, driver notes, lazy-loading pitfalls.\n- **[Migrations](references/migrations.md)** -- Alembic setup, autogenerate, migration operations, data migrations, async env.py.\n- **[Events](references/events.md)** -- ORM/session/mapper/attribute events, hybrid properties, column properties, optimistic locking.\n\n## Official References\n\n- <https://docs.sqlalchemy.org/en/20/>\n- <https://docs.sqlalchemy.org/en/20/orm/quickstart.html>\n- <https://alembic.sqlalchemy.org/en/latest/>\n\n## Shared Styleguide Baseline\n\n- Use shared styleguides for generic language/framework rules to reduce duplication in this skill.\n- [General Principles](https://github.com/cofin/flow/blob/main/templates/styleguides/general.md)\n- [ORM](https://github.com/cofin/flow/blob/main/templates/styleguides/frameworks/orm.md)\n- [Python](https://github.com/cofin/flow/blob/main/templates/styleguides/languages/python.md)\n- Keep this skill focused on tool-specific workflows, edge cases, and integration details.","tags":["sqlalchemy","flow","cofin","agent-skills","ai-agents","beads","claude-code","codex","cursor","developer-tools","gemini-cli","opencode"],"capabilities":["skill","source-cofin","skill-sqlalchemy","topic-agent-skills","topic-ai-agents","topic-beads","topic-claude-code","topic-codex","topic-cursor","topic-developer-tools","topic-gemini-cli","topic-opencode","topic-plugin","topic-slash-commands","topic-spec-driven-development"],"categories":["flow"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/cofin/flow/sqlalchemy","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add cofin/flow","source_repo":"https://github.com/cofin/flow","install_from":"skills.sh"}},"qualityScore":"0.455","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 11 github stars · SKILL.md body (8,612 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-04-24T07:03:20.148Z","embedding":null,"createdAt":"2026-04-23T13:04:01.800Z","updatedAt":"2026-04-24T07:03:20.148Z","lastSeenAt":"2026-04-24T07:03:20.148Z","tsv":"'/cofin/flow/blob/main/templates/styleguides/frameworks/orm.md)':957 '/cofin/flow/blob/main/templates/styleguides/general.md)':953 '/cofin/flow/blob/main/templates/styleguides/languages/python.md)':961 '/en/20/':926 '/en/20/orm/quickstart.html':929 '/en/latest/':932 '1':281,309,357 '10':210,753 '100':121,553,656 '18':273 '2':282,355,366 '2.0':13,37,41,417 '200':712 '255':663 '3':283,375,401 '4':385 '5':207,392,750 '5432/mydb':747 'access':326,332 'activ':4,587,666,773 'aggreg':297,857 'alemb':351,353,903 'alembic.sqlalchemy.org':931 'alembic.sqlalchemy.org/en/latest/':930 'alic':261 'alway':415,426,435,447,458,474 'annot':45,596,825 'applic':373,457,503 'async':16,183,191,194,196,199,216,218,226,330,333,380,398,450,465,478,498,536,539,562,569,579,618,621,735,738,741,759,761,770,779,872,888,890,910 'asyncpg':565 'asyncsess':193,221,620,764,881,893 'asyncsessionfactori':215,228,758,781 'author':692,720,728 'auto':3,178 'auto-activ':2 'auto-clos':177 'autogener':352,905 'avoid':469 'await':232,797 'back':486,690,732 'backref':489 'bare':555 'base':90,95,634,639,695 'baselin':935 'basic':254 'bidirect':492 'bio':126 'bodi':713 'bool':668 'build':386 'bulk':860 'call':455 'cascad':846 'case':972 'chain':341 'checkpoint':407,506 'class':89,93,220,633,637,693,763,823 'claus':275,855 'close':179 'code':510,540,810 'column':9,47,54,65,73,99,111,119,123,131,140,322,363,365,429,431,446,513,517,520,548,629,646,654,661,670,678,702,710,718,725,827,918 'commit':171,224,462,533,767 'complet':413 'condit':263 'configur':19,31 'connect':557,868 'consid':410 'context':377,399,479,504 'core':39 'correct':561 'creat':135,153,160,190,198,367,572,617,673,740,866 'ctes':859 'data':338,908 'databas':325,331 'datetim':79,82,84,138,141,598,600,611,676,679 'declar':821 'declarativebas':10,63,70,91,320,626,635 'def':771 'default':134,145,671,683 'defin':25,317,358 'definit':62 'deliv':508 'detail':807,975 'docs.sqlalchemy.org':925,928 'docs.sqlalchemy.org/en/20/':924 'docs.sqlalchemy.org/en/20/orm/quickstart.html':927 'document':816 'driver':563,895 'duplic':945 'e.g':564 'eager':286,343,543 'edg':971 'email':657 'engin':18,154,159,161,168,192,197,200,219,368,499,619,736,739,742,762,864,867,873,876 'engine/session':891 'engines/sessions':32 'env.py':911 'error':473,484 'event':871,912,915 'exampl':570,811 'exit':181 'expir':169,222,460,531,765 'explicit':437,491,542,550 'factori':148,185,328,335,371,467,529 'fals':172,225,463,534,768 'fetch':586 'filter':425 'focus':965 'follow':815 'foreignkey':608,726 'func':80,249,612 'func.count':300 'func.now':146,684 'futur':594 'general':949 'generic':940 'get':772 'github.com':952,956,960 'github.com/cofin/flow/blob/main/templates/styleguides/frameworks/orm.md)':955 'github.com/cofin/flow/blob/main/templates/styleguides/general.md)':951 'github.com/cofin/flow/blob/main/templates/styleguides/languages/python.md)':959 'guardrail':414 'guid':808 'handl':863 'hybrid':830,916 'id':107,642,698,721 'identifi':310 'implement':356 'import':7,69,76,83,87,152,157,189,245,252,316,595,599,603,607,616,625 'index':805 'inherit':829 'int':109,440,644,700,723 'integr':974 'join':284,856 'joinedload':347,546 'keep':962 'key':113,239,315,648,704 'language/framework':941 'lazi':471,480,898 'lazy-load':470,897 'legaci':52,519 'length':551 'lifecycl':384,882 'list':687,777,801 'load':287,344,472,481,544,844,899 'localhost':746 'localhost/db':165,204 'lock':921 'manag':378 'mani':839,841,843 'many-to-mani':840 'map':8,43,46,64,71,72,100,108,110,116,118,127,130,137,139,321,361,362,428,439,441,515,516,627,628,643,645,651,653,658,660,667,669,675,677,686,699,701,707,709,714,717,722,724,729,822,824,826 'max':208,751 'merg':885 'migrat':350,901,906,909 'missinggreenlet':483 'mix':495 'mixin':828 'model':15,27,61,319,359,434,575,632,819 'multi':875 'multi-engin':874 'multipl':262 'name':115,650 'need':313 'never':57,364,390,430,452,494 'non':103 'non-opt':102 'note':896 'null':106 'nullabl':122 'offici':922 'one':837 'one-to-mani':836 'oper':861,907 'optimist':920 'option':88,104,125,128,291,442,604,715,787 'order':793 'orm':14,29,38,433,954 'orm/session/mapper/attribute':914 'overflow':209,752 'pass':92,164,182,203,636,745 'pattern':22,53,241,312,314,877,894 'ping':213,756 'pitfal':900 'pool':205,211,748,754,870 'popul':487,691,733 'post':577,591,685,688,694,697,734,776 'postgresql':162,201,743 'pre':212,755 'prefer':485 'primari':112,647,703 'principl':950 'produc':11 'properti':831,917,919 'psycopg':567 'python':66,149,186,242,592,958 'queri':21,30,240,337,387,522,584,769,850 'quick':59 'raiseload':396 'raw':453 'reduc':944 'refer':60,804,812,818,923 'references/async.md':889 'references/engine.md':865 'references/events.md':913 'references/migrations.md':902 'references/models.md':820 'references/queries.md':851 'references/relationships.md':833 'references/sessions.md':879 'referenti':849 'refresh':886 'relat':345 'relationship':493,537,578,630,689,731,832,834 'requir':98 'result':231,796,862 'result.scalars':237,802 'return':800 'rule':942 'run':403 'savepoint':887 'schema':349 'scope':883 'select':34,49,234,246,255,257,266,277,289,299,301,339,389,419,524,613,785,852 'selectinload':253,292,346,394,476,545,631,788 'self':848 'self-referenti':847 'server':133,144,682 'session':17,147,176,184,230,370,383,454,466,528,580,737,783,878,880,884 'session.execute':233,798 'session.query':55,391,423,527 'sessionfactori':166,174 'sessionmak':158,167,195,217,327,334,449,451,622,760 'setup':581,892,904 'share':933,937 'size':206,749 'skill':948,964 'skill-sqlalchemy' 'source-cofin' 'specif':969 'sqlalchemi':1,6,12,26,36,40,75,151,244,342,509,606 'sqlalchemy.ext.asyncio':188,336,615 'sqlalchemy.orm':68,156,251,323,329,348,624 'startup':374 'statement':35,50,853 'step':308,354,400 'stmt':256,265,276,288,298,784,799 'str':117,129,443,652,659,708,716 'strategi':845 'string':77,120,547,552,556,609,655,662,711 'style':418,525 'styleguid':934,938 'subqueri':858 'sync':324,496 'tablenam':96,640,696 'task':571 'text':78,132,610,719 'throughout':51 'timezon':142,680 'titl':706 'tool':968 'tool-specif':967 'topic-agent-skills' 'topic-ai-agents' 'topic-beads' 'topic-claude-code' 'topic-codex' 'topic-cursor' 'topic-developer-tools' 'topic-gemini-cli' 'topic-opencode' 'topic-plugin' 'topic-slash-commands' 'topic-spec-driven-development' 'trigger':482 'true':114,143,214,271,296,306,649,665,672,681,705,757,792 'type':44,86,101,438,602,835 'uniqu':664 'untyp':445 'url':558,869 'use':23,33,42,58,124,360,376,393,416,427,436,448,459,475,514,523,530,559,936 'user':94,97,163,202,235,236,258,267,278,290,303,420,424,574,588,638,641,730,744,774,778,786 'user.active':270,295,305,791 'user.age':272 'user.id.in':280 'user.name':260,795 'user.posts':293,789 'users.id':727 'valid':402,406,505 'verifi':511 'work':412 'workflow':307,970 'write':28","prices":[{"id":"70dbbe3a-fb12-436b-b36e-dc4d9bab448b","listingId":"c5975850-57f7-4736-b4c4-34ed44d9f833","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"cofin","category":"flow","install_from":"skills.sh"},"createdAt":"2026-04-23T13:04:01.800Z"}],"sources":[{"listingId":"c5975850-57f7-4736-b4c4-34ed44d9f833","source":"github","sourceId":"cofin/flow/sqlalchemy","sourceUrl":"https://github.com/cofin/flow/tree/main/skills/sqlalchemy","isPrimary":false,"firstSeenAt":"2026-04-23T13:04:01.800Z","lastSeenAt":"2026-04-24T07:03:20.148Z"}],"details":{"listingId":"c5975850-57f7-4736-b4c4-34ed44d9f833","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"cofin","slug":"sqlalchemy","github":{"repo":"cofin/flow","stars":11,"topics":["agent-skills","ai-agents","beads","claude-code","codex","context-driven-development","cursor","developer-tools","gemini-cli","opencode","plugin","slash-commands","spec-driven-development","subagents","tdd","workflow"],"license":"apache-2.0","html_url":"https://github.com/cofin/flow","pushed_at":"2026-04-19T23:22:27Z","description":"Context-Driven Development toolkit for AI agents — spec-first planning, TDD workflow, and Beads integration.","skill_md_sha":"ca613d875ccee1f2c7ddb9016a09c0e4d2fb2cf3","skill_md_path":"skills/sqlalchemy/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/cofin/flow/tree/main/skills/sqlalchemy"},"layout":"multi","source":"github","category":"flow","frontmatter":{"name":"sqlalchemy","description":"Auto-activate for sqlalchemy imports, mapped_column, DeclarativeBase. Produces SQLAlchemy 2.0+ ORM models, async sessions, engine configurations, and query patterns. Use when: defining SQLAlchemy models, writing ORM queries, configuring engines/sessions, using select() statements, managing relationships, or working with asyncio sessions. Not for raw SQL without ORM (see postgres/mysql), or Advanced Alchemy patterns (see advanced-alchemy)."},"skills_sh_url":"https://skills.sh/cofin/flow/sqlalchemy"},"updatedAt":"2026-04-24T07:03:20.148Z"}}