SQL Database Basics

SQL databases store structured data in tables and let you query it with a declarative language. The database is not just a storage bucket. It enforces relationships, protects consistency, and answers questions efficiently when modeled well.

Tables and Relationships

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

CREATE TABLE tasks (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  completed_at TIMESTAMP
);

Primary keys identify rows. Foreign keys connect rows and prevent orphaned data. NOT NULL and UNIQUE constraints move important rules into the database, where every code path must respect them.

Reading Related Data

SELECT
  tasks.id,
  tasks.title,
  users.email AS owner_email
FROM tasks
JOIN users ON users.id = tasks.user_id
WHERE tasks.completed_at IS NULL;

Joins are one of SQL’s core strengths. Instead of duplicating user email into every task row, store it once and join when needed.

Transactions

Transactions group several database changes into one all-or-nothing operation.

async function transferCoins(fromUserId: string, toUserId: string, amount: number) {
  await db.transaction(async (tx) => {
    await tx.execute("UPDATE wallets SET coins = coins - ? WHERE user_id = ?", [amount, fromUserId]);
    await tx.execute("UPDATE wallets SET coins = coins + ? WHERE user_id = ?", [amount, toUserId]);
  });
}

Further Learning

Search these terms to go deeper:

• “SQL primary key foreign key constraints” — database integrity fundamentals
• “SQL joins visual explanation” — inner, left, and outer joins
• “database transactions ACID” — guarantees behind reliable writes
• “PostgreSQL data modeling basics” — practical relational schema design

SQL Database Basics

A SQL database stores your data in tables — like a set of connected spreadsheets — and lets you ask questions about it with a special language (SQL). It’s not just a storage bin; it actively enforces rules and connects related data for you.

Tables and connecting them

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

CREATE TABLE tasks (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  title TEXT NOT NULL
);

• The primary key uniquely identifies each row (like a row ID).
• A foreign key (user_id REFERENCES users(id)) connects a task to its owner, and stops you from ever pointing to a user that doesn’t exist.

Reading connected data with JOIN

Instead of copying a user’s email into every one of their tasks, store it once and combine tables when you need it:

SELECT tasks.title, users.email
FROM tasks
JOIN users ON users.id = tasks.user_id

This is one of SQL’s superpowers — pulling related data together in one query.

Transactions: all-or-nothing

Some operations need multiple writes to happen together, or not at all. Say you’re transferring coins between two users — you never want money to leave one account without arriving in the other:

await db.transaction(async (tx) => {
  await tx.execute('UPDATE wallets SET coins = coins - ? WHERE user_id = ?', [amount, fromId])
  await tx.execute('UPDATE wallets SET coins = coins + ? WHERE user_id = ?', [amount, toId])
})

In one sentence

SQL databases store data in connected tables (linked by primary/foreign keys), let you combine related data with JOIN, and wrap multi-step writes in a transaction so they either all succeed or all roll back.

Want to go deeper?

Switch to Expert mode above for constraints, more join types, and ACID transaction guarantees.