{"id":"c5975850-57f7-4736-b4c4-34ed44d9f833","shortId":"7Ky5LK","kind":"skill","title":"sqlalchemy","tagline":"Use when editing SQLAlchemy code, sqlalchemy imports, mapped_column, DeclarativeBase, ORM models, relationships, select() queries, async sessions, engines, events, or migrations.","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-05-18T19:07:39.619Z","embedding":null,"createdAt":"2026-04-23T13:04:01.800Z","updatedAt":"2026-05-18T19:07:39.619Z","lastSeenAt":"2026-05-18T19:07:39.619Z","tsv":"'/cofin/flow/blob/main/templates/styleguides/frameworks/orm.md)':944 '/cofin/flow/blob/main/templates/styleguides/general.md)':940 '/cofin/flow/blob/main/templates/styleguides/languages/python.md)':948 '/en/20/':913 '/en/20/orm/quickstart.html':916 '/en/latest/':919 '1':268,296,344 '10':197,740 '100':108,540,643 '18':260 '2':269,342,353 '2.0':24,28,404 '200':699 '255':650 '3':270,362,388 '4':372 '5':194,379,737 '5432/mydb':734 'access':313,319 'activ':574,653,760 'aggreg':284,844 'alemb':338,340,890 'alembic.sqlalchemy.org':918 'alembic.sqlalchemy.org/en/latest/':917 'alic':248 'alway':402,413,422,434,445,461 'annot':32,583,812 'applic':360,444,490 'async':17,170,178,181,183,186,203,205,213,317,320,367,385,437,452,465,485,523,526,549,556,566,605,608,722,725,728,746,748,757,766,859,875,877,897 'asyncpg':552 'asyncsess':180,208,607,751,868,880 'asyncsessionfactori':202,215,745,768 'author':679,707,715 'auto':165 'auto-clos':164 'autogener':339,892 'avoid':456 'await':219,784 'back':473,677,719 'backref':476 'bare':542 'base':77,82,621,626,682 'baselin':922 'basic':241 'bidirect':479 'bio':113 'bodi':700 'bool':655 'build':373 'bulk':847 'call':442 'cascad':833 'case':959 'chain':328 'checkpoint':394,493 'class':76,80,207,620,624,680,750,810 'claus':262,842 'close':166 'code':6,497,527,797 'column':10,34,41,52,60,86,98,106,110,118,127,309,350,352,416,418,433,500,504,507,535,616,633,641,648,657,665,689,697,705,712,814,905 'commit':158,211,449,520,754 'complet':400 'condit':250 'connect':544,855 'consid':397 'context':364,386,466,491 'core':26 'correct':548 'creat':122,140,147,177,185,354,559,604,660,727,853 'ctes':846 'data':325,895 'databas':312,318 'datetim':66,69,71,125,128,585,587,598,663,666 'declar':808 'declarativebas':11,50,57,78,307,613,622 'def':758 'default':121,132,658,670 'defin':304,345 'definit':49 'deliv':495 'detail':794,962 'docs.sqlalchemy.org':912,915 'docs.sqlalchemy.org/en/20/':911 'docs.sqlalchemy.org/en/20/orm/quickstart.html':914 'document':803 'driver':550,882 'duplic':932 'e.g':551 'eager':273,330,530 'edg':958 'edit':4 'email':644 'engin':19,141,146,148,155,179,184,187,206,355,486,606,723,726,729,749,851,854,860,863 'engine/session':878 'env.py':898 'error':460,471 'event':20,858,899,902 'exampl':557,798 'exit':168 'expir':156,209,447,518,752 'explicit':424,478,529,537 'factori':135,172,315,322,358,454,516 'fals':159,212,450,521,755 'fetch':573 'filter':412 'focus':952 'follow':802 'foreignkey':595,713 'func':67,236,599 'func.count':287 'func.now':133,671 'futur':581 'general':936 'generic':927 'get':759 'github.com':939,943,947 'github.com/cofin/flow/blob/main/templates/styleguides/frameworks/orm.md)':942 'github.com/cofin/flow/blob/main/templates/styleguides/general.md)':938 'github.com/cofin/flow/blob/main/templates/styleguides/languages/python.md)':946 'guardrail':401 'guid':795 'handl':850 'hybrid':817,903 'id':94,629,685,708 'identifi':297 'implement':343 'import':8,56,63,70,74,139,144,176,232,239,303,582,586,590,594,603,612 'index':792 'inherit':816 'int':96,427,631,687,710 'integr':961 'join':271,843 'joinedload':334,533 'keep':949 'key':100,226,302,635,691 'language/framework':928 'lazi':458,467,885 'lazy-load':457,884 'legaci':39,506 'length':538 'lifecycl':371,869 'list':674,764,788 'load':274,331,459,468,531,831,886 'localhost':733 'localhost/db':152,191 'lock':908 'manag':365 'mani':826,828,830 'many-to-mani':827 'map':9,30,33,51,58,59,87,95,97,103,105,114,117,124,126,308,348,349,415,426,428,502,503,614,615,630,632,638,640,645,647,654,656,662,664,673,686,688,694,696,701,704,709,711,716,809,811,813 'max':195,738 'merg':872 'migrat':22,337,888,893,896 'missinggreenlet':470 'mix':482 'mixin':815 'model':13,48,306,346,421,562,619,806 'multi':862 'multi-engin':861 'multipl':249 'name':102,637 'need':300 'never':44,351,377,417,439,481 'non':90 'non-opt':89 'note':883 'null':93 'nullabl':109 'offici':909 'one':824 'one-to-mani':823 'oper':848,894 'optimist':907 'option':75,91,112,115,278,429,591,702,774 'order':780 'orm':12,25,420,941 'orm/session/mapper/attribute':901 'overflow':196,739 'pass':79,151,169,190,623,732 'pattern':40,228,299,301,864,881 'ping':200,743 'pitfal':887 'pool':192,198,735,741,857 'popul':474,678,720 'post':564,578,672,675,681,684,721,763 'postgresql':149,188,730 'pre':199,742 'prefer':472 'primari':99,634,690 'principl':937 'properti':818,904,906 'psycopg':554 'python':53,136,173,229,579,945 'queri':16,227,324,374,509,571,756,837 'quick':46 'raiseload':383 'raw':440 'reduc':931 'refer':47,791,799,805,910 'references/async.md':876 'references/engine.md':852 'references/events.md':900 'references/migrations.md':889 'references/models.md':807 'references/queries.md':838 'references/relationships.md':820 'references/sessions.md':866 'referenti':836 'refresh':873 'relat':332 'relationship':14,480,524,565,617,676,718,819,821 'requir':85 'result':218,783,849 'result.scalars':224,789 'return':787 'rule':929 'run':390 'savepoint':874 'schema':336 'scope':870 'select':15,36,221,233,242,244,253,264,276,286,288,326,376,406,511,600,772,839 'selectinload':240,279,333,381,463,532,618,775 'self':835 'self-referenti':834 'server':120,131,669 'session':18,134,163,171,217,357,370,441,453,515,567,724,770,865,867,871 'session.execute':220,785 'session.query':42,378,410,514 'sessionfactori':153,161 'sessionmak':145,154,182,204,314,321,436,438,609,747 'setup':568,879,891 'share':920,924 'size':193,736 'skill':935,951 'skill-sqlalchemy' 'source-cofin' 'specif':956 'sqlalchemi':1,5,7,23,27,62,138,231,329,496,593 'sqlalchemy.ext.asyncio':175,323,602 'sqlalchemy.orm':55,143,238,310,316,335,611 'startup':361 'statement':37,840 'step':295,341,387 'stmt':243,252,263,275,285,771,786 'str':104,116,430,639,646,695,703 'strategi':832 'string':64,107,534,539,543,596,642,649,698 'style':405,512 'styleguid':921,925 'subqueri':845 'sync':311,483 'tablenam':83,627,683 'task':558 'text':65,119,597,706 'throughout':38 'timezon':129,667 'titl':693 'tool':955 'tool-specif':954 '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':469 'true':101,130,201,258,283,293,636,652,659,668,692,744,779 'type':31,73,88,425,589,822 'uniqu':651 'untyp':432 'url':545,856 'use':2,29,45,111,347,363,380,403,414,423,435,446,462,501,510,517,546,923 'user':81,84,150,189,222,223,245,254,265,277,290,407,411,561,575,625,628,717,731,761,765,773 'user.active':257,282,292,778 'user.age':259 'user.id.in':267 'user.name':247,782 'user.posts':280,776 'users.id':714 'valid':389,393,492 'verifi':498 'work':399 'workflow':294,957","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-05-18T19:07:39.619Z"}],"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-27T19:07:26Z","description":"Context-Driven Development toolkit for AI agents — spec-first planning, TDD workflow, and Beads integration.","skill_md_sha":"540a69f32e775cb7ced6168db347aac633d43bb7","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":"Use when editing SQLAlchemy code, sqlalchemy imports, mapped_column, DeclarativeBase, ORM models, relationships, select() queries, async sessions, engines, events, or migrations."},"skills_sh_url":"https://skills.sh/cofin/flow/sqlalchemy"},"updatedAt":"2026-05-18T19:07:39.619Z"}}