🧱 postgres-for-legosets

One command to a fully loaded PostgreSQL database — an Azure Developer CLI (azd) template that provisions an Azure Database for PostgreSQL Flexible Server and populates it with 21,503 LEGO sets spanning 1949–2023.

Perfect for demos, workshops, learning PostgreSQL on Azure, or bootstrapping a sample dataset for your next app.

🏗️ What gets created

┌──────────────────────────────────────────┐
│  Resource Group: rg-{env-name}           │
│                                          │
│  ┌────────────────────────────────────┐  │
│  │  PostgreSQL Flexible Server        │  │
│  │  • Burstable B1ms, v16            │  │
│  │  • Password auth                   │  │
│  │                                    │  │
│  │  └─ legodb                         │  │
│  │     └─ lego_sets table             │  │
│  │        21,503 rows                 │  │
│  └────────────────────────────────────┘  │
│                                          │
│  Firewall: Azure services + dev access   │
└──────────────────────────────────────────┘

✅ Prerequisites

Tool	Install
Azure Developer CLI (azd)	Install azd
Python 3.9+	python.org
Azure subscription	Free account

🚀 Quick start

# Clone and deploy in one shot
azd init -t charris-msft/postgres-for-legosets
azd auth login
azd up

When prompted, provide a PostgreSQL admin password. That's it — the postprovision hook creates the table and imports all 21,503 LEGO sets.

⚙️ Configuration

Variable	Description	Default
POSTGRES_ADMIN_LOGIN	PostgreSQL admin username	legoadmin
POSTGRES_ADMIN_PASSWORD	PostgreSQL admin password	(prompted during azd up)

📦 What happens during deployment

1. azd provision — Creates the resource group, PostgreSQL Flexible Server (Burstable B1ms, v16), legodb database, and firewall rules
2. postprovision hook — Installs Python dependencies and runs scripts/import_data.py, which creates the lego_sets table and imports all 21,503 rows from the bundled CSV

📐 Table schema

CREATE TABLE lego_sets (
    set_number VARCHAR(50) PRIMARY KEY,
    name       TEXT NOT NULL,
    year       INTEGER,
    num_parts  INTEGER,
    image_url  TEXT,
    theme_name VARCHAR(200)
);

Column	Type	Description
set_number	VARCHAR(50)	Official LEGO set number (e.g., 75192-1) — primary key
name	TEXT	Set name
year	INTEGER	Year released (1949–2023)
num_parts	INTEGER	Piece count
image_url	TEXT	Image URL from Rebrickable
theme_name	VARCHAR(200)	Theme name (e.g., Star Wars, Technic)

🔍 Sample queries

Connect with psql, Azure Data Studio, or your favorite PostgreSQL client:

-- Largest Star Wars sets
SELECT name, year, num_parts
FROM lego_sets
WHERE theme_name = 'Star Wars'
ORDER BY num_parts DESC
LIMIT 10;

-- Sets per theme (top 20)
SELECT theme_name, COUNT(*) AS set_count
FROM lego_sets
GROUP BY theme_name
ORDER BY set_count DESC
LIMIT 20;

-- Average piece count by decade
SELECT (year / 10) * 10 AS decade,
       ROUND(AVG(num_parts)) AS avg_parts,
       COUNT(*) AS sets
FROM lego_sets
WHERE year IS NOT NULL
GROUP BY decade
ORDER BY decade;

-- Search by name
SELECT set_number, name, year, theme_name
FROM lego_sets
WHERE name ILIKE '%millennium falcon%';

🧹 Clean up

azd down

⚠️ Note: azd down deletes the resource group and everything in it. This is the intended behavior for a standalone template. Do not point this template at a resource group containing other resources you want to keep. See azure/azure-dev#4785.

🗂️ Project structure

postgres-for-legosets/
├── azure.yaml                    # azd project config with postprovision hooks
├── data/
│   └── lego_sets_and_themes.csv  # 21,503 LEGO sets (bundled, CC0 license)
├── infra/
│   ├── main.bicep                # Subscription-scoped entry point
│   ├── main.parameters.json      # Parameter bindings from azd env
│   └── modules/
│       └── postgres.bicep        # PostgreSQL server, database, firewall rules
├── scripts/
│   ├── import_data.py            # CSV → PostgreSQL import script
│   ├── postprovision.ps1         # Windows hook
│   └── postprovision.sh          # Linux/macOS hook
├── requirements.txt              # Python dependencies
└── README.md

📊 Data source

LEGO Sets & Themes Database (1949-2023) by Jonathan Kraayenbrink — CC0 Public Domain. Original data sourced from Rebrickable.

📄 License

This project is licensed under the Apache License 2.0 — see LICENSE for details. The LEGO dataset is CC0 (public domain).