mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
hanzoskills.com/llms.txt now opens with the install line, the API reference index and the company index, so an agent that lands on any one of the three can find the rest. robots.txt claimed 537 documents while llms.txt counts 920. The number is gone from robots.txt; llms.txt derives it. public/ is regenerated from scripts/site.py, which it had fallen behind: the copy-prompt menu and agent bar were in the generator and not in the committed pages. tests/site.mjs passes against the result. Co-authored-by: Hanzo Dev <[email protected]>
859 lines
37 KiB
HTML
859 lines
37 KiB
HTML
<!doctype html>
|
|
<html lang="en"><head>
|
|
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
|
|
<title>database-orm-patterns</title>
|
|
<meta name="color-scheme" content="dark">
|
|
<style>
|
|
/* Committed to one visual world: a dark instrument panel. No light variant —
|
|
the dot field and the hairline rules only read on black, and a naive
|
|
inversion would wash both out. Every colour is painted explicitly so the page
|
|
never borrows a host background. */
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
|
|
|
|
:root{
|
|
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
|
|
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
|
|
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
|
|
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
|
|
--font-zen-pixel-square:'Zen Pixel Square',monospace;
|
|
--mono:var(--font-zen-mono);
|
|
--sans:var(--font-zen-sans);
|
|
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
|
|
--r:999px; --r-soft:14px;
|
|
}
|
|
*{box-sizing:border-box}
|
|
html{-webkit-text-size-adjust:100%}
|
|
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
|
|
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
|
|
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
|
|
|
|
/* The dot field. A halftone made of one repeating radial-gradient, faded out
|
|
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
|
|
/* No overflow clip here: the dot field is inset to the box already, and a clip
|
|
would cut off the copy-prompt menu. */
|
|
.hero{position:relative;border-bottom:1px solid var(--line)}
|
|
.hero::before{content:"";position:absolute;inset:0;
|
|
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
|
|
background-size:7px 7px;
|
|
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
pointer-events:none}
|
|
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
|
|
|
|
nav{border-bottom:1px solid var(--line)}
|
|
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
|
|
justify-content:space-between;align-items:center;gap:1rem}
|
|
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--ink);text-decoration:none;
|
|
display:inline-flex;align-items:center;gap:.6rem}
|
|
.brand i{font-style:normal;color:var(--faint)}
|
|
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
|
|
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--soft);text-decoration:none}
|
|
nav a.up:hover{color:var(--ink)}
|
|
|
|
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
|
|
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
|
|
h1 em{font-style:normal;color:var(--faint)}
|
|
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
|
|
|
|
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
|
|
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
|
|
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
|
|
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
|
|
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
|
|
line-height:1.2;font-weight:400}
|
|
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
|
|
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
|
|
.btn.solid:hover{background:#d8d8d4}
|
|
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
|
|
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
|
|
|
|
/* Copy-prompt selector. Each row copies the prompt for its own target. */
|
|
.copy{position:relative;display:inline-flex}
|
|
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
|
|
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
|
|
padding:.4rem;display:flex;flex-direction:column;
|
|
box-shadow:0 12px 32px rgba(0,0,0,.75)}
|
|
.menu[hidden]{display:none}
|
|
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
|
|
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
|
|
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
|
|
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
|
|
white-space:nowrap}
|
|
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
|
|
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
|
|
.mi:hover .mark{color:var(--ink)}
|
|
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
|
|
letter-spacing:.1em;text-transform:uppercase}
|
|
|
|
/* Works-with bar: one scrolling row, name and mark per agent. */
|
|
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
|
|
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
|
|
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
|
|
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
|
|
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
|
|
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
|
|
display:flex;align-items:center}
|
|
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
|
|
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
|
|
-webkit-overflow-scrolling:touch;
|
|
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
|
|
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
|
|
.bar::-webkit-scrollbar{display:none}
|
|
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
|
|
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
|
|
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
|
|
text-decoration:none;background:var(--ground)}
|
|
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
|
|
.agent .mark{width:.95rem;height:.95rem}
|
|
|
|
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
|
|
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
|
|
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
|
|
p{margin:0 0 1rem;max-width:70ch}
|
|
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
|
|
li{margin:.22rem 0}
|
|
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
|
|
text-decoration-color:var(--line-hi)}
|
|
a:hover{text-decoration-color:var(--ink)}
|
|
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
|
|
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
|
|
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
|
|
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
|
|
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
|
|
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
|
|
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
|
|
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
|
|
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
|
|
display:block;overflow-x:auto}
|
|
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
|
|
|
|
.curl{display:block;background:var(--panel);border:1px solid var(--line);
|
|
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
|
|
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
|
|
white-space:pre;line-height:1.9}
|
|
.curl b{color:var(--ink);font-weight:400}
|
|
.curl i{font-style:normal;color:var(--faint)}
|
|
|
|
/* index */
|
|
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
|
|
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
|
|
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
|
|
#q:focus{outline:none;border-color:var(--soft)}
|
|
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
|
|
text-transform:uppercase;margin:0 0 2rem}
|
|
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
|
|
padding-bottom:.45rem}
|
|
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
|
|
border-radius:var(--r-soft);text-decoration:none}
|
|
.row:hover{background:#0a0a0a}
|
|
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
|
|
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
|
|
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
|
|
color:var(--faint);font-size:.85rem}
|
|
footer a{color:var(--soft)}
|
|
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
|
|
margin:0 0 2rem;font-size:.85rem}
|
|
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
|
|
border-bottom:1px solid var(--line);padding-bottom:.4rem}
|
|
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
|
|
.fmap a:hover{text-decoration:underline}
|
|
.fmap div{color:var(--faint);line-height:1.5}
|
|
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
|
|
|
|
@media (max-width:640px){
|
|
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
|
|
.btns{gap:.5rem}
|
|
.btn{padding:.75rem 1.15rem;font-size:.68rem}
|
|
.copy,.copy .btn{width:100%;justify-content:center}
|
|
.menu{left:0;right:0;min-width:0}
|
|
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
|
|
.strip b{border-right:0;padding-right:0}
|
|
.bar{width:100%}
|
|
}
|
|
</style>
|
|
<link rel="alternate" type="text/markdown" href="/database-orm-patterns.md">
|
|
|
|
<link rel="icon" href="/favicon.svg">
|
|
<link rel="canonical" href="https://hanzoskills.com/database-orm-patterns">
|
|
</head>
|
|
<!-- Reading this with a machine? Every page here is also plain markdown.
|
|
This one: https://hanzoskills.com/database-orm-patterns.md
|
|
Index: https://hanzoskills.com/llms.txt
|
|
Corpus: https://hanzoskills.com/all.md
|
|
Append .md to any path for the markdown twin. -->
|
|
<body>
|
|
<nav><div class="in">
|
|
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
|
|
<a class="up" href="/">All skills</a>
|
|
</div></nav>
|
|
|
|
<div class="wrap">
|
|
<h1>database-orm-patterns</h1><p class="lede">Working with ORMs (SQLAlchemy, Prisma, GORM, Diesel)</p><h1>ORM Patterns</h1>
|
|
<p><strong>Scope</strong>: ORM usage patterns, N+1 prevention, transactions, best practices <strong>Lines</strong>: ~300 <strong>Last Updated</strong>: 2025-10-18</p>
|
|
<h2>When to Use This Skill</h2>
|
|
<p>Activate this skill when:</p>
|
|
<ul><li>Working with ORMs (SQLAlchemy, Prisma, GORM, Diesel)</li><li>Debugging N+1 query problems</li><li>Implementing eager loading strategies</li><li>Managing database transactions</li><li>Optimizing ORM query performance</li><li>Choosing between ORM and raw SQL</li><li>Performing batch operations</li></ul>
|
|
<h2>Core Concepts</h2>
|
|
<h3>ORM vs Raw SQL</h3>
|
|
<p><strong>ORM (Object-Relational Mapping)</strong>: Maps database tables to objects/classes.</p>
|
|
<p><strong>Benefits</strong>:</p>
|
|
<ul><li>Type safety (especially in typed languages)</li><li>Abstraction over SQL dialects</li><li>Protection against SQL injection</li><li>Easier refactoring</li><li>Built-in migrations</li></ul>
|
|
<p><strong>Drawbacks</strong>:</p>
|
|
<ul><li>Performance overhead</li><li>Complex queries can be verbose</li><li>Hidden N+1 problems</li><li>Learning curve for advanced features</li></ul>
|
|
<h3>When to Use Each</h3>
|
|
<pre><code>Use ORM when:
|
|
├─ Simple CRUD operations
|
|
├─ Type safety is critical
|
|
├─ Working with relationships
|
|
├─ Need cross-database compatibility
|
|
└─ Team prefers ORM patterns
|
|
|
|
Use Raw SQL when:
|
|
├─ Complex analytics queries
|
|
├─ Performance-critical paths
|
|
├─ Bulk operations (1000+ rows)
|
|
├─ Database-specific features needed
|
|
└─ ORM generates suboptimal queries</code></pre>
|
|
<hr>
|
|
<h2>The N+1 Query Problem</h2>
|
|
<h3>What is N+1?</h3>
|
|
<p><strong>Problem</strong>: Loading a collection of N items, then making 1 query per item to load related data.</p>
|
|
<p><strong>Example</strong>: Load 100 users, then query orders for each user = <strong>101 queries</strong> (1 + 100).</p>
|
|
<h3>Detection</h3>
|
|
<p><strong>Symptoms</strong>:</p>
|
|
<ul><li>Slow page loads despite simple data</li><li>Database query count >> expected</li><li>Repeated similar queries with different IDs</li></ul>
|
|
<p><strong>Logging</strong>:</p>
|
|
<pre><code class="lang-python"># SQLAlchemy: Enable query logging
|
|
import logging
|
|
logging.basicConfig()
|
|
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)</code></pre>
|
|
<pre><code class="lang-typescript">// Prisma: Enable query logging
|
|
const prisma = new PrismaClient({
|
|
log: ['query', 'info', 'warn', 'error'],
|
|
})</code></pre>
|
|
<pre><code class="lang-go">// GORM: Enable query logging
|
|
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
|
|
Logger: logger.Default.LogMode(logger.Info),
|
|
})</code></pre>
|
|
<pre><code class="lang-rust">// Diesel: No built-in logging, use diesel::debug_query
|
|
let query = users::table.filter(users::id.eq(1));
|
|
println!("{}", diesel::debug_query::<diesel::pg::Pg, _>(&query));</code></pre>
|
|
<h3>N+1 Example (Anti-Pattern)</h3>
|
|
<p><strong>SQLAlchemy (Python)</strong>:</p>
|
|
<pre><code class="lang-python"># ❌ N+1 PROBLEM: 1 query for users + N queries for orders
|
|
users = session.query(User).all() # 1 query
|
|
for user in users:
|
|
orders = user.orders # N queries (lazy load)
|
|
print(f"{user.name}: {len(orders)} orders")</code></pre>
|
|
<p><strong>Prisma (TypeScript)</strong>:</p>
|
|
<pre><code class="lang-typescript">// ❌ N+1 PROBLEM
|
|
const users = await prisma.user.findMany(); // 1 query
|
|
for (const user of users) {
|
|
const orders = await prisma.order.findMany({ // N queries
|
|
where: { userId: user.id }
|
|
});
|
|
console.log(`${user.name}: ${orders.length} orders`);
|
|
}</code></pre>
|
|
<p><strong>GORM (Go)</strong>:</p>
|
|
<pre><code class="lang-go">// ❌ N+1 PROBLEM
|
|
var users []User
|
|
db.Find(&users) // 1 query
|
|
for _, user := range users {
|
|
var orders []Order
|
|
db.Where("user_id = ?", user.ID).Find(&orders) // N queries
|
|
fmt.Printf("%s: %d orders\n", user.Name, len(orders))
|
|
}</code></pre>
|
|
<p><strong>Diesel (Rust)</strong>:</p>
|
|
<pre><code class="lang-rust">// ❌ N+1 PROBLEM
|
|
let users = users::table.load::<User>(&mut conn)?; // 1 query
|
|
for user in &users {
|
|
let orders = Order::belonging_to(user) // N queries
|
|
.load::<Order>(&mut conn)?;
|
|
println!("{}: {} orders", user.name, orders.len());
|
|
}</code></pre>
|
|
<hr>
|
|
<h2>Eager Loading Solutions</h2>
|
|
<h3>SQLAlchemy: joinedload / selectinload</h3>
|
|
<p><strong>joinedload</strong>: Single query with JOIN</p>
|
|
<pre><code class="lang-python">from sqlalchemy.orm import joinedload
|
|
|
|
# ✅ SOLUTION: 1 query with LEFT OUTER JOIN
|
|
users = session.query(User).options(joinedload(User.orders)).all()
|
|
for user in users:
|
|
orders = user.orders # No additional query
|
|
print(f"{user.name}: {len(orders)} orders")
|
|
|
|
# Generated SQL:
|
|
# SELECT users.*, orders.*
|
|
# FROM users LEFT OUTER JOIN orders ON users.id = orders.user_id</code></pre>
|
|
<p><strong>selectinload</strong>: Two queries (1 for users, 1 for all orders)</p>
|
|
<pre><code class="lang-python">from sqlalchemy.orm import selectinload
|
|
|
|
# ✅ SOLUTION: 2 queries total
|
|
users = session.query(User).options(selectinload(User.orders)).all()
|
|
|
|
# Query 1: SELECT * FROM users
|
|
# Query 2: SELECT * FROM orders WHERE user_id IN (?, ?, ...)</code></pre>
|
|
<p><strong>When to use which</strong>:</p>
|
|
<ul><li><code>joinedload</code>: Few related items, need data in single query</li><li><code>selectinload</code>: Many related items (avoids cartesian product)</li><li><code>lazyload</code>: Default (lazy), only load when accessed (causes N+1)</li><li><code>noload</code>: Don't load relationship at all</li></ul>
|
|
<p><strong>Multiple relationships</strong>:</p>
|
|
<pre><code class="lang-python">users = session.query(User).options(
|
|
selectinload(User.orders).selectinload(Order.items), # Nested
|
|
joinedload(User.profile) # Separate relationship
|
|
).all()</code></pre>
|
|
<h3>Prisma: include</h3>
|
|
<p><strong>include</strong>: Eager load relationships</p>
|
|
<pre><code class="lang-typescript">// ✅ SOLUTION: Single operation with nested query
|
|
const users = await prisma.user.findMany({
|
|
include: {
|
|
orders: true, // Load orders
|
|
}
|
|
});
|
|
|
|
// Or with nested includes
|
|
const users = await prisma.user.findMany({
|
|
include: {
|
|
orders: {
|
|
include: {
|
|
items: true // Load order items
|
|
}
|
|
},
|
|
profile: true // Load user profile
|
|
}
|
|
});</code></pre>
|
|
<p><strong>select</strong>: Load specific fields only</p>
|
|
<pre><code class="lang-typescript">// Optimize by selecting only needed fields
|
|
const users = await prisma.user.findMany({
|
|
select: {
|
|
id: true,
|
|
name: true,
|
|
orders: {
|
|
select: {
|
|
id: true,
|
|
total: true,
|
|
}
|
|
}
|
|
}
|
|
});</code></pre>
|
|
<h3>GORM: Preload / Joins</h3>
|
|
<p><strong>Preload</strong>: Separate queries (like selectinload)</p>
|
|
<pre><code class="lang-go">// ✅ SOLUTION: 2 queries total
|
|
var users []User
|
|
db.Preload("Orders").Find(&users)
|
|
|
|
// Query 1: SELECT * FROM users
|
|
// Query 2: SELECT * FROM orders WHERE user_id IN (?, ?, ...)
|
|
|
|
// Nested preload
|
|
db.Preload("Orders.Items").Preload("Profile").Find(&users)</code></pre>
|
|
<p><strong>Joins</strong>: Single query with JOIN</p>
|
|
<pre><code class="lang-go">// ✅ SOLUTION: 1 query with JOIN
|
|
var users []User
|
|
db.Joins("Orders").Find(&users)
|
|
|
|
// SELECT users.*, orders.* FROM users
|
|
// LEFT JOIN orders ON orders.user_id = users.id</code></pre>
|
|
<p><strong>Preload with conditions</strong>:</p>
|
|
<pre><code class="lang-go">db.Preload("Orders", "status = ?", "completed").Find(&users)</code></pre>
|
|
<h3>Diesel: Associations</h3>
|
|
<p><strong>Manual approach</strong> (Diesel doesn't have built-in eager loading):</p>
|
|
<pre><code class="lang-rust">use diesel::prelude::*;
|
|
|
|
// ✅ SOLUTION: 2 queries manually executed
|
|
let users = users::table.load::<User>(&mut conn)?;
|
|
let user_ids: Vec<i32> = users.iter().map(|u| u.id).collect();
|
|
|
|
// Single query for all orders
|
|
let orders = orders::table
|
|
.filter(orders::user_id.eq_any(&user_ids))
|
|
.load::<Order>(&mut conn)?;
|
|
|
|
// Group orders by user_id in application code
|
|
let orders_by_user: HashMap<i32, Vec<Order>> = orders
|
|
.into_iter()
|
|
.fold(HashMap::new(), |mut acc, order| {
|
|
acc.entry(order.user_id).or_insert_with(Vec::new).push(order);
|
|
acc
|
|
});
|
|
|
|
for user in &users {
|
|
let user_orders = orders_by_user.get(&user.id).unwrap_or(&vec![]);
|
|
println!("{}: {} orders", user.name, user_orders.len());
|
|
}</code></pre>
|
|
<p><strong>Using diesel-async with joins</strong>:</p>
|
|
<pre><code class="lang-rust">// Single query with JOIN
|
|
let results = users::table
|
|
.left_join(orders::table)
|
|
.select((User::as_select(), Option::<Order>::as_select()))
|
|
.load::<(User, Option<Order>)>(&mut conn)?;
|
|
|
|
// Group by user in application</code></pre>
|
|
<hr>
|
|
<h2>Transaction Management</h2>
|
|
<h3>SQLAlchemy</h3>
|
|
<p><strong>Context manager (recommended)</strong>:</p>
|
|
<pre><code class="lang-python">from sqlalchemy.orm import Session
|
|
|
|
# ✅ Automatic commit/rollback
|
|
with Session(engine) as session:
|
|
user = User(name="Alice")
|
|
session.add(user)
|
|
session.commit() # Explicit commit
|
|
|
|
# Or with automatic commit on exit
|
|
with Session(engine, expire_on_commit=False) as session:
|
|
with session.begin():
|
|
user = User(name="Bob")
|
|
session.add(user)
|
|
# Auto-commit on exit, rollback on exception</code></pre>
|
|
<p><strong>Manual transaction</strong>:</p>
|
|
<pre><code class="lang-python">session = Session(engine)
|
|
try:
|
|
user = User(name="Charlie")
|
|
session.add(user)
|
|
session.commit()
|
|
except Exception as e:
|
|
session.rollback()
|
|
raise
|
|
finally:
|
|
session.close()</code></pre>
|
|
<p><strong>Nested transactions (savepoints)</strong>:</p>
|
|
<pre><code class="lang-python">with session.begin():
|
|
user = User(name="Dave")
|
|
session.add(user)
|
|
|
|
with session.begin_nested(): # Savepoint
|
|
order = Order(user_id=user.id)
|
|
session.add(order)
|
|
# Rollback this savepoint if error, user still committed</code></pre>
|
|
<h3>Prisma</h3>
|
|
<p><strong>Implicit transactions</strong> (single operations are atomic):</p>
|
|
<pre><code class="lang-typescript">// Automatically wrapped in transaction
|
|
await prisma.user.create({
|
|
data: {
|
|
name: "Alice",
|
|
orders: {
|
|
create: [
|
|
{ total: 100 },
|
|
{ total: 200 }
|
|
]
|
|
}
|
|
}
|
|
});</code></pre>
|
|
<p><strong>Explicit transactions</strong>:</p>
|
|
<pre><code class="lang-typescript">// ✅ Manual transaction for multiple operations
|
|
await prisma.$transaction(async (tx) => {
|
|
const user = await tx.user.create({
|
|
data: { name: "Bob" }
|
|
});
|
|
|
|
await tx.order.create({
|
|
data: {
|
|
userId: user.id,
|
|
total: 150
|
|
}
|
|
});
|
|
|
|
// Rollback automatically on error
|
|
});</code></pre>
|
|
<p><strong>Sequential transactions</strong> (array form):</p>
|
|
<pre><code class="lang-typescript">// All-or-nothing: all succeed or all rollback
|
|
await prisma.$transaction([
|
|
prisma.user.create({ data: { name: "Charlie" }}),
|
|
prisma.order.create({ data: { userId: 1, total: 100 }}),
|
|
]);</code></pre>
|
|
<p><strong>Transaction isolation levels</strong>:</p>
|
|
<pre><code class="lang-typescript">await prisma.$transaction(
|
|
async (tx) => {
|
|
// Transaction operations
|
|
},
|
|
{
|
|
isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
|
|
maxWait: 5000, // Wait up to 5 seconds
|
|
timeout: 10000, // Transaction timeout
|
|
}
|
|
);</code></pre>
|
|
<h3>GORM</h3>
|
|
<p><strong>Automatic transactions</strong>:</p>
|
|
<pre><code class="lang-go">// Single operation is atomic
|
|
db.Create(&User{Name: "Alice"})</code></pre>
|
|
<p><strong>Manual transactions</strong>:</p>
|
|
<pre><code class="lang-go">// ✅ Explicit transaction
|
|
tx := db.Begin()
|
|
defer func() {
|
|
if r := recover(); r != nil {
|
|
tx.Rollback()
|
|
}
|
|
}()
|
|
|
|
if err := tx.Create(&User{Name: "Bob"}).Error; err != nil {
|
|
tx.Rollback()
|
|
return err
|
|
}
|
|
|
|
if err := tx.Create(&Order{UserID: 1, Total: 100}).Error; err != nil {
|
|
tx.Rollback()
|
|
return err
|
|
}
|
|
|
|
return tx.Commit().Error</code></pre>
|
|
<p><strong>Transaction callback</strong> (cleaner):</p>
|
|
<pre><code class="lang-go">// ✅ Auto rollback on error, commit on success
|
|
err := db.Transaction(func(tx *gorm.DB) error {
|
|
if err := tx.Create(&User{Name: "Charlie"}).Error; err != nil {
|
|
return err // Rollback
|
|
}
|
|
|
|
if err := tx.Create(&Order{UserID: 1, Total: 150}).Error; err != nil {
|
|
return err // Rollback
|
|
}
|
|
|
|
return nil // Commit
|
|
})</code></pre>
|
|
<p><strong>Savepoints</strong>:</p>
|
|
<pre><code class="lang-go">tx := db.Begin()
|
|
tx.Create(&User{Name: "Dave"})
|
|
|
|
tx.SavePoint("sp1")
|
|
tx.Create(&Order{UserID: 1})
|
|
tx.RollbackTo("sp1") // Rollback to savepoint
|
|
|
|
tx.Commit() // User created, order not</code></pre>
|
|
<h3>Diesel</h3>
|
|
<p><strong>Transactions</strong>:</p>
|
|
<pre><code class="lang-rust">use diesel::Connection;
|
|
|
|
// ✅ Explicit transaction
|
|
conn.transaction::<_, diesel::result::Error, _>(|conn| {
|
|
diesel::insert_into(users::table)
|
|
.values(&new_user)
|
|
.execute(conn)?;
|
|
|
|
diesel::insert_into(orders::table)
|
|
.values(&new_order)
|
|
.execute(conn)?;
|
|
|
|
Ok(()) // Commit
|
|
})
|
|
// Automatic rollback on Err</code></pre>
|
|
<p><strong>Nested transactions</strong> (savepoints):</p>
|
|
<pre><code class="lang-rust">conn.transaction(|conn| {
|
|
diesel::insert_into(users::table)
|
|
.values(&new_user)
|
|
.execute(conn)?;
|
|
|
|
conn.transaction(|conn| { // Savepoint
|
|
diesel::insert_into(orders::table)
|
|
.values(&new_order)
|
|
.execute(conn)?;
|
|
Ok(())
|
|
})?;
|
|
|
|
Ok(())
|
|
})</code></pre>
|
|
<hr>
|
|
<h2>Batch Operations</h2>
|
|
<h3>SQLAlchemy</h3>
|
|
<p><strong>Bulk insert</strong> (fast, no ORM overhead):</p>
|
|
<pre><code class="lang-python"># ✅ Efficient bulk insert
|
|
users = [
|
|
{"name": "Alice", "email": "[email protected]"},
|
|
{"name": "Bob", "email": "[email protected]"},
|
|
# ... 1000 more
|
|
]
|
|
|
|
session.bulk_insert_mappings(User, users)
|
|
session.commit()
|
|
|
|
# Or with ORM objects (slower, but fires events)
|
|
session.add_all([User(name="Alice"), User(name="Bob")])
|
|
session.commit()</code></pre>
|
|
<p><strong>Bulk update</strong>:</p>
|
|
<pre><code class="lang-python"># Update multiple rows at once
|
|
session.query(User).filter(User.status == "pending").update(
|
|
{"status": "active"},
|
|
synchronize_session=False # Skip session sync for performance
|
|
)
|
|
session.commit()</code></pre>
|
|
<p><strong>Bulk delete</strong>:</p>
|
|
<pre><code class="lang-python">session.query(User).filter(User.status == "inactive").delete()
|
|
session.commit()</code></pre>
|
|
<h3>Prisma</h3>
|
|
<p><strong>createMany</strong>:</p>
|
|
<pre><code class="lang-typescript">// ✅ Efficient batch insert
|
|
await prisma.user.createMany({
|
|
data: [
|
|
{ name: "Alice", email: "[email protected]" },
|
|
{ name: "Bob", email: "[email protected]" },
|
|
// ... more
|
|
],
|
|
skipDuplicates: true, // Optional: skip on unique constraint violation
|
|
});</code></pre>
|
|
<p><strong>updateMany</strong>:</p>
|
|
<pre><code class="lang-typescript">await prisma.user.updateMany({
|
|
where: { status: "pending" },
|
|
data: { status: "active" },
|
|
});</code></pre>
|
|
<p><strong>deleteMany</strong>:</p>
|
|
<pre><code class="lang-typescript">await prisma.user.deleteMany({
|
|
where: { status: "inactive" },
|
|
});</code></pre>
|
|
<h3>GORM</h3>
|
|
<p><strong>CreateInBatches</strong>:</p>
|
|
<pre><code class="lang-go">// ✅ Efficient batch insert
|
|
users := []User{
|
|
{Name: "Alice", Email: "[email protected]"},
|
|
{Name: "Bob", Email: "[email protected]"},
|
|
// ... more
|
|
}
|
|
|
|
// Insert in batches of 100
|
|
db.CreateInBatches(users, 100)</code></pre>
|
|
<p><strong>Batch updates</strong>:</p>
|
|
<pre><code class="lang-go">// Update all matching rows
|
|
db.Model(&User{}).Where("status = ?", "pending").Update("status", "active")
|
|
|
|
// Or with map for multiple columns
|
|
db.Model(&User{}).Where("status = ?", "pending").Updates(map[string]interface{}{
|
|
"status": "active",
|
|
"updated_at": time.Now(),
|
|
})</code></pre>
|
|
<h3>Diesel</h3>
|
|
<p><strong>Batch insert</strong>:</p>
|
|
<pre><code class="lang-rust">// ✅ Single INSERT with multiple VALUES
|
|
diesel::insert_into(users::table)
|
|
.values(&vec![
|
|
NewUser { name: "Alice", email: "[email protected]" },
|
|
NewUser { name: "Bob", email: "[email protected]" },
|
|
// ... more
|
|
])
|
|
.execute(&mut conn)?;</code></pre>
|
|
<p><strong>Batch update</strong>:</p>
|
|
<pre><code class="lang-rust">diesel::update(users::table.filter(users::status.eq("pending")))
|
|
.set(users::status.eq("active"))
|
|
.execute(&mut conn)?;</code></pre>
|
|
<hr>
|
|
<h2>Query Optimization Techniques</h2>
|
|
<h3>Select Only Needed Columns</h3>
|
|
<p><strong>SQLAlchemy</strong>:</p>
|
|
<pre><code class="lang-python"># ❌ Loads all columns
|
|
users = session.query(User).all()
|
|
|
|
# ✅ Load specific columns
|
|
users = session.query(User.id, User.name).all()
|
|
|
|
# Or with load_only
|
|
from sqlalchemy.orm import load_only
|
|
users = session.query(User).options(load_only(User.id, User.name)).all()</code></pre>
|
|
<p><strong>Prisma</strong>:</p>
|
|
<pre><code class="lang-typescript">// ✅ Select specific fields
|
|
const users = await prisma.user.findMany({
|
|
select: {
|
|
id: true,
|
|
name: true,
|
|
}
|
|
});</code></pre>
|
|
<p><strong>GORM</strong>:</p>
|
|
<pre><code class="lang-go">// ✅ Select specific columns
|
|
var users []User
|
|
db.Select("id", "name").Find(&users)</code></pre>
|
|
<h3>Limit + Offset Pagination</h3>
|
|
<p><strong>SQLAlchemy</strong>:</p>
|
|
<pre><code class="lang-python"># ✅ Efficient pagination
|
|
page = 2
|
|
per_page = 20
|
|
users = session.query(User).limit(per_page).offset((page - 1) * per_page).all()</code></pre>
|
|
<p><strong>Prisma</strong>:</p>
|
|
<pre><code class="lang-typescript">const page = 2;
|
|
const perPage = 20;
|
|
const users = await prisma.user.findMany({
|
|
skip: (page - 1) * perPage,
|
|
take: perPage,
|
|
});</code></pre>
|
|
<p><strong>Cursor-based pagination</strong> (better for large datasets):</p>
|
|
<pre><code class="lang-typescript">const users = await prisma.user.findMany({
|
|
take: 20,
|
|
skip: 1, // Skip the cursor itself
|
|
cursor: {
|
|
id: lastSeenId,
|
|
},
|
|
orderBy: {
|
|
id: 'asc',
|
|
},
|
|
});</code></pre>
|
|
<h3>Counting Efficiently</h3>
|
|
<p><strong>SQLAlchemy</strong>:</p>
|
|
<pre><code class="lang-python"># ❌ Loads all rows
|
|
count = len(session.query(User).all())
|
|
|
|
# ✅ COUNT query
|
|
count = session.query(User).count()
|
|
|
|
# Or with func.count
|
|
from sqlalchemy import func
|
|
count = session.query(func.count(User.id)).scalar()</code></pre>
|
|
<p><strong>Prisma</strong>:</p>
|
|
<pre><code class="lang-typescript">const count = await prisma.user.count({
|
|
where: { status: "active" },
|
|
});</code></pre>
|
|
<hr>
|
|
<h2>Raw SQL Escapes</h2>
|
|
<h3>When to Use Raw SQL</h3>
|
|
<ul><li>Complex joins ORM can't express efficiently</li><li>Database-specific functions (window functions, CTEs)</li><li>Bulk operations with special logic</li><li>Performance-critical queries</li></ul>
|
|
<h3>SQLAlchemy: text()</h3>
|
|
<pre><code class="lang-python">from sqlalchemy import text
|
|
|
|
# ✅ Safe parameterized query
|
|
result = session.execute(
|
|
text("SELECT * FROM users WHERE email = :email"),
|
|
{"email": "[email protected]"}
|
|
)
|
|
users = result.fetchall()
|
|
|
|
# Or with ORM objects
|
|
users = session.query(User).from_statement(
|
|
text("SELECT * FROM users WHERE status = :status")
|
|
).params(status="active").all()</code></pre>
|
|
<h3>Prisma: $queryRaw</h3>
|
|
<pre><code class="lang-typescript">import { Prisma } from '@prisma/client';
|
|
|
|
// ✅ Safe parameterized query (prevents SQL injection)
|
|
const users = await prisma.$queryRaw<User[]>`
|
|
SELECT * FROM users WHERE email = ${email}
|
|
`;
|
|
|
|
// For non-SELECT queries
|
|
await prisma.$executeRaw`
|
|
UPDATE users SET status = 'active' WHERE created_at < ${cutoffDate}
|
|
`;
|
|
|
|
// Unsafe (only use with trusted input)
|
|
const result = await prisma.$queryRawUnsafe(
|
|
'SELECT * FROM users WHERE status = $1',
|
|
'active'
|
|
);</code></pre>
|
|
<h3>GORM: Raw SQL</h3>
|
|
<pre><code class="lang-go">// ✅ Safe parameterized query
|
|
var users []User
|
|
db.Raw("SELECT * FROM users WHERE email = ?", "[email protected]").Scan(&users)
|
|
|
|
// Execute non-SELECT
|
|
db.Exec("UPDATE users SET status = ? WHERE created_at < ?", "active", cutoff)</code></pre>
|
|
<h3>Diesel: sql_query</h3>
|
|
<pre><code class="lang-rust">use diesel::sql_query;
|
|
use diesel::sql_types::Text;
|
|
|
|
// ✅ Safe parameterized query
|
|
let users = sql_query("SELECT * FROM users WHERE email = $1")
|
|
.bind::<Text, _>("[email protected]")
|
|
.load::<User>(&mut conn)?;</code></pre>
|
|
<hr>
|
|
<h2>Common ORM Anti-Patterns</h2>
|
|
<h3>1. Loading Full Objects When Counting</h3>
|
|
<pre><code class="lang-python"># ❌ Loads all data just to count
|
|
users = session.query(User).filter(User.status == "active").all()
|
|
count = len(users)
|
|
|
|
# ✅ Use COUNT
|
|
count = session.query(User).filter(User.status == "active").count()</code></pre>
|
|
<h3>2. Updating in Loop</h3>
|
|
<pre><code class="lang-python"># ❌ N queries
|
|
for user in users:
|
|
user.status = "active"
|
|
session.commit()
|
|
|
|
# ✅ Batch update
|
|
session.query(User).filter(User.id.in_([u.id for u in users])).update(
|
|
{"status": "active"},
|
|
synchronize_session=False
|
|
)
|
|
session.commit()</code></pre>
|
|
<h3>3. Not Using Transactions for Related Data</h3>
|
|
<pre><code class="lang-typescript">// ❌ No transaction (inconsistent state if error occurs)
|
|
const user = await prisma.user.create({ data: { name: "Alice" }});
|
|
await prisma.order.create({ data: { userId: user.id, total: 100 }});
|
|
|
|
// ✅ Use transaction
|
|
await prisma.$transaction(async (tx) => {
|
|
const user = await tx.user.create({ data: { name: "Alice" }});
|
|
await tx.order.create({ data: { userId: user.id, total: 100 }});
|
|
});</code></pre>
|
|
<h3>4. SELECT * in Queries</h3>
|
|
<pre><code class="lang-go">// ❌ Loads all columns (wastes bandwidth)
|
|
var users []User
|
|
db.Find(&users)
|
|
|
|
// ✅ Select needed columns
|
|
db.Select("id", "name", "email").Find(&users)</code></pre>
|
|
<h3>5. Not Handling Connection Pooling</h3>
|
|
<p>See <code>database-connection-pooling.md</code> for proper pool configuration per ORM.</p>
|
|
<hr>
|
|
<h2>Quick Reference</h2>
|
|
<h3>Eager Loading Comparison</h3>
|
|
<p>| ORM | Method | Queries | Use Case | |-----|--------|---------|----------| | SQLAlchemy | <code>joinedload()</code> | 1 (JOIN) | Few related items | | SQLAlchemy | <code>selectinload()</code> | 2 (IN) | Many related items | | Prisma | <code>include</code> | 1-2 | Any relationships | | GORM | <code>Preload()</code> | 2 (IN) | Standard loading | | GORM | <code>Joins()</code> | 1 (JOIN) | Need JOIN conditions | | Diesel | Manual grouping | 2 | Full control |</p>
|
|
<h3>Transaction Patterns</h3>
|
|
<pre><code class="lang-python"># SQLAlchemy
|
|
with session.begin():
|
|
# operations
|
|
|
|
# Prisma
|
|
await prisma.$transaction([...])
|
|
|
|
# GORM
|
|
db.Transaction(func(tx *gorm.DB) error { ... })
|
|
|
|
# Diesel
|
|
conn.transaction(|conn| { ... })</code></pre>
|
|
<h3>Optimization Checklist</h3>
|
|
<pre><code>Performance Issues:
|
|
[ ] Enable query logging to detect N+1
|
|
[ ] Use eager loading (joinedload, include, Preload)
|
|
[ ] Select only needed columns (select, load_only)
|
|
[ ] Use batch operations for multiple inserts/updates
|
|
[ ] Count with COUNT query, not len(results)
|
|
[ ] Use transactions for related operations
|
|
[ ] Paginate with LIMIT/OFFSET or cursor
|
|
[ ] Monitor slow queries (see postgres-query-optimization.md)
|
|
[ ] Use raw SQL for complex analytics
|
|
[ ] Configure connection pool properly</code></pre>
|
|
<hr>
|
|
<h2>Related Skills</h2>
|
|
<ul><li><code>postgres-query-optimization.md</code> - Optimize underlying SQL queries</li><li><code>database-connection-pooling.md</code> - Pool configuration per ORM</li><li><code>postgres-schema-design.md</code> - Schema design affects ORM usage</li><li><code>postgres-migrations.md</code> - ORM migrations (Alembic, Prisma Migrate)</li></ul>
|
|
<hr>
|
|
<h2>Common Pitfalls</h2>
|
|
<p>❌ <strong>Ignoring N+1 queries</strong> - Causes exponential slowdown ✅ Always eager load relationships with multiple items</p>
|
|
<p>❌ <strong>No transactions for related operations</strong> - Data inconsistency ✅ Wrap related inserts/updates in transaction</p>
|
|
<p>❌ <strong>Using loops for batch operations</strong> - Thousands of queries ✅ Use bulk_insert_mappings, createMany, CreateInBatches</p>
|
|
<p>❌ <strong>Loading full objects for counting</strong> - Wastes memory/bandwidth ✅ Use count() methods</p>
|
|
<p>❌ <strong>Not using proper connection pooling</strong> - Exhausted connections ✅ Configure pool based on workers/threads</p>
|
|
<p>❌ <strong>Raw SQL without parameterization</strong> - SQL injection ✅ Always use parameterized queries (text(), $queryRaw, bind)</p>
|
|
<hr>
|
|
<p><strong>Last Updated</strong>: 2025-10-18 <strong>Format Version</strong>: 1.0 (Atomic)</p>
|
|
<footer>
|
|
<div class="fmap">
|
|
<div><b>Build</b>
|
|
<a href="https://hanzo.app">hanzo.app</a> app builder
|
|
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
|
|
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
|
|
<div><b>Run</b>
|
|
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
|
|
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
|
|
<div><b>Account</b>
|
|
<a href="https://hanzo.id">hanzo.id</a> sign in
|
|
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
|
|
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
|
|
<div><b>Company</b>
|
|
<a href="https://hanzo.ai">hanzo.ai</a> canonical
|
|
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
|
|
</div>
|
|
<div class="fend"><a href="/database-orm-patterns.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
|
|
</footer>
|
|
</div></body></html> |