Writing Better SQL: Lessons from the Trenches

SQL is Everywhere

Despite decades of "SQL is dead" predictions, SQL remains the dominant language for working with structured data. Whether you're using PostgreSQL, MySQL, SQLite, or D1, knowing how to write good SQL pays dividends across your entire career.

Here are the lessons that took me the longest to learn.

---

1. SELECT * is a Trap

It's tempting to write SELECT * when prototyping, but it's rarely the right choice in production:

-- Bad: fetches all columns, including large blobs you don't need
SELECT * FROM posts;

-- Good: explicit columns, only what you need
SELECT id, slug, title, created_at FROM posts;

Explicit columns make your queries:

• Faster (less data transferred)
• More resilient to schema changes
• Easier to understand at a glance

---

2. Use Indexes Strategically

Indexes speed up reads but slow down writes and use disk space. Add them where they matter:

-- If you frequently filter or sort by created_at
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- Compound indexes for common query patterns
CREATE INDEX idx_posts_published_created ON posts(published, created_at DESC);

A good rule of thumb: add an index when a query shows up in your slow query log.

---

3. Avoid N+1 Queries

This is the most common performance mistake in applications:

// Bad: 1 query to get posts, then N queries to get each author
const posts = await db.query("SELECT * FROM posts");
for (const post of posts) {
  const author = await db.query("SELECT * FROM users WHERE id = ?", [post.author_id]);
}

// Good: JOIN fetches everything in one query
const posts = await db.query(`
  SELECT posts.*, users.name as author_name
  FROM posts
  JOIN users ON posts.author_id = users.id
`);

---

4. Prefer Migrations Over Manual Changes

Never modify your production schema by hand. Use migration files:

-- migrations/0002_add_tags.sql
ALTER TABLE posts ADD COLUMN tags TEXT NOT NULL DEFAULT '';

Track these in version control. Run them in order. This gives you a reproducible, auditable history of your schema.

---

5. Use Transactions for Related Writes

When multiple writes must succeed or fail together, use a transaction:

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 99.99);
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

Without a transaction, a failure halfway through leaves your data inconsistent.

---

6. Think in Sets, Not Loops

SQL is a set-based language. Embrace it:

-- Bad: updating rows one at a time in application code
UPDATE posts SET published = 1 WHERE id = 1;
UPDATE posts SET published = 1 WHERE id = 2;
UPDATE posts SET published = 1 WHERE id = 3;

-- Good: single set-based update
UPDATE posts SET published = 1 WHERE id IN (1, 2, 3);

---

Closing Thoughts

Good SQL is about clarity and intentionality. Explicit column lists, thoughtful indexes, and proper use of transactions will take you far. The fundamentals haven't changed in 50 years — and they're unlikely to change in the next 50.