Skip to main content

Week 3

Before we get into PostgreSQL, we need to understand why we use databases in the first place. Right now, if our apps use dictionaries to store our data. The problem is that the data disappears when the server restarts, creating a problem in the future. A database allows us to store our data permanently so it survives restarts, crashes, and can support concurrent users.

There are two categories of databases


 Relational (SQL) Non-Relational (NoSQL)
Structure Tables with rows and columns Similar to JSON
Schema Fixed, predefined Flexible, schema-less
Relationships Tables link with keys Handled in the app's code
Query Language SQL Varies but usually none
Best for Structured data that relates to each other Unstructured or rapidly changing data
Examples PostgreSQL, MySQL MongoDB, Redis

image.png

 

What is a relational database?

A relational database organizes data into tables. Each table represents one thing (a resource, a user, an order, etc). Each row is one record and each column is an attribute of that record, with a defined data type.

id first_name last_name email
1 Miguel Merlin mmerlin@stevens.edu
2 Nishit Sharma nsharma11@stevens.edu

Because these tables are relational, that means they can be linked to each other. An orders table can reference a user table, so every order knows which user placed it. 

 

What is PostgreSQL?

PostgreSQL is an open source Relational Database Management System (RDBMS). The RDBMS is a software layer that sits on top of your data. It enforces rules, handles queries, and manages connections.

 

Tables and Schema

When you create a table in PostgreSQL, you define its schema, the column names and what type of data each column holds. You do this with CREATE TABLE

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255)
);

You have to define this schema otherwise, PostgreSQL will reject any data that doesn't fit the schema

Data Types

Every column has a data type that constrains what values it can store:

Type What it stores Example
INTEGER Whole numbers 42
SERIAL Auto-incrementing integers (use it for IDs) 1, 2, 3, ...
VARCHAR(n) Text up to n characters "Nishit Sharma"
TEXT Unlimited length text Long descriptions
BOOLEAN True/False true
DATE Calendar date 2026-3-25
NUMERIC Decimal numbers 19.99

Primary Keys and Foreign Keys

A primary key is a column that uniquely identifies every row in a table. No two rows can share the same primary key. SERIAL PRIMARY KEY makes PostgreSQL auto assign a new integer ID to each row you insert.

A foreign Key is a column in one table that references the primary key of another table. This creates the relationship between tables

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER REFERENCES users(id),
    amount      NUMERIC,
    order_date  DATE
);

In this example, user_id is a foreign key, and it must match an existing id in the users table. If you try to insert an order with a user_id that doesn't exist, PostgreSQL will reject it automatically.

 

Basic SQL

SQL (Structured Query Language) is the language we will use. Every operation you will perform (creating tables, inserting data, and reading data) is written in SQL.

CREATE TABLE

Defines a new table and its schema:

CREATE TABLE resources (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(255) NOT NULL,
    category      VARCHAR(50),
    description   TEXT
);

NOT NULL means that the column is required, PostgreSQL won't let you insert a row without a value there.

 

INSERT (Create)

Adds a new row to a table:

INSERT INTO resources (name, category, description)
VALUES ('City Food Bank', 'Food', 'Free weekly groceries for families.');

 

SELECT (Read)

Retrieves rows from a table:

-- Get everything
SELECT * FROM resources;

-- Get specific columns
SELECT name, category FROM resources;

-- Filter with WHERE
SELECT * FROM resources WHERE category = 'Food';

 

UPDATE

Changes an existing row:

UPDATE resources
SET description = 'Updated description here.'
WHERE id = 1;

Make sure to include a WHERE clause on UPDATE otherwise you will update every row in the table

 

DELETE

Removes a row:

DELETE FROM resources WHERE id = 1;

 

Relationships between tables

The most common relationship in web apps is one-to-many. This means that one resource can have many referrals, one user can have many orders.

-- One user
CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- Many orders belonging to one user
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INTEGER REFERENCES users(id),
    amount   NUMERIC
);

To retrieve related data across two tables, you can use a JOIN

SELECT users.name, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id;

This pulls the user's name from the users table and matches it to their orders using the foreign key relationship

Back to top