Database Basics: Introduction to PostgreSQL for Beginners

Table of Contents

• What is a Database?
• Why PostgreSQL?
• Installing PostgreSQL
• Understanding Database Concepts
• Your First Database
• Essential SQL Commands
• Working with Tables
• CRUD Operations
• Understanding Relationships
• Querying Data
• Connecting PostgreSQL to Your Application
• Best Practices
• Common Mistakes to Avoid
• Troubleshooting Guide

---

Introduction

Databases are the backbone of modern applications, storing everything from user accounts to product inventories. In this tutorial, you'll learn PostgreSQL, one of the most powerful and popular open-source database systems used by companies like Instagram, Spotify, and Reddit.

What you'll learn:

• What databases are and why they matter
• How to install and set up PostgreSQL
• Essential SQL commands for data management
• Creating tables and establishing relationships
• Connecting databases to applications
• Database design best practices

Prerequisites:

• Basic command-line knowledge
• A computer running Windows, macOS, or Linux
• No prior database experience required

Time to complete: 45-60 minutes

---

What is a Database?

A database is an organized collection of data that can be easily accessed, managed, and updated. Think of it as a digital filing cabinet where information is stored in a structured way.

Real-World Analogy

Imagine a library:

• Database = The entire library building
• Tables = Different sections (Fiction, Non-Fiction, Reference)
• Rows = Individual books on the shelves
• Columns = Book properties (Title, Author, ISBN, Publication Date)

Why Do We Need Databases?
The Problem: Life Without a Database
Imagine you're building a simple user registration website. Without a database, you might try to store user data in your application's memory using variables or files. Let's see what goes wrong:
Problem 1: Data Disappears When Your App Stops
Without a database:
javascript// Storing users in a JavaScript array
let users = [
{ id: 1, name: "John", email: "[e-mail]" },
{ id: 2, name: "Sarah", email: "[e-mail]" }
];

// User signs up
users.push({ id: 3, name: "Mike", email: "[e-mail]" });

console.log(users); // Shows 3 users

// But if your server crashes or restarts...
// ALL DATA IS LOST! 💥
// users array starts empty again: []
Real-world scenario:

A new user registers on your website at 2 PM
Your server crashes at 3 PM
When the server restarts, that user's account is completely gone
They can't log in anymore - their data vanished!

With a database:
javascript// Data is saved to disk permanently
await db.query(
'INSERT INTO users (name, email) VALUES ($1, $2)',
['Mike', '[e-mail]']
);

// Even if server crashes, Mike's data is safely stored
// When server restarts, Mike can still log in ✅
Problem 2: You Can't Handle Multiple Users at Once
Without a database:
javascriptlet currentUser = null;

// User 1 logs in
currentUser = { name: "John", email: "[e-mail]" };

// User 2 logs in at the same time
currentUser = { name: "Sarah", email: "[e-mail]" };

// Now John is logged out! Only Sarah's data exists.
// This is called a "race condition" 🏃‍♂️💨
Real-world scenario:

100 people try to buy the last concert ticket
Without proper data management, the ticket gets sold to multiple people
Or worse, nobody gets it because data gets overwritten

With a database:
Databases handle thousands of simultaneous users safely. PostgreSQL uses locks and transactions to prevent conflicts.
Problem 3: No Way to Search or Filter Efficiently
Without a database:
javascriptconst users = [
{ id: 1, name: "John Doe", age: 28, city: "New York" },
{ id: 2, name: "Sarah Smith", age: 32, city: "London" },
// ... imagine 1 million more users
];

// Find all users over 30 in London
const results = users.filter(user =>
user.age > 30 && user.city === "London"
);

// This checks EVERY SINGLE USER one by one
// With 1 million users, this takes forever! 🐌
With a database:
sql-- This uses indexes and returns results instantly
SELECT * FROM users
WHERE age > 30 AND city = 'London';

-- Even with millions of users, this is fast ⚡
Problem 4: Data Gets Corrupted or Lost
Without a database (saving to files):
javascriptconst fs = require('fs');

// Try to save user data to a file
try {
const users = JSON.stringify(usersArray);
fs.writeFileSync('users.txt', users);
} catch (error) {
// What if the disk is full?
// What if the server crashes mid-write?
// File could be corrupted or half-written!
console.error('Failed to save:', error);
}
Real problems that happen:

Server crashes while writing → file is corrupted, all data lost
Disk runs out of space → partial data saved, rest deleted
Two processes write at once → file contents become scrambled
Accidental deletion → no way to recover

With a database:

Transactions ensure data is fully saved or not saved at all (no half-saved data)
Write-ahead logs allow recovery after crashes
Backups can be automated and restored
ACID compliance guarantees data integrity

Problem 5: No Data Relationships
Without a database:
javascript// You have to manually track relationships
const users = [
{ id: 1, name: "John" }
];

const posts = [
{ id: 1, title: "My Post", userId: 1 },
{ id: 2, title: "Another Post", userId: 1 }
];

// To find all posts by John, you must:
// 1. Find John's ID
const john = users.find(u => u.name === "John");
// 2. Manually search through all posts
const johnsPosts = posts.filter(p => p.userId === john.id);

// With thousands of users and millions of posts?
// This becomes impossibly slow and error-prone
With a database:
sql-- One simple query handles everything
SELECT posts.*
FROM posts
JOIN users ON posts.user_id = users.id
WHERE users.name = 'John';

-- Fast, reliable, and easy to maintain
Problem 6: No Security or Access Control
Without a database:
javascript// Everyone with file access can read/modify data
const users = JSON.parse(fs.readFileSync('users.txt'));

// No way to:
// - Require authentication before reading
// - Limit what different users can see
// - Track who changed what data
// - Encrypt sensitive information properly
With a database:
sql-- You can control exactly who can do what
GRANT SELECT ON users TO read_only_user;
GRANT INSERT, UPDATE ON users TO editor_user;
GRANT ALL ON users TO admin_user;

-- Plus built-in security features like:
-- - SSL/TLS encryption for connections
-- - Row-level security
-- - Audit logging
-- - Password hashing
Problem 7: Hard to Scale
Without a database:
javascript// All data stored in one file on one computer
const allUsers = readFromFile('users.txt'); // 10 GB file!

// Problems:
// - File gets huge and slow to read
// - Can't split across multiple servers
// - No way to distribute the load
// - One server failure = everything down
With a database:

Can distribute data across multiple servers (sharding)
Read replicas handle high traffic
Automatic failover if one server crashes
Can handle billions of records efficiently

Summary: Why Databases Are Essential
Without DatabaseWith Database❌ Data lost on restart✅ Data persists forever❌ Can't handle concurrent users✅ Thousands of simultaneous users❌ Slow searches✅ Lightning-fast queries❌ Data easily corrupted✅ Guaranteed data integrity❌ Manual relationship tracking✅ Automatic relationship management❌ No built-in security✅ Enterprise-grade security❌ Can't scale✅ Scales to billions of records❌ No backup/recovery✅ Automated backups & recovery
Real-World Example: An E-commerce Site
Without a database:

Customer adds item to cart → stored in variable
Server restarts → cart is empty
Two customers buy last item → both orders accepted, inventory negative
Want to find "red shoes size 10" → check every single product
Server crashes during checkout → money charged but order lost
Result: Angry customers, lost revenue, business failure 💸

With a database:

Cart saved in database → survives restarts
Transaction locks prevent overselling
Indexed searches find products instantly
Failed transactions automatically roll back
Result: Happy customers, reliable service, successful business ✅

Types of Data Stored in Databases

• User accounts - Usernames, passwords, profiles
• E-commerce data - Products, orders, inventory
• Social media - Posts, comments, likes, followers
• Financial records - Transactions, balances, invoices
• Analytics - Page views, user behavior, metrics

---

Why PostgreSQL?

PostgreSQL (often called "Postgres") is a powerful, open-source relational database management system. Here's why it's an excellent choice:

Key Advantages

1. Free and Open Source

• No licensing costs
• Active community support
• Transparent development

2. Feature-Rich

• Advanced data types (JSON, arrays, geometric types)
• Full-text search capabilities
• Support for both SQL and NoSQL workloads
• Excellent performance and reliability

3. Industry Standard

• Used by major companies (Apple, Netflix, Instagram)
• Extensive documentation and resources
• Large job market demand

4. ACID Compliant

• Atomicity - Transactions complete fully or not at all
• Consistency - Data remains valid after transactions
• Isolation - Concurrent transactions don't interfere
• Durability - Committed data is permanently saved

PostgreSQL vs Other Databases

Feature	PostgreSQL	MySQL	SQLite
Open Source	✅ Yes	✅ Yes	✅ Yes
Best For	Complex applications	Web applications	Small projects
ACID Compliance	✅ Full	⚠️ Partial	✅ Full
JSON Support	✅ Excellent	⚠️ Limited	❌ No
Scalability	✅ Excellent	✅ Good	⚠️ Limited

---

Installing PostgreSQL

Let's install PostgreSQL on your system. Choose your operating system below:

Windows Installation

Step 1: Download the installer

• Visit postgresql.org/download/windows
• Click "Download the installer"
• Choose the latest version (e.g., PostgreSQL 16)

Step 2: Run the installer

• Double-click the downloaded file
• Click "Next" through the setup wizard
• Choose installation directory (default is fine)
• Select components (keep all checked)
• Choose data directory (default is fine)

Step 3: Set password

• IMPORTANT: Set a password for the postgres user
• Write this down - you'll need it later!
• Example: mypassword123 (use something secure in production)

Step 4: Configure port

• Default port: 5432 (keep this unless you have conflicts)
• Click "Next" and complete installation

Step 5: Verify installation

• Open Command Prompt (cmd)
• Type: psql --version
• You should see something like: psql (PostgreSQL) 16.0

macOS Installation

Method 1: Using Homebrew (Recommended)

# Install Homebrew if you haven't already
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install PostgreSQL
brew install postgresql@16

# Start PostgreSQL service
brew services start postgresql@16

# Verify installation
psql --version

Method 2: Using PostgreSQL.app

• Download from postgresapp.com
• Drag to Applications folder
• Open the app and click "Initialize"
• PostgreSQL is now running!

Linux Installation (Ubuntu/Debian)

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
psql --version

Accessing PostgreSQL

After installation, access PostgreSQL:

Windows:

psql -U postgres
# Enter the password you set during installation

macOS/Linux:

sudo -u postgres psql
# Or simply: psql postgres

You should see the PostgreSQL prompt:

postgres=#

Congratulations! PostgreSQL is now installed and running.

---

Understanding Database Concepts

Before writing SQL, let's understand key database concepts.

Tables

A table is a collection of related data organized in rows and columns.

Example: Users Table

id	name	email	age
1	John Doe	[e-mail]	28
2	Sarah Smith	[e-mail]	32
3	Mike Johnson	[e-mail]	25

Rows and Columns

• Columns (also called fields) - Define what type of data is stored (name, email, age)
• Rows (also called records) - Individual entries in the table (each person)

Data Types

PostgreSQL supports many data types. Here are the most common:

Text Types:

• VARCHAR(n) - Variable-length text with maximum length
• TEXT - Unlimited length text
• CHAR(n) - Fixed-length text

Numeric Types:

• INTEGER - Whole numbers (-2147483648 to 2147483647)
• BIGINT - Large whole numbers
• DECIMAL(p,s) - Exact decimal numbers
• REAL - Floating-point numbers

Date/Time Types:

• DATE - Date only (YYYY-MM-DD)
• TIME - Time only (HH:MM:SS)
• TIMESTAMP - Date and time combined

Boolean:

• BOOLEAN - True or false values

Special Types:

• JSON - Store JSON data
• ARRAY - Store arrays of values
• UUID - Universally unique identifiers

Primary Keys

A primary key is a unique identifier for each row in a table.

-- The 'id' column is typically the primary key
id | name
---|------
1  | John    ← This '1' uniquely identifies John
2  | Sarah   ← This '2' uniquely identifies Sarah

Rules for primary keys:

• Must be unique for each row
• Cannot be NULL (empty)
• Typically auto-incremented (1, 2, 3, 4...)

Schemas

A schema is a namespace that contains database objects like tables, views, and functions. Think of it as a folder organizing your database.

Default schema: public

---

Your First Database

Let's create your first database and explore it.

Creating a Database

Step 1: Connect to PostgreSQL

psql -U postgres

Step 2: Create a new database

CREATE DATABASE my_first_db;

You should see:

CREATE DATABASE

Step 3: List all databases

\l

You'll see a list of databases including my_first_db.

Step 4: Connect to your new database

\c my_first_db

You should see:

You are now connected to database "my_first_db" as user "postgres".

Essential PostgreSQL Commands

These commands help you navigate PostgreSQL:

Command	Purpose
\l	List all databases
\c database_name	Connect to a database
\dt	List all tables in current database
\d table_name	Describe a table's structure
\q	Quit PostgreSQL
\?	Show all commands
\h SQL_COMMAND	Get help on a SQL command

Example:

-- Get help on CREATE TABLE
\h CREATE TABLE

---

Essential SQL Commands

SQL (Structured Query Language) is the language used to communicate with databases. Let's learn the fundamental commands.

The Four Main Operations (CRUD)

• Create - Add new data
• Read - Retrieve data
• Update - Modify existing data
• Delete - Remove data

We'll explore each in detail in the coming sections.

SQL Syntax Rules

Important conventions:

• SQL keywords are typically written in UPPERCASE (SELECT, FROM, WHERE)
• Table and column names are lowercase
• Statements end with a semicolon ;
• SQL is case-insensitive for keywords, but case-sensitive for data

Example:

-- These are all valid and identical:
SELECT * FROM users;
select * from users;
SeLeCt * FrOm users;

-- But 'John' and 'john' are different in data:
SELECT * FROM users WHERE name = 'John';  -- Finds 'John'
SELECT * FROM users WHERE name = 'john';  -- Finds 'john' (different!)

---

Working with Tables

Tables are where your data lives. Let's learn to create and manage them.

Creating Your First Table

Let's create a table to store user information:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Breaking it down:

• CREATE TABLE users - Creates a new table named "users"
• id SERIAL PRIMARY KEY - Auto-incrementing unique identifier
• name VARCHAR(100) NOT NULL - Name (max 100 characters, required)
• email VARCHAR(255) UNIQUE NOT NULL - Email (must be unique and required)
• age INTEGER - Age (optional, can be NULL)
• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP - Automatically set to current time

Constraints explained:

• PRIMARY KEY - Unique identifier for each row
• NOT NULL - Field cannot be empty
• UNIQUE - No two rows can have the same value
• DEFAULT - Sets a default value if none provided
• SERIAL - Auto-incrementing integer (1, 2, 3, ...)

Viewing Table Structure

-- See the table structure
\d users

Output will show:

Column     | Type                     | Modifiers
-----------+--------------------------+----------------------------------
id         | integer                  | not null default nextval(...)
name       | character varying(100)   | not null
email      | character varying(255)   | not null
age        | integer                  |
created_at | timestamp                | default now()

Modifying Tables (ALTER)

Add a new column:

ALTER TABLE users ADD COLUMN city VARCHAR(100);

Remove a column:

ALTER TABLE users DROP COLUMN age;

Rename a column:

ALTER TABLE users RENAME COLUMN name TO full_name;

Change column type:

ALTER TABLE users ALTER COLUMN city TYPE TEXT;

Deleting Tables

⚠️ Warning: This permanently deletes all data!

DROP TABLE users;

Safer option - only drop if exists:

DROP TABLE IF EXISTS users;

---

CRUD Operations (Create, Read, Update, Delete)

Now let's learn to manipulate data in your tables.

CREATE - Inserting Data

Insert a single row:

INSERT INTO users (name, email, age)
VALUES ('John Doe', '[e-mail]', 28);

Insert multiple rows:

INSERT INTO users (name, email, age)
VALUES 
  ('Sarah Smith', '[e-mail]', 32),
  ('Mike Johnson', '[e-mail]', 25),
  ('Emily Davis', '[e-mail]', 29);

Insert with auto-generated timestamp:

-- created_at will automatically be set to current time
INSERT INTO users (name, email, age)
VALUES ('Anna Wilson', '[e-mail]', 27);

Return inserted data:

INSERT INTO users (name, email, age)
VALUES ('Tom Brown', '[e-mail]', 31)
RETURNING *;

This shows you the complete row that was inserted, including auto-generated fields like id and created_at.

READ - Retrieving Data

Select all rows and columns:

SELECT * FROM users;

Select specific columns:

SELECT name, email FROM users;

Select with conditions (WHERE):

-- Users older than 30
SELECT * FROM users WHERE age > 30;

-- Users named John
SELECT * FROM users WHERE name = 'John Doe';

-- Users with email containing 'example'
SELECT * FROM users WHERE email LIKE '%example%';

Combining conditions:

-- AND - both conditions must be true
SELECT * FROM users WHERE age > 25 AND age < 35;

-- OR - at least one condition must be true
SELECT * FROM users WHERE name = 'John Doe' OR name = 'Sarah Smith';

-- NOT - negates a condition
SELECT * FROM users WHERE NOT age > 30;

Sorting results (ORDER BY):

-- Sort by age (ascending)
SELECT * FROM users ORDER BY age ASC;

-- Sort by age (descending)
SELECT * FROM users ORDER BY age DESC;

-- Sort by multiple columns
SELECT * FROM users ORDER BY age DESC, name ASC;

Limiting results:

-- Get only the first 5 users
SELECT * FROM users LIMIT 5;

-- Get users 6-10 (pagination)
SELECT * FROM users LIMIT 5 OFFSET 5;

Counting rows:

-- Count total users
SELECT COUNT(*) FROM users;

-- Count users over 30
SELECT COUNT(*) FROM users WHERE age > 30;

UPDATE - Modifying Data

Update a single field:

UPDATE users
SET age = 29
WHERE name = 'John Doe';

Update multiple fields:

UPDATE users
SET 
  age = 33,
  email = '[e-mail]'
WHERE id = 1;

Update all rows (⚠️ Be careful!):

-- This updates ALL users!
UPDATE users SET city = 'Unknown';

Update with calculation:

-- Increase everyone's age by 1
UPDATE users SET age = age + 1;

Update and return modified rows:

UPDATE users
SET age = 30
WHERE name = 'John Doe'
RETURNING *;

DELETE - Removing Data

Delete specific rows:

DELETE FROM users WHERE name = 'John Doe';

Delete with multiple conditions:

DELETE FROM users WHERE age < 18 OR age > 100;

Delete all rows (⚠️ Dangerous!):

-- This deletes ALL data!
DELETE FROM users;

Better approach - use TRUNCATE for deleting all:

-- Faster than DELETE for removing all rows
TRUNCATE TABLE users;

CRUD Operation Summary

-- CREATE
INSERT INTO users (name, email) VALUES ('New User', '[e-mail]');

-- READ
SELECT * FROM users WHERE age > 25;

-- UPDATE
UPDATE users SET age = 30 WHERE name = 'John Doe';

-- DELETE
DELETE FROM users WHERE id = 5;

---

Understanding Relationships (Foreign Keys)

Real-world data is connected. Let's learn how to establish relationships between tables.

Why Relationships Matter

Imagine an e-commerce system:

• Users place orders
• Orders contain products
• Products have categories

Without relationships, you'd duplicate data everywhere. With relationships, you connect data efficiently.

Types of Relationships

1. One-to-Many

• One user can have many orders
• One category can have many products

2. Many-to-Many

• One order can have many products
• One product can be in many orders

3. One-to-One

• One user has one profile
• One profile belongs to one user

Creating Related Tables

Let's create a blog system with users and posts:

Step 1: Create the users table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the posts table with a foreign key

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key concepts:

• user_id INTEGER - Stores the ID of the user who created the post
• REFERENCES users(id) - Creates a foreign key relationship to the users table
• ON DELETE CASCADE - If a user is deleted, all their posts are automatically deleted

Foreign Key Constraints

ON DELETE options:

• CASCADE - Delete related records automatically
• SET NULL - Set foreign key to NULL when parent is deleted
• RESTRICT - Prevent deletion if related records exist
• NO ACTION - Similar to RESTRICT (default)

Example:

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

If a post is deleted, all its comments are deleted.

If a user is deleted, their comments remain but user_id becomes NULL.

Working with Related Data

Insert related data:

-- First, insert a user
INSERT INTO users (username, email)
VALUES ('johndoe', '[e-mail]')
RETURNING id;
-- Let's say this returns id = 1

-- Then, insert a post for that user
INSERT INTO posts (title, content, user_id)
VALUES ('My First Post', 'Hello, World!', 1);

---

Querying Data (Advanced SELECTs)

Learn to retrieve data from multiple tables using JOINs.

Understanding JOINs

JOINs combine rows from two or more tables based on related columns.

INNER JOIN

Returns only matching rows from both tables.

-- Get all posts with their author's username
SELECT 
  posts.title,
  posts.content,
  users.username
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Result:

title           | content        | username
----------------|----------------|----------
My First Post   | Hello, World!  | johndoe
Another Post    | Great day!     | sarahsmith

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table (or NULL if no match).

-- Get all users and their posts (even users with no posts)
SELECT 
  users.username,
  posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

Result:

username    | title
------------|---------------
johndoe     | My First Post
sarahsmith  | Another Post
mikejohnson | NULL           ← Mike hasn't written any posts

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left table.

-- Get all posts and their authors (even orphaned posts)
SELECT 
  users.username,
  posts.title
FROM users
RIGHT JOIN posts ON users.id = posts.user_id;

Aggregate Functions

Perform calculations on multiple rows.

COUNT - Count rows:

-- Count total posts
SELECT COUNT(*) FROM posts;

-- Count posts per user
SELECT 
  users.username,
  COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.username;

SUM - Calculate totals:

-- Sum of all order amounts
SELECT SUM(amount) FROM orders;

-- Total spent per user
SELECT 
  user_id,
  SUM(amount) as total_spent
FROM orders
GROUP BY user_id;

AVG - Calculate average:

-- Average age of users
SELECT AVG(age) FROM users;

MAX and MIN:

-- Oldest user
SELECT MAX(age) FROM users;

-- Youngest user
SELECT MIN(age) FROM users;

GROUP BY and HAVING

GROUP BY groups rows with same values.

-- Count posts per user
SELECT 
  user_id,
  COUNT(*) as post_count
FROM posts
GROUP BY user_id;

HAVING filters grouped results (like WHERE for groups).

-- Find users with more than 5 posts
SELECT 
  user_id,
  COUNT(*) as post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5;

Subqueries

A query inside another query.

-- Find users who have written posts
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM posts);

-- Find users with above-average post counts
SELECT username
FROM users
WHERE id IN (
  SELECT user_id
  FROM posts
  GROUP BY user_id
  HAVING COUNT(*) > (SELECT AVG(post_count) FROM (
    SELECT COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
  ) as subquery)
);

---

Connecting PostgreSQL to Your Application

Let's connect a database to a Node.js application.

Setting Up Node.js with PostgreSQL

Step 1: Install the pg library

npm install pg

Step 2: Create a connection file (db.js)

const { Pool } = require('pg');

// Create a connection pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'my_first_db',
  password: 'your_password',
  port: 5432,
});

module.exports = pool;

Executing Queries from Node.js

Simple query:

const pool = require('./db');

async function getAllUsers() {
  try {
    const result = await pool.query('SELECT * FROM users');
    console.log(result.rows);
  } catch (error) {
    console.error('Error:', error);
  }
}

getAllUsers();

Parameterized query (prevents SQL injection):

async function getUserByEmail(email) {
  try {
    const result = await pool.query(
      'SELECT * FROM users WHERE email = $1',
      [email]
    );
    return result.rows[0];
  } catch (error) {
    console.error('Error:', error);
  }
}

getUserByEmail('[e-mail]');

Insert data:

async function createUser(name, email, age) {
  try {
    const result = await pool.query(
      'INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING *',
      [name, email, age]
    );
    console.log('User created:', result.rows[0]);
    return result.rows[0];
  } catch (error) {
    console.error('Error:', error);
  }
}

createUser('Alice Johnson', '[e-mail]', 26);

Update data:

async function updateUserAge(id, newAge) {
  try {
    const result = await pool.query(
      'UPDATE users SET age = $1 WHERE id = $2 RETURNING *',
      [newAge, id]
    );
    console.log('User updated:', result.rows[0]);
  } catch (error) {
    console.error('Error:', error);
  }
}

updateUserAge(1, 29);

Delete data:

async function deleteUser(id) {
  try {
    await pool.query('DELETE FROM users WHERE id = $1', [id]);
    console.log(`User ${id} deleted`);
  } catch (error) {
    console.error('Error:', error);
  }
}

deleteUser(5);

Why Use Parameterized Queries?

❌ Never do this (SQL Injection vulnerability):

// DANGEROUS - user input goes directly into query
const query = `SELECT * FROM users WHERE email = '${userEmail}'`;
pool.query(query);

✅ Always do this:

// SAFE - parameterized query
pool.query('SELECT * FROM users WHERE email = $1', [userEmail]);

If someone enters ' OR '1'='1 as email, the unsafe version would return all users!

---

Best Practices for Database Design

1. Use Meaningful Names

Bad:

CREATE TABLE t1 (
  a INTEGER,
  b VARCHAR(50)
);

Good:

CREATE TABLE users (
  id INTEGER,
  username VARCHAR(50)
);

2. Always Define Primary Keys

-- Every table should have a primary key
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2)
);

3. Use NOT NULL for Required Fields

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,      -- Required
  total_amount DECIMAL(10,2) NOT NULL,  -- Required
  notes TEXT                      -- Optional
);

4. Index Frequently Queried Columns

Indexes speed up searches but slow down inserts.

-- Create an index on email for faster lookups
CREATE INDEX idx_users_email ON users(email);

-- Create an index on multiple columns
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

5. Normalize Your Data

Don't repeat data - use relationships instead.

Bad (Repeated data):

orders table:
id | user_name | user_email      | product_name | price
1  | John Doe  | [e-mail]| Widget      | 19.99
2  | John Doe  | [e-mail]| Gadget      | 29.99

Good (Normalized):

users table:
id | name     | email
1  | John Doe | [e-mail]

orders table:
id | user_id | product_name | price
1  | 1       | Widget      | 19.99
2  | 1       | Gadget      | 29.99

6. Use Transactions for Related Operations

Transactions ensure all operations succeed or all fail together.

async function transferMoney(fromUserId, toUserId, amount) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Deduct from sender
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2',
      [amount, fromUserId]
    );

    // Add to receiver
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
      [amount, toUserId]
    );

    await client.query('COMMIT');
    console.log('Transfer successful');
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Transfer failed:', error);
  } finally {
    client.release();
  }
}

7. Regular Backups

Always backup your database regularly:

# Backup a database
pg_dump -U postgres my_first_db > backup.sql

# Restore from backup
psql -U postgres my_first_db < backup.sql

---

Common Mistakes to Avoid

1. Forgetting WHERE in UPDATE/DELETE

-- ❌ DISASTER - Updates ALL users!
UPDATE users SET age = 30;

-- ✅ CORRECT - Updates specific user
UPDATE users SET age = 30 WHERE id = 1;

2. Not Handling NULL Values

-- ❌ This won't find rows where age is NULL
SELECT * FROM users WHERE age != 30;

-- ✅ This properly handles NULL
SELECT * FROM users WHERE age IS NULL OR age != 30;

3. Using SELECT * in Production

-- ❌ Wasteful - retrieves unnecessary data
SELECT * FROM users;

-- ✅ Better - only get what you need
SELECT id, name, email FROM users;

4. Not Using Indexes on Large Tables

-- Without index: slow on millions of rows
SELECT * FROM orders WHERE user_id = 123;

-- Create index for faster queries
CREATE INDEX idx_orders_user_id ON orders(user_id);

5. Storing Passwords in Plain Text

-- ❌ NEVER do this
INSERT INTO users (username, password)
VALUES ('john', 'password123');

-- ✅ Always hash passwords (in your application)
-- Use bcrypt, argon2, or similar
INSERT INTO users (username, password_hash)
VALUES ('john', '$2b$10$...');  -- Hashed password

---

Troubleshooting Guide

Issue: "relation does not exist"

Error: ERROR: relation "users" does not exist

Solutions:

-- Check if you're in the right database
\c my_first_db

-- List all tables to verify
\dt

-- If table doesn't exist, create it
CREATE TABLE users (...);

Issue: "permission denied"

Error: `ERROR: permission denied for table users