Week 9

Types of Databases

Databases: Comparison, Choosing & Polyglot

jsonb: Concepts & Setup

jsonb: querying updating

jsonb: Indexes, Tradeoffs & Exercise

Object Storage

Practice

Assignment

• Teachers

Backend Track

Content

Let’s get practical

<aside> 🚧

This page is currently under construction. Please check back later.

</aside>

Exercises

💭 Practice exercises are optional and do not need to be submitted.

For all exercises, use a small Spring Boot project with Spring Web, JDBC API, PostgreSQL Driver, Lombok, and Jackson Databind. You may create a fresh project for each exercise, or keep each exercise in its own feature package inside one practice project.

Exercise 1 — Design a jsonb Schema

Choose a resource that has varying attributes per item type. Good candidates:

• Products: laptops, clothing, furniture — each type has different specs
• Job listings: engineering, design, marketing — each has different requirements
• Animals in a shelter: dogs, cats, birds — each has different care notes

Create a markdown file named SCHEMA_DESIGN.md and design a jsonb-backed table for your resource.

Requirements:

• One table with at least two normal columns and one jsonb column
• Show three example rows with different JSON shapes in the jsonb column — each row should have different fields inside the JSON
• Write the CREATE TABLE SQL statement
• Write four SQL queries using at least three different operators: >> > @> and jsonb_set
• For each query, write one sentence explaining why you chose that operator

Reflection questions:

1. Which fields did you put in normal columns and which in jsonb? What was your reasoning?
2. If your most common query filters by a JSON field — should that field stay in jsonb or become a proper column?

Exercise 2 — Build a jsonb API

Choose one of these resources:

• Articles with tags (news articles tagged with topics)
• Properties with amenities (rental listings with different features per property type)
• Employees with skills (each role has different skill requirements)

Build a Spring Boot REST API backed by PostgreSQL with a jsonb column.

Requirements:

• Create the table using schema.sql (not manually in psql)
• Load at least three sample records with different JSON shapes using a DataLoader
• Implement these endpoints:

• Use JdbcTemplate and RowMapper — same pattern as Week 5
• Handle the case where the record is not found — return 404 Not Found

Reflection questions:

1. What happens when you query details->>'field' for a row that does not have that field? Test it and explain the result.
2. You used @> to match multiple fields. What would break if you tried to do the same thing with >> and WHERE clauses?

Exercise 3 — Upload and Retrieve a File

Set up Backblaze B2 and connect it to a Spring Boot project. Build a minimal file API with three endpoints.

Setup (do this first):

Follow the Backblaze B2 Setup Guide to:

• Create a free account at backblaze.com — email only, no credit card
• Create a private bucket
• Create an application key — copy the keyID and applicationKey immediately, they are shown only once
• Add your credentials to application.yaml

Requirements:

Build these three endpoints:

• The upload endpoint must accept a multipart/form-data request with a field named file
• The upload endpoint must return the object key in the response body
• The presigned URL must expire after 15 minutes — test this by waiting and trying the URL again
• Verify each operation in the Backblaze dashboard after running it in Postman

Reflection questions:

1. You uploaded a file and got back a key. In a real application, where would you store this key?
2. What is the difference between using a presigned URL vs making the bucket public? When is each appropriate?
3. What happens if you try to use a presigned URL after it has expired?

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.