What Is SQL? Database Queries Explained for AI Coders

Why AI Coders Need to Know This

Every app that stores user data — a blog, a SaaS dashboard, an e-commerce site — needs a database. And that database speaks SQL. When you prompt AI to "build me a blog with users and posts," it is going to write database queries. Those queries will involve tables, columns, relationships, and filters. If you cannot read that code, you cannot debug it when it breaks, you cannot spot the security holes it might contain, and you cannot help the AI fix the problems it introduces.

Even if you use an ORM like Prisma, Drizzle, or Sequelize — tools that let you write database operations in JavaScript or TypeScript instead of raw SQL — you still need to understand the underlying SQL concepts. ORMs leak SQL: error messages reference table names and column names, query builders translate to SQL under the hood, and when performance matters, you often need to drop down to raw queries.

The good news: you do not need to become a database administrator. You need to understand four commands, one join type, and a few key concepts. That is enough to read AI-generated database code and know when something looks wrong.

Real Scenario

You are building a blog platform and type this prompt:

Build me a blog with the following features:
- Users can register and write posts
- Posts have a title, content, author, and publish date
- Show all published posts on the homepage, newest first
- Show a single post with the author's name

Use PostgreSQL. Show me the database schema and the queries I'd need.

Claude or Cursor will generate a schema (the blueprint for your database tables) and a set of SQL queries. Here is what that output typically looks like, and what every part of it means.

What AI Generated

-- Schema: defining the structure of your database tables
-- Run these once to set up the database

CREATE TABLE users (
  id SERIAL PRIMARY KEY,          -- auto-incrementing unique ID for each user
  email VARCHAR(255) UNIQUE NOT NULL,  -- email must be unique and can't be blank
  password_hash TEXT NOT NULL,    -- never store plain text passwords
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW()  -- automatically records when the row was created
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER REFERENCES users(id),  -- foreign key: links to a user's ID
  published BOOLEAN DEFAULT false,          -- draft by default
  published_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Query 1: Get all published posts, newest first
SELECT
  posts.id,
  posts.title,
  posts.published_at,
  users.name AS author_name        -- rename the column in the result
FROM posts
JOIN users ON posts.author_id = users.id   -- combine post rows with matching user rows
WHERE posts.published = true               -- only published posts
ORDER BY posts.published_at DESC;          -- newest first

-- Query 2: Get a single post with author
SELECT
  posts.*,                          -- all columns from posts
  users.name AS author_name,
  users.email AS author_email
FROM posts
JOIN users ON posts.author_id = users.id
WHERE posts.id = $1;               -- $1 is a parameter placeholder (prevents SQL injection)

-- Query 3: Create a new post
INSERT INTO posts (title, content, author_id, published, published_at)
VALUES ($1, $2, $3, true, NOW())
RETURNING id;                      -- return the new post's ID after inserting

-- Query 4: Update a post
UPDATE posts
SET title = $1, content = $2
WHERE id = $3 AND author_id = $4;  -- the author_id check prevents editing others' posts

-- Query 5: Delete a post
DELETE FROM posts
WHERE id = $1 AND author_id = $2;  -- same pattern: verify ownership before deleting

Understanding Each Part

The Four Core Commands

WHERE: Filtering Results

WHERE filters which rows the query affects. SELECT * FROM posts WHERE published = true only returns published posts. UPDATE posts SET title = 'New Title' WHERE id = 5 only updates the post with ID 5. Without a WHERE clause on an UPDATE or DELETE, you affect every row in the table — a common and painful mistake.

JOIN: Combining Tables

Databases store related data in separate tables. A JOIN combines them. In the example above, posts and users are stored separately — a post has an author_id column that references the user's id. A JOIN tells the database: "For each post, find the matching user row and combine them into one result."

The most important join types:

• INNER JOIN (or just JOIN) — Only returns rows where both tables have a match. If a post has no author, it won't appear.
• LEFT JOIN — Returns all rows from the left table, plus any matches from the right. If a post has no author, it still appears with null for author columns.

AI often uses INNER JOIN when LEFT JOIN is more appropriate. If your query is mysteriously returning fewer results than expected, check your join type.

Foreign Keys and Relationships

author_id INTEGER REFERENCES users(id) is a foreign key — it creates a relationship between two tables. The database enforces this: you can't insert a post with an author_id that doesn't exist in the users table. This prevents orphaned data and is one of the key features of relational databases.

Parameterized Queries: $1, $2, $3

See the $1, $2 placeholders in the queries? Those are parameters. Instead of writing WHERE id = 5 with the value hardcoded, you use a placeholder and pass the value separately: db.query('WHERE id = $1', [userId]). This is critical for security — it prevents SQL injection.

What AI Gets Wrong About SQL

1. SQL Injection via String Concatenation

The most dangerous mistake. AI sometimes generates queries like this:

// ❌ DANGEROUS — never do this
const query = `SELECT * FROM users WHERE email = '${userEmail}'`;

// ✅ SAFE — parameterized query
const query = 'SELECT * FROM users WHERE email = $1';
const result = await db.query(query, [userEmail]);

If userEmail contains something like ' OR '1'='1, the first version executes as valid SQL and returns every user in the database. The second version treats the input as data, not code — safe regardless of what the user submits.

2. Missing WHERE on DELETE or UPDATE

AI occasionally generates DELETE FROM posts without a WHERE clause during refactoring. This deletes every row. Always double-check UPDATE and DELETE queries have a WHERE clause before running them in production.

3. SELECT * in Production

SELECT * returns every column, including password hashes, internal flags, and fields you don't need. This wastes bandwidth and can accidentally expose sensitive data in API responses. AI uses it for convenience; replace it with explicit column names in production code.

4. N+1 Query Problem

AI sometimes generates code that runs a database query inside a loop — one query to get all posts, then one query per post to get the author. For 100 posts, that's 101 queries. Use a JOIN instead to get all the data in one query. ORMs like Prisma have include syntax that handles this automatically.

5. Wrong JOIN Type

Using INNER JOIN when you need LEFT JOIN silently drops rows. If you're missing data in a query result and can't figure out why, try changing INNER JOIN to LEFT JOIN and see if the missing rows appear.

ORMs vs Raw SQL: Prisma, Drizzle, and When to Use Each

Most modern AI-generated code uses an ORM (Object-Relational Mapper) rather than raw SQL. Here's what that looks like with Prisma:

// Raw SQL version
const posts = await db.query(
  'SELECT posts.*, users.name FROM posts JOIN users ON posts.author_id = users.id WHERE posts.published = true ORDER BY posts.published_at DESC'
);

// Prisma ORM version — same query, JavaScript syntax
const posts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { publishedAt: 'desc' },
  include: { author: { select: { name: true } } }  // JOIN handled automatically
});

ORMs are safer (parameterized by default), more readable, and easier to refactor. But understanding the SQL underneath helps you debug when things go wrong, write raw queries for complex operations ORMs can't express cleanly, and understand database migration files.

How to Debug SQL with AI

In Cursor

Paste the failing query and error message into Cursor chat. Common errors: "column does not exist" (check your column name spelling and case), "violates foreign key constraint" (you're referencing an ID that doesn't exist), "duplicate key value" (you're inserting a duplicate where UNIQUE is required).

In Windsurf

Use Cascade with codebase context: "Find all database queries in this codebase that use string concatenation instead of parameterized queries." Windsurf can scan the entire project and flag the unsafe patterns at once.

In Claude Code

Claude is excellent at SQL review. Paste a query and ask: "Review this SQL for correctness, performance issues, and security vulnerabilities. Focus on injection risks, missing WHERE clauses, and inefficient JOINs." This is one of Claude's strongest use cases.

General Tips

• Use a database GUI (TablePlus, DBeaver, Supabase Studio) to run queries manually and inspect results
• Enable query logging in development to see exactly what SQL your ORM generates
• Use EXPLAIN ANALYZE in PostgreSQL to see why a query is slow
• Test destructive queries (UPDATE, DELETE) with a SELECT version first to confirm you're targeting the right rows

What to Learn Next

Frequently Asked Questions