Data Access Patterns Skill

Use this skill when writing or modifying database access code in internal/models/ .

This project uses explicit SQL over ORMs for performance, readability, and fine-grained control.

Core Principles

• No ORM: Use database/sql directly. Do NOT introduce or use any ORM.

• PostgreSQL Driver: The project uses github.com/lib/pq (imported in internal/db/db.go ).

• Raw SQL: Write explicit, readable SQL queries. Use PostgreSQL-specific features (JSONB, ON CONFLICT) and $1, $2 placeholders.

• Transactional Integrity: Use *sql.Tx when multiple operations must complete together or fail (atomicity).

• Separation of Concerns: Keep database models and access logic in internal/models/ . Repository functions should focus on data retrieval and persistence.

Repository Function Naming

Pattern Purpose Example Signature

Get[Entity]ById

Retrieve a single entity GetWeaponById(db *sql.DB, id string) (*Weapon, error)

Get[Entities]By[Field]

Filtered retrieval GetTraderOffersByItemID(db *sql.DB, itemID string) ([]TraderOffer, error)

Upsert[Entity]

Insert or update one record UpsertWeapon(tx *sql.Tx, weapon Weapon) error

UpsertMany[Entity]

Batch insert/update UpsertManyWeapon(tx *sql.Tx, weapons []Weapon) error

Purge[Entity]

Clean up records PurgeOptimumBuilds(db *sql.DB) error

Writing Efficient Queries

JSONB for Complex Trees

When fetching an entity with nested children (e.g., a weapon with many slots), use jsonb_agg and jsonb_build_object to minimize round-trips and simplify Go-side scanning.

// Example: Single query to fetch weapon and all its slots query := SELECT w.name, w.item_id, jsonb_agg(jsonb_build_object( 'slot_id', ws.slot_id, 'name', ws.name )) as slots FROM weapons w JOIN slots ws ON w.item_id = ws.item_id WHERE w.item_id = $1 GROUP BY w.name, w.item_id;

Idempotent Writes (ON CONFLICT )

Prefer ON CONFLICT for upsert operations to ensure idempotency and handle existing records gracefully.

query := INSERT INTO weapons (item_id, name) VALUES ($1, $2) ON CONFLICT (item_id) DO UPDATE SET name = EXCLUDED.name;

Transaction Management Checklist

When implementing writes:

• Composite Writes: If a function calls multiple other write functions (e.g., UpsertWeapon calling upsertManySlot ), it MUST accept a *sql.Tx .

• Top-Level Orchestration: Start the transaction at the highest possible level (usually in the importer or service layer).

• Defer Rollback: Defer a rollback immediately after starting a transaction to prevent leaks on error.

tx, err := db.Begin() if err != nil { return err } defer tx.Rollback() // Safe: does nothing if committed

if err := UpsertManyWeapon(tx, weapons); err != nil { return err }

return tx.Commit()

Developer Best Practices

• ✅ Explicit Scanning: Scan rows into structs carefully; match types exactly with the DB schema.

• ✅ Resource Management: defer rows.Close() immediately after a Query call.

• ✅ Strict Errors: Check errors after rows.Scan() AND after the loop with rows.Err() .

• ✅ Clean Signatures: Pass *sql.DB for read-only operations and *sql.Tx for multi-step write operations.

• ❌ **Avoid SELECT ***: Explicitly list required columns to prevent breakage from schema changes.

• ❌ No Global DB: Pass the database handle as an argument.