SQL for Web Developers: Database Basics You Can't Skip

SQL for Web Developers: Database Basics You Can't Skip

I avoided databases for the first year of learning web development. I stored everything in JSON files, local storage, even hardcoded arrays. It worked — until I needed to search data, sort it, filter it, or relate it to other data.

One query that would have taken three lines of SQL took 80 lines of JavaScript gymnastics. And it was slower, harder to maintain, and broke under concurrent access.

Databases exist because they solve hard problems better than application code. Understanding SQL doesn't just make you a "backend developer" — it makes you a better developer at every level of the stack.

This guide covers the SQL you'll actually use as a web developer: selecting and filtering data, inserting and updating records, joining related tables, and structuring a real schema.

What is a Relational Database?

A relational database stores data in tables — rows and columns, like a structured spreadsheet. Each table has a defined schema (column names and types), and tables relate to each other through foreign keys.

PostgreSQL is the recommended database for most web applications in 2025. It's free, open source, battle-tested at scale, and handles everything from a hobby project to millions of daily active users. SQLite is excellent for development and small applications.

Core SQL: SELECT

SELECT retrieves data from a table:

-- All columns from the users table
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With filtering
SELECT name, email FROM users
WHERE active = true;

-- Multiple conditions
SELECT * FROM posts
WHERE published = true
  AND created_at > '2025-01-01';

-- With ordering
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;  -- First 10 results

-- Pattern matching
SELECT * FROM users
WHERE email LIKE '%@gmail.com';  -- All Gmail users

Aggregate Functions

-- Count all users
SELECT COUNT(*) FROM users;

-- Count active users
SELECT COUNT(*) FROM users WHERE active = true;

-- Average, min, max
SELECT AVG(price), MIN(price), MAX(price) FROM products;

-- Sum
SELECT SUM(amount) FROM orders WHERE status = 'completed';

-- Group by
SELECT category, COUNT(*) as product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

INSERT, UPDATE, DELETE

INSERT — Add new rows

-- Insert one row
INSERT INTO users (name, email, created_at)
VALUES ('Alice Johnson', 'alice@example.com', NOW());

-- Insert and get the created record back (PostgreSQL)
INSERT INTO posts (title, body, user_id, published)
VALUES ('My First Post', 'Hello world!', 42, true)
RETURNING id, created_at;

-- Insert multiple rows
INSERT INTO tags (name)
VALUES ('javascript'), ('react'), ('css');

UPDATE — Modify existing rows

-- Update one user's email
UPDATE users
SET email = 'alice.new@example.com'
WHERE id = 42;

-- Update multiple columns
UPDATE posts
SET published = true,
    published_at = NOW()
WHERE id = 7;

-- Update with a WHERE clause — always include this!
-- Omitting WHERE updates EVERY row in the table
UPDATE users SET active = false;  -- DANGEROUS — deactivates everyone

DELETE — Remove rows

-- Delete one user
DELETE FROM users WHERE id = 42;

-- Delete old data
DELETE FROM sessions
WHERE expires_at < NOW();

-- Delete with RETURNING (PostgreSQL)
DELETE FROM notifications
WHERE user_id = 42 AND read = true
RETURNING id;

JOINs: Relating Tables

Real applications have related tables. A posts table has a user_id that references the users table. A JOIN combines them:

Schema Example

CREATE TABLE users (
  id        SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  email     TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
  id        SERIAL PRIMARY KEY,
  title     TEXT NOT NULL,
  body      TEXT,
  user_id   INTEGER REFERENCES users(id),  -- foreign key
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE comments (
  id      SERIAL PRIMARY KEY,
  body    TEXT NOT NULL,
  post_id INTEGER REFERENCES posts(id),
  user_id INTEGER REFERENCES users(id)
);

INNER JOIN

Returns rows that match in both tables:

-- Get posts with author name
SELECT
  posts.title,
  posts.created_at,
  users.name AS author_name
FROM posts
INNER JOIN users ON posts.user_id = users.id
WHERE posts.published = true
ORDER BY posts.created_at DESC;

Result includes only posts that have a matching user.

LEFT JOIN

Returns all rows from the left table, with NULLs for the right table where no match:

-- All users, including those with no posts
SELECT
  users.name,
  COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.name
ORDER BY post_count DESC;

Multiple JOINs

-- Comments with post title and author name
SELECT
  comments.body,
  posts.title AS post_title,
  u_comment.name AS commenter,
  u_post.name AS post_author
FROM comments
INNER JOIN posts    ON comments.post_id = posts.id
INNER JOIN users u_comment ON comments.user_id = u_comment.id
INNER JOIN users u_post    ON posts.user_id = u_post.id;

Indexes: Making Queries Fast

Without an index, every query scans every row. On a table with 1 million rows, this is slow:

-- Without index: scans 1,000,000 rows
SELECT * FROM users WHERE email = 'alice@example.com';

-- With index: finds the row in microseconds
CREATE INDEX idx_users_email ON users (email);

When to Add Indexes

-- Primary keys are indexed automatically
CREATE TABLE users (id SERIAL PRIMARY KEY);  -- id is already indexed

-- Add index on columns used in WHERE clauses
CREATE INDEX idx_posts_user_id ON posts (user_id);
CREATE INDEX idx_posts_published ON posts (published) WHERE published = true;

-- Add index on columns used in ORDER BY
CREATE INDEX idx_posts_created_at ON posts (created_at DESC);

-- Composite index for common query patterns
CREATE INDEX idx_posts_user_published ON posts (user_id, published);

Indexes make reads fast but add overhead to writes. Don't index every column — only columns your queries actually filter on.

Real Web Application Patterns

Pagination

-- Page 1 (items 1-20)
SELECT * FROM posts
WHERE published = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Page 2 (items 21-40)
SELECT * FROM posts
WHERE published = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

-- Cursor-based pagination (better for large tables)
SELECT * FROM posts
WHERE published = true
  AND created_at < '2025-05-01T00:00:00Z'  -- cursor
ORDER BY created_at DESC
LIMIT 20;

Search

-- Basic text search
SELECT * FROM posts
WHERE title ILIKE '%javascript%'  -- case-insensitive
   OR body  ILIKE '%javascript%';

-- Full-text search (PostgreSQL)
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body)
   @@ to_tsquery('english', 'javascript & react');

Using SQL with Node.js (pg)

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Safe parameterized query — prevents SQL injection
async function getUserByEmail(email) {
  const result = await pool.query(
    'SELECT id, name, email FROM users WHERE email = $1',
    [email]   // $1 is replaced safely — never use string interpolation
  );
  return result.rows[0] ?? null;
}

async function createPost(title, body, userId) {
  const result = await pool.query(
    `INSERT INTO posts (title, body, user_id)
     VALUES ($1, $2, $3)
     RETURNING id, created_at`,
    [title, body, userId]
  );
  return result.rows[0];
}

Always use parameterized queries — never interpolate user input into SQL strings. SQL injection is one of the most common security vulnerabilities. For a broader look at web security, see our HTTP vs HTTPS guide.

For learning where databases fit in the full web application stack, see our web developer roadmap and the API tutorial that covers how APIs expose database data to the frontend.

Frequently Asked Questions

Do web developers need SQL?

Yes. All real applications use databases. Understanding SQL makes you a better developer at every level of the stack, even when using ORMs.

SQL vs NoSQL?

PostgreSQL for most web apps. NoSQL for specific needs: real-time (Redis), massive scale writes (Cassandra), flexible documents (MongoDB).

What is a JOIN?

Combines rows from two tables based on a related column. INNER JOIN: only matching rows. LEFT JOIN: all rows from left table, NULLs for missing right-side matches.

What is a database index?

A data structure that speeds up queries on specific columns. Index foreign keys and columns used in WHERE and ORDER BY.

ORM or raw SQL?

Learn SQL first. Use ORM for standard CRUD, raw SQL for complex queries. Mix both in production.