Two Weeks of Database Learning
Weeks 14 and 15 covered advanced PostgreSQL and MongoDB basics.
JOINs
JOINs were tricky. Understanding how they work took practice.
INNER JOIN returns only matching rows from both tables. If a row in table A has no match in table B, it's excluded.
LEFT JOIN returns all rows from the left table. If there's no match in the right table, you get NULL values for those columns.
RIGHT JOIN is the opposite. All rows from the right table, NULLs for missing matches on the left.
FULL OUTER JOIN returns all rows from both tables. NULLs where there are no matches.
When to use which JOIN matters. Finding authors with their books uses an INNER JOIN if you only want authors who have books. Finding all authors, including those without books, uses LEFT JOIN.
Multiple JOINs work by chaining. Join books to authors, then join reviews to books. Three tables connected through two JOINs.
Database Design
This was the key learning. How to design effective databases.
Understanding relationships between tables. One-to-one is rare, like users and profiles. One-to-many is common, like users and posts. Many-to-many needs junction tables, like students and courses.
Junction tables connect two tables in many-to-many relationships. The enrollments table connects students and courses. It stores student_id and course_id pairs.
Normalization prevents data redundancy. Instead of storing customer information in every order, store customers once and reference them by ID. Changes happen in one place.
Foreign key constraints enforce relationships. They prevent orphaned records. ON DELETE CASCADE removes child records when the parent is deleted. ON DELETE RESTRICT prevents deletion if children exist.
Designing databases is harder than writing queries. Deciding what tables you need, how they connect, and what constraints ensure data integrity. Getting structure wrong early creates problems later.
Node.js + PostgreSQL
Connected PostgreSQL to Node.js using the pg library. Pool connections manage database connections efficiently. Parameterized queries prevent SQL injection.
Writing queries from code is different from writing them in psql. Handling async results, error cases, and edge conditions. Testing that queries return what you expect.
SQL injection prevention is critical. Never concatenate user input into queries. Always use parameterized queries with $1, $2 placeholders.
Express Integration
Built a complete REST API connected to PostgreSQL. Routes define endpoints. Controllers handle business logic and database queries. Error handling catches database errors and validation failures.
Pagination requires LIMIT and OFFSET. Filtering adds WHERE clauses dynamically based on query parameters. Validation ensures that data is correct before it hits the database.
The full stack: Express receives requests, controllers query PostgreSQL, results get formatted, and returned—everything connected.
MongoDB Basics
Started learning MongoDB alongside PostgreSQL. Different mental model.
NoSQL databases store documents instead of rows. Documents are JSON-like objects. Collections hold documents like tables hold rows, but without fixed schemas.
Set up with MongoDB Atlas. Cloud-hosted database free tier. Connection strings, authentication, and network access. Different from the local PostgreSQL setup.
Understanding the differences between SQL and NoSQL. PostgreSQL enforces schemas and relationships. MongoDB is flexible; documents in the same collection can have different fields.
When to use which database? PostgreSQL for structured data with clear relationships. MongoDB for flexible, rapidly changing data structures.
Challenges
JOINs took time to understand. When to use which type, how they affect results, and why you sometimes need multiple JOINs.
Database design is harder than it looks. Relationships, normalization, constraints. Getting it right requires thinking through how data connects.
Moving Forward
Diving deeper into MongoDB. CRUD operations, queries, and aggregation. Understanding NoSQL thoroughly before comparing both approaches.
Question: What database concept was hardest for you to understand?
Top comments (0)