ping·aiCSTU · CSE 642 · Foundations · Database basics
Part A · The words

Why a database

Memory that survives a restart.

A program that only keeps data in memory forgets it when it stops. A database stores data on disk in an organized way, so the app can save it, find it again, and share it between users.

In the course app, every question and answer is saved as a row in a table.

Slide 1 of 14

Read this deck as an article

The same content as the slides above, in one page.

Why a database

Memory that survives a restart.

A program that only keeps data in memory forgets it when it stops. A database stores data on disk in an organized way, so the app can save it, find it again, and share it between users.

In the course app, every question and answer is saved as a row in a table.

Two places you type

A terminal takes exact commands. A Coding Agent App takes plain-language prompts.

The Terminal

A text window where you type exact commands. The shell inside it runs each command the moment you press Enter.

  • Spelling and spacing must be exact.
  • macOS and Linux use zsh or bash. Upper and lower case matter there: Git is not git.
  • Windows uses PowerShell. It does not care about case in command names, but type them as shown anyway.
  • Names inside a command, such as Linux file paths, branch names and image names, can be case-sensitive in any shell.
  • The two kinds of shell have different commands: ls -la works in zsh and bash, while PowerShell uses Get-ChildItem -Force.

Terminal command, macOS and Linux (zsh or bash, case-sensitive): ls -la

Terminal command, Windows PowerShell (command names are not case-sensitive): Get-ChildItem -Force

A Coding Agent App

It looks like a chatbot, but it does much more: it can read your project files, run commands, edit code and use tools, and it often asks your permission first. Product names and features change fast and differ a lot, so these decks use one group name for all of them.

Examples: Kiro, Claude Code Desktop, GitHub Copilot's desktop app, Google Antigravity, the Codex and ChatGPT desktop apps, OpenCode, Hermes, LangChain's Deep Agents desktop app, and others. Which one you use does not matter here.

  • Plain language is fine. Say what you want, and use the right names for things.
  • Capital letters and small slips are forgiving; the meaning is what counts.
  • It may run terminal commands for you. Read what it proposes, especially anything that deletes files or sends data.
  • Pasting a command into the prompt does not guarantee it runs as typed. Ask for the result you want, or ask the app to explain the command first.
Prompt for a Coding Agent App: “What changed in my project since the last commit? Explain it in plain words.”

The same job, two ways

Terminal command, Any shell: git status

In a terminal you type the exact command. In a Coding Agent App you describe what you want.

Six words, part 1

Each card gives the word, what it means, and a sentence you can say to a Coding Agent App.

Table
One kind of thing, such as messages or customers.
Prompt for a Coding Agent App: “Which tables does this app have?”
Row
One item in a table: one message, one customer.
Prompt for a Coding Agent App: “Show me the last five rows.”
Column
One fact about every item, such as the text of a message or when it was created.
Prompt for a Coding Agent App: “Add a column for the user's name.”
Primary key
A column whose value is different in every row, so a row can be found exactly.
Prompt for a Coding Agent App: “What is the primary key of this table?”

Six words, part 2

Query
A question you ask the database, such as “the messages from this session”.
Prompt for a Coding Agent App: “Write a query that finds today's messages.”
SQL
The language for queries and changes in a relational database.
Prompt for a Coding Agent App: “Explain this SQL line by line.”
Schema
The design: which tables, which columns, and what type each holds.
Prompt for a Coding Agent App: “Draw the schema of this database.”
Migration
A file that changes the schema in a controlled, repeatable way.
Prompt for a Coding Agent App: “Write a migration that adds this column.”

Wrong word, right word: “a spreadsheet tab” is a table; “a line in it” is a row.

A table, pictured

The messages table in the course starter app.

id
Primary key A number the database assigns to each new row.
session_id
Text Groups the messages of one chat.
role
“user” or “assistant” Who wrote the message.
content
Text What was said.
created_at
A timestamp When the row was saved.
One row
One message Ask a question in the app and a new row appears.

Four things SQL does

Read, add, change, remove.

SELECT

Terminal command, SQL, typed inside psql: select role, content from messages order by id;

Read rows. Say which columns, which table, and in what order.
INSERT

Terminal command, SQL, typed inside psql: insert into messages (session_id, role, content) values ('a', 'user', 'hi');

Add a row.
UPDATE

Terminal command, SQL, typed inside psql: update messages set content = 'hello' where id = 1;

Change rows. Without where, it changes every row.
DELETE

Terminal command, SQL, typed inside psql: delete from messages where id = 1;

Remove rows. Without where, it removes every row.

Always check the where part before you run update or delete.

Reading a SELECT

select role, content from messages where session_id = 'a' order by id;

Terminal command, SQL, typed inside psql: select role, content

Which columns Use * for all columns.

Terminal command, SQL, typed inside psql: from messages

Which table Where the rows live.

Terminal command, SQL, typed inside psql: where session_id = 'a'

Which rows A filter. Text values go in single quotes.

Terminal command, SQL, typed inside psql: order by id

In what order Add desc for newest first, and limit 5 for only five rows.

Keys and relationships

Primary key
Identifies one row in its own table No two rows share it.
Foreign key
A column that points to a row in another table For example a message pointing to the user who wrote it.
Index
A lookup structure that makes a query faster Like the index of a book; it costs a little space and write time.
Join
A query that combines rows from two tables Matches a foreign key to the primary key it points to.

Migrations, and why a new database is empty

  • A migration is a numbered SQL file. Running them in order builds the same schema on every computer.
  • In the course starter app, the database container applies the migration files only when its data volume is new.
  • So a new migration file does not change a database you already have. Reset with docker compose down -v, then docker compose up.
  • That reset deletes all rows in that database. Do it only when you can afford to lose the data.

Say to your AI: “Write a migration for this change, and tell me how to apply it to my local database.”

Look at your data

In the course starter app, open the database from the project folder.

Terminal command, Any shell: docker compose exec db psql -U postgres

Open the database prompt psql is PostgreSQL's command-line client.

Terminal command, SQL, typed inside psql: \dt

List the tables Typed at the psql prompt.

Terminal command, SQL, typed inside psql: \d messages

Describe one table Shows its columns and types.

Terminal command, SQL, typed inside psql: \q

Leave psql Or press Ctrl + D.

End every SQL statement with a semicolon, or psql waits for more input.

Embeddings and vector search

Finding text by meaning, not by exact words.

  • An embedding is a list of numbers that stands for the meaning of a piece of text. Similar meanings get nearby lists.
  • A vector column stores an embedding. Vector search finds the stored embeddings closest to the embedding of your question.
  • pgvector is a PostgreSQL extension that adds the vector column type and the distance operators, so one database holds both your rows and your embeddings.
  • The nearest items are candidates, not facts. A model still needs to read them and an app should check them.

Say to your AI: “Add an embedding column to this table and show me a query for the five closest rows.”

Common problems and the words to use

Connection refused
The database is not running or the port is wrong Check docker compose ps and the port in the connection string.
relation "messages" does not exist
The table was never created The migration did not run. Reset with docker compose down -v, then up.
password authentication failed
The user or password does not match Compare the connection string with the database settings.
Rows disappeared
The data volume was deleted down -v removes it. A plain docker compose down keeps your data.
syntax error at or near ...
A typo in the SQL Paste the whole statement and the error into your Coding Agent App.
Update changed everything
The where part was missing Restore from a backup if you have one, and always read where before you run.

Say it to your Coding Agent App

Name the table and the columns, and paste the exact error.

Prompt for a Coding Agent App: “The messages table has columns id, session_id, role, content, created_at. Write a query for the last 10 user messages.”
Prompt for a Coding Agent App: “This query fails with: <paste the exact error>. What is wrong?”
Prompt for a Coding Agent App: “Explain what this update will change, and how to check it first with a select.”
Prompt for a Coding Agent App: “Design a schema for customers and orders and explain the keys.”
  • Never paste passwords or connection strings with real credentials into a prompt.

Learn more from the source

Checked against the linked pages on 7 November 2026.

  • SQLBolt teaches SQL with short interactive lessons.
  • The PostgreSQL tutorial walks through tables, queries and keys.
  • The pgvector repository explains vector columns and nearest-neighbor search.