EXPLAINER

SQL

SQL is the language of relational databases: you declare tables and ask questions with SELECT, JOIN, and WHERE. ORMs can generate SQL, but you still need to read it to understand performance and correctness.

Short answer: SQL is how you query relational databases such as PostgreSQL, MySQL and SQLite. Learn SELECT, JOIN and indexes before you trust an ORM like Prisma to hide them.

What it is

SQL (Structured Query Language) is the standard language for relational databases. You define tables with columns and types, insert rows, and ask questions. A SELECT names the columns you want, the tables they come from, and the conditions. A JOIN combines rows from two tables using a key, such as a user id. WHERE filters. GROUP BY aggregates. INSERT, UPDATE and DELETE change data. The database, not your for-loop, is supposed to do the filtering.

SQL is a language, not a product. PostgreSQL, MySQL and SQLite all speak SQL with dialects that differ in types, functions and autoincrement. SQL versus NoSQL covers when a relational model is the wrong tool. This page is the language you will still need on the days the model is right.

Why and when it is used

Use SQL whenever the data has relationships and you want constraints: a user has many orders, an order has a total, a foreign key must exist. Reporting, billing, and application state fit. You also use SQL inside AI products more than tutorials admit: chat history, users, and document metadata belong in tables even if embeddings live in a vector index.

Do not pull an entire table into Python to filter it. Do not build a join in application memory that the database can do with an index. Do use a non-SQL store when the data is a document you always load whole, a cache, or a vector neighbourhood. Even then, the system of record is often still SQL.

How it works

The database parses your statement, plans how to run it, and executes that plan. An index is a structure that lets it find matching rows without reading every row. Without an index, a filter on a large table becomes a sequential scan. Transactions group statements so they all commit or all roll back. Isolation levels decide what concurrent transactions can see. You do not need to memorise every level on day one; you do need to know that two requests can update the same row and that a transaction is how you keep an account balance honest.

NULL means unknown, and it does not equal NULL. Predicates with NULL surprise everyone once. Parameters ($1, ?) are how you pass user input. Concatenating a user's string into SQL is SQL injection.

Technologies and dependencies

A database engine, a client (psql, a GUI, or a driver in your language), and a schema you wrote down. ORMs generate SQL and migrations. Using PostgreSQL in AI Applications shows Postgres beside an AI app. Permissions in the database (a user that cannot drop tables) are part of the design. Migrations belong in version control next to the code that expects the new column.

How to get started

  1. Install SQLite or Postgres locally. Create two tables: users and orders with a foreign key.
  2. Insert a few rows by hand. Select them. Join them. Filter by a user.
  3. Add an index on the column you filter and look at the plan (EXPLAIN). You are not optimising yet; you are seeing that a plan exists.
  4. Run two updates in a transaction and roll it back. Confirm the data did not change.
  5. From your application, issue the same join with parameters, not string concatenation.
  6. When an ORM enters the project, print the SQL it generated for the one query you already understand.

If you cannot write the join by hand, you cannot review the ORM. Spend the afternoon on SQL before you spend a week on the ORM's API.

Cautions and trade-offs

SELECT * in application code breaks when a column is added and a mapper shifts. Name columns. Unbounded queries without LIMIT will eventually hurt. N+1 queries (one query for the parent, one per child) are the ORM classic; a join or a batched IN list fixes them.

Migrations that lock a big table can take the product down. Learn what your engine locks. Dialects differ: do not paste MySQL tutorials into Postgres and assume types match. Finally, SQL will not model every problem. Hierarchies, graphs and full-text search can be done and can also be the moment to add a specialised tool. Use SQL for the relationships it is good at, and be explicit when you step outside it.