Databases Explained for Beginners: Tables, Rows, and Queries

When you are learning, saving data to a JSON file seems easier than setting up a database. And for tiny projects, it works. But real applications need more:

• Multiple users at once: What happens when two people try to write to the same JSON file at the same time? One write overwrites the other. Databases handle concurrent access safely.
• Speed at scale: Finding one record in a 10-row JSON file is fast. Finding one record in a 1-million-row JSON file is slow. Databases use indexes to find records instantly.
• Data integrity: A database can enforce rules. "Every member must have a unique phone number." "Every contribution must link to a valid member." A JSON file has no such protections.
• Querying: "Show me all contributions over KES 5,000 from last month, sorted by date." Doing this with a JSON file requires writing a lot of JavaScript. With SQL, it is one line.

Let us build a mental model using a chama (informal savings group) contributions tracker. A chama has members, and each member makes contributions every month.

We need two tables:

members table:

| id | name            | phone         | joined_at   |
|----|-----------------|---------------|-------------|
| 1  | Wanjiku Mwangi  | 0712345678    | 2026-01-15  |
| 2  | Ochieng Otieno  | 0723456789    | 2026-01-15  |
| 3  | Aisha Mohamed   | 0734567890    | 2026-02-01  |

contributions table:

| id | member_id | amount | month      | paid_at     |
|----|-----------|--------|------------|-------------|
| 1  | 1         | 5000   | 2026-01-01 | 2026-01-10  |
| 2  | 2         | 5000   | 2026-01-01 | 2026-01-12  |
| 3  | 1         | 5000   | 2026-02-01 | 2026-02-08  |
| 4  | 3         | 5000   | 2026-02-01 | 2026-02-15  |
| 5  | 2         | 3000   | 2026-02-01 | 2026-02-20  |

Key concepts:

• Table: A collection of related data. Like a sheet in a spreadsheet.
• Row: A single record. Each row in the members table is one member.
• Column: A field that every row has. Every member has a name, phone, and joined_at.
• Primary key: The id column. A unique identifier for each row.
• Foreign key: The member_id column in contributions links each contribution to a member. This is how tables relate to each other.

SQL (Structured Query Language) is the language databases understand. Here is how you create the chama tables:

-- Create the members table
CREATE TABLE members (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  phone       TEXT UNIQUE NOT NULL,
  joined_at   DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Create the contributions table
CREATE TABLE contributions (
  id          SERIAL PRIMARY KEY,
  member_id   INTEGER NOT NULL REFERENCES members(id),
  amount      INTEGER NOT NULL CHECK (amount > 0),
  month       DATE NOT NULL,
  paid_at     DATE NOT NULL DEFAULT CURRENT_DATE
);

Notice the rules built into the table definition:

• NOT NULL: This field cannot be empty.
• UNIQUE: No two members can have the same phone number.
• REFERENCES members(id): Every contribution must belong to a valid member.
• CHECK (amount > 0): You cannot contribute a negative amount.
• SERIAL: The id auto-increments. You do not have to set it manually.

The database enforces these rules automatically. If someone tries to insert a contribution for a member_id that does not exist, the database rejects it.

Everything you do with data falls into four categories: Create, Read, Update, Delete (CRUD).

INSERT: adding new data

-- Add a new member
INSERT INTO members (name, phone)
VALUES ('Kamau Njoroge', '0745678901');

-- Add a contribution
INSERT INTO contributions (member_id, amount, month)
VALUES (1, 5000, '2026-03-01');

SELECT: reading data

-- Get all members
SELECT * FROM members;

-- Get members who joined after February
SELECT name, phone FROM members
WHERE joined_at > '2026-02-01';

-- Get total contributions per member
SELECT m.name, SUM(c.amount) AS total
FROM members m
JOIN contributions c ON m.id = c.member_id
GROUP BY m.name
ORDER BY total DESC;

That last query joins the two tables together, sums up each member's contributions, and sorts by the total. This is where SQL shines: complex questions answered in a few lines.

UPDATE: changing existing data

-- Update a member's phone number
UPDATE members
SET phone = '0756789012'
WHERE id = 3;

DELETE: removing data

-- Delete a contribution
DELETE FROM contributions
WHERE id = 5;

Always use a WHERE clause with UPDATE and DELETE. Without it, you will update or delete every row in the table.

In a real app, you do not type SQL into a terminal. You send queries from your code. Here is how you query a Supabase PostgreSQL database from a Next.js app:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Get all members
const { data: members, error } = await supabase
  .from('members')
  .select('*');

// Get contributions for a specific member
const { data: contributions } = await supabase
  .from('contributions')
  .select('amount, month, paid_at')
  .eq('member_id', 1)
  .order('month', { ascending: false });

// Add a new contribution
const { data: newContribution } = await supabase
  .from('contributions')
  .insert({
    member_id: 1,
    amount: 5000,
    month: '2026-03-01',
  })
  .select()
  .single();

Supabase's client library generates SQL behind the scenes. The .eq('member_id', 1) becomes WHERE member_id = 1. The .order('month', { ascending: false }) becomes ORDER BY month DESC.

Whether you use Supabase, Drizzle, Prisma, or raw SQL, the underlying concepts are the same: tables, rows, columns, and CRUD operations.

SQL databases (PostgreSQL, MySQL, SQLite) store data in structured tables with defined columns. They enforce data types and relationships. They use SQL for queries. PostgreSQL is the most popular choice for web apps.

NoSQL databases (MongoDB, Firebase Firestore, DynamoDB) store data as flexible documents (similar to JSON objects). They do not require a fixed schema. They use their own query languages.

Learn SQL first. Here is why:

• SQL databases are more common in job listings.
• SQL is a universal skill. The syntax works across PostgreSQL, MySQL, SQLite, and others.
• Data relationships (foreign keys, joins) are easier to model and enforce in SQL databases.
• Most NoSQL use cases can be handled by PostgreSQL with JSONB columns.

NoSQL databases have legitimate use cases (real-time sync, massive unstructured data, document storage), but for your first web app and your first job, PostgreSQL is the better investment.