Product Catalog Database Model

Build a product catalog database model with proper field constraints and validation.

Requirements

Create a database model for an e-commerce product catalog that properly defines fields with appropriate constraints, validation rules, and database-level configurations.

Product Model

Create a Product table model with the following specifications:

• id: Auto-incrementing primary key
• sku: Unique product identifier (string, max 50 characters, required, must be unique)
• name: Product name (string, max 200 characters, required)
• description: Optional product description text
• price: Product price with exactly 2 decimal places precision (required, must be greater than 0)
• stock_quantity: Current stock level (integer, required, must be greater than or equal to 0, default value of 0)
• category_id: Foreign key reference to a category (optional, should cascade on delete)
• is_active: Boolean flag indicating if product is available (required, default True)

Category Model

Create a Category table model with:

• id: Auto-incrementing primary key
• name: Category name (string, max 100 characters, required, must be unique)

Establish a proper relationship between Category and Product models where a category can have multiple products.

Database Setup

Implement functions to:

• Create the database engine with an in-memory SQLite database
• Create all tables in the database
• Return a session for database operations

Test Cases

• Creating a product with all required fields should succeed @test
• Creating a product with a negative price should fail validation @test
• Creating a product with negative stock should fail validation @test
• Creating a product without a name should fail @test
• SKU field should enforce uniqueness constraint @test
• Category name should enforce uniqueness constraint @test
• Products should be accessible from a category through the relationship @test

@generates

API

from sqlmodel import SQLModel, Field, Relationship, create_engine, Session
from typing import Optional, List
from decimal import Decimal
from datetime import datetime

class Category(SQLModel, table=True):
    """Category model for organizing products."""
    pass

class Product(SQLModel, table=True):
    """Product model with field constraints and validation."""
    pass

def create_database() -> tuple:
    """
    Create database engine and tables.

    Returns:
        tuple: (engine, session) for database operations
    """
    pass

Dependencies { .dependencies }

sqlmodel { .dependency }

Provides database modeling with validation support.