What is SQL?

The library with no librarian

Imagine a massive library with millions of books, but no librarian. The books are organized on shelves by category, author, and year. You can't just shout "give me something good!" You need to write a precise request on a slip of paper: which shelf, which section, which criteria. The library reads your slip and returns the books that match.

That library is a database. The slip of paper is a SQL query.

SQL (Structured Query Language, pronounced "sequel" or "S-Q-L") is the language you use to talk to relational databases. Every time your Rails app loads a user profile, lists blog posts, or checks a password: SQL is the conversation happening underneath.

Why databases exist

You could store data in a text file. Many beginners do. But text files break the moment two things happen at once:

1. Two users edit the file simultaneously: whose change wins?
2. You need to find one record among millions: do you read the entire file?

A relational database (PostgreSQL, MySQL, SQLite) solves both problems. It provides:

• Concurrency control: multiple users read and write without corrupting data, PostgreSQL and MySQL do it with MVCC (Multi-Version Concurrency Control): each transaction sees a consistent snapshot of the data. SQLite uses locking instead.
• Indexing: instead of scanning every row, the database builds data structures (B-trees, hash indexes) that let it jump directly to the rows you need. Finding one user among 10 million takes milliseconds, not minutes.
• ACID guarantees: every operation is Atomic (all-or-nothing), Consistent (rules are enforced), Isolated (transactions don't interfere), and Durable (committed data survives a crash).

SQL is the standard interface to all of this power. It has been the dominant database language since the 1970s, and every major web framework (including Rails) speaks it.

The relational model: tables, rows, and columns

Relational databases store data in tables. A table looks like a spreadsheet, but with strict rules.

+----+----------------+---------------------------+--------+
| id | name           | email                     | active |
+----+----------------+---------------------------+--------+
|  1 | Alice Müller   | [email protected]         | true   |
|  2 | Bob Santos     | [email protected]           | true   |
|  3 | Carol Tanaka   | [email protected]         | false  |
+----+----------------+---------------------------+--------+
       ↑ columns (attributes)       ↑ each horizontal line = one row (record)

• Table: a collection of related data (e.g., users, orders, products). In Rails, each model maps to one table.
• Column: a single attribute with a defined type. name is a text column. active is a boolean column. The database enforces these types: you can't store a sentence in an integer column.
• Row: one record. Alice is one row. Bob is another. Each row has a value for every column.
• Primary Key: a column that uniquely identifies each row. Usually id, an auto-incrementing integer. No two rows can share the same primary key.

This is the relational model, invented by Edgar F. Codd at IBM in 1970. The word "relational" comes from the mathematical concept of a relation: a set of tuples (rows) with named attributes (columns). It does not mean "tables are related to each other" (though they can be, via foreign keys).

What SQL does

SQL is divided into sub-languages, each with a specific job:

This course focuses on DQL: specifically SELECT and FROM. These are the commands you will use most often as a Rails developer, because most web requests are reads (loading pages, searching, filtering).

When you write a SQL query, PostgreSQL processes it through a pipeline:

flowchart TB
  Q["Your SQL query (text)"] --> P["Parser<br/>checks syntax, builds a query tree"]
  P --> R["Rewriter<br/>applies rules, e.g. expands views"]
  R --> PL["Planner / optimizer<br/>index or table scan? join order? picks the cheapest plan"]
  PL --> E["Executor<br/>runs the plan"]
  E --> RS["Result set (the rows you asked for)"]

This matters because the same query can be executed in different ways. The planner is the brain: it looks at table sizes, available indexes, and statistics to choose the fastest execution path. Understanding this pipeline is why some queries are fast and others are slow.

Why Rails developers need raw SQL

Rails has ActiveRecord: an ORM (Object-Relational Mapper) that lets you write Ruby instead of SQL:

# Ruby (ActiveRecord)
User.where(active: true).order(:name)

# SQL that ActiveRecord generates
SELECT "users".* FROM "users" WHERE "users"."active" = TRUE ORDER BY "users"."name" ASC

So why learn SQL at all? Three critical reasons:

First, ActiveRecord generates SQL: you need to read it. When a page is slow, you'll open the Rails log and see raw SQL. If you can't read it, you can't debug it.

Second, ActiveRecord can't express everything. Complex reports, window functions, CTEs (Common Table Expressions), and advanced aggregations often require find_by_sql or Arel. You need SQL to write them.

Third, SQL is portable. ActiveRecord is Rails-only. SQL works in PostgreSQL, MySQL, SQLite, and every BI tool, data pipeline, and admin console you'll ever use. It is the universal language of data.

Every senior Rails developer reads SQL fluently. Starting now puts you ahead.

Your first SQL query

Here is the simplest possible SQL query:

SELECT 1;

Output:

 ?column?
----------
        1
(1 row)

This query has no table, no data, no FROM. It asks the database to evaluate the expression 1 and return it. The database responds with one row containing the value 1.

This is important: SELECT is an expression evaluator first, a data retriever second. You can use it as a calculator:

SELECT 2 + 3;

 ?column?
----------
        5

We will explore this in depth in the next lesson.

Common mistakes

Mistake: Thinking SQL is outdated because ORMs exist.
Why it's wrong: ORMs generate SQL. When the generated SQL is slow or wrong, the only way to fix it is to understand SQL. Every production Rails app hits this wall.

Mistake: Confusing the database with the application.
Why it's wrong: The database is a separate process running independently of your Rails app. Your Rails app connects to it over a socket (usually TCP) and sends SQL text. The database parses, plans, and executes that SQL, then sends results back. They are two distinct systems communicating via SQL.

Summary