sql

ActiveRecord provides powerful query interface, but naive usage causes N+1 queries. includes eager loads associations in 2-3 queries. joins performs SQL JOINs for filtering. preload always uses separate queries; eager_load forces LEFT OUTER JOIN. I us

Sometimes raw SQL is the cleanest approach—just keep it safe. Use exec_query with bind params instead of interpolating values. You get both safety and correctness with types.

The most common transaction bug I see is forgetting to roll back on early returns. With pgx, I like the “defer rollback” pattern: start the transaction, defer tx.Rollback(ctx), then call tx.Commit(ctx) only on success. Rollback after a successful comm

When using sqlc, the generated query set usually has a WithTx method. I wrap that pattern so business logic can depend on an interface and still run inside a transaction. The key is to keep transaction boundaries explicit while avoiding passing *sql.T

For queries too complex for the ORM, I use raw SQL. The raw() method returns model instances. I use cursor.execute() for non-model queries. I always use parameterized queries to prevent SQL injection—never string interpolation. For reporting, raw SQL

Window functions perform calculations across row sets without grouping. ROWNUMBER assigns unique sequential numbers. RANK/DENSERANK handle ties differently. I use PARTITION BY to reset calculations per group. ORDER BY determines calculation order with

When multiple clients can update the same record, I prefer optimistic locking over heavy row locks. The idea is simple: every row has a version that increments on each update. The update statement includes WHERE id=$1 AND version=$2, so if someone els

CTEs improve query readability and maintainability. WITH clauses define named subqueries referenced in main query. I use CTEs to break complex queries into logical steps. Recursive CTEs handle hierarchical data—org charts, category trees, graph traver

For leaderboards, let the database do ranking. Window functions are fast and expressive. Use them to compute daily top N without Ruby loops.

Query objects encapsulate complex database queries in reusable, testable classes. I use query objects when scopes become too complex or require parameters. Query objects compose smaller scopes, handle conditionals, and apply filtering logic. They're i

Counters are classic race-condition bait. If two requests read, increment, and write, you’ll lose updates under load. I prefer letting Postgres do the atomic work with an upsert: INSERT ... ON CONFLICT ... DO UPDATE to increment the existing value. Th

Offset pagination (LIMIT/OFFSET) is fine until it isn’t: it gets slow on large tables and it produces weird duplicates when rows are inserted between pages. For APIs I prefer cursor pagination with an opaque token. The token encodes the last seen (cre