Any skills that you use for sql code review

[u/arup_r]

I am a Ruby on Rails developer. I’m looking for some skills that can help me self code review for sql part. I use Claude. Like that can guide me not to write sql that are anti patterns etc

[u/Gargunok]

Fundamentally I don't think you can code review your own code. Peer review is the best way.

Ai tools can help but I think many are too biased to support your opinion to be critical enough when challenged.

[u/elevarq]

Anti patterns start in the application, for example the data model. There is nothing you can fix in SQL when the problem sits elsewhere.

[u/alinroc]

Like that can guide me not to write sql that are anti patterns etc

The trick with SQL is that once you're experienced in it, you start to realize that most "anti-patterns" have cases where they actually are the best or maybe "least bad" solution. Even crazier, you start to discover tricks to improve performance that make no sense on the surface, but once you understand what it's doing you realize how genius it is.

So you can't just have a blanket "don't ever do this" set of rules. There's nuance and exceptions that have to be considered.

If you're depending upon Ruby on Rails to handle all of your database interactions for you, your potential problems are manifold. You have to not only understand what it's doing so that you can identify the bad behaviors, and you have to understand how to write your application code to not trigger the framework to produce bad database interactions.

[u/Einar_Son_of_Bjorn]

Using Claude as a SQL review partner is a good instinct. Most Rails bugs I see are not “can’t write a join.” They’re patterns that look fine in Ruby and get expensive in the database. A fixed checklist plus a model that never gets tired of repeating it is better than hoping you’ll notice N+1 on a Friday.What that approach actually buys you: you catch string-interpolated where, SELECT *, and NOT IN on a nullable column before production.

You also build a list you can paste on every PR, so review quality doesn’t depend on mood.Claude is still a second pair of eyes. It will not see the expensive plan unless you paste EXPLAIN.What I review in Rails SQL:

• binds vs where("id = #{id}")

- N+1 and SELECT * through ActiveRecord

- WHERE YEAR(created_at) = 2024 instead of a range on the column

- NOT IN on a subquery that can contain NULL

- no LIMIT on anything a page can trigger

- row-by-row writes that should be one statement

- a join that explodes rows, then DISTINCT to hide it

Prompt that works: “This is production MySQL/ MariaDB. Find injectability, implicit scans, and nullable NOT IN. Don’t rewrite in Postgres dialect.”Then run:

sql

EXPLAIN
-- the SQL from the Rails log

If you skip type / key / rows, the review is theater.Are you on MySQL/ MariaDB or Postgres, and do you already log queries in development (Bullet / AR query log)?

[u/arup_r → u/Einar_Son_of_Bjorn]

Thanks for your reply. I use postgresql. When I write custom sql which is not standard active record one, I feel sometimes I could have write it better way. So I was also looking for if any skills that I can use here.